Re: Caching of Queries - Mailing list pgsql-performance

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