Shulou(Shulou.com)05/31 Report--

This article mainly introduces "the arrangement of configuration parameters commonly used in Mysql". In daily operation, I believe that many people have doubts about the arrangement of configuration parameters commonly used in Mysql. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful for you to answer the doubts about "arrangement of configuration parameters commonly used in Mysql". Next, please follow the editor to study!

Cat / etc/my.cnf

# For advice on how to change settings please see

# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

# * * DO NOT EDIT THIS FILE. It's a template which will be copied to the

# * * default location during install, and will be replaced if you

# * * upgrade to a newer version of MySQL.

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data

# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.

# innodb_buffer_pool_size = 128m

# Remove leading # to turn on a very important data integrity option: logging

# changes to the binary log between backups.

# log_bin

# These are commonly set, remove the # and set as required.

# basedir =.

# datadir =.

# port =.

# server_id =.

# socket =.

# Remove leading # to set options mainly useful for reporting servers.

# The server defaults are faster for transactions and fast SELECTs.

# Adjust sizes as needed, experiment to find the optimal values.

# # #

# innodb

User=mysql

Innodb_buffer_pool_size=6G

Innodb_log_file_size=4G

Innodb_log_buffer_size = 8m

Innodb_flush_log_at_trx_commit=2

Innodb_file_per_table=1

Innodb_file_io_threads=4

Innodb_flush_method=O_DIRECT

Innodb_io_capacity=2000

Innodb_io_capacity_max=6000

Innodb_lru_scan_depth=2000

Innodb_thread_concurrency = 0

Innodb_additional_mem_pool_size=16M

Innodb_autoinc_lock_mode = 2

# # #

# Binary log/replication

Log-bin

Sync_binlog=1

Sync_relay_log=1

Relay-log-info-repository=TABLE

Master-info-repository=TABLE

Expire_logs_days=7

Binlog_format=ROW

Transaction-isolation=READ-COMMITTED

# # #

# cache

Tmp_table_size=512M

Character-set-server=utf8

Collation-server=utf8_general_ci

Skip-external-locking

Back_log=1024

Key_buffer_size=1024M

Thread_stack=256k

Read_buffer_size=8M

Thread_cache_size=64

Query_cache_size=128M

Max_heap_table_size=256M

Query_cache_type=1

Binlog_cache_size = 2m

Table_open_cache=128

Thread_cache=1024

Thread_concurrency=8

Wait_timeout=30

Join_buffer_size = 1024m

Sort_buffer_size = 8m

Read_rnd_buffer_size = 8m

# # #

# connect

Max-connect-errors=100000

Max-connections=1000

# # #

Explicit_defaults_for_timestamp=true

Sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

# # #

# Binary log/replication (here is mainly the replication function, that is, the master-slave configuration, which is configured in advance, and the master-slave configuration later)

# binary log

Log-bin

# in order to ensure the persistence and consistency of replicated InnoDB transactions on the maximum program

Sync_binlog=1

Sync_relay_log=1

# enable these two items, which can be used to ensure binary and slave server security in the event of a crash

Relay-log-info-repository=TABLE

Master-info-repository=TABLE

# set the time to clear logs

Expire_logs_days=7

# Line copy

Binlog_format=ROW

# there are four mysql database transaction isolation levels (READ UNCOMMITTED,READ COMMITTED,REPEATABLE READ,SERIALIZABLE)

Transaction-isolation=READ-COMMITTED

# cache

# maximum value of internal memory temporary table

Tmp_table_size=512M

Character-set-server=utf8

Collation-server=utf8_general_ci

# that is, skip external locking

Skip-external-locking

# number of connections that can be temporarily stored by MySQL (based on actual settings)

Back_log=1024

# specify the size of the index buffer, which only works on the MyISAM table. It doesn't matter if you write it here

Key_buffer_size=1024M

# this instruction limits the stack size for each database thread

Thread_stack=256k

# when a query constantly scans a table, MySQL allocates a memory buffer to it

Read_buffer_size=8M

# Thread caching

Thread_cache_size=64

# query cache size

Query_cache_size=128M

# the maximum value of the temporary table of internal memory, which should be allocated by each thread

Max_heap_table_size=256M

# put the query results in the query cache

Query_cache_type=1

# represents the cache size that holds binary log SQL statements during a transaction

Binlog_cache_size = 2m

# it is also the cache table size

Table_open_cache=128

# caching thread

Thread_cache=1024

# it is recommended to set it to 2 times the number of server CPU cores

