MySQL parameter optimization

MySQL parameter optimization
MySQL parameter optimization for different sites, and their online volume, traffic, number of posts, network conditions, as well as the machine hardware configuration has a relationship, optimization can not be done at once, need constant observation and debugging, it is possible to get the best results.

1) The variable of the connection request
1, Max_connections
MySQL maximum number of connections, if the server's concurrent connection request is large, it is recommended to increase the value of the number of parallel connections, of course, this is based on the machine can support the case, because if the number of connections, MySQL aftertaste each connection provides a connection buffer, the more memory will be overhead, so to adjust the value appropriately, You cannot blindly increase the set value.

The 
  value is too small and often error 1040:too mant connetcions errors can be obtained by mysql>show status like '    Connections '; wildcard characters to see the number of connections in the current state (the number of connections attempting to connect to MySQL, regardless of whether the connection is successful), to determine the size of the value. Show variadles like ' max_connections ' Maximum number of connections show variables like ' max_used_connection ' corresponding connections Max_used_connection/max_co nnections*100% (ideal value is approximately equal to 85%) if Max_used_connections and Max_connections are the same, then the Max_connections value is set too low or exceeds the server load limit, less than 10% The settings are too large. 2. Back_log the number of connections that MySQL can temporarily hold. When the primary MySQL thread gets very many connection requests in a very short period of time, he will work. If the MySQL connection data reaches max_connections, the new request will be present in the stack, waiting for a connection to release the resource, the number of the stack is Back_log, and if the number of waiting connections exceeds back_log, the connection resource will not be accepted.    3, Wait_timeout and interative_timeout wait_timeout: refers to the number of seconds that MySQL waits before it closes a non-interactive connection.    Interative_timeout: Refers to the number of seconds to wait before closing an interactive connection. Impact on Performance wait_timeout (1) If the settings are too small, then the connection is closed very quickly, so that some persistent connections do not work (2) If the settings are too large to cause the connection to open too long, at show Processlist, you can see too        Multiple sleep states, resulting in too many connections errors. (3) generally want to wait_timeuot as low as possible interative_timeout settings will have little impact on your Web application  

