MySQL optimization notes

I. Use of mysqladmin
#mysqladmin extended-status-u root-i 2-c 2-p | grep Connect
Check the status of MySQL with the Connect character variable, statistics every two seconds, total statistics 2 times

#mysqladmin extended-status-u root-r-I 2-p | grep Connect
View incremental output within 2 seconds, this item does not work

Two. Important part of the MySQL server state variable

1.aborted_clients
If this variable increases over time, make sure the connection is gracefully closed. If it is not necessary to check network performance and check the configuration variables of max_allowed_packet, queries that exceed Max_allowed_packet will be forcibly interrupted

2.aborted_connection
This value should be close to 0. No, it could be a network problem. Of course, if there is an attack or an invalid database is defined, the value is incremented.

3.binlog_cache_disk and Binlog_cache_use
If the ratio between Binlog_cache_disk and Binlog_cache_use is large, then the value of binlog_cache_size should be increased. Most things should fall into the binary log cache.
There is no good way to reduce the binary log cache misses, only add binlog_cache_size to observe.

4.bytes_received and Bytes_sent
These two values can help you to investigate whether the problem is caused by too much data being received, or by sending too much data

5.com_*
Should be careful not to let com_rollback such uncommon variables exceed expectations

6.Connections
This value table is connected to the number of servers. If the value increases quickly, for example, hundreds of per second, you should check the number of connections or adjust the operating system's network stack

7.created_tmp_disk_tables
If this value is higher, two things are wrong.
A. Querying a BLOB or text column when a temporary table is created
B.tmp_table_size and max_heap_table_size may not be big enough.

8.created_tmp_tables
The only way to handle this value is to refine the query

9.handler_read_rnd_next
Handler_read_rnd_next/handler_read_rnd shows the approximate average of a full-scale scan. If the value is large, you should optimize the schema, index, and query

10.key_blocks_used
If
The value of key_blocks_used * key_cache_block_size (parameters, not variables) is much smaller than the key_buffer_size on the server that has been fully warmed up, so long means this key_buffer_size value is too large, The memory is wasted.

11.key_reads (number of requests to read the index from disk)
Be careful to observe the number of reads that occur per second, and this value matches the I/O system. If the value is too large in unit time, you need to see if the disk matches this.

12.max_used_connections
If the value is the same as max_connections, it is possible that the max_connections setting is too low or the maximum system load exceeds the server limit. In many cases, simply adding max_connections does not solve the problem. See if the program behaves correctly, Server tuning is correct, server architecture is good.

13.open_files
Be careful not to close with the Open_files_limit value, if close, increase Open_files_limit

14.open_tables and Opened_tables
Open_tables: Is the number of tables currently open in the cache. (Flush tables will work for this item)
Opened_tables: Is the number of open tables since MySQL started.
These two values should be compared with table_cache, if there are too many opened_tables per second, then Table_cache is not large enough. Table cache is not being exploited. Explicitly create a temporary table (create temporary table Table_ Name) also leads to an increase in opened_tables.

15.qcache_*
Query cache

16.select_full_join
A full join is a no-index join. This is the real performance killer, it is best to avoid, even if a minute is too much.

17.select_full_range_join
If the variable is too high, then the description runs a number of join tables that use the range query. The range query is slow and is also a performance optimization point.

18.select_range_check
This variable records the number of queries that re-examines the index for each row of data at the time of the join. This performance overhead is significant, and if the value is high or increasing, it means that some queries do not find good indexes.

19.slow_launch_threads
This variable illustrates some of the factors that are delaying the join of a new thread. Typically, the system is overloaded.

20.sort_merge_passes
This variable is larger and should be increased by sort_buffer_size.

21.table_locks_waited
This variable shows how many tables are locked and causes a server-level lock wait. The InnoDB does not increase this value. If the value is high and increasing, it indicates that a serious concurrency bottleneck has been encountered. This time you should consider using the InnoDB storage engine, manually partitioning the large table, Or use MySQL's partitioning mechanism to optimize queries, enable concurrent insertions, or optimize locking settings.

22.threads_created
If the value variable is large or increasing, you should probably increase the value of thread_cache_size. You can check Threads_cache to know how many threads are in the cache.

Three. Tuning each connection setting

1.sort_buffer_size
Controls the size of the cache used for file sorting. Even if the amount of data that needs to be sorted is small, MySQL will allocate all of the space at once by setting

2.read_buffer_size (for MyISAM)
A request to sequentially scan a table allocates a read-in buffer, and MySQL allocates a memory buffer for it. The read_buffer_size variable controls the size of this buffer. If the sequential scan requests for a table are frequent, and you think that frequent scans are going too slowly, you can improve their performance by increasing the value of the variable and the size of the memory buffer.

3.read_rnd_buffer_size (for MyISAM)
If you think that the continuous scan is going too slowly, you can improve its performance by increasing the value of the variable and the size of the memory buffer
Increase ORDER BY query efficiency

4.tmp_table_size
Temp Table Size

5.myisam_sort_buffer_size
For repairing tables

MySQL optimization notes