I have a CentOS 7 server with MySQL 5.7.4 installed. The machine has 8 cores and 16 GB of RAM, and only the MySQL service is running. The database is not very large, and there are not many queries. Normally, the server load is around 0.x. However, recently, I observed that every hour, the server load increases to about 10, and this lasts for about 10 minutes. The business department says that there are no scheduled tasks during this time, and during high load, the business system does not show any obvious problems.
MySQL uses InnoDB, and the pool size is set to 10 GB. When the load is high, I logged in to MySQL with the root account and ran 'show processlist', but there were only a few queries, and the QPS was only about a dozen on average according to Prometheus. Other metrics did not show any significant differences.
During this time, I used tools such as iostat/iotop to check for high I/O, but there were no significant differences compared to normal operation. Memory usage was also normal, with only 3-4 GB used out of 16 GB. The InnoDB pool hit rate and page free metrics were also normal. I enabled slow query logging with 'set global long_query_time=1', but there were no slow queries logged.
The only abnormality I could observe was that the 'r' value reported by vmstat was around 10 during high load. Other metrics showed no significant differences compared to normal operation.
please advise,thanks!
normal state
procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----
r b swpd free buff cache si so bi bo in cs us sy id wa st
1 0 0 5667436 5440 7967916 0 0 0 2 0 0 8 0 92 0 0
0 0 0 5667536 5440 7967916 0 0 0 0 3013 226 18 0 82 0 0
0 0 0 5667520 5440 7967916 0 0 0 0 2918 213 17 0 83 0 0
0 0 0 5667544 5440 7967916 0 0 0 0 3097 291 18 0 82 0 0
1 0 0 5667596 5440 7967916 0 0 0 0 3626 180 22 0 78 0 0
0 0 0 5667596 5440 7967916 0 0 0 0 3822 240 22 0 78 0 0
high load state
procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----
r b swpd free buff cache si so bi bo in cs us sy id wa st
14 0 0 5666088 5440 7967860 0 0 0 2 0 0 8 0 92 0 0
12 0 0 5665908 5440 7967860 0 0 0 0 4612 340 24 0 76 0 0
10 0 0 5666032 5440 7967860 0 0 0 0 3055 318 17 0 83 0 0
SHOW FULL PROCESSLIST
at 'high load'.