psycopg2 out of shared memory and hints of increase max_pred_locks_per_transaction

Question

While inserting a lot of data into postgresql 9.1. using a Python script, we are getting the following error on this query:

X: psycopg2.ProgrammingError in /home/hosting/apps/X
X_psycopg.py:162 in : Execute 'execute' (
                        SELECT * FROM xml_fifo.fifo
                        WHERE type_id IN (1,2)
                        ORDER BY type_id, timestamp LIMIT 10
                        ): out of shared memory
HINT:  You might need to increase max_pred_locks_per_transaction

We increased this number but still we get a out of shared memory (max_pred_locks_per_transaction = 192). Everytime we start the script again it runs for some time then gives this error message. On Postgres 8.1 we did not have this problem.

Here is a piece of the postgresql log file:

2012-06-28 02:55:43 CEST HINT:  Use the escape string syntax for backslashes, e.g., E'\\'.
2012-06-28 02:55:43 CEST WARNING:  nonstandard use of \\ in a string literal at character 271
2012-06-28 02:55:43 CEST HINT:  Use the escape string syntax for backslashes, e.g., E'\\'.
2012-06-28 02:55:43 CEST WARNING:  nonstandard use of \\ in a string literal at character 271
2012-06-28 02:55:43 CEST HINT:  Use the escape string syntax for backslashes, e.g., E'\\'.
2012-06-28 02:56:11 CEST WARNING:  there is already a transaction in progress
2012-06-28 02:57:01 CEST WARNING:  there is already a transaction in progress
2012-06-28 02:57:01 CEST ERROR:  out of shared memory
2012-06-28 02:57:01 CEST HINT:  You might need to increase max_pred_locks_per_transaction.
2012-06-28 02:57:01 CEST STATEMENT:
                                SELECT * FROM xml_fifo.fifo
                                WHERE type_id IN (1,2)
                                ORDER BY type_id ASC, timestamp LIMIT 10

2012-06-28 02:57:01 CEST ERROR:  out of shared memory
2012-06-28 02:57:01 CEST HINT:  You might need to increase max_pred_locks_per_transaction.
2012-06-28 02:57:01 CEST STATEMENT:
                                SELECT * FROM xml_fifo.fifo
                                WHERE type_id IN (1,2)
                                ORDER BY type_id ASC, timestamp LIMIT 10

What would be the problem?

score 8 · Answer 1 · answered Apr 21 '12 at 23:58

8

PostgreSQL added new functionality to SERIALIZABLE transactions in version 9.1, to avoid some serialization anomalies which were previously possible at that isolation level. The error you are seeing is only possible when using these new serializable transactions. Some workloads have run into the issue you describe when using serializable transactions in 9.1.

One solution would be to use the REPEATABLE READ transaction isolation level instead of SERIALIZABLE. This will give you exactly the same behavior that SERIALIZABLE transactions did in PostgreSQL versions before 9.1. Before deciding to do that, you might want to read up on the differences, so that you know whether it is likely to be worthwhile to instead reconfigure your environment to avoid the issue at the SERIALIZABLE isolation level:

http://www.postgresql.org/docs/9.1/interactive/transaction-iso.html

http://wiki.postgresql.org/wiki/SSI

If increasing max_pred_locks_per_transaction doesn't fix it (and you could try going significantly higher without chewing up too much RAM), you could try increasing max_connections (without increasing actual connections used).

I worked on the Serializable Snapshot Isolation feature for 9.1, along with Dan R.K. Ports of MIT. The cause of this problem is that the heuristic for combining multiple fine-grained predicate locks into a single coarser-grained lock is really simple in this initial version. I'm sure it can be improved, but any information you could give me on the circumstances under which it is hitting this problem would be valuable in terms of designing a better heuristic. If you could tell me a little bit about the number of CPUs you are using, the number of active database connections, and a bit about the workload where you hit this, I would really appreciate it.

Thanks for any info, and my apologies for the problem.

answered Apr 21 '12 at 23:58

kgrittn

18,113
3
39
47

We work with the following hardware: - Dual haxacore with hyperthreading, so 24 logical processors - 24GB memory - 160GB raid 1 Intel SSD storage partitioned in 1 volume for postgresql - avg 100-500 short period db connections – Brambo76 May 20 '12 at 11:03
Hi Kgrittn, the problem did occur again, i watch the load but it is not significant: 2.9% CPU (load 1.2) and 578mb Postgresql – Brambo76 Jun 28 '12 at 06:14
I also added some postgresql logfile to the question, maybe you can see what the problem is. – Brambo76 Jun 28 '12 at 08:43
So far the details I've gotten for people who have run into this error the best options for them in terms of performance would be to either boost the configuration parameter as suggested in the hint, and/or to use a connection pool to reduce the number of active database transactions. http://wiki.postgresql.org/wiki/Number_Of_Database_Connections Trying to run 500 queries at one time on 12 cores is never going to be the most efficient option. – kgrittn Jun 28 '12 at 12:43
We are hitting this issue. max_connections 600, max_pred at 3000, 16 cores, 32GB RAM, 24GB shared buffers. High query load. – Tony K. Oct 15 '12 at 03:08
@TonyK.: try pgbouncer in transaction mode; limit database connections from pgbouncer to 50, and allow 600 client connections. You might also want to try lower settings of shared_buffers; although it's not totally *unheard-of* for a setting that high to be optimal, it is unusual. If you want to pursue this further, start a new question. – kgrittn Oct 17 '12 at 18:25
@kgrittn: Asked: http://stackoverflow.com/questions/12946715/can-postgresql-9-1-leak-locks-out-of-shared-memory-increase-max-pred-locks-per – Tony K. Oct 18 '12 at 03:40

psycopg2 out of shared memory and hints of increase max_pred_locks_per_transaction

1 Answers1