KfWiki: Baseline config

HomePage » Database » MySql » MysqlTuning

Subtopics

Baseline config

[mysqld]
open_files_limit=8192
key_buffer = 64M
read_buffer = 2M
sort_buffer = 1M
max_connections = 200
# Other settings
wait_timeout=180
thread_concurrency = 4 # 2x number of CPU

thread_cache=32
query_cache_size = 12M
query_cache_limit=2M
query-cache-type = 1
log-slow-queries=/var/lib/mysql/slow-queries.log
# 5.1 # slow_query_log_file=/var/lib/mysql/slow-queries.log
long_query_time = 1

innodb_buffer_pool_size = 256M
innodb_file_io_threads = 4
innodb_thread_concurrency = 8
innodb_file_per_table
innodb_log_file_size=32M 
innodb_log_files_in_group=3
innodb_log_buffer_size=8M
# see http://www.mysqlperformanceblog.com/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/
# innodb_doublewrite = no
# default-table-type = innodb
skip-bdb

Mysql max_connections

It can be adjusted while the database is running.

set global max_connections = 200;

Mysql tuning (displaying usage)

Check your mysql's usage with mtop [http://mtop.sourceforge.net]. For example:

# For illustration, let's do 10+10 9 million times
SELECT BENCHMARK(900000000,10+10);
# Then run mtop
mtop --dbuser=root --host=localhost

will give you

localhost  mysqld 5.0.37 up 0 day(s),  0:35 hrs
2 threads: 2 running, 0 cached. Queries/slow: 657/0 Cache Hit: 60.00%
Opened tables: 0  RRN: 16.8K  TLW: 0  SFJ: 0  SMP: 0  QPS: 0

ID       USER     HOST             DB           TIME   COMMAND STATE        INFO
45       root     localhost        test         4      Query   executing    SELE
44       root     localhost                            Query                show

mtop fields

