Dude db vacuuming

RouterOS The Dude
1

Hi,
ROS 6.44.3 on CHR
How to do vacuuming right?
Does DB will reduce in it size after vacuuming?

2

There is only one vacuum command available so you cannot make a mistake. But it might not help at all. You have to try…

3

I did it. But it have to “eat” up to 1GB of system partition. Not Dude disk.
And finishes without any status info.
Database sill >3.5GB

Maybe I need to transfer db-file to x86 pc and do some other actions on it?

4

Try this.
http://forum.mikrotik.com/t/the-dude-6-40-8-db-failure-database-disk-image-is-malformed/122932/1

5

Does it applicable for >6.41 dude installations?

6

Sure. There is no change in dude for ages because mikrotik doesn’t have available capacities. So the difference between the versions is just the number.

7

Hi,

To purge all historical data and only keep the devices / network maps this works.
Just performed the purge on 6.49, zero issues after cleaning. My results are: 615MBytes → 1.2MBytes

First download SQLLite3 prebuild for your OS: https://www.sqlite.org/index.html

Stop the dude service, wait for it to finish stopping, it can take awhile, then download dude.db to a local folder.
Place sqlite3.exe in the same folder as you downloaded dude.db.

Src: http://www.mtin.net/blog/cleaning-the-dude-database/

cd C:\path\to\dude\dir\
sqlite3.exe dude.db

DELETE FROM outages;
DELETE FROM chart_values_raw;
DELETE FROM chart_values_10min;
DELETE FROM chart_values_2hour;
DELETE FROM chart_values_1day;
pragma integrity_check;
vacuum;
reindex;
.quit

Rename old dude.db to dude.db.orig and upload the now smaller dude.db back to the server/CHR/routerboard and start the service again.

8

interesting, thank you for sharing

9

VACUUM do not do already all?

pragma integrity_check;
vacuum;
reindex;

10

Of course VACUUM does NOT delete valid data in tables, whether or not anybody considers it unwanted.

11

I’m talking about last 3 lines, not all the other “delete” lines

12

Hi,

To fix a corrupt Dude instance, e.g. ran out of space.

First download SQLLite3 prebuild for your OS: https://www.sqlite.org/index.html

Stop the dude service, wait for it to finish stopping, it can take awhile, then download dude.db to a local folder.
Place sqlite3.exe in the same folder as you downloaded dude.db. e.g. C:\SqlLite3

cd C:\SqlLite3\
echo .dump | sqlite3.exe dude.db > dude.sql
move dude.db dude.db.orig

Copy dude.sql to /tmp/ on a linux host

cd /tmp/
awk '/^INSERT INTO objs/' dude.sql > objs.sql

Copy objs.sql to C:\SqlLite3\ on your windows host
Create a clean empty dude.db, by deleting the original from the dude server and starting the server, then stop the server and copy the fresh dude.db to C:\SqlLite3

sqlite3.exe dude.db
pragma integrity_check;
delete from objs;
.read objs.sql
.quit

Last copy dude.db to /dude/ on routeros and start the server.

13

thank you again for sharing

14

Might as well document the process. Cant be the first, nor the last to run into these issues :slight_smile: