Configuration optimization for MySQL

Requirements: MySQL parameter optimization for different sites, extremely online volume, access volume, number of posts, network conditions, as well as the hardware configuration of the machine has a relationship, optimization can not be a million times, need to constantly monitor the work of observation and debugging, in order to get the best results. The variables most affected by performance optimization are the connection request variable and the buffer variable.

Theoretical Summary:

Modify VIM/MY.CNF

Max_connections = 1024 Sets the maximum number of connections to 1024

Back_log = 100 number of staged connections

Wait_timeout = 100

Interactive_timeout = 100 Modify the value of the interactive

Key_bugger_size = 268435456 and key_buffer_size=256m adjustment

Query_cache_size = 256M Cache Size

Query_cache_type = 1 Buffer type

Max_connect_errors = 20 Turn on security-related counters, host connection request extra 20 prohibit connection request

Sort_buffer_size = 2M Allocates a buffer of that size for the sorted thread

connection request Variable :

max_connections. If the server has a large number of concurrent connection requests, this value should be increased accordingly. Increasing the number of concurrent connections is to establish the server can support the situation, the more connections, MySQL for the internal connection to provide a connection buffer overhead memory, the larger the value should be adjusted appropriately. A value that is too small will often cause error 1040:too many connections error.

Show status like ' connections '; Wildcards view the current number of connections, regardless of whether they are successful or not.

Show variables like '%max_connections% ' Maximum number of connections

Show status like '%max_used_connections ' response connection number

Show variables like '%wait_timeout% ' view wait_timeout

Show variables like '%interactive_timeout% ' view interactive

Show status like ' qcache% '; You can specify whether the Query_cache_size setting is reasonable

Show variables like ' query_cache% '; Verify that the Query_cache configuration is turned on

Max_used_connections and Max_connections Same then is max_connections set too low or exceed the server load limit, Hell 10% is set too large.

Staging Connection Data : Back_log. The main MySQL thread gets a lot of requests in a short period of time and it works. When the connection value reaches Max_connections, the new request will be present in the stack, waiting for a connection to release the resource. The connection resource is not granted if the number of pending connections exceeds Back_log. Back_log how many requests can be present on the stack in a short period of time before the new request is temporarily stopped. If you want to connect a lot in a short time, you need to increase the Back_log value.

View Host Process table: show variables Processlist

View the value of Back_log: Show variables like ' Back_log '

If the host process table has a large number of pending connections, increase the value of max_connections.

MySQL closes a non-interactive connection before asking for the number of seconds to wait wait_timeout and Interactive_timeout

Wait_timeout refers to the number of seconds MySQL waits before closing a non-interactive connection

Interactive_timeout refers to the number of seconds that MySQL requests to wait before closing an interactive connection, such as when we enter MySQL management on the terminal, using an interactive connection, if the operation time exceeds the value of the Interactive_timeout setting, will be automatically disconnected.

Impact on performance

If the size is set, the connection closes quickly so that some persistent connections do not work

If the settings are too large, causing the connection to open too long, if you do show processlist can see too much sleep state connection, resulting in too many connections error

Generally want the value of wait_timeout as low as possible. Interactive settings have little impact on Web application

Buffer variable

Global buffering

Specifies the size of the index buffer, which determines the speed of index processing, especially the speed of index reads.

Status values: Key_read_requests and Key_reads

Create_tmp_disk_tables

Key_reads/key_read_requests should be as low as possible, at least 1:100,1:1000 would be better.

Show status like ' key_read% '

Show status like ' key_read% ';

Variables_name value

Key_read_requests 6

Key_reads 3

A total of 6 index read requests, with 3 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% = 50%

Key_buffer_size only works on Mylsam tables. Even if you do not use the Mylsam table, the internal temporary disk table is the Mylsam table, and you use that value

query Buffer Simple QC: query_cache_size

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

An SQL statement, as long as the difference is one character, then these two SQL will use a different cache

Show status like ' qcache% '

Variables_name value

The number of contiguous memory blocks in the Qcache_free_blocks 1 buffer, which is larger, indicates that there are many memory fragments, and FLUSH QUERY cache organizes the fragments in the cache.

Qcache_free_memory 1031832 of the remaining memory size, you can more accurately observe the current system Quuery cache memory size is sufficient.

