Thoroughly optimize the mysql database under high load on Linux

At the same time, the number of online visits continues to increase. When the server with 1G memory is obviously severely strained, it will even crash every day or the server will freeze from time to time. This problem has bothered me for more than half a month. MySQL uses a very scalable algorithm, so you Usually it can be run with less memory or give MySQL more memory to get better performance.

After installing mysql, the configuration files should be in the /usr/local/mysql/share/mysql directory. There are several configuration files, including my-huge.cnf my-medium.cnf my-large.cnf my-small.cnf, Websites with different traffic flows and server environments with different configurations will of course require different configuration files.

Under normal circumstances, the configuration file my-medium.cnf can meet most of our needs; generally we will copy the configuration file to /etc/my.cnf and only need to modify this configuration file, use mysqladmin variables extended- status –u root –p can see the current parameters. There are three configuration parameters that are the most important, namely key_buffer_size, query_cache_size, table_cache .

key_buffer_size only works for MyISAM tables,

key_buffer_size specifies the size of the index buffer, which determines the speed of index processing, especially the speed of index reading. Generally, we set it to 16M. In fact, this number is far from enough for slightly larger sites. By checking the status values ​​Key_read_requests and Key_reads, you can know whether the key_buffer_size setting is reasonable. The ratio key_reads / key_read_requests should be as low as possible, at least 1:100, 1:1000 is better (the above status value can be obtained using SHOW STATUS LIKE 'key_read%'). Or if you have installed phpmyadmin, you can see it through the server running status. The author recommends using phpmyadmin to manage mysql. The following status values ​​are the example analysis I obtained through phpmyadmin:

This server has been running for 20 days
key_buffer_size – 128M
key_read_requests – 650759289
key_reads - 79112
The ratio is close to 1:8000 and the health condition is very good.

Another way to estimate key_buffer_size is to add up the space occupied by the indexes of each table in your website database. Take this server as an example: the indexes of several larger tables add up to about 125M. This number will increase as the table becomes larger. get bigger.

Starting from 4.0.1, MySQL provides a query buffering mechanism. Using query buffering, MySQL stores the SELECT statement and query results in the buffer. In the future, for the same SELECT statement (case-sensitive), the results will be read directly from the buffer. According to the MySQL user manual, using query buffering can achieve up to 238% efficiency.

By adjusting the following parameters, you can know whether the query_cache_size setting is reasonable.
Qcache inserts
Qcache hits
Qcache lowmem prunes
Qcache free blocks
Qcache total blocks

If the value of Qcache_lowmem_prunes is very large, it indicates that there is often insufficient buffering. At the same time, if the value of Qcache_hits is very large, it indicates that the query buffer is used very frequently. In this case, the buffer size needs to be increased. If the value of Qcache_hits is small, it indicates that your query repetition rate is very high. Low. In this case, using query buffering will affect efficiency, so you can consider not using query buffering. In addition, adding SQL_NO_CACHE to the SELECT statement can clearly indicate that the query buffer 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 specifies whether to use query buffering

I set:
query_cache_size = 32M
query_cache_type= 1

Get the following status value:
Qcache queries in cache 12737 indicates the number of items currently cached
Qcache inserts 20649006
Qcache hits 79060095 It seems that the repeated query rate is quite high.
Qcache lowmem prunes 617913 There are so many times that the cache is too low.
Qcache not cached 189896
Qcache free memory 18573912 Current remaining cache space
Qcache free blocks 5328 This number seems a bit large and fragmented
Qcache total blocks 30953
If the memory allows 32M, you should add more.

table_cache specifies the size of the table cache. Whenever MySQL accesses a table, if there is space in the table buffer, the table is opened and placed in it, allowing faster access to the table contents. By checking the status values ​​Open_tables and Opened_tables at the peak time, you can decide whether you need to increase the value of table_cache. If you find that open_tables is equal to table_cache, and open_tables is growing, then you need to increase the value of table_cache (the above status value can be obtained using SHOW STATUS LIKE 'Open%tables'). Note that table_cache cannot be set to a large value blindly. If set too high, it may cause insufficient file descriptors, resulting in unstable performance or connection failure.

For machines with 1G of memory, the recommended value is 128-256.

Author settings
table_cache = 256

Get the following status:
Open tables 256
Open tables 9046
Although open_tables is already equal to table_cache, relative to the server running time, it has been running for 20 days and the value of open_tables is also very low. Therefore, increasing the value of table_cache should be of little use. If the above value appears after running for 6 hours, then you should consider increasing table_cache.

If you do not need to record binary logs, turn off this function. Note that after turning it off, you cannot restore the data before the problem. You need to manually back up. The binary log contains all statements that update data, and its purpose is to restore the database. Use it to restore data to its final state as much as possible. In addition, if you do synchronous replication (Replication), you also need to use the binary log to transfer modifications.

log_bin specifies the log file. If no file name is provided, MySQL will generate the default file name itself. MySQL will automatically add a numeric index after the file name, and will regenerate a new binary file every time the service is started. In addition, use log-bin-index to specify the index file; use binlog-do-db to specify the database for recording; use binlog-ignore-db to specify the database not to be recorded. Note: binlog-do-db and binlog-ignore-db only specify one database at a time. Specifying multiple databases requires multiple statements. Moreover, MySQL will change all database names to lowercase. You must use all lowercase names when specifying the database, otherwise it will not work.

To turn off this function, just add a # sign in front of it
#log-bin

Turn on slow query log (slow query log)

The slow query log is useful for tracking down problematic queries. It records all queries that checked long_query_time, and if necessary, records without using an index. Here is an example of a slow query log:

To enable slow query logs, you need to set the parameters log_slow_queries, long_query_times, and log-queries-not-using-indexes.

log_slow_queries specifies log files. If no file name is provided, MySQL will generate a default file name by itself. long_query_times specifies the slow query threshold, the default is 10 seconds. log-queries-not-using-indexes is a parameter introduced after 4.1.0, which indicates that queries that do not use indexes are logged. The author sets long_query_time=10

Author settings:

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
The number of connections that MySQL is required to have. This works when the main MySQL thread gets a lot of connection requests in a short period of time, and then the main thread takes some time (albeit briefly) to check for connections and start a new thread. The back_log value indicates how many requests can be stored in the stack in a short period of time before MySQL temporarily stops answering new requests. Only if you expect many connections in a short period of 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 limit on this queue size. The man page for the Unix listen(2) system call should have more details. Check your OS documentation to find out the maximum value of this variable. Attempting to set back_log higher than your operating system's limit will have no effect.
max_connections
The maximum number of concurrent connections is 120. If it exceeds this value, it will automatically recover and problems will be automatically solved.

thread_cache
I couldn't find any specific instructions, but after setting it to 32, more than 400 threads were created in 20 days, whereas thousands of threads were created in one day before, so it is still useful.

thread_concurrency
#Set to the number of your cpu x2, for example, if there is only one cpu, then thread_concurrency=2
#There are 2 cpu, then thread_concurrency=4

skip-innodb
#Remove innodb support