2) buffer variable
Global buffer
4, key_buffer_size
Key_buffer_size Specifies the size of the index buffer, and he determines the processing speed of the index, especially the speed of the index read. By checking the status values key_read_requests and Key_reads, you can see if the key_buffer_size settings are reasonable. The proportional key_reads/key_read_requests should be as low as possible, at least 1:100,1:1000 better (the above status values can be obtained using the show status like ' key_read% ')

    Probability of missing cache: Key_cache_miss_rate = key_reads/key_read_requests*100% key_buffer_size only works on Maisam tables. How to adjust the value of key_buffer_size default configuration number when 8388608 (8M), the host has 4G memory can be tuned to 268435456 (256M) 5, query_cache_size (query cache abbreviation QC) using the query cache,    MySQL stores the query results in a buffer, and in the future the same SELECT statement (case sensitive) will read the result directly from the buffer.    If a SQL query starts with SELECT, the MySQL server will attempt to use the query cache for it.    NOTE: Two SQL statements, as long as the difference between a character (such as the case is not the same size: more than one space, etc.), then two SQL will use a different cache through show ststus like ' qcache% ' can know query_cache_size set is reasonable Qcache_free_blocks: The number of contiguous memory blocks in the cache.    If the value is too large, it means that there is more memory fragmentation in query cache. Note: When a table is updated, the cache block associated with him will be free. However, the block may still exist in the queue, unless it is at the end of the queue.    You can use the Flush query cache statement to empty the free blocks. The amount of memory currently remaining in the Qcache_free_memory:query Cache.    With this parameter we can accurately observe the current system of the query cache memory size is sufficient, is the need to increase or too much. Qcache_hits: Indicates how many times the cache has been hit. We can mainly use this value to verify the effect that our query can cache.    The larger the number, the more desirable the cache effect. Qcache_inserts: Indicates how many misses were inserted, meaning that the new SQL request was not found in the cache, had to perform query processing, and then executed the query processing to insert the results into the query cache.    The more times this happens, the less the query cache is applied to, and the more undesirable the effect is. Qcache_lowmen_prunes: How many query caches have been cleared out of the query Cache because of insufficient memory, combined with qcache_lowmem_prunes and qcache_free_memory to make it clearerUnderstand that the memory size of query cache in our system is really enough, whether it occurs very frequently because of insufficient memory and query is swapped out.    This number is best seen over a long period of time, and if this number is growing, it means that fragmentation can be very serious, or that there is little memory. Qcache_queries_in_cache: The number of queries to the cache in the current query cache Qcache_total_blocks: The number of blocks in the current query cache querying the server about Query_cache Configure the explanations for each field: Query_cache_limit: Queries that exceed this size will not be cached Query_cache_min_res_unit: The minimum size of the cache block, the Query_cache_min_res_unit configuration is a double-edged sword, the default    is 4KB, setting the value of Big data query is good, but if you query is small data query, it is easy to create 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 value that is a multiple of the nearest current value equal to 1024. Query_cache_type: Cache type, determine what kind of query to cache, note that this value can not be arbitrarily set to a number, optional values and the following description: 0:off equivalent to disabling 1:on will cache all results unless your SELECT statement makes    With Sql_no_cache, the query cache is disabled 2:denand only the queries that need to be cached are cached in the SELECT statement through Sql_cache.    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 in the Read 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% Query Cache utilization below 25% indicates that the query_cache_size settings are too large and can be appropriately reduced: query cache utilization is above 80% and qcache_lowmem_prunes>50 description Query_ Cache_size may be a little bit small, or it's too fragmented. Query Cache Hit rate: qcache_hits/(qcache_hits+qcache_inserts) *100% the limit for the query cache a) external queries in all subqueries SQL cannot The query in P ' rocedure,function and trigger cannot be cache:b by cache C) contains many other functions that may have different results each time the query cannot be Cache6, max_connect_ Errors: is a MySQL security-related counter value, he is responsible for preventing too many attempts to fail the client to prevent brute force password, when more than a specified number of times, the MySQL server will prohibit the host connection request until the MySQL server restarts or through the flush The Hotos command clears information about this host. (Not much in relation to performance) 7, Sort_buffer_size: Each thread that needs to be sorted allocates a buffer of that size.    Increase this value to accelerate the order by or GROUP by Operation Sort_buffer_size is a connection-level parameter that allocates the set of memory once for the first time each connection (session) needs to use this buffer. Sort_buffer_size: The larger the better, because it is a connection-level parameter, too large a setting + high concurrency may deplete the system's memory resources. For example: 500 connections will consume 500*sort_buffer_size (2M) =1g8, max_allowed_packet=32m limit the packet size accepted by the server according to the profile. 9. Join_buffer_size=2m is used to represent the size of the associated cache, and as with Sort_buffer_size, the allocated memory for that parameter is also exclusive to each connection. 10, thread_cache_size=300 server thread cache, this value indicates that the number of threads saved in the cache can be re-used, when disconnected, then the client's thread will be placed in the cache in response to the next customer rather than destroy (if the cache number is not up to the maximum), If the thread is requested again,Then the request will be read from the cache, if the cache is empty or a new request, the thread will be re-requested, then this thread will be re-created, if there are many new threads, increasing this value can improve system performance by comparing connections and Threads_    Created the state of the variable, you can see the role of this variable.    Set the rules as follows: 1G memory is configured to 8,2g memory to 16. The server processing this customer's thread will be cached in response to the next customer instead of being destroyed (provided the cache count has not reached the upper limit) Threads_cached: Represents the current number of idle threads in the thread cache at this moment.    Threads_connected: Represents the number of currently established connections, because a connection requires a thread, so it can also be considered as the number of threads currently being used. Threads_created: Represents the most recent service startup, the number of created threads, if the threads_created value is found to be too large, the MySQL server has been creating threads, which is also more resource-intensive, can be appropriately increased in the configuration file Thread_ Cache_size value threads_running: Represents the number of currently active (non-sleep) threads. Does not represent the number of threads in use, sometimes the connection is established, but the connection is in a sleep state.

