MySQL Configuration Variables

来源:互联网 时间:1970-01-01

This is an overview of MySQL variables that can be tweaked in the my.cnf file for MySQL 5.5 or 5.6.

I will use MiB to denote values, but if you want to specify this in the configuration files, you would use 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.


Default:131072 (128 KiB)

Range: 8200 - 2147479552

Each request that performs a sequential scan of a table allocates

Used for all egines with:

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.


Default:262144 (256 KiB)

Value Range: 8200 - 2147483647Buffer for non-sequential reading of rows to prevent disk seeks (used when performing sort etc)


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.

One of the first things I do when configuring a database is increase this to a much higher value as it is a frequently occurring and avoidable issue in a lot of applications.


Default:2097144 (2M)

Range: 32768 - 18446744073709551615 (64bit Linux)Non engine-specific each session that needs to do a sort will allocate a buffer of this size.

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_sizethreads 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. he query cache needs a minimum size of about 40KB to allocate its structures. THis will use memory EVEN IF query cachetype 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

If you set this to 1or 2, I recommend that you enable 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: ONand OFF


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_sizevalue might be helpful in improving performance, because it can reduce the need for the server to perform memory allocation during query execution operations.


Default:33554432 (32MiB)

The maximum size of internal in-memory temporary tables (although if max_heap_tablesize is less, that will be the limit).


Default:16777216 (16MiB)

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

User created memory tables are not limited by tmp_table_size, however non-user created temporary tables may be limited by this.

InnoDB Specific 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.

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_sizeFROM (SELECT SUM(index_length) KBS FROM information_schema.tables WHEREengine='InnoDB') A,(SELECT 3 pw) B; 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.

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_sizewhich controls the amount of memory for caching indexes and data.