MySQL Overall Performance Tuning

How to configure MySQL server, but considering the different server hardware configuration, the specific application of the differences, those articles can only be used as a preliminary setting reference, we need to

Configuration optimization, it is good practice that the MySQL server runs stably for a period of time and is optimized based on the "state" of the server.

Note: Parameters can be adjusted by modifying the/etc/my.cnf file and restarting the MySQL implementation. This is a relatively cautious work, you can according to the hardware of your own host (special

Not the memory size) further modifications.

Mysql> show global status;

The MySQL server can be listed to run various status values, in addition, query the MySQL server configuration information statement:

Mysql> Show variables;

One, slow query

Mysql> Show variables like '%slow% ';

+------------------+-------+

| variable_name | Value |

+------------------+-------+

| log_slow_queries | On |

| Slow_launch_time | 2 |

+------------------+-------+

Mysql> show global status like '%slow% ';

+---------------------+-------+

| variable_name | Value |

+---------------------+-------+

| Slow_launch_threads | 0 |

| slow_queries | 4148 |

+---------------------+-------+

In the configuration, the record slow query is opened, the execution time is more than 2 seconds is slow query, the system shows 4,148 slow query, you can analyze the slow query log, find the problematic SQL

Sentence, slow query time should not be set too long, otherwise the meaning is not big, preferably within 5 seconds, if you need a microsecond level of slow query, you can consider to patch MySQL:

Http://www.percona.com/docs/wiki/release:start, remember to find the corresponding version.

Opening the slow query log may have a little impact on system performance, and if your MySQL is master-slave structure, you can consider opening one of the slow query logs from the server so

It can monitor the slow query, and has little effect on system performance.

Second, the number of connections

Often meet "mysql:error 1040:too many connections" situation, one is the traffic is really high, MySQL server can not withstand, this time to test

Consider increasing the spread of read pressure from the server, another scenario is that the max_connections value in the MySQL configuration file is too small:

Mysql> Show variables like ' max_connections ';

+-----------------+-------+

| variable_name | Value |

+-----------------+-------+

| max_connections | 256 |

+-----------------+-------+

The maximum number of connections for this MySQL server is 256, then query the maximum number of connections the server responds to:

Mysql> show global status like ' Max_used_connections ';

MySQL server in the past the maximum number of connections is 245, did not reach the maximum number of server connections 256, there should be no 1040 error, the more ideal setting is:

Max_used_connections/max_connections * 100%≈85%

The maximum number of connections is about 85% of the maximum number of connections, if the ratio is found below 10%, the maximum number of MySQL server connections is set too high.

Third, Key_buffer_size

Key_buffer_size is one of the most influential parameters for MyISAM table performance, and the following is a configuration of the primary storage engine server with MyISAM:

Mysql> Show variables like ' key_buffer_size ';

+-----------------+------------+

| variable_name | Value |

+-----------------+------------+

| Key_buffer_size | 536870912 |

+-----------------+------------+

Allocated 512MB of memory to Key_buffer_size, let's look at the usage of key_buffer_size:

Mysql> show global status like ' key_read% ';

+------------------------+-------------+

| variable_name | Value |

+------------------------+-------------+

| key_read_requests | 27813678764 |

| Key_reads | 6798830 |

+------------------------+-------------+

A total of 27,813,678,764 index read requests, with 6,798,830 requests not found in memory directly from the hard disk to read the index, calculate the probability of index misses cache:

Key_cache_miss_rate = key_reads/key_read_requests * 100%

For example, the above data, Key_cache_miss_rate is 0.0244%, 4,000 index read requests have a direct read hard disk, has been very bt, key_cache_miss_rate

Under 0.1% is very good (every 1000 requests have a direct read hard disk), if key_cache_miss_rate under 0.01%, Key_buffer_size allocated too much, can

appropriately reduced.

The MySQL server also provides the key_blocks_* parameter:

Mysql> show global status like ' key_blocks_u% ';

