RelStorage: packing growth Databaze size quickly, something wrong?

Plone Support
1

Hi,
I'm using RelStorage for our system with history-fee option. With FileStorage. packing mean remove old records which result in Data.fs size reduced. However after changing to RelStorage, I have a Postgresql Database size around 10GB, I deleted ~20000 objects, the DB size increase to ~15GB, then I do packing , after that the DB size increase to 50GB. This is doesnt sound right to me. Did I miss anything here?

2

Packing optimizes the ZODB database. This optimization writes changes to the underlying Postgresql database. You'll have to optimize that layer too. See
https://www.postgresql.org/docs/9.1/static/sql-vacuum.html

3

You can also try to use my relstorage_packer. It works only for PostgreSQL with history free storages. Read more about it on PyPI.

It creates a table of inverse references in the DB itself. If you keep it later packing is much faster. It consumes space itself too, so you can delete it (the tool keeps it).

Vacuum of on DB level is needed anyway to free space on DB level.. Usually PostgreSQL has autovacuum configured, so it should not need manual intervention.

Just for the case: Also avoid storing blobs in the database, always use a shared filestorage such as a SAN/NFS/...

4

yeah, the Vacuum works here, after vacuum, DB size reduced to smaller number than before deleting objects which is good. Thank you all for the help.
P/S: I actually used relstorage_packer with history free storage, blobs stored in FS.

5

If you ever opt to keep history, you can also consider a weekly truncate of object_ref; and object_refs_added if you have a day of the week that things are slow, just keeping in mind that your next pack will take more time to rebuild these. I use this, keep 7 days history, and use autovacuum for reasonable results. This is what I do on a weekly cron job:

parts/opt/bin/psql -h var/postgres -p 5432 plone_zodb -c "TRUNCATE object_ref; TRUNCATE object_refs_added"

Sean

6

This approach looks as a hack. But there is no option in zodbpack to clean up object_ref table. In my case object_ref is 3 times bigger than object_state which is very strange.
Is it really safe to truncate object_ref and object_refs_added?

7

According to Migrating to RelStorage version 1.4.2 - RelStorage 4.0.1.dev0 documentation :

It [object_refs_added] contains only temporary state used during packing, so it is safe to drop and create the table at any time except while packing.

Has anyone had contrary experiences?

8

This is fine.

1 Like