Field Definition
RRN If this value is more than 50k, tables may not be properly indexed (lots of table scan.
SMP If this value is high, sort memory is not enough
TLW Table lock wait. If this is high, optimize queries, then split tables.
SFJ # of joins without keys
QPS Total # of sql handled
COMMAND Most of them are self-explained except - Binlog Dump (Slave thread reading queries from master)

Mytop utility

MyTop is similiar to mtop. It can be obtained from http://jeremy.zawodny.com/mysql/mytop. To install, download the source file from the URL, and then perl Makefile.PL; make && make install. Sample output:

>mytop -uroot -ppasswd
MySQL on localhost (5.0.37-log)                                                    up 0+11:29:09 [18:59:44]
 Queries: 369.0  qps:    0 Slow:     0.0         Se/In/Up/De(%):    00/00/00/00 
			 qps now:    2 Slow qps: 0.0  Threads:    2 (   2/   0) 00/00/00/00 
 Key Efficiency: 100.0%  Bps in/out:   0.0/  0.6   Now in/out:  28.3/ 4.4k

	  Id      User         Host/IP         DB      Time    Cmd Query or State                              
	  --      ----         -------         --      ----    --- ----------                                  
	   3      root       localhost       test         0  Query show full processlist                       
	   2      root       localhost       test         1  Query SELECT BENCHMARK(900000000,10+10)

No tool

You can always use mysql itself to obtain some statistics

show status like '%cache%';
show status like 'max_used_connections';
show status like 'open%tables';
SHOW STATUS LIKE 'created_tmp%';

Other tools

mysqlreport
mysqlard - record statistics every 5 min and give tuning suggestions

Mysql tuning (compile time)

Starting from mysql4, external locking can be disabled by --external-locking and --skip-external-locking. Works when the same data is used by one mysql instance.

Mysql tuning (configuration)

my.cnf offers parameters that affect mysql's performance. mysqld --verbose --help shows the current values, or show status / show variables on running instance. To show running parameters, do show variables like '%buffer%';

Here're each parameter and their usage

Parameter	Explaination
max_connections	500, def 100 (set it to MaxChildren defined in apache, if 1 connection is used per request)
key_buffer	384M (set it to half of system memory)
table_cache	1000 depends on number of tablest
wait_timeout	7200 seconds before mysql dump a connection
max_allowed_packet	16M (default 8M)

memory_required=global_buffer+(thread_buffers)*max_connections

where thread_buffers includes the following:

sort_buffer
myisam_sort_buffer
read_buffer
join_buffer
read_rnd_buffer

and global_buffers includes:

key_buffer_size
query_cache_size
innodb_buffer_pool
innodb_log_buffer
innodb_additional_mem_pool
net_buffer

Parameters

query_cache_size:

* MySQL 4 provides one feature that can prove very handy - a query cache. In a situation where the database has to repeatedly run the same queries on the same data set, returning the same results each time, MySQL can cache the result set, avoiding the overhead of running through the data over and over and is extremely helpful on busy servers.

key_buffer_size:

* The value of key_buffer_size is the size of the buffer used with indexes. The larger the buffer, the faster the SQL command will finish and a result will be returned. The rule-of-thumb is to set the key_buffer_size to at least a quarter, but no more than half, of the total amount of memory on the server. Ideally, it will be large enough to contain all the indexes (the total size of all .MYI files on the server).
* A simple way to check the actual performance of the buffer is to examine four additional variables: key_read_requests, key_reads, key_write_requests, and key_writes.
* If you divide the value of key_read by the value of key_reads_requests, the result should be less than 0.01. Also, if you divide the value of key_write by the value of key_writes_requests, the result should be less than 1.

table_cache:

* The default is 64. Each time MySQL accesses a table, it places it in the cache. If the system accesses many tables, it is faster to have these in the cache. MySQL, being multi-threaded, may be running many queries on the table at one time, and each of these will open a table. Examine the value of open_tables at peak times. If you find it stays at the same value as your table_cache value, and then the number of opened_tables starts rapidly increasing, you should increase the table_cache if you have enough memory.

sort_buffer:

* The sort_buffer is very useful for speeding up myisamchk operations (which is why it is set much higher for that purpose in the default configuration files), but it can also be useful everyday when performing large numbers of sorts.

myisam_sort_buffer

* The size of the buffer that is allocated when sorting MyISAM indexes during a REPAIR TABLE or when creating indexes with CREATE INDEX or ALTER TABLE.

read_rnd_buffer_size:

* The read_rnd_buffer_size is used after a sort, when reading rows in sorted order. If you use many queries with ORDER BY, upping this can improve performance. Remember that, unlike key_buffer_size and table_cache, this buffer is allocated for each thread. This variable was renamed from record_rnd_buffer in MySQL 4.0.3. It defaults to the same size as the read_buffer_size. A rule-of-thumb is to allocate 1KB for each 1MB of memory on the server, for example 1MB on a machine with 1GB memory.

thread_cache:

* If you have a busy server that's getting a lot of quick connections, set your thread cache high enough that the Threads_created value in SHOW STATUS stops increasing. This should take some of the load off of the CPU.

tmp_table_size:

* "Created_tmp_disk_tables" are the number of implicit temporary tables on disk created while executing statements and "created_tmp_tables" are memory-based. Obviously it is bad if you have to go to disk instead of memory all the time.

Quickly, if I have a system with 2G memory, I should set these?

key_buffer = 1G
read_buffer = 2M
sort_buffer = 1M
max_connections = 340
# Note 1G + (2M + 1M) * 340 = 2044M
# Other settings
wait_timeout=180
thread_concurrency = 4 # 2x number of CPU

If the above formula gives you a key_buffer requirement that can't be met on your server, check the cache hit ratio. If it's below 0.01, that means your key_buffer is large enough. cache hit ratio = Key_reads/Key_read_requests. In the following case, the ratio is 588/5042 = 0.1166. Just an illustration, this mysql hardly used, the values are probably of no reference value.

show status like '%key_read%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Key_read_requests | 5042  |
| Key_reads         | 588   |
+-------------------+-------+
2 rows in set (0.00 sec)

table_cache

open_tables shows you how many tables are opened at the moment. opened_tables tells you how many were opened in the past. So if your mysql has been running for a short period of time, opened_tables should give you a pretty good idea on what value should be set to table_cache.

table_cache is related to max_connections. For example, for 200 concurrent running connections, you should have a table cache size of at least 200 Ã— N, where N is the maximum number of tables per join in any of the queries which you execute. You must also reserve some extra file descriptors for temporary tables and files.

When the table cache fills up, the server uses the following procedure to locate a cache entry to use:

* Tables that are not currently in use are released, beginning with the table least recently used.
* If a new table needs to be opened, but the cache is full and no tables can be released, the cache is temporarily extended as necessary.

show status like 'open%tables%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables   | 128   |
| Opened_tables | 515   |
+---------------+-------+
2 rows in set (0.00 sec)

query cache

http://www.databasejournal.com/features/mysql/article.php/3110171

query-cache-size = 64M
query-cache-type = 1

Slow queries

log-slow-queries
long_query_time = 1

InnoDB

innodb_buffer_pool_size = 1G
innodb_file_io_threads = 4
innodb_thread_concurrency = 8
innodb_file_per_table

By default, innodb stores data in a single datafile. To make innodb stores table in separate files, enable the innodb_file_per_table option then do the following. This will cause mysql rebuild the table. To move it back to single data file, remove that option and repeat the following.

ALTER TABLE <table> ENGINE=InnoDB;

Disk contention

This option tells innodb to commit to disk every second as oppose to every DML. Brings about TPS improvement, but at the risk of changes not committing to disk in case of server issues.

innodb_flush_log_at_trx_commit = 2

Other interesting tuning parameters

delay_key_write: index in memory are not flushed between writes to a table. Instead, it's flushed when a table is closed. ref: http://www.petefreitag.com/item/441.cfm

KfWiki : MysqlTuning