Re: Scaling postgres

On 4/13/02 7:38 PM, "Martijn van Oosterhout" <kleptog(at)svana(dot)org> wrote:

>> I'm less concerned with performance (at the moment) than concurrency. My
>> worry is that (lacking a multithreaded web server, which Apache 2.0 appears
>> to give me), I need to have 800-1200 processes, one per connection, running
>> on the web server or servers. I don't know if that's feasible under any
>> circumstances.
>
> In a sense they are tied together. Say all your queries for each transaction
> are done in less than 100ms, then you can handle many more clients for the
> same load than if they took 10 times as long.
>
> Also, an idling client generally does not keep a connection open to the
> Apache server. So if you have 800 people changing webpage once a minute,
> you're really only going to be handling 15 processes at the same time.

Actually, I have a lot of trouble with this if I instruct PHP to make
persistent connections. If I do this, the idle connections just pile up and
never close. A few hours or days, and postgres maxes out on the number of
open connections. The idle ones are never reclaimed. I have heard rumors
that PHP's persistent connection to postgres has some troubles, and this
seems to be so.
>
>> I guess I'm less worried about the front end though, because load-balancing
>> across multiple web servers is not a huge deal.
>
> I find it helpful if 1) you make all your queries simple and fast and 2) if
> your query does require some trawling, cache and/or precalculate.

For the largest application I currently run, we are fairly denormalized and
push a lot of data around the back end with triggers. I imagine this is
helping to some degree.

>>
>> Can you clarify that second sentence a bit? I wasn't aware I had much choice
>> -- meaning that, since Apache 1.x + PHP is not multithreaded and does not do
>> connection pooling, I think I'm stuck with one database connection per
>> front-side client connection.
>
> Well, number of apache connections == number database connections == number
> of postgres backend generally. The connection pooling in PHP only stops it
> reconnecting for each page. But as I state above, the number of apache
> connections will probably be *far* less than the number of clients.

Ah, that's the kind of simple math I was looking for. Thanks.

-- steve