pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedTwo questions.. shared_buffers and long reader issue
5+ messages / 4 participants
[nested] [flat]
* Two questions.. shared_buffers and long reader issue
@ 2007-07-11 15:35 Patric de Waha <lists@p-dw.com>
0 siblings, 3 replies; 5+ messages in thread
From: Patric de Waha @ 2007-07-11 15:35 UTC (permalink / raw)
To: pgsql-performance
Hi,
I've two questions for which I not really found answers in the web.
Intro:
I've a Website with some traffic.
2 Million queries a day, during daylight.
Postgres is running on a dedicated server P4 DualCore, 4 Gig Ram.
Mainly updates on 1 tuple. And more or less complex SELECT statements.
I noticed that the overall performance of postgres is decreasing
when one or more long
readers are present. Where a long reader here is already a Select
count(*) from table.
As postgres gets slower an slower, and users still hammering on the
reload button to get their
page loaded. Postgres begins to reach max connections, and web site
is stuck.
It's not because of a bad schema or bad select statements. As I said,
a select count(*) on big table is already
triggering this behaviour.
Why do long readers influence the rest of the transactions in such a
heavy way?
Any configuration changes which can help here?
Is it a disc-IO bottleneck thing?
Second question. What is the right choice for the shared_buffers size?
On a dedicated postgres server with 4 Giga RAM. Is there any rule of
thumb?
Actually I set it to +-256M.
thanks for any suggestions.
Patric
My Setup:
Debian Etch
PSQL: 8.1.4
WAL files are located on another disc than the dbase itself.
max_connections = 190
shared_buffers = 30000
temp_buffers = 3000
work_mem = 4096
maintenance_work_mem = 16384
fsync = on
wal_buffers = 16
effective_cache_size = 5000
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Two questions.. shared_buffers and long reader issue
@ 2007-07-11 15:51 Bryan Murphy <bryan.murphy@gmail.com>
parent: Patric de Waha <lists@p-dw.com>
2 siblings, 0 replies; 5+ messages in thread
From: Bryan Murphy @ 2007-07-11 15:51 UTC (permalink / raw)
To: Patric de Waha <lists@p-dw.com>; +Cc: pgsql-performance
We have a few tables that we need to pull relatively accurate aggregate
counts from, and we found the performance of SELECT COUNT(*) to be
unacceptable. We solved this by creating triggers on insert and delete to
update counts in a secondary table which we join to when we need the count
information.
This may or may not work in your scenario, but it was a reasonable trade off
for us.
Bryan
On 7/11/07, Patric de Waha <lists@p-dw.com> wrote:
>
> Hi,
> I've two questions for which I not really found answers in the web.
>
> Intro:
> I've a Website with some traffic.
> 2 Million queries a day, during daylight.
> Postgres is running on a dedicated server P4 DualCore, 4 Gig Ram.
> Mainly updates on 1 tuple. And more or less complex SELECT statements.
> I noticed that the overall performance of postgres is decreasing
> when one or more long
> readers are present. Where a long reader here is already a Select
> count(*) from table.
>
> As postgres gets slower an slower, and users still hammering on the
> reload button to get their
> page loaded. Postgres begins to reach max connections, and web site
> is stuck.
> It's not because of a bad schema or bad select statements. As I said,
> a select count(*) on big table is already
> triggering this behaviour.
>
> Why do long readers influence the rest of the transactions in such a
> heavy way?
> Any configuration changes which can help here?
> Is it a disc-IO bottleneck thing?
>
> Second question. What is the right choice for the shared_buffers size?
> On a dedicated postgres server with 4 Giga RAM. Is there any rule of
> thumb?
> Actually I set it to +-256M.
>
>
> thanks for any suggestions.
>
> Patric
>
>
> My Setup:
>
> Debian Etch
> PSQL: 8.1.4
>
> WAL files are located on another disc than the dbase itself.
>
> max_connections = 190
> shared_buffers = 30000
> temp_buffers = 3000
> work_mem = 4096
> maintenance_work_mem = 16384
> fsync = on
> wal_buffers = 16
> effective_cache_size = 5000
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Two questions.. shared_buffers and long reader issue
@ 2007-07-11 16:57 Tom Lane <tgl@sss.pgh.pa.us>
parent: Patric de Waha <lists@p-dw.com>
2 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2007-07-11 16:57 UTC (permalink / raw)
To: Patric de Waha <lists@p-dw.com>; +Cc: pgsql-performance
Patric de Waha <lists@p-dw.com> writes:
> Postgres is running on a dedicated server P4 DualCore, 4 Gig Ram.
When you don't even mention your disk hardware, that's a bad sign.
In a database server the disk is usually more important than the CPU.
> Why do long readers influence the rest of the transactions in such a
> heavy way?
> Any configuration changes which can help here?
> Is it a disc-IO bottleneck thing?
Very possibly. Have you spent any time watching "vmstat 1" output
to get a sense of whether your I/O is saturated?
> WAL files are located on another disc than the dbase itself.
That's good, but it only relates to update performance not SELECT
performance.
> effective_cache_size = 5000
That's way too small for a 4G machine. You could probably stand to
boost maintenance_work_mem too. However, neither of these have any
immediate relationship to your problem.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Two questions.. shared_buffers and long reader issue
@ 2007-07-11 16:58 Andrew Sullivan <ajs@crankycanuck.ca>
parent: Patric de Waha <lists@p-dw.com>
2 siblings, 0 replies; 5+ messages in thread
From: Andrew Sullivan @ 2007-07-11 16:58 UTC (permalink / raw)
To: pgsql-performance
On Wed, Jul 11, 2007 at 05:35:33PM +0200, Patric de Waha wrote:
> Mainly updates on 1 tuple.
Are you vacuuming that table enough?
> And more or less complex SELECT statements.
> I noticed that the overall performance of postgres is decreasing
> when one or more long
> readers are present. Where a long reader here is already a Select
> count(*) from table.
SELECT count(*) is expensive in Postgres. Do you really need it?
Unqualified count() in PostgreSQL is just a bad thing to do, so if
you can work around it (by doing limited subselects, for instance,
where you never scan more than 50 rows, or by keeping counts using
triggers, or various other tricks), it's a good idea.
> Why do long readers influence the rest of the transactions in such a
> heavy way?
It could be because of all those updated tuples not getting vacuumed
(which results in a bad plan). Or it could be that your connection
pool is exhausted: note that when someone hits "reload", that doesn't
mean your old query goes away. It is still crunching through
whatever work it was doing.
> Second question. What is the right choice for the shared_buffers size?
> On a dedicated postgres server with 4 Giga RAM. Is there any rule of
> thumb?
> Actually I set it to +-256M.
There has been Much Discussion of this lately on this list. I
suggest you have a look through the recent archives on that topic.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
"The year's penultimate month" is not in truth a good way of saying
November.
--H.W. Fowler
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Two questions.. shared_buffers and long reader issue
@ 2007-07-11 20:48 Patric de Waha <lists@p-dw.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: Patric de Waha @ 2007-07-11 20:48 UTC (permalink / raw)
To: ; +Cc: pgsql-performance
Ok thanks.
iostat confirmed it's an IO bottleneck.
Will add some discs to the RAID unit.
Used 4 Raptor discs in Raid 10 until now.
best regards,
patric
Tom Lane wrote:
> Patric de Waha <lists@p-dw.com> writes:
>
>> Postgres is running on a dedicated server P4 DualCore, 4 Gig Ram.
>>
>
> When you don't even mention your disk hardware, that's a bad sign.
> In a database server the disk is usually more important than the CPU.
>
>
>> Why do long readers influence the rest of the transactions in such a
>> heavy way?
>> Any configuration changes which can help here?
>> Is it a disc-IO bottleneck thing?
>>
>
> Very possibly. Have you spent any time watching "vmstat 1" output
> to get a sense of whether your I/O is saturated?
>
>
>> WAL files are located on another disc than the dbase itself.
>>
>
> That's good, but it only relates to update performance not SELECT
> performance.
>
>
>> effective_cache_size = 5000
>>
>
> That's way too small for a 4G machine. You could probably stand to
> boost maintenance_work_mem too. However, neither of these have any
> immediate relationship to your problem.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2007-07-11 20:48 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-07-11 15:35 Two questions.. shared_buffers and long reader issue Patric de Waha <lists@p-dw.com>
2007-07-11 15:51 ` Bryan Murphy <bryan.murphy@gmail.com>
2007-07-11 16:57 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-07-11 20:48 ` Patric de Waha <lists@p-dw.com>
2007-07-11 16:58 ` Andrew Sullivan <ajs@crankycanuck.ca>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox