Shulou(Shulou.com)06/01 Report--

(1) connection

The connection usually comes from the Web server. Here 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, so keep in mind that session memory is used for each connection (as for session memory, which is covered later in the article).

2 、 max_packet_allowed

The most big data packet size is usually equal to the size of the most big data set you need to return in a large chunk, and if you are using remote mysqldump, its value needs to be larger.

3 、 aborted_connects

Check the counter for the status of the system to determine that it is not growing, and if the number increases, an error was encountered while connecting to the client.

4 、 thread_cache_size

Inbound connections create a new thread in MySQL, because opening and closing connections in MySQL are cheap and fast, so it doesn't have as many persistent connections as other databases, such as Oracle, but pre-creating threads doesn't save time, which is why MySQL threads cache.

If you are growing, pay close attention to the threads created to make your thread cache larger and less memory intensive for 2550 or 100th thread_cache_size.

(2) query cache

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

1 、 query_cache_min_res_unit

The blocks in the query_cache_min_res_unit query cache in the MySQL parameters are allocated according to this size. Use the following formula to calculate the average size of the query cache. If you set this variable according to the calculation result, MySQL will use the query cache more effectively, 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 drop queries larger than this size. Generally, large queries are relatively rare, such as running a batch to execute the statistics of a large report, so those large result sets 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%'

Gets the value of query_cache_size.

(3) temporary tables

Memory is quite fast, so we want all sorting operations to be done in memory, and we can adjust the query to make the result set smaller to achieve memory sorting, or to set the variables larger.

Tmp_table_size

Max_heap_table_size

Whenever a temporary table is created in MySQL, it uses the minimum values of these two variables as critical values, and in addition to building temporary tables on disk, it creates a number of sessions that preempt restricted resources, so it is best to adjust the query rather than set these parameters higher, and it is important to note that tables with BLOB or TEXT field types will be written directly to disk. 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 it to be as large as possible to meet your needs. But you have to balance the number of consistent sessions in the database at the same time. The dark 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 is estimated to be much larger than the typical use of MySQL. The best practice is to start MySQL, connect all sessions, and then continue to focus on the VIRT column for the top-level session. The number of mysqld rows usually remains relatively stable, which is the actual total memory usage. Subtract all static MySQL memory regions, and you get the actual all session memory, 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

To perform the size of the sort cache, it is best to set it to 1M-2M, and then set it in the session to set a higher value for a particular query.

3 、 join_buffer_size

Execute the cache size allocated by the federated query, set it to 1M-2M size, and then set it separately on demand in each session.

4 、 read_rnd_buffer_size

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

(5) slow query log

Slow query logs 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 and sets it to on. By default, MySQL will put 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 when setting this option.

2 、 long_query_time

The default value is 10 seconds, you can dynamically set it from 1 to set it to on, if the database is started, by default, the log will be turned off. As of 5.1.21 and the version with the Google patch installed, this option can be set in microseconds, which is a great feature, because once you eliminate all queries that take more than 1 second, the adjustment is very successful, which can help you eliminate the problem SQL before the problem gets bigger.

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.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.