[ZODB] zodbconvert to Oracle very slow

[Nicolas Göddel]

Hi,

we are developing a project for a customer using Plone 5.0.7 (later 5.1)
and RelStorage 1.6.3. For developing we are using our own instance of
Oracle 12.1.0.2.0 in our own infrastructure. It works good and fast and
without any issues.

Now our customer needs our application as a docker image which will be
build from our source within in their infrastructure. Because there are
some settings and predefined objects in Plone we created on our side and
which have to always be there when the docker image is started, I always do
a zodbpack on our database and then a zodbconvert from our RelStorage to a
Data.fs file and blobstorage directory. This process lasts nearly 10
minutes and produces a Data.fs of 279 MB and a blobstorage of 1,3 MB. Then
I tar.gz them to a size of 46 MB and send it over to our customers docker
build platform. The database will then be integrated in the docker image
and zodbconvert will be called at the start of the docker application
before Plone if and only if the Oracle database is empty or if it is a new
version of the initial database. But that is the culprit. Converting the
Data.fs and blobstorage back to our customers Oracle instance lasts over 7
hours and in the end it runs with 0.008 MB/s. But as soon as the conversion
is done Plone itself gets started and runs very smoothly, maybe a little
bit slower than on our machines, but with a bigger object cache it is very
good.
So the main thing for me is the slowness of zodbconvert. Is there something
what I can do to speed it up? Or do you have an other idea instead of
zodbconvert to fill the database quickly with the content of our database?
Sometimes there are tids with more than 50,000 records, e.g. one had 86457
records and it lasts nearly 90 minutes until the next tid was processed.

What else do you need to know about my scenario? What information can I
give you to track down this slowness issue?

[Nicolas Göddel]

My idea would be to change `zodbconvert` in such a way that it only commits
transferred transactions in the end and not every single state. Or it could
accumulate 100 transactions and commit them all together like
portal_catalog does it on an update.
Or is there a way to compress thousands of states in a transactions to a
single state? Or has it to be the way it is?

[Nicolas Göddel]

I can list all of the problematic transactions using this SQL query:

SELECT * FROM
(
SELECT TID, COUNT(ZOID) AS cnt FROM OBJECT_STATE GROUP BY TID
)
ORDER BY cnt DESC

I then get something like this:

TID CNT
272491772864331366 22020
272385852108654984 21603
272398516995100808 21301
272577782172764091 20394
272551817462108040 16122
272600915779832320 12449
272577837397018146 11741
... ...

The fourth tid for example with 20394 states lasts 20 minutes to process
with zodbconvert. Together with all the other tids it takes up to two
hours. That's exaggerating.

[Nicolas Göddel]

No ideas?

[Sean Upton]

Have you tried running convert from a zero-day-packed Data.fs copy?

Sean

Post by Nicolas Göddel
No ideas?
--
You received this message because you are subscribed to the Google Groups "zodb" group.
For more options, visit https://groups.google.com/d/optout.

[Nicolas Göddel]

Yes, I always do a zodbpack of the Relstorage with zero days before using
zodbconvert. Or do you mean packing Data.fs after converting it from
RelStorage?

Post by Sean Upton
Have you tried running convert from a zero-day-packed Data.fs copy?

[Sean Upton]

I cannot tell you what is slowing you down with Oracle+RelStorage target here, I can suggest:

1. Profile the calls within this method as you run zodbconvert:

https://github.com/zodb/relstorage/blob/a821ec1147135752119ce443d22dae0a31d3abd8/relstorage/storage.py#L1503-L1561 <https://github.com/zodb/relstorage/blob/a821ec1147135752119ce443d22dae0a31d3abd8/relstorage/storage.py#L1503-L1561>

2. Optional, but a really good idea to compare against importing into PostgreSQL with a known-good config, for process of elimination (see my previous response to your post on community.plone.org <http://community.plone.org/>).

I hope this is helpful direction.

Sean

Yes, I always do a zodbpack of the Relstorage with zero days before using zodbconvert. Or do you mean packing Data.fs after converting it from RelStorage?
Have you tried running convert from a zero-day-packed Data.fs copy?
--
You received this message because you are subscribed to the Google Groups "zodb" group.
For more options, visit https://groups.google.com/d/optout <https://groups.google.com/d/optout>.