zmaudit.pl is hammering mysql 100% cpu

Forum for questions and support relating to the 1.28.x releases only.
Post Reply
patpend
Posts: 43
Joined: Thu Mar 13, 2014 12:23 pm

zmaudit.pl is hammering mysql 100% cpu

Post by patpend » Tue Oct 27, 2015 10:17 pm

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
Posts: 2442
Joined: Mon Mar 26, 2012 11:40 am
Location: Pennsylvania

Re: zmaudit.pl is hammering mysql 100% cpu

Post by bbunge » Tue Oct 27, 2015 11:57 pm

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....

User avatar
knight-of-ni
Posts: 2229
Joined: Thu Oct 18, 2007 1:55 pm
Location: Shiloh, IL

Re: zmaudit.pl is hammering mysql 100% cpu

Post by knight-of-ni » Wed Oct 28, 2015 1:44 am

It's probably due to this, which has been fixed in the latest master:
https://github.com/ZoneMinder/ZoneMinder/issues/1085
Visit my blog for ZoneMinder related projects using the Raspberry Pi, Orange Pi, Odroid, and the ESP8266
All of these can be found at https://zoneminder.blogspot.com/

PacoLM
Posts: 967
Joined: Wed Dec 02, 2009 9:55 pm
Location: Spain

Re: zmaudit.pl is hammering mysql 100% cpu

Post by PacoLM » Wed Oct 28, 2015 8:31 am


mini-ITX system (6 IPCams - Ubuntu14.04 - ZM1.31.1)

User avatar
asker
Posts: 1085
Joined: Sun Mar 01, 2015 12:12 pm

Re: zmaudit.pl is hammering mysql 100% cpu

Post by asker » Wed Oct 28, 2015 2:23 pm

Yup this was due to a field not being indexed -- it is fixed in master. I suppose there will be a 108 soon?
--
My collection of ZoneMinder learnings:
https://wiki.zoneminder.com/Various_ZM_thoughts

User avatar
knight-of-ni
Posts: 2229
Joined: Thu Oct 18, 2007 1:55 pm
Location: Shiloh, IL

Re: zmaudit.pl is hammering mysql 100% cpu

Post by knight-of-ni » Wed Oct 28, 2015 2:32 pm

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`)
Visit my blog for ZoneMinder related projects using the Raspberry Pi, Orange Pi, Odroid, and the ESP8266
All of these can be found at https://zoneminder.blogspot.com/

PacoLM
Posts: 967
Joined: Wed Dec 02, 2009 9:55 pm
Location: Spain

Re: zmaudit.pl is hammering mysql 100% cpu

Post by PacoLM » Wed Oct 28, 2015 3:31 pm

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

Thanks!

PacoLM

mini-ITX system (6 IPCams - Ubuntu14.04 - ZM1.31.1)

User avatar
knight-of-ni
Posts: 2229
Joined: Thu Oct 18, 2007 1:55 pm
Location: Shiloh, IL

Re: zmaudit.pl is hammering mysql 100% cpu

Post by knight-of-ni » Wed Oct 28, 2015 3:41 pm

Whether or not you notice a difference is going to depend on your hardware and how big your Frames table is.
Visit my blog for ZoneMinder related projects using the Raspberry Pi, Orange Pi, Odroid, and the ESP8266
All of these can be found at https://zoneminder.blogspot.com/

PacoLM
Posts: 967
Joined: Wed Dec 02, 2009 9:55 pm
Location: Spain

Re: zmaudit.pl is hammering mysql 100% cpu

Post by PacoLM » Wed Oct 28, 2015 4:11 pm

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

mini-ITX system (6 IPCams - Ubuntu14.04 - ZM1.31.1)

patpend
Posts: 43
Joined: Thu Mar 13, 2014 12:23 pm

Re: zmaudit.pl is hammering mysql 100% cpu

Post by patpend » Thu Oct 29, 2015 1:03 am

+1 that made all the difference, thanks everyone.

tuxmos
Posts: 16
Joined: Fri Oct 30, 2015 11:13 am

Re: zmaudit.pl is hammering mysql 100% cpu

Post by tuxmos » Fri Oct 30, 2015 11:21 am

+1 :lol: :D
thanks. great help

SteveGilvarry
Posts: 418
Joined: Sun Jun 29, 2014 1:12 pm
Location: Melbourne, AU

Re: zmaudit.pl is hammering mysql 100% cpu

Post by SteveGilvarry » Sat Oct 31, 2015 2:22 pm

zmuser will need index permissions to apply .108, raised an issue to cover adding to README in distro folders.
Production Zoneminder 1.32.3
Random Selection of Cameras (Dahua and Hikvision)
More Dev VM's than I care to count.

Post Reply

Who is online

Users browsing this forum: No registered users and 4 guests