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... _______________________________________________ For more information about ZODB, see http://zodb.org/ ZODB-Dev mailing list - ZODB-Dev@zope.org https://mail.zope.org/mailman/listinfo/zodb-dev
