MySQL Performance tuning

MySQL Performance tuning
Improve the performance and impact speed of your MySQL system
-Replacement hardware (cpu/disk/memory, etc.)
-Operating parameter adjustment of the service program
-Optimization of SQL queries

Concurrency and connection control
-Number of connections, connection timeout
Max_connections//maximum number of concurrent connections allowed
Connect_timeout//Waiting for the number of seconds to establish a connection, default 10 seconds, only valid at logon
Wait_timeout//waits for the inactivity timeout seconds of the connection to close, default 28,800 seconds (8 hours)

-Production to see the number of connections currently in use
Mysql>flush status;
Mysql>show globale Status like "Max_used_connections";

-View the default maximum number of connections
Mysql>show variables like "max_connections";

-Cache parameter Control
buffers, number of threads, number of open tables
Key_buffer_size//Key index cache size for MyISAM engine
Sort_buffer_size//Allocate a cache space of this size for each thread to be sorted
Read_buffer_size//cache size reserved for sequential reading of table records
Read_rnd_buffer_size//cache size reserved for reading table records by sort results
Thread_cache_size//Allow to save the number of threads that are reused in the cache
Table_open_cache//number of open tables cached for all threads

Key_buffer_size=8m
This cache value can be appropriately increased when the key_reads/key_read_requests is low
Mysql>show Global status like "key_read%";
Mysql>show variables like "key_buffer_size";

sort_buffer_size=256k
Increase this value to increase the speed of order and group
Mysql>show variables like "sort_buffer_size";

View table record Read cache
-This cache value affects the response speed of SQL queries
Mysql>show variables like "read_%_size";

To view reusable threads
Mysql>show variables like "thread_%_size";

View the current thread reuse status
Mysql>show Global status like "threads_%";

See how many tables have been opened and opened
Mysql>show Global status like "Open%tables";

See how many open tables can be cached
Mysql>show variables like "Table_open_cache";

SQL query optimization
MySQL log type, common log types and options:
Error log Log_error[=name]//Log error messages for the Enable/run/stop process
Query log general_log,general_log_file=//Record client connection and query operations
Slow query log slow_query_log,slow_query_log_file=,long_query_time=//record query operations that take a long time or do not use indexes

Log Slow query:
Slow-query-log//Enable slow query
Slow-query-log-file//Specify slow query log file
Long-query-time//queries that exceed a specified number of seconds (default 10 seconds) are logged
Log-queries-not-using-indexes//record queries that do not use indexes

To adjust the service configuration:
Vim/etc/my.cnf
[Mysqld]
...
Slow_query_log=1
Slow_query_log_file=mysql-slow.log
Long_query_time=5
Log_queries_not_using_indexes=1

Systemctl Restart Mysqld

View the slow query log, using the Mysqldumpslow tool
Mysqldumpslow/var/lib/mysql/mysql-slow.log

To view the size of the cache
Mysql>show variables like "query_cache%";

View the current query cache statistics
Mysql>show Global status like "qcache%";

MySQL Performance tuning