From dave@oneit.com.au Sat Jul  28 04:33:48 2012
Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229])
	by mail.postgresql.org (Postfix) with ESMTP id 65DEC1A5F428
	for <pgsql-performance@postgresql.org>;
	Sat, 28 Jul 2012 01:33:48 -0300 (ADT)
Received: from smtp4.wa.amnet.net.au ([203.161.124.53])
	by magus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <dave@oneit.com.au>) id 1Suyim-0007Zu-41
	for pgsql-performance@postgresql.org; Sat, 28 Jul 2012 04:33:47 +0000
Received: from localhost (localhost.localdomain [127.0.0.1])
	by smtp4.wa.amnet.net.au (Postfix) with ESMTP id 12CA47DF5A
	for <pgsql-performance@postgresql.org>;
	Sat, 28 Jul 2012 12:33:24 +0800 (WST)
X-Virus-Scanned: amavisd-new at smtp4.wa.amnet.net.au
Received: from smtp4.wa.amnet.net.au ([127.0.0.1])
	by localhost (smtp4.wa.amnet.net.au [127.0.0.1]) (amavisd-new,
	port 10024)
	with ESMTP id U1CLuOxy6ZfO for <pgsql-performance@postgresql.org>;
	Sat, 28 Jul 2012 12:33:21 +0800 (WST)
Received: from [192.168.167.135] (203.161.71.159.static.amnet.net.au
	[203.161.71.159])
	by smtp4.wa.amnet.net.au (Postfix) with ESMTP id 4CBB57E132
	for <pgsql-performance@postgresql.org>;
	Sat, 28 Jul 2012 12:33:20 +0800 (WST)
Message-ID: <50136B8D.7070502@oneit.com.au>
Date: Sat, 28 Jul 2012 12:33:17 +0800
From: David Barton <dave@oneit.com.au>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:14.0) Gecko/20120714 Thunderbird/14.0
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Postgres 9.1.4 - high stats collector IO usage
Content-Type: multipart/alternative;
	boundary="------------080307080603030405040402"
X-Pg-Spam-Score: -1.9 (-)
X-Archive-Number: 201207/347
X-Sequence-Number: 47528

This is a multi-part message in MIME format.
--------------080307080603030405040402
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hi,

I am running postgres 9.1.4 on Ubuntu 12.04 and the stats collector is 
generating very high IO usage even when nothing appears to be happening 
on the system.

I have roughly 150 different databases, each of which is running in 1 of 
roughly 30 tablespaces.  The databases are small (the dump of most is 
are under 100M, and all but 3 are under 1G, nothing larger than 2G).

Previously iotop reported the disk write speed, at ~6MB / second.  I 
went and reset the stats for every database and that shrunk the stats 
file and brought the IO it down to 1MB / second.  I still think this is 
too high for an idle database.  I've now noticed it is growing.

ls -l /var/lib/postgresql/9.1/main/pg_stat_tmp/pgstat.stat
-rw------- 1 postgres postgres 3515080 Jul 28 11:58 
/var/lib/postgresql/9.1/main/pg_stat_tmp/pgstat.stat

*<reset of stats> *

ls -l /var/lib/postgresql/9.1/main/pg_stat_tmp/pgstat.stat
-rw------- 1 postgres postgres 514761 Jul 28 12:11 
/var/lib/postgresql/9.1/main/pg_stat_tmp/pgstat.stat

*<watch the file grow>*

ls -l /var/lib/postgresql/9.1/main/pg_stat_tmp/pgstat.stat
-rw------- 1 postgres postgres 776711 Jul 28 12:25 
/var/lib/postgresql/9.1/main/pg_stat_tmp/pgstat.stat

In the 15 minutes since the reset, IO has nearly doubled to 1.6+ MB / 
second.

FWIW, I just migrated all these databases over to this new server by 
restoring from pg_dump  I was previously experiencing this on 8.3, which 
was why I upgraded to 9.1 and I also have another server with similar 
problems on 9.1.

Any help would be sincerely appreciated.


David Barton dave@oneit.com.au

--------------080307080603030405040402
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<html>
  <head>

    <meta http-equiv="content-type" content="text/html; charset=ISO-8859-1">
  </head>
  <body bgcolor="#FFFFFF" text="#000000">
    Hi,<br>
    <br>
    I am running postgres 9.1.4 on Ubuntu 12.04 and the stats collector
    is generating very high IO usage even when nothing appears to be
    happening on the system.<br>
    <br>
    I have roughly 150 different databases, each of which is running in
    1 of roughly 30 tablespaces.&nbsp; The databases are small (the dump of
    most is are under 100M, and all but 3 are under 1G, nothing larger
    than 2G).<br>
    <br>
    Previously iotop reported the disk write speed, at ~6MB / second.&nbsp; I
    went and reset the stats for every database and that shrunk the
    stats file and brought the IO it down to 1MB / second.&nbsp; I still
    think this is too high for an idle database.&nbsp; I've now noticed it is
    growing.<br>
    <br>
    ls -l /var/lib/postgresql/9.1/main/pg_stat_tmp/pgstat.stat<br>
    -rw------- 1 postgres postgres 3515080 Jul 28 11:58
    /var/lib/postgresql/9.1/main/pg_stat_tmp/pgstat.stat<br>
    <br>
    <b>&lt;reset of stats&gt; </b><br>
    <br>
    ls -l /var/lib/postgresql/9.1/main/pg_stat_tmp/pgstat.stat<br>
    -rw------- 1 postgres postgres 514761 Jul 28 12:11
    /var/lib/postgresql/9.1/main/pg_stat_tmp/pgstat.stat<br>
    <br>
    <b>&lt;watch the file grow&gt;</b><br>
    <br>
    ls -l /var/lib/postgresql/9.1/main/pg_stat_tmp/pgstat.stat<br>
    -rw------- 1 postgres postgres 776711 Jul 28 12:25
    /var/lib/postgresql/9.1/main/pg_stat_tmp/pgstat.stat<br>
    <br>
    In the 15 minutes since the reset, IO has nearly doubled to 1.6+ MB
    / second.<br>
    <br>
    FWIW, I just migrated all these databases over to this new server by
    restoring from pg_dump&nbsp; I was previously experiencing this on 8.3,
    which was why I upgraded to 9.1 and I also have another server with
    similar problems on 9.1.<br>
    <br>
    Any help would be sincerely appreciated.<br>
    <br>
    <br>
    David Barton <a class="moz-txt-link-abbreviated" href="mailto:dave@oneit.com.au">dave@oneit.com.au</a><br>
  </body>
</html>

--------------080307080603030405040402--



From pavel.stehule@gmail.com Sat Jul  28 07:08:47 2012
Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229])
	by mail.postgresql.org (Postfix) with ESMTP id B1E811A3D38B
	for <pgsql-performance@postgresql.org>;
	Sat, 28 Jul 2012 04:08:47 -0300 (ADT)
Received: from mail-vb0-f46.google.com ([209.85.212.46])
	by magus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <pavel.stehule@gmail.com>) id 1Sv18k-0001RR-Me
	for pgsql-performance@postgresql.org; Sat, 28 Jul 2012 07:08:46 +0000
Received: by vbbff1 with SMTP id ff1so3370539vbb.19
	for <pgsql-performance@postgresql.org>;
	Sat, 28 Jul 2012 00:08:29 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113;
	h=mime-version:in-reply-to:references:from:date:message-id:subject:to
	:cc:content-type;
	bh=EjnpzOwPKlhE14/JeDjjS2x13AVTgdalZ59feXYNnQg=;
	b=fTSXLcxCB5EMTEH7QUBMZeTbYIFx3Fzf4eLYteb03Q+UZSkC3XJ5N0l0/mihrovwF4
	64g9J45jcsBtNcnpg+lGJjv68Vogur3CKO0ND+B0JhU60ueDV85n0nTmtqin2qOK8DQ0
	shZyrDcZFmRgbdkOkDRC9/MBFVYY9IK3oqPJh/sZEfliM69BweMoOiGbuNI8VMLL5pzD
	NLQhSpvwek8Cl01plgKcJk3wT0YxDj1JKf5PqrU13lBQoENXum7sdz6HKRe9uE2/HL8x
	nrbDKb5feZt7KygSFQ7Imv4i5gUW6mMAbuvtq6e6okhKCNIrlHBuZLsIhu7et0aZcisq
	VWOA==
Received: by 10.52.23.107 with SMTP id l11mr4370405vdf.6.1343459309061; Sat,
	28 Jul 2012 00:08:29 -0700 (PDT)
MIME-Version: 1.0
Received: by 10.52.117.163 with HTTP; Sat, 28 Jul 2012 00:07:48 -0700 (PDT)
In-Reply-To: <50136B8D.7070502@oneit.com.au>
References: <50136B8D.7070502@oneit.com.au>
From: Pavel Stehule <pavel.stehule@gmail.com>
Date: Sat, 28 Jul 2012 09:07:48 +0200
Message-ID: 
 <CAFj8pRB=Tq-xYeMHSE=52Oqkpx=miazcqEgwHa+m3cFxTo7i2g@mail.gmail.com>
Subject: Re: Postgres 9.1.4 - high stats collector IO usage
To: David Barton <dave@oneit.com.au>
Cc: pgsql-performance@postgresql.org
Content-Type: text/plain; charset=UTF-8
X-Pg-Spam-Score: -2.7 (--)
X-Archive-Number: 201207/348
X-Sequence-Number: 47529

Hello

I had same problem with large numbers of tables - you can move
pg_stat_tmp to tmpfs filesystem - it was solution for us

Regards

Pavel

