Setup and optimization of MySQL server

This article will describe the various MySQL settings, mainly those that affect performance. For convenience, all variables are divided into sections (basic settings, restrictions, settings streams, query caching, timings, buffers, InnoDB). First, we clarify the names of some variables that have changed in version 4 of MySQL, and the network continues to meet both old and new variants of names, which causes questions.



So, in version 4 a number of variables appeared ending _size . This applies to the thread_cache_size variable and variables from the Buffers section. And the variable read_buffer_size to version 4 was called record_buffer . Also, the variable skip_external_locking from the Basic Settings section prior to version 4 was called skip_locking .

Variables are divided into two main categories: variables with values ​​and variable flags. Variables with values ​​are written in the configuration file as variable = value , and variable flags are simply specified. You also probably noticed that in some cases the names of variables use " - ", and in some " _ ". Variables with a hyphen are the starting options of the server and cannot be changed while the server is running (using SET); Underlined variables are server options and can be changed on the fly. If we are talking about a “state variable” or it is recommended to monitor the value of a variable whose name is recorded as Variable_Name , then you should run the SHOW STATUS LIKE "Variable_Name" query to get the value of this variable, or look at the status tab in phpMyAdmin, where you will also see comments by the value of this variable.

And now we will be engaged in the description of variables and their possible values.

Basic settings

• low-priority-updates - this option reduces the priority of INSERT / UPDATE operations compared to SELECT. Actually, if the data is important to read faster than write faster.
• skip-external-locking - the option is installed by default, starting with version 4. Tells the MySQL server not to use external locks when working with the database. External locks are necessary in situations where several servers are working with the same data files, i.e. have the same datadir , which is not used in practice.
• skip-name-resolve - do not determine domain names for IP addresses of connecting clients. At the same time, user permissions need to be configured not to hosts, but to IP addresses (with the exception of localhost). If you connect to the server only from the local machine, it does not matter much. For external connections, speed up the connection setup.
• skip-networking - do not use the network, i.e. do not handle TCP / IP connections at all. Communication with the server will occur exclusively through the socket. Recommended if you do not have software that uses only TCP / IP to communicate with the server.

Restrictions

• bind-address is the interface that the server will listen to. For security reasons, it is recommended to install here 127.0.0.1 if you are not using external connections to the server.
• max_allowed_packet - the maximum size of data that can be transferred in one request. Should be increased if you encounter the error "Packet too large".
• max_connections - the maximum number of parallel connections to the server. Increase it if you encounter the “Too many connections” problem.
• max_join_size - disables SELECT statements that are expected to analyze more than the specified number of rows or more than the specified number of searches on the disk. Used to protect against curve queries that attempt to read millions of rows. The default value is over 4 billion, so you probably want to reduce it significantly.
• max_sort_length - indicates how many bytes from the beginning of the BLOB or TEXT fields to use when sorting. The default value is 1024, if you fear incorrectly designed tables or queries, you should reduce it.

Streaming settings

• thread_cache_size - indicates the number of cached threads. After processing the request, the server will not terminate the stream, but will place it in the cache if the number of threads found in the cache is less than the specified value. The default value is 0, increase it to 8 or immediately to 16. If the value of the state variable Threads_Created , then you should increase the thread_cache_size .
• thread_concurrency - relevant only for Solaris / SunOS, contrary to what is written on the network. “Prompts” to the system how many threads to start at the same time by calling the thr_setconcurrency function. The recommended value is double or triple the number of processor cores.

Query caching

• query_cache_limit - the maximum size of the cached query.
• query_cache_min_res_unit - the minimum size of the block stored in the cache.
• query_cache_size - cache size. 0 disables the use of the cache. To select the optimal value, you need to monitor the state variable Qcache_lowmem_prunes and ensure that its value increases slightly. You also need to remember that unnecessarily large cache will create an unnecessary load.
• query_cache_type - (OFF, DEMAND, ON). OFF disables caching, DEMAND - caching will be performed only if there is SQL_CACHE directive in the request, ON enables caching.
• query_cache_wlock_invalidate - determines whether data will be taken from the cache if the table to which they belong is locked for reading.

