Re: Basic Database Performance

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