2012/7/28 David Barton <dave@oneit.com.au>:
> Hi,
>
> I am running postgres 9.1.4 on Ubuntu 12.04 and the stats collector is
> generating very high IO usage even when nothing appears to be happening on
> the system.
>
> I have roughly 150 different databases, each of which is running in 1 of
> roughly 30 tablespaces.  The databases are small (the dump of most is are
> under 100M, and all but 3 are under 1G, nothing larger than 2G).
>
> Previously iotop reported the disk write speed, at ~6MB / second.  I went
> and reset the stats for every database and that shrunk the stats file and
> brought the IO it down to 1MB / second.  I still think this is too high for
> an idle database.  I've now noticed it is growing.
>
> ls -l /var/lib/postgresql/9.1/main/pg_stat_tmp/pgstat.stat
> -rw------- 1 postgres postgres 3515080 Jul 28 11:58
> /var/lib/postgresql/9.1/main/pg_stat_tmp/pgstat.stat
>
> <reset of stats>
>
> ls -l /var/lib/postgresql/9.1/main/pg_stat_tmp/pgstat.stat
> -rw------- 1 postgres postgres 514761 Jul 28 12:11
> /var/lib/postgresql/9.1/main/pg_stat_tmp/pgstat.stat
>
> <watch the file grow>
>
> ls -l /var/lib/postgresql/9.1/main/pg_stat_tmp/pgstat.stat
> -rw------- 1 postgres postgres 776711 Jul 28 12:25
> /var/lib/postgresql/9.1/main/pg_stat_tmp/pgstat.stat
>
> In the 15 minutes since the reset, IO has nearly doubled to 1.6+ MB /
> second.
>
> FWIW, I just migrated all these databases over to this new server by
> restoring from pg_dump  I was previously experiencing this on 8.3, which was
> why I upgraded to 9.1 and I also have another server with similar problems
> on 9.1.
>
> Any help would be sincerely appreciated.
>
>
> David Barton dave@oneit.com.au



From tgl@sss.pgh.pa.us Sat Jul  28 16:14:13 2012
Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125])
	by mail.postgresql.org (Postfix) with ESMTP id 427101A3D38D
	for <pgsql-performance@postgresql.org>;
	Sat, 28 Jul 2012 13:14:13 -0300 (ADT)
Received: from sss.pgh.pa.us ([66.207.139.130])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <tgl@sss.pgh.pa.us>) id 1Sv9ee-0000g2-PJ
	for pgsql-performance@postgresql.org; Sat, 28 Jul 2012 16:14:13 +0000
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.14.5/8.14.5) with ESMTP id q6SGDsYB029997;
	Sat, 28 Jul 2012 12:13:54 -0400 (EDT)
From: Tom Lane <tgl@sss.pgh.pa.us>
To: David Barton <dave@oneit.com.au>
cc: pgsql-performance@postgresql.org
Subject: Re: Postgres 9.1.4 - high stats collector IO usage
In-reply-to: <50136B8D.7070502@oneit.com.au>
References: <50136B8D.7070502@oneit.com.au>
Comments: In-reply-to David Barton <dave@oneit.com.au>
	message dated "Sat, 28 Jul 2012 12:33:17 +0800"
Date: Sat, 28 Jul 2012 12:13:54 -0400
Message-ID: <29996.1343492034@sss.pgh.pa.us>
X-Pg-Spam-Score: -1.9 (-)
X-Archive-Number: 201207/349
X-Sequence-Number: 47530

David Barton <dave@oneit.com.au> writes:
> I am running postgres 9.1.4 on Ubuntu 12.04 and the stats collector is 
> generating very high IO usage even when nothing appears to be happening 
> on the system.

> I have roughly 150 different databases, each of which is running in 1 of 
> roughly 30 tablespaces.  The databases are small (the dump of most is 
> are under 100M, and all but 3 are under 1G, nothing larger than 2G).

That's a lot of databases.  I think your problem probably stems from
autovacuum madly trying to cover all of them.  Backing off (increasing)
autovacuum_naptime to slow its cycle might help.

			regards, tom lane



From dave@oneit.com.au Sat Jul  28 17:08:38 2012
Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229])
	by mail.postgresql.org (Postfix) with ESMTP id CB4A51A3D38D
	for <pgsql-performance@postgresql.org>;
	Sat, 28 Jul 2012 14:08:38 -0300 (ADT)
Received: from smtp1.wa.amnet.net.au ([203.161.124.50])
	by magus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <dave@oneit.com.au>) id 1SvAVG-0001xN-MH
	for pgsql-performance@postgresql.org; Sat, 28 Jul 2012 17:08:37 +0000
Received: from localhost (localhost.localdomain [127.0.0.1])
	by smtp1.wa.amnet.net.au (Postfix) with ESMTP id A2A687DF81;
	Sun, 29 Jul 2012 01:08:16 +0800 (WST)
X-Virus-Scanned: amavisd-new at smtp1.wa.amnet.net.au
Received: from smtp1.wa.amnet.net.au ([127.0.0.1])
	by localhost (smtp1.wa.amnet.net.au [127.0.0.1]) (amavisd-new,
	port 10024)
	with ESMTP id ivepT4p73Llg; Sun, 29 Jul 2012 01:08:16 +0800 (WST)
Received: from [192.168.167.135] (203.161.71.159.static.amnet.net.au
	[203.161.71.159])
	by smtp1.wa.amnet.net.au (Postfix) with ESMTP id 653207DF6B;
	Sun, 29 Jul 2012 01:08:13 +0800 (WST)
Message-ID: <50141C7A.4050607@oneit.com.au>
Date: Sun, 29 Jul 2012 01:08:10 +0800
From: David Barton <dave@oneit.com.au>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:14.0) Gecko/20120714 Thunderbird/14.0
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-performance@postgresql.org
Subject: Re: Postgres 9.1.4 - high stats collector IO usage
References: <50136B8D.7070502@oneit.com.au> <29996.1343492034@sss.pgh.pa.us>
In-Reply-To: <29996.1343492034@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Pg-Spam-Score: -1.9 (-)
X-Archive-Number: 201207/350
X-Sequence-Number: 47531

Thanks so much, Tom.

That did the job.  I increased it to every 15 minutes and it has dropped 
substantially even though the pgstat.stat file is over 1 MB again.

It is unfortunate that the IO utilisation of this seems to be O(n^2) as 
that is a big impediment to shared hosting.  Is there any type of bounty 
system where people can contribute to developing features?

Regards, David

On 29/07/12 00:13, Tom Lane wrote:
> David Barton <dave@oneit.com.au> writes:
>> I am running postgres 9.1.4 on Ubuntu 12.04 and the stats collector is
>> generating very high IO usage even when nothing appears to be happening
>> on the system.
>> I have roughly 150 different databases, each of which is running in 1 of
>> roughly 30 tablespaces.  The databases are small (the dump of most is
>> are under 100M, and all but 3 are under 1G, nothing larger than 2G).
> That's a lot of databases.  I think your problem probably stems from
> autovacuum madly trying to cover all of them.  Backing off (increasing)
> autovacuum_naptime to slow its cycle might help.
>
> 			regards, tom lane
>




From dave@oneit.com.au Sat Jul  28 17:12:19 2012
Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125])
	by mail.postgresql.org (Postfix) with ESMTP id 667151A3D38D
	for <pgsql-performance@postgresql.org>;
	Sat, 28 Jul 2012 14:12:19 -0300 (ADT)
Received: from smtp1.wa.amnet.net.au ([203.161.124.50])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <dave@oneit.com.au>) id 1SvAYq-0001gx-Kx
	for pgsql-performance@postgresql.org; Sat, 28 Jul 2012 17:12:18 +0000
Received: from localhost (localhost.localdomain [127.0.0.1])
	by smtp1.wa.amnet.net.au (Postfix) with ESMTP id 626FB7DF31;
	Sun, 29 Jul 2012 01:12:01 +0800 (WST)
X-Virus-Scanned: amavisd-new at smtp1.wa.amnet.net.au
Received: from smtp1.wa.amnet.net.au ([127.0.0.1])
	by localhost (smtp1.wa.amnet.net.au [127.0.0.1]) (amavisd-new,
	port 10024)
	with ESMTP id SlmvGpdSq0Hy; Sun, 29 Jul 2012 01:11:57 +0800 (WST)
Received: from [192.168.167.135] (203.161.71.159.static.amnet.net.au
	[203.161.71.159])
	by smtp1.wa.amnet.net.au (Postfix) with ESMTP id 21BA77DF05;
	Sun, 29 Jul 2012 01:11:57 +0800 (WST)
Message-ID: <50141D5A.2090302@oneit.com.au>
Date: Sun, 29 Jul 2012 01:11:54 +0800
From: David Barton <dave@oneit.com.au>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:14.0) Gecko/20120714 Thunderbird/14.0
MIME-Version: 1.0
To: Pavel Stehule <pavel.stehule@gmail.com>
CC: pgsql-performance@postgresql.org
Subject: Re: Postgres 9.1.4 - high stats collector IO usage
References: <50136B8D.7070502@oneit.com.au>
	<CAFj8pRB=Tq-xYeMHSE=52Oqkpx=miazcqEgwHa+m3cFxTo7i2g@mail.gmail.com>
In-Reply-To: 
 <CAFj8pRB=Tq-xYeMHSE=52Oqkpx=miazcqEgwHa+m3cFxTo7i2g@mail.gmail.com>
Content-Type: multipart/alternative;
	boundary="------------030404060600010208080007"
X-Pg-Spam-Score: -0.4 (/)
X-Archive-Number: 201207/351
X-Sequence-Number: 47532

This is a multi-part message in MIME format.
--------------030404060600010208080007
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

Thanks, Pavel.

I was a bit worried about trying this because of a comment elsewhere 
that the file was supposed to be permanent.  Tom's solution of 
increasing the vacuum delay has solved it for now.

Regards, David


On 28/07/12 15:07, Pavel Stehule wrote:
> Hello
>
> I had same problem with large numbers of tables - you can move
> pg_stat_tmp to tmpfs filesystem - it was solution for us
>
> Regards
>
> Pavel
>
> 2012/7/28 David Barton <dave@oneit.com.au>:
>> Hi,
>>
>> <snip>

-- 

*David Barton - Managing Director*
1iT Pty Ltd "The Power of One"

Tel: (08) 9382 2296
Direct: (08) 9200 4269
Mob: 0404 863 671
Fax: (08) 6210 1354
Web: www.1it.com.au

First Floor
41 Oxford Close
West Leederville, 6007


--------------030404060600010208080007
Content-Type: multipart/related;
	boundary="------------000605090601020903000403"


--------------000605090601020903000403
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

<html>
  <head>
    <meta content=3D"text/html; charset=3DUTF-8" http-equiv=3D"Content-Ty=
pe">
  </head>
  <body bgcolor=3D"#FFFFFF" text=3D"#000000">
    Thanks, Pavel.<br>
    <br>
    I was a bit worried about trying this because of a comment elsewhere
    that the file was supposed to be permanent.=C2=A0 Tom's solution of
    increasing the vacuum delay has solved it for now.<br>
    <br>
    Regards, David<br>
    <br>
    <br>
    <div class=3D"moz-cite-prefix">On 28/07/12 15:07, Pavel Stehule wrote=
:<br>
    </div>
    <blockquote
cite=3D"mid:CAFj8pRB=3DTq-xYeMHSE=3D52Oqkpx=3DmiazcqEgwHa+m3cFxTo7i2g@mai=
l.gmail.com"
      type=3D"cite">
      <pre wrap=3D"">Hello

I had same problem with large numbers of tables - you can move
pg_stat_tmp to tmpfs filesystem - it was solution for us

Regards

Pavel

2012/7/28 David Barton <a class=3D"moz-txt-link-rfc2396E" href=3D"mailto:=
dave@oneit.com.au">&lt;dave@oneit.com.au&gt;</a>:
</pre>
      <blockquote type=3D"cite">
        <pre wrap=3D"">Hi,

&lt;snip&gt;
</pre>
      </blockquote>
      <pre wrap=3D"">
</pre>
    </blockquote>
    <br>
    <div class=3D"moz-signature">-- <br>
      <p>
        <img src=3D"cid:part1.06010101.05080600@oneit.com.au"
          align=3D"right">
      </p>
      <p> <b>David Barton - Managing Director</b><br>
        1iT Pty Ltd "The Power of One"
      </p>
      <p>
        Tel: (08) 9382 2296<br>
        Direct: (08) 9200 4269<br>
        Mob: 0404 863 671<br>
        Fax: (08) 6210 1354<br>
        Web: <a href=3D"www.1it.com.au">www.1it.com.au</a>
      </p>
      <p>
        First Floor<br>
        41 Oxford Close<br>
        West Leederville, 6007
      </p>
    </div>
  </body>
</html>

--------------000605090601020903000403
Content-Type: image/gif;
 name="Logo.gif"
Content-Transfer-Encoding: base64
Content-ID: <part1.06010101.05080600@oneit.com.au>
Content-Disposition: inline;
 filename="Logo.gif"