The query cache can be thought of as a hash array, the keys of which are queries, and the values ​​are the results of queries. In addition to the results, MySQL caches a list of tables, a selection of which is cached. If any of the tables, the sample from which is in the cache, are changed, then MySQL removes such samples from the cache. Also, MySQL does not cache queries whose results may change.

When you start MySQL allocates a block of memory size in query_cache_size . When the query is executed, as soon as the first result lines are received, the server starts caching them: it allocates a block of memory equal to query_cache_min_res_unit in the cache, writes the result of the selection to it. If not all of the sample fit into the block, the server allocates the next block, and so on. At the moment of writing, MySQL does not know about the size of the resulting sample, so if the sample size recorded in the cache is larger than query_cache_limit , then the recording stops and the space occupied is released, therefore, if you know in advance that the result will be large, you should execute it with the directive SQL_NO_CACHE .

Timings

• interactive_timeout - time in seconds during which the server waits for activity from the interactive connection (using the CLIENT_INTERACTIVE flag) before closing it.
• log_slow_queries - tells the server to log long (“slow”) requests (running for a long_query_time ). The full file name is passed as a value (for example, /var/log/slow_queries ).
• long_query_time - if the request is executed longer than the specified time (in seconds), then it will be considered “slow”.
• net_read_timeout - time in seconds during which the server will wait for data to be received before the connection is terminated. If the server does not serve clients with very slow or unstable channels, then 15 seconds will suffice here.
• net_write_timeout - time in seconds during which the server will wait for data to be received before the connection is terminated. If the server does not serve clients with very slow or unstable channels, then 15 seconds will suffice here.
• wait_timeout is the time in seconds that the server waits for the connection to be active before it terminates. In general, 30 seconds will be enough.

Buffers

All buffers have a common feature - if, due to the large buffer size, the data goes to the paging file, then the buffer will do more harm than good. Therefore, always be guided by the amount of physical RAM available to you.

• key_buffer_size is the size of the buffer allocated for indexes and accessible to all threads. A very important setting that affects performance. The default value of 8 MB, it is definitely worth increasing. 15-30% of the total RAM is recommended, but there is no point in installing more than the total size of all .MYI files. Watch for the Key_reads and Key_read_requests state variables, the Key_reads/Key_read_requests should be as small as possible (<0.01). If this ratio is large, then the buffer size should be increased.
• max_heap_table_size is the maximum allowable size of a table stored in memory (of type MEMORY). The default value is 16 MB, if you do not use MEMORY tables, then set this value to tmp_table_size .
• myisam_sort_buffer_size is the size of the buffer allocated by MyISAM for sorting indexes for REPAIR TABLE or for creating indexes for CREATE INDEX, ALTER TABLE . The default value is 8 MB, it should be increased up to 30-40% of RAM. The performance gain will be correspondingly only when performing the above requests.
• net_buffer_length — The amount of memory allocated for the connection buffer and for the result buffer for each stream. The connection buffer will be the specified size and the result buffer will be the same size, i.e. each stream will be allocated a double net_buffer_length size. The specified value is initial and, if necessary, buffers will increase up to max_allowed_packet . The default size is 16 KB. In the case of limited memory or using only small requests, the value can be reduced. In the case of constant use of large queries and sufficient memory, the value should be increased to the expected average size of the query.
• read_buffer_size — each stream, when sequentially scanning tables, allocates a specified amount of memory for each table. As the tests show, this value should not be particularly increased. The default size is 128 KB, try to increase it to 256 KB, and then to 512 KB and watch the speed of execution of queries like SELECT COUNT(*) FROM table WHERE expr LIKE "a%"; on large tables.
• read_rnd_buffer_size is relevant for queries with " ORDER BY ", i.e. for queries whose results must be sorted and that refer to a table that has indices. The default value is 256 KB, increase it to 1 MB or higher if memory allows. Note that the specified memory value is also allocated to each stream.
• sort_buffer_size - each thread performing sorting operations ( ORDER BY ) or grouping ( GROUP BY ) allocates a buffer of the specified size. The default value is 2 MB, if you use the specified types of requests and if memory allows, the value should be increased. The large value of the state variable Sort_merge_passes indicates the need to increase the sort_buffer_size . It is also worth checking the speed of execution of queries like SELECT * FROM table ORDER BY name DESC on large tables, possibly increasing the buffer will only slow down the work (in some tests this is so).
• table_cache ( table_open_cache since version 5.1.3) - the number of cached open tables for all threads. Opening a table file can be quite a resource-intensive operation, so it’s best to keep open tables in the cache. It should be noted that each entry in this cache uses a system descriptor, so it may be necessary to increase the limit on the number of descriptors ( ulimit ). The default value is 64, it is best to increase it to the total number of tables, if their number is within acceptable limits. The state variable Opened_tables allows you to keep track of the number of tables that are open to bypassing the cache; preferably, its value is as low as possible.
• tmp_table_size is the maximum amount of memory allocated for temporary tables created by MySQL for its internal needs. This value is also limited by the max_heap_table_size variable, so the minimum value from max_heap_table_size and tmp_table_size will be selected in the end, and the other temporary tables will be created on disk. The default value depends on the system, try setting it to 32 MB and watching the state variable Created_tmp_disk_tables , its value should be as small as possible.

