pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feed From: Jason Coene <jcoene@gotfrag.com>
To: 'Tom Lane' <tgl@sss.pgh.pa.us>
Cc: 'Mr Pink' <mr_pink_is_the_only_pro@yahoo.com>
Cc: 'Scott Kirkwood' <scottakirkwood@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Caching of Queries
Date: Thu, 23 Sep 2004 13:22:30 -0400
Message-ID: <200409231722.i8NHMZaX014707@ms-smtp-02.nyroc.rr.com> (raw )
In-Reply-To: <27437.1095959132@sss.pgh.pa.us >
Hi Tom,
Easily recreated with Apache benchmark, "ab -n 30000 -c 3000
http://webserver ". This runs 1 query per page, everything else is cached
on webserver.
The lone query:
SELECT
id,
gameid,
forumid,
subject
FROM threads
WHERE nuked = 0
ORDER BY nuked DESC,
lastpost DESC LIMIT 8
Limit (cost=0.00..1.99 rows=8 width=39) (actual time=27.865..28.027 rows=8
loops=1)
-> Index Scan Backward using threads_ix_nuked_lastpost on threads
(cost=0.0 0..16824.36 rows=67511 width=39) (actual time=27.856..27.989
rows=8 loops=1)
Filter: (nuked = 0)
Total runtime: 28.175 ms
I'm not sure how I go about getting the stack traceback you need. Any info
on this? Results of "ps" below. System is dual xeon 2.6, 2gb ram, hardware
raid 10 running FreeBSD 5.2.1.
Jason
last pid: 96094; load averages: 0.22, 0.35, 0.38
up 19+20:50:37 13:10:45
161 processes: 2 running, 151 sleeping, 8 lock
CPU states: 12.2% user, 0.0% nice, 16.9% system, 1.6% interrupt, 69.4%
idle
Mem: 120M Active, 1544M Inact, 194M Wired, 62M Cache, 112M Buf, 2996K Free
Swap: 4096M Total, 4096M Free
PID USERNAME PRI NICE SIZE RES STATE C TIME WCPU CPU COMMAND
50557 pgsql 98 0 95276K 4860K select 0 24:00 0.59% 0.59% postgres
95969 pgsql 4 0 96048K 34272K sbwait 0 0:00 2.10% 0.29% postgres
95977 pgsql -4 0 96048K 29620K semwai 2 0:00 1.40% 0.20% postgres
96017 pgsql 4 0 96048K 34280K sbwait 0 0:00 2.05% 0.20% postgres
95976 pgsql -4 0 96048K 30564K semwai 3 0:00 1.05% 0.15% postgres
95970 pgsql -4 0 96048K 24404K semwai 1 0:00 1.05% 0.15% postgres
95972 pgsql -4 0 96048K 21060K semwai 1 0:00 1.05% 0.15% postgres
96053 pgsql -4 0 96048K 24140K semwai 3 0:00 1.54% 0.15% postgres
96024 pgsql -4 0 96048K 22192K semwai 3 0:00 1.54% 0.15% postgres
95985 pgsql -4 0 96048K 15208K semwai 3 0:00 1.54% 0.15% postgres
96033 pgsql 98 0 95992K 7812K *Giant 2 0:00 1.54% 0.15% postgres
95973 pgsql -4 0 96048K 30936K semwai 3 0:00 0.70% 0.10% postgres
95966 pgsql 4 0 96048K 34272K sbwait 0 0:00 0.70% 0.10% postgres
95983 pgsql 4 0 96048K 34272K sbwait 2 0:00 1.03% 0.10% postgres
95962 pgsql 4 0 96048K 34268K sbwait 2 0:00 0.70% 0.10% postgres
95968 pgsql -4 0 96048K 26232K semwai 2 0:00 0.70% 0.10% postgres
95959 pgsql 4 0 96048K 34268K sbwait 2 0:00 0.70% 0.10% postgres
> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Thursday, September 23, 2004 1:06 PM
> To: Jason Coene
> Cc: 'Mr Pink'; 'Scott Kirkwood'; pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] Caching of Queries
>
> "Jason Coene" <jcoene@gotfrag.com> writes:
> > All of our "postgres" processes end up in the "semwai" state - seemingly
> > waiting on other queries to complete. If the system isn't taxed in CPU
> or
> > disk, I have a good feeling that this may be the cause.
>
> Whatever that is, I'll bet lunch that it's got 0 to do with caching
> query plans. Can you get stack tracebacks from some of the stuck
> processes? What do they show in "ps"?
>
> regards, tom lane
view thread (72+ messages) latest in thread
Message-ID: <200409231722.i8NHMZaX014707@ms-smtp-02.nyroc.rr.com>
Permalink: ../200409231722.i8NHMZaX014707@ms-smtp-02.nyroc.rr.com/
Also on: postgresql.org/message-id/200409231722.i8NHMZaX014707@ms-smtp-02.nyroc.rr.com
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: jcoene@gotfrag.com, tgl@sss.pgh.pa.us, mr_pink_is_the_only_pro@yahoo.com, scottakirkwood@gmail.com
Subject: Re: Caching of Queries
In-Reply-To: <200409231722.i8NHMZaX014707@ms-smtp-02.nyroc.rr.com>
References: <27437.1095959132@sss.pgh.pa.us> <200409231722.i8NHMZaX014707@ms-smtp-02.nyroc.rr.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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