Parsing the my. cnf parameter settings in MySQL

1. To obtain the current configuration parameters, you must first understand the current configuration parameters and running conditions. Use the following command to obtain the configuration parameters currently used by the server: mysqldnda

1. To obtain the current configuration parameters, you must first understand the current configuration parameters and running conditions. Use the following command to obtain the configuration parameters currently used by the server: mysqld nda

1. Get the current configuration parameters
To optimize configuration parameters, you must first understand the current configuration parameters and running conditions. Use the following command to obtain the configuration parameters currently used by the server:

Mysqld-verbose-help

Mysqladmin variables extended-status-u root-p

In the MySQL console, run the following command to obtain the value of the status variable:

Mysql> show status;

If you only need to check several status variables, you can use the following command:

Mysql> show status like '[matching mode]'; (% ,? )

2. optimization parameters
Parameter Optimization is based on the premise that InnoDB tables are generally used in our databases, rather than MyISAM tables. When optimizing MySQL, two configuration parameters are the most important, table_cache and key_buffer_size.

Table_cache

Table_cache specifies the table cache size. When MySQL accesses a table, if there is space in the table buffer, the table is opened and put into it, so that the table content can be accessed more quickly. Check the status values Open_tables and Opened_tables of the peak time to determine whether to increase the value of table_cache. If you find that open_tables is equal to table_cache and opened_tables is growing, you need to increase the value of table_cache (the preceding STATUS values can be obtained using show status like 'open % tables ). Note that you cannot blindly set table_cache to a large value. If it is set too high, the file descriptor may be insufficient, resulting in unstable performance or connection failure.

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

Case 1: a server that is not particularly busy

Table_cache-512

Open_tables-103

Open ed_tables-1273

Uptime-4021421 (measured in seconds)

In this case, table_cache seems to be too high. During the peak time, the number of opened tables is much less than that of table_cache.

Case 2: A Development Server.

Table_cache-64

Open_tables-64

Opened-tables-431

Uptime-1662790 (measured in seconds)

Although open_tables is already equal to table_cache, opened_tables has a very low value compared to the server running time. Therefore, increasing the value of table_cache should be of little use.

Case 3: A upder‑ming Server

Table_cache-64

Open_tables-64

Open ed_tables-22423

Uptime-1, 19538

In this case, table_cache is set too low. Although the running time is less than 6 hours, open_tables reaches the maximum value, and opened_tables has a very high value. In this way, you need to increase the value of table_cache.

Key_buffer_size

Key_buffer_size specifies the size of the index buffer, which determines the index processing speed, especially the index reading speed. Check the status values Key_read_requests and Key_reads to check whether the key_buffer_size setting is reasonable. The ratio of key_reads/key_read_requests should be as low as possible, at least and (the above STATUS values can be obtained using show status like 'key _ read % ).

Key_buffer_size only applies to the MyISAM table. This value is used even if you do not use the MyISAM table, but the internal temporary disk table is a MyISAM table. You can use the check status value created_tmp_disk_tables to learn the details.

For machines with 1 GB memory, if the MyISAM table is not used, the recommended value is 16 M (8-64 M ).

,