[ZODB-Dev] Relstorage and over growing database.

[Jürgen Herrmann]

Am 07.02.2013 21:18, schrieb Jürgen Herrmann:
> Am 07.02.2013 20:22, schrieb Shane Hathaway:
>> On 02/06/2013 04:23 AM, Jürgen Herrmann wrote:
>>> I think this is not entirely correct. I ran in to problems serveral
>>> times when new_oid was emptied! Maybe Shane can confirm this?
>>> (results in read conlfict errors)
>>
>> Ah, that's true. You do need to replicate new_oid.
>>
>>> Then I'd like to talk a little about my current relstorage setup 
>>> here:
>>> It's backed by mysql, history-preserving setup. Recently one of our
>>> DBs started to grow very quickly and it's object_state.ibd (InnoDB)
>>> file is just over 86GB as of today. Packing now fails due to mysql
>>> not being able to complete sorts in the object_ref table. 
>>> object_ref
>>> is also very big (36GB MYD file, 25GB MYI file). I took a backup of 
>>> the
>>> DB and let zodbconvert convert it back to a FileStorage, the 
>>> resulting
>>> file is 6GB (!). I will pack it and see how big it is then. I will
>>> also investigate how big on disk this DB would be when stored in
>>> postgresql. This situation poses another problem for us: using
>>> zodbconvert to convert this mess to a Filestorage tages just over 
>>> an
>>> hour when writing to a ramdisk. I suspect converting to postgres
>>> will take more than 10 hours, which is unacceptable for us as this
>>> is a live database an cannot be offline for more than 2-3 hours in
>>> the nicht. So we will have to investigate into a special 
>>> zodbconvert
>>> that uses a two step process:
>>> 1. import transactions to new storage from a mysql db backup
>>> 2. import "rest" of transactions that occurred after the backup was
>>>     made from the "live" database (which is offline for that time
>>>     of course)
>>>
>>> looking at zodbconvert using copyTransactionsFrom() i thnik this
>>> should be possible but up to now i did non investigate furhter.
>>> maybe shane could confirm this? maybe this could also be 
>>> transformed
>>> into a neat way of getting incremental backups out of zodbs in
>>> general?
>>
>> Yes, that could work.
>>
>> As for MySQL growing tables without bounds... well, that wouldn't
>> surprise me very much.
>
> I know that's entirely not your fault but may be worth mentioning
> in the docs. Relstorage with MySQL works *very* well for DB sizes
> <5GB or so, above that - not so much :/

Also for the docs: on disk Restorage/MySQL uses 4x the size of a
FileStorage with same contents. As packing tables are filled this
grows by another factor of ~2. If you don't pack very regularly
you might up ending in DBs that donb't permit packing anymore
because of the big size very quickly.

best regards,
Jürgen

>
> That issue has given me some sleepless nights, especially because
> the conversion step to another storage type takes quite a long
> time. But in less than two hours i came up with a workable
> solution today, maybe see the other messages on the list regarding
> that issue. I LOVE OPEN SOURCE. I LOVE PYTHON. :)
>
> best regards,
> Jürgen
> --
>>> XLhost.de ® - Webhosting von supersmall bis eXtra Large <<
>
> XLhost.de GmbH
> Jürgen Herrmann, Geschäftsführer
> Boelckestrasse 21, 93051 Regensburg, Germany
>
> Geschäftsführer: Jürgen Herrmann
> Registriert unter: HRB9918
> Umsatzsteuer-Identifikationsnummer: DE245931218
>
> Fon:  +49 (0)800 XLHOSTDE [0800 95467833]
> Fax:  +49 (0)800 95467830
> Web:  http://www.XLhost.de
> _______________________________________________
> For more information about ZODB, see http://zodb.org/
>
> ZODB-Dev mailing list  -  ZODB-Dev at zope.org
> https://mail.zope.org/mailman/listinfo/zodb-dev

-- 
>> XLhost.de ® - Webhosting von supersmall bis eXtra Large <<

XLhost.de GmbH
Jürgen Herrmann, Geschäftsführer
Boelckestrasse 21, 93051 Regensburg, Germany

Geschäftsführer: Jürgen Herrmann
Registriert unter: HRB9918
Umsatzsteuer-Identifikationsnummer: DE245931218

Fon:  +49 (0)800 XLHOSTDE [0800 95467833]
Fax:  +49 (0)800 95467830
Web:  http://www.XLhost.de