The values ​​in the configuration file are specified in bytes, respectively, kilobytes and megabytes must be converted to bytes.

')

InnoDB

• innodb_additional_mem_pool_size is the size of memory allocated by InnoDB to store various internal structures. If InnoDB is not enough of this memory, then the memory will be requested from the OS and a warning will be written to the MySQL error log.
• innodb_buffer_pool_size is the size of memory allocated by InnoDB for storing both indexes and data. Meaning - the more the better. You can increase up to the total size of all InnoDB tables or up to 80% of RAM, whichever is less.
• innodb_flush_log_at_trx_commit - has three valid values: 0, 1, 2. If the value is 0 , the log is flushed to disk once per second, regardless of the transactions that occur. If the value is 1 , the log is flushed to the disk during each transaction. If the value is 2 , the log is written at every transaction, but it is never flushed to the disk, leaving it on the OS’s conscience. The default is 1, which is the most reliable setting, but not the fastest. In general, you can safely use 2, data can be lost only in case of OS crash and only in a few seconds (depends on OS settings). 0 is the fastest mode, but data can be lost both when the OS crashes and when the MySQL server crashes (however, the data is only 1-2 seconds).
• innodb_log_buffer_size is the size of the log buffer. The default value is 1 MB. It is worth increasing it if you know that there will be a large number of InnoDB transactions or if the value of the state variable Innodb_log_waits increases. You hardly have to increase it above 8 MB.
• innodb_log_file_size - the maximum size of a single log file. When this size is reached, InnoDB will create a new file. The default value of 5 MB, increasing the size will improve performance, but will increase data recovery time. Set this value in the range of 32 MB - 512 MB, depending on the size of the server (estimating it subjectively).

It is also convenient to use phpMyAdmin to monitor server performance, the Status and Variables tabs are of interest. Additionally, phpMyAdmin gives tips on tuning certain variables depending on the server operation parameters.

In preparing the article, in addition to official documentation and own head, the following materials were used:

• http://blogfreebsd.com/freebsd/optimizaciya-mysql.html
• http://habrahabr.ru/blogs/mysql/41166/
• http://www.mysqlperformanceblog.com/
• http://mysyslog.ru/posts/223

I will read with interest the comments, comments and additions.

Additional discussion of the article can be found here .