Complete optimization of MySQL database under Linux high load

Friendship tip: At the same time online access to continue to increase the number of 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.

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.

1 2 Next page > full text reading tips: Try "←→" button, turn the page more convenient Oh!