MySQL Server Optimization

There are three methods for MySQL tuning to speed up the running of MySQL servers. The efficiency is from low to high: replacing faulty hardware. Optimize the settings of the MySQL process. Optimize the query. Replacing faulty hardware is usually our first consideration, mainly because the database occupies a large amount of resources. However, this solution is only

There are three methods for MySQL tuning to speed up the running of MySQL servers. The efficiency is from low to high: replacing faulty hardware. Optimize the settings of the MySQL process. Optimize the query. Replacing faulty hardware is usually our first consideration, mainly because the database occupies a large amount of resources. However, this solution is only

MySQL Optimization

There are three ways to speed up the running of the MySQL server:

1. Replace faulty hardware.
2. Optimize the settings of the MySQL process.
3. Optimize the query.

Replacing faulty hardware is usually our first consideration, mainly because the database occupies a large amount of resources. However, this solution is limited to this. In fact, you can usually double the speed of the central processor (CPU) or disk, or increase the memory by 4 to 8 times.

The second method ismysqld. Optimizing this process means allocating the memory appropriately and enablingmysqldUnderstand what type of load will be supported. Speed up disk operation is not as fast as reducing the number of disk accesses required. Similarly, ensuring that the MySQL process operates correctly means that it spends more time on service queries than processing background tasks (such as processing temporary disk tables or opening and closing files). PairmysqldTuning is the focus of this article.

The best way is to make sure that the query has been optimized. This means that an appropriate index is applied to the table, and the query is written in a way that fully utilizes the MySQL function. Although this article does not include the content of the query optimization (many books have already discussed this topic), it will configuremysqldTo report queries that may require optimization.

Although these tasks have been assigned an order, pay attention to the hardware andmysqldTo optimize the query. The machine speed is too slow. I have seen a very fast machine that fails due to heavy load when running well-designed queries, becausemysqldIt is occupied by a large number of busy jobs and cannot be queried by the Service.

Slow query of records

On an SQL Server, data tables are stored on disks. Indexes provide a method for the server to search for specific data rows in a table instead of the entire table. To search for the entire table, it is called a table scan. Generally, you may only want to obtain a subset of the data in the table. Therefore, full table scan will waste a lot of disk I/O, and thus a lot of time. When data must be connected, this problem is more complicated, because multi-row data at both ends of the connection must be compared.

Of course, table scanning does not always cause problems. Sometimes reading the entire table is more effective than selecting a part of the data from it (the query planner in the server process is used to make these decisions ). If the index is very inefficient or cannot be used at all, the query speed will be slowed down and the problem will become more significant as the server load and table size increase. A query whose execution time exceeds the specified time range is called a slow query.

You can configuremysqldRecord these slow queries to the appropriately named slow query log. The Administrator then checks the log to help them determine which parts of the application need further investigation. Listing 1 shows the configuration that needs to be done in my. cnf to enable slow query logs.

Listing 1. Enabling MySQL slow query logs

[mysqld]; enable the slow query log, default 10 secondslog-slow-queries; log queries taking longer than 5 secondslong_query_time = 5; log queries that don't use indexes even if they take less than long_query_time; MySQL 4.1 and newer onlylog-queries-not-using-indexes

These three settings are used together to record queries that have been executed for more than 5 seconds and that have not used an index. Please note thatlog-queries-not-using-indexesWarning: You must use MySQL 4.1 or later. Slow query logs are saved in the MySQL data directory named hostname-slow.log. If you want to use a different name or path, you can use it in my. cnf.log-slow-queries = /new/path/to/fileTo achieve this goal.

It is best to read slow query logs throughmysqldumpslowCommand. Specify the log file path to view the sorted list of slow queries, and display the number of times they appear in the log file. A very useful feature ismysqldumpslowBefore comparing the results, any data specified by the user is deleted, so different calls to the same query are counted as one; this can help you find the query that requires the most workload.

Cache queries

