Thread: Basic Database Performance
Hi Guys,
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.
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.
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.
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!)
Any help would be greatly appreciated!
All the very best,
James Dey
tel +27 11 704-1945
cell +27 82 785-5102
fax +27 11 388-8907
mail james@mygus.com
myGUS / SLT retains all its intellectual property rights in any information contained in e-mail messages (or any attachments thereto) which relates to the official business of myGUS / SLT or of any of its associates. Such information may be legally privileged, is to be treated as confidential and myGUS / SLT will take legal steps against any unauthorised use. myGUS / SLT does not take any responsibility for, or endorses any information which does not relate to its official business, including personal mail and/or opinions by senders who may or may not be employed by myGUS / SLT. In the event that you receive a message not intended for you, we request that you notify the sender immediately, do not read, disclose or use the content in any way whatsoever and destroy/delete the message immediately. While myGUS / SLT will take reasonable precautions, it cannot ensure that this e-mail will be free of errors, viruses, interception or interference therewith. myGUS / SLT does not, therefore, issue any guarantees or warranties in this regard and cannot be held liable for any loss or damages incurred by the recipient which have been caused by any of the above-mentioned factors.
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
Don't forget to cc: the list. James Dey wrote: > Hi Richard, > > Firstly, thanks a million for the reply. > > To answer your questions: > 1. Are you limited by CPU, memory or disk i/o? > I am not limited, but would like to get the most out of the config I have in > order to be able to know what I'll get, when I scale up. But you said: "During the day, the system is incredibly slow to a point where it is unusable". So presumably one or more of cpu,memory or disk i/o is the problem. > 2. Are you happy your config settings are good? How do you know? > I'm not, and would appreciate any help with these. If you have a look here, there is an introduction for 7.4 http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php For 8.x you might find the following more useful. http://www.powerpostgresql.com/PerfList > 3. Are there particular queries that are causing the problem, or lock > contention? > Not that I can see What is the balance between activity on Apache/PHP/PostgreSQL? -- Richard Huxton Archonet Ltd
Sorry about that James Dey tel +27 11 704-1945 cell +27 82 785-5102 fax +27 11 388-8907 mail james@mygus.com -----Original Message----- From: Richard Huxton [mailto:dev@archonet.com] Sent: 10 February 2006 11:50 AM To: James Dey Cc: 'Postgresql Performance' Subject: Re: [PERFORM] Basic Database Performance Don't forget to cc: the list. James Dey wrote: > Hi Richard, > > Firstly, thanks a million for the reply. > > To answer your questions: > 1. Are you limited by CPU, memory or disk i/o? > I am not limited, but would like to get the most out of the config I have in > order to be able to know what I'll get, when I scale up. But you said: "During the day, the system is incredibly slow to a point where it is unusable". So presumably one or more of cpu,memory or disk i/o is the problem. > 2. Are you happy your config settings are good? How do you know? > I'm not, and would appreciate any help with these. If you have a look here, there is an introduction for 7.4 http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php For 8.x you might find the following more useful. http://www.powerpostgresql.com/PerfList > 3. Are there particular queries that are causing the problem, or lock > contention? > Not that I can see What is the balance between activity on Apache/PHP/PostgreSQL? -- Richard Huxton Archonet Ltd
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
> We are running a prototype of a system running on
> PHP/Postgresql on an Intel Xeon 2ghz server, 1GB RAM, 40GB hard drive,
I think this is a decent server...
Now, I guess you are using Apache and PHP like everyone.
Know these facts :
- A client connection means an apache process (think HTTP 1.1
Keep-Alives...)
- The PHP interpreter in mod_php will be active during all the time it
takes to receive the request, parse it, generate the dynamic page, and
send it to the client to the last byte (because it is sent streaming). So,
a php page that might take 10 ms to generate will actually hog an
interpreter for between 200 ms and 1 second, depending on client ping time
and other network latency figures.
- This is actually on-topic for this list, because it will also hog a
postgres connection and server process during all that time. Thus, it will
most probably be slow and unscalable.
The solutions I use are simple :
First, use lighttpd instead of apache. Not only is it simpler to use and
configure, it uses a lot less RAM and resources, is faster, lighter, etc.
It uses an asynchronous model. It's there on my server, a crap Celeron,
pushing about 100 hits/s, and it sits at 4% CPU and 18 megabytes of RAM in
the top. It's impossible to overload this thing unless you benchmark it on
gigabit lan, with 100 bytes files.
Then, plug php in, using the fast-cgi protocol. Basically php spawns a
process pool, and you chose the size of this pool. Say you spawn 20 PHP
interpreters for instance.
When a PHP page is requested, lighttpd asks the process pool to generate
it. Then, a PHP interpreter from the pool does the job, and hands the page
over to lighttpd. This is very fast. lighttpd handles the slow
transmission of the data to the client, while the PHP interpreter goes
back to the pool to service another request.
This gives you database connection pooling for free, actually. The
connections are limited to the number of processes in the pool, so you
won't get hundreds of them all over the place. You can use php's
persistent connections without worries. You don't need to configure a
connection pool. It just works (TM).
Also you might want to use eaccelerator on your PHP. It precompiles your
PHP pages, so you don't lose time on parsing. Page time on my site went
from 50-200 ms to 5-20 ms just by installing this. It's free.
Try this and you might realize that after all, postgres was fast enough !