I did some analysis of my info.db with sqlite3_analyzer here is the result. (need to split this in multiple messages)
What can’t we take out of these informations.
- 70% of the Data is not needed for the operation.
The order_archive could be helpfull if there are problems with the payment to check if something wents wrong, but after a successfull payment there is no reason to keep them. I am also see no benefit to keep this in database, the archive should be written in a text log, the situation where it is needed are rare.
The certificates seams only to be uplink certificates. Afaik the libuplink generates a new cert for every upload, this explains why are some many entries in there. As long the uplink generate everytime a new one there is no reason to keep them, if they are only taken onetime or short period they should hold in ram
Same for bandwidth_usage historic data could be written in a text file each per month, with a result head, then it don’t be needed recalculate each time.
- The database could be more normalized
The satellite_id is written in multiple columns in its full 32byte length, many many many times, same with other ids. We have 4 known satellites, if the get its own table and only get refered with a one to four byte pointer much space could be saved.
Why are there such many blob types?
All id types stored as blob, but blobs are slower than varchar entries, of course it took time in storagenode to convert the bytes to varchar, but it you be faster to convert it and have a faster database.
Delete old data
My Database is around 1GB in size, i started in April, there were 3 wipes after my start, but this database containing data over all the time.
This are many low hanging fruits, if this all get fixed i belive the database could shrink down to 10% or less in size, which would speed up many things. I see no sense to switch to MySQL or other database, this will speed up nothing.