Re: [PERFORM] query against pg_locks leads to large memory alloc
Tom Lane t...@sss.pgh.pa.us wrote: Kevin Grittner kgri...@ymail.com writes: Dave Owens d...@teamunify.com wrote: max_connections = 450 ...we have found that we run out of shared memory when max_pred_locks_per_transaction is less than 30k. It gathers the information in memory to return for all those locks (I think both the normal heavyweight locks and the predicate locks do that). 450 * 3 is 13.5 million predicate locks you could have, so they don't need a very big structure per lock to start adding up. I guess we should refactor that to use a tuplestore, so it can spill to disk when it gets to be more than work_mem. Seems to me the bigger issue is why does he need such a huge max_pred_locks_per_transaction setting? It's hard to believe that performance wouldn't tank with 10 million predicate locks active. Whether you can do select * from pg_locks seems pretty far down the list of concerns about this setting. It would be interesting to know more about the workload which is capable of that, but it would be a lot easier to analyze what's going on if we could look at where those locks are being used (in summary, of course -- nobody can make sense of 10 million detail lines). About all I can think to ask at this point is: how many total tables and indexes are there in all databases in this cluster (counting each partition of a partitioned table as a separate table)? With the promotion of finer-grained locks to courser ones this should be pretty hard to hit without a very large number of tables. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] query against pg_locks leads to large memory alloc
Hi Kevin, Looking at pg_stat_all_tables and pg_stat_all_indexes on our four databases we have: 1358 tables 1808 indexes The above totals do not include template1, template0, or postgres databases. We do not use partitioned tables. Only one database has a meaningful level of concurrency (New Relic reports about 30k calls per minute, from our main webapp). That database alone consists of 575 tables and 732 indexes. Dave Owens -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] query against pg_locks leads to large memory alloc
Dave Owens d...@teamunify.com wrote: 1358 tables 1808 indexes Hmm, that's not outrageous. How about long-running transactions? Please check pg_stat_activity and pg_prepared_xacts for xact_start or prepared (respectively) values older than a few minutes. Since predicate locks may need to be kept until an overlapping transaction completes, a single long-running transaction can bloat the lock count. Also, could you show use the output from?: SELECT version(); -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] query against pg_locks leads to large memory alloc
On Tue, Aug 19, 2014 at 9:40 AM, Kevin Grittner kgri...@ymail.com wrote: Hmm, that's not outrageous. How about long-running transactions? Please check pg_stat_activity and pg_prepared_xacts for xact_start or prepared (respectively) values older than a few minutes. Since predicate locks may need to be kept until an overlapping transaction completes, a single long-running transaction can bloat the lock count. I do see a handful of backends that like to stay IDLE in transaction for minutes at a time. We are refactoring the application responsible for these long IDLE times, which will hopefully reduce the duration of their connections. # select backend_start, xact_start, query_start, waiting, current_query from pg_stat_activity where xact_start now() - interval '3 minutes'; backend_start | xact_start | query_start | waiting | current_query ---+---+---+-+--- 2014-08-19 09:48:00.398498-07 | 2014-08-19 09:49:19.157478-07 | 2014-08-19 10:03:04.99303-07 | f | IDLE in transaction 2014-08-19 09:38:00.493924-07 | 2014-08-19 09:53:47.00614-07 | 2014-08-19 10:03:05.003496-07 | f | IDLE in transaction (2 rows) ... now() was 2014-08-19 10:03 in the above query. I do not see anything in pg_prepared_xacts, we do not use two-phase commit. Also, could you show use the output from?: SELECT version(); version --- PostgreSQL 9.1.13 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-4), 64-bit (1 row) -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] query against pg_locks leads to large memory alloc
On 2014-08-18 14:36:52 -0700, Dave Owens wrote: On Mon, Aug 18, 2014 at 2:21 PM, Matheus de Oliveira matioli.math...@gmail.com wrote: Do you really need such large values? What is your max_connections value? max_connections = 450 ...we have found that we run out of shared memory when max_pred_locks_per_transaction is less than 30k. What was the precise error message when that happened? Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] query against pg_locks leads to large memory alloc
Hi Andres, On Tue, Aug 19, 2014 at 10:17 AM, Andres Freund and...@2ndquadrant.com wrote: max_connections = 450 ...we have found that we run out of shared memory when max_pred_locks_per_transaction is less than 30k. What was the precise error message when that happened? 2014-07-31 15:00:25 PDT 53dabbea.29c7ERROR: 53200: out of shared memory 2014-07-31 15:00:25 PDT 53dabbea.29c7HINT: You might need to increase max_pred_locks_per_transaction. 2014-07-31 15:00:25 PDT 53dabbea.29c7LOCATION: CreatePredicateLock, predicate.c:2247 2014-07-31 15:00:25 PDT 53dabbea.29c7STATEMENT: SELECT member_id, SUM(credit_quarters) FROM ondeck_tallies_x WHERE team_id = $1 AND credit_quarters 0 AND EXTRACT(day from current_timestamp - dt_attendance_taken) = $2 GROUP BY member_id -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] query against pg_locks leads to large memory alloc
I wonder if it would be helpful to restart the database, then begin gathering information pg_locks while it can still respond to queries. I speculate that this is possible because the amount of memory needed to query pg_locks continues to grow (around 1900MB now). Dave Owens -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] query against pg_locks leads to large memory alloc
Dave Owens d...@teamunify.com wrote: I do see a handful of backends that like to stay IDLE in transaction for minutes at a time. We are refactoring the application responsible for these long IDLE times, which will hopefully reduce the duration of their connections. That may help some. Other things to consider: - If you can use a connection pooler in transaction mode to reduce the number of active connections you may be able to improve performance all around, and dodge this problem in the process. Very few systems can make efficient use of hundreds of concurrent connections, but for various reasons fixing that with a connection pooler is sometimes difficult. - If you have transactions (or SELECT statements that you run outside of explicit transactions) which you know will not be modifying any data, flagging them as READ ONLY will help contain the number of predicate locks and will help overall performance. (If the SELECT statements are not in explicit transactions, you may have to put them in one to allow the READ ONLY property to be set, or set default_transaction_read_only in the session to accomplish this.) - Due to the heuristics used for thresholds for combining fine-grained locks into coarser ones, you might be able to work around this by boosting max_connections above the number you are going to use. Normally when you increase max_pred_locks_per_transaction it increases the number of page locks it will allow in a table or index before it combines them into a relation lock; increasing max_connections doesn't affect the granularity promotion threshold, but it increases the total number of predicate locks allowed, so if you boost that and reduce max_pred_locks_per_transaction in proportion, you may be able to dodge the problem. It's an ugly workaround, but it might get you into better shape. If that does work, it's good evidence that we should tweak those heuristics. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] query against pg_locks leads to large memory alloc
Dave Owens d...@teamunify.com wrote: I wonder if it would be helpful to restart the database, then begin gathering information pg_locks while it can still respond to queries. I speculate that this is possible because the amount of memory needed to query pg_locks continues to grow (around 1900MB now). If restart is an option, that sounds like a great idea. If you could capture the data into tables where we can summarize to analyze it in a meaningful way, that would be ideal. Something like: CREATE TABLE activity_snap_1 AS SELECT * FROM pg_stat_activity; Of course, boost the number for each subsequent run. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] query against pg_locks leads to large memory alloc
On Tue, Aug 19, 2014 at 11:01 AM, Kevin Grittner kgri...@ymail.com wrote: If restart is an option, that sounds like a great idea. If you could capture the data into tables where we can summarize to analyze it in a meaningful way, that would be ideal. Something like: CREATE TABLE activity_snap_1 AS SELECT * FROM pg_stat_activity; Of course, boost the number for each subsequent run. Kevin - Would the you or the list be interested in snapshots of pg_locks as well? I can take a restart tonight and get this going on a half-hourly basis (unless you think more frequent snaps would be useful). -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] query against pg_locks leads to large memory alloc
Dave Owens d...@teamunify.com wrote: On Tue, Aug 19, 2014 at 11:01 AM, Kevin Grittner kgri...@ymail.com wrote: CREATE TABLE activity_snap_1 AS SELECT * FROM pg_stat_activity; Would the you or the list be interested in snapshots of pg_locks as well? Most definitely! I'm sorry that copied/pasted the pg_stat_activity example, I was playing with both. pg_locks is definitely the more important one, but it might be useful to try to match some of these locks up against process information as we drill down from the summary to see examples of what makes up those numbers. I can take a restart tonight and get this going on a half-hourly basis (unless you think more frequent snaps would be useful). Each half-hour should be fine as long as that gives at least three or four samples before you are unable to query pg_locks. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