R0lGODlhyABgAOf/AAIAWgMBUwACWAUCTQAGSAAFTgMAYQACXwcDSAMIOQIIPgAFVAAIRAQK
MAkEQwACaQoFPgABcQUKNQgAZAQNLAsHOgABeggAagAAgwwLLA0LMQwAcw0CZgwOKQIIZwUA
jQAFjBIAfAgHeAAGlw8AmQsMbBMURBAXQRQYMhEVSxMPbwoVcxQWUxUUWwwLpQ8QhRcRchoe
NBMYaBAWfBQOnxsWbxcSkhgUmx0hTR4adhcVqg8bpiImPR8geCMmUiQnSyAfiiQdlCcrQiMl
cBwdsSMoZiYkfSQofC4vQSoscSUnqycopCglsiguiC0smTMvgzg5Vy81hzk7TTQruTc0gTY6
cjo6aTQvtzc1nj9CTzg1rTQ3u0A8i0RGYT89nztBmz5CkEVEdEM5uUZKXUVEjUJJgkBDskFB
vktPXExKwk1Rm1JVfEtRrFdaaVRWh1FQwFJZj1lWm1VamlhVyFVgllZcs2Jid1ZfrGRmclxh
pFpeylxfwWNioWVomGhnp/9BAGpvr2Vszv9DEWlwwHR4g3V1lnFzrW5yy215qv9LFf5SFHR5
z/9UIICCjHeAs/9VKHd+wHV+yYB+tXqEr32Fqf9cLoWFs/9iLP9jNImNlomMp4GI1oSMwf9p
NY2Mu4WQvP9qPYaO0v9sR5KWq4uXuZGUvJSUw5KatI6Xz/92TJiapJecnpqWwJCX2P96V5Wg
zaCcxpejxpmh1v+CZ6Gkup+jy52mwaWmrf+IY/+IbamozP6Pef+UcK6yta+v0v+YeLGzwLC0
yv+dh/+egrW02Li00rK307a6vf+llf6pirzAxMDCv7zC0b7B3sLGyf+zm/+0qMfI38bMzsTL
2svK0cvNyv68rs/P5s7T1tDS3NPV0tXV7P/Ku9nW6Nbb3trc2dfb69zb4v3Qyd3h8eHj3//Y
0ODl5+Lk7ubk6P/c0eXn5Ojq5+jt7+vr9v/m3O/s8ezv6/Dy7/D1+P/w6/Xz9/P18v7x8fb4
9Pz39fr8+f/6+f36//j9///9+/7//P///yH+FUNyZWF0ZWQgd2l0aCBUaGUgR0lNUAAh+QQB
CgD/ACwAAAAAyABgAAAI/gD9CRxIsKDBgwgTKlzIsKHDhxAjSpxIsaLFixgzamzIjhktYOHu
5dtIsqTJkygd7ss2ycsLFUmAvbuXsqbNmzgfstMVSg8TEhZkKLNHM6fRowzd8cJ0qdOweQrT
SU3nzuY+f8Fk9bxCIsQDN+HgIR1LdiA+V3/+CPqT6JfCToriDrspD9GmRW+YfLDwoIi0d1fL
Csapbx6jtGv/gErI73BaXnTVpEmzhcbeC0mckSs6uHPKs4nTuk1YSa2g0Sn58eNzg4gLEHy/
ajMXmKE+z7gjzhuWKpWrZ1BJp/0DOWU/gcFmfMCw4cIEGbe+wRupUF85ZK5S/eJ2O7d3xveC
/vNLqM/xH9QO5U2rZQudPOoK+eU71eOB/SOqsKGjV/vgPm65pALKI4IwwktwuPX3nUD7jLfQ
eIet9Ut3De3jCyBOPFAFNew0hE881FBSiCYgkSOWQv2IAw0yu7iCCSMS4hNRO9EQY2M07WzU
jY3EdKOgROM0w2M3GVHIGIyPOdgQM69EogUNHKyx30IN+kNPPOuoo8469vxYkD3QhCnMLKI8
kogginBTYTSecAGDB3ByUEINTxgSDT0NxXHEE7UIRI8vcfRQA5xz9uBHNEo21E4xauRQQgke
QFpCD2rUMk6iDuECyqZzFZSOgJ2AkohafzCyKSidJJPQPvI4coge/kp8cEERynTIED/H4YPP
PeNhWhA/5YQJzZipVKKIhA+ew0cOBzxggAcr1MDsBAcYsIIa1/haEBUGTGDKPs2A4cEEE3hQ
w6ATGGCACpLkyBA9ulDhgQEHRHpuCQeky8ERrLj7UGlp4WIQN6OGNtxwghSHkDxwaMEEDRjM
Sg06XiKkWpXHrWpNmMgIk0uZiZyZSnXEHGHAAwccYYguxUQTjS+WPMGBujXAUrE//ERBriee
rPBsFKa0/LIlR8zrAR94KiSPHyVMwMEKcdRijMvE6KJGDd16oAY4EF0ynMAEAYsqqmutpUio
oXbq3z6URPBBCBEAsEY15MC3UD/H9aNt/kH7QLNLxy2WKciZrihkStMGHFHLPhmrxk+DxoCh
rgeG3MztBE+UYAAXzQj0+FX80GPIzAZwotA7cWRtyDkj3darPu2w4rMBOXDtECZfJ5RPY8P9
Mt4++uwtUD/vhLHAAQFYsUw48tycUMbxcePKLLO4IgomIbOlKkK6rEDu1oni7U8/++TTDiD0
euDJ3jpXy4Ec9GCqN6txzGyEvwUtPe8KtfCHs4MZu0o3olCtKODvebhbC9gsBrDzPEQ17LhF
JlRBjW/M5CTks4f1RHE9M63lEulACD2MQC8wnAN6BzkOPfjggQOogEgHocIEnEWGduAKIfoA
h9GMgRBfeM8D/jY7zlVQaJZokJADknDeQLwWML4VJELEUSLfQvcOdhBFihXhBzcw8QgPpiUR
z7CYKU5WAxg25BxNOAAH/ICp8RBwAitoBhHDdhsSGoAVB+GHDA0gB3kwxh8Nuo0n1HWEcdwK
d01EyFXMozCVqIZXwstIg/6DicENjhHPMBJB2tE+QMxxVbVwVg3OURC9XY4MSVuI5B5QOYMU
gwPlMkU3tkHLWtpyG+CoJTF6oC5dSNFrCowPFNHjECFGUiPH4Yc9xJGMYTyjKgjpxzm8V4Ji
RGQaOXBWNA5CQAPwoSH9yIO65KAgfsBiXAY4lzrXqc5onWsFKsiXAQDREET+YYEG/tEbIyny
yZMEskGqQVE4aLbNh+xjHEeoVp8MIsMJGMIhfFCXGg5iiW4Z4FEYzWgJVIBRjs5pBSWAgRwa
Asx7LsQ8xFxQQTTpn4EeIAfXgMhBm6CuhQ5kPFQ4wAFMAU5xGkAN/blKRR/whGYY9ahIbUY0
jhqNYhh1qTG9GxNNeiRSNVKlYWsIP9qxggd4gIcQuUYNHsCBqA7kODJ8AE8bwgeyxsEg/GDF
vHLwoJvyQx+7YylCShNMhTQwpVgt5j7oAQZn+QEi/ShFtXpAyrDpMV9rZUhED6AGI+ktGpDy
gFkJMsSADmQfWPTcVPH5K3/sM7AU4ccnqrWCzS6kHU8g/qsfFIRWckV2IXyYYRx8tQ8wpIsP
N8Obg/IRDTno4oDC6WsKTUsqwKKWIXiThxFmBoZUrsofeagWDAwJV24Z4LaMye4B4lBOf/hC
cx7QBYoYBA4jOEsSWh3tqnj3mOdSZB/RGKsBwOBag7RDnHDURT915i2I6jafqrHE/kwRyX0Y
owfzekJjGcJXqiZkmPZN7YPTZQRPdKON3ajFE/b3iZvp7LsOAXBlSxm8dkT0otiyrkBoZAgV
EDJbWk2ghfOI0gxThB7RiEILH2CEOFgCFr7QBSf4IDN19UAXMkqId8GbkIhOYLdwPQ47DKE5
AKgADIaohS98YQpDkMHGJczG/g1JmjuFQJEXofUxIAWC13Y4ogbpIusD0qWuaqkAEOe4mRvT
ReU85oFa5FXkbYrBhRbObM/qohaHYWFDg+qYtL86rZwhEtDB1sIPVDjCCuBphCjIwRTneBzO
oskPS/CBD9ZsSC1ejUf/2PUaklDDEWqgAhio4AhgJsbu+pkQUDBCEQZaiLEZwQi1PVce7GCH
H/1ht7rirB3h6Ia2z+Gu7hC7H3g1KLX/dzd9dOeg4Eh3N7i9aonwYx7uiDeCEBJveT+XH+ew
RR/KUIUwaCIb9ODVA8ENPAaBNjUBJfZNGTQecAdvfGueiOP0alfHHXMw/MgGKRyRBy8AYQYB
wMEt/qwo42iu+uLI5OfChzgR0OoDbwof3/jEh1p8MMMWrwjFIN6wBBtYQAAmGPkFZbrpjKC8
6OCIxSu0gpcrQOwCBfiBZvxX9KovaOOoaMUiApEGJdAABBEQQAUa4Y111MPqaMfNOcoAiEhs
/Q1bcA0GImAAAkDhG+R4R8nTzvecZCMJXnjDG86whSnQYARzN0DUpSEdzvT98TiZRguAoAQm
TGEHJCDB3FGGAKl/wxxUh7zoU+INBlzgAyRwAQneZoEIpAsBUKjGN9Rx9tHb3iSq8UEARICB
3lvAAhfYswAYYAdphAMd9Yjz7ZePIn3sIxMKCMAFIhCBCzjHAAAYAA56/iF7dtSe+eDP4j3e
MQYFDAAA6qJX9hXQiGpMbB33UH7458+ge+BDG2NIAAMKMIABFIAAJkAIzqAN2rAOe0d/CKgQ
DSIP6JAJPCABCaAAJ9AFFFQN4aAOM3F0CYiAL4cP8PAO60ANvdALA+h+34AO8EAPMbeBLNgr
V6IO5PAN2vAN4eAN5LAO8BBlLLiD62Ul9GAP8IAOW2IPXNI8PHiECugPvEIP9SAP9iAP8qCB
SLiBgWFuDUduU5iFBgFQnEVzWvh48hAO2TAN2WAOqvYQK/iFaMcOwUAJZVAELAABOGAHwDBY
oaeGLZgNsUAJk5AHZHAEMlAACpABbeAN8RBw/tWGh+HHDzcXC7HwCYbABk0AAyUQAArQAGOA
DeygdweoiKOnD9NACragdJwQCWzgBEEQfAOQAB2ABltyiN/nict3DpRACkr3CTr3BlpgA14R
ABCgAR2QCeZgDuxAD50oi3wXC3TwCZyACqUYCGagBTsAAhugeBWQAUKAd+sQD/WQiMjId/Kw
BnAACJAQCYMQCIP3MHthAAOgAMB4C8cHevTgjd9oddNQBWSgBndQB3vwBmZwBTowAusoAAQg
ARpACNoQDiYyj/UIecFQBFHQBF5gBtF4eTqwHNVHkNeIBwRoDuhwiA2JE/jGDLEwCXAAB31A
Iu+QRf5ACykgA07g/gRLUHmYp3mJFwAEcI1tIA3UQA7qcIj0GJJGdw7GQAqIYAhwQAZUIAMt
wAJWMAroMBHHAQwvuQIvYAM3QAMugHiJl30MkAANgAfVQIAomHxCeRI2RwqfsJacYAh3AAZO
kAMC0I5QwAzypxre8AMIIAAq0HsYsBwbQH3YNwAMIAEZkAnul5DeF5RnmVq2sJavEJmooHNs
gAUfpwILwAAacAtSKFxdIAELcDIR0HqtNwEAAAABgADuiALH4Ayyhw7bKH+NqRD4EAuI8Amv
UAuyIAuTuQh1YAZB8AIiwAGWmAG3IA+OtxDOdwsZkAAIEADoVy3YN5cEAAEJQIjLQA3U/qAN
sBkPjDmbEGEMdOAInLB0SzeZkbAHZqAEN/ACGwAACCABPEAN5eMQ40F+GSABCoAAAxAAAdCf
CEAAhZkBSNALyrAMskcO9mCW4GkR+8AOYZAHhtAKqLCbrdAKm/AqZ+B16+iLhPgO7+EQ+YAP
2CAEFNAACqAADCCg1ZkA14kCmZCd2vl58CASDXoRpEAFakCOF3qhofB2Z2B4lwEAmokCvWAP
d2gx5RMP2CAFHXCiEJgAEjClFCAEq7AMBzqWsBlwsnmjA4EOZRAFX1AHg7AJmxAKZvoqlMEE
OqB5F6B4rNgG8JCCjJlMu7IO3kAISNABGdAAftoBMdAGBiqj/jNoDgbopRUBii1wBFjABntw
CIuwCIcQCHowB2dwBURweK3HjhVQpeFgDikIXcqED+uADtKQCXiABlnQBo3QC1iKpWOpjcj5
nYiaR/pACy3QA05AkXOgB76qB4O3BWw6AohHd5nZADGwDFuanHlkf0EYg+FAgNSQnTK6nQqJ
Dk8ohbV6U5pQACsABEugBWeQBnOQBmcgBoW3AzSwehaQLr74jngHD1jEKkCIDjHoDdWQr+43
ljNIDt2JDxS3rfFRCAhQAjMQBEuwBQqrsFfApqoHAhgAfOsnAR2QH2XHkLciD/XwDjCIDjSI
DTSoDeTgr9sYhQJLEZTAAB4gAjZA/gM7wASWRwQOu3oR63peCYyrMIPqIK8O4XzjZw/qYK8e
66/osKW7ErAnqxCqoAAHMJofMAJaqQOqt64QGwKbCp8KIAFGup2gyqyrkg9QGA9EuA5kyyXw
EA/yoINJy2nAIAED0LQWgHqZl3nLgQFw86YGQJDXyQMIqh8GqHwjeg/ycIjvgA/00Dw0oa1r
izfhgAIMsAAPMJp++ZcR+3uul7cFAAEUOwbUkK+G6rVwxVnjcxv7cA8icXBr+2N20AAEEAAH
MH0WYLWBCXx4m30OMIjBmJ0gC6JSBD2JQrppmLq2BkjA0AESQAADYAAcYH3UN33p958EkLUd
kAWuGqtm/odYWSW8GXEP8EAI+ckAe4l+6XeaADCXCKC5GRADV5qd3Gl2Xeo52nsRj/MO35AF
xqsABFAA/gmd/tl/0XudHUAIvXAM7IsO7AC64BS/GGG67yANe9oA+gkBBBCgAVqdWUsBHYAH
BHwMyiB76mAPZ6jASAFuG2sO1IAGfArBEZiiLvqnMUAIx+CqykAN4fB5aivCSMEP9xAP70AO
2pAJe0oBJ+qnDYDBKIAGqxDDy3AM21mAaKu4OCxJ/IAl6KAN2KAMmdAGSMADMRADSJAFeHCl
ruqq7BsO23jDUWwU+nAP9FCq39C5S7wMqzCCrqrEBDzD1QCb9oAPUJzGGHEVmPQADzCIDZ17
oEtMwHWMpdo5gwY8bX5MFoBsD0X7DYTsDM6wDHGMyQhaQSO7oPnwvo9sdB7oxtrgfp2rnagc
rUULD8gZyp1hf6N8fDKIDQTYrzdYsvgAyq58EaClGrpSD2VbtMJMsk+Ixrs8FuDmy/SAD0BI
tnPKzPggD8HTx8e8EcL1P/YHy00IQMFbzTghRBAXUEiLWgEBADs=
--------------000605090601020903000403--

--------------030404060600010208080007--



From mark.kirkwood@catalyst.net.nz Tue Jul  31 01:21:34 2012
Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229])
	by mail.postgresql.org (Postfix) with ESMTP id 8C0DE2FE69B
	for <pgsql-performance@postgresql.org>;
	Mon, 30 Jul 2012 22:21:34 -0300 (ADT)
Received: from [2404:130:0:10::40:2] (helo=mail.catalyst.net.nz)
	by magus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <mark.kirkwood@catalyst.net.nz>) id 1Sw19P-0004EF-Ag
	for pgsql-performance@postgresql.org; Tue, 31 Jul 2012 01:21:33 +0000
Received: from localhost (localhost [127.0.0.1])
	by mail.catalyst.net.nz (Postfix) with ESMTP id 2D2C032D70;
	Tue, 31 Jul 2012 13:21:13 +1200 (NZST)
X-Virus-Scanned: Debian amavisd-new at catalyst.net.nz
Received: from mail.catalyst.net.nz ([127.0.0.1])
	by localhost (bertrand.catalyst.net.nz [127.0.0.1]) (amavisd-new,
	port 10024)
	with ESMTP id 4YBgAvFmofte; Tue, 31 Jul 2012 13:21:12 +1200 (NZST)
Received: from [IPv6:2404:130:0:1000:b419:dd7d:c30c:c6a3] (unknown
	[IPv6:2404:130:0:1000:b419:dd7d:c30c:c6a3])
	(Authenticated sender: mark.kirkwood)
	by mail.catalyst.net.nz (Postfix) with ESMTPSA id 1848A32EF6;
	Tue, 31 Jul 2012 13:21:05 +1200 (NZST)