+------------------------+-------------+

| variable_name | Value |

+------------------------+-------------+

| key_blocks_unused | 0 |

| key_blocks_used | 413543 |

+------------------------+-------------+

Key_blocks_unused represents the number of unused cache clusters (blocks), key_blocks_used indicates the maximum number of blocks ever used, such as this server, all the slow

It is used, either by adding key_buffer_size, or by the transition index, and the cache is fully occupied. More Ideal settings:

Key_blocks_used/(key_blocks_unused + key_blocks_used) * 100%≈80%

Iv. Temporary tables

Mysql> show global status like ' created_tmp% ';

+-------------------------+---------+

| variable_name | Value |

+-------------------------+---------+

| Created_tmp_disk_tables | 21197 |

| Created_tmp_files | 58 |

| Created_tmp_tables | 1771587 |

+-------------------------+---------+

Each time a temporary table is created, created_tmp_tables increases, if the temporary table is created on disk, Created_tmp_disk_tables also increases, created_tmp_files table

Shows the number of temporary file files created by the MySQL service, the ideal configuration is: Created_tmp_disk_tables/created_tmp_tables * 100% <= 25%

For example, the server above created_tmp_disk_tables/created_tmp_tables * 100% = 1.2%, should be quite good. Let's look at the MySQL server for temporary

Configuration of the table:

Mysql> Show variables where variable_name in (' Tmp_table_size ', ' max_heap_table_size ');

+---------------------+-----------+

| variable_name | Value |

+---------------------+-----------+

| Max_heap_table_size | 268435456 |

| Tmp_table_size | 536870912 |

+---------------------+-----------+

Only temporary tables below 256MB can be used for all memory, and the hard disk temporary tables will be available.

V. Open table case

Mysql> show global status like ' open%tables% ';

+---------------+-------+

| variable_name | Value |

+---------------+-------+

| Open_tables | 919 |

| Opened_tables | 1951 |

+---------------+-------+

Open_tables indicates the number of open tables, Opened_tables represents the number of open tables, and if the number of opened_tables is too large, the description is in the configuration Table_cache

(after 5.1.3 This value is called Table_open_cache) the value may be too small, we query the server Table_cache value:

Mysql> Show variables like ' Table_cache ';

+---------------+-------+

| variable_name | Value |

+---------------+-------+

| Table_cache | 2048 |

+---------------+-------+

The more appropriate values are:

　　

Open_tables/opened_tables * 100% >= 85%

Open_tables/table_cache * 100% <= 95%

Vi. use of the process

Mysql> show global status like ' thread% ';

+-------------------+-------+

| variable_name | Value |

+-------------------+-------+

| threads_cached | 46 |

| threads_connected | 2 |

| threads_created | 570 |

| threads_running | 1 |

+-------------------+-------+

If we set the thread_cache_size in the MySQL server configuration file, when the client disconnects, the server processes the client's thread and caches it in response to the

A customer instead of destroying it (provided the cache count is not up to the limit). Threads_created indicates the number of threads created, and if threads_created values are found to be too large, it indicates

MySQL server has been creating threads, which is also a relatively resource-intensive, you can appropriately increase the thread_cache_size value in the configuration file, query server Thread_cache_size configuration

：

Mysql> Show variables like ' thread_cache_size ';

+-------------------+-------+

| variable_name | Value |

+-------------------+-------+

| Thread_cache_size | 64 |

+-------------------+-------+

The server in the example is still quite healthy.

Querying cache (query caches)

Mysql> show global status like ' qcache% ';

+-------------------------+-----------+

| variable_name | Value |

+-------------------------+-----------+

| Qcache_free_blocks | 22756 |

| Qcache_free_memory | 76764704 |

| Qcache_hits | 213028692 |

| Qcache_inserts | 208894227 |

| Qcache_lowmem_prunes | 4010916 |

| qcache_not_cached | 13385031 |

| Qcache_queries_in_cache | 43560 |

