Timeouts on Parallel Requests about django-sequences HOT 6 CLOSED

How many parallel connections do you have and at what rate are you generating IDs? The technique used by django-sequences on PostgreSQL < 9.5 likely doesn't scale to hundreds of connections creating tens of IDs per second.

Also, do you generate several IDs within the same database transaction? In that case you need to create them in a consistent order to avoid deadlocks.

from django-sequences.

@aaugustin, I'm trying up to 30 parallel connections, requesting about 10 request per second.
And no - only one ID per request.
It happens also with much lower number of parallel connections and rps- 5 users and about 2 rps.

from django-sequences.

That could be pushing the limits of the implementation on PostgreSQL < 9.5, depending on the hardware you're using to run PostgreSQL.

You should check the health of your database when running this workload, with the help of your DBA if you have one.

You should also get better results on PostgreSQL 9.5 which has a more optimized implementation.

from django-sequences.

@aaugustin , thanks for your answer.

When not using django-sequences seems like it works fine on those loads.

some more technical data that may help with diagnose the problem -
We're using gunicorn 19.6.0 configured to 5 threads, 3 servers, postgreSql with max_connections of 856, Django's CONN_MAX_AGE is currently set to 600.

Does it sound reasonable?

from django-sequences.

django-sequences is really designed to generate gapless sequences. This is a regulatory requirement in some contexts (e.g. accounting). These tend not to be write-heavy contexts, or at least not concurrent-write-heavy contexts.

This causes PostgreSQL to do a lot of work to synchronize between connections attempting to increment the counter. This is where the overhead comes from.

If you don't need gapless sequences, you can probably find a less expensive solution (in terms of database load), perhaps involving PostgreSQL counters.

If you do need gapless sequences, then you need either a database that's fast enough to handle the transactional workload or another solution for generating IDs.

from django-sequences.

I just noticed this limitation is discussed in the README:

Database transactions that call get_next_value for a given sequence are serialized. In other words, when you call get_next_value in a database transaction, other callers which attempt to get a value from the same sequence will block until the transaction completes, either with a commit or a rollback. You should keep such transactions short to minimize the impact on performance.

from django-sequences.