Compress sqlite files

[djdookie]

Recently, I transferred the blockchain and multiple wallet database files and realized those files can be compressed to under 50% of it's uncompressed size. (Something like 45%).
Now that those files grow rapidly in size daily and take a lot of space (blockchain close to 5GB, every wallet db close to 3GB), this could become a problem for small devices like Rasperry Pi's soon, if they store the dbs on something like their 32GB microSD system drive.
So I asked myself if it's worth the small performance tradeoff and compress those files to under 50% of it's current size?
I personally use compression at least for backups of those sqlite files.

In some weeks this would save you the space for 1 more plot. ;)

Is there any database compression coming with sqlite out of the box like with other database systems?

What do you think?

[XIII39]

I don't know but I think that the chia guys should offer a current version, updated every night a mid-night, for download from chia.net so every time I have to re-install the chia software I can catch-up to the block 4 days faster.

[ghost]

Hi, can't you just keep a copy of the mainnet folder?
I'm running chia-docker and new versions of Chia usually work well with the previous mainnet folder. Only thing I notice is that the config.yaml file doesn't get updated, but I've never had to re-sync wallet or blockchain.

[djdookie]

I don't know but I think that the chia guys should offer a current version, updated every night a mid-night, for download from chia.net so every time I have to re-install the chia software I can catch-up to the block 4 days faster.

This should be a great official way to faster sync new installations. Ideally the software would give you a fast sync option where it initially downloads the compresed blockchain to get started.

[djdookie]

Hi, can't you just keep a copy of the mainnet folder?
I'm running chia-docker and new versions of Chia usually work well with the previous mainnet folder. Only thing I notice is that the config.yaml file doesn't get updated, but I've never had to re-sync wallet or blockchain.

Yes you should be able to da that.
But my question is more about generally having compressed sqlite files in access to save a lot of space in the mainnet folder.

[mcartoixa]

My db file is now >20Gb... Any news on this front?

[stefan-lange]

the vacuum command of sqlite seems to reduce the *.db a little bit (21 gb ro 19-20). Maybe this could save a few GB for you too. I have 120 GB SSD in my PI, but I am also worried about the db size.

[cypher619]

There needs to be a faster way to replicate this DB. We're almost at 1M blocks and the sync time is days. You would think that a Ryzen 5950x with a 100Mb connection to the internet could do this in hours. Come on DEVS this is the internet age. Maybe you need to revisit the DB design and optimize it either that or fix your algorithm for bringing data across so that it takes advantage of the hardware on the receiving end. This is rediculous.

[rmica]

May an official checksummed weekly database image be a temporary solution? Yesterday I suffered a full power failure of the farm, I've 2 independent full-node boxes, one db was corrupted and the other was clean. So obviously, I was able to resync both from the clean one. That corroborates my belief that rsyncing the database live, once a day, is an option. I often use a rsync/inodes differential backup process on servers. If the most recent day failed to recover I'd pick the previous one and have, considering one week time span, a 100% recovering chance. Thus optimizing network traffic, service availability and disk consumption, not having to reserve a database-size*2 window to perform a live "vacuum into" command. I'm going to test it. If somebody's interested in results/scripts I'll share it as is.

[mcartoixa]

As the rumours would have it, something might be in the works around this problem: https://www.reddit.com/r/chia/comments/q8iaw7/comment/hgq18zu/ 🤷🏻‍♂️

[FrancescoSaverioZuppichini]

mine is 150 gb

[ElDavoo]

My db file is now >40Gb... and my Rasp SD is 120GB. This will really become a huge problem in the long term.
Is there any way to farm without running a full node, or at least without having a complete copy of the chain?

[rmica]

My general opinion is that by the point of view of a space farmer, even a 1TB blockchain isn't much of an issue. The only problem that I see is first time synchronization.

[ElDavoo]

It's not that simple. Plots only need to be read, while db needs constant rw i/o. As people have said in other discussions, putting plots and dbs in the same HDD gives significant problems. As the DB size keeps growing, farmers will have to put the databases in a dedicated SSD, so it will become more and more difficult for the occasional guy to run a full node, and thus to farm, unless alternative options are used like flexpool (but still, I won't be able to farm my old solo plots)

[rmica]

It's hard to generalize this topic with thousands of users ranging in power from minnows to whales. Generally speaking 40GB is less than one half of one k32 plot and yes, it's better for blockchain/wallet operations to stay on a dedicated HDD if not on a dedicated box, for improved harvester's I/O performance and eventually, CPU usage. Not for space.

[rmica]

... so it will become more and more difficult for the occasional guy to run a full node, and thus to farm, unless alternative options are used like flexpool (but still, I won't be able to farm my old solo plots)

Even if you keep running your full-node, if you're small size you'd benefit from replotting and joining an official protocol pool.

[ElDavoo]

yeah it's just that i'm lazy lmao

[rmica]

I've come to the conclusion that the best blockchain backup strategy is to run a secondary standalone full-node. No keys, no upnp, no port forwarding, config.yaml: db_sync: full. It's not farming so you won't care much about drive speed. Needs neither have to reserve blockchain*2 disk size to run sqlite live backups on the primary node, nor stopping full-node before to copy files.

[taweesak0803650558]

I've come to the conclusion that the best blockchain backup strategy is to run a secondary standalone full-node. No keys, no upnp, no port forwarding, config.yaml: db_sync: full. It's not farming so you won't care much about drive speed. Needs neither have to reserve blockchain*2 disk size to run sqlite live backups on the primary node, nor stopping full-node before to copy files.

[mlin]

Hello all, just dropping a pointer to sqlite_zstd_vfs which is the type of SQLite extension one would need to compress the database whilst still permitting random I/O. I strongly doubt the Chia software can use that code as-is (for several reasons), but, at least I have a lot of experience built up in how to implement this underneath SQLite. If any of the devs want to brainstorm about this then I'd be happy to.

[stefan-lange]

I thought there is already something "going on" with compression. You could try to ask arvidn, he is a dev.

[Jacek-ghub]

My understanding is that what Chia is working on is rather db reorganization that results in a smaller db, not really a compression per se. As it is recommended to use as fast media as possible to host those dbs, anything that could penalize the access speed I would think is rather a no go.

On the other hand, sqlite has a VACUUM option (for doing backups). It basically removes what it perceives as unused data. I did try to run it (as someone has recommended it as a backup for chia dbs) and got ~12% size saving (37.5 GB down to 33 GB). However, I don't know much about sqlite, nor blockchain db to be able to say whether the end result is a valid db that can be used for production, so I abandoned this route.