To: "James Dey" <james@mygus.com>, pgsql-performance@postgresql.org
Subject: Re: Basic Database Performance
References: <20060210082236.4CF9D443F@ctb-mesg3.saix.net>
Message-ID: <op.s4rw5satcigqcu@apollo13>
Date: Fri, 10 Feb 2006 21:14:06 +0100
From: PFC <lists@peufeu.com>
Content-Type: text/plain; format=flowed; delsp=yes; charset=utf-8
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
In-Reply-To: <20060210082236.4CF9D443F@ctb-mesg3.saix.net>
User-Agent: Opera M2/8.51 (Linux, build 1462)

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