X-Original-To: pgsql-admin-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 917259FB202 for ; Thu, 27 Jul 2006 12:28:21 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 09198-03 for ; Thu, 27 Jul 2006 12:28:10 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mail.89glass.com (mail.89glass.com [72.10.2.250]) by postgresql.org (Postfix) with SMTP id 628709FB206 for ; Thu, 27 Jul 2006 12:28:10 -0300 (ADT) Received: (qmail 17254 invoked by uid 1006); 27 Jul 2006 15:28:09 -0000 Received: from 192.168.254.111 by lumberjack (envelope-from , uid 201) with qmail-scanner-1.25 (clamdscan: 0.67. Clear:RC:1(192.168.254.111):. Processed in 0.055101 secs); 27 Jul 2006 15:28:09 -0000 Comment: DomainKeys? See http://antispam.yahoo.com/domainkeys DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=domkey; d=rbsinteractive.com; b=nysxrdCOw1Yx41tY750fEpivqBPXj3luY4+uo9j5xoqAjQCETWa+339ybsbsDOJ1v/CmIzKzj/xG44hseUc1WtBreoCi4eZA4vD5ndpXHn8UxZSe+RNPM5TeDgHDWtVTBVmIMfL8R6HkWcZfTFpnXOBcuhqpLl9L8c9pTXyqopU= ; Received: from unknown (HELO ?192.168.254.111?) (192.168.254.111) by mail.89glass.com with SMTP; 27 Jul 2006 15:28:08 -0000 Message-ID: <44C8DB88.80708@rbsinteractive.com> Date: Thu, 27 Jul 2006 08:28:08 -0700 From: Nolan Cafferky User-Agent: Mozilla Thunderbird 1.0.6 (Windows/20050716) X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-admin@postgresql.org Subject: VACUUM ANALYZE suddenly taking forever Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200607/237 X-Sequence-Number: 22483 Synopsis: VACUUM ANALYZE on full database used to take just a few minutes, now it takes several hours, with no apparant improvement in successive runs. Details: I have a production database server hosting two heavily used databases and not much else. We're currently running postgres 8.0.8. Normally we have a VACUUM ANALYZE run nightly on both databases, which only takes a couple of minutes each to complete. We also have a report that runs hourly on one of the databases and dumps a large amount of data into a materialized view. It normally takes 10-20 minutes (we could probably optimize it, but it's never made it up the priority list). Anyway, about two nights ago, the hourly report started running indefinitely, and we've had to turn it off, after having 16 copies of it waiting in line for the first to finish. Since then, VACUUM ANALYZE has been taking several hours instead of several minutes on both databases. Yesterday I ran the VACUUM ANALYZE manually on both databases, hoping that there was just some transient cleanup problem, but we've had the same results today. What would cause this, and what can I do to fix it? For the moment, I'm going to claim the "we didn't change anything!" mantra - no development we've done in the past few days seems like it would significantly influence both databases. The so far untried ideas I've had are: * Try out the autovacuum service * Re-index tables (this hasn't been done for at least months, maybe never) * Do some selective VACUUM FULL on high-use tables (materialized view for report seems like a likely culprit, but also seems like it wouldn't influence both databases) * Restart postgres, restart the machine itself, and other useless handwaving \begin{more-background-information} * The database server is a quad Opteron, about 2GHz each. 8 GB of RAM, and a several hard disk RAID. It's burly. I believe we're running on a Gentoo linux installation, although postgres was installed from source. Again, we're running postgres 8.0.8. Here's some sample output from a "vmstat 1 5" that I just ran: procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu---- r b swpd free buff cache si so bi bo in cs us sy id wa 0 0 1208 5658464 0 2256384 0 0 554 344 1 1 10 2 83 6 1 0 1208 5640272 0 2273928 0 0 24 476 1405 1885 12 3 83 2 1 0 1208 5652368 0 2258628 0 0 0 560 1194 663 6 1 91 2 0 0 1208 5653392 0 2259104 0 0 16 750 1979 4362 15 4 78 2 1 0 1208 5649744 0 2259716 0 0 24 661 1651 3114 21 4 73 2 * Yes, so far we've been doing a direct VACUUM ANALYZE on everything, plus VACUUM FULL ANALYZE on a few tables, instead of using the autovacuum service like we should. It seems like there wouldn't be such an abrupt change in performace because of that. * Shortly after killing the 16 or so backed-up reports, the partition postgres had the data/subtrans directory in filled up, and we had a bunch of "No space left on device" errors for a minute or two. The partitions do deserve some rearranging, but for now we've made some adjustments and postgres is wallowing in free disk space. \end{more-background-information} Suggestions? -- Nolan Cafferky Software Developer IT Department RBS Interactive nolan.cafferky@rbsinteractive.com