Message-ID: <50173300.20800@catalyst.net.nz>
Date: Tue, 31 Jul 2012 13:21:04 +1200
From: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:14.0) Gecko/20120714 Thunderbird/14.0
MIME-Version: 1.0
To: David Barton <dave@oneit.com.au>
CC: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-performance@postgresql.org
Subject: Re: Postgres 9.1.4 - high stats collector IO usage
References: <50136B8D.7070502@oneit.com.au> <29996.1343492034@sss.pgh.pa.us>
	<50141C7A.4050607@oneit.com.au>
In-Reply-To: <50141C7A.4050607@oneit.com.au>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Host-Lookup-Failed: Reverse DNS lookup failed for 2404:130:0:10::40:2
	(failed)
X-Pg-Spam-Score: 0.3 (/)
X-Archive-Number: 201207/355
X-Sequence-Number: 47536

On 29/07/12 05:08, David Barton wrote:
> Thanks so much, Tom.
>
> That did the job.  I increased it to every 15 minutes and it has 
> dropped substantially even though the pgstat.stat file is over 1 MB 
> again.
>
>

I'd be a little concerned that having autovacuum tuned down to run so 
infrequently might result in massive table bloat - which could be even 
worse than than the original stats file problem.  Keep an eye out for 
any table(s) that used to be small but become unexpectedly larger over 
the next few days, and if you see any then decrease the naptime again.

Regards

Mark






From magnus@hagander.net Mon Aug  6 14:10:40 2012
Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229])
	by mail.postgresql.org (Postfix) with ESMTP id D03D0182A958
	for <pgsql-performance@postgresql.org>;
	Mon,  6 Aug 2012 11:10:40 -0300 (ADT)
Received: from mail-lb0-f174.google.com ([209.85.217.174])
	by magus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <magnus@hagander.net>) id 1SyO0v-00028t-Lq
	for pgsql-performance@postgresql.org; Mon, 06 Aug 2012 14:10:39 +0000
Received: by lbbgm6 with SMTP id gm6so2833676lbb.19
	for <pgsql-performance@postgresql.org>;
	Mon, 06 Aug 2012 07:10:20 -0700 (PDT)
X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
	d=google.com; s=20120113;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:x-gm-message-state;
	bh=pRUkvNZPA37NTVjwPpOi5StLXnYFIGs/vZvUOO1zla8=;
	b=E2KekbXGeEVqCKeNH6+aj14gWkZz/DLYwA0G1hwrzit9XzbdTIVmJy3Gp8GdIzPCJn
	UF4fvjQZPbgIaS88bSUqBjI6Emdl3t5HBRxR5ry3mvE7wLwzWFXDkqJIIlvyevc38m55
	IJ94cskzNRgItoMQn6OQAiGHcfOZu9FkYGzEovUFZzQVM8Ir3LEc1cfCL/FC4fTIOvfr
	uq9wcvNsA5TaEjcZOGcy91eDZLqCSCXVesSZenGBasVhIIeaKtvUmvtCBBPDQJSmgAGt
	ST63Av/+pmHYEv8g85AeGC0tOb55hbKCR0LljkdIt9uZ+B4y+1q7nL5gKOWx4fYgV3mr
	p/mg==
MIME-Version: 1.0
Received: by 10.152.109.212 with SMTP id hu20mr10934557lab.3.1344262220412;
	Mon, 06 Aug 2012 07:10:20 -0700 (PDT)
Received: by 10.152.128.7 with HTTP; Mon, 6 Aug 2012 07:10:20 -0700 (PDT)
In-Reply-To: 
 <CAFj8pRB=Tq-xYeMHSE=52Oqkpx=miazcqEgwHa+m3cFxTo7i2g@mail.gmail.com>
References: <50136B8D.7070502@oneit.com.au>
	<CAFj8pRB=Tq-xYeMHSE=52Oqkpx=miazcqEgwHa+m3cFxTo7i2g@mail.gmail.com>
Date: Mon, 6 Aug 2012 16:10:20 +0200
Message-ID: 
 <CABUevEy1nw2SqOR_1vEPgaz38wSk3gZEeSQREX=uq8Uxuv5n+g@mail.gmail.com>
Subject: Re: Postgres 9.1.4 - high stats collector IO usage
From: Magnus Hagander <magnus@hagander.net>
To: Pavel Stehule <pavel.stehule@gmail.com>
Cc: David Barton <dave@oneit.com.au>, pgsql-performance@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
X-Gm-Message-State: 
 ALoCoQnlapyQze8pVvGamyUGvmoJ7LbtU7jtGN92VAp8ScbIJSB3W4/pTOjza0UuzDzgsLgyBdNN
X-Pg-Spam-Score: -2.6 (--)
X-Archive-Number: 201208/20
X-Sequence-Number: 47558

That's not a good way of doing it, since you loose persistent storage.

Instead, you should set the stats_temp_dir paramter to a filesystem
somewhere else that is tmpfs. Then PostgreSQL will automatically move
the file to and from the main data directory on startup and shutdown,
so you get both the performance of tmpfs and the persistent
statistics.

//Magnus

On Sat, Jul 28, 2012 at 9:07 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> Hello
>
> I had same problem with large numbers of tables - you can move
> pg_stat_tmp to tmpfs filesystem - it was solution for us
>
> Regards
>
> Pavel
>
> 2012/7/28 David Barton <dave@oneit.com.au>:
>> Hi,
>>
>> I am running postgres 9.1.4 on Ubuntu 12.04 and the stats collector is
>> generating very high IO usage even when nothing appears to be happening on
>> the system.
>>
>> I have roughly 150 different databases, each of which is running in 1 of
>> roughly 30 tablespaces.  The databases are small (the dump of most is are
>> under 100M, and all but 3 are under 1G, nothing larger than 2G).
>>
>> Previously iotop reported the disk write speed, at ~6MB / second.  I went
>> and reset the stats for every database and that shrunk the stats file and
>> brought the IO it down to 1MB / second.  I still think this is too high for
>> an idle database.  I've now noticed it is growing.
>>
>> ls -l /var/lib/postgresql/9.1/main/pg_stat_tmp/pgstat.stat
>> -rw------- 1 postgres postgres 3515080 Jul 28 11:58
>> /var/lib/postgresql/9.1/main/pg_stat_tmp/pgstat.stat
>>
>> <reset of stats>
>>
>> ls -l /var/lib/postgresql/9.1/main/pg_stat_tmp/pgstat.stat
>> -rw------- 1 postgres postgres 514761 Jul 28 12:11
>> /var/lib/postgresql/9.1/main/pg_stat_tmp/pgstat.stat
>>
>> <watch the file grow>
>>
>> ls -l /var/lib/postgresql/9.1/main/pg_stat_tmp/pgstat.stat
>> -rw------- 1 postgres postgres 776711 Jul 28 12:25
>> /var/lib/postgresql/9.1/main/pg_stat_tmp/pgstat.stat
>>
>> In the 15 minutes since the reset, IO has nearly doubled to 1.6+ MB /
>> second.
>>
>> FWIW, I just migrated all these databases over to this new server by
>> restoring from pg_dump  I was previously experiencing this on 8.3, which was
>> why I upgraded to 9.1 and I also have another server with similar problems
>> on 9.1.
>>
>> Any help would be sincerely appreciated.
>>
>>
>> David Barton dave@oneit.com.au
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance



-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/



From pavel.stehule@gmail.com Mon Aug  6 14:17:12 2012
Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229])
	by mail.postgresql.org (Postfix) with ESMTP id 877F3182A958
	for <pgsql-performance@postgresql.org>;
	Mon,  6 Aug 2012 11:17:12 -0300 (ADT)
Received: from mail-ee0-f46.google.com ([74.125.83.46])
	by magus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <pavel.stehule@gmail.com>) id 1SyO7F-0002Fn-Nr
	for pgsql-performance@postgresql.org; Mon, 06 Aug 2012 14:17:11 +0000
Received: by eeil10 with SMTP id l10so850467eei.19
	for <pgsql-performance@postgresql.org>;
	Mon, 06 Aug 2012 07:16:52 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113;
	h=mime-version:in-reply-to:references:from:date:message-id:subject:to
	:cc:content-type;
	bh=U0vfIhHiUKM3uen77FtEKR6t9nZlntkc6rheKpYxoyY=;
	b=P/ybyUx5Ok/WEjFOiKlEHb5uU2tl67uQnmiIAsvvCxFJST6FHgPWNtVpLFqcgJYR6V
	6J0eAG7lSQAbfCZxzHcAO/bFpKSQNAMOaIU/G4KviUMKK4yweU5idNeQroIwaroNETxz
	uUp+uorOSKh2BilfzsM3eMwoG3eQcxFeqbGsS1EWmGfMGvxRldbcygPdbTCbeVyIkB9x
	vi4L/ygJVe6R5Q+Lv8XsAMUB2CQLKkgA4TmbiaTfS4uX4eIlNXp8e6+fbuWnBZ0m2UlR
	VFblClrbLSH8VtSRBg8P+5fEHqxBFnWz8K9mAKgerWt+6e3+0CwP1zyGEwhB64dtKoep
	NILA==
Received: by 10.14.194.198 with SMTP id m46mr13140746een.13.1344262612902;
	Mon, 06 Aug 2012 07:16:52 -0700 (PDT)
MIME-Version: 1.0
Received: by 10.14.177.193 with HTTP; Mon, 6 Aug 2012 07:16:12 -0700 (PDT)
In-Reply-To: 
 <CABUevEy1nw2SqOR_1vEPgaz38wSk3gZEeSQREX=uq8Uxuv5n+g@mail.gmail.com>
References: <50136B8D.7070502@oneit.com.au>
	<CAFj8pRB=Tq-xYeMHSE=52Oqkpx=miazcqEgwHa+m3cFxTo7i2g@mail.gmail.com>
	<CABUevEy1nw2SqOR_1vEPgaz38wSk3gZEeSQREX=uq8Uxuv5n+g@mail.gmail.com>
From: Pavel Stehule <pavel.stehule@gmail.com>
Date: Mon, 6 Aug 2012 16:16:12 +0200
Message-ID: 
 <CAFj8pRBEKi7UnnDvpAzfyo3veL4xizCAHd=F=iAPYgNMqaCspA@mail.gmail.com>
