Community discussions

MikroTik App
 
bda
Member Candidate
Member Candidate
Topic Author
Posts: 189
Joined: Fri Sep 03, 2010 11:07 am

Dude db vacuuming

Fri Jul 05, 2019 7:31 pm

Hi,
ROS 6.44.3 on CHR
How to do vacuuming right?
Does DB will reduce in it size after vacuuming?
 
jarda
Forum Guru
Forum Guru
Posts: 7752
Joined: Mon Oct 22, 2012 4:46 pm

Re: Dude db vacuuming

Mon Jul 08, 2019 8:45 am

There is only one vacuum command available so you cannot make a mistake. But it might not help at all. You have to try...
 
bda
Member Candidate
Member Candidate
Topic Author
Posts: 189
Joined: Fri Sep 03, 2010 11:07 am

Re: Dude db vacuuming

Tue Jul 09, 2019 11:54 pm

There is only one vacuum command available so you cannot make a mistake. But it might not help at all. You have to try...
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?
 
jarda
Forum Guru
Forum Guru
Posts: 7752
Joined: Mon Oct 22, 2012 4:46 pm

Re: Dude db vacuuming

Wed Jul 10, 2019 12:40 am

 
bda
Member Candidate
Member Candidate
Topic Author
Posts: 189
Joined: Fri Sep 03, 2010 11:07 am

Re: Dude db vacuuming

Wed Jul 10, 2019 11:15 am

Does it applicable for >6.41 dude installations?
 
jarda
Forum Guru
Forum Guru
Posts: 7752
Joined: Mon Oct 22, 2012 4:46 pm

Re: Dude db vacuuming

Wed Jul 10, 2019 11:17 am

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.
 
User avatar
merlinthemagic7
newbie
Posts: 43
Joined: Fri Sep 16, 2016 8:49 pm

Re: Dude db vacuuming

Fri May 27, 2022 10:58 am

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.
 
User avatar
chechito
Forum Guru
Forum Guru
Posts: 2551
Joined: Sun Aug 24, 2014 3:14 am
Location: Bogota Colombia
Contact:

Re: Dude db vacuuming

Fri May 27, 2022 5:40 pm

interesting, thank you for sharing
 
User avatar
rextended
Forum Guru
Forum Guru
Posts: 9582
Joined: Tue Feb 25, 2014 12:49 pm
Location: 🇮🇹, my 💔 is in 🇺🇦

Re: Dude db vacuuming

Fri May 27, 2022 6:34 pm

VACUUM do not do already all?

pragma integrity_check;
vacuum;
reindex;
 
sid5632
Long time Member
Long time Member
Posts: 516
Joined: Fri Feb 17, 2017 6:05 pm

Re: Dude db vacuuming

Sun May 29, 2022 3:36 am

VACUUM do not do already all?

pragma integrity_check;
vacuum;
reindex;
Of course VACUUM does NOT delete valid data in tables, whether or not anybody considers it unwanted.
 
User avatar
rextended
Forum Guru
Forum Guru
Posts: 9582
Joined: Tue Feb 25, 2014 12:49 pm
Location: 🇮🇹, my 💔 is in 🇺🇦

Re: Dude db vacuuming

Sun May 29, 2022 10:41 am

I'm talking about last 3 lines, not all the other "delete" lines
 
User avatar
merlinthemagic7
newbie
Posts: 43
Joined: Fri Sep 16, 2016 8:49 pm

Re: Dude db vacuuming

Fri Sep 09, 2022 2:22 pm

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.
 
User avatar
chechito
Forum Guru
Forum Guru
Posts: 2551
Joined: Sun Aug 24, 2014 3:14 am
Location: Bogota Colombia
Contact:

Re: Dude db vacuuming

Fri Sep 09, 2022 5:40 pm

thank you again for sharing
 
User avatar
merlinthemagic7
newbie
Posts: 43
Joined: Fri Sep 16, 2016 8:49 pm

Re: Dude db vacuuming

Fri Sep 09, 2022 9:04 pm

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

Who is online

Users browsing this forum: No registered users and 5 guests