Shulou(Shulou.com)06/01 Report--

MySQL parameter optimization is related to different websites, and their online traffic, visits, posts, network conditions, and machine hardware configuration. Optimization can not be completed at one time, and it takes constant observation and debugging to get the best results.

1) variable of connection request

1 、 max_connections

The maximum number of MySQL connections. If the server has a large number of concurrent connection requests, it is recommended to increase the number of parallel connections. Of course, this is built on the condition that the machine can support it, because the more connections, the MySQL aftertaste that each connection provides a connection buffer, the more memory will be spent, so it is necessary to adjust this value appropriately, not blindly increase the setting.

ERROR 1040:Too mant connetcions errors often occur when the number is too small, and you can determine the size of the value by looking at the number of connections in the current state (the number of connections trying to connect to MySQL, regardless of whether the connection is successful or not) through the mysql > show status like 'connections'; wildcard.

Maximum number of connections in show variadles like 'max_connections'

Show variables like 'max_used_connection' corresponding number of connections

Max_used_connection/max_connections*100% (ideal value is about 85%)

If max_used_connections and max_connections are the same, then the max_ connections value is set too low or exceeds the server's load limit, and less than 10% is set too high.

2 、 back_log

The number of connections that MySQL can hold temporarily. When the main MySQL thread gets a lot of connection requests in a very short period of time, it works. If the connection data of MySQL reaches max_connections, new requests will be stored in the stack waiting for a connection to release resources, that is, back_log. If the number of waiting connections exceeds back_log, connection resources will not be accepted.

3. Wait_timeout and interative_timeout

Wait_timeout: refers to the number of seconds that MySQL needs to wait before closing a non-interactive connection.

Interative_timeout: refers to the number of seconds to wait before closing an interactive connection.

Impact on performanc

Wait_timeout

(1) if the setting is too small, the connection closes quickly so that some persistent connections do not work.

(2) if the setting is too large, it is easy to open the connection for too long, in show processlist, you can see too many connections in sleep status, resulting in too many connections error.

(3) generally, wait_timeuot is expected to be as low as possible.

The setting of interative_timeout will have little effect on your web application.

2) buffer variable

Global buffering

4 、 key_buffer_size

Key_buffer_size specifies the size of the index buffer, and he 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 key_read%' 100 and 1 key_read%' is better. (the above state values can be obtained using the ratio.)

Probability of missing the cache:

Key_cache_miss_rate = key_reads/key_read_requests*100%

Key_buffer_size only works on MAISAM tables.

How to adjust the key_buffer_size value

The default configuration is 8388608 (8m), and the host has 4G memory that can be tuned to 268435456 (256m).

5. Query_cache_size (query cache referred to as QC)

Using the query cache, MySQL stores the query results in a buffer and reads the results directly from the buffer for the same select statement (case sensitive) in the future.

If a SQL query starts with select, the MySQL server will try to use query caching for it.

Note: as long as the difference between two SQL statements is even one character (for example, different case: one more space, etc.), then the two SQL will use different cache.

You can know whether the setting of query_cache_size is reasonable through show ststus like 'Qcache%'.

Qcache_free_blocks: the number of adjacent memory blocks in the cache. If the value appears too large, there is more memory fragmentation in Query Cache.

Note: when a table is updated, the cache block associated with it will be free. But the block may still exist in the queue, except at the end of the queue. You can use the flush query cache statement to clear the free blocks.

The amount of memory currently remaining in the Qcache_free_memory:Query Cache. Through this parameter, we can accurately observe whether the Query Cache memory size in the current system is enough, whether it needs to be increased or too much.

Qcache_hits: indicates how many times the cache has been hit. We can mainly use this value to verify that our query can be cached. The larger the number, the better the cache effect.

Qcache_inserts: indicates the number of misses and inserts, which means that the new SQL request is not found in the cache and has to perform query processing. After executing the query processing, the result insert is brought to the query cache. The more times this happens, the less query caching is applied, and the effect is not ideal.