Subject: Re: Postgres 9.1.4 - high stats collector IO usage
To: Magnus Hagander <magnus@hagander.net>
Cc: David Barton <dave@oneit.com.au>, pgsql-performance@postgresql.org
Content-Type: text/plain; charset=UTF-8
X-Pg-Spam-Score: -2.7 (--)
X-Archive-Number: 201208/21
X-Sequence-Number: 47559

2012/8/6 Magnus Hagander <magnus@hagander.net>:
> That's not a good way of doing it, since you loose persistent storage.
>
> Instead, you should set the stats_temp_dir paramter to a filesystem
> somewhere else that is tmpfs. Then PostgreSQL will automatically move
> the file to and from the main data directory on startup and shutdown,
> so you get both the performance of tmpfs and the persistent
> statistics.

we had to do it because our read/write of  stat file created really
high IO - and it was problem on Amazon :( - probably we had not this
issue elsewhere

Regards

Pavel



>
> //Magnus
>
> On Sat, Jul 28, 2012 at 9:07 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
>> Hello
>>
>> I had same problem with large numbers of tables - you can move
>> pg_stat_tmp to tmpfs filesystem - it was solution for us
>>
>> Regards
>>
>> Pavel
>>
>> 2012/7/28 David Barton <dave@oneit.com.au>:
>>> Hi,
>>>
>>> I am running postgres 9.1.4 on Ubuntu 12.04 and the stats collector is
>>> generating very high IO usage even when nothing appears to be happening on
>>> the system.
>>>
>>> I have roughly 150 different databases, each of which is running in 1 of
>>> roughly 30 tablespaces.  The databases are small (the dump of most is are
>>> under 100M, and all but 3 are under 1G, nothing larger than 2G).
>>>
>>> Previously iotop reported the disk write speed, at ~6MB / second.  I went
>>> and reset the stats for every database and that shrunk the stats file and
>>> brought the IO it down to 1MB / second.  I still think this is too high for
>>> an idle database.  I've now noticed it is growing.
>>>
>>> ls -l /var/lib/postgresql/9.1/main/pg_stat_tmp/pgstat.stat
>>> -rw------- 1 postgres postgres 3515080 Jul 28 11:58
>>> /var/lib/postgresql/9.1/main/pg_stat_tmp/pgstat.stat
>>>
>>> <reset of stats>
>>>
>>> ls -l /var/lib/postgresql/9.1/main/pg_stat_tmp/pgstat.stat
>>> -rw------- 1 postgres postgres 514761 Jul 28 12:11
>>> /var/lib/postgresql/9.1/main/pg_stat_tmp/pgstat.stat
>>>
>>> <watch the file grow>
>>>
>>> ls -l /var/lib/postgresql/9.1/main/pg_stat_tmp/pgstat.stat
>>> -rw------- 1 postgres postgres 776711 Jul 28 12:25
>>> /var/lib/postgresql/9.1/main/pg_stat_tmp/pgstat.stat
>>>
>>> In the 15 minutes since the reset, IO has nearly doubled to 1.6+ MB /
>>> second.
>>>
>>> FWIW, I just migrated all these databases over to this new server by
>>> restoring from pg_dump  I was previously experiencing this on 8.3, which was
>>> why I upgraded to 9.1 and I also have another server with similar problems
>>> on 9.1.
>>>
>>> Any help would be sincerely appreciated.
>>>
>>>
>>> David Barton dave@oneit.com.au
>>
>> --
>> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-performance
>
>
>
> --
>  Magnus Hagander
>  Me: http://www.hagander.net/
>  Work: http://www.redpill-linpro.com/



From magnus@hagander.net Mon Aug  6 14:19:04 2012
Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125])
	by mail.postgresql.org (Postfix) with ESMTP id 3963F182A958
	for <pgsql-performance@postgresql.org>;
	Mon,  6 Aug 2012 11:19:04 -0300 (ADT)
Received: from mail-lpp01m010-f46.google.com ([209.85.215.46])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <magnus@hagander.net>) id 1SyO99-0008BN-9c
	for pgsql-performance@postgresql.org; Mon, 06 Aug 2012 14:19:03 +0000
Received: by lagy9 with SMTP id y9so760257lag.19
	for <pgsql-performance@postgresql.org>;
	Mon, 06 Aug 2012 07:18:49 -0700 (PDT)
X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
	d=google.com; s=20120113;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:x-gm-message-state;
	bh=/8sR5wk5DcSwAB4dkkFJE3YlyBLbAwRiTJXzDW+FrZQ=;
	b=IlPP3U4/TImoAbYL1ADuFslM2j9cGZA/fZo+1mhgt2q4xCWs0AXFqFBE58upxbP2qX
	G7xq9a2ibtIXHVtamlZ1AM5ONMEqrvlpDVSAwxJ/N5eEx4IUecu1fDdsZwgLVwMDFTeY
	ITcg9EJDynz6nA/rrzU0dhQeHeSDJi8W1XhxVkiNqVoAwk6/TwgjOdl2Q3lcLXAh6PLU
	mTQzdwvpoD20MjPMcVL0gGgt/61aYW00U9t+RtucRfhnKRK60xLrjzY0te20Tlg8Uhe4
	UpSZMQnRtyWjnczG3/rRy3uPwXxapCUkU1TbXDsfJLWlxDj9p2Zn6oQcyacJu/vhp3kM
	VauA==
MIME-Version: 1.0
Received: by 10.112.30.41 with SMTP id p9mr4834636lbh.26.1344262729485; Mon,
	06 Aug 2012 07:18:49 -0700 (PDT)
Received: by 10.152.128.7 with HTTP; Mon, 6 Aug 2012 07:18:49 -0700 (PDT)
In-Reply-To: 
 <CAFj8pRBEKi7UnnDvpAzfyo3veL4xizCAHd=F=iAPYgNMqaCspA@mail.gmail.com>
References: <50136B8D.7070502@oneit.com.au>
	<CAFj8pRB=Tq-xYeMHSE=52Oqkpx=miazcqEgwHa+m3cFxTo7i2g@mail.gmail.com>
	<CABUevEy1nw2SqOR_1vEPgaz38wSk3gZEeSQREX=uq8Uxuv5n+g@mail.gmail.com>
	<CAFj8pRBEKi7UnnDvpAzfyo3veL4xizCAHd=F=iAPYgNMqaCspA@mail.gmail.com>
Date: Mon, 6 Aug 2012 16:18:49 +0200
Message-ID: 
 <CABUevEwA7gqn0OCahWx41T74-AEc8UG2EjLWkSxwQxmAqCn=Og@mail.gmail.com>
Subject: Re: Postgres 9.1.4 - high stats collector IO usage
From: Magnus Hagander <magnus@hagander.net>
To: Pavel Stehule <pavel.stehule@gmail.com>
Cc: David Barton <dave@oneit.com.au>, pgsql-performance@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
X-Gm-Message-State: 
 ALoCoQlqAItXvBHA2xG5vRa0EFqNuPBOBAILwYbS33/ivby4aBxMc6rj121kI3lOVb8Zl3SQNtbX
X-Pg-Spam-Score: -2.6 (--)
X-Archive-Number: 201208/22
X-Sequence-Number: 47560

On Mon, Aug 6, 2012 at 4:16 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> 2012/8/6 Magnus Hagander <magnus@hagander.net>:
>> That's not a good way of doing it, since you loose persistent storage.
>>
>> Instead, you should set the stats_temp_dir paramter to a filesystem
>> somewhere else that is tmpfs. Then PostgreSQL will automatically move
>> the file to and from the main data directory on startup and shutdown,
>> so you get both the performance of tmpfs and the persistent
>> statistics.
>
> we had to do it because our read/write of  stat file created really
> high IO - and it was problem on Amazon :( - probably we had not this
> issue elsewhere

Uh. You realize that if you set stats_temp_dir, it only ever writes to
the persistent storage once, when you do "pg_ctl stop" (or
equivalent). Are you saying the shutdown took too long?

I've had to change that param many times on Amazon, but I've never had
a problem with the shutdown writes. (And I've seen it often enough on
dedicated hardware as well..)

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/



From pavel.stehule@gmail.com Mon Aug  6 14:44:38 2012
Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229])
	by mail.postgresql.org (Postfix) with ESMTP id EFB1B182A958
	for <pgsql-performance@postgresql.org>;
	Mon,  6 Aug 2012 11:44:38 -0300 (ADT)
Received: from mail-ey0-f174.google.com ([209.85.215.174])
	by magus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <pavel.stehule@gmail.com>) id 1SyOXr-0002wJ-VG
	for pgsql-performance@postgresql.org; Mon, 06 Aug 2012 14:44:38 +0000
Received: by eaac11 with SMTP id c11so864056eaa.19
	for <pgsql-performance@postgresql.org>;
	Mon, 06 Aug 2012 07:44:22 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113;
	h=mime-version:in-reply-to:references:from:date:message-id:subject:to
	:cc:content-type;
	bh=Y1S8+a6J3NlMXjG6yv16CDwLOvFl/h/Ii3VXYgPs4f8=;
	b=VtEzCBg2twQ18nD1vARvtf/7pWZam9EUVPQ9kya+1xwUJ1d7vJBh4UohBzIxztcy2t
	SBxgYbh2eRY2F/wOjuvUV9Nlh4glwf5JEheUXb39QZ+1gSVziE9+Ac9sGY+t2ATcaa+1
	AUXn/dDlF1ZveQTHwMFy6uLYnWDEpywOPOz1NZx7AxHrIs07dsgcTFAKbmh+zTXYmPue
	QODEvlABpH/yWbOUmQ8SIRZla+LakuJ9SY0YEd2Lg9PM1ZZpZyN3OXZ3SRCYu7ZYwn4t
	yQCppmHPIQeQwsk0+9719+55q9gzYUuCOm8YQJqnJAQ9r8uapxz8uu2mx6hPGQ/gKV21
	kyEw==
Received: by 10.14.202.69 with SMTP id c45mr13414838eeo.4.1344264262853; Mon,
	06 Aug 2012 07:44:22 -0700 (PDT)
MIME-Version: 1.0
Received: by 10.14.177.193 with HTTP; Mon, 6 Aug 2012 07:43:42 -0700 (PDT)
In-Reply-To: 
 <CABUevEwA7gqn0OCahWx41T74-AEc8UG2EjLWkSxwQxmAqCn=Og@mail.gmail.com>
