MySQL Initialization Parameters Tuning

Preamble

Aim of the document is to describe a non exhaustive list of important MySQL initialization parameters that will affect directly the performance of your MySQL database.

You can start from template files that you can found at installation directory/support_files:

my-small.cnf
my-medium.cnf
my-large.cnf
my-huge.cnf
my-innodb-heavy-4G.cnf

This document has been written using MySQL 5.1 but I hope to be able to do an update with MySQL 5.5 one day. The parameters in each section are listed by my preferred importance.

To display the variables you can use:

mysql> show variables;
+---------------------------------+-------------------------------+
| Variable_name                   | Value                         |
+---------------------------------+-------------------------------+
| auto_increment_increment        | 1                             |
| auto_increment_offset           | 1                             |
| automatic_sp_privileges         | ON                            |
| back_log                        | 50                            |
| basedir                         | /                             |
.
.

mysql> show variables like 'query%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| query_alloc_block_size       | 8192    |
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 0       |
| query_cache_type             | ON      |
| query_cache_wlock_invalidate | OFF     |
| query_prealloc_size          | 8192    |
+------------------------------+---------+
7 rows in set (0.00 sec)

mysql> select @@global.version;
+------------------------------+
| @@global.version             |
+------------------------------+
| 5.0.44sp1-enterprise-gpl-log |
+------------------------------+
1 row in set (0.00 sec)

mysql> select @@session.div_precision_increment;
+-----------------------------------+
| @@session.div_precision_increment |
+-----------------------------------+
|                                 4 |
+-----------------------------------+
1 row in set (0.00 sec)

You can change the system variables for the MySQL server or only for your session with:

SET [GLOBAL | SESSION] system_var_name = expr;
SET [@@global. | @@session.]system_var_name = expr;

Example:

mysql> select 1/7;
+--------+
| 1/7    |
+--------+
| 0.1429 |
+--------+
1 row in set (0.04 sec)
 
mysql> set @@session.div_precision_increment=7;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select 1/7;
+-----------+
| 1/7       |
+-----------+
| 0.1428571 |
+-----------+
1 row in set (0.00 sec)

Remark:
Changing date format like NLS parameters in Oracle is not yet implemented:

mysql> show variables like 'date%';
+-----------------+-------------------+
| Variable_name   | Value             |
+-----------------+-------------------+
| date_format     | %Y/%m/%d          |
| datetime_format | %Y-%m-%d %H:%i:%s |
+-----------------+-------------------+
2 rows in set (0.00 sec)
 
mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2008-10-29 13:55:45 |
+---------------------+
1 row in set (0.00 sec)
 
mysql> select curdate();
+------------+
| CURDATE()  |
+------------+
| 2008-10-29 |
+------------+
1 row in set (0.00 sec)

The initialization parameters that need to be tuned to have full usage of your hardware are under the [mysqld] section of the my.cnf initialization parameter file.

Generic parameters

Parameter

Recommendation

back_log

Queue that the listener can handle. Default value is 50 and could be interesting to tune it for non-optimal application that are not using pooling. Avoid the "connection refused" error message.

bind-address

The IP address to bind to. For cluster configuration.

datadir

The MySQL server main directory (/mysql/ for my installation).

init_file

Full SQL filename that you want to execute when MySQL database start. Each statement must be on a single line and should not include comments. Example: init_file=/mysql/mysqld_init.sql.

log

Deactivated by default. Can be used to log all what is running on your server (debug mode).

log_error

Location of the error log (datadir by default).

max_connections

SESSIONS Oracle parameter equivalent i.e. maximum number of connections to your MySQL server. Default is 100.

max_user_connections

Maximum connection per MySQL account. Default value of 0 means unlimited.

pid_file

Directory of pid file hostname.pid. Better not to put it in /tmp.

port

The port on which MySQL is listening for connection. Default value is 3306.

skip-bdb

Disable the BDB storage engine. This saves memory and might speed up some operations. Do not use this option if you require BDB tables.

socket