Qcache_lowmen_prunes: how many Query are cleared out of Query Cache because of insufficient memory. Through the combination of Qcache_lowmem_prunes and Qcache_free_memory, we can know more clearly whether the memory size of Query Cache in our system is really enough, and whether Query is swapped out very frequently because of insufficient memory. This number is best seen over a long period of time, and if it is growing, it means that the fragmentation may be very severe, or there may be very little memory.

Qcache_queries_in_cache: the number of Query of cache in the current Query Cache

Qcache_total_blocks: the number of block in the current Query Cache

Query the server's configuration about query_cache

Explanation of each field:

Query_cache_limit: queries beyond this size will not be cached

Query_cache_min_res_unit: the minimum size of the cache block. The configuration of query_cache_min_res_unit is a double-edged sword. The default is 4KB. Setting a large value is good for big data query, but if you query small data queries, it is easy to cause memory fragmentation and waste.

Query_cache_size: query cache size (Note: the minimum unit of QC storage is 1024byte, so if you set a value that is not a multiple of 1024. This value is rounded to a multiple of 1024 closest to the current value. )

Query_cache_type: cache type, which determines what kind of query to cache. Note that this value cannot be set casually and must be set to a number. Optional values and instructions are as follows:

0:OFF is equivalent to disabling

1:ON will cache all results unless your select statement uses SQL_NO_CACHE to disable query caching

2:DENAND caches only the queries specified by SQL_CACHE in the select statement that need to be cached.

Query_cache_wlock_invalidate: when other clients are 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 before reading the table to get the result.

Query cache fragmentation rate: Qcache_free_block/Qcache_total_block*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 less than 25%, it means that the query_cache_size setting is too large and can be reduced appropriately: the query cache utilization is more than 80% and Qcache_lowmem_prunes > 50

It means that query_cache_size may be a little small, or there are too many fragments.

Query cache hit ratio: Qcache_hits/ (Qcache_hits+Qcache_inserts) * 100%

Limitations of Query Cache

A) external query SQL in all subqueries cannot be Cache:

B) Query in pairrocedurefunction and trigger cannot be Cache

C) Query containing many other functions that may get different results each time you execute cannot be Cache

6 、 max_connect_errors:

Is a security-related counter value in MySQL, which is responsible for preventing too many failed clients from breaking the password violently. When the number of times is exceeded, the MySQL server will prohibit host connection requests until the mysql server restarts or clears the relevant information of the host through the flush hotos command. (it doesn't have much to do with performance)

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

Sort_buffer_size is a connection-level parameter that allocates the set memory at once when each connection (session) needs to use this buffer for the first time.

Sort_buffer_size: not the bigger the better, because it is a connection-level parameter, too large settings + high concurrency may deplete the system's memory resources. For example, 500 connections will consume 500*sort_buffer_size (2m) = 1G

8 、 max_allowed_packet=32M

Limit the packet size accepted by server according to the configuration file.

9 、 join_buffer_size=2M

It is used to indicate the size of the associated cache. Like sort_buffer_size, the allocated memory corresponding to this parameter is exclusive to each connection.

10 、 thread_cache_size=300

Server thread cache, this value indicates that the number of threads stored in the cache can be reused. When disconnected, the client thread will be placed in the cache to respond to the next client rather than destroy (if the cache number does not reach the limit), if the thread is re-requested, the request will be read from the cache, and if the cache is empty or a new request, the thread will be re-requested Then the thread will be recreated, and if there are many new threads, increasing this value can improve system performance. You can see the effect of this variable by comparing the variables of Connections and Threads_created states.

The setting rules are as follows: 1G memory is configured as 8g memory, 2G memory is 16. The threads that the server processes this client will be cached to respond to the next customer rather than being destroyed (provided that the number of caches does not reach the limit)

Threads_cached: represents how many threads are free in the thread cache at the moment.

Threads_connected: represents the number of connections currently established, because a connection requires one thread, so it can also be regarded as the number of threads currently in use.

Threads_created: represents the number of threads created during the last service startup. If the Threads_created value is found to be too high, it means that the MySQL server has been creating threads, which consumes resources. You can increase the thread_cache_ size value in the configuration file appropriately.

Threads_running: represents the number of currently active (non-sleeping) threads. It does not represent the number of threads in use, sometimes the connection is established, but the connection is in the sleep state.

3) configure several variables of Innodb

