excessive logging and cannot delete database log entries

[patmansf]

Hi -

Running zoneminder on current Fedora 33

[edited to fix the version of Zoneminder I'm using]:

Zoneminder 1.36.1-3.fc33 rpm from rpmfusion-free-updates-testing

It looks like there is no way to manually delete database log entries via the web UI, and the auto delete of them is failing because there are too many database log entries.

What SQL commands can I use to manually delete zoneminder database log entries from the command line?

Some details:

After updating to 1.36, I ran out of disk space due to excessive logging.

I don't have the older set of log messages that caused my first out of space problems, as I had to delete them to recover and I can't use zm to get DB logs and getting DB settings is really slow, but I think it was the number of frames or such and processing not being able to keep up.

I disabled those cameras as I wanted to get the system back in working order, and I can get the exact logs later (and plan to fix whatever settings are bad) once I get the main system back and working without excessive logging.

But then there was another set of errors being logged excessively, these about 10 per second:

Code: Select all

Jun 11 07:40:47 specu zmc_m11[1158613]: ERR [zmc_m11] [Unable to free up older packets.  Not queueing this video packet.]

I was able to see logs at one point, and there were about 19 million entries.

I changed the logging option to save 1 million rows, but now I'm getting these failures about every 5 minutes, it looks like it's failing to delete the database log entries because there are too many database log entries

Code: Select all

Jun 11 08:40:17 umbrella zmstats[9023]: ERR [Failed DELETE low_priority FROM `Logs` ORDER BY `TimeKey` ASC LIMIT '18119067' :Lock wait timeout exceeded; try restarting transaction]

Thanks ...

[ergamus]

You need to increase the "Maximum Image Buffer Size" for whatever cameras are returning those log errors. You'll most likely have to allocate more memory and expect more CPU usage for recording/analysis than previously on 1.34. Another thing that might help if you're using Motion Detection is to increase the Analysis FPS beyond your cameras recording FPS. That allows the analysis process to speed ahead and catch up with the buffer if analysis is blocked temporarily because of I/O, CPU usage, DB contention, etc.

Are the logs being deleted at all, do you see any activity in your DB?

I highly recommend you skip the outdated RPMFusion packages and use https://koji.rpmfusion.org/koji/package ... kageID=501 to download 1.36.3 (or 1.36.4 if the build arrives). 1.36.2 and above has a bugfix related to the log filter only deleting 100 entries per run/loop: https://github.com/ZoneMinder/zoneminde ... 2c028b4274

If you want to use the above link to install a newer version of ZM, you select for example zoneminder-1.36.3-1.fc33, download the RPMs for:
zoneminder-1.36.3-1.fc33.x86_64.rpm
zoneminder-common-1.36.3-1.fc33.x86_64.rpm
zoneminder-httpd-1.36.3-1.fc33.x86_64.rpm (or the nginx version if you use it)
and upgrade the installed packages with: rpm -Uv zoneminder-*.rpm

Just make sure to stop the zoneminder, httpd/nginx process before you do this, and run zmupdate.pl afterwards to upgrade the DB version.

[patmansf]

You need to increase the "Maximum Image Buffer Size" for whatever cameras are returning those log errors. You'll most likely have to allocate more memory and expect more CPU usage for recording/analysis than previously on 1.34. Another thing that might help if you're using Motion Detection is to increase the Analysis FPS beyond your cameras recording FPS. That allows the analysis process to speed ahead and catch up with the buffer if analysis is blocked temporarily because of I/O, CPU usage, DB contention, etc.

Are the logs being deleted at all, do you see any activity in your DB?

I highly recommend you skip the outdated RPMFusion packages and use https://koji.rpmfusion.org/koji/package ... kageID=501 to download 1.36.3 (or 1.36.4 if the build arrives). 1.36.2 and above has a bugfix related to the log filter only deleting 100 entries per run/loop: https://github.com/ZoneMinder/zoneminde ... 2c028b4274

If you want to use the above link to install a newer version of ZM, you select for example zoneminder-1.36.3-1.fc33, download the RPMs for:
zoneminder-1.36.3-1.fc33.x86_64.rpm
zoneminder-common-1.36.3-1.fc33.x86_64.rpm
zoneminder-httpd-1.36.3-1.fc33.x86_64.rpm (or the nginx version if you use it)
and upgrade the installed packages with: rpm -Uv zoneminder-*.rpm

Just make sure to stop the zoneminder, httpd/nginx process before you do this, and run zmupdate.pl afterwards to upgrade the DB version.

Thanks for the input.

I entered the wrong version, I'm already at 1.36.3 via the rpmfusion testing repo.

There is no zm DB activity I can see other then the timeout I included, I was trying to find the Logs rows but nothing is showing up, though the query for zones worked as expected, and Logs file for the DB is huge.

Maybe my DB is corrupted?

This:

Code: Select all

MariaDB [zm]> SELECT count(*) from Logs;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.000 sec)

