large .ibd files

[zm_user1001]

Hi,

Noticed my Ubuntu machine running ZoneMinder 1.34.9 was out of disk space in root. Looking at larger files, I see that /var/lib/mysql/zm/Stats.idb is 500M, Frames.idb is 1.1G and Logs.idb is 6.8G.

I had left logging set to default (7 days), so I went back and changed it to 1. I've tried going to the log screen and using the clear/reset buttons to clear the table with no luck. I've also tried rebooting the server to see if that would trigger house keeping processes for cleanup, but agian, no luck.

Is there a way to cleanup the logs .db at a minimum in 1.34.9 and ideally the Frames.idb?

[zm_user1001]

I was able to reclaim some of the space by changing the setting on frame deletion from based on disk space to based on age then running an optimize table on Frames, however, I'm still unable to clear the log as optimize fails (guessing due to disk space, 2GB free, table is 6GB).

Checking the wiki, there's no information on deleting/recreate tables:

https://wiki.zoneminder.com/General_Not ... s.2FTables

but, I did find this info from 8 years ago and was wondering if it's still valid for 1.34:
viewtopic.php?t=20758

[dougmccrary]

I believe the truncate logs should work. DB hasn't changed that much.
It'll either work or throw an error.

[zm_user1001]

I believe the truncate logs should work. DB hasn't changed that much.
It'll either work or throw an error.

Yeah, my concern isn't that truncate won't work, it's that it'll cause problems for zonemineder, since I'm yanking data out from under it.

I'll give it a shot, there's nothing I can't live without and it'll teach me to include a weekly table optimization

[zm_user1001]

Truncate worked, still have a function video system. Thanks Doug.

For anyone googling the solution in the future, to reduce the frames and/or logs tables:

viewtopic.php?t=19014

I had set the filter to delete oldest videos when the file system they're writing to hit 80%. I changed the filter to delete all files over 30 days. After the purge completed, I logged into the server and ran the following commands:

mysql
use zm;
optimize table Frames;
exit;

To clean up the logs, I tried to optimize table Logs, but it was too large and failed, instead I ran:

mysql
use zm;
truncate logs;

When I was done, the root file system was down from 93% to 40% utilization. The file system I store video in was down from 80% to 56%.

[dougmccrary]

Cool.
You know you can limit the logs in Options->Logging->LOG_DATABASE_LIMIT

I set mine to 1000.

[zm_user1001]

Interesting, mine was set to 1 day... Changed the value to 1000, we'll see what that does for it