Hey everyone!
We noticed that under high load of registrations and using the pua module we
can reach a state where the PUA table bloats our Postgres to several GB.
However, a SELECT on pua only shows a few hundred entries. The other half
million or so entries are obsoleted but not cleaned up.
The reason for this seems to be that the autovacuumer of the Postgres can't get
to work because the PUA table is almost permanently locked.
Our Postgres logs are full with errors about the autovacuumer being canceled
because it couldn't get to work.
Looking at modules/db_postgres/km_dbase.c::db_postgres_start_transaction() I
noticed that there are two locking modes used:
str lock_write_end_str = str_init(" IN EXCLUSIVE MODE");
str lock_full_end_str = str_init(" IN ACCESS EXCLUSIVE MODE");
According to
https://www.postgresql.org/docs/9.2/static/explicit-locking.html
these modes mean forbidding everything else except reads and forbidding
everything else.
However, according to this documentation ROW EXCLUSIVE is the standard lock for
entry altering operations (INSERT, UPDATE, DELETE). And this would still allow
the autovacuumer to do its job.
Is there a specific reason why these more restricting locking modes are used
instead of ROW EXCLUSIVE?
Thanks.
Best regards,
Stefan
_______________________________________________
SIP Express Router (SER) and Kamailio (OpenSER) - sr-users mailing list
sr-users@lists.sip-router.org
http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-users
