List:General Discussion« Previous MessageNext Message »
From:Justin Date:August 28 2007 5:08pm
Subject:Re: servers full potential / FT searches locking tables
View as plain text  
32bit, but I have all available memory..

MemTotal:      8179612 kB
MemFree:         43684 kB

on the box.   I think the 4gb is only windows.

All my tables are in myisam

so if I was to set
key_buffer_size=5500M

That'd be acceptable?

----- Original Message ----- 
From: "Mathieu Bruneau" <mathieu.bruneau@stripped>
To: "Justin" <mysql@stripped>
Cc: <mysql@stripped>
Sent: Tuesday, August 28, 2007 12:51 AM
Subject: Re: servers full potential / FT searches locking tables


> Your settings doesn't seem optimized much.
>
> So here first question, do you use 32bits or 64 bits platform? If you have 
> 64 bits platform with 64 bits mysql and os you can boost most the settings 
> to use almost the 8G of ram you have on the server. If you are using 
> 32bits you will have to do some calculation so you don't go over ~2.6G 
> (why not 4Gb?, go read on that on the net)
>
> So the 2 most importants settings are:
> key_buffer_size (mainly myisam table)
> and/or
> innodb_buffer_pool_size (innodb table)
>
> Depending if you're using more innodb or myisam (or a mix) you'll tweak 
> those pamareters differently, it's usually however not recommended to go 
> over 4Gb for the key_buffer_size. MyIsam only stores the key into that 
> buffer, so you don't have much index, not worth taking it too big for no 
> reason. Innodb however can cache data as well, and will benefit from the 
> biggest value possible.
>
> The server generate statistic that you can look to know the effect of 
> that. If you are using phpmyadmin in the variables and status part you can 
> see the index usage to guide you.
>
>
> You can have a look at the different my.cnf that comes with mysql 
> distribution they put comment in there with interesting value for thumbs 
> rule. Here the except for key_buffer_size and innodb_buffer_pool_size:
> # Size of the Key Buffer, used to cache index blocks for MyISAM tables.
> # Do not set it larger than 30% of your available memory, as some memory
> # is also required by the OS to cache rows. Even if you're not using
> # MyISAM tables, you should still set it to 8-64M as it will also be
> # used for internal temporary disk tables.
> key_buffer_size=2G
>
> # InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and
> # row data. The bigger you set this the less disk I/O is needed to
> # access data in tables. On a dedicated database server you may set this
> # parameter up to 80% of the machine physical memory size. Do not set it
> # too large, though, because competition of the physical memory may
> # cause paging in the operating system.  Note that on 32bit systems you
> # might be limited to 2-3.5G of user level memory per process, so do not
> # set it too high.
> innodb_buffer_pool_size=2G
>
> Regards,
> -- 
> Mathieu Bruneau
> aka ROunofF
>
> ===
> GPG keys available @ http://rounoff.darktech.org
>
> Justin a écrit :
>> Ok.. Straight to the point.. Here is what I currently have.
>>
>> MySQL Ver 14.12 Distrib 5.0.27
>> RHEL vs 5
>> 584GB Raid 5 storage
>> 8GB of RAM
>> and Dual 5130 processors (2.0GHz Intel Dual-Core Xeon)
>>
>> what my question is.. is am I utilizing the servers potential with the 
>> following as my settings.  The server is a dedicated MySQL server so I 
>> want all power to go to the server. It just seems to be laggy at times. 
>> And I want to be sure I've optimized to the fullest potential
>>
>> My biggest issue is with FT searches. Tables get locked during larger 
>> queries and I can't select anything when that happens. Is there any way 
>> not to lock the tables on a Full Text search? (does that make sense?)
>>
>> thanks again for any insight
>>
>> Justin.
>>
>> Here's a dump of the my.cnf and the phpmyadmin dump of vars.
>> ------------
>> /etc/my.cnf
>>
>> [mysqld]
>> datadir=/var/lib/mysql
>> socket=/var/lib/mysql/mysql.sock
>> wait_timeout=60
>> default-character-set=utf8
>> max_allowed_packet = 3000M
>> max_connections = 5000
>> ft_min_word_len=3
>>
>> server-id=1
>> log-error = /var/log/mysql/error.log
>> expire_logs_days = 3
>>
>>
>> # Default to using old password format for compatibility with mysql 3.x
>> # clients (those using the mysqlclient10 compatibility package).
>> old_passwords=0
>>
>> [mysql.server]
>> user=mysql
>>
>> [mysqld_safe]
>> err-log=/var/log/mysql/mysqld.log
>> pid-file=/var/run/mysqld/mysqld.pid
>> ------------
>>
>> auto increment increment   1
>> auto increment offset  1
>> automatic sp privileges  ON
>> back log  50
>> basedir  /
>> binlog cache size  32,768
>> bulk insert buffer size  8,388,608
>> character set client  utf8
>> character set connection  utf8
>> character set database  utf8
>> character set filesystem  binary
>> character set results  utf8
>> character set server  utf8
>> character set system  utf8
>> character sets dir  /usr/share/mysql/charsets/
>> collation connection  utf8_general_ci
>> collation database  utf8_general_ci
>> collation server  utf8_general_ci
>> completion type  0
>> concurrent insert  1
>> connect timeout  5
>> datadir  /var/lib/mysql/
>> date format  %Y-%m-%d
>> datetime format  %Y-%m-%d %H:%i:%s
>> default week format  0
>> delay key write  ON
>> delayed insert limit  100
>> delayed insert timeout  300
>> delayed queue size  1,000
>> div precision increment  4
>> engine condition pushdown  OFF
>> expire logs days  3
>> flush  OFF
>> flush time  0
>> ft boolean syntax  + -><()~*:""&|
>> ft max word len  84
>> ft min word len  3
>> ft query expansion limit  20
>> ft stopword file  (built-in)
>> group concat max len  1,024
>> have archive  YES
>> have bdb  NO
>> have blackhole engine  NO
>> have compress  YES
>> have crypt  YES
>> have csv  NO
>> have dynamic loading  YES
>> have example engine  NO
>> have federated engine  NO
>> have geometry  YES
>> have innodb  YES
>> have isam  NO
>> have merge engine  YES
>> have ndbcluster  NO
>> have openssl  DISABLED
>> have query cache  YES
>> have raid  NO
>> have rtree keys  YES
>> have symlink  YES
>> init connect
>> init file
>> init slave
>> innodb additional mem pool size  1,048,576
>> innodb autoextend increment  8
>> innodb buffer pool awe mem mb  0
>> innodb buffer pool size  8,388,608
>> innodb checksums  ON
>> innodb commit concurrency  0
>> innodb concurrency tickets  500
>> innodb data file path  ibdata1:10M:autoextend
>> innodb data home dir
>> innodb doublewrite  ON
>> innodb fast shutdown  1
>> innodb file io threads  4
>> innodb file per table  OFF
>> innodb flush log at trx commit  1
>> innodb flush method
>> innodb force recovery  0
>> innodb lock wait timeout  50
>> innodb locks unsafe for binlog  OFF
>> innodb log arch dir
>> innodb log archive  OFF
>> innodb log buffer size  1,048,576
>> innodb log file size  5,242,880
>> innodb log files in group  2
>> innodb log group home dir  ./
>> innodb max dirty pages pct  90
>> innodb max purge lag  0
>> innodb mirrored log groups  1
>> innodb open files  300
>> innodb support xa  ON
>> innodb sync spin loops  20
>> innodb table locks  ON
>> innodb thread concurrency  8
>> innodb thread sleep delay  10,000
>> interactive timeout  28,800
>> join buffer size  131,072
>> key buffer size  8,388,600
>> key cache age threshold  300
>> key cache block size  1,024
>> key cache division limit  100
>> language  /usr/share/mysql/english/
>> large files support  ON
>> large page size  0
>> large pages  OFF
>> lc time names  en_US
>> license  GPL
>> local infile  ON
>> locked in memory  OFF
>> log  OFF
>> log bin  OFF
>> log bin trust function creators  OFF
>> log error  /var/log/mysql/error.log
>> log queries not using indexes  OFF
>> log slave updates  OFF
>> log slow queries  OFF
>> log warnings  1
>> long query time  10
>> low priority updates  OFF
>> lower case file system  OFF
>> lower case table names  0
>> max allowed packet  1,073,740,800
>> max binlog cache size  4,294,967,295
>> max binlog size  1,073,741,824
>> max connect errors  10
>> max connections  5,000
>> max delayed threads  20
>> max error count  64
>> max heap table size  16,777,216
>> max insert delayed threads  20
>> max join size  18446744073709551615
>> max length for sort data  1,024
>> max prepared stmt count  16,382
>> max relay log size  0
>> max seeks for key  4,294,967,295
>> max sort length  1,024
>> max sp recursion depth  0
>> max tmp tables  32
>> max user connections  0
>> max write lock count  4,294,967,295
>> multi range count  256
>> myisam data pointer size  6
>> myisam max sort file size  2,147,483,647
>> myisam recover options  OFF
>> myisam repair threads  1
>> myisam sort buffer size  8,388,608
>> myisam stats method  nulls_unequal
>> net buffer length  16,384
>> net read timeout  30
>> net retry count  10
>> net write timeout  60
>> new  OFF
>> old passwords  OFF
>> open files limit  25,010
>> optimizer prune level  1
>> optimizer search depth  62
>> pid file  /var/lib/mysql/dbs.live.pid
>> port  3,306
>> preload buffer size  32,768
>> prepared stmt count  0
>> protocol version  10
>> query alloc block size  8,192
>> query cache limit  1,048,576
>> query cache min res unit  4,096
>> query cache size  0
>> query cache type  ON
>> query cache wlock invalidate  OFF
>> query prealloc size  8,192
>> range alloc block size  2,048
>> read buffer size  131,072
>> read only  OFF
>> read rnd buffer size  262,144
>> relay log purge  ON
>> relay log space limit  0
>> rpl recovery rank  0
>> secure auth  OFF
>> server id  1
>> skip external locking  ON
>> skip networking  OFF
>> skip show database  OFF
>> slave compressed protocol  OFF
>> slave load tmpdir  /tmp/
>> slave net timeout  3,600
>> slave skip errors  OFF
>> slave transaction retries  10
>> slow launch time  2
>> socket  /var/lib/mysql/mysql.sock
>> sort buffer size  2,097,144
>> sql big selects  ON
>> sql mode
>> sql notes  ON
>> sql warnings  OFF
>> ssl ca
>> ssl capath
>> ssl cert
>> ssl cipher
>> ssl key
>> storage engine  MyISAM
>> sync binlog  0
>> sync frm  ON
>> system time zone  EDT
>> table cache  64
>> table lock wait timeout  50
>> table type  MyISAM
>> thread cache size  0
>> thread stack  196,608
>> time format  %H:%i:%s
>> time zone  SYSTEM
>> timed mutexes  OFF
>> tmp table size  33,554,432
>> tmpdir  /tmp/
>> transaction alloc block size  8,192
>> transaction prealloc size  4,096
>> tx isolation  REPEATABLE-READ
>> updatable views with limit  YES
>> version  5.0.27-standard
>> version comment  MySQL Community Edition - Standard (GPL)
>> version compile machine  i686
>> version compile os  pc-linux-gnu
>> wait timeout  60
>> Open new phpMyAdmin window
>>
>>
>>
>>
>>
>>
>>
>
> 

