2012/8/6 Magnus Hagander <magnus@xxxxxxxxxxxx>: > 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@xxxxxxxxx> 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@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 > > > > -- > Magnus Hagander > Me: http://www.hagander.net/ > Work: http://www.redpill-linpro.com/ -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
