How to set MySQL parameters (2) _ MySQL-mysql tutorial

How to set MySQL parameters (2) tips: (1) connection usually comes from the Web server. The following lists some connection-related parameters and how to set them. 1. max_connections: this is the maximum number of connections allowed by the Web server. remember that session memory is used for each connection (this article will cover session memory later ). 2. the maximum data packet size of max_packet_allowed is usually equal

(3) temporary tables

The memory speed is quite fast, so we want all the sorting operations to be carried out in the memory. we can adjust the query to make the result set smaller to achieve memory sorting, or set the variable bigger.

Tmp_table_size

Max_heap_table_size

Whenever you create a temporary table in MySQL, it uses the minimum values of these two variables as the critical value. in addition to creating a temporary table on the disk, it also creates many sessions, these sessions will seize limited resources, so it is best to adjust the query instead of setting these parameters higher. at the same time, note that tables with BLOB or TEXT fields will be directly written to the disk. In-depth introduction to MySQL two-way replication technology

(4) session memory

Each session in MySQL has its own memory, which is the memory allocated to the SQL query, so you want to make it as large as possible to meet your needs. However, you have to balance the number of consistent sessions in the database at the same time. The black art here is that MySQL allocates cache on demand. Therefore, you cannot just add them and multiply them by the number of sessions. this is estimated to be much larger than the typical MySQL usage. The best practice is to start MySQL, connect all sessions, and continue to pay attention to the VIRT column of top-level sessions. the number of mysqld rows is usually relatively stable, which is the actual total memory usage, after all the static MySQL memory areas are subtracted, the actual memory of all sessions is obtained. then, the average value is obtained by dividing the number of sessions.

1. read_buffer_size

Cached continuously scanned blocks. this cache is cross-storage engine, not just a MyISAM table.

2. sort_buffer_size

The size of the cache for sorting. it is best to set it to 1 M-2 M, and then set it in the session to set a higher value for a specific query.

3. join_buffer_size

The size of the cache area allocated for execution of the joint query is set to 1-2 MB, and then separately set as needed in each session.

4. read_rnd_buffer_size

For sorting and order by operations, it is best to set it to 1 M, and then you can set it as a session variable to a larger value in the session.

(5) slow query logs

Slow log query is a useful feature of MySQL.

1. log_slow_queries

In my. set it in the cnf file and set it to on. by default, MySQL puts the file in the data directory, and the file uses "host name-slow. log, but you can also specify a name for this option.

2. long_query_time

The default value is 10 seconds. you can set it dynamically. The value ranges from 1 to on. if the database is started, the log is disabled by default. As of 5.1.21 and the version with the Google patch installed, this option can be set in microseconds. this is a great feature, because once you have eliminated all queries whose query time exceeds 1 second, the adjustment is very successful, which helps you eliminate the problem SQL before the problem increases.

3. log_queries_not_using_indexes

Enabling this option is a good idea. it actually records the query that returns all rows.

Summary

We have introduced the setting of five MySQL parameters. we usually seldom touch them. These parameters are very useful for database performance optimization and fault diagnosis, the next article will introduce the other five parameters.