Quick Links

Re: Odd blocking (or massively latent) issue - even with EXPLAIN

From:	"Martin French" <Martin(dot)French(at)romaxtech(dot)com>
To:	Jim Vanns <james(dot)vanns(at)framestore(dot)com>
Cc:	pgsql-performance(at)postgresql(dot)org,pgsql-performance-owner(at)postgresql(dot)org
Subject:	Re: Odd blocking (or massively latent) issue - even with EXPLAIN
Date:	2012-07-23 13:46:28
Message-ID:	OF4731D4BD.B722B5CE-ON80257A44.004B3A30-80257A44.004BABD3@LocalDomain
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Thread:
Lists:	pgsql-performance

<html><body>Hi <tt>> > We're seeing SELECT statements and even EXPLAIN (no ANAYLZE) > statements hang indefinitely until *something* (we don't know what) > releases some kind of resource or no longer becomes a massive bottle > neck. These are the symptoms. </tt> <tt>Is this in pgAdmin? Or psql on the console?</tt> <tt> > However, the system seems healthy - no table ('heavyweight') locks are > held by any session (this happens with only a few connected sessions), > all indexes are used correctly, other transactions are writing data (we > generally only have a few sessions running at a time - perhaps 10) etc. > etc. In fact, we are writing (or bgwriter is), 2-3 hundred MB/s > sometimes. </tt> <tt>What is shown in "top" and "iostat" whilst the queries are running?</tt> <tt>> > We regularly run vacuum analyze at quiet periods - generally 1-2s daily. > > These sessions (that only read data) that are blocked can block from > anything from between only 5 minutes to 10s of hours then miraculously > complete successfully at once. > </tt> <tt>Are any "blockers" shown in pg_stat_activity? </tt> <tt>> > checkpoint_segments = 128 > maintenance_work_mem = 256MB > synchronous_commit = off > random_page_cost = 3.0 > wal_buffers = 16MB > shared_buffers = 8192MB > checkpoint_completion_target = 0.9 > effective_cache_size = 18432MB > work_mem = 32MB > effective_io_concurrency = 12 > max_stack_depth = 8MB > log_autovacuum_min_duration = 0 > log_lock_waits = on > autovacuum_vacuum_scale_factor = 0.1 > autovacuum_naptime = 8 > autovacuum_max_workers = 4 </tt> <tt>Memory looks reasonably configured to me. effective_cache_size is only an indication to the planner and is not actually allocated. </tt> <tt>Is anything being written to the logfiles?</tt> <tt>Cheers</tt>=============================================

Romax Technology Limited
Rutherford House
Nottingham Science & Technology Park
Nottingham,
NG7 2PZ
England

Telephone numbers:
+44 (0)115 951 88 00 (main)

For other office locations see:
http://www.romaxtech.com/Contact
=================================
===============
E-mail: info(at)romaxtech(dot)com
Website: www.romaxtech.com
=================================

================
Confidentiality Statement
This transmission is for the addressee only and contains information that is confidential and privileged.
Unless you are the named addressee, or authorised to receive it on behalf of the addressee
you may not copy or use it, or disclose it to anyone else.
If you have received this transmission in error please delete from your system and contact the sender. Thank you for your cooperation.
=================================================
</body></html>

Attachment	Content-Type	Size
unknown_filename	text/html	3.2 KB

In response to

Odd blocking (or massively latent) issue - even with EXPLAIN at 2012-07-23 08:41:45 from Jim Vanns

Responses

Re: Odd blocking (or massively latent) issue - even with EXPLAIN at 2012-07-23 14:46:03 from Jim Vanns

Browse pgsql-performance by date

	From	Date	Subject
Next Message	Andrew Dunstan	2012-07-23 13:47:16	Re: Checkpointer split has broken things dramatically (was Re: DELETE vs TRUNCATE explanation)
Previous Message	Craig Ringer	2012-07-23 13:04:46	Re: Checkpointer split has broken things dramatically (was Re: DELETE vs TRUNCATE explanation)