Re: [ZODB-Dev] Relstorage and over growing database.
2013/2/2 Shane Hathaway sh...@hathawaymix.org: On 02/01/2013 09:08 PM, Juan A. Diaz wrote: 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 object_ref is essentially a cache of object_state, and object_refs_added is a list of what's in that cache. Therefore you can freely truncate object_ref as long as you also truncate object_refs_added. Don't truncate them during packing, though. Do you think that add one option in zodbpack to truncate this tables after the pack could be a god idea? 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 your database grows by 2.2 GB per day, it's not surprising that the database is 15 GB. With drive and RAM sizes today, 15 GB doesn't sound like a problem to me... unless it's on a Raspberry Pi. :-) Shane Yes, but after the pack the size of new objects that remain in the database is just like 200MB. Also 15GB as you say is not a real big database for this days, but we are synchronizing our databases through a low bandwidth channel across various datacenters and in some cases recover the database from a failure in the sync process is real pain!. Do you think that is possible to don't replicate that tables could be safe? There are other tables that maybe we don't need replicate? Cheers and many thanks for you time :) 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
Re: [ZODB-Dev] Relstorage and over growing database.
2013/2/2 Shane Hathaway sh...@hathawaymix.org: On 02/02/2013 04:13 PM, Juan A. Diaz wrote: 2013/2/2 Shane Hathaway sh...@hathawaymix.org: On 02/01/2013 09:08 PM, Juan A. Diaz wrote: Do you think that add one option in zodbpack to truncate this tables after the pack could be a god idea? The object_ref table is intended to help the next pack run quickly, but I suppose it might make sense to clear it anyway with an option. Ok, I going to try implement it in the next week, and send a pull request :) If your database grows by 2.2 GB per day, it's not surprising that the database is 15 GB. With drive and RAM sizes today, 15 GB doesn't sound like a problem to me... unless it's on a Raspberry Pi. :-) Yes, but after the pack the size of new objects that remain in the database is just like 200MB. Also 15GB as you say is not a real big database for this days, but we are synchronizing our databases through a low bandwidth channel across various datacenters and in some cases recover the database from a failure in the sync process is real pain!. Do you think that is possible to don't replicate that tables could be safe? There are other tables that maybe we don't need replicate? You never need to replicate the MyISAM tables. Only the InnoDB tables (object_state, current_object, transaction) need to be replicated. Perfect! Shane Thank again for you time =) Cheers. 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
[ZODB-Dev] Relstorage and over growing database.
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