| Qcache_total_blocks | 111212 |

+-------------------------+-----------+

MySQL Query cache variable interpretation:

Qcache_free_blocks: The number of contiguous memory blocks in the cache. A large number indicates that there may be fragmentation. FLUSH QUERY Cache organizes the fragments in the buffer to

to a free block.

Qcache_free_memory: Free memory in the cache.

Qcache_hits: Increases each time the query hits the cache

Qcache_inserts: Increases each time a query is inserted. The number of hits divided by the number of inserts is not the ratio.

Qcache_lowmem_prunes: The number of times that the cache is out of memory and must be cleaned up to provide space for more queries. This number is best seen for a long time;

Numbers are growing, it means that fragmentation can be very serious, or that there is little memory. (The above free_blocks and free_memory can tell you which kind of situation)

Qcache_not_cached: The number of queries that are not appropriate for caching, usually because these queries are not a SELECT statement or are using functions such as now ().

Qcache_queries_in_cache: The number of queries (and responses) that are currently cached.

Qcache_total_blocks: The number of blocks in the cache.

Let's look at the server's configuration for Query_cache:

Mysql> Show variables like ' query_cache% ';

+------------------------------+-----------+

| variable_name | Value |

+------------------------------+-----------+

| Query_cache_limit | 2097152 |

| Query_cache_min_res_unit | 4096 |

| Query_cache_size | 203423744 |

| Query_cache_type | On |

| Query_cache_wlock_invalidate | OFF |

+------------------------------+-----------+

Explanation of each field:

Query_cache_limit: Queries that exceed this size will not be cached

Query_cache_min_res_unit: Minimum size of cache block

Query_cache_size: Query Cache Size

Query_cache_type: Cache type, determines what queries are cached, example indicates that select Sql_no_cache queries are not cached

Query_cache_wlock_invalidate: When there are other clients that are writing to the MyISAM table, if the query is to be returned with the cache result or

Wait for the write operation to complete before reading the table to get results.

Query_cache_min_res_unit configuration is a "double-edged sword", the default is 4KB, set the value of large data query is good, but if your query is small data query

, it is easy to create memory fragmentation and waste.

Query Cache Fragmentation Rate = Qcache_free_blocks/qcache_total_blocks * 100%

If the query cache fragmentation rate exceeds 20%, you can use flush query cache to defragment the cache, or try to reduce query_cache_min_res_unit if your query

is a small amount of data.

Query Cache utilization = (query_cache_size-qcache_free_memory)/query_cache_size * 100%

The query cache utilization under 25% indicates that the query_cache_size setting is too large to be appropriately reduced; the query cache utilization is above 80% and

Qcache_lowmem_prunes > 50 said query_cache_size may be a little small, or too many fragments.

Query Cache Hit Ratio = (qcache_hits-qcache_inserts)/qcache_hits * 100%

Sample server query Cache Fragmentation rate = 20.46%, query cache utilization = 62.26%, query cache Hit ratio = 1.94%, bad hit ratio, possibly write operations more frequently

, and there may be some fragments.

Viii. Sorting usage

Mysql> show global status like ' sort% ';

+-------------------+------------+

| variable_name | Value |

+-------------------+------------+

| sort_merge_passes | 29 |

| Sort_range | 37432840 |

| Sort_rows | 9178691532 |

| Sort_scan | 1860569 |

+-------------------+------------+

The sort_merge_passes consists of two steps. MySQL first tries to sort in memory, using the memory size determined by the system variable sort_buffer_size, if it's

Not enough to read all the records into memory, MySQL will put the results of each in-memory sorted into a temporary file, after MySQL found all the records, and then the temporary

The records in the file are sorted once. This re-ordering will increase the sort_merge_passes. In fact, MySQL will use another temporary file to store the results of the re-ordering, so the

It is often seen that sort_merge_passes increases by twice times the number of temporary files built. Because of the use of temporary files, so the speed may be slower, increase