Socket file of the MySQL database (mysql.sock) for local connections. Default directory is often /tmp so better to change it to avoid its removal by default /tmp cleaning.

storage_engine

Default storage engine when creating tables. To set it use default-storage-engine=innodb.

table_cache (table_open_cache for MySQL 5.1)

The number of open tables for all threads. You can check whether you need to increase with:

mysql> show status like 'Opened_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Opened_tables | 0     |
+---------------+-------+
1 row in set (0.00 sec)

If the value of Opened_tables is large and you don't do FLUSH TABLES often then you should increase its value.

thread_cache_size

How many threads the server should cache for reuse. When a client disconnects, the client's threads are put in the cache if there are fewer than thread_cache_size threads there. Requests for threads are satisfied by reusing threads taken from the cache if possible, and only when the cache is empty is a new thread created. This variable can be increased to improve performance if you have a lot of new connections. Deactivated by default and if using connection pooling you should not see bug performance improvement. By examining the difference between the Connections and Threads_created status variables, you can see how efficient the thread cache is.

mysql> show status like 'Threads_created';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| Threads_created | 150   |
+-----------------+-------+
1 row in set (0.00 sec)
 
mysql> show status like 'Connections';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections   | 151   |
+---------------+-------+
1 row in set (0.00 sec)

tmpdir

Directory for temporary files and temporary tables. Usually /tmp but you can specify multiple location for I/O tuning. When using MySQL replication this directory should not be a memory based filesystem.

Generic memory parameters

Parameter	Recommendation
join_buffer_size	Size of pool for join between no-indexes tables. Even if this should not occur as in a perfect world tables have indexes it could be interesting to size it when those operations are really needed. Default value is 128KB. It is used by SQL layer so applies to all storage engines.
max_allowed_packet	The maximum size of a query packet the server can handle as well as maximum query size server can process. Could need to be increased if using BLOB columns or long strings. Standard value is 1MB.
query_alloc_block_size	This parameter specifies the size of the blocks of memory (memory is organized into "memory roots") to be allocated during various phases of sql processing, such as parsing. It is used by SQL layer so applies to all storage engines. query_alloc_block_size is the size of the blocks to allocate to these memory roots when more memory needs to be allocated. Blocks are allocated for performance reasons, and to reduce fragmentation of memory, as well as reduce the number of calls to malloc().
query_prealloc_size	This is the amount of memory to pre-allocate when a memory root is initialized. If you know for a fact that most queries will end up needing 32kb of memory, because they are large SQL statements, it can be beneficial to preallocate it, instead of doing separate allocations for each block during parsing. The query_alloc_block_size is used in the "sql layer", which is above the storage engine layer. Specifically a thread is needing a small "memory root" before parsing an SQL statement, or before preparing a prepared statement. This happens before mysqld even knows what storage engine will be used. Consider this is for a microbuffer, such as when an app just needs to call malloc() for some small amount memory – we do not do that – instead we pull it from memory roots which is more efficient, and can be cleaned up properly when needed.
range_alloc_block_size	Variable specific to the range optimizer. Default value is 2KB.
read_buffer_size	It is used by SQL layer so applies to all storage engines. Buffer allocated when reading a table. Default value is 131072. Could be interesting to increase it if doing multiple sequential scans. It is a per session parameter.
read_rnd_buffer_size	It is used by SQL layer so applies to all storage engines. Buffer allocated when doing sort (ORDER BY) instead of using disk. Default value is 520192 and is allocated for each client so behave when increasing it.
sort_buffer_size	It is used by SQL layer so applies to all storage engines. Buffer that is allocated when a client need to perform a sort (ORDER BY GROUP BY). Unless use temporary file (tmpdir). It's a per session parameter.
tmp_table_size	Maximum size of internal memory tables (in fact min(max_heap_table_size, tmp_table_size)). Default value is 32MB, if a sort operation need more then MySQL create a MyISAM temporary table. Does not apply for user created memory tables. Equivalent of Oracle PGA_AGGREGATE_TARGET parameter. The eventual temporary table (a .MYD and a .MYI) is created in the directory defined by tmpdir so this value should be set to a dedicated directory or to datadir. It is a per session parameter.
transaction_alloc_block_size	transaction_prealloc_size and transaction_alloc_block_size deal with specific transaction related memory allocations. So if you have many statements in a transaction, bumping these two variables up can reduce number of allocations. This parameter is the increment size.
transaction_prealloc_size	transaction_prealloc_size and transaction_alloc_block_size deal with specific transaction related memory allocations. So if you have many statements in a transaction, bumping these two variables up can reduce number of allocations. This parameter is the initial allocation.

