Hey all,
I mentioned this issue here, but since it was not really related, I am creating a new topic with further findings.
As you can see in the picture, there are two processes of mysqld that really causes this spike.
I monitored it today, it seems to be happening once a minute between :35 and :40 second mark.
I tried repairing and optimizing all databases, no difference.
So I looked in mysqladmin
This is how it looks normally:
[root@pbx1 ~]# mysqladmin pr -vvv
+-----+-------------+-----------+----------+---------+------+--------------------------+-----------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+-----+-------------+-----------+----------+---------+------+--------------------------+-----------------------+----------+
| 1 | system user | | | Daemon | | InnoDB purge coordinator | | 0.000 |
| 2 | system user | | | Daemon | | InnoDB purge worker | | 0.000 |
| 3 | system user | | | Daemon | | InnoDB purge worker | | 0.000 |
| 4 | system user | | | Daemon | | InnoDB purge worker | | 0.000 |
| 5 | system user | | | Daemon | | InnoDB shutdown handler | | 0.000 |
| 8 | asterisk | localhost | asterisk | Sleep | 2496 | | | 0.000 |
| 9 | vitalpbx | localhost | ombutel | Sleep | 3057 | | | 0.000 |
| 912 | root | localhost | | Query | 0 | Init | show full processlist | 0.000 |
+-----+-------------+-----------+----------+---------+------+--------------------------+-----------------------+----------+
And this is how it looks when it spikes:
[root@pbx1 ~]# mysqladmin pr -vvv
+-----+-------------+-----------+----------+---------+------+--------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+-----+-------------+-----------+----------+---------+------+--------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+
| 1 | system user | | | Daemon | | InnoDB purge coordinator | | 0.000 |
| 2 | system user | | | Daemon | | InnoDB purge worker | | 0.000 |
| 3 | system user | | | Daemon | | InnoDB purge worker | | 0.000 |
| 4 | system user | | | Daemon | | InnoDB purge worker | | 0.000 |
| 5 | system user | | | Daemon | | InnoDB shutdown handler | | 0.000 |
| 8 | asterisk | localhost | asterisk | Sleep | 2498 | | | 0.000 |
| 9 | vitalpbx | localhost | ombutel | Sleep | 3058 | | | 0.000 |
| 915 | ombutel | localhost | ombutel | Query | 0 | Sending data | select
`uniqueid`,
SUM(IF(`calltype` = 1, 1, 0)) AS 'internal',
SUM(IF(`calltype` = 2, 1, 0)) AS 'incoming',
SUM(IF(`calltype` = 3, 1, 0)) AS 'outgoing',
SUM(IF(`calltype` = 4, 1, 0)) AS 'transit'
from asterisk.cdr
where uniqueid between '1629777600' and '1629863999' and CHAR_LENGTH(tenant) > 0 group by hour(calldate) | 0.000 |
| 917 | root | localhost | | Query | 0 | Init | show full processlist | 0.000 |
+-----+-------------+-----------+----------+---------+------+--------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------±---------+
I tried checking the cron logs, I couldn’t spot anything that runs every minute and starts so late into the minute.
However, I do see the following in /var/log/messages
Aug 24 05:50:35 pbx1 systemd: Started Session c162 of user root.
Aug 24 05:50:35 pbx1 systemd: Started Session c163 of user root.
Aug 24 05:50:35 pbx1 dbus[447]: [system] Activating via systemd: service name='org.freedesktop.timedate1' unit='dbus-org.freedesktop.timedate1.service'
Aug 24 05:50:35 pbx1 systemd: Starting Time & Date Service...
Aug 24 05:50:35 pbx1 dbus[447]: [system] Successfully activated service 'org.freedesktop.timedate1'
Aug 24 05:50:35 pbx1 systemd: Started Time & Date Service.
Aug 24 05:51:35 pbx1 systemd: Started Session c164 of user root.
Aug 24 05:51:35 pbx1 systemd: Started Session c165 of user root.
Aug 24 05:51:35 pbx1 dbus[447]: [system] Activating via systemd: service name='org.freedesktop.timedate1' unit='dbus-org.freedesktop.timedate1.service'
Aug 24 05:51:35 pbx1 systemd: Starting Time & Date Service...
Aug 24 05:51:35 pbx1 dbus[447]: [system] Successfully activated service 'org.freedesktop.timedate1'
Aug 24 05:51:35 pbx1 systemd: Started Time & Date Service.
Aug 24 05:52:35 pbx1 systemd: Started Session c166 of user root.
Aug 24 05:52:35 pbx1 systemd: Started Session c167 of user root.
Aug 24 05:52:35 pbx1 dbus[447]: [system] Activating via systemd: service name='org.freedesktop.timedate1' unit='dbus-org.freedesktop.timedate1.service'
Aug 24 05:52:35 pbx1 systemd: Starting Time & Date Service...
Aug 24 05:52:35 pbx1 dbus[447]: [system] Successfully activated service 'org.freedesktop.timedate1'
Aug 24 05:52:35 pbx1 systemd: Started Time & Date Service.
Aug 24 05:53:35 pbx1 systemd: Started Session c168 of user root.
Aug 24 05:53:35 pbx1 systemd: Started Session c169 of user root.
Aug 24 05:53:35 pbx1 dbus[447]: [system] Activating via systemd: service name='org.freedesktop.timedate1' unit='dbus-org.freedesktop.timedate1.service'
Aug 24 05:53:35 pbx1 systemd: Starting Time & Date Service...
Aug 24 05:53:35 pbx1 dbus[447]: [system] Successfully activated service 'org.freedesktop.timedate1'
Aug 24 05:53:35 pbx1 systemd: Started Time & Date Service.
Let me know if you need more info.
Any help appreciated
Thank you