Re: Basic Database Performance

Hi, James,

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.

The first thing that comes into my mind here is "connection pooling /
recycling".

Try to make shure that connections are reused between http requests.
Reopening the connection on every http request will break your system,
as the backend startup time is rather high.

> 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.

Note that amount of data, concurrent users, hardware and speed don't
always scale linearly.

> The server spikes from 5% usage to 95% up and down. 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 should make shure that you run vacuum / analyze regularly (either
autovacuum, or vacuum full at night when you have no users on the system).

Use statement logging or other profiling means to isolate the slow
queries, and EXPLAIN ANALYZE them to see what goes wrong. Create the
needed indices, and drop unneded one. (insert usual performance tuning
tips here...)

> 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!)

Ignore 8.0 and go to 8.1 directly.

HTH,
Markus

--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org