Table of contents
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:
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.
| |
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:
To destroy it use:
Sun Microsystems suggest to create 3 different key buffer cache:
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:
| |||
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_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:
| |
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:
You can activate it with:
To monitor query cache performance use:
| |||
query_cache_limit | Maximum cache limit of query result sets. Default is 1MB. | |||
query_cache_type | Set the query cache behavior:
| |||
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 |