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--