Skip site navigation (1) Skip section navigation (2)

Re: stats collector suddenly causing lots of IO

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Josh Kupershmidt <schmiddy(at)gmail(dot)com>
Cc: Chris <lists(at)deksai(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: stats collector suddenly causing lots of IO
Date: 2010-04-16 16:25:46
Message-ID: 10744.1271435146@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
Josh Kupershmidt <schmiddy(at)gmail(dot)com> 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(at)\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(at)\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(at)\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(at)\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(at)\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(at)\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(at)\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

In response to

Responses

pgsql-performance by date

Next:From: Josh KupershmidtDate: 2010-04-16 16:31:51
Subject: Re: stats collector suddenly causing lots of IO
Previous:From: Tom LaneDate: 2010-04-16 15:45:48
Subject: Re: Planner not using column limit specified for one column for another column equal to first

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group