Many LAMP applications rely heavily on databases, but repeatedly execute the same query. Each time a query is executed, the database must perform the same job-analyze the query, determine how to execute the query, load the information from the disk, and then return the result to the client. MySQL has a feature called query cache, which stores the query results (which will be used later) in memory. In many cases, this will greatly improve the performance. However, the problem is that the query cache is disabled by default.

Setquery_cache_size = 32MTo/etc/my. conf, you can enable the 32 MB query cache.

Monitor query Cache

After enabling the query cache, it is important to understand whether it is effectively used. MySQL has several variables that can be viewed and used to understand the cache conditions. Listing 2 shows the cache status.

List 2. display the statistics of the query Cache

mysql> SHOW STATUS LIKE 'qcache%';+-------------------------+------------+| Variable_name           | Value      |+-------------------------+------------+| Qcache_free_blocks      | 5216       || Qcache_free_memory      | 14640664   || Qcache_hits             | 2581646882 || Qcache_inserts          | 360210964  || Qcache_lowmem_prunes    | 281680433  || Qcache_not_cached       | 79740667   || Qcache_queries_in_cache | 16927      || Qcache_total_blocks     | 47042      |+-------------------------+------------+8 rows in set (0.00 sec)

The explanations of these items are shown in table 1.

Table 1. MySQL query cache Variables

Variable name	Description
Qcache_free_blocks	The number of adjacent memory blocks in the cache. A large number of fragments may exist.FLUSH QUERY CACHEThe fragments in the cache are sorted to get a free block.
Qcache_free_memory	Idle memory in the cache.
Qcache_hits	Each time the query hits the cache, it increases.
Qcache_inserts	Each time a query is inserted, it increases. The number of hits divided by the number of inserts is the ratio of not to the middle, and the value minus 1 is the hit rate. In the above example, about 87% of queries are hit in the cache.
Qcache_lowmem_prunes	The cache is out of memory and must be cleaned up to provide more space for queries. It would be better to look at this number for a long time; if this number continues to grow, it may indicate that the fragmentation is very serious, or the memory is very small. (The abovefree_blocksAndfree_memoryCan tell you which situation ).
Qcache_not_cached	The number of queries that are not suitable for caching, usually because these queries are notSELECTStatement.
Qcache_queries_in_cache	The number of queries (and responses) cached currently.
Qcache_total_blocks	The number of cached blocks.

Normally, you can see the difference when the variables are displayed several seconds later, which helps determine whether the cache is being effectively used. RunFLUSH STATUSYou can reset some counters. This is very helpful if the server has been running for a while.

The idea of using a very large query cache to cache everything is very attractive. BecausemysqldThe cache must be maintained. For example, when the memory becomes low, the server may be in trouble when trying to manage the cache. As a rule, ifFLUSH QUERY CACHEIf it takes a long time, the cache is too large.

Force limit

You canmysqldTo ensure that the system load does not cause resource depletion. Listing 3 shows some important resource-related settings in my. cnf.

Listing 3. MySQL resource settings

set-variable=max_connections=500set-variable=wait_timeout=10max_connect_errors = 100

The maximum number of connections is managed in the first row. In ApacheMaxClientsSimilarly, the idea is to ensure that only the connections allowed by the service are established. To determine the maximum number of connections currently established on the server, runSHOW STATUS LIKE 'max_used_connections'.

2nd rowsmysqldTerminate all connections that have been idle for more than 10 seconds. In LAMP applications, the time spent connecting to the database is usually the time that the Web server takes to process requests. Sometimes, if the load is too heavy, the connection will be suspended and will occupy the connection tablespace. If multiple interactive users or persistent connections to the database are used, it is not advisable to set this value to a lower value!

The last line is a secure method. If a host has problems connecting to the server and does not give up after many retries, the host will be lockedFLUSH HOSTSBefore running. By default, 10 failures are enough to cause the lock. Changing this value to 100 will give the server enough time to recover from the problem. If a connection cannot be established after 100 retries, using a higher value will not help much, and it may not be able to connect at all.

