Re: [PERFORM] query against pg_locks leads to large memory alloc
I now have 8 hours worth of snapshots from pg_stat_activity and pg_locks (16 snapshots from each table/view). I have turned off collection at this point, but I am still able to query pg_locks: # SELECT mode, count(mode) AS count FROM pg_locks GROUP BY mode ORDER BY mode; mode | count --+- AccessShareLock | 291 ExclusiveLock| 19 RowExclusiveLock | 4 RowShareLock | 1 SIReadLock | 7287531 (5 rows) SIReadLocks continue to grow. It seems, in general, that our application code over uses Serializable... we have produced a patch that demotes some heavy-hitting queries down to Read Committed, and we will see if this makes an impact on the number of SIReadLocks. Is it interesting that only 101557 out of 7 million SIReadLocks have a pid associated with them? -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
On Tue, Aug 19, 2014 at 11:01 AM, Kevin Grittner 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
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
Hi Andres, On Tue, Aug 19, 2014 at 10:17 AM, Andres Freund 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
On Tue, Aug 19, 2014 at 9:40 AM, Kevin Grittner 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 | 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 | 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
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
On Mon, Aug 18, 2014 at 2:21 PM, Matheus de Oliveira 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. On Mon, Aug 18, 2014 at 2:29 PM, Merlin Moncure wrote: > performance of any query to pg_locks is proportional to the setting of > max_locks_per_transaction. still, something is awry here. can you > 'explain' that query? tudb=# explain SELECT mode, count(mode) AS count FROM pg_locks GROUP BY mode ORDER BY mode; QUERY PLAN - Sort (cost=0.63..0.65 rows=200 width=32) Sort Key: l.mode -> HashAggregate (cost=0.30..0.32 rows=200 width=32) -> Function Scan on pg_lock_status l (cost=0.00..0.10 rows=1000 width=32) (4 rows) > SELECT COUNT(*) from pg_locks; ERROR: invalid memory alloc request size 1562436816 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] query against pg_locks leads to large memory alloc
Hi, I have a tool that is trying to collect stats from postgres (v9.1.13). postgres attempts to allocate more memory than is allowed: SELECT mode, count(mode) AS count FROM pg_locks GROUP BY mode ORDER BY mode; ERROR: invalid memory alloc request size 1459291560 Memory-related configs from the server: shared_buffers = 1MB work_mem = 15MB maintenance_work_mem = 400MB effective_cache_size = 5MB max_locks_per_transaction = 9000 max_pred_locks_per_transaction = 4 The machine is running CentOS 6, a 32-core AMD 6276 processor, and is configured with 64GB of memory. Transparent Huge Pages are disabled :-) Thanks in advance for your time and expertise. 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] CPU spikes and transactions
Hi, Apologies for resurrecting this old thread, but it seems like this is better than starting a new conversation. We are now running 9.1.13 and have doubled the CPU and memory. So 2x 16 Opteron 6276 (32 cores total), and 64GB memory. shared_buffers set to 20G, effective_cache_size set to 40GB. We were able to record perf data during the latest incident of high CPU utilization. perf report is below: Samples: 31M of event 'cycles', Event count (approx.): 16289978380877 44.74% postmaster [kernel.kallsyms] [k] _spin_lock_irqsave 15.03% postmaster postgres [.] 0x002ea937 3.14% postmaster postgres [.] s_lock 2.30% postmaster [kernel.kallsyms] [k] compaction_alloc 2.21% postmaster postgres [.] HeapTupleSatisfiesMVCC 1.75% postmaster postgres [.] hash_search_with_hash_value 1.25% postmaster postgres [.] ExecScanHashBucket 1.20% postmaster postgres [.] SHMQueueNext 1.05% postmaster postgres [.] slot_getattr 1.04% init [kernel.kallsyms] [k] native_safe_halt 0.73% postmaster postgres [.] LWLockAcquire 0.59% postmaster [kernel.kallsyms] [k] page_fault 0.52% postmaster postgres [.] ExecQual 0.40% postmaster postgres [.] ExecStoreTuple 0.38% postmaster postgres [.] ExecScan 0.37% postmaster postgres [.] check_stack_depth 0.35% postmaster postgres [.] SearchCatCache 0.35% postmaster postgres [.] CheckForSerializableConflictOut 0.34% postmaster postgres [.] LWLockRelease 0.30% postmaster postgres [.] _bt_checkkeys 0.28% postmaster libc-2.12.so [.] memcpy 0.27% postmaster [kernel.kallsyms] [k] get_pageblock_flags_group 0.27% postmaster postgres [.] int4eq 0.27% postmaster postgres [.] heap_page_prune_opt 0.27% postmaster postgres [.] pgstat_init_function_usage 0.26% postmaster [kernel.kallsyms] [k] _spin_lock 0.25% postmaster postgres [.] _bt_compare 0.24% postmaster postgres [.] pgstat_end_function_usage ...please let me know if we need to produce the report differently to be useful. We will begin reducing shared_buffers incrementally over the coming days. Dave Owens 541-359-2602 TU Facebook<https://app.getsignals.com/link?url=https%3A%2F%2Fwww.facebook.com%2Fteamunify&ukey=agxzfnNpZ25hbHNjcnhyGAsSC1VzZXJQcm9maWxlGICAgOCP-IMLDA&k=179943a8-e0fa-494a-f79a-f86a69d3abdc> | Free OnDeck Mobile Apps<https://app.getsignals.com/link?url=http%3A%2F%2Fwww.teamunify.com%2F__corp__%2Fondeck%2F&ukey=agxzfnNpZ25hbHNjcnhyGAsSC1VzZXJQcm9maWxlGICAgOCP-IMLDA&k=504a29f5-3415-405c-d550-195aa1ca1ee3> On Tue, Oct 15, 2013 at 8:14 PM, Merlin Moncure wrote: > On Tue, Oct 15, 2013 at 12:26 PM, Julien Cigar wrote: > > On Tue, Oct 15, 2013 at 08:59:08AM -0700, Tony Kay wrote: > >> On Mon, Oct 14, 2013 at 4:42 PM, Tomas Vondra wrote: > >> > >> > On 15.10.2013 01:00, Tony Kay wrote: > >> > > Hi, > >> > > > >> > > I'm running 9.1.6 w/22GB shared buffers, and 32GB overall RAM on a > >> > > 16 Opteron 6276 CPU box. We limit connections to roughly 120, but > >> > > our webapp is configured to allocate a thread-local connection, so > >> > > those connections are rarely doing anything more than half the time. > >> > > >> > Lower your shared buffers to about 20% of your RAM, unless you've > tested > >> > it's actually helping in your particular case. It's unlikely you'll > get > >> > better performance by using more than that, especially on older > >> > versions, so it's wiser to leave the rest for page cache. > >> > > >> > It might even be one of the causes of the performance issue you're > >> > seeing, as shared buffers are not exactly overhead-free. > >> > > >> > See this for more details on tuning: > >> > > >> >http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server > >> > >> > >> I had followed the general directions from several sources years ago, > which > >> indicate up to 40% of RAM. We've been running very large shared buffers > fo
