1

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
6
  • 1
    MySQL 5.7 is reaching end of life 7 months from now. I think it's a good time to upgrade. Commented Mar 21, 2023 at 16:02
  • Please post TEXT results of SHOW FULL PROCESSLIST; at the 8 minute mark of the 10 minutes of high load for analysis. Could you tell us WHERE the host is located? If you could browse to myip.ms and run their utility for your URL, they would tell you how many servers are sharing your IP address - that may be a clue. Commented Mar 22, 2023 at 13:54
  • The procs r column is reporting the number of processes waiting to run. b column is number of sleeping tasks. Looking forward to getting the info posted from request of 13 minutes ago. Commented Mar 22, 2023 at 14:07
  • Sounds like a cron job. MySQL has no regular tasks. See what Prometheus does. Capture SHOW FULL PROCESSLIST at 'high load'.
    – Rick James
    Commented Mar 30, 2023 at 16:42
  • use root to run "show full processlist" is no difference even during peak time,and There is no negative impact either. strange thing....
    – Mr.spark
    Commented Apr 3, 2023 at 8:47

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.