configurable sqlite memory cache, or how it works now?

[nnmfnwl7]

Is your feature request related to a problem? Please describe.

I'm always frustrated when talking about SSD/RAM usage by apps. I really understand that step moving from level-db to sqlite-db because of infinitive increasing ram capacity requirements. But if we could talk about number of READ/WRITES with sqlite to storage, please let me talk about this:

I have SSD that i want to live as long as it could be. I have other software running on machine that also want to read/write to storage. I have UPS(Uninterruptible Power Supply) backup for hours. I have 64GB RAM and ie. having 32GB RAM free of choice(actually)

I do care about app performance and SSD can not be ever faster than RAM cache.

I do not care about power outages because i have UPS, so I do not need to read/write to storage every sqlite DB select/update/..., rather POSTPONE as long as it could be by configurable RAM cache. I do not care about -SHM -WAL files i do not want to see em when there is no need to have them.

Describe the solution you'd like Describe alternatives you've considered

I actually have no clue what sqlite development limitations are, just considering if would be possible to add configurable options: -sqlite-write-postpone=true/false or more advanced like -sqlite-write-postpone=5000MB -sqlite-cache-size=32GB

With this two options enabled, sqlite files will not be updated until reaching sqlite-write-postpone/sqlite-cache-size size.

In my particular case, i can really imagine that with 32GB RAM cache enabled will sqlite not write down any updates down to storage rather one time final update with calling pocketnet node to stop. And maybe next time i start node with 6GB RAM cache it will write down most of changes to cache.

This are just thoughts... maybe it is already in tasks to be done...

[the-real-vortex-v]

Currently you can recompile the source so you can get a massive sql cache.

goto: pocketnet.core-alpha-0.20.13/src/sqlite/sqlite3.c

change the below line:

define SQLITE_DEFAULT_CACHE_SIZE -400000

It's set to 400MB in cache size. You can change that to whatever you want. Add an extra zero for 4gb for example.

As far as I know there will be a command line/config option added later perhaps but for now recompiling is the only solution.

[nnmfnwl7]

Currently you can recompile the source so you can get a massive sql cache.

goto: pocketnet.core-alpha-0.20.13/src/sqlite/sqlite3.c

change the below line:

define SQLITE_DEFAULT_CACHE_SIZE -400000

It's set to 400MB in cache size. You can change that to whatever you want. Add an extra zero for 4gb for example.

As far as I know there will be a command line/config option added later perhaps but for now recompiling is the only solution.

Thank you very much, yea i saw this commit https://github.com/pocketnetteam/pocketnet.core/commit/42f3df17f05a292a81aecbbe3b751c6e8a71375c ,so i was very curious about situation because was decreased to 5MB.

Thanks again, i will try.

[the-real-vortex-v]

No problem. Also there's a bunch of other cache settings in there but I have no idea which are safe/good/bad etc to fiddle with. I've been changing the above define because I'm testing the node software on a seriously underpowered system and dropping it down to 40mb or 80mb cache is handy.

[tawmaz]

The value was reduced due to amount of memory the current sqlite client is consuming. We should provide a command line option to allow a larger cache for higher performance node or anyone wanting to minimize disk usage and smaller cache for a node with less memory.

[andyoknen]

Increasing the sqlite cache will help us significantly reduce the load on the disk for reading from the database, but alas, it will not reduce the load for writing. The main difference between version 0.20 and 0.19 is that we write all the changes to the database to disk at once, which guarantees data integrity in case of malfunctions. This describes the potential opportunities for us to work with memory first, and later dump the dump to disk.

https://www.sqlite.org/backup.html

[tawmaz]

I am working on some testing to compare synchronization performance of 5mb and 500mb. The initial writeup is located here: https://github.com/tawmaz/PocketnetDocs/blob/main/SQLiteCacheTest.md

[andyoknen]

120

[nnmfnwl7]

I am working on some testing to compare synchronization performance of 5mb and 500mb. The initial writeup is located here: https://github.com/tawmaz/PocketnetDocs/blob/main/SQLiteCacheTest.md

Great testing has been done, Little bit practical question, what if someone try to run also other chains on that machine, ie BTC, LTC for doing PKON atomic swaps within DEX? if could cause some impact...

[nnmfnwl7]

Increasing the sqlite cache will help us significantly reduce the load on the disk for reading from the database, but alas, it will not reduce the load for writing. The main difference between version 0.20 and 0.19 is that we write all the changes to the database to disk at once, which guarantees data integrity in case of malfunctions. This describes the potential opportunities for us to work with memory first, and later dump the dump to disk.

https://www.sqlite.org/backup.html

Yea, true. I was trying to find out universal solution on operating system layer, like tuning fstab parameters like, data=writeback,commit=120. There are lots of discussions about fs tuning, but i was looking for something specific, maybe it exist something that is able to run like tmpfs on top of existing data, that will keep merging all write changes to RAM until umount or tmpfs ram limit. I can imagine it like new linux kernel feature, that could have usage like: mount -t ramcache -o size=5G,user ramcache /home/${SUSER}/.pocketcoin.sqlite/pocketdb/ ...