Thread_concurrency=8

Wait_timeout=30

# size of buffer for table and table join

Join_buffer_size = 1024m

# is a connection-level parameter. When each connection needs to use this buffer for the first time, it allocates the set memory at once.

Sort_buffer_size=8M

# Random read data buffer using memory

Read_rnd_buffer_size = 8m

# connect

# is a security-related counter value in MySQL, which is responsible for preventing too many failed clients from breaking passwords violently

Max-connect-errors=100000

# number of connections

Max-connections=1000

# enable query caching

Explicit_defaults_for_timestamp=true

# mysql server can work in different modes and apply these modes in different ways for different clients

Sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

The main variables that have a great impact on performance optimization are listed below, mainly divided into connection request variables and buffer variables.

1. Variables for connection requests:

1) max_connections

The maximum number of connections for MySQL, increasing this value increases the number of file descriptors required by mysqld. If the server has a large number of concurrent connection requests, it is recommended to increase this value to increase the number of parallel connections. Of course, this is based on the case that the machine can support, because if the number of connections is more, between MySQL will provide a connection buffer for each connection, the more memory will be spent, so adjust this value appropriately, not blindly increase the setting value.

ERROR 1040: Too many connections errors often occur when the value is too small. You can check the number of connections in the current state through the 'conn%' wildcard to determine the size of this value.

Maximum number of connections in show variables like 'max_connections'

Number of connections to show status like 'max_used_connections' response

As follows:

Mysql > show variables like 'max_connections'

+-+

| | Variable_name | Value |

+-+

| | max_connections | 256 |

+-+

Mysql > show status like 'max%connections'

+-+

| | Variable_name | Value |

+-+

| | max_used_connections | 256 |

+-+

Max_used_connections / max_connections * 100% (ideal value ≈ 85%)

If the max_used_connections is the same as max_connections, then the max_connections setting is too low or exceeds the server load limit, and less than 10% is too large.

2) back_log

The number of connections that MySQL can hold temporarily. This works when the main MySQL thread gets very many connection requests in a very short period of time. If the connection data of MySQL reaches max_connections, the new request will be stored in the stack to wait for a connection to release resources, the number of the stack is back_log, if the number of waiting connections exceeds back_log, connection resources will not be granted.

The back_ log value indicates how many requests can be stored on the stack in a short period of time before MySQL temporarily stops answering new requests. Only if you expect to have many connections in a short period of time, you need to increase it, in other words, this value is the size of the listening queue for incoming TCP/IP connections.

When looking at the list of your host processes (mysql > show full processlist), you can find a large number of 264084 | unauthenticated user | xxx.xxx.xxx.xxx | Connect | NULL | login | NULL processes to be connected, you should increase the value of back_log.

The default value is 50, can be tuned to 128, and the system setting range is an integer less than 512.

3) interactive_timeout

The number of seconds an interactive connection waits for action before being shut down by the server. An interactive customer is defined as a customer who uses the CLIENT_INTERACTIVE option for mysql_real_connect ().

The default value is 28800 and can be tuned to 7200.

two。 Buffer variable

Global buffering:

4) key_buffer_size

Key_buffer_size specifies the size of the index buffer, which determines the speed of index processing, especially the speed of index reads. By checking the status values Key_read_requests and Key_reads, you can see whether the key_buffer_size setting is reasonable. The key_reads / key_read_requests ratio should be as low as possible, at least 1 SHOW STATUS LIKE 100 and 1 SHOW STATUS LIKE 1000 is better. (the above state values can be obtained using the state value).

Key_buffer_size only works on MyISAM tables. Use this value even if you do not use the MyISAM table, but the internal temporary disk table is the MyISAM table. You can use the check status value created_tmp_disk_tables to learn more.

Examples are as follows:

Mysql > show variables like 'key_buffer_size'

+-+

| | Variable_name | Value |

+-+

| | key_buffer_size | 536870912 | |

+-+-+

Key_buffer_size is 512MB. Let's take a 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 | |

+-+ +

There are 27813678764 index read requests, of which 6798830 are not found in memory to read the index directly from the hard disk. Calculate the probability that the index misses the cache:

Key_cache_miss_rate = Key_reads / Key_read_requests * 100%, it is better to set it to around 1max 1000.

The default configuration value is 8388600 (8m). The host has 4GB memory and can be tuned to 268435456 (256MB).

5) query_cache_size

Using query buffering, MySQL stores the query results in a buffer, and in the future, for the same SELECT statement (case sensitive), the results will be read directly from the buffer.

You can see whether the query_cache_size setting is reasonable by checking the status value Qcache_*, (the above status values can be obtained using SHOW STATUS LIKE 'Qcache%'). If the value of Qcache_lowmem_prunes is very large, it means that there is often insufficient buffering; if the value of Qcache_hits is also very large, it means that query buffering is used very frequently, and you need to increase the buffer size; if the value of Qcache_hits is not large, it means that your query repetition rate is very low, in this case, using query buffering will affect the efficiency, then you can consider not using query buffering. In addition, adding SQL_NO_CACHE to the SELECT statement makes it clear that query buffering is not used.

Other parameters related to query buffering are query_cache_type, query_cache_limit, and query_cache_min_res_unit.

Query_cache_type specifies whether to use query buffering, which can be set to 0, 1, 2, which is a SESSION-level variable.

Query_cache_limit specifies the size of the buffer that can be used by a single query, which defaults to 1m.

Query_cache_min_res_unit, introduced after version 4.1, specifies the minimum unit of buffer space allocated, which defaults to 4K. Check the status value Qcache_free_blocks, which, if very large, indicates that there is a lot of fragmentation in the buffer, which indicates that the query results are relatively small, and you need to reduce query_cache_min_res_unit at this point.

Examples are as follows:

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 > 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 |

+-+ +

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%

If the query cache utilization is below 25%, it means that the query_cache_size setting is too large and can be reduced appropriately; if the query cache utilization is above 80% and the Qcache_lowmem_prunes is more than 50, the query_cache_size may be a little small, or there may be too many fragments.

Query cache hit ratio = (Qcache_hits-Qcache_inserts) / Qcache_hits * 100%

Example server query cache fragmentation rate = 20.46%, query cache utilization = 62.26%, query cache hit rate = 1.94%, the hit rate is very poor, maybe write operations are more frequent, and there may be some fragments.

Buffer per connection

6) record_buffer_size

Each thread that performs a sequential scan allocates a buffer of this size to each table it scans. If you do a lot of sequential scans, you may want to increase this value.

The default value is 131072 (128K), which can be changed to 16773120 (16m)

7) read_rnd_buffer_size

Random read buffer size. When rows are read in any order (for example, in sort order), a random read cache is allocated. When sorting a query, MySQL will first scan the buffer to avoid disk search and improve the query speed. If you need to sort a large amount of data, you can increase the value appropriately. However, MySQL allocates this buffer space for each customer connection, so you should try to set this value appropriately to avoid excessive memory overhang.

It can generally be set to 16m

8) sort_buffer_size

Each thread that needs to sort allocates a buffer of that size. Increase this value to speed up ORDER BY or GROUP BY operations.

The default value is 2097144 (2m), which can be changed to 16777208 (16m).

9) join_buffer_size

Buffer size that can be used by federated query operations

Record_buffer_size,read_rnd_buffer_size,sort_buffer_size,join_buffer_size is exclusive to each thread, that is, if there are 100 thread connections, the occupancy is 16M*100

10) table_cache

Size of the table cache. Whenever MySQL accesses a table, if there is space in the table buffer, the table is opened and placed in it, allowing faster access to the table contents. By checking the state values of peak time Open_tables and Opened_tables, you can decide whether you need to increase the value of table_cache. If you find that open_tables equals table_cache and opened_tables is growing, you need to increase the value of table_cache (the above state values can be obtained using SHOW STATUS LIKE 'Open%tables'). Note that table_cache cannot be set to a large value blindly. If set too high, it may result in insufficient file descriptors, resulting in unstable performance or connection failure.

One gigabyte memory machine, the recommended value is 128mur256. Servers with memory around 4GB this parameter can be set to 256m or 384m.

11) max_heap_table_size

The size of the memory table (memory table) that the user can create. This value is used to calculate the maximum row value of the memory table. This variable supports dynamic change, i.e. set @ max_heap_table_size=#

This variable, along with tmp_table_size, limits the size of internal memory tables. If the size of an internal heap (stacking) table exceeds tmp_table_size,MySQL, you can automatically change the in-memory heap table to a hard disk-based MyISAM table as needed.

12) tmp_table_size

