Filter makes CPU very busy

[Andyrh]

I have not seen a post about this.
When I run a simple filter to find events the CPU goes to 100% and the filter never returns. I left it over night.
mysql becomes the top CPU user, often using more than 2 cores. The DB drive becomes very busy, but varies. The console page shows a load of about 12, I have seen 25.

Is there some optimization that needs to be done to mysql? Or something else that I am missing?

I have restarted the system with no change.
Nothing interesting in the logs.
Tried Chrome and Edge.

This did not happen with 1.34. This system was upgraded from 1.34.

ZM 1.36.5
Ubuntu 20.04
Intel(R) Core(TM) i7-2700K CPU @ 3.50GHz
16GB RAM
SSD root drive

x PID %CPU Size Res Res Res Res Shared Faults Command x
x Used KB Set Text Data Lib KB Min Maj x
x 1119 198.0 5078832 232068 63116 1331228 0 3 2 mysqld x
x 181476 153.7 2442684 1854432 1340 1827376 0 78600 0zmc x
x 180855 100.5 2509220 523312 1340 2037208 0 41521 1408 zmc x
x 180860 98.0 1052516 524552 1340 703496 0 53 0 497 zmc x
x 180880 92.5 1632200 896704 1340 1345224 0 630128 558zmc x
x 181092 74.6 1518744 920880 1340 1052892 0 63750 3343 zmc x
x 180865 66.2 2455112 1093408 1340 1979960 0 3 4 zmc x

[dougmccrary]

And the simple filter is?

[Andyrh]

Should have included that.
A purge filter, limited to 10 results. (looking for the oldest events)
Also if I select the total events from the console it has the same result.

ZM8-9-21.JPG (57.61 KiB) Viewed 3447 times

[dougmccrary]

Oh. That.
ISTR this on 1.34, too. Be that as it may, looks like a bug when you run from the filter page.
It returns no matches when there are, and hangs when there aren't matches.
The filter works when run in background.

Seems to be semi-fixed in 1.37.1, based on a quick test, although I can't set the limit to anything but 100.

[iconnor]

Assuming that all events are stored in this storage area... this filter will look at ALL event rows.

You didn't say how many events you have but if it's a lot... then... this can be a lot of work.

You might consder adding a startdatetime option... or some other way allowing mysql to use an index.

[Andyrh]

There are ~430k events.
That will help.

Is there a list of the indexes or a way for me to see them. My DB knowledge is only to a level of how to make the disks for a DB super fast.

Thank you,

[burger]

There are ~430k events.
That will help.

Is there a list of the indexes or a way for me to see them. My DB knowledge is only to a level of how to make the disks for a DB super fast.

Thank you,

There are sometimes built in indexes (count of all entries) but a starttime (which I assume will make a type of virtual index based on the date provided) should be enough. Start with https://wiki.zoneminder.com/MySQL if you want to run commands manually to get an idea of what it's pulling from the DB. Something like (pseudocode) select * from events where datetime <= June 5th and monitor = 5.

[kitkat]

Is there a list of the indexes or a way for me to see them. My DB knowledge is only to a level of how to make the disks for a DB super fast.

In the Events table, these fields are indexed: Id, MonitorId, StorageId, StartDateTime, EndDateTime, EndDateTime.DiskSpace (composite index).

You might find phpMyAdmin useful if you want to avoid the MySQL CLI

(Probably 'apt-get install phpmyadmin' on Ubuntu)


e2a: There doesn't seem to be an index on the 'Archived' field,' which is a bit of a surprise given how often it's referenced, so adding one may help:

Code: Select all

mysql> ALTER TABLE Events ADD INDEX `Archived` (`Archived`);

[Andyrh]

If I add the archive index will that survive upgrades or will it need to be redone?

Thank you for the list.

[dougmccrary]

I fear it will not survive an update.
If you use a script to install, you could always add the index at the end...

[kitkat]

If I add the archive index will that survive upgrades or will it need to be redone?

My feeling is that it will probably survive.

I can see an update adding new indices or dropping known ones that are no longer useful, but dropping unknown one seems unlikely because the update would have to either delete all existing indices and then create all of the required ones or, it would have to look for any unknown indices and drop those.

[Andyrh]

With the new version out I will find out in a few weeks when I get time to upgrade.

[iconnor]

Assuming most of your events aren't archived, an archived index won't help much.

You might want to look at db tuning...

At the end of the day, because we are sorting we will have to look at all 340k rows... I can't think of any reasonable way of making this filter performant.

You should decide how many days of events you want and just delete events older than that date.

[Andyrh]

I have thought about the amount also. On my list of things to do is to install my RAID controller. At that point I am likely to reduce the RAID group by 1 disk, going from RAID 5 to RAID 1 with only 3TB of space instead of 6. For now searching with dates seems to resolve the problem.
DB tuning is not my thing, making disks fast to make up for DB problems is...

[iconnor]

Just run mysqltuner and do what it says.

Remember, the disk in question here is the system disk, where mysql stores it's work files and the actual zm db. Not where we store the video.