zmaudit.pl is hammering mysql 100% cpu

[patpend]

Newly set-up system, running 1.28.107 in a docker container, 5 IP cameras, 4 core athlon 3.8GHz with 16gb ram. After running for a few days, mysqld is hitting 100% cpu almost constantly. Mysql processlist reveals this suspect:

Code: Select all

mysql> show full processlist;
+-------+--------+-----------+------+---------+------+-------+-----------------------+
| Id    | User   | Host      | db   | Command | Time | State | Info                  |
+-------+--------+-----------+------+---------+------+-------+-----------------------+
   .
   .
   .
| 74131 | zmuser | localhost | zm   | Query   |  446 | Sending data | SELECT * FROM Events as E LEFT JOIN Frames as F ON (E.Id = F.EventId)
                                WHERE isnull(F.EventId) AND now() - interval 86400 second > E.StartTime | 
   .
   .
   .

Which as it turns out is coming from zmaudit.pl. I disabled auditing, restarted zm, and mysqld is behaving nice again. My question is what should I be looking at to figure out why zmaudit.pl is hitting so hard? Are there any reasons I shouldn't just leave it disabled for the long term?

Thanks,
patpend

[bbunge]

Had the same issue with MySQL 5.5 on Ubuntu 14.04-3 after doing an upgrade from 1.28.1 to 1.28.107. Without going into gory details I removed MySQL 5.5 and installed MySQL 5.6 and reinstalled ZM 1.28.107. System has been running for over a week with less load than before. Never did figure out what happened....

[knight-of-ni]

It's probably due to this, which has been fixed in the latest master:
https://github.com/ZoneMinder/ZoneMinder/issues/1085

[PacoLM]

Same thing:

http://www.zoneminder.com/forums/viewto ... 957#p89957

PacoLM

[asker]

Yup this was due to a field not being indexed -- it is fixed in master. I suppose there will be a 108 soon?

[knight-of-ni]

If you don't want to wait for a 1.28.108 package to roll out, you can fix it yourself with the following sql query:

Code: Select all

CREATE INDEX `EventId_idx` ON `Frames` (`EventId`)

[PacoLM]

Just ran the sql query and restarted Zoneminder, no big difference noticed in few minutes after restarting the server. Will post results!

Thanks!

PacoLM

[knight-of-ni]

Whether or not you notice a difference is going to depend on your hardware and how big your Frames table is.

[PacoLM]

Whether or not you notice a difference is going to depend on your hardware and how big your Frames table is.

It made a HUGE difference, load was between 6-8, now returned to the usual 0.9-2.0. Also the CPU-eaters mysqld processes have lowered in usage.

Many thanks!

PacoLM

[patpend]

+1 that made all the difference, thanks everyone.

[tuxmos]

+1
thanks. great help

[SteveGilvarry]

zmuser will need index permissions to apply .108, raised an issue to cover adding to README in distro folders.