Sqlite3 db vacuum

I’ve been looking for a way to backup a chia/fork db live. I see a lot of posts about using the vacuum option. I’ve tried it, I simply get a message saying to DB is locked.

Has anyone been able to get this to work successfully? or something similare?

Other people may have spoke about it, I just shut down CHIA gui and copy the whole .chia folder to a spare USB hard disk along with the .keys etc. Then I have the whole kaboom…

SQLite has two options. One is live backup, and the other is vacuum. I really don’t know much about sqlite, as potentially there are other third-party tools that can do the job as well. Although, the main target for sqlite are small dbs; therefore, for most applications a simple copy may take just few short seconds, so that is not an issue that sqlite may be interested to address.

The way live backup works is that if in the middle of backing up db will get changed, it restarts the process from scratch. Although, it should produce exact copy of the db. So, it really works well for dbs that are about 10 bytes long and updated once a week. I tried it and every time killed it after several hours of practically choked NVMe due to small chunks reads.

As far as vacuum, you need to stop db (i.e., chia software) to get it working. Also, as it reads db record after record, that is basically killing the media read speeds, as those chunks are very small, so the whole process took me about 15-30 minutes, where just straight db copy around a minute or so). Vacuum also rearranges db, and removes deleted records. Therefore, the vacuumed db usually is smaller and may perform better (records are contiguous on the drive - although, it may not be much difference on SSD/NVMe). However, in my opinion, it is a waste of time, as straight copy is 20+x faster.

So, in my opinion, as long as Chia is not going to fix problems with db corruption and syncing speed, I would go for db download options. If there is a security issue that Chia implies may be there, again, they need to fix their side first.

There was one thread about chia-database.com service, where it was outlined what such service should try to do to legitimize such downloads. It would be very easy for Chia to be the db source for those services, while keeping db checksum on their side. This way, we would know that the db origin is Chia, not some potentially unverified source. At least, they should support such arrangement until their side is fixed.

Therefore, having Chia people bash those that face yet another sync, due to db corruption by their software and are asking for db downloads is just a tone-deaf position, as they produce crap software, where we farmers (over 300 thousand people) are hit with wasted hours.

1 Like

Use a command line like this (Windows-style):
sqlite3.exe W:\Chia\db\blockchain_v1_mainnet.sqlite “VACUUM INTO ‘W:\Chia\db\backup\blockchain_v1_mainnet.sqlite’”
Make sure to get the double and single-quotes correct.

I have moved my location for the Chia database (and keeping the backup in a subdir to that) so you need to change your paths accordingly.

It is perfectly fine to do this while Chia is running. Documentation for the VACUUM is at VACUUM

If you are using some Linux variant SpaceFarmers has made a guide at How to backup the Chia blockchain database [SpaceFarmers.io Wiki]
They also show how to automate it for daily backups.

2 Likes