Re: Scaling postgres

On Sat, Apr 13, 2002 at 05:24:48PM -0500, Steve Lane wrote:
> On 4/13/02 5:10 PM, "Neil Conway" <nconway@klamath.dyndns.org> wrote:
>
> > On Sat, 13 Apr 2002 17:00:19 -0500
> > "Steve Lane" <slane@fmpro.com> wrote:
> >> On the front end, as I understand it (I know this is not really
> >> Postgres-specific), with Apache and mod_php I need one process per
> >> concurrent user. Anyone care to speculate how many concurrent users I could
> >> get on a single box? I really don't know what's reasonable to expect.
> >
> > For the front-end, it totally depends on the hardware you're using,
> > the OS you're running this on, and the design/performance requirements
> > of your application. For example, a good caching layer could easily
> > improve performance by 100% or more.
>
> 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.

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

> > Yes, there is 1 postgres process per database connection. Whether
> > you create 1 database connection per client would depend on how you
> > design your application.
>
> 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.

HTH,
--
Martijn van Oosterhout <kleptog@svana.org>   http://svana.org/kleptog/
> Ignorance continues to thrive when intelligent people choose to do
> nothing.  Speaking out against censorship and ignorance is the imperative
> of all intelligent people.