From: Patric de Waha <lists@p-dw.com>
To: pgsql-performance@postgresql.org
Subject: Two questions.. shared_buffers and long reader issue
Date: Wed, 11 Jul 2007 17:35:33 +0200
Message-ID: <4694F8C5.2040005@p-dw.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: lists@p-dw.com
Subject: Re: Two questions.. shared_buffers and long reader issue
In-Reply-To: <4694F8C5.2040005@p-dw.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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