pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedInterpreting query debug output
3+ messages / 2 participants
[nested] [flat]
* Interpreting query debug output
@ 2004-05-18 23:42 Steve Lane <slane@moyergroup.com>
2004-05-19 01:06 ` Re: Interpreting query debug output Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: Steve Lane @ 2004-05-18 23:42 UTC (permalink / raw)
To: pgsql-admin
Hello all:
I have a database that is exhibiting sluggishness under load. Suspecting
that some queries may be poorly optimized, I turned on a fair amount of
debugging output in the logs. But I could use some help interpreting it.
For the record, this is Postgres 7.2.1. I've already been rightly chastised
for using such an old version, and the upgrade is scheduled.
Here's some sample output:
2004-05-18 15:46:04 [27129] DEBUG: connection: host=127.0.0.1
user=postgres database=iep_db
2004-05-18 15:46:04 [26914] DEBUG: query: SELECT CAST(name_first || ' '
|| name_last AS TEXT) FROM iep_personnel WHERE id_personnel = $1
2004-05-18 15:46:08 [27015] DEBUG: query: select * from iep_student where
id_student = '1002863';
2004-05-18 15:46:08 [26914] DEBUG: query: SELECT $1
2004-05-18 15:46:08 [26914] DEBUG: query: SELECT CAST(name_county AS
TEXT) FROM iep_county WHERE id_county = $1
2004-05-18 15:46:05 [26892] DEBUG: QUERY STATISTICS
! system usage stats:
! 106.317726 elapsed 21.080000 user 0.240000 system sec
! [21.110000 user 0.250000 sys total]
! 0/0 [0/0] filesystem blocks in/out
! 7959/191 [8515/747] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
! 0/0 [0/0] voluntary/involuntary context switches
! postgres usage stats:
! Shared blocks: 0 read, 0 written, buffer hit rate
= 100.00%
! Local blocks: 0 read, 0 written, buffer hit rate
= 0.00%
! Direct blocks: 0 read, 0 written
Some questions:
1) The statistics appear to be reporting on a series of queries. These
queries are all from inside a function, hence a single transaction. Are the
query statistics being reported here on a per-transaction basis?
Two numbers leap out: 106 seconds for the query, and a high number on the
page faults line.
2) 106 seconds for the operation is too horrible for words. All of the
operations are where-clauses involving single, index id fields. The server
load is heavy, but I can't account for this slowness due to stupidly-written
queries. Anyone see anything suspect in the queries?
3) I don't know how to read the page faults line. Is this referring to
system virtual memory, or postgres buffer management? What does this line
mean, and what parameter does it imply needs tuning?
I have RAM to spare on this box, as follows:
[root@iep log]# cat /proc/meminfo
total: used: free: shared: buffers: cached:
Mem: 6161747968 3970502656 2191245312 0 13320192 3259764736
Swap: 2089209856 47116288 2042093568
MemTotal: 6017332 kB
MemFree: 2139888 kB
MemShared: 0 kB
Buffers: 13008 kB
Cached: 3138220 kB
SwapCached: 45144 kB
Active: 1362996 kB
Inactive: 2210704 kB
HighTotal: 5177216 kB
HighFree: 1643716 kB
LowTotal: 840116 kB
LowFree: 496172 kB
SwapTotal: 2040244 kB
SwapFree: 1994232 kB
Some key postgresql.conf parameters:
max_connections = 150
shared_buffers = 175000 # 2*max_connections, min 16
sort_mem = 32000 # min 32
effective_cache_size = 100000 # default in 8k pages
log_connections = true
log_timestamp = true
log_pid = true
debug_print_query = true
show_query_stats = true
Sorry for the big dump. I'm feeling a bit at sea in this information and I
need to know if I'm swimming in the right direction. Thanks for any help.
-- sgl
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Interpreting query debug output
2004-05-18 23:42 Interpreting query debug output Steve Lane <slane@moyergroup.com>
@ 2004-05-19 01:06 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-05-19 03:08 ` Re: Interpreting query debug output Steve Lane <slane@moyergroup.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2004-05-19 01:06 UTC (permalink / raw)
To: Steve Lane <slane@moyergroup.com>; +Cc: pgsql-admin
Steve Lane <slane@moyergroup.com> writes:
> I have a database that is exhibiting sluggishness under load. Suspecting
> that some queries may be poorly optimized, I turned on a fair amount of
> debugging output in the logs. But I could use some help interpreting it.
I think you're going at this all wrong. EXPLAIN ANALYZE should be the
first tool you turn to, not low-level stats. Browsing the archives of
the pgsql-performance mailing list may help you get started.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Interpreting query debug output
2004-05-18 23:42 Interpreting query debug output Steve Lane <slane@moyergroup.com>
2004-05-19 01:06 ` Re: Interpreting query debug output Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-05-19 03:08 ` Steve Lane <slane@moyergroup.com>
0 siblings, 0 replies; 3+ messages in thread
From: Steve Lane @ 2004-05-19 03:08 UTC (permalink / raw)
To: pgsql-admin
> From: Tom Lane <tgl@sss.pgh.pa.us>
> Date: Tue, 18 May 2004 21:06:43 -0400
> To: Steve Lane <slane@moyergroup.com>
> Cc: pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] Interpreting query debug output
>
> Steve Lane <slane@moyergroup.com> writes:
>> I have a database that is exhibiting sluggishness under load. Suspecting
>> that some queries may be poorly optimized, I turned on a fair amount of
>> debugging output in the logs. But I could use some help interpreting it.
>
> I think you're going at this all wrong. EXPLAIN ANALYZE should be the
> first tool you turn to, not low-level stats. Browsing the archives of
> the pgsql-performance mailing list may help you get started.
>
> regards, tom lane
>
Hmm. When I do a process listing I can see that there are postgres processes
occupying large chunks of CPU, sometimes in the 60-99% range, for long
enough to be noticeable in the process list. I'd like to capture those
process IDs and then correlate them with the stats captured in the log to
see why they take so much CPU.
If I want to EXPLAIN ANALYZE, I have to pick individual queries. The query
logic of the application is distributed across many source files. I'd have
to do quite some combing to recover a list of all queries the system runs. I
figure distilling the log output might be the best approach.
Given these points, is this still the wrong approach? Even if so, I'd still
love to know how to read the debug output.
-- sgl
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-05-19 03:08 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-05-18 23:42 Interpreting query debug output Steve Lane <slane@moyergroup.com>
2004-05-19 01:06 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-05-19 03:08 ` Steve Lane <slane@moyergroup.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox