Page 1 of 1
zmaudit.pl is hammering mysql 100% cpu
Posted: Tue Oct 27, 2015 10:17 pm
by 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
Re: zmaudit.pl is hammering mysql 100% cpu
Posted: Tue Oct 27, 2015 11:57 pm
by 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....
Re: zmaudit.pl is hammering mysql 100% cpu
Posted: Wed Oct 28, 2015 1:44 am
by knight-of-ni
It's probably due to this, which has been fixed in the latest master:
https://github.com/ZoneMinder/ZoneMinder/issues/1085
Re: zmaudit.pl is hammering mysql 100% cpu
Posted: Wed Oct 28, 2015 8:31 am
by PacoLM
Re: zmaudit.pl is hammering mysql 100% cpu
Posted: Wed Oct 28, 2015 2:23 pm
by asker
Yup this was due to a field not being indexed -- it is fixed in master. I suppose there will be a 108 soon?
Re: zmaudit.pl is hammering mysql 100% cpu
Posted: Wed Oct 28, 2015 2:32 pm
by 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`)
Re: zmaudit.pl is hammering mysql 100% cpu
Posted: Wed Oct 28, 2015 3:31 pm
by 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
Re: zmaudit.pl is hammering mysql 100% cpu
Posted: Wed Oct 28, 2015 3:41 pm
by knight-of-ni
Whether or not you notice a difference is going to depend on your hardware and how big your Frames table is.
Re: zmaudit.pl is hammering mysql 100% cpu
Posted: Wed Oct 28, 2015 4:11 pm
by PacoLM
knnniggett wrote: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
Re: zmaudit.pl is hammering mysql 100% cpu
Posted: Thu Oct 29, 2015 1:03 am
by patpend
+1 that made all the difference, thanks everyone.
Re: zmaudit.pl is hammering mysql 100% cpu
Posted: Fri Oct 30, 2015 11:21 am
by tuxmos
+1
thanks. great help
Re: zmaudit.pl is hammering mysql 100% cpu
Posted: Sat Oct 31, 2015 2:22 pm
by SteveGilvarry
zmuser will need index permissions to apply .108, raised an issue to cover adding to README in distro folders.