Optimizer parameters

Parameter	Recommendation
log_queries_not_using_indexes	Log in a text file (hostname-slow.log file in datadir directory) the queries that are not using indexes. Not activated by default.
log-slow-admin-statements	Log slow administrative statements such as OPTIMIZE TABLE, ANALYZE TABLE, and ALTER TABLE to the slow query log.
log_slow_queries	Log queries that are running longer than long_query_time in (hostname-slow.log file in datadir directory). Not activated by default.
long_query_time	Threshold when to log slow query. Default value is 10 (seconds).
max_join_size	Forbid execution of statements that will examine more than max_join_size rows or combinations of rows (joins) or disk seeks. Changing the default values will reset BIG_TABLES to 0 (when BIG_TABLES = 0 then all temporary tables are in memory rather than disk). If resultset is in query cache then SQL command is not affected by this parameter.
sql_mode	No default value (!!). Define how MySQL database engine will behave and its resistance to human error. Suggested value is TRADITIONAL. One interesting value to append when migrating from Oracle could be ORACLE (values comma separated).

Binary logging

Parameter	Recommendation
binlog_cache_size	Memory used to cache binary logging writes. Default value is 32KB. When binary logging is activated (recommended) could be interesting to set it at 1MB (binlog_cache_size = 1M).
binlog-format	MySQL was not fully clear when setting the default value for this parameter, and whether the release you have the default value is really different. Since MySQL 5.1.29 the STATEMENT value is the default and this was done to make it easier for people running 5.0 to upgrade to 5.1, as MIXED constitutes a change in default behavior from 5.0. But at the end MIXED is a better choice than STATEMENT in most situations.
expire_logs_days	To activate binary log removal. Suggested value is 1 week + 2 days i.e. 9 days. Don't be surprised if binary logs files are not removed automatically because as clearly stated in the official documentation possible removals happen at startup and at binary log rotation.
log_bin	The Oracle ARCHIVELOG mode equivalent. Strongly recommended to be activated for PITR (Point In Time Recovery) and MySLQ replication (log-bin=/mysql/mysql-bin.log). Log all DML of your MySQL database. To read the binary logging file you can use mysqlbinlog tool.
log_bin_trust_function_creators	When binary logging is activated functions/procedures/triggers which modifie tables' rows are not replicated and are not logged in binary logging for PITR (i.e. are non-deterministic). When binary logging is activated to create routines you must have the SUPER privilege or set this parameter to ON (default is OFF) and assume you trust the people developing codes. Keep in mind that if you specify DETERMINISTIC in a procedure MySQL is not checking your source code and assume that you do not modify figures. This issue is resolved in 5.1.
max_binlog_cache_size	If a multiple statement transaction require more than this amount of memory MySQL raise an error. Default and maximum value is 4GB (??!!) so you should not encounter any error.
max_binlog_size	Maximum size of the binary logging file. Default value is 1GB. Could be interesting to decrease it for low activity servers. If a transaction need more than max_binlog_size then binary log files could be bigger than max_binlog_size because transactions are not split between multiple binary log files.
sync_binlog	If this parameter is strictly positive the MySQL synchronize its binary logs to disk every sync_binlog writes (one write to binary logs every statement if autocommit enabled unless at every transaction). Safest choice if different from 0 (no transactions lost in case of crash) but also slower one, default value is 0 (deactivated) and MySQL relies on OS filesystems management (as for other files).

