Re: [HACKERS] [rfc] overhauling pgstat.stat

2013-09-09 Thread Tomas Vondra
On 9 Září 2013, 18:50, Robert Haas wrote: > On Sat, Sep 7, 2013 at 6:57 PM, Tomas Vondra wrote: >> Don't forget the stats are written only by the postmaster, all the >> regular backends only read it (and eventually send updates back). > > The postmaster, or the stats collector? Stats collector,

Re: [HACKERS] [rfc] overhauling pgstat.stat

2013-09-09 Thread Robert Haas
On Sat, Sep 7, 2013 at 6:57 PM, Tomas Vondra wrote: > Don't forget the stats are written only by the postmaster, all the > regular backends only read it (and eventually send updates back). The postmaster, or the stats collector? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enter

Re: [HACKERS] [rfc] overhauling pgstat.stat

2013-09-09 Thread Satoshi Nagayasu
(2013/09/09 8:19), Tomas Vondra wrote: On 8.9.2013 23:04, Jeff Janes wrote: On Tue, Sep 3, 2013 at 10:09 PM, Satoshi Nagayasu wrote: 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 hol

Re: [HACKERS] [rfc] overhauling pgstat.stat

2013-09-08 Thread Tomas Vondra
On 8.9.2013 23:04, Jeff Janes wrote: > On Tue, Sep 3, 2013 at 10:09 PM, Satoshi Nagayasu > wrote: >> 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

Re: [HACKERS] [rfc] overhauling pgstat.stat

2013-09-08 Thread Jeff Janes
On Tue, Sep 3, 2013 at 10:09 PM, Satoshi Nagayasu wrote: > 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

Re: [HACKERS] [rfc] overhauling pgstat.stat

2013-09-07 Thread Atri Sharma
Sent from my iPad On 08-Sep-2013, at 4:27, Tomas Vondra wrote: > On 5.9.2013 09:36, Atri Sharma wrote: >> On Thu, Sep 5, 2013 at 10:59 AM, Alvaro Herrera >> wrote: >>> Satoshi Nagayasu wrote: >>> But, for now, I think we should have a real index for the statistics data because we

Re: [HACKERS] [rfc] overhauling pgstat.stat

2013-09-07 Thread Atri Sharma
Sent from my iPad On 08-Sep-2013, at 4:27, Tomas Vondra wrote: > On 5.9.2013 09:36, Atri Sharma wrote: >> On Thu, Sep 5, 2013 at 10:59 AM, Alvaro Herrera >> wrote: >>> Satoshi Nagayasu wrote: >>> But, for now, I think we should have a real index for the statistics data because we

Re: [HACKERS] [rfc] overhauling pgstat.stat

2013-09-07 Thread Tomas Vondra
On 5.9.2013 07:29, Alvaro Herrera wrote: > Satoshi Nagayasu wrote: > >> But, for now, I think we should have a real index for the >> statistics data because we already have several index storages, and >> it will allow us to minimize read/write operations. >> >> BTW, what kind of index would be p

Re: [HACKERS] [rfc] overhauling pgstat.stat

2013-09-07 Thread Tomas Vondra
On 5.9.2013 09:36, Atri Sharma wrote: > On Thu, Sep 5, 2013 at 10:59 AM, Alvaro Herrera > wrote: >> Satoshi Nagayasu wrote: >> >>> But, for now, I think we should have a real index for the >>> statistics data because we already have several index storages, >>> and it will allow us to minimize

Re: [HACKERS] [rfc] overhauling pgstat.stat

2013-09-05 Thread Atri Sharma
On Thu, Sep 5, 2013 at 10:59 AM, Alvaro Herrera wrote: > Satoshi Nagayasu wrote: > >> But, for now, I think we should have a real index for the >> statistics data because we already have several index storages, >> and it will allow us to minimize read/write operations. >> >> BTW, what kind of inde

Re: [HACKERS] [rfc] overhauling pgstat.stat

2013-09-04 Thread Alvaro Herrera
Satoshi Nagayasu wrote: > But, for now, I think we should have a real index for the > statistics data because we already have several index storages, > and it will allow us to minimize read/write operations. > > BTW, what kind of index would be preferred for this purpose? > btree or hash? I find

Re: [HACKERS] [rfc] overhauling pgstat.stat

2013-09-04 Thread Atri Sharma
Sent from my iPad On 05-Sep-2013, at 8:58, Satoshi Nagayasu wrote: > (2013/09/05 3:59), Alvaro Herrera wrote: >> Tomas Vondra wrote: >> >>> 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

Re: [HACKERS] [rfc] overhauling pgstat.stat

2013-09-04 Thread Satoshi Nagayasu
(2013/09/05 3:59), Alvaro Herrera wrote: Tomas Vondra wrote: 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 pos

Re: [HACKERS] [rfc] overhauling pgstat.stat

