Flush old data from ibd files
-
- Posts: 271
- Joined: Tue Feb 11, 2014 2:04 pm
Flush old data from ibd files
Got 1.30.3 working, I've switched the database to use innodb_file_per_table. Right now I have no events showing up, the filesystem is empty for all the monitors. But when I look at the files in /var/lib/mysql/zm/ I see that Frames.ibd and Logs.ibd are 3 and 5 GB in size and Stats.ibd is about 240MB. Is there a recommended method to flushing the old data from at least Frames.ibd. There shouldn't be any frames to track since there are no events currently saved.
Re: Flush old data from ibd files
I would tend to want to find out what is in there. You say that there are no events, so there should be no frames...
so if you open a mysql shell...
What does
SELECT * from Frames;
give you?
If you don't care about the contents, then
DELETE FROM Frames;
Should clear it out.
I'd be interested in the out put of zmaudit...
so if you open a mysql shell...
What does
SELECT * from Frames;
give you?
If you don't care about the contents, then
DELETE FROM Frames;
Should clear it out.
I'd be interested in the out put of zmaudit...
Re: Flush old data from ibd files
From what I've read it is not uncommon for .idb files to stay large even when "empty." Reclaim space by running OPTIMIZE TABLE under MySQL
-
- Posts: 271
- Joined: Tue Feb 11, 2014 2:04 pm
Re: Flush old data from ibd files
I see year old frames showing up. I did a little googling later yesterday and I'm currently deleting old frames a little at a time while zoneminder is shut down. When I started I had over 30 million rows in the Frames table. Once I get it cleaned out I'll run the optimize process and see what happens. If that goes well I'm gonna probably do the same on the Logs and Stats tables.
iconnor: Maybe I caused this myself. On occasion I'll forget to check things in ZM and end up with a couple or three days of events. Deleting them via the web UI can be a bit tedious. So one method I use is to shut down ZM, Run a find for jpg files in the events subdirectory and rm them all. Then I run zmaudit I get a bunch of warnings that it couldn't find frames (or maybe images, can't quite recall the exact wording) and it then deletes the event. Does that not also delete related rows in the Frames table? There is a single SQL error message when it finishes but I can't recall what it says right off the top of my head.
iconnor: Maybe I caused this myself. On occasion I'll forget to check things in ZM and end up with a couple or three days of events. Deleting them via the web UI can be a bit tedious. So one method I use is to shut down ZM, Run a find for jpg files in the events subdirectory and rm them all. Then I run zmaudit I get a bunch of warnings that it couldn't find frames (or maybe images, can't quite recall the exact wording) and it then deletes the event. Does that not also delete related rows in the Frames table? There is a single SQL error message when it finishes but I can't recall what it says right off the top of my head.
-
- Posts: 271
- Joined: Tue Feb 11, 2014 2:04 pm
Re: Flush old data from ibd files
Got all the row in Frames deleted, ran the optimize table Frames and now Frames.ibd is about 147kB. Much better! Starting the same procedure on Logs now.
Re: Flush old data from ibd files
Much easier to set a filter to clean out the old events. Or, run a filter manually...BiloxiGeek wrote: ↑Mon May 08, 2017 12:55 pm Got all the row in Frames deleted, ran the optimize table Frames and now Frames.ibd is about 147kB. Much better! Starting the same procedure on Logs now.
-
- Posts: 271
- Joined: Tue Feb 11, 2014 2:04 pm
Re: Flush old data from ibd files
Just started up ZM, forced an alarm on one monitor just to have one event to play with. I see the appropriate entries in the Events and Frames tables. Then I deleted the event. The Events table now has no rows, but the Frames table still shows all the Frames.
Stopped ZM, ran zmaudit.pl and it deleted the events and gives this error:
Still have 740 rows in Frames with no associated Event.
Stopped ZM, ran zmaudit.pl and it deleted the events and gives this error:
Code: Select all
DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 2 at /usr/bin/zmaudit.pl line 384.
-
- Posts: 271
- Joined: Tue Feb 11, 2014 2:04 pm
Re: Flush old data from ibd files
Deleted 5 events using the web UI. Checking from mysql I see no events but 909 frames. Ran zmaudit and checked mysql again, same state. Events are deleted but frames are not. The frames themselves are deleted from the filesystem, just the reference in the Frames table remains.
Code: Select all
[root@zone zm]# zmaudit.pl
Argument "" isn't numeric in numeric gt (>) at /usr/bin/zmaudit.pl line 262.
Filesystem event '208/3' does not exist in database, deleting
Filesystem event '203/6' does not exist in database, deleting
Filesystem event '203/4' does not exist in database, deleting
Filesystem event '103/7' does not exist in database, deleting
Filesystem event '103/5' does not exist in database, deleting
DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 2 at /usr/bin/zmaudit.pl line 384.
[root@zone zm]# zmaudit.pl
DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 2 at /usr/bin/zmaudit.pl line 384.