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@alvh.no-ip.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@alvh.no-ip.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@uptime.jp>
Uptime Technologies, LLC. http://www.uptime.jp