Five configuration parameters that affect Mysql performance _mysql

The following article is mainly about the performance of MySQL closely related to the five configuration parameters of the introduction, I saw a few days ago in the relevant web site on the performance of MySQL closely related to the five parameters of the configuration of data, feel very good, take out for everyone to share, hope you can have some harvest.

(i) connection

Connections are typically from a Web server, and the following are lists of connection-related parameters and how to set them up.

1, Max_connections

This is the maximum number of connections allowed by the Web server, remembering that session memory is used for each connection (about session memory, which is covered later in the article).

2, max_packet_allowed

The maximum packet size, which is usually equal to the size of the maximum dataset you need to return in a chunk, if you are using a remote mysqldump, it needs to be larger.

3, Aborted_connects

Check the System state counters to determine that they are not growing, and if the number of increases indicates an error occurred while the client is connected.

4, Thread_cache_size

Inbound connections Create a new thread in MySQL, because the connection between open and close in MySQL is cheap and fast, it does not have as many continuous connections as other databases, such as Oracle, but the thread creation does not save time, which is why MySQL thread caching is the reason.

If in growth please pay close attention to the threads created to make your thread cache larger, for 2550 or 100 of thread_cache_size, memory footprint is not much.

(ii) Query caching

(iii) Temporary tables

Memory speed is fairly fast, so we want all sort operations to be in memory, and we can adjust the query to make the result set smaller to achieve the memory sort, or to set the variable larger.

Tmp_table_size

Max_heap_table_size

Whenever you create a temporary table in MySQL, it uses the minimum value of these two variables as a critical value, in addition to building temporary tables on disk, many sessions are created that preempt restricted resources, so it is better to adjust the query rather than set the parameters higher, while Note that a table with a BLOB or text field type is written directly to the disk. MySQL bidirectional replication technology

(iv) 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. But you have to balance the number of consistent sessions within the database at the same time. It's a bit of a black art here. MySQL is cached on demand, so you can't just add them and multiply the number of sessions, which is much larger than the typical MySQL usage.

The best practice is to start MySQL, connect all sessions, and then continue to focus on the Virt column of the top-level session, where the number of mysqld rows is usually relatively stable, which is the actual total memory consumption, minus all the static MySQL memory areas, and gets all the actual session memory. The average is then divided by the number of sessions.

1, Read_buffer_size

Cache the contiguous scanned block, which is spanned by the storage engine, not just the MyISAM table.

2, Sort_buffer_size

The size of the sort buffer is performed, preferably set to 1m-2m, and then set in session to set higher values for a particular query.

3, Join_buffer_size

Execute the buffer size for the union query allocation, set it to 1m-2m size, and then set it on demand separately in each session.

4, Read_rnd_buffer_size

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

(v) Slow query log

A slow query log is a useful feature of MySQL.

1, Log_slow_queries

The log_slow_queries parameter in the MySQL parameter sets it in the My.cnf file, sets it to on, by default, MySQL places the file in the data directory, and the file is named "Host name-slow.log". But you can also specify a name for this option when you set it.

2, Long_query_time

The default value is 10 seconds, you can set it dynamically, values from 1 to set to ON, and if the database is started, the log will be closed by default. As of 5.1.21 and installing the Google patch version, this option can be set in microseconds, which is a terrific feature, because once you've eliminated all queries for more than 1 seconds, the adjustment is very successful, which can help you eliminate the problem SQL before the problem becomes larger.

3, Log_queries_not_using_indexes

It's a good idea to turn on this option, which actually records the query that returns all rows.

Summary

We introduced the MySQL parameters of the five types of settings, usually we rarely touch them, in the MySQL performance tuning and troubleshooting these parameters are very useful.

The cached query in MySQL includes two parse query plans and the returned dataset, which will invalidate items in the query cache if the underlying table data or structure changes.

1, Query_cache_min_res_unit

MySQL parameters in the Query_cache_min_res_unit query cache in the block is allocated in this size, using the following formula to calculate the average size of the query cache, based on the results set this variable, MySQL will be more efficient use of query caching, caching more queries, Reduce the waste of memory.

2, Query_cache_size

This parameter sets the total size of the query cache.

3, Query_cache_limit

This parameter tells MySQL to throw out queries larger than this size, and generally large queries are relatively rare, such as running a batch to perform statistics on a large report, so those large result sets should not fill the query cache.

Copy Code code as follows:

Qcache hit ratio = Qcache_hits/(qcache_hits + com_select)

Use

Copy Code code as follows:

Sql> Show status like ' qcache% ';
Sql> Show status like ' com_% ';

Find these variables.

Copy Code code as follows:

Average query size = (query_cache_size–qcache_free_memory)/qcache_queries_in_cache

Use

Copy Code code as follows:

Sql> Show variables like ' query% ';
qcache_* status variables you can get with:
Sql> Show status like ' qcache% ';