Page 1 of 1

Where is db cleanup and maintenance info

Posted: Thu Sep 06, 2012 12:14 am
by duanes1967
I need instructions on how to drop (or at least delete large portions) of the historical data.

The forum mentions a thread on database maintenance and cleanup procedures followed by the use of vacuum, but I cannot locate that thread.

This 2gb issue really needs some attention.

Re: Where is db cleanup and maintenance info

Posted: Thu Sep 06, 2012 1:01 am
by lebowski
I wrote these instructions long ago...

--------------
An easy way to vacuum the database is to copy dude.db and sqlite3.exe into a new folder "somewhere other than the dude folder" this will let you modify a copy of dude.db instead of the original. Make a shortcut to sqlite3.exe, edit the properties of the shortcut so that dude.db is on the command line.

"C:\Documents and Settings\person\Desktop\sqlite3.exe" dude.db

Then double click the shortcut, type VACUUM; A journal file will be created while it is vacuuming...
--------------

Here is a screenshot of the process...
vacuum.png

Re: Where is db cleanup and maintenance info

Posted: Thu Sep 06, 2012 4:28 pm
by duanes1967
I REALLY appreciate the prompt response... Thanks.

From the other post, it appeared that there might be a more in depth process.

That said, I get a Primary key error as follows:

SQLite version 3.7.14 2012-09-03 15:42:36
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> vacuum;
Error: PRIMARY KEY must be unique
sqlite>


I don't know where to turn other than completely remove all data but I have a considerable number of items being watched. Is there a table description or database dictionary anywhere ?

Re: Where is db cleanup and maintenance info

Posted: Fri Sep 07, 2012 2:13 am
by lebowski
Wow primary key is corrupt you will need to go Google how to make it unique (although I know nothing about this), the database is also compressed so you need to extract it,some where on here someone mentions the tool they used... probably easier to start over or find a backup before you had issues... bummer.

Re: Where is db cleanup and maintenance info

Posted: Fri Sep 07, 2012 3:17 am
by ditonet

Re: Where is db cleanup and maintenance info

Posted: Fri Sep 07, 2012 5:21 pm
by duanes1967
Followed the link and learned a few things.

1 - Apparently, there is a object created for EVERY MIB entry for EVERY map item.
2 - If the same item appears on two different maps, they are treated as unique items.
3 - I have 21,000+ items in my objs table.