Buffer and Cache

MySQL supports more than 100 Adjustable settings, but fortunately, a few can meet most of the needs. You can useSHOW STATUSCommand to view the status variables, from which you can determinemysqldWhether the operation meets our expectations. The memory allocated to the buffer and cache cannot exceed the existing memory in the system. Therefore, tuning usually requires some compromise.

MySQL Adjustable settings can be applied to the entiremysqldProcess can also be applied to a single client session.

Server Settings

Each table can be represented as a file on the disk. It must be opened first and then read. To speed up the process of reading data from a file,mysqldThese opened files are cached. the maximum number of opened files is determinedtable_cache. Listing 4 shows how to display activities related to opening a table.

Listing 4. Show the activity of opening a table

mysql> SHOW STATUS LIKE 'open%tables';+---------------+-------+| Variable_name | Value |+---------------+-------+| Open_tables   | 5000  || Opened_tables | 195   |+---------------+-------+2 rows in set (0.00 sec)

Listing 4 shows that 5,000 tables are currently opened, and 195 tables need to be opened, because no available file descriptor is available in the cache (because the statistics have been cleared before, therefore, there may be 5,000 open records in 195 open tables ). IfOpened_tablesWith re-runningSHOW STATUSThe quick increase of the command indicates that the cache hit rate is not enough. IfOpen_tablesRatiotable_cacheIf the setting is much smaller, it indicates that the value is too large (but it is not a bad thing to increase the space ). For exampletable_cache = 5000You can adjust the table cache.

Similar to the table cache, a thread also has a cache.mysqldA thread is generated as needed when the connection is received. On a busy server with fast connection changes, caching threads for later use can speed up the initial connection.

Listing 5 shows how to determine whether enough threads are cached.

Listing 5. display thread usage statistics

mysql> SHOW STATUS LIKE 'threads%';+-------------------+--------+| Variable_name     | Value  |+-------------------+--------+| Threads_cached    | 27     || Threads_connected | 15     || Threads_created   | 838610 || Threads_running   | 3      |+-------------------+--------+4 rows in set (0.00 sec)

The important value here isThreads_created, Each timemysqldThis value is added when a new thread needs to be created. If this number is executed consecutivelySHOW STATUSTo increase the thread cache. For example, you can usethread_cache = 40To achieve this goal.

The keyword buffer stores the index block of the MyISAM table. Ideally, requests for these blocks should come from memory, not from disks. Listing 6 shows how to determine how many blocks are read from the disk and how many are read from the memory.

Listing 6. Determining keyword Efficiency

mysql> show status like '%key_read%';+-------------------+-----------+| Variable_name     | Value     |+-------------------+-----------+| Key_read_requests | 163554268 || Key_reads         | 98247     |+-------------------+-----------+2 rows in set (0.00 sec)

Key_readsThe number of requests that hit the disk,Key_read_requestsIs the total number. The number of read requests that hit the disk divided by the total number of read requests is the ratio of not hit-in this example, each 1,000 requests, about 0.6 do not hit the memory. If the number of disks hit by each 1,000 requests exceeds 1, you should consider increasing the keyword buffer. For example,key_buffer = 384MThe buffer is set to 384 MB.

Temporary tables can be used in more advanced queries, where data is being processed (for exampleGROUP BYStatement), must be saved to the temporary table; ideally, create a temporary table in the memory. However, if the temporary table is too large, it needs to be written to the disk. Listing 7 shows the statistics related to creating a temporary table.

Listing 7. determine the use of temporary tables

mysql> SHOW STATUS LIKE 'created_tmp%';+-------------------------+-------+| Variable_name           | Value |+-------------------------+-------+| Created_tmp_disk_tables | 30660 || Created_tmp_files       | 2     || Created_tmp_tables      | 32912 |+-------------------------+-------+3 rows in set (0.00 sec)

Each time a temporary table is used, it will increase.Created_tmp_tables; Disk-based tables will also increaseCreated_tmp_disk_tables. There are no strict rules for this ratio, because it depends on the involved query. Long observationCreated_tmp_disk_tablesThe ratio of the created disk table is displayed. You can determine the setting efficiency.tmp_table_sizeAndmax_heap_table_sizeYou can control the maximum size of the temporary table. Therefore, make sure that both values are set in my. cnf.

Settings for each session

The following settings apply to each session. Exercise caution when setting these numbers because these options indicate a large amount of memory when multiplied by the number of possible connections! You can use the code to modify the numbers in a session, or modify these settings for all sessions in my. cnf.

When MySQL needs to sort data, it will allocate a sort buffer when reading data from the disk to store the data rows. If the data to be sorted is too large, the data must be saved to the temporary files on the disk and sorted again. Ifsort_merge_passesThe status variable is large, which indicates the disk activity. Listing 8 lists some status counters related to sorting.

Listing 8. Displaying sorting statistics

mysql> SHOW STATUS LIKE "sort%";+-------------------+---------+| Variable_name     | Value   |+-------------------+---------+| Sort_merge_passes | 1       || Sort_range        | 79192   || Sort_rows         | 2066532 || Sort_scan         | 44006   |+-------------------+---------+4 rows in set (0.00 sec)

Ifsort_merge_passesVery large, it means you need to pay attentionsort_buffer_size. For example,sort_buffer_size = 4MSet the sorting buffer to 4 MB.

MySQL also allocates some memory to read the table. Ideally, the Index provides enough information to read only the required rows, but sometimes the query (poor design or data nature) needs to read a large amount of data in the table. To understand this behavior, you need to know how manySELECTStatement, and the number of times the data in the next row in the table needs to be read (instead of directly accessing through the index ). The command to implement this function is shown in listing 9.

Listing 9. Determining the table scan rate

mysql> SHOW STATUS LIKE "com_select";+---------------+--------+| Variable_name | Value  |+---------------+--------+| Com_select    | 318243 |+---------------+--------+1 row in set (0.00 sec)mysql> SHOW STATUS LIKE "handler_read_rnd_next";+-----------------------+-----------+| Variable_name         | Value     |+-----------------------+-----------+| Handler_read_rnd_next | 165959471 |+-----------------------+-----------+1 row in set (0.00 sec)

Handler_read_rnd_next/Com_selectThe table scan rate is obtained. In this example, the ratio is 521: 1. If the value exceeds 4000, Viewread_buffer_sizeFor exampleread_buffer_size = 4M. If this number exceeds 8 Mb, you should discuss with the developer about tuning these queries!

Three essential tools

Even if you know the specific settings,SHOW STATUSCommands are very useful, but you still need some tools to explainmysqldProvides a large amount of data. I found that three tools are essential. You can find the corresponding links in the references section.

Most system administrators are very familiartopCommand, which provides a continuously updated view of the CPU and memory consumed by the task.mytopPairtopSimulation is performed. It provides a view for all connected clients and their running queries.mytopIt also provides real-time data and historical data about the keyword buffer and query cache efficiency, as well as statistics about running queries. This is a very useful tool that allows you to view the status of the system (for example, within 10 seconds), you can get a view of server health information, and display any connection that causes the problem.

mysqlardIt is a daemon connecting to the MySQL server. It collects data every five minutes and stores the data in a Round Robin Database in the background. A Web page displays the data, such as table Cache Usage, keyword efficiency, connected clients, and usage of temporary tables. AlthoughmytopSnapshots of server health information are provided,mysqlardProvides long-term health information. As a reward,mysqlardUse the information you have collected to provide some suggestions on how to optimize the server.

CollectSHOW STATUSAnother tool for information ismysqlreport. Its reports are far greatermysqlardThis is more complex because you need to analyze every aspect of the server. This is a very good tool for server tuning, because it performs proper calculation of state variables to help determine which problems need to be corrected.