Sort_buffer_size will reduce the number of sort_merge_passes and create temporary files. But blindly increasing sort_buffer_size does not necessarily improve speed, see

How fast can I sort data with MySQL? (quoted Http://qroom.blogspot.com/2007/09/mysql-select-sort.html, seemingly by the wall)

Also, increasing the value of Read_rnd_buffer_size (3.2.3 is record_rnd_buffer_size) is a bit of a benefit for sorting operations.

Ix. Number of open files (open_files)

Mysql> show global status like ' Open_files ';

+---------------+-------+

| variable_name | Value |

+---------------+-------+

| Open_files | 1410 |

+---------------+-------+

Mysql> Show variables like ' open_files_limit ';

+------------------+-------+

| variable_name | Value |

+------------------+-------+

| Open_files_limit | 4590 |

+------------------+-------+

More appropriate setting: Open_files/open_files_limit * 100% <= 75%

Ten, the table lock situation

Mysql> show global status like ' table_locks% ';

+-----------------------+-----------+

| variable_name | Value |

+-----------------------+-----------+

| Table_locks_immediate | 490206328 |

| table_locks_waited | 2084912 |

+-----------------------+-----------+

Table_locks_immediate indicates that the number of table locks is released immediately,

Table_locks_waited indicates the number of table locks to wait, if table_locks_immediate/

table_locks_waited > 5000, it is best to use the InnoDB engine, because InnoDB is a row lock and MyISAM is a table lock, for high concurrent write application InnoDB effect will be better. Shown

The server table_locks_immediate/table_locks_waited = 235,myisam In the example is sufficient.

Xi. Table Scan Situation

Mysql> show global status like ' handler_read% ';

+-----------------------+-------------+

| variable_name | Value |

+-----------------------+-------------+

| Handler_read_first | 5803750 |

| Handler_read_key | 6049319850 |

| Handler_read_next | 94440908210 |

| Handler_read_prev | 34822001724 |

| Handler_read_rnd | 405482605 |

| Handler_read_rnd_next | 18912877839 |

+-----------------------+-------------+

Handler_delete the number of times a row was requested to be deleted from a table.

The number of times the Handler_read_first request reads the first row in the table.

The Handler_read_key requests a number based on the key read line.

The number of times a handler_read_next request reads into a row based on one key.

The number of times a HANDLER_READ_RND request reads into a row based on a fixed position.

Handler_update the number of times a row in the table was requested to be updated.

The number of times the Handler_write request inserts a row into the table.

Handler_read_prev The number of requests to read the previous row of data in the order in which they are indexed. This variable value is usedbyastatement ofthe Select FieldList ORDER by fields desc type

Handler_read_rnd_next The number of requests to read the next row of data in the data file. Generally, this value cannot be too high because this means that the query operation does not use the index and must be read from the data file

Call up the query request completed by the server

Number:

Mysql> show global status like ' Com_select ';

+---------------+-----------+

| variable_name | Value |

+---------------+-----------+

| Com_select | 222693559 |

+---------------+-----------+

Calculate table Scan Rate:

Table Scan rate = Handler_read_rnd_next/com_select

12. Skip Domain Name Resolution

Set the parameters inside the/ETC/MY.CNF:

Skip-name-resolve

Reason:

Ysql receives the connection request, obtains the client's IP, in order to better match the permission record in the Mysql.user (some are defined by hostname).

If the MySQL server has a DNS server set up and the client IP does not have a corresponding hostname on the DNS, then this process is slow and causes the connection to wait.

After adding skip-name-resolve, I skipped a process.

If the table scan rate exceeds 4000, indicating that too many table scans have been performed, it is likely that the index has not been built, and that increasing the read_buffer_size value will have some benefits, but it is best not to exceed

8MB.

This article is from the "Linux" blog, so be sure to keep this source http://chenjisong.blog.51cto.com/7858025/1659328

MySQL Overall Performance Tuning