Message-ID: <0b23386aa250a031713067d46ec33edc.squirrel@sq.gransy.com>
In-Reply-To: 
 <D83E55F5F4D99B4A9B4C4E259E6227CD014A1450@AUX1EXC01.apac.experian.local>
References: 
 <D83E55F5F4D99B4A9B4C4E259E6227CD014A1351@AUX1EXC01.apac.experian.local>
 <CAFj8pRAj2_Dro=h1enFiMnujFJDD-roB22ROqXPJQXDYd7nARw@mail.gmail.com>
 <D83E55F5F4D99B4A9B4C4E259E6227CD014A1450@AUX1EXC01.apac.experian.local>
Date: Thu, 19 Jun 2014 12:01:44 +0200
Subject: Re: huge pgstat.stat file on PostgreSQL 8.3.24
From: "Tomas Vondra" <tv@fuzzy.cz>
To: "Huang, Suya" <Suya.Huang@au.experian.com>
Cc: "Pavel Stehule" <pavel.stehule@gmail.com>,
 "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
User-Agent: SquirrelMail/1.4.22
MIME-Version: 1.0
Content-Type: text/plain;charset=utf-8
Content-Transfer-Encoding: 8bit
Importance: Normal
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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