From: Pavel Stehule [mailto:pavel.stehule@gmail.com]
Sent: Thursday, June 19, 2014 3:41 PM
To: Huang, Suya
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] huge pgstat.stat file on PostgreSQL 8.3.24
2014-06-19 7:35 GMT+02:00 Huang, Suya <Suya.Huang@au.experian.com>:
From: Pavel Stehule [mailto:pavel.stehule@gmail.com]
Sent: Thursday, June 19, 2014 3:28 PM
To: Huang, Suya
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] huge pgstat.stat file on PostgreSQL 8.3.24
Hello
The size of statfile is related to size of database objects in database. Depends on PostgreSQL version this file can be
oneper 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
migrationstat 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
questionwould 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
Hi Pavel,
our version is 8.3.24, not 9.3. I also want to know the impact caused by run pg_stat_reset to application, is that able
tobe mitigated by doing an analyze database command?
your version is too old - you can try reset statistics. ANALYZE statement should not have a significant impact on
theseruntime statistics.
Pavel
Attention: PostgreSQL 8.3 is unsupported now
Thanks,
Suya
Thanks Pavel, to be more clear, what does " pg_stat_reset "really reset? In the document it says " Reset all statistics
countersfor the current database to zero(requires superuser privileges) ". I thought it would reset all statistics of
alltables/indexes, thus why I am thinking of re-run analyze database to gather statistics. Because if table/indexes
don'thave statistics, the query plan would be affected which is not a good thing to a production box... I'm not so sure
ifI understand "run statistics" you mentioned here.
Thanks,
Suya