This article analyzes the problem of MySQL database server queries gradually becoming slower and proposes corresponding solutions. The specific analysis and solutions are as follows:
It is often found that developers run statements without indexes or without a LIMIT n clause. These statements can greatly impact the database, for example, full scans on large tables with tens of millions of records, or continuous filesort operations, causing IO issues for the database and server. This is the situation on the mirror database.
On the production database, besides statements without indexes or LIMIT clauses, there is an additional problem: too many MySQL connections. Speaking of which, let’s first look at our previous monitoring practices
1. Deploy open-source distributed monitoring systems like Zabbix to capture daily database IO, CPU, and connection counts
2. Deploy weekly performance statistics, including data growth, iostat, vmstat, and datasize status
3. Collect MySQL slowlog, listing the top 10
Previously, I thought having these monitoring setups was perfect. Now, after deploying MySQL node process monitoring, many drawbacks have been discovered
Drawbacks of the first method: Zabbix is too large, and since monitoring is not done inside MySQL, much of the data is not very accurate. It is now mainly used to review historical data
Drawbacks of the second method: Since it runs only once a week, many situations cannot be detected or alerted on
Drawbacks of the third method: When a node has a very large number of slow logs, the top 10 becomes meaningless, and often it gives you those scheduled task statements that must run. The reference value is limited
So how do we solve and investigate these problems?
For troubleshooting and identifying performance bottlenecks, the easiest problems to find and resolve are MySQL slow queries and queries that do not use indexes.
OK, let’s start finding the SQL statements in MySQL that don’t run “smoothly.”
Method 1: I am currently using this method, hehe, I quite like its immediacy.
| 01 |
MySQL version 5.0 and above can support recording SQL statements that execute relatively slowly. |
| 02 |
mysql> show variables like 'long%'; Note: This long_query_time defines how many seconds a query must exceed to be considered a “slow query.” |
| 04 |
| Variable_name | Value | |
| 06 |
| long_query_time | 10.000000 | |
| 08 |
1 row in set (0.00 sec) |
| 09 |
mysql> set long_query_time=1; Note: I set it to 1, meaning any execution time exceeding 1 second is considered a slow query. |
| 10 |
Query OK, 0 rows affected (0.00 sec) |
| 11 |
mysql> show variables like 'slow%'; |
| 13 |
| Variable_name | Value | |
| 15 |
| slow_launch_time | 2 | |
| 16 |
| slow_query_log | ON | Note: Whether logging is turned on |
| 17 |
| slow_query_log_file | /tmp/slow.log | Note: Location where it is set |
| 19 |
3 rows in set (0.00 sec) |
| 20 |
mysql> set global slow_query_log='ON' Note: Turn on logging |
| 21 |
Once the slow_query_log variable is set to ON, MySQL will start recording immediately. |
| 22 |
The initial values for the above MySQL global variables can be set in /etc/my.cnf. |
| 24 |
slow_query_log_file=/tmp/slow.log |
Method 2: The mysqldumpslow command
| 01 |
/path/mysqldumpslow -s c -t 10 /tmp/slow-log |
| 02 |
This will output the 10 SQL statements with the highest record count, where: |
| 03 |
-s specifies the sort order: c, t, l, r sort by count, time, query time, and returned records respectively; ac, at, al, ar indicate the corresponding descending order; |
| 04 |
-t means top n, i.e., return the first n rows of data; |
| 05 |
-g can be followed by a case-insensitive regex pattern; |
| 07 |
/path/mysqldumpslow -s r -t 10 /tmp/slow-log |
| 08 |
Gets the 10 queries with the largest returned record sets. |
| 09 |
/path/mysqldumpslow -s t -t 10 -g “left join” /tmp/slow-log |
| 10 |
Gets the top 10 queries sorted by time that contain left join statements. |
Finally, let’s summarize the benefits of node monitoring
1. Lightweight monitoring, real-time, and customizable based on actual conditions
2. A filtering program can be set up to filter out those statements that must run
3. Timely detection of queries that do not use indexes or are not valid. Although handling these slow statements takes time, it prevents the database from crashing, which is worthwhile
4. When the database experiences too many connections, the program automatically saves the current database processlist. This is a powerful tool for DBAs when performing root cause analysis
5. Using mysqlbinlog for analysis provides clear time periods of abnormal database status
Some people recommend adjusting MySQL configuration file settings
While adjusting tmp_table_size, I found some other parameters
Qcache_queries_in_cache Number of queries registered in the cache
Qcache_inserts Number of queries added to the cache
Qcache_hits Number of cache hits
Qcache_lowmem_prunes Number of queries removed from the cache due to lack of memory
Qcache_not_cached Number of queries not cached (cannot be cached, or due to QUERY_CACHE_TYPE)
Qcache_free_memory Total free memory for query cache
Qcache_free_blocks Number of free memory blocks in the query cache
Qcache_total_blocks Total number of blocks in the query cache
Qcache_free_memory can cache some commonly used queries. If commonly used SQL is loaded into memory, it will increase database access speed.
