Sighthound using 100% CPU during SLOW SQL fetchall

[samimob]

Greetings, I have Sighthound running a large number of cameras on a powerful multi processor server with lots of RAM.

Recently I have started to encounter an issue with one of the Sighthound Video processes. Every ~5 minutes, the Sighthound front end interface freezes e.g. if I am in the middle of searching, everything would freeze temporarily for around 15 seconds then all goes back to normal. During the freeze time, all cameras are still operational, it is just the front end interface that seems to freeze.

I am certain the issue is nothing do to with the number of cameras as the server itself has a lot of spare capacity both in processing and RAM. After further investigation, the problem has been traced to the following fetchall log entry in the Diskcleaner:

After further investigation and checking the logs:
<---
2018-06-06 12:56:28,313 - INFO - *** - Reached time limit while cleaning cache.
2018-06-06 12:56:30,238 - INFO - ***.- run - Starting cleanup loop
2018-06-06 12:56:31,791 - INFO - *** - Cache purge - was 1780834, now 646108
2018-06-06 12:56:46,038 - WARNING - *** - fetchall - SLOW SQL fetchall (11.4): ('SELECT filename, camLoc, firstMs, lastMs FROM clips WHERE isCache=0 ORDER BY firstMs',) {}
2018-06-06 12:56:46,058 - INFO - *** - Querying list of size 648562
2018-06-06 12:56:46,992 - INFO - *** - Querying list of size 3145835

--->

It seems that whenever the fetchall query above takes place, the front end interface freezes temporarily. I was running 5.17 and downgraded to 5.16 to see if this is a new bug but it doesn't seem to be the case.

It seems that the archive is just getting too big somehow. I have the system running on 4TB clips + temp video and 48 hours temp. I have tried to tweak these settings to see if it makes a difference with no luck.

Can you please let me know if there is some cleanup tool/script or way to clear up any backlog that could have accumulated due to the size of the installation? I am certain the issue is related to the above query. I prefer not to delete the whole archive if possible. I look forward to hearing your thoughts on possible remedies.

Thank you for your time.

[samimob]

bump

@Devin would appreciate any thoughts or things to try. Thanks!

[kgonzalez]

Hey there,

I would recommend sending in a bug report to Sighthound Support. This will give the team a better insight of what might be occurring within in your system. However, without knowing much of your system spec's or how many cameras, you have configured in Sighthound the freezing could be results of different factors. One example could be that the system is struggling to keep up with the Sighthound processes which in turn may result in queueing delays.

Regards,
Kenneth

[Galm_OneF15]

The previous message was spam, but if someone else runs into that issue, it'll be because of hardware limitations. I had this problem all the time, and it suddenly disappeared when I got a computer that was three time as powerful