Re: [rfc] overhauling pgstat.stat

Hi,

(2013/09/04 13:07), Alvaro Herrera wrote:
> Satoshi Nagayasu wrote:
>
>> As you may know, this file could be handreds of MB in size, because
>> pgstat.stat holds all access statistics in each database, and it needs
>> to read/write an entire pgstat.stat frequently.
>>
>> As a result, pgstat.stat often generates massive I/O operation,
>> particularly when having a large number of tables in the database.
>
> We already changed it:
>
> commit 187492b6c2e8cafc5b39063ca3b67846e8155d24
> Author: Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>
> Date: Mon Feb 18 17:56:08 2013 -0300
>
> Split pgstat file in smaller pieces

Thanks for the comments. I forgot to mention that.

Yes, we have already split single pgstat.stat file into
several pieces.

However, we still need to read/write large amount of statistics
data when we have a large number of tables in single database
or multiple databases being accessed. Right?

I think the issue here is that it is necessary to write/read
statistics data even it's not actually changed.

So, I'm wondering how we can minimize read/write operations
on these statistics data files with using heap and btree.

Regards,

>
> commit 187492b6c2e8cafc5b39063ca3b67846e8155d24
> Author: Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>
> Date: Mon Feb 18 17:56:08 2013 -0300
>
> Split pgstat file in smaller pieces
>
> We now write one file per database and one global file, instead of
> having the whole thing in a single huge file. This reduces the I/O that
> must be done when partial data is required -- which is all the time,
> because each process only needs information on its own database anyway.
> Also, the autovacuum launcher does not need data about tables and
> functions in each database; having the global stats for all DBs is
> enough.
>
> Catalog version bumped because we have a new subdir under PGDATA.
>
> Author: Tomas Vondra. Some rework by Álvaro
> Testing by Jeff Janes
> Other discussion by Heikki Linnakangas, Tom Lane.
>
>
> I don't oppose further tweaking, of course, but I wonder if you are
> considering these changes.
>

--
Satoshi Nagayasu <snaga(at)uptime(dot)jp>
Uptime Technologies, LLC. http://www.uptime.jp