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
3
  • sysload normal state load average: 0.05, 0.08, 0.07
    – Mr.spark
    Commented Mar 22, 2023 at 1:16
  • sysload normal state load average: 0.05, 0.08, 0.07 sysload abnormal state load average load average: 11.29, 11.64, 8.52 can see mysql qps connection metric here (the latest started at 8:50)i.328888.xyz/2023/03/22/aLoya.png
    – Mr.spark
    Commented Mar 22, 2023 at 1:22
  • system proc state systemd─┬─NetworkManager───2*[{NetworkManager}] ├─agetty ├─auditd───{auditd} ├─crond ├─dbus-daemon ├─firewalld───{firewalld} ├─irqbalance ├─lvmetad ├─mysql_alarm───17*[{mysql_alarm}] ├─mysqld───74*[{mysqld}] ├─polkitd───5*[{polkitd}] ├─rsyslogd───3*[{rsyslogd}] ├─sshd───sshd───bash───pstree ├─systemd-journal ├─systemd-logind ├─systemd-udevd ├─tuned───4*[{tuned}] ├─vmtoolsd───{vmtoolsd} └─zabbix_agentd───5*[zabbix_agentd]
    – Mr.spark
    Commented Mar 22, 2023 at 1:27

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.