pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedhuge pgstat.stat file on PostgreSQL 8.3.24
9+ messages / 3 participants
[nested] [flat]
* huge pgstat.stat file on PostgreSQL 8.3.24
@ 2014-06-19 04:38 Huang, Suya <Suya.Huang@au.experian.com>
0 siblings, 1 reply; 9+ messages in thread
From: Huang, Suya @ 2014-06-19 04:38 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: huge pgstat.stat file on PostgreSQL 8.3.24
@ 2014-06-19 05:28 Pavel Stehule <pavel.stehule@gmail.com>
parent: Huang, Suya <Suya.Huang@au.experian.com>
0 siblings, 1 reply; 9+ messages in thread
From: Pavel Stehule @ 2014-06-19 05:28 UTC (permalink / raw)
To: Huang, Suya <Suya.Huang@au.experian.com>; +Cc: pgsql-performance
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
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: huge pgstat.stat file on PostgreSQL 8.3.24
@ 2014-06-19 05:35 Huang, Suya <Suya.Huang@au.experian.com>
parent: Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 2 replies; 9+ messages in thread
From: Huang, Suya @ 2014-06-19 05:35 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; +Cc: pgsql-performance
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 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
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 to be mitigated by doing an analyze database command?
Thanks,
Suya
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: huge pgstat.stat file on PostgreSQL 8.3.24
@ 2014-06-19 05:41 Pavel Stehule <pavel.stehule@gmail.com>
parent: Huang, Suya <Suya.Huang@au.experian.com>
1 sibling, 1 reply; 9+ messages in thread
From: Pavel Stehule @ 2014-06-19 05:41 UTC (permalink / raw)
To: Huang, Suya <Suya.Huang@au.experian.com>; +Cc: pgsql-performance
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 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
>
>
>
>
> 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 to be 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 these runtime statistics.
Pavel
Attention: PostgreSQL 8.3 is unsupported now
> Thanks,
> Suya
>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: huge pgstat.stat file on PostgreSQL 8.3.24
@ 2014-06-19 10:01 Tomas Vondra <tv@fuzzy.cz>
parent: Huang, Suya <Suya.Huang@au.experian.com>
1 sibling, 0 replies; 9+ messages in thread
From: Tomas Vondra @ 2014-06-19 10:01 UTC (permalink / raw)
To: Huang, Suya <Suya.Huang@au.experian.com>; +Cc: Pavel Stehule <pavel.stehule@gmail.com>; pgsql-performance
On 19 Červen 2014, 7:35, Huang, Suya wrote:
> 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 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
>
>
>
>
> 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 to be mitigated by doing an
> analyze database command?
Hi,
I really doubt you're on 8.3.24. The last version in 8.3 branch is 8.3.23.
Running pg_stat_reset has no impact on planning queries. There are two
kinds of statistics - those used for planning are stored withing the
database, not in pgstat.stat file and are not influenced by pg_stat_reset.
The stats in pgstat.stat are 'runtime stats' used for monitoring etc. so
you may see some distuption in your monitoring system. ANALYZE command has
nothing to do with the stats in pgstat.stat.
However, if you really have a pgstat.stat this large, this is only a
temporary solution - it will grow back, possibly pretty quickly, depending
on how often you access the objects.
Another option is to move the file to a tmpfs (ramdisk) partition. It will
eliminate the IO overhead, but it will consume more CPU (because it still
needs to be processed, and IO is not the bottleneck anymore).
The other thing is that you should really start thinking about upgrading
to a supported version. 8.3 did not get updates for > 1 year (and won't).
Tomas
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: huge pgstat.stat file on PostgreSQL 8.3.24
@ 2014-06-19 23:44 Huang, Suya <Suya.Huang@au.experian.com>
parent: Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Huang, Suya @ 2014-06-19 23:44 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; +Cc: pgsql-performance
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 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
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 to be 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 these runtime 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 counters for the current database to zero(requires superuser privileges) ". I thought it would reset all statistics of all tables/indexes, thus why I am thinking of re-run analyze database to gather statistics. Because if table/indexes don't have statistics, the query plan would be affected which is not a good thing to a production box... I'm not so sure if I understand "run statistics" you mentioned here.
Thanks,
Suya
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: huge pgstat.stat file on PostgreSQL 8.3.24
@ 2014-06-20 03:33 Pavel Stehule <pavel.stehule@gmail.com>
parent: Huang, Suya <Suya.Huang@au.experian.com>
0 siblings, 1 reply; 9+ messages in thread
From: Pavel Stehule @ 2014-06-20 03:33 UTC (permalink / raw)
To: Huang, Suya <Suya.Huang@au.experian.com>; +Cc: pgsql-performance
2014-06-20 1:44 GMT+02:00 Huang, Suya <Suya.Huang@au.experian.com>:
>
>
> 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 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
>
>
>
> 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 to be 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 these runtime 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 counters for the current
> database to zero(requires superuser privileges) ". I thought it would
> reset all statistics of all tables/indexes, thus why I am thinking of
> re-run analyze database to gather statistics. Because if table/indexes
> don't have statistics, the query plan would be affected which is not a good
> thing to a production box... I'm not so sure if I understand "run
> statistics" you mentioned here.
>
you have true - anyway you can clean a content of this directory - but if
your database has lot of database objects, your stat file will have a
original size very early
Pavel
>
> Thanks,
> Suya
>
>
>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: huge pgstat.stat file on PostgreSQL 8.3.24
@ 2014-06-20 10:14 Tomas Vondra <tv@fuzzy.cz>
parent: Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Tomas Vondra @ 2014-06-20 10:14 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; +Cc: Huang, Suya <suya.huang@au.experian.com>; pgsql-performance
On 20 Červen 2014, 5:33, Pavel Stehule wrote:
> 2014-06-20 1:44 GMT+02:00 Huang, Suya <Suya.Huang@au.experian.com>:
>>
>> Thanks Pavel, to be more clear, what does " pg_stat_reset "really reset?
>> In the document it says " Reset all statistics counters for the current
>> database to zero(requires superuser privileges) ". I thought it would
>> reset all statistics of all tables/indexes, thus why I am thinking of
>> re-run analyze database to gather statistics. Because if table/indexes
>> don't have statistics, the query plan would be affected which is not a
>> good
>> thing to a production box... I'm not so sure if I understand "run
>> statistics" you mentioned here.
>>
>
> you have true - anyway you can clean a content of this directory - but if
> your database has lot of database objects, your stat file will have a
> original size very early
>
> Pavel
>
No, he's not right.
Suya, as I wrote in my previous message, there are two kinds of statistics
in PostgreSQL
a) data distribution statistics
- histograms, MCV lists, number of distinct values, ...
- stored in regular tables
- used for planning
- collected by ANALYZE
- not influenced by pg_stat_reset() at all
b) runtime statistics
- number of scans for table/index, rows fetched from table/index, ...
- tracks activity within the database
- stored in pgstat.stat file (or per-db files in the recent releases)
- used for monitoring, not for planning
- removed by pg_stat_reset()
So running pg_stat_reset will not hurt planning at all.
regards
Tomas
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: huge pgstat.stat file on PostgreSQL 8.3.24
@ 2014-06-24 07:07 Huang, Suya <Suya.Huang@au.experian.com>
parent: Tomas Vondra <tv@fuzzy.cz>
0 siblings, 0 replies; 9+ messages in thread
From: Huang, Suya @ 2014-06-24 07:07 UTC (permalink / raw)
To: Tomas Vondra <tv@fuzzy.cz>; Pavel Stehule <pavel.stehule@gmail.com>; +Cc: pgsql-performance
-----Original Message-----
From: Tomas Vondra [mailto:tv@fuzzy.cz]
Sent: Friday, June 20, 2014 8:14 PM
To: Pavel Stehule
Cc: Huang, Suya; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] huge pgstat.stat file on PostgreSQL 8.3.24
On 20 Červen 2014, 5:33, Pavel Stehule wrote:
> 2014-06-20 1:44 GMT+02:00 Huang, Suya <Suya.Huang@au.experian.com>:
>>
>> Thanks Pavel, to be more clear, what does " pg_stat_reset "really reset?
>> In the document it says " Reset all statistics counters for the
>> current database to zero(requires superuser privileges) ". I thought
>> it would reset all statistics of all tables/indexes, thus why I am
>> thinking of re-run analyze database to gather statistics. Because if
>> table/indexes don't have statistics, the query plan would be affected
>> which is not a good thing to a production box... I'm not so sure if I
>> understand "run statistics" you mentioned here.
>>
>
> you have true - anyway you can clean a content of this directory - but
> if your database has lot of database objects, your stat file will have
> a original size very early
>
> Pavel
>
No, he's not right.
Suya, as I wrote in my previous message, there are two kinds of statistics in PostgreSQL
a) data distribution statistics
- histograms, MCV lists, number of distinct values, ...
- stored in regular tables
- used for planning
- collected by ANALYZE
- not influenced by pg_stat_reset() at all
b) runtime statistics
- number of scans for table/index, rows fetched from table/index, ...
- tracks activity within the database
- stored in pgstat.stat file (or per-db files in the recent releases)
- used for monitoring, not for planning
- removed by pg_stat_reset()
So running pg_stat_reset will not hurt planning at all.
regards
Tomas
Hi Tomas,
You're right, my DB version is 8.3.11, I remembered the wrong version... we've got a new project using the latest version 9.3.4, and the old DB will be decommissioned in the future, so that's why the management people don't want to spend resources on upgrading and QA, etc.
Still have a question of why the file would become so big, is that related to the number of objects I have in database?
Thanks again for your clear explanation on the two different statistics in PostgreSQL DB, really helped a lot! I'm wondering if they should also exist in the documentation, as it really confuses people... :)
Thanks,
Suya
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2014-06-24 07:07 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-06-19 04:38 huge pgstat.stat file on PostgreSQL 8.3.24 Huang, Suya <Suya.Huang@au.experian.com>
2014-06-19 05:28 ` Pavel Stehule <pavel.stehule@gmail.com>
2014-06-19 05:35 ` Huang, Suya <Suya.Huang@au.experian.com>
2014-06-19 05:41 ` Pavel Stehule <pavel.stehule@gmail.com>
2014-06-19 23:44 ` Huang, Suya <Suya.Huang@au.experian.com>
2014-06-20 03:33 ` Pavel Stehule <pavel.stehule@gmail.com>
2014-06-20 10:14 ` Tomas Vondra <tv@fuzzy.cz>
2014-06-24 07:07 ` Huang, Suya <Suya.Huang@au.experian.com>
2014-06-19 10:01 ` Tomas Vondra <tv@fuzzy.cz>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox