A thorough optimization method of MySQL database in Linux under high load

At the same time, the amount of online access continues to increase for 1G memory server Obviously feel the difficulty of severe when even every day will panic or occasional server card This problem once bothered me for more than half a month MySQL use is a very scalable algorithm, So you can usually run with little memory or give MySQL more to save for better performance.

After the installation of MySQL, the preparation file should be in the/usr/local/mysql/share/mysql directory, the preparation of a number of files, there are my-huge.cnf my-medium.cnf my-large.cnf my-small.cnf, Different traffic sites and different configuration of the server environment, of course, need to have different configuration files.
In general, my-medium.cnf this configuration file can meet most of our needs; generally we will copy the configuration file to/etc/my.cnf only need to modify this profile on it, use mysqladmin variables Extended-status–u Root–p can see the current parameters, and there are 3 configuration parameters that are most important, that is, Key_buffer_size,query_cache_size,table_cache.
Key_buffer_size only works on the MyISAM table,
KEY_BUFFER_SIZE Specifies the size of the index buffer, which determines the speed at which indexing is processed, especially the speed at which index reads. Generally we set to 16M, actually slightly larger site this number is far from enough, by checking the status value Key_read_requests and Key_reads, you can know 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 state values can be obtained using show status like ' key_read% '). Or if you install the phpMyAdmin can be seen through the server running state, the author recommended the use of phpMyAdmin management MySQL, the following state values are I obtained through the phpMyAdmin case analysis:
This server has been running for 20 days
key_buffer_size–128m
key_read_requests–650759289
key_reads-79112
Proportion close to 1:8,000 health condition very good
Another way to estimate key_buffer_size is to add up the size of the index of each table in your site database. For example: a larger number of table indexes add up to about 125M this figure will become larger as the table becomes larger.
Starting with 4.0.1, MySQL provides a query buffer mechanism. With query buffering, MySQL stores the SELECT statement and query results in a buffer, and in future, for the same SELECT statement (case-sensitive), the result is read directly from the buffer. According to the MySQL user manual, you can use query buffering up to 238% efficiency.
By adjusting the following parameters, you can know if the query_cache_size setting is reasonable.
Qcache Inserts
Qcache Hits
Qcache Lowmem prunes
Qcache Free Blocks
Qcache Total Blocks
Qcache_lowmem_prunes value is very large, it indicates that the buffer is often insufficient, while the value of qcache_hits is very large, it indicates that query buffering use very frequently, at this time need to increase the buffer size Qcache_hits value is not small, It shows that your query is very low repetition rate, in this case, the use of query buffering will affect efficiency, then you can consider not to query buffer. In addition, adding sql_no_cache to a SELECT statement makes it clear that query buffering is not used.
Qcache_free_blocks, if the value is very large, it indicates that there are many fragments in the buffer Query_cache_type specify whether to use query buffering
I set:
Query_cache_size = 32M
Query_cache_type= 1
The following status values are obtained:
Qcache queries in cache 12737 indicates the number of bars currently cached
Qcache Inserts 20649006
Qcache hits 79060095 seems to have a very high repeat query rate.
Qcache Lowmem prunes 617913 There are so many times when cache is too low
Qcache Not Cached 189896
Qcache free memory 18573912 current remaining cache space
Qcache Free Blocks 5328 This number seems to be a bit of a lot of debris
Qcache Total Blocks 30953
If the memory allows 32M you should add a dot
Table_cache Specifies the size of the table cache. Whenever MySQL accesses a table, if there is room in the table buffer, the table is opened and placed in it, so that the table content can be accessed more quickly. By checking the status values of peak time open_tables and Opened_tables, you can decide whether to increase the Table_cache value. If you find that open_tables equals Table_cache, and Opened_tables is growing, then you need to add Table_cache values (the above status values can be used show status like ' open% Tables ' Get). Note that Table_cache can not be blindly set to a large value. If set too high, the file descriptor may be insufficient, causing performance instability or connection failure.
For machines with 1G of memory, the recommended value is 128-256.
I set
Table_cache = 256
Get the following status:
Open Tables 256
Opened Tables 9046
Although Open_tables is already equal to Table_cache, it has been running for 20 days relative to server uptime, and opened_tables values are very low. Therefore, increasing the value of Table_cache should be of little use. If you run for 6 hours, the above values will be considered to increase table_cache.
If you don't need to log 2 into the log, turn this feature off, note that when you turn off the data before you can recover the problem, you need to manually back up the binary log, which contains all the statements that update the data, to use it to restore the data to its final state as much as possible when restoring the database. In addition, if you do synchronous replication (Replication), you also need to use binary log shipping modification.
LOG_BIN Specifies the log file, and if the file name is not provided, MySQL produces the default file name itself. MySQL automatically adds a digital citation after the filename, and each time the service is started, a new binary is regenerated. In addition, you can use Log-bin-index to specify index files, use BINLOG-DO-DB to specify a database of records, and use BINLOG-IGNORE-DB to specify a database that is not logged. Note that binlog-do-db and binlog-ignore-db specify only one database at a time, and multiple statements are required to specify multiple databases. Also, MySQL will change all the database names to lowercase, you must use all lowercase names when specifying the database, otherwise it will not work.
You just need to put a # in front of him to turn off the function.
#log-bin
Turn on slow query log (slow query logs)
Slow query logs are useful for tracking queries that have problems. It records all queries that have been checked for Long_query_time, and records that do not use the index if necessary. The following is an example of a slow query log:
Open the slow query log, you need to set parameters log_slow_queries, Long_query_times, log-queries-not-using-indexes.
log_slow_queries Specifies the log file, and if the file name is not provided, MySQL produces the default file name itself. LONG_QUERY_TIMES Specifies the threshold value for a slow query, which defaults to 10 seconds. Log-queries-not-using-indexes is a parameter introduced later in 4.1.0, which indicates that a query that does not use an index is logged. The author sets long_query_time=10
The author sets:
Sort_buffer_size = 1M
max_connections=120
Wait_timeout =120
back_log=100
Read_buffer_size = 1M
Thread_cache=32
interactive_timeout=120
Thread_concurrency = 4
Parameter description:
Back_log
Require MySQL to have the number of connections. This works when the main MySQL thread gets a lot of connection requests in a very short time, and then the main thread takes some time (albeit very short) to check the connection and start a new thread. The Back_log value indicates how many requests can be on the stack within a short time before MySQL temporarily stops answering the new request. Only if you expect to have a lot of connections in a short time, you need to increase it, in other words, this value is the size of the listening queue for incoming TCP/IP connections. Your operating system has its own limits on the size of this queue. Unix Listen (2) system calls to the hand album should have more details. Check your OS documentation to find the maximum value for this variable. Attempting to set Back_log above your operating system will be ineffective.
Max_connections
The maximum number of concurrent connections, 120 more than this will automatically recover, out of the problem can be automatically resolved
Thread_cache
No specific instructions were found, but it is still useful to create more than 400 threads in the 20 days after 32 and create thousands of threads a day ago.
Thread_concurrency
#设置为你的cpu数目x2, for example, only one CPU, then thread_concurrency=2
#有2个cpu, then thread_concurrency=4
Skip-innodb
#去掉innodb支持