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

This article mainly tells you briefly how to tune the performance of MySQL. You can look up the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here, so let's go straight to the topic. I hope this article on how to tune the performance of MySQL can bring you some practical help.

MySQL performance tuning

Improve the performance and influence speed of MySQL system

-replace hardware (CPU/ disk / memory, etc.)

-adjust the running parameters of the service program

-optimize SQL queries

Concurrency and connection control

-number of connections, connection timeout

Max_connections / / maximum number of concurrent connections allowed

Connect_timeout / / timeout seconds to wait for a connection to be established. Default is 10 seconds, which is valid only when logging in.

Wait_timeout / / the number of inactive timeouts waiting for the connection to be closed. Default is 28800 seconds (8 hours)

-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

Cache area, number of threads, number of open tables

Key_buffer_size / / key index cache size for the MyISAM engine

Sort_buffer_size / / allocate this size of cache space for each thread to be sorted

Read_buffer_size / / cache size reserved for sequential read table records

Read_rnd_buffer_size / / the cache size reserved for reading table records by sort result

Thread_cache_size / / the number of threads that are allowed to be reused in the cache

Table_open_cache / / number of open tables cached for all threads

Key_buffer_size=8M

When the key_reads/key_read_requests is low, the cache value can be increased appropriately.

Mysql > show global status like "key_read%"

Mysql > show variables like "key_buffer_size"

Sort_buffer_size=256K

Increasing this value can 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"

View reusable threads

Mysql > show variables like "thread_%_size"

View current thread reuse status

Mysql > show global status like "threads_%"

Check 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 types, commonly used log types and options:

Error log log_error [= name] / / record error messages for the enable / run / stop process

Query log general_log,general_log_file= / / record client connections 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

Record slow query:

Slow-query-log / / enable slow query

Slow-query-log-file / / specify slow query log file

Only queries with long-query-time / / exceeding the specified number of seconds (default 10 seconds) are recorded.

Log-queries-not-using-indexes / / record queries that do not use indexes

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 and use the mysqldumpslow tool

Mysqldumpslow / var/lib/mysql/mysql-slow.log

View the size of the cache

Mysql > show variables like "query_cache%"

View current query cache statistics

Mysql > show global status like "qcache%"

How to tune the performance of MySQL will first tell you here, for other related issues you want to know can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

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.