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@xxxxxxxxxxxx>: > 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@xxxxxxxxxxxx -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
