MySQL Configuration Variables
This is an overview of MySQL variables that can be tweaked in the my.cnf file for MySQL 5.5 or 5.6.
M, also, you would use G for GiB, and K for KiB
table_cache
Default: 64
The number of tables that can be open at any one time for all threads.
read_buffer_size
Default: 131072 (128 KiB)
Range: 8200 - 2147479552 Each request that performs a sequential scan of a table allocates this:
- For caching the indexes in a temporary file (not a temporary table), when sorting rows for ORDER BY.
- For bulk insert into partitions.
- For caching results of nested queries.
As well as some other areas that are engine specific.
read_rnd_buffer_size
Default: 262144 (256 KiB)
Value Range: 8200 - 2147483647 Buffer for non-sequential reading of rows to prevent disk seeks (used when performing sort etc)
max_allowed_packet
Default: 4 MiB as of MySQL 5.6.6, 1MiB for before that.
The maximum size of one packet or any generated/intermediate string, or any parameter sent by the mysql_stmt_send_long_data() C API function.
sort_buffer_size
Default: 2 MiB
Range: 32768 - 18446744073709551615 (64bit Linux) Non engine-specific each session that needs to do a sort will allocate a buffer of this size.
join_buffer_size
Default: 256 KiB
Range: 128 to 18446744073709547520 (bytes)
Minimum size of the buffer used for queries that cannot use an index, and instead perform a full table scan. Increase to get faster full joins when adding indexes is not possible. Joins will always allocate this minimum size. Best left low globally and set high in sessions that require large full joins.
thread_cache
Default: 0
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.
query_cache_size
Default: 0
The amount of memory allocated for caching query results. The permissible values are multiples of 1024. Other values are rounded down to the nearest multiple. The query cache needs a minimum size of about 40KB to allocate its structures. This will use memory even if query_cache_type is set to off (0).
query_cache_type
Default: 1
Set the caching policy. Allowed values are:
- 0 - off - Do not cache results in or retrieve results from the query cache.
- 1 - on - Cache all cacheable query results except for those that begin with
SELECT SQL_NO_CACHE - 2 - demand - cache only those queries that begin with
SELECT SQL_CACHE
1 or 2, I recommend that you enable query_cache_wlock_invalidate
query_cache_wlock_invalidate
Set to true to clear the cache for that table when a write lock is aquired for that table. This prevents other read queries circumventing the lock by pulling from the cache.
Allowed values: ON and OFF
query_prealloc_size
The size of the persistent buffer used for statement parsing and execution. This buffer is not freed between statements. If you are running complex queries, a larger query_prealloc_size value might be helpful in improving performance, because it can reduce the need for the server to perform memory allocation during query execution operations.
tmp_table_size
Default: 33554432 (32 MiB)
The maximum size of internal in-memory temporary tables (although if [max_heap_table_size](#max_heap_table_size) size is less, that will be the limit).
max_heap_table_size
Default: 16777216 (16 MiB)
The maximum to which user created memory tables are permitted to grow. The value of the variable is used to calculate MEMORY table MAX_ROWS values
tmp_table_size, however non-user created temporary tables may be limited by this.
InnoDB Specific
innodb_io_capacity
Default: 200
An upper limit on the I/O activity performed by the InnoDB background tasks, such as flushing pages from the buffer pool and merging data from the insert buffer. If you are running on an SSD (which you should), then you probably want to increase this to your SSD's IOPS.
innodb_buffer_pool_size
Set the amount of memory allocated to both Innodb data and index buffer cache. If the server requests data available in the cache, the data can be processed right away. Otherwise, the operating system will request that the data be loaded from the disk into the buffer. It is important to set this value as high as possible to use the more efficient innodb data and index buffer cache instead of operating system buffer.
If you get the error message:
The total number of locks exceeds the lock table size
... one way to resolve that is to increase this variable.
If set to 2 GB or more, you will probably want to adjust innodb_buffer_pool_instances as well.
The following script (source) should tell you the minimum pool size you should have.
SELECT CONCAT(ROUND(KBS/POWER(1024,IF(pw<0,0,IF(pw>3,0,pw)))+0.49999),
SUBSTR(' KMG',IF(pw<0,0,IF(pw>3,0,pw))+1,1)) recommended_innodb_buffer_pool_size
FROM (SELECT SUM(index_length) KBS FROM information_schema.tables WHERE
engine='InnoDB') A,(SELECT 3 pw) B;
innodb_buffer_pool_instances
Default: 1
Range: 1-64
If innodb_buffer_pool_size is set to more than 1GB, innodb_buffer_pool_instances divides the InnoDB buffer pool into this many instances. Many instances can help reduce contention concurrency. Each instance manages its own data structures and takes an equal portion of the total buffer pool size, so for example if innodb_buffer_pool_size is 4GB and innodb_buffer_pool_instances is set to 4, each instance will be 1GB.
Each instance should ideally be at least 1GB in size.
innodb_change_buffer_max_size
Default 25
Range 0 - 50
This configuration option allows you to configure the maximum size of the change buffer as a percentage of the total size of the buffer pool. You may wish to increase this on databases that have heavy insert, update, and delete activity where change buffer merging does not keep pace with new change buffer entries, causing the change buffer to reach its maximum size limit.
If your database is mainly static data, then you may want to change this to 0 to ensure that most of the buffer is used for caching results for faster SELECT statements.
innodb_change_buffering
Default: all
Valid values: inserts, none, deletes, purges, changes, all.
Sets how InnoDB change buffering is performed. INSERT, UPDATE and DELETE statements can be particularly heavy operations to perform, as the indexes need to be updated after each change. If there are multiple statements, it makes sense to buffer these changes so that they happen more efficiently. [Source]
- inserts - Only buffer insert operations
- deletes - Only buffer delete operations
- changes - Buffer both insert and delete operations
- purges - Buffer the actual physical deletes that occur in the background
- all - Buffer inserts, deletes and purges.
- none - Don't buffer any operations.
innodb_adaptive_flushing
Default: 1 (on)
If set to 1, the server will dynamically adjust the flush rate of dirty pages in the InnoDB buffer pool. This assists to reduce brief bursts of I/O activity.
innodb_flush_method
Linux Default: fsync
Defines the method used to flush data to the InnoDB data files and log files, which can affect I/O throughput. This variable is only configurable on Unix and Linux systems.
5.6 Values
fsync- use thefsync()system call to flush both the data and log files. fsync is the default setting.littlesync- don't useosync- don't useO_DSYNC- InnoDB usesO_SYNCto open and flush the log files, andfsync()to flush the data files. InnoDB does not useO_DSYNCdirectly because there have been problems with it on many varieties of Unix.O_DIRECT- InnoDB usesO_DIRECTto open the data files, and uses fsync() to flush both the data and log files.O_DIRECT_NO_FSYNC- InnoDB usesO_DIRECTduring flushing I/O, but skips thefsync()system call afterwards. This setting is suitable for some types of file systems but not others. For example, it is not suitable for XFS. If you are not sure whether the file system you use requires an fsync(), for example to preserve all file metadata, useO_DIRECTinstead. This option was introduced in MySQL 5.6.7.
innodb_adaptive_flushing_method
5.6. 5.6 Has a completely different system and you should refer to innodb_flush_method
Default: 1 (estimate)
Determines the method of flushing dirty blocks from the InnoDB buffer pool.
If set to native or 0, the original InnoDB method is used. The maximum checkpoint age is determined by the total length of all transaction log files. When the checkpoint age reaches the maximum checkpoint age, blocks are flushed. This can cause lag if there are many updates per second and many blocks with an almost identical age need to be flushed.
If set to estimate or 1, the default, the oldest modified age will be compared with the maximum age capacity. If it's more than 1/4 of this age, blocks are flushed every second. The number of blocks flushed is determined by the number of modified blocks, the LSN progress speed and the average age of all modified blocks. It's therefore independent of the innodb_io_capacity for the 1-second loop, but not entirely so for the 10-second loop.
If set to keep_average or 2, designed specifically for SSD cards, a shorter loop cycle is used in an attempt to keep the I/O rate constant. Removed in MariaDB 10.0/XtraDB 5.6 and replaced with InnoDB flushing method from MySQL 5.6.
Was renamed to [innodb_flush_method](#innodb_flush_method ) in 5.6.
sync_binlog
Default: 0
If the value of this variable is greater than 0, the MySQL server synchronizes its binary log to disk (using fdatasync()) after every sync_binlog writes to the binary log. There is one write to the binary log per statement if autocommit is enabled, and one write per transaction otherwise. The default value of sync_binlog is 0, which does no synchronizing to disk. In this case, the server relies on the operating system to flush the binary log's contents from time to time as for any other file.
A value of 1 is the safest choice because in the event of a crash you lose at most one statement or transaction from the binary log. However, it is also the slowest choice (unless the disk has a battery-backed cache, which makes synchronization very fast).
MyISAM Specific
key_buffer_size
Default: 8388608 (8 MiB)
Also known as the key cache, this is the size of the buffer used for index blocks, which are shared by all threads. Innodb's equivalent for this is the innodb_buffer_pool_size which controls the amount of memory for caching indexes and data.
innodb_log_buffer_size
Default: 1 MiB
Minimum: 256 KiB
The MySQL InnoDB log buffer allows transactions to run without having to write the log to disk before the transactions commit. Sensible values range from 1 MB to 8 MB. The default is 1 MB. The minimum value is 256 kB. A setting of 8M is often big enough for most database needs, but some setups with 20 to 30 MB have been seen.
A large log buffer allows large transactions to run without a need to write the log to disk before the transactions commit. Thus, if you have big transactions, making the log buffer larger saves disk I/O.
Run the following query to find out how many times your buffer has been too small:
SHOW GLOBAL STATUS LIKE 'innodb_log_waits';
innodb_flush_log_at_trx_commit
Default: 1
Controls the balance between strict ACID compliance for commit operations, and higher performance that is possible when commit-related I/O operations are rearranged and done in batches. You can achieve better performance by changing the default value, but then you can lose up to a second of transactions in a crash.
The default value of 1 is required for full ACID compliance. With this value, the contents of the InnoDB log buffer are written out to the log file at each transaction commit and the log file is flushed to disk.
With a value of 0, the contents of the InnoDB log buffer are written to the log file approximately once per second and the log file is flushed to disk. No writes from the log buffer to the log file are performed at transaction commit. Once-per-second flushing is not 100% guaranteed to happen every second, due to process scheduling issues. Because the flush to disk operation only occurs approximately once per second, you can lose up to a second of transactions with any mysqld process crash.
With a value of 2, the contents of the InnoDB log buffer are written to the log file after each transaction commit and the log file is flushed to disk approximately once per second. Once-per-second flushing is not 100% guaranteed to happen every second, due to process scheduling issues. Because the flush to disk operation only occurs approximately once per second, you can lose up to a second of transactions in an operating system crash or a power outage.
For the greatest possible durability and consistency in a replication setup using InnoDB with transactions, use [innodb_flush_log_at_trx_commit](#innodb_flush_log_at_trx_commit)=1 and [sync_binlog](#sync_binlog)=1 in your master server my.cnf file.
Memory Allocation Types
Static
The following variables allocate a chunk of memory upon initialization and never change in size.
Dyanmically Allocated Any Number of Times
The following variables dynamically allocate the full amount of memory and can do so several times. Some may do it per query/connection, others may do it per table so be sure to keep their size to a minimum.
join_buffer_size- allocated once per joined tableread_buffer_sizesort_buffer_size- can be allocated multiple times in a single complicated query
Variables Directly Related to Write I/O
sync_binloginnodb_flush_log_at_trx_commitinnodb_io_capacityinnodb_adaptive_flushinginnodb_log_buffer_size
References
- MySQL Docs - How MySQL Uses Memory
- PosiDev Blog - Tunning MySQL InnoDB performance
- Stack Overflow -What is the InnoDB equivalent of MyISAM's
key_buffer_size? - Percona - MySQL server memory usage troubleshooting tips
- Optimizing for InnoDB Tables
First published: 16th August 2018
