Postgres Pro
Downloads
Home   >   mailing lists

Re: huge pgstat.stat file on PostgreSQL 8.3.24 - Mailing list pgsql-performance

From Pavel Stehule
Subject Re: huge pgstat.stat file on PostgreSQL 8.3.24
Date
Msg-id CAFj8pRAj2_Dro=h1enFiMnujFJDD-roB22ROqXPJQXDYd7nARw@mail.gmail.com
Whole thread Raw
In response to huge pgstat.stat file on PostgreSQL 8.3.24  ("Huang, Suya" <Suya.Huang@au.experian.com>)
Responses Re: huge pgstat.stat file on PostgreSQL 8.3.24
List pgsql-performance
Tree view
Hello


The size of statfile is related to size of database objects in database. Depends on PostgreSQL version this file can be one per database cluster or one per database (from 9.3),

These statistics should by reset by call pg_stat_reset() http://www.postgresql.org/docs/9.2/static/monitoring-stats.html

Autovacuum on large stat files has significant overhead - it can be increasing by using new PostgreSQL (9.3) and by migration stat directory to ramdisk - by setting stats_temp_directory to some dir on ramdisk (tmpfs on Linux)

Regards

Pavel



2014-06-19 6:38 GMT+02:00 Huang, Suya <Suya.Huang@au.experian.com>:

Hi group,

 

We’ve found huge pgstat.stat file on our production DB boxes, the size is over 100MB. autovacuum is enabled. So my question would be:

1.       What’s a reasonable size of pgstat.stat file, can it be estimated?

2.       What’s the safest way to reduce the file size to alleviate the IO impact on disk?

3.       If need to drop all statistics, would a “analyze DB” command enough to eliminate the performance impact on queries?

 

Thanks,

Suya


pgsql-performance by date:

Previous
From: "Huang, Suya"
Date:
Subject: huge pgstat.stat file on PostgreSQL 8.3.24
Next
From: "Huang, Suya"
Date:
Subject: Re: huge pgstat.stat file on PostgreSQL 8.3.24