MyISAM

Parameter

Recommendation

bulk_insert_buffer_size

Improve INSERT … SELECT, INSERT … VALUES (…), (…), …, and LOAD DATA INFILE when adding data to non-empty tables. The default value is 8MB.

key_buffer_size

Buffer size for MyISAM indexes blocks. If massively using InnoDB do not increase it as the internal dictionary does not need performance tuning. There is no particular cache for MyISAM tables blocks and MySQL relies on OS filesystem cache. Like KEEP and RECYCLE cache of Oracle it is possible to configure multiple key buffer cache for MyISAM tables and assign indexes to it with:

mysql> create table test (val integer not null, descr varchar(30),primary key (val)) engine=myisam;
Query OK, 0 rows affected (0.07 sec)
 
mysql> set global hot_cache.key_buffer_size=128*1024;
Query OK, 0 rows affected (0.00 sec)
 
mysql> cache index test in hot_cache;
+------------+--------------------+----------+----------+
| Table      | Op                 | Msg_type | Msg_text |
+------------+--------------------+----------+----------+
| leadm.test | assign_to_keycache | status   | OK       |
+------------+--------------------+----------+----------+
1 row in set (0.01 sec)

To destroy it use:

mysql> set global hot_cache.key_buffer_size=0;
Query OK, 0 rows affected (0.00 sec)

Sun Microsystems suggest to create 3 different key buffer cache:

A “hot” key cache that takes up 20% of the space allocated for all key caches. Use this for tables that are heavily used for searches but that are not updated.
A “cold” key cache that takes up 20% of the space allocated for all key caches. Use this cache for medium-sized, intensively modified tables, such as temporary tables.
A “warm” key cache that takes up 60% of the key cache space. Employ this as the default key cache, to be used by default for all other tables.

To set, and load, indexes in particular caches while starting your MySQL server us the init_file parameter.

To load an entire index in a key buffer cache use:

mysql> load index into cache test ignore leaves;
+------------+--------------+----------+----------+
| Table      | Op           | Msg_type | Msg_text |
+------------+--------------+----------+----------+
| leadm.test | preload_keys | status   | OK       |
+------------+--------------+----------+----------+
1 row in set (0.00 sec)

key_cache_age_threshold

Number of seconds when a block at the end of the hot sub-chain LRU buffer cache can move to the warm sub-chain LRU buffer cache. Minimum is 100 and default is 300.

key_cache_block_size

Size of the blocks in the key buffer cache. Default value is 1024. Can be used to tune I/O operations, for example set it at size of I/O buffer. But as indexes blocks of your MyISAM indexes have different size (that you cannot control and based on the keys size and other indexes) setting this parameter too high may consume more memory to store leaf blocks of your indexes.

key_cache_division_limit

Key buffer cache management is done using a LRU management (Least Recently Used). The default value of this parameter is 100 (percents) i.e. the midpoint insertion strategy is deactivated. When setting this parameter lower than 100 the inital LRU chain is divided in two LRU sub-chains (hot sub-chain and a warm sub-chain with auto-managed limit). When read block are placed in the warm sub-chain and promoted in hot sub-chain after number of hits. A block in the hot sub-chain is demoted to warm sub-chain if it stays at the end of the hot sub-chain more than key_cache_age_threshold seconds.

myisam_use_mmap

Default is OFF. Enable memory-mapped access to MyISAM datafiles. Let MyISAM access to .MYD files through the operating system's page cache and avoids costly I/O system calls. Set it to ON.

InnoDB

show innodb status\G

show status;

Parameter

Recommendation

innodb_additional_mem_pool_size

Default value is 1MB. Used for InnoDB when it needs to store data dictionary related items (tables, FK's, etc). Increase it keeping in mind your server available memory and linked to the size of your InnoDB data model(s).

innodb_autoextend_increment

Size in MB of the increment of InnoDB datafiles (next value for autoextend feature of Oracle datafiles).

innodb_buffer_pool_size

InnoDB buffer pool (db_cache_size for Oracle). Default value is 8MB. Increase it keeping in mind your server available memory.

innodb_data_file_path

Paths to datafiles and their sizes (you can use K, M or G suffix letters). Autoextend feature can also be used, wtih eventually a maximum size. Creating multiple datafiles is the only I/O tuning you can perform. This does not allow you to split application/schema by datafiles, if you create multiple datafiles MySQL will fill the first one and when full will move to the second one and so on. clearer, it is not possible to specify in which tablespace you want to create your object. Example:

innodb_data_file_path = ibdata1:10M:autoextend
innodb_data_file_path = ibdata1:10M:autoextend:max:500M
innodb_data_file_path = ibdata1:100M;ibdata2:100M:autoextend:max:500M
innodb_data_file_path = /disk1/ibdata1:100M;/disk2/ibdata2:100M

innodb_data_home_dir

The home directory of InnoDB files (innodb_data_home_dir = /mysql/).

innodb_file_per_table

Default value is OFF. When activated creates tables, with datas and associated indexes, in their own tablespace (i.e. their own .ibd datafiles). You can change the parameter when you want has it change the behavior of newly created tables and not the behavior of existing tables. Can be used to make per table backup and more interesting balance the I/O over multiple physical disks.

innodb_flush_method

Default value is fdadasync (when not set it uses default value). Similar to Oracle filesystemio_options initialization parameters. Let you instruct MySQL how it should proceed with I/O (System buffer cache, direct I/O, …). Recommended value are default value and O_DIRECT.

innodb_log_buffer_size

Equivalent to log_buffer for Oracle. Save I/O when writing to InnoDB log. Default value 1M and performance could be improved by setting it at 8MB.

innodb_log_file_size

InnoDB log file size. Default value is 5MB. Size can vary from 1MB to 1/N the size of innodb_buffer_pool_size (N is the number of log file i.e. innodb_log_files_in_group). To change them after MySQL database creation shutdown cleanly your MysQL database. Move the ib_logfilex (where x=0,1…) to another location (belt and braces) and then restart the MySQL server. You should see in MysQL log file something like:

081103 17:01:44  mysqld started
081103 17:01:44  InnoDB: Log file /mysql/ib_logfile0 did not exist: new to be created
InnoDB: Setting log file /mysql/ib_logfile0 size to 25 MB
InnoDB: Database physically writes the file full: wait...
081103 17:01:45  InnoDB: Log file /mysql/ib_logfile1 did not exist: new to be created
InnoDB: Setting log file /mysql/ib_logfile1 size to 25 MB
InnoDB: Database physically writes the file full: wait...
081103 17:01:46  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
081103 17:01:46  InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 661606924.
InnoDB: Doing recovery: scanned up to log sequence number 0 661606924
InnoDB: Last MySQL binlog file position 0 5713894, file name /mysql/mysql-bin.000006
081103 17:01:46  InnoDB: Started; log sequence number 0 661606924
081103 17:01:46 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.0.44sp1-enterprise-gpl-log'  socket: '/mysql/mysql.sock'  port: 3306  MySQL Enterprise Server (GPL)

innodb_log_files_in_group

Number of log file, same behavior as Oracle redo log file. Default an recommended value is 2.

innodb_log_group_home_dir

Default value is datadir. You can set it to put your InnoDB log files to another disk for I/O tuning.

innodb_mirrored_log_groups

Number of members of InnoDB log files. Default value is 1.

lower_case_table_names

Default value is 0 and if set to 1 table names are stored in lowercase on disk and table name comparisons are not case sensitive. If using InnoDB tables this parameter should be set to 1 to force names to be lowercase converted.

Query cache

Parameter

Recommendation

query_cache_size

Default value is 0 means that Query Cache is disabled. To confirm you can use:

mysql> show variables like 'have_query_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+
1 row in set (0.00 sec)

You can activate it with:

mysql> set global query_cache_size = 16*1024*1024;
Query OK, 0 rows affected (0.00 sec)
 
mysql> show variables like 'query%';
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| query_alloc_block_size       | 8192     |
| query_cache_limit            | 1048576  |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 16777216 |
| query_cache_type             | ON       |
| query_cache_wlock_invalidate | OFF      |
| query_prealloc_size          | 8192     |
+------------------------------+----------+
7 rows in set (0.00 sec)
 
mysql> select count(*) from leadm.leads;
+----------+
| count(*) |
+----------+
|    54714 |
+----------+
1 row in set (0.29 sec)
 
mysql> select count(*) from leadm.leads;
+----------+
| count(*) |
+----------+
|    54714 |
+----------+
1 row in set (0.00 sec)

To monitor query cache performance use:

mysql> show status like 'Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 16766872 |
| Qcache_hits             | 3        |
| Qcache_inserts          | 3        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 240      |
| Qcache_queries_in_cache | 1        |
| Qcache_total_blocks     | 4        |
+-------------------------+----------+
8 rows in set (0.00 sec)

query_cache_limit

Maximum cache limit of query result sets. Default is 1MB.

query_cache_type

Set the query cache behavior:

0 | OFF: query cache desactivated (memory not reclaimed until you set query_cache_size to 0).
1 | ON: Default value, all queries cached except "SELECT SQL_NO_CACHE" queries.
2 | DEMAND: Cache only "SELECT SQL_CACHE" queries

query_cache_min_res_unit

Size of the block inside the query cache. For large queries it could be interesting to increase it while for lots of small queries a lower value may be beneficial. Default value of 4KB should be adequate for most installation.

query_cache_wlock_invalidate

Normally, when one client acquires a WRITE lock on a MyISAM table, other clients are not blocked from issuing statements that read from the table if the query results are present in the query cache. Setting this variable to 1 (OFF) causes acquisition of a WRITE lock for a table to invalidate any queries in the query cache that refer to the table. This forces other clients that attempt to access the table to wait while the lock is in effect.

To be tested as it looks the opposite !!!

Recommended MySQL initialization parameters for InnoDB data model

# The following options will be passed to all MySQL clients
[client]
port = 3306
socket = /mysql/mysql.sock
 
# The MySQL server
[mysqld]
port = 3306
socket = /mysql/mysql.sock
pid_file = /mysql/server1.pid
skip-locking
server-id	= 1
default-storage-engine = innodb
 
# Memory
join_buffer_size = 512K
max_allowed_packet = 1M
sort_buffer_size = 512K
read_buffer_size = 512K
read_rnd_buffer_size = 512K
 
# Optimizer
log_queries_not_using_indexes
log-slow-admin-statements
log_slow_queries
long_query_time = 60
table_cache = 64
thread_cache_size = 8
 
# Binary logging
log-bin=/mysql/mysql-bin.log
binlog_cache_size = 1M
expire_logs_days = 9
max_binlog_size = 100M
 
# MyISAM
key_buffer_size = 8M
myisam_sort_buffer_size = 8M
myisam_use_mmap = ON
 
# InnoDB
innodb_additional_mem_pool_size = 16M
innodb_data_home_dir = /mysql/
innodb_data_file_path = ibdata1:10M:autoextend:max:1G
innodb_log_group_home_dir = /mysql/
innodb_buffer_pool_size = 100M
innodb_log_file_size = 25M
innodb_log_buffer_size = 8M
innodb_autoextend_increment = 10
lower_case_table_names = 1
 
# Query cache
query_cache_size = 16M
 
[mysqldump]
quick
max_allowed_packet = 16M
 
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
 
[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
 
[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
 
[mysqlhotcopy]
interactive-timeout

IT World

MySQL Initialization Parameters Tuning

Table of contents

Preamble

Generic parameters

Generic memory parameters

Optimizer parameters

Binary logging

MyISAM

InnoDB

Query cache

Recommended MySQL initialization parameters for InnoDB data model

About Post Author

Yannick Jaquier

Related Posts:

Leave a Reply Cancel Reply