large .ibd files

Forum for questions and support relating to the 1.34.x releases only.
Post Reply
zm_user1001
Posts: 9
Joined: Sat May 29, 2021 11:45 pm

large .ibd files

Post by 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
Posts: 9
Joined: Sat May 29, 2021 11:45 pm

Re: large .ibd files

Post by 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
Posts: 1172
Joined: Sat Aug 31, 2019 7:35 am
Location: San Diego

Re: large .ibd files

Post by dougmccrary »

I believe the truncate logs should work. DB hasn't changed that much.
It'll either work or throw an error.
zm_user1001
Posts: 9
Joined: Sat May 29, 2021 11:45 pm

Re: large .ibd files

Post by zm_user1001 »

dougmccrary wrote: Mon Jun 07, 2021 11:15 pm 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 :D
zm_user1001
Posts: 9
Joined: Sat May 29, 2021 11:45 pm

Re: large .ibd files

Post by 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
Posts: 1172
Joined: Sat Aug 31, 2019 7:35 am
Location: San Diego

Re: large .ibd files

Post by dougmccrary »

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

I set mine to 1000.
zm_user1001
Posts: 9
Joined: Sat May 29, 2021 11:45 pm

Re: large .ibd files

Post by zm_user1001 »

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