Question about Pyramid + ZODB + RelStorage performances

[tflorac]

Hi!
I'm working on a Pyramid application using ZODB and RelStorage with a PostgreSQL backend.
This application is a document management application using many file blobs which are stored on the filesystem (not in PostgreSQL), and a catalog is used to index documents metadatas. The application is running in an Apache process using mod_wsgi.
My question is that we have another application which is used to transfer documents from another repository to this one; this application is only using one process and one thread to avoid ZODB conflicts on catalog updates; when the application is started, we handle approximately 120 transactions/minute and everything is OK but after 50 to 60 minutes, transactions rate is quickly decreasing down to 30 tpm!
While looking at server processes, it seems that most activity is related to PostgreSQL; I tried to disable autovacuum but it doesn't change anything! If I reload the Apache service, the transaction rate is restarting at 120 tpm before decreasing after another hour of high activity...
So, does anyone have any idea about the origin of this increase in PostgreSQL activity and performance decrease?

Best regards,
Thierry

[jamadden]

In our experience running a very large, very active site, we didn't find performance degradation associated with just being up and processing transactions, assuming consistent transaction sizes. I wouldn't consider such degredation normal.

I would probably first start by increasing the various cache sizes (the ZODB connection's ghost cache, the RelStorage internal pickle cache). (You could also look to make sure you don't have any idle-in-transaction connections open to the database.)

But that's a shot in the dark without knowing much, much more about your application's behaviour. To find out more, look at the metrics that RelStorage produces by hooking it up to a StatsD/Graphite system with a visualization front-end (graphite itself comes with a simple one). See also the perfmetrics package documentation. RelStorage can also be configured to log some interesting performance information as well; this configuration is usually handled with environment variables (of which RelStorage supports many!). Many environment variables are documented (in the release notes and/or API documentation), but if you have the relstorage logger configured at debug level when RelStorage is imported, all the environment variables it reads will be logged.

Good luck!

[tflorac]

Hi Jason,
As I use a quite huge catalog, I actually increased the RelStorage cache size and everything seems much better now!
Thank you anyway for all your advises!
Best regards,
Thierry