4 - Apparently, there is something about the objects stored elsewhere. If I delete the db and recreate the schema then import just the objs table, the passwords, maps and all other configs are gone as well. :(

Re: Where is db cleanup and maintenance info

Posted: Mon Sep 10, 2012 4:36 pm
by lebowski
That is an impressive number of things to monitor and yes every map item is unique. Many times when I need a copy of an object on another map I make it a static object and leave the real on on the main map. I didn't get to read about primary key but it sounds like you might have to start over?

Re: Where is db cleanup and maintenance info

Posted: Mon Sep 10, 2012 7:03 pm
by ditonet
@duanes1967
First of all make a backup copy of your Dude database.
Next connect to database with 'SQLite' utility (or try this one: http://www.yunqa.de/delphi/doku.php/pro ... espy/index)
and execute following SQL statements, one by one:
SELECT id FROM "objs" GROUP BY id HAVING (COUNT(id) > 1);
SELECT timeAndServiceID FROM "outages" GROUP BY id HAVING (COUNT(timeAndServiceID) > 1);
SELECT sourceIDandTime FROM "chart_values_raw" GROUP BY id HAVING (COUNT(sourceIDandTime) > 1);
SELECT sourceIDandTime FROM "chart_values_1day" GROUP BY id HAVING (COUNT(sourceIDandTime) > 1);
SELECT sourceIDandTime FROM "chart_values_2hour" GROUP BY id HAVING (COUNT(sourceIDandTime) > 1);
SELECT sourceIDandTime FROM "chart_values_10min" GROUP BY id HAVING (COUNT(sourceIDandTime) > 1);
Every executed SQL SELECT statement will show you duplicated 'primary key' records in specified table.
Write down values returned for every table and delete duplicated records.
Finally execute :
REINDEX;
VACUUM;
HTH,

Re: Where is db cleanup and maintenance info

Posted: Mon Sep 10, 2012 7:58 pm
by duanes1967
A HUGE thanks to everyone helping.It looks like the db file is corrupted. I think that the .dump worked, so maybe hunting the duplicates from there might work although I'm still not sure my the maps and all config data was missing after I killed the db and then recreated the tables (from .schema) and then re-imported only the objs table. It looked like the blob data contained each item's info about maps, options etc.

Here is what I get from your scripts. The first two table are OK.
==============================
sqlite> delete from chart_values_raw where sourceidandtime in (0, 1);
sqlite> SELECT timeAndServiceID id FROM "outages" GROUP BY id HAVING (COUNT(timeAndServiceID) > 1);
sqlite> SELECT sourceIDandTime id FROM "chart_values_raw" GROUP BY id HAVING (COUNT(sourceIDandTime) > 1);
0
1
0
0
0
1
1
0
0
0
1
0
1
0
0
Error: database disk image is malformed
sqlite> delete from chart_values_raw where sourceidandtime = '0';
sqlite> delete from chart_values_raw where sourceidandtime = "0";
sqlite> SELECT sourceIDandTime id FROM "chart_values_raw" GROUP BY id HAVING (COUNT(sourceIDandTime) > 1);
0
1
0
0
0
1
1
0
0
0
1
0
1
0
0
Error: database disk image is malformed

Re: Where is db cleanup and maintenance info

Posted: Mon Sep 10, 2012 10:28 pm
by ditonet
Try to dump database (structure and data) as SQL script, then import it to newly created database.
Next peform all steps described in my previous post.

HTH,

Re: Where is db cleanup and maintenance info

Posted: Sun Sep 16, 2018 3:11 pm
by MikrotikOdessa
Hello,
The Dude 6.42.7 running on RB750Gr3
Command /dude vacuum-db works fine in CLI.
But it can not be correctly scheduled. Checkbox "Policy: Dude" can not be saved.

Re: Where is db cleanup and maintenance info

Posted: Wed Sep 19, 2018 4:51 pm
by jarda
Have you seen any real impact of vacuuming so it makes sense to schedule it for you?

Re: Where is db cleanup and maintenance info

Posted: Sat Jan 19, 2019 11:59 am
by MikrotikOdessa
Yes.
I am monitoring OpenWRT access points interfaces.
OpenWrt 18.06-SNAPSHOT r7407-1cd945ea22

Without periodic vacuuming there "ghost" interfaces occurs like (42949672985) instead of normal like wlan0, wlan1
After vacuuming all OK.

Re: Where is db cleanup and maintenance info

Posted: Mon Feb 18, 2019 12:50 am
by billjellis
Hi All,

I have a Dude server that is sitting at 8 GB running. What is the best way to purge old data?

Bill

Re: Where is db cleanup and maintenance info

Posted: Tue Apr 16, 2019 9:36 am
by amt
Hi All,

I have a Dude server that is sitting at 8 GB running. What is the best way to purge old data?

Bill
any solution ? I'm also wanting to clean old data

Re: Where is db cleanup and maintenance info

Posted: Tue Apr 16, 2019 10:52 am
by msatter
The Wiki on this:

https://wiki.mikrotik.com/wiki/Manual:T ... /db_vacuum

Also have a look at this script to backup and vacuum:

https://github.com/sayajin101/Dude-Backup-Script

Re: Where is db cleanup and maintenance info

Posted: Tue Apr 16, 2019 5:57 pm
by amt
The Wiki on this:

https://wiki.mikrotik.com/wiki/Manual:T ... /db_vacuum

Also have a look at this script to backup and vacuum:

https://github.com/sayajin101/Dude-Backup-Script
Hello,
vacumm not helped. I found another solutin and it's great now :)

here is a solution;
http://www.mtin.net/blog/cleaning-the-dude-database/