Brian Ferhle <brianf@xxxxxxxxxxxxxxxxxxx> wrote: > I've got a situation where I need to increase > max_pred_locks_per_transaction value to allow the addition of a > slony node to complete on postgres 9.1. We had this issue before > and we increased the default value from 64 to 128, but now we have > a sync that takes even longer and the value for 128 isn't enough > either. > > Looking at the activity I saw in pg_locks, I believe I need to set > it to at least 256, but a value this high I want to make sure that > I don't cause other issues with the system. The docs mention that > it may need to request more system v shared memory, but should I > also re-calculate other parameters such as work_mem, > shared_buffers, etc? > > Current System: > Postgres 9.1.3 > 252 GB system memory > shared_buffers 40GB > work_mem 200MB > max_connections = 300 > max_prepared_transactions = 0 > > free -g > total used free shared buffers > cached > Mem: 252 239 12 0 1 > 221 > > I have a lot of cashed memory, but I just want to make sure this > isn't something that cascades out of control and I'm suddenly > having major 'out of memory' issues. Given all the other settings, doubling max_pred_locks_per_transaction would probably increase the shared memory needs by something less than 24MB. As long as the OS isn't near its limit on shared memory allocations, this would come out of OS cache without any other significant effects. The other issue that jumps out at me, though, is max_connections = 300. How many cores are in this machines, and what does the storage system look like? You might well benefit from a connection pool which limits the number of concurrent transactions which are active. In general, 300 connections is more that what is optimal for both throughput and latency, and when using serializable transactions you might see the impact of too many connections rather sooner than at other transaction isolation levels. We have a web application which at any one moment typically has several thousand users actively hitting it, and we incrementally changed our connection pool size until we found the "sweet spot" where we got best performance for that load -- it was with 35 connections for the web application and 6 connections for replicating data from the 72 sources that feed into the database. That's on a system with 16 cores, 40 spindles, and a moderate level of caching (5TB of database and 128GB RAM). On the other hand, I would really like to improve the heuristic used for promoting predicate locks of one granularity to another, to allow a more graceful performance degradation when predicate locks get tight, but I've lacked data on what sort of workloads hit this. If you could send me (of list) a copy of your pg_locks data when you are at or approaching this problem, it would be helpful in adjusting this. A rough description of the workload would help, too. -Kevin -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
