Message-ID: <43EC5EA3.5070001@archonet.com>
Date: Fri, 10 Feb 2006 09:36:35 +0000
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 1.5 (X11/20051201)
MIME-Version: 1.0
To: James Dey <james@mygus.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Basic Database Performance
References: <20060210082236.4CF9D443F@ctb-mesg3.saix.net>
In-Reply-To: <20060210082236.4CF9D443F@ctb-mesg3.saix.net>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

James Dey wrote:
> 
> Apologies if this is a novice queston, but I think it is a performance one
> nevertheless. We are running a prototype of a system running on
> PHP/Postgresql on an Intel Xeon 2ghz server, 1GB RAM, 40GB hard drive, as a
> test bench. The system will be used for tens of thousands of users, and at
> the moment we are testing on a base of around 400 users concurrently during
> the day.

OK, that's 400 web-users, so presumably a fraction of that for 
concurrent database connections.

> During the day, the system is incredibly slow to a point where it is
> unusable. The reason we are testing on such as small server is to test
> performance under pressure, and my estimation is that spec should handle
> thousands of users.

It'll depend on what the users are doing
It'll depend on what your code is doing
It'll depend on how you've configured PostgreSQL.

> The server spikes from 5% usage to 95% up and down.

Usage? Do you mean CPU?

 > The system is a very
> simple e-learning and management system and has not given us any issues to
> date, only since we've been testing with more users has it done so. The fact
> that 400 users doing inserts and queries every few minutes is very
> concerning, I would like to know if I could be tweaking some config
> settings.

You haven't said what config settings you're working with.

OK - the main questions have to be:
1. Are you limited by CPU, memory or disk i/o?
2. Are you happy your config settings are good?
    How do you know?
3. Are there particular queries that are causing the problem, or lock 
contention?

> We are running PG 7.4 on a Debian Sarge server, and will be upgrading to
> pg8.0 on a new server, but have some migration issues (that's for another
> list!)

Go straight to 8.1 - no point in upgrading half-way. If you don't like 
compiling from source it's in backports.org

-- 
   Richard Huxton
   Archonet Ltd