3) Configure several variables for InnoDB
11, innodb_buffer_pool_size
for InnoDB tables, innodb_buffer_pool_size is equivalent to Key_buffer_ Size is the same for MyISAM tables. InnoDB uses this parameter to specify the size of memory to buffer data and indexes. The maximum value can be set to 80% of physical memory.

12, Innodb_flush_log_at_trx_commit main control innodb log buffer to write the data to the logfile and flush the disk point in time, the value is 0,1,2. The actual test found that the value of the insertion of data is very large, set to 2 o'clock insert 10,000 records only two seconds, set to 0 only one second, set to 1 o'clock, it takes 229 seconds. Therefore, the MySQL manual also recommends merging inserts into one transaction as much as possible, which can greatly increase the speed. 13, innodb_thread_concurrency=0 This parameter is used to set the number of concurrent InnoDB threads, the default value of 0 means not to be limited, to set the CPU core number of the server is the same or twice times the core of the CPU. 14. Innodb_log_buffer_size This parameter determines the amount of memory used by the log file, in M. A larger buffer can improve performance, and for larger transactions, the cache size may be increased. 15, innodb_log_file_size=50m This parameter determines the size of the data log file, in M, 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. The recommended setting is 317, read_buffer_size=1m MySQL read-in buffer size. A request to sequentially scan a table is assigned to a read-in buffer MySQL allocates a memory buffer of 18, read_rnd_buffer_size=16m MySQL to the random read (query operation) buffer size. When rows are read in any order (for example, in sort order), they are assigned to a random buffer. When you sort a query, MySQL scans the buffer first to avoid disk searches, improve query speed, and, if you need to sort large amounts of data, raise the value appropriately.    However, MySQL will issue this buffer space for each client connection, so you should set this value as appropriate to avoid excessive memory consumption. Note: Sequential reads are the rows of data that can be sequentially read based on the leaf node data of the index. Random reading refers to the general need to look for the primary key in the secondary index leaf node to find the way to the data, and the secondary index and the primary key is located on the same data side, so access is random. 19, bulk_insert_buffer_size=64m Bulk INSERT data cache size, can effectively improve the efficiency of insertion, the default is 8M20, binary log binlog_cache_size=2m//For each session assignedMemory, which is used to store the cache of binary logs during a transaction, improves the efficiency of recording bin-log. Max_binlog_cache_size=8m//Represents the maximum cache memory size that Binlog can use max_binlog_size=512m//Specifies the size of the Binlog log file. You cannot set a variable to be 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, or the hard drive will not carry, and it is recommended to do the deletion regularly. Expire_logs_days=7//Defines the time parameter rollup for MySQL purge expiration log: [mysqld] Basedir =/usr/local/mysql DataDir =/usr/l Ocal/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_tim        E = 1 Log-queries-not-using-indexes max_connections = 1024x768 Back_log = 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 = sort_buffer_size = 2M Max_allowed_packet = 32M Join_buffer_size = 2M Thread_cachE_size = Innodb_buffer_pool_size = 2048M Innodb_flush_log_at_trx_commit = 1 Innodb_log_buffer_siz E = 32M Innodb_log_file_size = 128M Innodb_log_files_in_group = 3 Log-bin=/usr/local/mysql/data/mysql  Bin 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 L Eading # and set to the amount of RAM for the most important data # cache in MySQL.        Start at 70% of all 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 is 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 is 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_sub  Stitution,strict_trans_tables

MySQL parameter optimization