X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 7EE25329C89 for ; Thu, 23 Sep 2004 18:23:04 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 08053-04 for ; Thu, 23 Sep 2004 17:22:44 +0000 (GMT) Received: from ms-smtp-02.nyroc.rr.com (ms-smtp-02.nyroc.rr.com [24.24.2.56]) by svr1.postgresql.org (Postfix) with ESMTP id F2DCC329C63 for ; Thu, 23 Sep 2004 18:22:43 +0100 (BST) Received: from vaio (roc-66-66-153-192.rochester.rr.com [66.66.153.192]) by ms-smtp-02.nyroc.rr.com (8.12.10/8.12.10) with ESMTP id i8NHMZaX014707; Thu, 23 Sep 2004 13:22:36 -0400 (EDT) Message-Id: <200409231722.i8NHMZaX014707@ms-smtp-02.nyroc.rr.com> From: "Jason Coene" To: "'Tom Lane'" Cc: "'Mr Pink'" , "'Scott Kirkwood'" , Subject: Re: Caching of Queries Date: Thu, 23 Sep 2004 13:22:30 -0400 MIME-Version: 1.0 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit X-Mailer: Microsoft Office Outlook, Build 11.0.5510 In-reply-to: <27437.1095959132@sss.pgh.pa.us> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 Thread-Index: AcShj4qLbo55O8Z/TNGioECdKf2v1wAANwkg X-Virus-Scanned: Symantec AntiVirus Scan Engine X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200409/245 X-Sequence-Number: 8373 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" 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