Re: EXPLAIN SELECT .. does not return

On 12/5/05, David Link <dlink(at)soundscan(dot)com> wrote:
> Hi, This has become a major problem for us. Thank you in advance for
> your help.
>
> OS: SUSE Linux 2.6.5-7.191-bigsmp
> PostgreSQL: 7.4.8
> Application: ModPerl Web application using DBI.pm
> Database size: 100 Gb, 1025 Tables.
>
> Problem: EXPLAIN SELECT ... does not return.
>
> Description:
>
> The Application uses an EXPLAIN cost to determine whether a client's dynamic
> request for data is too demanding for the server so it can gracefully deny
> them. (Currently, anything over cost=0.00..500000.00).
>
> The system gets about 3000 page requests a day.
>
> Certain SQL Queries, I believe those with many table joins, when run as
> EXPLAIN plans, never return. As a result they seem to stay churning in the
> system. Once that happens other queries build up and the performance of the
> whole database server grinds to a halt. Postgresql never dies, but
> eventually, user requests start timing out.
>
> This happens on average two or three times a week. I kill an offending
> process and all's well again. I have not been able to identify with
> certainty
> an offending SQL statement.
>
> Config params, that have changed from default:
>
> tcpip_socket = true
> max_connections = 200
> shared_buffers = 2000
> sort_mem = 1048576
> vacuum_mem = 65536
> max_fsm_pages = 100000
> max_fsm_relations = 1000
> max_files_per_process = 1000
> fsync = false
> wal_sync_method = fsync
> wal_buffers = 800
> checkpoint_segments = 30
> commit_delay = 100
> commit_siblings = 50
> effective_cache_size = 1000
> random_page_cost = 4
> geqo = true
> geqo_threshold = 14
> default_statistics_target = 100
> from_collapse_limit = 13
> join_collapse_limit = 13
>
> Note: we load lumps of data ea. week. Then primarily it is a readonly
> database.
>
>

when you have thoses cases, you can take a look in pg_stats_activity
to find the offending query...

or simply logs all queries

--
Atentamente,
Jaime Casanova
(DBA: DataBase Aniquilator ;)