Thread
servers full potential / FT searches locking tablesJustin27 Aug
  • Re: servers full potential / FT searches locking tablesJay Pipes27 Aug
    • Re: servers full potential / FT searches locking tablesRolando Edwards27 Aug
      • Re: servers full potential / FT searches locking tablesJay Pipes27 Aug
        • Re: servers full potential / FT searches locking tablesJustin27 Aug
          • Re: servers full potential / FT searches locking tablesnigel wood27 Aug
    • Re: servers full potential / FT searches locking tablesJustin27 Aug
  • Re: servers full potential / FT searches locking tablesMathieu Bruneau28 Aug
    • Re: servers full potential / FT searches locking tablesJustin28 Aug
      • Re: servers full potential / FT searches locking tablesMichael Dykman28 Aug
        • Re: servers full potential / FT searches locking tablesJustin28 Aug
        • Re: servers full potential / FT searches locking tablesKen Peng29 Aug
          • Re: servers full potential / FT searches locking tablesMichael Dykman30 Aug
            • Re: servers full potential / FT searches locking tablesKen Peng30 Aug
              • Re: servers full potential / FT searches locking tablesMichael Dykman30 Aug
        • Re: servers full potential / FT searches locking tablesJustin1 Sep
          • Re: servers full potential / FT searches locking tablesJustin3 Sep
            • Re: servers full potential / FT searches locking tablesBaron Schwartz3 Sep
              • Re: servers full potential / FT searches locking tablesJustin4 Sep
                • Memory Issue would someone confirmJustin4 Sep
                  • Re: Memory Issue would someone confirmJustin4 Sep