[ZODB-Dev] Relstorage and over growing database.

[Juan A. Diaz]

Hi all,

I have a site in where we are using Relstorage with Mysql and we have
a few concerns about how the database size is growing.
We have configured Relstorage with history-preserving
(keep-history=true), but some time after release the site into
production we change the keep-hisotry-days from 7 to 0, whit the
intention to avoid the over growing.

Now the size of the database is 15GB, but after a dump with the
zodbconvert utility the size of the Data.fs file is the only 1.2GB.
Changing the keep-history to false, to have a history-free storage,
and loading again the database to mysql with zodbconvert result in a
database of 2.1GB.

Looking in the files of in where the mysql server save the database i
could see that 12.4GB are used for the data and indices of the table
object_ref and the remaining space is used for the object_state table.

Here you can see a comparisons of the size of that tables in the two databases.

table name; history-preserving db ; history-free db
------------------------------------------------------------------------
object_ref.MYD;         5.5G ;  0
object_ref.MYI;         6.9G ;  1K

object_refs_added.MYD;  1.1M ;  0
object_refs_added.MYI;  1.8M ;  1K

object_state.ibd;       1.8G ;  2.1G
------------------------------------------------------------------------


and a few simple queries for count the object in that tables in the
history-preserving db;

mysql> SELECT COUNT(*) FROM object_ref;
+-----------+
| COUNT(*)  |
+-----------+
| 232749451 |
+-----------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM object_refs_added;
+----------+
| COUNT(*) |
+----------+
|   125135 |
+----------+
1 row in set (0.00 sec)


The same queries with in the history-free db.

mysql> SELECT COUNT(*) FROM object_ref;
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM object_refs_added;
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

Reading the some comments [0] in the code
(relstorage/adapters/schema.py) I could see that the object_ref
database is uses during the packing, then the question is, in a
history-preserving database there is something that we could do to
decrease the size of that table? could be safe truncate that table? We
want to move to a storage with history-free, but for now we are
looking some options and actions to perform on production without the

[0]
# object_ref: A list of referenced OIDs from each object_state. This
# table is populated as needed during packing. To prevent unnecessary
# table locking, it does not use foreign keys, which is safe because
# rows in object_state are never modified once committed, and rows are
# removed from object_state only by packing.

When we realize that the size of the database was over growing we
start to make daily packs but now after read this comment I start to
think that that could be now part of the problem, could be? Normally
in a normal day the DB grows like 2.2GB, but after make a pack the DB
size is decrease clouse to 1.5GB or 2GB.

If somebody have some glue, link to some post or documentation, or
maybe experiences with this kind of issues, I really appreciate ear
about that.

Thanks In advance.

nueces...