Lists: | pgsql-general |
---|
From: | "Markus Bertheau" <mbertheau(dot)pg(at)googlemail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Lockless pg_buffercache |
Date: | 2008-02-10 04:34:40 |
Message-ID: | 684362e10802092034rebc1e67n910f5ddb8816a31d@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Hi,
I want to use the pg_buffercache contrib module for monitoring our
server. It takes a lock on all buffers and then on each buffer header
in order to get a consistent picture of the buffers. I would be
running the function provided by the module once every 5 minutes.
I'm worrying about the performance hit of that - a comment in the code
says it's horrible for concurrency. Additionally, as I don't use this
for debugging, but just for monitoring, I don't need a 100% consistent
picture, just rough numbers how much of the buffer cache is used for
what relation.
Does removing all locking as in the attached patch have any negative
impact other than the non-consistency of the results?
Thanks
Markus
Attachment | Content-Type | Size |
---|---|---|
pg_buffercache_nolocks.patch | text/x-diff | 1.8 KB |
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Markus Bertheau" <mbertheau(dot)pg(at)googlemail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Lockless pg_buffercache |
Date: | 2008-02-10 06:12:39 |
Message-ID: | 8409.1202623959@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
"Markus Bertheau" <mbertheau(dot)pg(at)googlemail(dot)com> writes:
> Does removing all locking as in the attached patch have any negative
> impact other than the non-consistency of the results?
Removing the LockBufHdr bit is definitely a pretty foolish idea.
regards, tom lane
From: | "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com> |
---|---|
To: | "Markus Bertheau" <mbertheau(dot)pg(at)googlemail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Lockless pg_buffercache |
Date: | 2008-02-10 09:39:40 |
Message-ID: | dcc563d10802100139n5f92062eg152c63628828ef1@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On Feb 9, 2008 10:34 PM, Markus Bertheau <mbertheau(dot)pg(at)googlemail(dot)com> wrote:
> Hi,
>
> I want to use the pg_buffercache contrib module for monitoring our
> server. It takes a lock on all buffers and then on each buffer header
> in order to get a consistent picture of the buffers. I would be
> running the function provided by the module once every 5 minutes.
If it takes 500mS every 5 minutes that might be an acceptable decrease
in performance for that second or two.
> I'm worrying about the performance hit of that - a comment in the code
> says it's horrible for concurrency.
It is, but again, it really depends on the time frame etc. If you can
run it quickly as a series of related queries or a user function, it
should be fast enough to not be a significant burden.
So, what's the real burden like from the pg_buffercache contrib module?
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com> |
Cc: | "Markus Bertheau" <mbertheau(dot)pg(at)googlemail(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Lockless pg_buffercache |
Date: | 2008-02-10 17:24:33 |
Message-ID: | 15812.1202664273@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
"Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com> writes:
> So, what's the real burden like from the pg_buffercache contrib module?
I wonder whether pg_buffercache should be changed to work like the
statistics views do, ie, you take a snapshot during the first call
within a transaction. This would allow correlation of the results
across multiple queries.
regards, tom lane