Increase the size of a temporary table by setting the tmp_table_size option, such as a temporary table generated by advanced GROUP BY operations. If you increase this value, MySQL will also increase the size of the heap table, which can improve the speed of the join query. It is recommended to optimize the query as far as possible to ensure that the temporary table generated during the query process is in memory, so as to avoid the generation of MyISAM table based on hard disk due to the temporary table being too large.

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 size exceeds tmp_table_size, a temporary table is created on disk, and Created_tmp_disk_tables is also increased. Created_tmp_files represents the number of temporary files created by the MySQL service. The ideal configuration is:

Created_tmp_disk_tables / Created_tmp_tables * 100% show variables

1. 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 | |

+-+ +

Slow query is opened in the configuration. If the execution time is more than 2 seconds, it is slow query. The system shows that there are 4148 slow queries. You can analyze the slow query log and find out the problematic SQL statements. Slow query time should not be set too long, otherwise it is not meaningful, preferably within 5 seconds. If you need microsecond-level slow query, you can consider patching MySQL: http://www.percona.com/docs/wiki/release:start. Remember to find the corresponding version.

Opening the slow log may have a slight impact on system performance. If your MySQL is a master-slave structure, consider opening the slow log of one of the slave servers, so that you can monitor slow queries with little impact on system performance.

Second, the number of connections

We often encounter the situation of "MySQL: ERROR 1040: Too manyconnections". One is that the traffic is really high and the MySQL server cannot resist it. At this time, it is necessary to consider increasing the decentralized reading pressure from the server, and the other 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, and then query the maximum number of connections that the server responded to:

Mysql > show global status like 'Max_used_connections'

+-+ +

| | Variable_name | Value |

+-+ +

| | Max_used_connections | 245 |

+-+ +

The maximum number of connections to the MySQL server in the past is 245.It does not reach the server connection limit of 256. there should be no 1040 error. The ideal setting is:

Max_used_connections / max_connections * 100% ≈ 85%

The maximum number of connections accounts for about 85% of the upper limit. If the proportion is found to be less than 10%, the maximum number of MySQL server connections is set too high.

III. Key_buffer_size

Key_buffer_size is a parameter that has the greatest impact on the performance of the MyISAM table. The following is a configuration of a server with MyISAM as the main storage engine:

Mysql > show variables like 'key_buffer_size'

+-+ +

| | Variable_name | Value |

+-+ +

| | key_buffer_size | 536870912 | |

+-+ +

After allocating 512MB memory to key_buffer_size, let's take a look at key_buffer_size usage:

Mysql > show global status like 'key_read%'

+-+ +

| | Variable_name | Value |

+-+ +

| | Key_read_requests | 27813678764 |

| | Key_reads | 6798830 | |

+-+ +

There are 27813678764 index read requests, of which 6798830 are not found in memory to read the index directly from the hard disk. Calculate the probability that the index misses the cache:

Key_cache_miss_rate = Key_reads / Key_read_requests * 100%

Compared with the above data, the key_cache_miss_rate is 0.0244% per 4,000 index read requests before there is one direct read hard disk, which is already very BT. The key_cache_miss_rate below 0.1% is good (there is one direct read hard disk for every 1000 requests). If the key_cache_miss_rate is less than 0.01%, the key_buffer_size allocation is too much, which can be reduced appropriately.

The MySQL server also provides key_blocks_* parameters:

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), and Key_blocks_used indicates the maximum number of blocks ever used, such as this server, where all the cache is used, either adding key_buffer_size or transitional indexes, filling up the cache. Ideal settings:

Key_blocks_used / (Key_blocks_unused + Key_blocks_used) * 100% ≈ 80%

IV. Temporary watch

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 a temporary table is created on disk, Created_tmp_disk_tables also increases. Created_tmp_files represents the number of temporary files created by the MySQL service. The ideal configuration is:

Created_tmp_disk_tables / Created_tmp_tables * 100% 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 stored in memory, and hard disk temporary tables will be used if they are exceeded.

V. Open Table situation

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

+-+ +

| | Variable_name | Value |

+-+ +

| | Open_tables | 919 | |

| | Opened_tables | 1951 | |

+-+ +

Open_tables indicates the number of open tables, and Opened_tables indicates the number of opened tables. If the number of Opened_tables is too large, the value of table_cache (called table_open_cache after 5.1.3) in the configuration may be too small. Let's 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% show global status like 'Thread%'

+-+ +

| | Variable_name | Value |

+-+ +

| | Threads_cached | 46 | |

| | Threads_connected | 2 | |

| | Threads_created | 570 |

| | Threads_running | 1 | |

