Re: [rfc] overhauling pgstat.stat - Mailing list pgsql-hackers

On 4.9.2013 07:24, Satoshi Nagayasu wrote:
> Hi,
> 
> (2013/09/04 12:52), Atri Sharma wrote:
>> On Wed, Sep 4, 2013 at 6:40 AM, Satoshi Nagayasu <snaga@uptime.jp>
>>  wrote:
>>> Hi,
>>> 
>>> I'm considering overhauling pgstat.stat, and would like to know 
>>> how many people are interested in this topic.
>>> 
>>> 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.
>>> 
>>> To support multi-tenancy or just a large number of tables (up to
>>>  10k tables in single database), I think pgstat.stat needs to be
>>>  overhauled.
>>> 
>>> I think using heap and btree in pgstat.stat would be preferred to
>>> reduce read/write and to allow updating access statistics for 
>>> specific tables in pgstat.stat file.
>>> 
>>> Is this good for us?
>> 
>> Hi,
>> 
>> Nice thought. I/O reduction in pgstat can be really helpful.
>> 
>> I am trying to think of our aim here. Would we be looking to split 
>> pgstat per table, so that the I/O write happens for only a portion
>> of pgstat? Or reduce the I/O in general?
> 
> I prefer the latter.
> 
> Under the current implementation, DBA need to split single database 
> into many smaller databases with considering access locality of the 
> tables. It's difficult and could be change in future.
> 
> And splitting the statistics data into many files (per table, for 
> example) would cause another performance issue when 
> collecting/showing statistics at once. Just my guess though.
> 
> So, I'm looking for a new way to reduce I/O for the statistics data 
> in general.

Hi,

as one of the authors of the 9.3 patch (per database stats), I planned
to work on this a bit more in the 9.4 cycle. So a few comments / ideas.

I'm not entirely sure splitting the stats per table would be that bad.
After all we already have per-relation data files, so either the users
already have serious problems (so this won't make it noticeably worse)
or it will work fine. But I'm not saying it's the right choice either.

My idea was to keep the per-database stats, but allow some sort of
"random" access - updating / deleting the records in place, adding
records etc. The simplest way I could think of was adding a simple
"index" - a mapping of OID to position in the stat file.

I.e. a simple  array of (oid, offset) pairs, stored in oid.stat.index or
something like that. This would make it quite simple to access existing
record
 1: get position from the index 2: read sizeof(Entry) from the file 3: if it's update, just overwrite the bytes, for
deleteset isdeleted    flag (needs to be added to all entries)
 

or reading all the records (just read the whole file as today).

regards
Tomas