Shulou(Shulou.com)05/31 Report--

This article mainly explains "the summary of parameters commonly used in mysql". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Next let the editor to take you to learn the "mysql commonly used parameters summary" bar!

1.query_cache_size

After a select query, the query structure is cached as sql text, and when the same sql is queried again, the data is directly returned from the cache.

The value of Qcache_lowmem_prunes is very large, which indicates that there is often insufficient buffering. At the same time, the value of Qcache_hits is very large, which indicates that query buffering is used very frequently. If you need to increase the buffer size, the value of Qcache_hits is not large, it indicates that your query repetition rate is very low. In this case, using query buffering will affect efficiency, so you can consider not using query buffering.

Defect:

When mysql works with query_cache, it requires that the table involved in the statement has not changed during this period of time. If the data is updated, all query_cache will be set to invalidate first, and then updated.

If the query_cache_size is too large, there are many queries, the invalidation will be slow, and the update or insert will be slow.

2.key_buffer_size

Only works on myisam tables

Key_buffer_size specifies the size of the index buffer, which determines the speed of index processing, especially the speed of index reads.

By checking the status values Key_read_requests and Key_reads, you can know whether the key_buffer_size setting is reasonable

Total read requests for key_read_requests. Key_keys is the number of disk requests, that is, the smaller the key_read_requests/key_reads, the better.

3.table_open_cache (table_cache prior to version 5.1)

Table_open_cache specifies the size of the table cache. Whenever MySQL accesses a table, if there is space in the table buffer, the table is opened and placed in it, allowing faster access to the table contents

By checking the state values of peak time Open_tables and Opened_tables, you can decide whether you need to increase the value of table_open_cache. If you find that open_tables equals table_open_cache, and opened_tables is growing, then you need to increase the value of table_open_cache

4.log_bin

Binary logs can be closed from the library, and shutting down can improve performance by about 1%.

5.max_connections

Maximum number of connections

6.back_bog

The back_ log value indicates how many requests can be stored on the stack in a short period of time before MySQL temporarily stops answering new requests. Only if you expect to have many connections in a short period of time, you need to increase it, in other words, this value is related to the size of the listening queue for incoming TCP/IP connections.

7.thread_cache

In order to improve the performance of the client request to create a connection, mySQL provides a connection pool, that is, Thread_Cache pool, which places idle connection threads in the connection pool instead of destroying them immediately. The advantage is that when there is a new request, mysql will not immediately create a connection thread, but first go to Thread_Cache to find free connection thread, if there is, then directly use, do not exist to create a new connection thread.

Thread_cache_size

The maximum number of connection threads stored in Thread_Cache. The effect of Thread_Cache is very obvious in the application of short connection.

Thread_stack

The memory that mysql allocates to each connection when it is created. It is generally believed that this value can be applied to most scenarios by default.

Thread_cache hit rate calculation

Show variables like 'thread%';show status like'% connections%';show status like'% thread%'

Formula: thread_cache_hit= (connections-thread_create) / connections*100%

8.log-slave-update

For master-slave architecture, it is used when the slave library needs to keep the same binary as the master library (by default, the slave library does not need to open the binary log)

nine. Sync_binlog

If positive, when each sync_binlog writes to the binary log, the MySQL server synchronizes its binary log to the hard disk

If 0 is not synchronized with the hard disk and 1 is synchronized with the hard disk, the mysql crash loses at most one statement or transaction.

10. Record slow log, analyze

Slow-query-log-file

Long-query-time

Set the time to 1s.

Through explain analysis

Syntax: explain [extended] select... From... where

If extended is used, after the explain statement is executed, you can use the show warnings statement to query the appropriate optimization information.

Type: ALL stands for full table scan

Key: NULL does not use the index

Extra: Using where; Using filesort does not use index sorting

--

Type: range indicates that there is no full table scan

Extra: Using where indicates that there is no file sorting

10. Lock

Show status like 'table%'

Table_locks_immediate indicates the number of table locks to be released immediately, and Table_locks_waited indicates the number of table locks to wait.

If the value of Table_locks_waited is high, there is a serious table-level lock contention condition. At this point, we need to do further inspection of the application to determine the problem.

11.innodb_buffer_pool_size

The size of the memory buffer that buffers innodb data and indexes. The higher this value, the less disk iCompo is required.

12.innodb_flush_log_at_trx_commit

0: means that the log buffer is written to the log file every second and the log file is flushed to disk.

1: when each transaction is committed, the log buffer is written to the log file and flushed to disk

2: for each submission, the log cache is written to the log file, but the army log file is refreshed to disk.

The default is 1, which is also the safest.

At this point, I believe that you have a deeper understanding of the "summary of parameters commonly used in mysql", you might as well come to the actual operation! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.