Monitor::UpdateFPS() clobbers database every second

[alandpearson]

Describe Your Environment

• Version of ZoneMinder [v1.37.65, development ]
• How you installed ZoneMinder [e.g. .deb package for Debian bookworm]
• Full name and version of OS
• Browser name and version (if this is an issue with the web interface)

Describe the bug
In the current master branch zm_monitor.cpp Monitor::UpdateFPS() updates the database every second, causing high io when several cameras are in use. In previous versions (1.36 branch) updates were constrained to every ten seconds.
This causes large IO on the mysql transaction log.
Suggest ten-second behaviour is restored or, behaviour of the database update matches the FPSReportInterval in monitor->misc_>options ?

Either way, once a second seems a little aggressive.

To Reproduce
Steps to reproduce the behavior:

1. setup a monitor
2. enable mysql general_log to see all queries
3. notice every second, every monitor issuing UPDATE LOW_PRIORITY Monitor_Status SET statements

Expected behavior
Database should not be updates each second for monitor status

[welcome]

Thanks for opening your first issue here! Just a reminder, this forum is for Bug Reports only. Be sure to follow the issue template!

[connortechnology]

The code is actually set to only do it every 10 seconds per monitor... so that's weird.

[connortechnology]

We actually put the sql into a queue which gets processed in another thread.. so I suppose if there was a backlog we might see this...but then it could be a lot faster than 1/s and you would think it would catch up quickly. Are there warnings in the logs about a db queue backlog?

[connortechnology]

I have reproduced this, so I should be able to fix this soon.

[alandpearson]

Hi Issac, No, there are no warnings. Enabling the mysql general log with the current master code in github, shows the update statements happening once per second, per monitor. I recompiled the code and changed the sql update stanza to be in the same if-branch as the fps_report interval and it works as you had described in a reddit posting 2 years ago (reports and updates only on the frequency given by the fps_report_interval) ``` if ( fps_report_interval and ( !(shared_data->image_count%fps_report_interval) or ( (shared_data->image_count < 100) and !(shared_data->image_count%10) ) ) ) { Info("%s: %d - Capturing at %.2lf fps, capturing bandwidth %ubytes/sec Analysing at %.2lf fps", name.c_str(), shared_data->image_count, new_capture_fps, new_capture_bandwidth, new_analysis_fps); #if MOSQUITTOPP_FOUND if (mqtt) mqtt->send(stringtf("Capturing at %.2lf fps, capturing bandwidth %ubytes/sec Analysing at %.2lf fps", new_capture_fps, new_capture_bandwidth, new_analysis_fps)); #endif shared_data->capture_fps = new_capture_fps; last_capture_image_count = shared_data->image_count; shared_data->analysis_fps = new_analysis_fps; last_motion_frame_count = motion_frame_count; last_camera_bytes = new_camera_bytes; last_fps_time = now; std::string sql = stringtf( "UPDATE LOW_PRIORITY Monitor_Status SET Status='Connected', CaptureFPS = %.2lf, CaptureBandwidth=%u, AnalysisFPS = %.2lf, UpdatedOn=NOW() WHERE MonitorId=%u", new_capture_fps, new_capture_bandwidth, new_analysis_fps, id); dbQueue.push(std::move(sql)); } // end if fps_report_interval } // now != last_fps_time } // void Monitor::UpdateFPS() ```

…

On 30 Dec 2024, at 19:43, Isaac Connor ***@***.***> wrote: We actually put the sql into a queue which gets processed in another thread.. so I suppose if there was a backlog we might see this...but then it could be a lot faster than 1/s and you would think it would catch up quickly. Are there warnings in the logs about a db queue backlog? — Reply to this email directly, view it on GitHub <#4199 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AB7LLAVSSRDSCMG32DUVEI32IGO4NAVCNFSM6AAAAABUJLXH36VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDKNRVHA2TGOBRG4>. You are receiving this because you authored the thread.

[alandpearson]

Thank you Issac, our message overlapped, appreciated.

[connortechnology]

Apparently I was looking at the code in 1.36 which is every 10 seconds. I guess I never got around to doing the same in master. So now I have.

Honestly though I don't consider 1/s to be a significant load on the db. For event frame data etc we target 1/s for updates.

[alandpearson]

Thanks. Would it not be better to tie this to the frame update interval ?
You once commented on this reddit thread that is how it should work :

https://www.reddit.com/r/ZoneMinder/comments/127tfso/are_constant_repetitive_database_queries_normal/

On the once per second.. multiply that by all monitors (I have 15) and it’s enough to keep the hard drive light on solid on my system (granted no SSD).
I guess because you’re hitting an ACID complaint DB the overhead is more than you’d imagine.
But it makes sense to be as effecient as possible, so appreciate your attention to it.
I caught my attention as I was trying to debug the high IO (and iowaits) mysql was generating, and took a bit of sleuthing to find out why.

Turning this frequency down really improved my system responsive and dramatically dropped iowait.

Thanks again
Alan

[connortechnology]

The problem is that people set their update frequency to very large values. This table is like a heartbeat. It is how we know that things are still alive.

Initially I made it an in-ram table, but mysql's ram tables don't have row level locking so it became a bottleneck. You could re-create the table as a ram based and see how that goes.