Debugging this, it appeared zma connections to mysql would timeout for some reason.zm.log:Nov 3 17:26:27 zmserver zma_m3[11232]: ERR [Can't insert event: MySQL server has gone away]
I also checked to see if the system was running something every day, via anacron (or cron), that was causing a load spike. Nope, that wasn't it.
After more research, I tried tuning mysql to see if it would help. I edited the mysql config file /etc/mysql/my.cnf to put these settings in place:
I left the "wait_timeout" to be the default (28800, which is 8hrs)key_buffer_size=32M
max_allowed_packet=16M
thread_cache_size=40
max_connections=256
skip-locking
I stopped zoneminder, restarted mysqld and then started zoneminder.
Did it fix the problem? Nope.
Darned if a few of the error messages would still appear every 24hrs. I've left the tunings as-is. The higher thread_cache is generally a good thing on a system with adequate ram and CPU.
Next I tried running mysqld in the "skip-name-resolve" mode. This can reduce db connection latencies because mysql will not waste time looking up the hostname associated with a db connection. Note that this may not work as expected in all mysql implementations. It worked for me because I have all the needed hostname/addresses in the /etc/hosts file on my server. The only way to activate this feature is to add it as an argument to the command that starts mysql at boot.
So, I edited /etc/init.d/mysql, changing this:
To this:case "${1:-''}" in
'start')
sanity_checks;
# Start daemon
log_daemon_msg "Starting MySQL database server" "mysqld"
if mysqld_status check_alive nowarn; then
log_progress_msg "already running"
log_end_msg 0
else
/usr/bin/mysqld_safe > /dev/null 2>&1 &
Basically, I just changed the /usr/bin/mysqld_safe line in the 'start' section.case "${1:-''}" in
'start')
sanity_checks;
# Start daemon
log_daemon_msg "Starting MySQL database server" "mysqld"
if mysqld_status check_alive nowarn; then
log_progress_msg "already running"
log_end_msg 0
else
/usr/bin/mysqld_safe --skip-name-resolve > /dev/null 2>&1 &
Did this eliminate the error messages? Nope.
Still saw them. But I did notice that all the mysql tuning rewarded me with a lower system load trend, so it wasn't a complete loss.
So, now it was time for me to dive into the source. I put a fix in place that eliminated the problem. I've seen no unwanted side effects in testing and prolonged use in production. The fix is pretty simple.
Within zm_monitor.cpp, I changed this:
to this:if ( image_count && !(image_count%fps_report_interval) )
{
fps = double(fps_report_interval)/(now.tv_sec-last_fps_time);
Info(( "%s: %d - Processing at %.2f fps", name, image_count, fps ));
last_fps_time = now.tv_sec;
The section of code is easy to find. Just look forif ( image_count && !(image_count%fps_report_interval) )
{
fps = double(fps_report_interval)/(now.tv_sec-last_fps_time);
Info(( "%s: %d - Processing at %.2f fps", name, image_count, fps ));
last_fps_time = now.tv_sec;
//
// Wake up connection
mysql_ping( &dbconn );
And then addlast_fps_time = now.tv_sec
after it.//
// Wake up connection
mysql_ping( &dbconn );
"last_fps_time = now.tv_sec" only appears once in zm_monitor.cpp.
In effect, it causes the mysql connection to be pinged periodically. The ping is done at the same time as the FPS report interval. Note that you have control over what the FPS report interval is via Zoneminder's options. Thus, whenever the FPS report interval happens, the monitor's mysql connection is pinged. In effect, this functions as a keepalive on the monitor's database connection. I believe this is in line with what the original developer expected; that the mysql connection setup by a monitor would remain as long as the monitor was alive. At least, that's what their code seems to assume.
So, did it work? Yes it did.
No more "MySQL server has gone away" error messages.
I prefer this solution over what other people have had to resort to: setting the global mysql "wait_timeout" number to something huge (days or months, some folks have set it to a year or more) so that no connection ever times out. I'm not convinced setting giant "wait_timeouts" really solves the problem right - especially on systems running other apps that use mysql (like my system).
My guess as to why some people see the "MySQL server has gone away" message and others don't is that it happens most often on implementations where there's many hours of no events. That's how it is for me. I have relatively long stretches of time when things are quiet and there's no activities within the zones at all.
Hope this helps others.
---
eric