Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id CB1CC632414 for ; Fri, 16 Apr 2010 13:26:02 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 99332-05 for ; Fri, 16 Apr 2010 16:25:52 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mail.postgresql.org (Postfix) with ESMTP id 47E91632393 for ; Fri, 16 Apr 2010 13:25:52 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id o3GGPkDh010745; Fri, 16 Apr 2010 12:25:46 -0400 (EDT) To: Josh Kupershmidt cc: Chris , pgsql-performance@postgresql.org Subject: Re: stats collector suddenly causing lots of IO In-reply-to: References: <20100413170105.GA29571@chris-laptop.a2hosting.com> <25952.1271370711@sss.pgh.pa.us> <20100416151900.GA3179@chris-laptop.a2hosting.com> <9857.1271431787@sss.pgh.pa.us> Comments: In-reply-to Josh Kupershmidt message dated "Fri, 16 Apr 2010 11:54:17 -0400" Date: Fri, 16 Apr 2010 12:25:46 -0400 Message-ID: <10744.1271435146@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.79 tagged_above=-10 required=5 tests=BAYES_50=0.8, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201004/212 X-Sequence-Number: 38462 Josh Kupershmidt writes: > I made a small half-empty table like this: > CREATE TABLE test_vacuum (i int PRIMARY KEY); > INSERT INTO test_vacuum (i) SELECT a FROM generate_series(1,500000) AS a; > DELETE FROM test_vacuum WHERE RANDOM() < 0.5; > and then ran: > VACUUM test_vacuum; > while an strace of the stats collector process was running. Then after > a few seconds, found the PID of the VACUUM process, and ran strace on > it. I killed them after the VACUUM finished. Outputs attached. Huh. The VACUUM strace clearly shows a boatload of TABPURGE messages being sent: sendto(7, "\2\0\0\0\350\3\0\0\5@\0\0\366\0\0\0\324\206<\24\321uC\24\320\350)\24\225\345,\24"..., 1000, 0, NULL, 0) = 1000 sendto(7, "\2\0\0\0\350\3\0\0\5@\0\0\366\0\0\0C\274?\24\365\323?\24\241N@\24\217\0309\24"..., 1000, 0, NULL, 0) = 1000 sendto(7, "\2\0\0\0\350\3\0\0\5@\0\0\366\0\0\0\375Z2\24\211\f@\0241\3047\24\357mH\24"..., 1000, 0, NULL, 0) = 1000 sendto(7, "\2\0\0\0\350\3\0\0\5@\0\0\366\0\0\0\242\3529\24\234K\'\24\17\227)\24\300\22+\24"..., 1000, 0, NULL, 0) = 1000 and the stats collector is receiving them: recvfrom(7, "\2\0\0\0\350\3\0\0\5@\0\0\366\0\0\0\324\206<\24\321uC\24\320\350)\24\225\345,\24"..., 1000, 0, NULL, NULL) = 1000 recvfrom(7, "\2\0\0\0\350\3\0\0\5@\0\0\366\0\0\0C\274?\24\365\323?\24\241N@\24\217\0309\24"..., 1000, 0, NULL, NULL) = 1000 recvfrom(7, "\2\0\0\0\350\3\0\0\5@\0\0\366\0\0\0\375Z2\24\211\f@\0241\3047\24\357mH\24"..., 1000, 0, NULL, NULL) = 1000 So this *should* have resulted in the stats file shrinking. Did you happen to notice if it did, after you did this? regards, tom lane