+-if we set thread_cache_size in the MySQL server configuration file, when the client is disconnected, the server's thread dealing with this client will be cached to respond to the next client rather than destroyed (provided that the cache number does not reach the limit). Threads_created indicates the number of threads created. If the Threads_created value is too high, it means that the MySQL server has been creating threads, which is also resource-consuming. You can appropriately increase the value of thread_cache_ size in the configuration file to query the 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 quite healthy.

7. Query cache (query cache)

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 explanation:

Qcache_free_blocks: the number of adjacent memory blocks in the cache. A large number means there may be fragments. FLUSH QUERY CACHE will defragment the cache to get a free block.

Qcache_free_memory: free memory in the cache.

Qcache_hits: increments every time a query hits in the cache

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

Qcache_lowmem_prunes: the number of times the cache runs out of memory and must be cleaned to provide space for more queries. This number is best seen over a long period of time; if it is growing, it means that the fragmentation may be very serious, or there may be very little memory. (the free_blocks and free_memory above can tell you which case it is.)

Qcache_not_cached: the number of queries that are not suitable for caching, usually because they are not SELECT statements or use functions such as now ().

Qcache_queries_in_cache: the number of queries (and responses) currently cached.

Qcache_total_blocks: the number of blocks in the cache. Let's check the server's configuration for query_cache again:

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 larger than this size will not be cached

Query_cache_min_res_unit: minimum size of the cache block

Query_cache_size: query cache size

Query_cache_type: cache type, which determines what kind of query to cache. In the example, select sql_no_cache query is not cached.

Query_cache_wlock_invalidate: when another client is writing to the MyISAM table, if the query is in query cache, whether to return the cache result or wait for the write operation to complete and then read the table to get the result. Query_cache_min_res_unit configuration is a "double-edged sword", the default is 4KB, set a large value for big data query is good, but if your queries are small data queries, it is easy to cause 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 if the query cache utilization is below 25%, it means that the query_cache_size setting is too large and can be reduced appropriately; if the query cache utilization is above 80% and the Qcache_lowmem_prunes is more than 50, the query_cache_size may be a little small, or there are too many fragments.

Query cache hit rate = (Qcache_hits-Qcache_inserts) / Qcache_hits * 100% sample server query cache fragmentation rate = 20.46%, query cache utilization = 62.26%, query cache hit rate = 1.94%, hit rate is very poor, maybe write operations are more frequent, 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 | |

+-+-+ Sort_merge_passes consists of two steps. MySQL will first try to sort in memory, and the memory size used is determined by the system variable Sort_buffer_size. If it is not big enough to read all the records into memory, MySQL will save the results of each sort in memory to a temporary file, and wait for MySQL to find all the records, and then sort the records in the temporary file again. This sort again increases the Sort_merge_passes. In fact, MySQL uses another temporary file to store the re-sorted results, so it is common to see that Sort_merge_passes increases by twice the number of temporary files created. Because temporary files are used, the speed may be slow, and increasing Sort_buffer_size reduces the number of times Sort_merge_passes and temporary files are created. But blindly increasing Sort_buffer_size does not necessarily improve speed.

In addition, increasing the value of read_rnd_buffer_size (3.2.3 is record_rnd_buffer_size) also has a little benefit for sorting operations.

IX. Number of document openings (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% show global status like 'table_locks%'

+-+ +

| | Variable_name | Value |

+-+ +

| | Table_locks_immediate | 490206328 | |

| | Table_locks_waited | 2084912 | |

+-+ Table_locks_immediate indicates the number of table locks to be released immediately, and Table_locks_waited indicates the number of table locks to wait. If Table_locks_immediate / Table_locks_waited > 5000, it is best to use InnoDB engine, because InnoDB is a row lock and MyISAM is a table lock. InnoDB works better for applications with high concurrent writes. The server in the example, Table_locks_immediate / Table_locks_waited = 235 ~ MyISAM, is sufficient.

11. Table scanning

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 |

+-+-+ explanation for each field see the number of query requests completed by the server:

Mysql > show global status like 'com_select'

+-+ +

| | Variable_name | Value |

+-+ +

| | Com_select | 222693559 | |

+-+ calculation table scan rate:

Table scan rate = Handler_read_rnd_next / Com_select if the table scan rate exceeds 4000, it means that too many table scans have been performed, and it is very likely that the index is not well built. Increasing the read_buffer_size value will have some benefits, but it is best not to exceed 8MB. At this point, the study of "sorting out the commonly used configuration parameters of Mysql" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.