Re: Scaling postgres

On 4/13/02 5:10 PM, "Neil Conway" <nconway(at)klamath(dot)dyndns(dot)org> wrote:

> On Sat, 13 Apr 2002 17:00:19 -0500
> "Steve Lane" <slane(at)fmpro(dot)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.

I guess I'm less worried about the front end though, because load-balancing
across multiple web servers is not a huge deal.
>
>> On the back end, is there any direct relationship between the number of open
>> client connections and the number of processes used?
>
> 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.
>
>> My question again would be, is it at all reasonable to think that
>> the postgres back end, running on a single box, could handle 800-1200
>> concurrent users?
>
> Not really sure. By "concurrent users", do you mean "executing queries
> at any given time", or "logged in" (so that perhaps 10% of those will
> actually be hitting the DB)?

More the latter -- open database connections that may be active or idle.

>> If I can't get all those users on one back-end box, how do I distribute them
>> across multiple servers but have them all access the same data store?
>
> There might be support for replication in 7.3; until then, there
> are some projects like erServer you can take a look at.

Thanks for the detailed response.

-- steve