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.

Low values often occur error 1040:too mant connetcions errors, which 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 connection number

max_used_connection/max_connections*100% (ideal value 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's load limit, and the setting is too large for less than 10%.

2, Back_log

The number of connections that MySQL can stage. 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 setting is too small, then the connection is closed very quickly, so that some persistent connections do not work

(2) If the setting is too large to cause the connection to open too long, when show processlist, can see too much sleep state connection, resulting in too many connections error.

(3) generally want to wait_timeuot as low as possible

Interative_timeout settings will not have much effect on your Web application

2) Buffer variable

Global buffering

4, Key_buffer_size

KEY_BUFFER_SIZE Specifies the size of the index buffer, 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. 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% ')

The probability that the cache is not hit:

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), host has 4G memory can be tuned to 268435456 (256M)

5, Query_cache_size (query cache referred to as QC)

Using query caching, 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

By show ststus like ' qcache% ' you can tell if Query_cache_size's settings are 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 are cleared out of the query Cache due to insufficient memory, combined with qcache_lowmem_prunes and Qcache_free_memory, Be able to know more clearly whether the memory size of query cache in our system is really enough, whether it is very frequent 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 cache query in the current query cache

Qcache_total_blocks: Number of blocks in the current query cache

Querying the server about Query_cache configuration

Explanation of 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, 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 you are querying 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 the

1:on will cache all results unless your SELECT statement uses Sql_no_cache to disable the query cache

2:denand only caches the query that needs to be cached by Sql_cache in the SELECT statement.

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 queries are small data volumes.

Query Cache Utilization: (query_cache_size-qcache_free_memory)/query_cache_size*100%

The query cache utilization below 25% indicates that the query_cache_size setting is too large to be properly reduced: The query cache utilization is above 80% and qcache_lowmem_prunes>50

That means query_cache_size may be a little bit small, or too much debris.

Query Cache Hit Ratio: qcache_hits/(qcache_hits+qcache_inserts) *100%

Limitations of Query Cache

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

b) query in P ' rocedure,function and trigger cannot be cache

c) A query that contains many other functions that may have different results each time it is executed cannot be deleted by the cache

6, Max_connect_errors: is a MySQL in the security-related counter value, he is responsible for preventing excessive attempts to fail the client to prevent brute-force password situation, when the specified number of times, the MySQL server will prohibit the host connection request, The information about this host is cleared until the MySQL server restarts or through the Flush Hotos command. (does not have much relationship with performance)

7. Sort_buffer_size: Each thread that needs to be sorted allocates a buffer of that size. Increase this value to accelerate order by or GROUP by operations

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) =1g

8, max_allowed_packet=32m

Limits the size of packets accepted by the server based on the profile.

9, join_buffer_size=2m

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 caching, which indicates that the number of threads saved in the cache can be re-used, and when disconnected, the client's thread will be placed in the cache in response to the next customer rather than destroyed (as long as the cache is not capped), and if the thread is requested again, the request will be read from the cache. If the cache is empty or a new request, the thread will be re-requested, then the thread will be re-created, and if there are many new threads, increasing this value can improve system performance by comparing the variables of the connections and threads_created states, You can see the effect 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 how many idle threads are currently 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, the Innodb_buffer_pool_size function is equivalent to key_buffer_size 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

The main control is InnoDB writes the data in the log buffer to the logfile and flush the disk at a point in time, with values of 0,1,2 respectively.

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, with a default value of 0, which is not limited, and is set to the same number of CPU cores as the server or twice times the core number 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 the larger setting can improve performance.

16, Innodb_log_files_in_group=3

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

17, 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 for him

18, read_rnd_buffer_size=16m

MySQL's 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 8M

20, binary Log

BINLOG_CACHE_SIZE=2M//For each session allocated memory, in the transaction process to store the binary log cache, improve the efficiency of recording bin-log.

Max_binlog_cache_size=8m//Indicates 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 when MySQL clears an 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 = 128

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 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_substitution,strict_trans_tables

MySQL parameter optimization