Qcache_hits 0 has a number of hit caches. To verify the effect of our query buffer, the larger the number, the buffer effect is obvious

Qcache_inserts more than 0 this miss and then insert, the new SQL request is not found in the buffer, you have to perform query processing, the larger the value, indicates that the query cache is applied to less, the effect is not ideal.

Qcache_lowment_prunes more than 0 query cache was cleared out because of insufficient memory. By combining Qcache_lowmen_prunes and qcache_free_memory, it is more clear that the memory size of the query cache in the system is really sufficient, and whether it occurs very frequently because of insufficient memory and the query is swapped out

Qcache_not_cached 1 The number of queries that are not suitable for caching, which are not functions such as SELECT.

Qcache_queries_in_cache 0 The cache in the current query cache gets the query number

Qcache_total_blocks 1 Block number in current query cache

Inquire about the configuration of Query_cache: show variables like ' query_cache% ';

Variables_name value

Query_cache_limit 104876 queries that exceed this size will not be cached

Query_cache_min_res_unit 4096 buffer fast size. The default is 4kB, and setting the value is good for big data queries, but if the query is small data, it will cause memory fragmentation and waste.

Query_cache_size 209715209 Query Cache size

Query_cache_type OFF buffer type, decide to buffer what kind of query, not casually set, must be set to number, optional items

0 off, no buffering or re-getting results

1 on cache all results except select Sql_no_cache ... Inquire

2 Dnmand cache only select Sql_cache ... Inquire

Query_cache_wlock_invalidata When a client has a correct write to the Mylsam table, if the query is queried in the cache, whether to return the cache result or wait for the write operation to get the result in the Read table

Query buffer Fragment rate = Qcache_free_blocks/qcache_total_blocks * 100%

Fragmentation rate below 25% description query_cache_size settings too large, can be appropriately reduced, the query buffer utilization above 80* and qcache_lowmen_prunes>50 that query_cache_size may be a bit small, or too many fragments

Query buffer hit ratio =qcache_hits/(qcache_hits+qcache_inserts) *100%

Limitations of Query Cache

External query in all subqueries SQL cannot be cache

The query in procedure,function and trigger cannot be deleted by the cache

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

security-related counter values in MySQL : max_connect_errors, which is responsible for preventing clients who have failed too many attempts to prevent brute-force passwords from being cracked for a specified number of times, will prohibit host connection requests. Information about this host is not cleared until the MYSL server restarts or through the flush hosts command.

The thread that needs to be sorted allocates a buffer of that size : sort_buffer_size. Accelerates an order by or group by operation. Sort_buffer_size is a connections-level parameter, each time the connection needs to use this buffer, the memory of the set is allocated once. This value is not as big as the better, too high concurrency may deplete traditional memory resources

limit server to receive packet size , large inserts and updates are subject to max_allowed_packet parameter limits, resulting in write or update failures. The maximum value is 1GB, and a multiple of 1024 must be set.

The size of the associated cache between tables , as with Sort_buffer_size, the corresponding allocated memory is also the exclusive one for each connection

Server thread caching, which indicates the number of threads that can be re-used to remain in the buffer, when a port is connected, the client's thread is placed in the cache in response to the next customer rather than destroyed, and if the thread is re-requested, the request is read from the cache, the cache is empty or a new request, and the thread is recreated. If there are many new threads, adding this value can improve system performance, and by comparing the variables of the connections and threads_created states, you can see the effect of this variable

Number of connections attempting to connect to MySQL: Show status like ' Conections ';

The number of connections attempting to connect to MySQL (regardless of whether the connection was successful): Show status like ' threads_% ';

Configure several variables for InnoDB

Modify the/etc/my.cnf file and add it under [mysqld]

Innodb_buffer_pool_size = 2048M

the equivalent of key_buffer_size for the Mylsam table . InnoDB uses this parameter to specify the size of the memory to buffer the data and index, a separate MySQL database, the maximum can be set to the value of physical memory 80&innodb_buffer_pool_size

Control InnoDB writes data from log buffer to the log file and flush the disk at a point in time, with the value 0,1,2.0 representing the transaction commit, not log write operations, but the log per second The data in buffer is written to the log file and flush disk once; 1 per second or per commit of the transaction causes the log file to be written to the flush disk, ensuring that the acid;2 of the transaction causes the action to be written to the log file each time the transaction commits, but completes the flush disk operation once per second.

The actual operation found that the value of the insertion of data on the speed of a large impact, set to 2 is to insert 10,000 records only 2 seconds, set to 0 is just 1 seconds, set to 1 is required 229 seconds. So the MySQL manual recommends merging inserts and one transaction as much as possible to increase speed.

set the number of concurrent InnoDB threads , the default value of 0 means no limit, to set the same as the server CPU cores or twice times the number of cores of the CPU, we recommend the default settings, generally 8.:innodb_thread_concurrency = 0

determines the amount of memory used by this log file , in units of M, where the buffer is larger to improve performance, and for larger transactions, you can increase the cache size Innodb_log_bugger) size = 32M

determine the size of the log file , in M, for a larger setting to provide performance: Innodb_log_file_size = 50M

For high performance , MySQL can write log files to multiple files in a circular manner, recommended setting to 3:innodb_log_iles_in_group = 3

MySQL reads the buffer size , the request to sequentially scan the table allocates a read-in buffer, and MySQL allocates one of the memory buffers for it, if the order of the table is very frequent, and the frequent scan is considered too slow, You can improve its performance by increasing the value of the variable and the size of the memory buffer. As with Sort_buffer_size, the allocated memory for this parameter is also exclusive to each connection. Read_buffer_size = 1M

Random Read buffer size , when the row is read in any order, a random read buffer is allocated, the query is sorted, MySQL will first scan the buffer to avoid disk search, improve query speed, if you need to sort large amounts of data, you can appropriately increase the value. However, MySQL will issue this buffer space for each client connection, so you should set this value as appropriately as possible to avoid excessive memory overhead. Read_rnd_buffer_size = 16M

Bulk_insert_buffer_size = 64M BULK INSERT data cache size , can effectively improve the efficiency of insertion, the default is 8M

binary Log

Log-bin=/usr/local/mysql/data/mysql-bin

binlog_cache_size = 2M //memory allocated for each session , used to store the cache of binary logs during transaction , improve logging Bin-log 's efficiency. There is no big business,and DML is not very frequent when you can set a small point, if the transaction is large and many,DML operations are also frequent, you can adjust the size of the appropriate. The former suggests --1m, the latter suggesting: 2--4m

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, and if the current log size reaches max_binlog_size, a new binary log is automatically created. You cannot set the variable to be greater than 1GB or less than 4096 bytes. The default value is 1GB. When importing large-capacity SQL files, it is recommended to turn off sql_log_bin, otherwise 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.
the number of days that the binary log was automatically deleted. The default value is 0, which means " no automatic deletion ".

Mysqladmin flush-logs can also start a new Binarylog

Execute the mysqlslap tool for testing before tuning

[[Email protected] ~] #mysqlslap--defaults-file=/etc/my.cnf--concurrency=10--iterations=1--create-schema= ' test1 '- -query= ' select * from Test1.tb1 '--engine=innodb--number-of-queries=2000-uroot-p123456–verbose

Show Results:

Benchmark

Runningfor engine InnoDB

Averagenumber of seconds to run all queries:13.837 seconds

Minimumnumber of seconds to run all queries:13.837 seconds

Maximumnumber of seconds to run all queries:13.837 seconds

NUMBEROF clients running Queries:10

Averagenumber of Queries per client:200

after optimization , execute the Mysqlslap tool for testing

[[Email protected] ~] #mysqlslap--defaults-file=/etc/my.cnf--concurrency=10--iterations=1--create-schema= ' test1 '- -query= ' select * fromtest1.tb1 '--engine=innodb--number-of-queries=2000-uroot-p123456–verbose

Show Results:

Benchmark

Runningfor engine InnoDB

Averagenumber of seconds to run all queries:4.199 seconds

Minimumnumber of seconds to run all queries:4.199 seconds

Maximumnumber of seconds to run all queries:4.199 seconds

NUMBEROF clients running Queries:10

Averagenumber of Queries per client:200

This article is from the "11853028" blog, please be sure to keep this source http://tanhong.blog.51cto.com/11853028/1905429

Configuration optimization for MySQL