Five ways to optimize mysql performance

(1) Connection

The connection usually comes from the Web server. Listed below are some parameters related to the connection and how to set them.

1. max_connections

This is the maximum number of connections allowed by the web server. Remember that each connection uses session memory (session memory will be covered later in the article).

2. max_packet_allowed

The maximum packet size is usually equal to the size of the largest data set you need to return in one large block. If you are using remote mysqldump, its value needs to be more big.

3. aborted_connects

Check the system status counter to make sure it has not increased. If the number increases, it means that the client encountered an error when connecting.

4. thread_cache_size

The inbound connection will create a new thread in MySQL. Because opening and closing connections in MySQL is cheap and fast, it is not like other databases. There are so many continuous connections like Oracle, but pre-creating threads will not save time, which is why MySQL thread cache is needed.

If it is growing, please pay close attention to the threads created to make your thread cache larger. For thread_cache_size of 2550 or 100, the memory usage is not much.

(2) Query cache

The cached query in MySQL includes two parsing query plans and the returned data set. If the underlying table data or structure changes, Will invalidate items in the query cache.

1. query_cache_min_res_unit

The blocks in the query cache of query_cache_min_res_unit in the MySQL parameter are allocated based on this size. Use the following formula to calculate the average size of the query cache and set this variable according to the calculation result. MySQL will use the query cache more efficiently, cache more queries, and reduce memory waste.

2. query_cache_size

This parameter sets the total size of the query cache.

3. query_cache_limit

This parameter tells MySQL to discard queries larger than this size. Generally, large queries are relatively rare, such as running a batch process to perform statistics on a large report, so those large results The set should not fill the query cache.

qcache hit ratio = qcache_hits / (qcache_hits + com_select)

Use

SQL> show status like 'qcache%';

SQL> show status like 'com_%';

Find these variables.

average query size = (query_cache_size - qcache_free_memory)/qcache_queries_in_cache

Use

SQL> show variables like 'query%';

qcache_* status variables you can get with:

SQL> show status like 'qcache%';

Get the value of query_cache_size.

(3) Temporary table

The memory speed is quite fast, so we hope that all sorting operations are performed in memory. We can adjust the query to make the results Set smaller to achieve memory sorting, or 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 the critical value, except when building the temporary table on disk In addition to the table, many sessions will be created, and these sessions will seize limited resources, so it is better to adjust the query rather than setting these parameters higher. At the same time, it should be noted that tables with BLOB or TEXT field types will directly Write to disk. An in-depth explanation of MySQL two-way replication technology

(4) Session memory

Each session in MySQL has its own memory, which is the memory allocated to SQL queries. So you want to make it as big as necessary. But you have to balance the number of consistent sessions in the database at the same time. What's a bit of a black art here is that MySQL allocates caches on demand, so you can't just add them and multiply them by the number of sessions, which would result in a much larger estimate than MySQL's typical usage. Best practice is to start MySQL, connect all sessions, and then continue to focus on the VIRT column of the top session. The number of mysqld rows usually remains relatively stable. This is the actual total memory usage. Subtract all static MySQL memory areas and you get the actual memory of all sessions, and then divide by the number of sessions to get the average.

1. read_buffer_size

Cache continuously scanned blocks. This cache is across storage engines, not just MyISAM tables.

2. sort_buffer_size

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

3. join_buffer_size

The size of the buffer area allocated to execute the joint query, set it to 1M-2M size, and then set it individually as needed in each session.

4. read_rnd_buffer_size

is used for sorting and order by operations. It is best to set it to 1M, and then set it to a larger value as a session variable in the session.

(5) Slow query log

The slow query log is a very useful feature of MySQL.

1、log_slow_queries

In the log_slow_queries parameter of MySQL parameters, set it in the my.cnf file and set it to on. By default, MySQL will place the file in the data directory, and the file will be named in the form of "hostname-slow.log". But you can also specify a name for it when setting this option.

2. long_query_time

The default value is 10 seconds, you can set it dynamically, the value ranges from 1 to set it to on. If the database is started, the log will be turned off by default. As of 5.1.21 and Google patched versions, this option can be set in microseconds, which is an amazing feature because once you eliminate all queries that take longer than 1 second, the adjustment is very successful and this can help you Eliminate problematic SQL before it becomes a big problem.

3. log_queries_not_using_indexes

It is a good idea to turn this option on, it truly records the query that returns all rows.

Summary

We have introduced the five major categories of MySQL parameter settings. We rarely touch them at ordinary times. These parameters are still very useful when performing database performance tuning and fault diagnosis.