Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 9AB109FB446 for ; Sat, 14 Apr 2007 01:45:10 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 35162-05 for ; Sat, 14 Apr 2007 01:45:06 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from web50303.mail.re2.yahoo.com (web50303.mail.re2.yahoo.com [206.190.38.57]) by postgresql.org (Postfix) with SMTP id 8644D9FB444 for ; Sat, 14 Apr 2007 01:45:06 -0300 (ADT) Received: (qmail 21864 invoked by uid 60001); 14 Apr 2007 04:45:05 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Received:X-Mailer:Date:From:Subject:To:MIME-Version:Content-Type:Message-ID; b=hv5JCQLlGxRa83Rze82Si4V+sm2nhNt/BjWDx760LgLtwvQmwSacU5cXl3nCFyKW3qG0OCiY60qKr4/A4zU0GlkXFOLnJrGEAhO4eoegxY1N/pnbZ9KWRNXnZsmDjoP1M0/4kzORH1apmvyNVWNmRCZf5tO/CVE4EgkqfdUIn5M=; Received: from [72.231.8.153] by web50303.mail.re2.yahoo.com via HTTP; Fri, 13 Apr 2007 21:45:05 PDT X-Mailer: YahooMailRC/478 YahooMailWebService/0.7.41.10 Date: Fri, 13 Apr 2007 21:45:05 -0700 (PDT) From: ogjunk-pgjedan@yahoo.com Subject: Fast SELECT sometimes blocks To: pgsql-admin@postgresql.org MIME-Version: 1.0 Content-Type: text/plain; charset=ascii Message-ID: <820195.21018.qm@web50303.mail.re2.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=4.317 tagged_above=0 required=5 tests=BAYES_50=0.001, DNS_FROM_RFC_ABUSE=0.2, DNS_FROM_RFC_POST=1.708, DNS_FROM_RFC_WHOIS=1.447, NO_REAL_NAME=0.961 X-Spam-Level: **** X-Archive-Number: 200704/135 X-Sequence-Number: 25199 Hi, A perfectly normal and healthy PG 8.2.3 instance I have started acting funny - a query that normally takes only a few milliseconds sometimes runs for minutes. I thought maybe something's funny with indices, so I reindexed them all. Didn't help. Thought maybe something's funky with the whole box, so I rebooted, but that didn't help either. The symptoms are: - high load (5-10 vs. the usual 0.2 or so) - lots of processes waiting for IO. See the last ("wa") column in the vmstat output: procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu---- r b swpd free buff cache si so bi bo in cs us sy id wa 0 1 192 477508 7620 420336 0 0 1074 1279 714 204 7 14 40 39 0 0 192 476488 7628 422928 0 0 1132 90 1335 407 4 2 49 45 0 0 192 476548 7644 423952 0 0 566 118 1424 393 6 2 76 16 0 0 192 475716 7644 424732 0 0 394 4 1166 201 2 1 87 10 I run the problematic query with EXPLAIN ANALYZE and saw that it uses indices correctly and returns quickly. But once in a while it just runs for minutes, which I can see with something like this: select procpid as "PID", datname as "Database", usename as "User", query_start, now(), current_query from pg_stat_activity where ((now() - query_start) > 0) and current_query NOT LIKE '%' Now the interesting part. I monitor this box and can see when exactly the load went up - it went up suddenly and rapidly at *exactly* 3 AM last night, which is when my db maintenance scripts run. These scripts do: 1. VACUUM VERBOSE 2. ANALYZE 3. pg_dump (for backup) I checked the logs - nothing funky in there, nothing with "ERROR" or "WARN". But that is exactly when this problem started. Is there any way I can see why sometimes a speedy SELECT runs for several minutes? Maybe my tables grew too big and something doesn't have enough space/memory? Maybe I can see that from the vacuum verbose output? Thanks, Otis