References: <50136B8D.7070502@oneit.com.au>
	<CAFj8pRB=Tq-xYeMHSE=52Oqkpx=miazcqEgwHa+m3cFxTo7i2g@mail.gmail.com>
	<CABUevEy1nw2SqOR_1vEPgaz38wSk3gZEeSQREX=uq8Uxuv5n+g@mail.gmail.com>
	<CAFj8pRBEKi7UnnDvpAzfyo3veL4xizCAHd=F=iAPYgNMqaCspA@mail.gmail.com>
	<CABUevEwA7gqn0OCahWx41T74-AEc8UG2EjLWkSxwQxmAqCn=Og@mail.gmail.com>
From: Pavel Stehule <pavel.stehule@gmail.com>
Date: Mon, 6 Aug 2012 16:43:42 +0200
Message-ID: 
 <CAFj8pRCRPv3i-9WwsSCa=my9M+J5B7K1i1c7TWi0pFLRyjkpWQ@mail.gmail.com>
Subject: Re: Postgres 9.1.4 - high stats collector IO usage
To: Magnus Hagander <magnus@hagander.net>
Cc: David Barton <dave@oneit.com.au>, pgsql-performance@postgresql.org
Content-Type: text/plain; charset=UTF-8
X-Pg-Spam-Score: -2.7 (--)
X-Archive-Number: 201208/23
X-Sequence-Number: 47561

2012/8/6 Magnus Hagander <magnus@hagander.net>:
> On Mon, Aug 6, 2012 at 4:16 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
>> 2012/8/6 Magnus Hagander <magnus@hagander.net>:
>>> That's not a good way of doing it, since you loose persistent storage.
>>>
>>> Instead, you should set the stats_temp_dir paramter to a filesystem
>>> somewhere else that is tmpfs. Then PostgreSQL will automatically move
>>> the file to and from the main data directory on startup and shutdown,
>>> so you get both the performance of tmpfs and the persistent
>>> statistics.
>>
>> we had to do it because our read/write of  stat file created really
>> high IO - and it was problem on Amazon :( - probably we had not this
>> issue elsewhere
>
> Uh. You realize that if you set stats_temp_dir, it only ever writes to
> the persistent storage once, when you do "pg_ctl stop" (or
> equivalent). Are you saying the shutdown took too long?
>
> I've had to change that param many times on Amazon, but I've never had
> a problem with the shutdown writes. (And I've seen it often enough on
> dedicated hardware as well..)
aha, this is my mistake - we use a stats_temp_directory GUC already -
my advice was use a tmpfs for statfile, but I though realize it via
stats_temp_directory, but I didn't say it.

Regards

Pavel

>
> --
>  Magnus Hagander
>  Me: http://www.hagander.net/
>  Work: http://www.redpill-linpro.com/



From jeff.janes@gmail.com Sun Aug  12 21:23:14 2012
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
	by malur.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <jeff.janes@gmail.com>) id 1T0fcw-0004A2-Ae
	for pgsql-performance@postgresql.org; Sun, 12 Aug 2012 21:23:14 +0000
Received: from mail-ob0-f174.google.com ([209.85.214.174])
	by magus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <jeff.janes@gmail.com>) id 1T0fcs-0005RA-PF
	for pgsql-performance@postgresql.org; Sun, 12 Aug 2012 21:23:13 +0000
Received: by obbuo13 with SMTP id uo13so5825559obb.19
	for <pgsql-performance@postgresql.org>;
	Sun, 12 Aug 2012 14:23:09 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type;
	bh=zO7fbErRJjkGcTNE2HcAmhMaG5vyMqIIb7t9OoKKQGw=;
	b=fYomgsbJllvoNGw+FVXNSukFtqpn2hr05t5tbkhz0bYLwE9jSBifOITrRmHelIUhCC
	q6vrFjJdfCfmz86fyTPmYx6xGDB5tM9VpbXxsEz3iscnqEz1/A9gG1EFXy3DDbJK3Vw1
	5XBMX4wIigqp8AplOXFpvlCQqrUpDUMrGrAh6VVPStbad+STgiFcVVZ3/kVAY6kgbIjT
	fU++WvGsV7I4xd83+deGgbfVZdxipTZunrNecUIovpGxrNjmHq7ZBNVXryvTCfk5oXkw
	334AhY3E5LfN3dQzRdREeQ5qhGAY74hphrCnV2mMarmTiSy+xryPWOyZ+7NJP6nM5Lqv
	ST4g==
MIME-Version: 1.0
Received: by 10.50.193.201 with SMTP id hq9mr3510674igc.48.1344806589226; Sun,
	12 Aug 2012 14:23:09 -0700 (PDT)
Received: by 10.42.95.69 with HTTP; Sun, 12 Aug 2012 14:23:09 -0700 (PDT)
In-Reply-To: <50136B8D.7070502@oneit.com.au>
References: <50136B8D.7070502@oneit.com.au>
Date: Sun, 12 Aug 2012 14:23:09 -0700
Message-ID: 
 <CAMkU=1z2mCOuue7XktPDHt9D2HWR4D+8FyvCKmv+YDfd0ZfBHQ@mail.gmail.com>
Subject: Re: Postgres 9.1.4 - high stats collector IO usage
From: Jeff Janes <jeff.janes@gmail.com>
To: David Barton <dave@oneit.com.au>
Cc: pgsql-performance@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
X-Pg-Spam-Score: -2.7 (--)
X-Archive-Number: 201208/69
X-Sequence-Number: 47607

On Fri, Jul 27, 2012 at 9:33 PM, David Barton <dave@oneit.com.au> wrote:
> Hi,
>
> I am running postgres 9.1.4 on Ubuntu 12.04 and the stats collector is
> generating very high IO usage even when nothing appears to be happening on
> the system.
>
> I have roughly 150 different databases, each of which is running in 1 of
> roughly 30 tablespaces.  The databases are small (the dump of most is are
> under 100M, and all but 3 are under 1G, nothing larger than 2G).

It isn't the size of the data that matters, but the number of objects.
 It sounds like your databases have about 150 statistics-containing
objects each, in order to come up with a 3.5MB stats file.

What do you gain by using databases rather than schema to do the segregation?

> Previously iotop reported the disk write speed, at ~6MB / second.

So that corresponds to about 2 physical write-outs of the stats file
per second.  Are you using ext4?  It has the peculiar (to me) property
that when a file is renamed out of existence, it writes out all of
that file's obsolete dirty buffers, rather than just dropping them as
uninteresting to anyone.  That generates about 10 times the physical
IO as the ext3 file system does.  And of course about infinite times
the physical IO as a tmpfs.

> FWIW, I just migrated all these databases over to this new server by
> restoring from pg_dump  I was previously experiencing this on 8.3, which was
> why I upgraded to 9.1 and I also have another server with similar problems
> on 9.1.
>
> Any help would be sincerely appreciated.

I think the first line of defense would be using /dev/shm to hold the
stats file.  I don't see any downside to that.  You are reading and
writing that file so ferociously anyway that it is always going to be
taking up RAM, no matter where you put it.  Indeed, under ext4 you
might use even have several copies of it all locked into RAM as they
wait to reach the disk before being dropped.

Increasing the naptime, as you have already done, will also decrease
the physical IO, but that has the trade-off of risking bloat.  (But
since you are running 150 databases on one machine, I doubt any of
them are active enough for the risk of bloat to be all that great).
However using /dev/shm should eliminate the IO entirely with no
trade-off at all.

But with /dev/shm the CPU usage of repeatedly formatting, writing,
reading, and parsing the stat file will still be considerable, while
increasing the naptime will reduce that as well.

As far as coding changes to overcome the fundamental problem:

A relatively easy change would be to make any given autovacuum worker
on start up tolerate a stats file that is out of date by up to, say,
naptime/5.  That would greatly reduce the amount of writing the stats
collector needs to do (assuming that few tables actually need
vacuuming during any given cycle), but wouldn't change the amount of
reading a worker needs to do because it still needs to read the file
each time as it doesn't inherit the stats from anyone.  I don't think
it would be a problem that a table which becomes eligible for
vacuuming in the last 20% of a cycle would have to wait for one more
round.  Especially as this change might motivate one to reduce the
naptime since doing so will be cheaper.

But it seems like maybe the stats collector could use a ground-up
recoding.  Maybe it could use a shared relation to store the stats
within the database cluster itself, so that edits could be done in
place per database rather than re-writing the entire cluster's stats?
 But I certainly am not volunteering to take on that task.

A compromise might be to have one stats file per database.  That way
any given backend only needs to read in the database file it cares
about, and the stat's collector only needs to write out the one
database asked of it.  This change could be mostly localized to just
pgstat.c, I think.

Cheers,

Jeff




From dave@oneit.com.au Mon Aug  13 02:17:42 2012
Received: from makus.postgresql.org ([98.129.198.125])
	by malur.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <dave@oneit.com.au>) id 1T0kDu-0000UM-76
	for pgsql-performance@postgresql.org; Mon, 13 Aug 2012 02:17:42 +0000
Received: from smtp4.wa.amnet.net.au ([203.161.124.53])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <dave@oneit.com.au>) id 1T0kDq-0004FC-Fh
	for pgsql-performance@postgresql.org; Mon, 13 Aug 2012 02:17:40 +0000
Received: from localhost (localhost.localdomain [127.0.0.1])
	by smtp4.wa.amnet.net.au (Postfix) with ESMTP id 144767DD19
	for <pgsql-performance@postgresql.org>;
	Mon, 13 Aug 2012 10:17:35 +0800 (WST)
X-Virus-Scanned: amavisd-new at smtp4.wa.amnet.net.au
Received: from smtp4.wa.amnet.net.au ([127.0.0.1])
	by localhost (smtp4.wa.amnet.net.au [127.0.0.1]) (amavisd-new,
	port 10024)
	with ESMTP id zrB6yMOLMvSO for <pgsql-performance@postgresql.org>;
	Mon, 13 Aug 2012 10:17:33 +0800 (WST)
