[ZODB-Dev] Relstorage and over growing database.
Juan A. Diaz
nueces at ravvit.net
Sat Feb 2 04:08:28 UTC 2013
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...
More information about the ZODB-Dev
mailing list