Hi,
in a project we are using Plone 5 with Relstorage connected to an Oracle Database.
In our test environment we prepare the folder hierarchy, some objects, files and settings which are also needed on the production machine. Then we use zodbconvert to convert the Relstorage to filestorage and blobstorage, create a docker image and start it on the production machine. When the docker is started the first time on an empty database zodbconvert will be started to convert the filestorage and blobstorage back to Relstorage. But although the database is relatively small it lasts up to 24 hours to convert it back to Relstorage. In its output one can see that the speed is under 1 kB per second sometimes. I don't understand that behavior. How could I speed up this. The conversion from Relstorage to filestorage/blobstorage only needs a few minutes, but the other way around it is so damn slow.
At the moment I am using Relstorage in version 1.6.3.
I made some tests with Oracle + plone 4.3 + relstorage and everything was slow, including relstorage/filestorage conversions.
I did not investigate further, and I thought maybe it was some problem with my Oracle setup (I don't know much about DBA stuff), but it was just a guess.
An operation which was also pretty slow was to empty the database. I made an script to speed it up a little bit. I can share it if you need it.
Please share if you find out something that helps you. I'll try to find the data about the tests I made, so we can compare.
I also have to mention the folks at the zodb mailing list are very helpful. The maintainer of relstorage was super responsive about an issue I was having regarding versions mismatches (not performance related though).
I suggest asking about the slowness there, providing detailed information like number of transactions in the db and the time took by zodbconvert.
In my database were nearly 2,000,000 states and like 2,000 transactions. I just did a zodbpackand I will try to zodbconvert the now very small database to Relstorage again. Maybe the issue was that there were still so many undo actions in the database. I also will try to get into this mailing list. Is this the mailing list you were talking about: https://mail.zope.org/mailman/listinfo/zodb-dev
For the record, here are the results some tests I made using zodbconvert:
I attempt two conversions from a ZEO storage to two different instances of Oracle DB. ZEO was accessed via an SSH tunnel in a a remote server. Oracle was also accessed over the network using the standard Oracle protocol. I think network was not a bottleneck since the bandwidth used during the test was very small.
Total number of transactions in ZODB: 120,614. I gave up before zodbconvert could copy all transactions.
Test on the production Oracle instance: Copied 5014 transactions in 592 seconds, 8.47 transactions per second.
Test on the test Oracle instance: Copied 6073 transactions in 4749 seconds, 1.28 transactions per second.
I wrote my own script to clear the database but your script looks more thought-out. I simply delete all tables.
At the moment I zodbconvert a Data.fs with the size of 66M and a blobstorage with the size of 1.3M to Relstorage again. Just a few minutes ago there was a single tid with 86457 records which lasts from 11:26 to 13:02, nearly 90 minutes! What the heck? I started at 10:26 and the process is still not done. It's 13:22 now and we're at 87,69%. The speed is 0.012 MB/s. I don't know how to explain that to our customer.
zodbconvert is still running. It was 7 hours ago as I started it. The speed dropped to 0.008 MB/s at 95,70%. That is ridiculous. Tomorrow I try to contact the guys from the mailing list.
If nothing, but for information, I would try zodbconvert with the same source FileStorage against a stock PostgreSQL (see below) with really simple tuning for comparison's sake?
IMHO, you should post to the ZODB Google group/list, and include your storage configuration options from your zope.conf and possibly describing how your Oracle installation is set up.
If you do decide to try PostgreSQL to compare performance for informational purposes, here are some pointers:
Here is some simple postgresql.conf tuning from a modest config used in production with PostgreSQL 9.3 and RelStorage 1.6.
To use PostgreSQL, you only need a running server, a connection over TCP or domain socket, and running something similar to $ psql template1 (insert user and port-specific bits as needed, per whatever is allowed in your pg_hba.conf) with a query like CREATE USER admin WITH PASSWORD "bananas"; CREATE DATABASE zodb OWNER admin and a dsn in your relstorage config (zope.conf) pointing to the database 'zodb'.
If this convert runs fine in PostgreSQL, then you at least know there is definitely something up specific to Oracle (likely server config, much less likely the client adapter implementation in RelStorage).
We have to use the Oracle Instance of our customer. Our plone application simply has to connect to it and work. We can not configure it in any way nor can we install any other database service like Postgresql.
But thank you for your postgressql.conf tuning advise. Do you recommend Postgre instead of MySQL? Because we have also some other systems where MySQL is running behind the RelStorage.