MariaDB [zm]> show columns in Logs;
+-----------+----------------------+------+-----+---------+----------------+
| Field     | Type                 | Null | Key | Default | Extra          |
+-----------+----------------------+------+-----+---------+----------------+
| Id        | int(10) unsigned     | NO   | PRI | NULL    | auto_increment |
| TimeKey   | decimal(16,6)        | NO   | MUL | NULL    |                |
| Component | varchar(32)          | NO   |     | NULL    |                |
| ServerId  | int(10) unsigned     | YES  |     | NULL    |                |
| Pid       | int(10)              | YES  |     | NULL    |                |
| Level     | tinyint(3)           | NO   | MUL | NULL    |                |
| Code      | char(3)              | NO   |     | NULL    |                |
| Message   | text                 | NO   |     | NULL    |                |
| File      | varchar(255)         | YES  |     | NULL    |                |
| Line      | smallint(5) unsigned | YES  |     | NULL    |                |
+-----------+----------------------+------+-----+---------+----------------+
10 rows in set (0.001 sec)

MariaDB [zm]> SELECT count(*) from Zones;
+----------+
| count(*) |
+----------+
|       15 |
+----------+
1 row in set (0.000 sec)

Code: Select all

# ls -lh /var/lib/mysql/zm/Logs.*
-rw-rw----. 1 mysql mysql 2.9K Jan 25  2020 /var/lib/mysql/zm/Logs.frm
-rw-rw----. 1 mysql mysql 5.1G Jun 11 09:09 /var/lib/mysql/zm/Logs.ibd

[patmansf]

I ran

Code: Select all

mysqlcheck

and it says all tables are "OK".

[patmansf]

Now I'm not sure what's happening, I started zm and it added one log entry, and now I can see that one entry in the DB but the DB Logs.ibd is still 5 GiB.

Let me look for some sort of way to shrink the corresponding file.

[ergamus]

You might need to restart your DB server, at least for MySQL & MariaDB the table sizes aren't shrunk until you restart.

[patmansf]

Even after a reboot the DB Logs.ibd is still 5.1G.

Given the extent of the problems (excessive logging, requires tuning each source, then uses more CPU and memory, and then there is a memory leak), I am going to to try and downgrade back to 1.34.

[ergamus]

Hmm, I was wrong about a couple of things after reading up some more. Looks like you have to optimize the table, which causes it to be rebuilt to reduce size:
https://forums.percona.com/t/how-to-red ... ize/1410/3

And I was also wrong about 1.36.2/1.36.3 containing a fix for log deletion, it's only just mentioned in 1.36.4:
https://github.com/ZoneMinder/zoneminde ... tag/1.36.4

The mysqlcheck command has a parameter for optimizing tables in a db:
• --optimize, -o

sudo mysqlcheck -o zm
...
zm.Events_Week
note : Table does not support optimize, doing recreate + analyze instead
status : OK
zm.Filters
note : Table does not support optimize, doing recreate + analyze instead
status : OK
...

Hope that helps until you figure out how to downgrade.