Re: Odd blocking (or massively latent) issue - even with EXPLAIN
От | Martin French |
---|---|
Тема | Re: Odd blocking (or massively latent) issue - even with EXPLAIN |
Дата | |
Msg-id | OF4731D4BD.B722B5CE-ON80257A44.004B3A30-80257A44.004BABD3@LocalDomain обсуждение исходный текст |
Ответ на | Odd blocking (or massively latent) issue - even with EXPLAIN (Jim Vanns <james.vanns@framestore.com>) |
Ответы |
Re: Odd blocking (or massively latent) issue - even with
EXPLAIN
(Jim Vanns <james.vanns@framestore.com>)
|
Список | pgsql-performance |
<p><font face="sans-serif" size="2">Hi</font><br /><br /><tt><font size="2">> <br />> We're seeing SELECT statementsand even EXPLAIN (no ANAYLZE) <br />> statements hang indefinitely until *something* (we don't know what)<br/>> releases some kind of resource or no longer becomes a massive bottle<br />> neck. These are the symptoms.<br/></font></tt><br /><tt><font size="2">Is this in pgAdmin? Or psql on the console?</font></tt><br /><tt><fontsize="2"><br />> However, the system seems healthy - no table ('heavyweight') locks are<br />> held by anysession (this happens with only a few connected sessions),<br />> all indexes are used correctly, other transactionsare writing data (we<br />> generally only have a few sessions running at a time - perhaps 10) etc.<br />>etc. In fact, we are writing (or bgwriter is), 2-3 hundred MB/s<br />> sometimes.<br /></font></tt><br /><tt><fontsize="2">What is shown in "top" and "iostat" whilst the queries are running?</font></tt><br /><br /><tt><fontsize="2">> <br />> We regularly run vacuum analyze at quiet periods - generally 1-2s daily.<br />> <br/>> These sessions (that only read data) that are blocked can block from<br />> anything from between only 5 minutesto 10s of hours then miraculously<br />> complete successfully at once.<br />> <br /></font></tt><br /><tt><fontsize="2">Are any "blockers" shown in pg_stat_activity?<br /></font></tt><br /><tt><font size="2">> <br />>checkpoint_segments = 128<br />> maintenance_work_mem = 256MB<br />> synchronous_commit = off<br />> random_page_cost= 3.0<br />> wal_buffers = 16MB<br />> shared_buffers = 8192MB<br />> checkpoint_completion_target= 0.9<br />> effective_cache_size = 18432MB<br />> work_mem = 32MB<br />> effective_io_concurrency= 12<br />> max_stack_depth = 8MB<br />> log_autovacuum_min_duration = 0<br />> log_lock_waits= on<br />> autovacuum_vacuum_scale_factor = 0.1<br />> autovacuum_naptime = 8<br />> autovacuum_max_workers= 4<br /></font></tt><br /><tt><font size="2">Memory looks reasonably configured to me. effective_cache_sizeis only an indication to the planner and is not actually allocated. <br /></font></tt><br /><tt><fontsize="2">Is anything being written to the logfiles?</font></tt><br /><br /><tt><font size="2">Cheers</font></tt><fontface="sans-serif">============================================= Romax Technology LimitedRutherford House Nottingham Science & Technology Park Nottingham, NG7 2PZ England Telephone numbers: +44 (0)115951 88 00 (main) For other office locations see: http://www.romaxtech.com/Contact ================================================ E-mail: info@romaxtech.com Website: www.romaxtech.com ================================================= Confidentiality Statement This transmission is for the addressee only andcontains information that is confidential and privileged. Unless you are the named addressee, or authorised to receiveit on behalf of the addressee you may not copy or use it, or disclose it to anyone else. If you have received thistransmission in error please delete from your system and contact the sender. Thank you for your cooperation. =================================================</font>
В списке pgsql-performance по дате отправления:
Следующее
От: Andrew DunstanДата:
Сообщение: Re: Odd blocking (or massively latent) issue - even with EXPLAIN