List:General Discussion« Previous MessageNext Message »
From:Mathieu Bruneau Date:August 28 2007 4:51am
Subject:Re: servers full potential / FT searches locking tables
View as plain text  
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