Received: from [192.168.167.135] (203.161.71.159.static.amnet.net.au
	[203.161.71.159])
	by smtp4.wa.amnet.net.au (Postfix) with ESMTP id C415D7DCC7
	for <pgsql-performance@postgresql.org>;
	Mon, 13 Aug 2012 10:17:33 +0800 (WST)
Message-ID: <502863B8.4000203@oneit.com.au>
Date: Mon, 13 Aug 2012 10:17:28 +0800
From: David Barton <dave@oneit.com.au>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:14.0) Gecko/20120714 Thunderbird/14.0
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Postgres 9.1.4 - high stats collector IO usage
References: <50136B8D.7070502@oneit.com.au>
	<CAMkU=1z2mCOuue7XktPDHt9D2HWR4D+8FyvCKmv+YDfd0ZfBHQ@mail.gmail.com>
In-Reply-To: 
 <CAMkU=1z2mCOuue7XktPDHt9D2HWR4D+8FyvCKmv+YDfd0ZfBHQ@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Pg-Spam-Score: -1.9 (-)
X-Archive-Number: 201208/71
X-Sequence-Number: 47609

Hi Jeff,

Thanks for the detailed reply.

On 13/08/12 05:23, Jeff Janes wrote:
> On Fri, Jul 27, 2012 at 9:33 PM, David Barton <dave@oneit.com.au> wrote:
>> Hi,
>>
>> <snip>
>> I have roughly 150 different databases, each of which is running in 1 of
>> roughly 30 tablespaces.  The databases are small (the dump of most is are
>> under 100M, and all but 3 are under 1G, nothing larger than 2G).
> It isn't the size of the data that matters, but the number of objects.
>   It sounds like your databases have about 150 statistics-containing
> objects each, in order to come up with a 3.5MB stats file.
>
> What do you gain by using databases rather than schema to do the segregation?
I had never imagined that there was such a profound difference between 
using schemas and using databases.  I imagine that I could convert from 
using databases to using schemas.
>
>> Previously iotop reported the disk write speed, at ~6MB / second.
> So that corresponds to about 2 physical write-outs of the stats file
> per second.  Are you using ext4?  It has the peculiar (to me) property
> that when a file is renamed out of existence, it writes out all of
> that file's obsolete dirty buffers, rather than just dropping them as
> uninteresting to anyone.  That generates about 10 times the physical
> IO as the ext3 file system does.  And of course about infinite times
> the physical IO as a tmpfs.
It was previously on ext3 and moved to ext4.  That didn't seem to make a 
difference, I'm guessing that the higher IO on the new server was just 
that it was capable of doing it.
>
>> FWIW, I just migrated all these databases over to this new server by
>> restoring from pg_dump  I was previously experiencing this on 8.3, which was
>> why I upgraded to 9.1 and I also have another server with similar problems
>> on 9.1.
>>
>> Any help would be sincerely appreciated.
> I think the first line of defense would be using /dev/shm to hold the
> stats file.  I don't see any downside to that.  You are reading and
> writing that file so ferociously anyway that it is always going to be
> taking up RAM, no matter where you put it.  Indeed, under ext4 you
> might use even have several copies of it all locked into RAM as they
> wait to reach the disk before being dropped.
>
> Increasing the naptime, as you have already done, will also decrease
> the physical IO, but that has the trade-off of risking bloat.  (But
> since you are running 150 databases on one machine, I doubt any of
> them are active enough for the risk of bloat to be all that great).
> However using /dev/shm should eliminate the IO entirely with no
> trade-off at all.
>
> But with /dev/shm the CPU usage of repeatedly formatting, writing,
> reading, and parsing the stat file will still be considerable, while
> increasing the naptime will reduce that as well.
The CPU overhead seems pretty minimal, and a slight reduction in naptime 
should be more than enough.
>
> As far as coding changes to overcome the fundamental problem:
>
> A relatively easy change would be to make any given autovacuum worker
> on start up tolerate a stats file that is out of date by up to, say,
> naptime/5.  That would greatly reduce the amount of writing the stats
> collector needs to do (assuming that few tables actually need
> vacuuming during any given cycle), but wouldn't change the amount of
> reading a worker needs to do because it still needs to read the file
> each time as it doesn't inherit the stats from anyone.  I don't think
> it would be a problem that a table which becomes eligible for
> vacuuming in the last 20% of a cycle would have to wait for one more
> round.  Especially as this change might motivate one to reduce the
> naptime since doing so will be cheaper.
If the stats are mirrored in memory, then that makes sense.  Of course, 
if that's the case then couldn't we just alter the stats to flush at 
maximum once per N seconds / minutes?  If the stats are not mirrored in 
memory, doesn't that imply that most of the databases will never flush 
updates stats to disk and so the file will become stale?
>
> But it seems like maybe the stats collector could use a ground-up
> recoding.  Maybe it could use a shared relation to store the stats
> within the database cluster itself, so that edits could be done in
> place per database rather than re-writing the entire cluster's stats?
>   But I certainly am not volunteering to take on that task.
>
> A compromise might be to have one stats file per database.  That way
> any given backend only needs to read in the database file it cares
> about, and the stat's collector only needs to write out the one
> database asked of it.  This change could be mostly localized to just
> pgstat.c, I think.
That approach was what I had thought  I am not a C programmer by any 
stretch of the imagination, which is why I asked if there was a place to 
find this kind of thing.  It seems likely there are a few features that 
people would be willing to put money towards.

>
> Cheers,
>
> Jeff
>
>
Regards,
David




From jeff.janes@gmail.com Mon Aug  13 19:40:33 2012
Received: from magus.postgresql.org ([87.238.57.229])
	by malur.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <jeff.janes@gmail.com>) id 1T10V7-0006ML-C2
	for pgsql-performance@postgresql.org; Mon, 13 Aug 2012 19:40:33 +0000
Received: from mail-yx0-f174.google.com ([209.85.213.174])
	by magus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <jeff.janes@gmail.com>) id 1T10V3-0008R1-Qr
	for pgsql-performance@postgresql.org; Mon, 13 Aug 2012 19:40:33 +0000
Received: by yenl14 with SMTP id l14so2358587yen.19
	for <pgsql-performance@postgresql.org>;
	Mon, 13 Aug 2012 12:40:28 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type;
	bh=kcZhaf5pk8r+MLbS/MgT+gI0pWYNjIbamFe3CObATDQ=;
	b=RulKkGzko5d/8YqbJjcJ0DObMWY6KBw0nW3T6ie4Ozabka8/JFXZ2gb1mxPSyMcYWh
	zSPokgzDh4mk9E/qR3eDxUJO1Rh257UeGU+QMkmSYA3hRnemHmn8NLxFEPnFgPivrV24
	71HXW1Rg3qCtiDgbfj2RuUhjkiS9GzvAGyJIpimipzz/Q/fnY2ZD6Y5O83jLcHg2DWsE
	8KoIoYyiktMbnq9seBowJaieMZ4t+olIZxGmjzGj3aARaAln7TGRr+FDp2tTGFarZo5t
	scNlFNxLbCxNF7Jhf0mcJtyv+FToR/J+DZQmeVo+j4s2bnJl5rKxOeB9FkmTsqPga0L/
	0rWg==
MIME-Version: 1.0
Received: by 10.50.100.129 with SMTP id ey1mr6936800igb.35.1344886828169; Mon,
	13 Aug 2012 12:40:28 -0700 (PDT)
Received: by 10.43.132.68 with HTTP; Mon, 13 Aug 2012 12:40:28 -0700 (PDT)
In-Reply-To: <502863B8.4000203@oneit.com.au>
References: <50136B8D.7070502@oneit.com.au>
	<CAMkU=1z2mCOuue7XktPDHt9D2HWR4D+8FyvCKmv+YDfd0ZfBHQ@mail.gmail.com>
	<502863B8.4000203@oneit.com.au>
Date: Mon, 13 Aug 2012 12:40:28 -0700
Message-ID: 
 <CAMkU=1wRP1qAkYsJyLd0CZSjCDeaRurQtPbh00snb=yAb=TYGw@mail.gmail.com>
Subject: Re: Postgres 9.1.4 - high stats collector IO usage
From: Jeff Janes <jeff.janes@gmail.com>
To: David Barton <dave@oneit.com.au>
Cc: pgsql-performance@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
X-Pg-Spam-Score: -2.7 (--)
X-Archive-Number: 201208/73
X-Sequence-Number: 47611

On Sun, Aug 12, 2012 at 7:17 PM, David Barton <dave@oneit.com.au> wrote:
>>
>> A relatively easy change would be to make any given autovacuum worker
>> on start up tolerate a stats file that is out of date by up to, say,
>> naptime/5.  That would greatly reduce the amount of writing the stats
>> collector needs to do (assuming that few tables actually need
>> vacuuming during any given cycle), but wouldn't change the amount of
>> reading a worker needs to do because it still needs to read the file
>> each time as it doesn't inherit the stats from anyone.  I don't think
>> it would be a problem that a table which becomes eligible for
>> vacuuming in the last 20% of a cycle would have to wait for one more
>> round.  Especially as this change might motivate one to reduce the
>> naptime since doing so will be cheaper.
>
> If the stats are mirrored in memory, then that makes sense.  Of course, if
> that's the case then couldn't we just alter the stats to flush at maximum
> once per N seconds / minutes?

The actual flushing is not under our control, but under the kernel's
control.  But in essence that is what I am suggesting.  If the vacuum
workers ask the stats collector for fresh stats less often, the stats
collector will write them to the kernel less often.  If we are writing
them to the kernel less often, the kernel will flush them less often.
The kernel could choose to flush them less often anyway, but for some
reason with ext4 it doesn't.

> If the stats are not mirrored in memory,
> doesn't that imply that most of the databases will never flush updates stats
> to disk and so the file will become stale?

We don't do anything specific to cause them to be mirrored, it is just
that that is the way the kernel deals with frequently accessed
file-system data.  If the kernel decided to evict the data from
memory, it would have to make sure it reached disk first.  It is the
kernel's job to present a consistent image of all the file-system
data, regardless of whether it is actually in memory, or on disk, or
both.  If the data on disk is stale, the kernel guarantees requests to
read it will be served from memory rather than from disk.

Cheers,

Jeff