Very slow query for the console

[maron]

Hello!

I have a system consisting of 13 cameras. The query that runs to create the console view is bogging down the system (since this refreshes every now and then). It takes around 40-60 seconds to run each time.

My system specs are as follows:
Pentium 2.8 Ghz
1 GB RAM
400GB storage (stripe raid) with reiserfs filesystem.
Gentoo Linux.
250.000 events currently stored consisting of 16.316.526 frames.

This is the query that runs:

Code: Select all

select M.*, count(if(E.Archived=0,1,NULL)) as EventCount, count(if(E.Archived,1,NULL)) as ArchEventCount, count(if(E.StartTime>'2004-09-01 12:09:00' - INTERVAL 1 HOUR && E.Archived = 0,1,NULL)) as HourEventCount, count(if(E.StartTime>'2004-09-01 12:09:00' - INTERVAL 1 DAY && E.Archived = 0,1,NULL)) as DayEventCount, count(if(E.StartTime>'2004-09-01 12:09:00' - INTERVAL 7 DAY && E.Archived = 0,1,NULL)) as WeekEventCount, count(if(E.StartTime>'2004-09-01 12:09:00' - INTERVAL 1 MONTH && E.Archived = 0,1,NULL)) as MonthEventCount from Monitors as M left join Events as E on E.MonitorId = M.Id group by M.Id order by M.Id

During the query the mysqld process can get up to 85% CPU.


Anybody else experiencing this?

[fernando]

yes if you have much cameras and whit much events this could be a problem
since console display all the events, hourly events, daily event, weekly events and monthly events all by camera. not dificult to see that the mysql engine will have much to think about.

[zoneminder]

It is likely that the current default console view will be replaced in future versions. One of the reasons is that this query is unecessarily long and probably not all that useful. You can change it to a different view now by modifying the default setting of $view in zm_html.php however I am planning a 'console lite' view to which will be similar to the existing one but without the heavyweight queries..

Phil,