[ZODB-Dev] Relstorage and over growing database.

[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 :/

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