search

biokoda / actordb

ActorDB distributed SQL database
Mozilla Public License 2.0
1.89k stars 72 forks source link

LMDB still grows after delete then insert #65

Open rquast opened 5 years ago

rquast commented 5 years ago

I've recently started testing actordb with thrift and php. I set up a few tests to insert a few million records, delete them then insert more records and see show big the lmdb database file grew. Unfortunately, I noticed the file size grew around the same pace after inserting 3 million records, deleting them all then inserting 1 million. I don't think it's reclaiming space, it seems to be just appending. Is this the expected behavior? If so, is there some more sophisticated methods for compaction or space reclamation rather than running actordb in a cluster, bringing one of the nodes down, running a compact/copy, replacing the original lmdb file with the compacted one and doing this for each node?

rquast commented 5 years ago

I just noticed that the page count is unchanged after a delete from (table) for an actor (used actordb_tool print info to see that) and when you run a compact copy the mdb file size doesn't change a lot. However, if you run a pragma delete on the actor then a compact copy, the file size reduces correctly.

sabyasachi commented 5 years ago

Were you able to conclude this? If I insert new data and delete stale data every day. Will the DB size continue to grow every day?

SergejJurecko commented 5 years ago

Lmdb does not reclaim space. But after a bunch of deletes it will reuse old space.Sqlite will keep the same number of pages generally but they will be empty after a delete so they will compress to a very small size compared to page size.