Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 7FE24133655C for ; Mon, 24 Jan 2011 22:26:28 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 13512-09 for ; Tue, 25 Jan 2011 02:26:22 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from outmail149066.authsmtp.net (outmail149066.authsmtp.net [62.13.149.66]) by mail.postgresql.org (Postfix) with ESMTP id AF050133616B for ; Mon, 24 Jan 2011 22:26:20 -0400 (AST) Received: from mail-c194.authsmtp.com (mail-c194.authsmtp.com [62.13.128.121]) by punt8.authsmtp.com (8.14.2/8.14.2/Kp) with ESMTP id p0P2QKgO054931 for ; Tue, 25 Jan 2011 02:26:20 GMT Received: from Sidney-Stratton.local (dsl081-245-111.sfo1.dsl.speakeasy.net [64.81.245.111]) (authenticated bits=0) by mail.authsmtp.com (8.14.2/8.14.2) with ESMTP id p0P2QHgI020001 for ; Tue, 25 Jan 2011 02:26:18 GMT Message-ID: <4D3E34C8.9040209@agliodbs.com> Date: Mon, 24 Jan 2011 18:26:16 -0800 From: Josh Berkus User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.5; en-US; rv:1.9.1b3pre) Gecko/20090223 Thunderbird/3.0b2 MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Bloat issue on 8.3; autovac ignores HOT page splits? Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit X-Server-Quench: 7e049541-282a-11e0-80b9-0022640b883e X-AuthReport-Spam: If SPAM / abuse - report it at: http://www.authsmtp.com/abuse X-AuthRoute: OCdyZgscClZXSx8a IioLCC5HRQ8+YBZL BAkbIhBDJB8RWB5f NVxfJ1xYPXUEQkpF VCReGBUITgIzDi13 bhUrPFtCYEpPXhVq UkJLRFhTHEtuBQIJ BwAcVhxsdgRHZ3x1 Z0N9Xj4bXCR4FD4q O05WFW0GZCliaGYX HkcNdwdddQdLYx1N aAFiVXJYYXgPZihp WldrZWpvYW1VeHQO TxcnKVUbXQ4KBSUh SlgFG31/WxZWDywu Mxs4I1VUHUkXNV8/ KhMBfm5QKxYdB0VF B0VMHDQOT+X/ X-Authentic-SMTP: 61633136333939.1015:706 X-AuthFastPath: 0 (Was 255) X-AuthVirus-Status: No virus detected - but ensure you scan with your own anti-virus system. X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201101/228 X-Sequence-Number: 42015 Folks, I'm doing a postmortem on an 8.3 database which recently had to be rebuilt. The database was over 200% bloated ... 176GB as opposed to dump/reload size of 55GB. What I find really interesting is *which* tables were bloated. Consider these two tables, for example, which consist of one row which gets updated around 1000 times/day: -[ RECORD 2 ]----------+------------------------------ schemaname | public relname | general_info n_dead_tup | 12 n_live_tup | 1 changed | 8817 n_tup_hot_upd | 8817 pg_relation_size | 155648 pg_total_relation_size | 172032 -[ RECORD 4 ]----------+------------------------------ schemaname | public relname | current_info n_dead_tup | 27 n_live_tup | 1 changed | 3296 n_tup_hot_upd | 3296 pg_relation_size | 385024 pg_total_relation_size | 409600 As you can see, in both cases almost all of the updates on these tables were HOT updates. Yet these HOT updates led to bloat (hundreds of disk pages instead of the one required for each table), and autovacuum doesn't seem to think it needed to do anything about them ... neither table was *ever* autovacuumed. It looks to me like autovacuum doesn't ever consider when HOT updates lead to page splits, and so require vacuuming. Or am I diagnosing it wrong? max_fsm_pages may also have been slightly undersized. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com