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