2013-09-04 Thread Satoshi Nagayasu
(2013/09/05 3:50), Pavel Stehule wrote: > we very successfully use a tmpfs volume for pgstat files (use a backport > of multiple statfiles from 9.3 to 9.1 It works quite well as long as you have the objects (tables, indexes, functions) spread across multiple databases. Once

Re: [HACKERS] [rfc] overhauling pgstat.stat

2013-09-04 Thread Tomas Vondra
On 4.9.2013 14:13, Stephen Frost wrote: > * Satoshi Nagayasu (sn...@uptime.jp) wrote: > >> 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 data

Re: [HACKERS] [rfc] overhauling pgstat.stat

2013-09-04 Thread Tomas Vondra
On 4.9.2013 20:59, Alvaro Herrera wrote: > Tomas Vondra wrote: > >> 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

Re: [HACKERS] [rfc] overhauling pgstat.stat

2013-09-04 Thread Alvaro Herrera
Tomas Vondra wrote: > 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

Re: [HACKERS] [rfc] overhauling pgstat.stat

2013-09-04 Thread Pavel Stehule
> > > > > we very successfully use a tmpfs volume for pgstat files (use a backport > > of multiple statfiles from 9.3 to 9.1 > > It works quite well as long as you have the objects (tables, indexes, > functions) spread across multiple databases. Once you have one database > with very large number o

Re: [HACKERS] [rfc] overhauling pgstat.stat

2013-09-04 Thread Tomas Vondra
On 4.9.2013 14:43, Pavel Stehule wrote: > > > > 2013/9/4 Atri Sharma mailto:atri.j...@gmail.com>> > > > > Sent from my iPad > > On 04-Sep-2013, at 15:39, Satoshi Nagayasu > wrote: > > > (2013/09/04 15:23), Atri Sharma wrote: > >> > >> > >

Re: [HACKERS] [rfc] overhauling pgstat.stat

2013-09-04 Thread Tomas Vondra
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 >> wrote: >>> Hi, >>> >>> I'm considering overhauling pgstat.stat, and would like to know >>> how many people are interested in this topic. >>> >>> A

Re: [HACKERS] [rfc] overhauling pgstat.stat

2013-09-04 Thread Pavel Stehule
2013/9/4 Atri Sharma > > > Sent from my iPad > > On 04-Sep-2013, at 15:39, Satoshi Nagayasu wrote: > > > (2013/09/04 15:23), Atri Sharma wrote: > >> > >> > >> Sent from my iPad > >> > >> On 04-Sep-2013, at 10:54, Satoshi Nagayasu wrote: > >> > >>> Hi, > >>> > >>> (2013/09/04 12:52), Atri Sharma

Re: [HACKERS] [rfc] overhauling pgstat.stat

2013-09-04 Thread Pavel Stehule
2013/9/4 Atri Sharma > > > Sent from my iPad > > On 04-Sep-2013, at 15:39, Satoshi Nagayasu wrote: > > > (2013/09/04 15:23), Atri Sharma wrote: > >> > >> > >> Sent from my iPad > >> > >> On 04-Sep-2013, at 10:54, Satoshi Nagayasu wrote: > >> > >>> Hi, > >>> > >>> (2013/09/04 12:52), Atri Sharma

Re: [HACKERS] [rfc] overhauling pgstat.stat

2013-09-04 Thread Atri Sharma
Sent from my iPad On 04-Sep-2013, at 15:39, Satoshi Nagayasu wrote: > (2013/09/04 15:23), Atri Sharma wrote: >> >> >> Sent from my iPad >> >> On 04-Sep-2013, at 10:54, Satoshi Nagayasu wrote: >> >>> Hi, >>> >>> (2013/09/04 12:52), Atri Sharma wrote: On Wed, Sep 4, 2013 at 6:40 AM, S

Re: [HACKERS] [rfc] overhauling pgstat.stat

2013-09-04 Thread Stephen Frost
Satoshi, * Satoshi Nagayasu (sn...@uptime.jp) wrote: > (2013/09/04 13:07), Alvaro Herrera wrote: > >We already changed it: > > > > commit 187492b6c2e8cafc5b39063ca3b67846e8155d24 > > Author: Alvaro Herrera > > Date: Mon Feb 18 17:56:08 2013 -0300 > > > > Split pgstat file in smaller pieces

Re: [HACKERS] [rfc] overhauling pgstat.stat

2013-09-04 Thread Satoshi Nagayasu
(2013/09/04 15:23), Atri Sharma wrote: Sent from my iPad On 04-Sep-2013, at 10:54, Satoshi Nagayasu wrote: Hi, (2013/09/04 12:52), Atri Sharma wrote: On Wed, Sep 4, 2013 at 6:40 AM, Satoshi Nagayasu wrote: Hi, I'm considering overhauling pgstat.stat, and would like to know how many peo

Re: [HACKERS] [rfc] overhauling pgstat.stat

2013-09-03 Thread Atri Sharma
Sent from my iPad On 04-Sep-2013, at 10:54, Satoshi Nagayasu wrote: > Hi, > > (2013/09/04 12:52), Atri Sharma wrote: >> On Wed, Sep 4, 2013 at 6:40 AM, Satoshi Nagayasu wrote: >>> Hi, >>> >>> I'm considering overhauling pgstat.stat, and would like to know how many >>> people are interested

Re: [HACKERS] [rfc] overhauling pgstat.stat

2013-09-03 Thread Satoshi Nagayasu
Hi, (2013/09/04 12:52), Atri Sharma wrote: On Wed, Sep 4, 2013 at 6:40 AM, Satoshi Nagayasu 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

Re: [HACKERS] [rfc] overhauling pgstat.stat

2013-09-03 Thread Satoshi Nagayasu
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 gener

Re: [HACKERS] [rfc] overhauling pgstat.stat

2013-09-03 Thread Alvaro Herrera
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, > particular

Re: [HACKERS] [rfc] overhauling pgstat.stat

2013-09-03 Thread Atri Sharma
On Wed, Sep 4, 2013 at 6:40 AM, Satoshi Nagayasu 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 eac

[HACKERS] [rfc] overhauling pgstat.stat

2013-09-03 Thread Satoshi Nagayasu
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 frequent