11 、 innodb_buffer_pool_size

For innodb tables, innodb_buffer_pool_size is the same as key_buffer_size is for MyISAM tables. Innodb uses this parameter to specify the size of memory to buffer data and indexes. You can set this value to up to 80% of physical memory.

12 、 innodb_flush_log_at_trx_commit

It mainly controls the time when innodb writes the data in log buffer to the log file and flush disk.

The actual test shows that this value has a great influence on the speed of inserting data. It takes only two seconds to insert 10000 records when set to 2, one second when set to 0, and 229 seconds when set to 1. Therefore, the MySQL manual also recommends that inserts be merged into a single transaction as much as possible, which can greatly increase speed.

13 、 innodb_thread_concurrency=0

This parameter is used to set the number of concurrency of innodb threads. The default value is 0, which means there is no limit. If you want to set it, it will be the same as the number of CPU cores of the server or twice the number of cores of CPU.

14 、 innodb_log_buffer_size

This parameter determines the amount of memory used by the log file, in M units. Larger buffers can improve performance, and for larger transactions, you can increase the cache size.

15 、 innodb_log_file_size=50M

This parameter determines the size of the data log file, in M units, and larger settings can improve performance.

16 、 innodb_log_files_in_group=3

To improve performance, MySQL can write log files to multiple files in a circular manner. Recommended setting to 3

17 、 read_buffer_size=1M

MySQL read buffer size. The request for a sequential scan of the table will be allocated to a read buffer MySQL will allocate a memory buffer for him

18 、 read_rnd_buffer_size=16M

The random read (query operation) buffer size of the MySQL. When rows are read in any order (for example, in sort order), they are assigned to a random all buffer. When sorting a query, MySQL scans the buffer first to avoid disk search and improve 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 consumption.

Note: sequential reading is the row data that can be read sequentially according to the leaf node data of the index. Random reading means that it is generally necessary to find its lane for data according to the primary key in the secondary index leaf node, but the data side where the secondary index and the primary key are located is different, so the access mode is random.

19 、 bulk_insert_buffer_size=64M

Bulk insert data cache size, which can effectively improve insertion efficiency. The default is 8m.

20 、 binary log

Binlog_cache_size=2M / / the memory allocated for each session is used to store the cache of binary logs during the transaction, improving the efficiency of recording bin-log.

Max_binlog_cache_size=8M / / represents the maximum amount of cache memory that binlog can use

Max_binlog_size=512M / / specifies the size of the binlog log file. You cannot set a variable greater than 1G or less than 4096 bytes. The default value is 1G. When importing large-capacity sql files, it is recommended to close, sql_log_bin, otherwise the hard disk can not bear, and it is recommended to delete it regularly.

Expire_logs_days=7 / / defines when mysql clears the expired log.

Summary of parameters:

[mysqld] basedir = / usr/local/mysql datadir = / usr/local/mysql/data server_id = 1 socket = / usr/local/mysql/mysql.sock log-error = / usr/local/mysql/data/mysqld.err slow_query_log = 1 slow_query_log_file=/usr/local/mysql/data/slow-query.log long_query_time = 1 log-queries-not-using-indexes max_connections = 1024 back_log = 1024 wait_timeout = 60 interactive_timeout = 7200 key _ buffer_size = 256m query_cache_size = 256m query_cache_type = 1 query_cache_limit = 50m max_connect_errors = 20 sort_buffer_size = 2m max_allowed_packet = 32m join_buffer_size = 2m thread_cache_size = 200 innodb_buffer_pool_size = 2048m innodb_flush_log_at_trx_commit = 1 innodb_log_buffer_size = 32m innodb_log_file_size = 128m innodb_log_files_in_group = 3 log-bin=/usr/ Local/mysql/data/mysqlbin binlog_cache_size = 2m max_binlog_cache_size = 8m max_binlog_size = 512m expire_logs_days = 7 read_buffer_size = 1m read_rnd_buffer_size = 16m bulk_insert_buffer_size = 64m # 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. # join_buffer_size = 128m # sort_buffer_size = 2m # read_rnd_buffer_size = 2m sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

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.