MySQL spiking CPU once a minute

Hey all,

I mentioned this issue here, but since it was not really related, I am creating a new topic with further findings.

image

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

I guess this is a query to show call summary in real time on dashboard.

Thanks, @miguel.

Question. Which process/script or cronjob controls this? Also, how come other PBX’s do not spike like that?