pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Re: PgPool changes WAS: PostgreSQL clustering VS MySQL clustering
8+ messages / 4 participants
[nested] [flat]

* Re: PgPool changes WAS: PostgreSQL clustering VS MySQL clustering
@ 2005-01-24 17:52 Josh Berkus <josh@agliodbs.com>
  2005-01-24 18:24 ` Re: PgPool changes WAS: PostgreSQL clustering VS MySQL Ragnar Hafstað <gnari@simnet.is>
  0 siblings, 1 reply; 8+ messages in thread

From: Josh Berkus @ 2005-01-24 17:52 UTC (permalink / raw)
  To: pgsql-performance; +Cc: Tatsuo Ishii <t-ishii@sra.co.jp>

Tatsuo,

> > Depends on your connection pooling software, I suppose.  Most connection
> > pooling software only returns connections to the pool after a user has
> > been inactive for some period ... generally more than 3 seconds.  So
> > connection continuity could be trusted.
>
> Not sure what you mean by "most connection pooling software", but I'm
> sure that pgpool behaves differently.

Ah, clarity problem here.    I'm talking about connection pooling tools from 
the client (webserver) side, such as Apache::DBI, PHP's pg_pconnect, 
Jakarta's connection pools, etc.   Not pooling on the database server side, 
which is what pgPool provides.

Most of these tools allocate a database connection to an HTTP/middleware 
client, and only release it after a specific period of inactivity.    This 
means that you *could* count on "web-user==connection" for purposes of 
switching back and forth to the master -- as long as the connection-recycling 
timeout were set higher than the pgPool switch-off period.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: PgPool changes WAS: PostgreSQL clustering VS MySQL
  2005-01-24 17:52 Re: PgPool changes WAS: PostgreSQL clustering VS MySQL clustering Josh Berkus <josh@agliodbs.com>
@ 2005-01-24 18:24 ` Ragnar Hafstað <gnari@simnet.is>
  2005-01-24 23:45   ` Re: PgPool changes WAS: PostgreSQL clustering VS MySQL Josh Berkus <josh@agliodbs.com>
  2005-01-25 00:21   ` Re: PgPool changes WAS: PostgreSQL clustering VS MySQL Tatsuo Ishii <t-ishii@sra.co.jp>
  0 siblings, 2 replies; 8+ messages in thread

From: Ragnar Hafstað @ 2005-01-24 18:24 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-performance@postgresql.org, Tatsuo Ishii <t-ishii@sra.co.jp>

On Mon, 2005-01-24 at 09:52 -0800, Josh Berkus wrote:
> [about keeping connections open in web context]
> Ah, clarity problem here.    I'm talking about connection pooling tools from 
> the client (webserver) side, such as Apache::DBI, PHP's pg_pconnect, 
> Jakarta's connection pools, etc.   Not pooling on the database server side, 
> which is what pgPool provides.

note that these sometimes do not provide connection pooling as such,
just persistent connections (Apache::DBI)

> Most of these tools allocate a database connection to an HTTP/middleware 
> client, and only release it after a specific period of inactivity.    This 
> means that you *could* count on "web-user==connection" for purposes of 
> switching back and forth to the master -- as long as the connection-recycling 
> timeout were set higher than the pgPool switch-off period.

no. you can only count on web-server-process==connection, but not
web-user==connection, unless you can garantee that the same user
client always connects to same web-server process.

am i missing something ?

gnari





^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: PgPool changes WAS: PostgreSQL clustering VS MySQL
  2005-01-24 17:52 Re: PgPool changes WAS: PostgreSQL clustering VS MySQL clustering Josh Berkus <josh@agliodbs.com>
  2005-01-24 18:24 ` Re: PgPool changes WAS: PostgreSQL clustering VS MySQL Ragnar Hafstað <gnari@simnet.is>
@ 2005-01-24 23:45   ` Josh Berkus <josh@agliodbs.com>
  2005-01-25 09:52     ` Re: PgPool changes WAS: PostgreSQL clustering VS MySQL Ragnar Hafstað <gnari@simnet.is>
  2005-01-25 16:49     ` Re: PgPool changes WAS: PostgreSQL clustering VS MySQL Peter Darley <pdarley@kinesis-cem.com>
  1 sibling, 2 replies; 8+ messages in thread

From: Josh Berkus @ 2005-01-24 23:45 UTC (permalink / raw)
  To: Ragnar Hafstað <gnari@simnet.is>; +Cc: pgsql-performance@postgresql.org, Tatsuo Ishii <t-ishii@sra.co.jp>

Ragnar,

> note that these sometimes do not provide connection pooling as such,
> just persistent connections (Apache::DBI)

Yes, right.

> no. you can only count on web-server-process==connection, but not
> web-user==connection, unless you can garantee that the same user
> client always connects to same web-server process.

Are there ones that you use which might use several different connections to 
send a series of queries from a single web-user, less than 5 seconds apart?

-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: PgPool changes WAS: PostgreSQL clustering VS MySQL
  2005-01-24 17:52 Re: PgPool changes WAS: PostgreSQL clustering VS MySQL clustering Josh Berkus <josh@agliodbs.com>
  2005-01-24 18:24 ` Re: PgPool changes WAS: PostgreSQL clustering VS MySQL Ragnar Hafstað <gnari@simnet.is>
  2005-01-24 23:45   ` Re: PgPool changes WAS: PostgreSQL clustering VS MySQL Josh Berkus <josh@agliodbs.com>
@ 2005-01-25 09:52     ` Ragnar Hafstað <gnari@simnet.is>
  1 sibling, 0 replies; 8+ messages in thread

From: Ragnar Hafstað @ 2005-01-25 09:52 UTC (permalink / raw)
  To: pgsql-performance

On Mon, 2005-01-24 at 15:45 -0800, Josh Berkus wrote:

> [about keeping open DB connections between web-client connections]

> [I wrote:]
> > no. you can only count on web-server-process==connection, but not
> > web-user==connection, unless you can garantee that the same user
> > client always connects to same web-server process.
> 
> Are there ones that you use which might use several different connections to 
> send a series of queries from a single web-user, less than 5 seconds apart?

actually, it had never occurred to me to test all browsers in this
reguard, but i can think of LWP::UserAgent.

gnari





^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: PgPool changes WAS: PostgreSQL clustering VS MySQL
  2005-01-24 17:52 Re: PgPool changes WAS: PostgreSQL clustering VS MySQL clustering Josh Berkus <josh@agliodbs.com>
  2005-01-24 18:24 ` Re: PgPool changes WAS: PostgreSQL clustering VS MySQL Ragnar Hafstað <gnari@simnet.is>
  2005-01-24 23:45   ` Re: PgPool changes WAS: PostgreSQL clustering VS MySQL Josh Berkus <josh@agliodbs.com>
@ 2005-01-25 16:49     ` Peter Darley <pdarley@kinesis-cem.com>
  2005-01-25 16:58       ` Re: PgPool changes WAS: PostgreSQL clustering VS MySQL Josh Berkus <josh@agliodbs.com>
  1 sibling, 1 reply; 8+ messages in thread

From: Peter Darley @ 2005-01-25 16:49 UTC (permalink / raw)
  To: josh@agliodbs.com; Ragnar Hafstað <gnari@simnet.is>; +Cc: pgsql-performance; Tatsuo Ishii <t-ishii@sra.co.jp>

Josh,

	Please excuse how my client quotes things...

> Are there ones that you use which might use several different connections to 
> send a series of queries from a single web-user, less than 5 seconds apart?

	Using Apache/Perl I often have a situation where we're sending several queries from the same user (web client) within seconds, or even simultaneously, that use different connections.

	When someone logs in to our system they get a frameset that has 5 windows, each of which is filled with data from queries.  Since the pages in the frames are requested separately by the client the system doesn't insure that they go to the same process, and subsequently, that they're not served by the same db connection.

	Session information is stored in the database (so it's easily persistent across server processes), so it would be bad if a request for a page was served by a db server that didn't yet have information about the user (such as that they're logged in, etc.).

	If we ever have enough traffic to warrant it, we're going to go to a load balancer that passes requests to different identical web servers, at which point we won't even be getting requests from the same machine, much less the same connection.

Thanks,
Peter Darley

-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org]On Behalf Of Josh Berkus
Sent: Monday, January 24, 2005 3:46 PM
To: Ragnar Hafstað
Cc: pgsql-performance@postgresql.org; Tatsuo Ishii
Subject: Re: [PERFORM] PgPool changes WAS: PostgreSQL clustering VS
MySQL


Ragnar,

> note that these sometimes do not provide connection pooling as such,
> just persistent connections (Apache::DBI)

Yes, right.

> no. you can only count on web-server-process==connection, but not
> web-user==connection, unless you can garantee that the same user
> client always connects to same web-server process.

Are there ones that you use which might use several different connections to 
send a series of queries from a single web-user, less than 5 seconds apart?

-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: PgPool changes WAS: PostgreSQL clustering VS MySQL
  2005-01-24 17:52 Re: PgPool changes WAS: PostgreSQL clustering VS MySQL clustering Josh Berkus <josh@agliodbs.com>
  2005-01-24 18:24 ` Re: PgPool changes WAS: PostgreSQL clustering VS MySQL Ragnar Hafstað <gnari@simnet.is>
  2005-01-24 23:45   ` Re: PgPool changes WAS: PostgreSQL clustering VS MySQL Josh Berkus <josh@agliodbs.com>
  2005-01-25 16:49     ` Re: PgPool changes WAS: PostgreSQL clustering VS MySQL Peter Darley <pdarley@kinesis-cem.com>
@ 2005-01-25 16:58       ` Josh Berkus <josh@agliodbs.com>
  2005-01-26 01:09         ` Re: PgPool changes WAS: PostgreSQL clustering VS MySQL Tatsuo Ishii <t-ishii@sra.co.jp>
  0 siblings, 1 reply; 8+ messages in thread

From: Josh Berkus @ 2005-01-25 16:58 UTC (permalink / raw)
  To: pgsql-performance; +Cc: Peter Darley <pdarley@kinesis-cem.com>; Ragnar Hafstað <gnari@simnet.is>; Tatsuo Ishii <t-ishii@sra.co.jp>

Peter, Ragnar,

> > Are there ones that you use which might use several different connections
> > to send a series of queries from a single web-user, less than 5 seconds
> > apart?
>
> 	Using Apache/Perl I often have a situation where we're sending several
> queries from the same user (web client) within seconds, or even
> simultaneously, that use different connections.

So from the sound of it, the connection methods I've been using are the 
exception rather than the rule.   Darn, it worked well for us.  :-(

What this would point to is NOT being able to use Slony-I for database server 
pooling for most web applications.   Yes?  Users should look to pgCluster and 
C-JDBC instead.

BTW, Tatsuo, what's the code relationship between pgPool and pgCluster, if 
any?

--Josh

-- 
Josh Berkus
Aglio Database Solutions
San Francisco



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: PgPool changes WAS: PostgreSQL clustering VS MySQL
  2005-01-24 17:52 Re: PgPool changes WAS: PostgreSQL clustering VS MySQL clustering Josh Berkus <josh@agliodbs.com>
  2005-01-24 18:24 ` Re: PgPool changes WAS: PostgreSQL clustering VS MySQL Ragnar Hafstað <gnari@simnet.is>
  2005-01-24 23:45   ` Re: PgPool changes WAS: PostgreSQL clustering VS MySQL Josh Berkus <josh@agliodbs.com>
  2005-01-25 16:49     ` Re: PgPool changes WAS: PostgreSQL clustering VS MySQL Peter Darley <pdarley@kinesis-cem.com>
  2005-01-25 16:58       ` Re: PgPool changes WAS: PostgreSQL clustering VS MySQL Josh Berkus <josh@agliodbs.com>
@ 2005-01-26 01:09         ` Tatsuo Ishii <t-ishii@sra.co.jp>
  0 siblings, 0 replies; 8+ messages in thread

From: Tatsuo Ishii @ 2005-01-26 01:09 UTC (permalink / raw)
  To: josh@agliodbs.com; +Cc: pgsql-performance; pdarley@kinesis-cem.com; gnari@simnet.is; mitani@sraw.co.jp

> Peter, Ragnar,
> 
> > > Are there ones that you use which might use several different connections
> > > to send a series of queries from a single web-user, less than 5 seconds
> > > apart?
> >
> > 	Using Apache/Perl I often have a situation where we're sending several
> > queries from the same user (web client) within seconds, or even
> > simultaneously, that use different connections.
> 
> So from the sound of it, the connection methods I've been using are the 
> exception rather than the rule.   Darn, it worked well for us.  :-(
> 
> What this would point to is NOT being able to use Slony-I for database server 
> pooling for most web applications.   Yes?  Users should look to pgCluster and 
> C-JDBC instead.

Yup. That's the limitaion of async replication solutions.

> BTW, Tatsuo, what's the code relationship between pgPool and pgCluster, if 
> any?

PGCluster consists of three kind of servers, "load balance server",
"cluster server"(modified PostgreSQL backend) and "replication
server". I believe some of codes of pgpool are used in the load
balance server to avoid "re-invent a wheel". This is a beauty of open
source software project.
--
Tatsuo Ishii



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: PgPool changes WAS: PostgreSQL clustering VS MySQL
  2005-01-24 17:52 Re: PgPool changes WAS: PostgreSQL clustering VS MySQL clustering Josh Berkus <josh@agliodbs.com>
  2005-01-24 18:24 ` Re: PgPool changes WAS: PostgreSQL clustering VS MySQL Ragnar Hafstað <gnari@simnet.is>
@ 2005-01-25 00:21   ` Tatsuo Ishii <t-ishii@sra.co.jp>
  1 sibling, 0 replies; 8+ messages in thread

From: Tatsuo Ishii @ 2005-01-25 00:21 UTC (permalink / raw)
  To: gnari@simnet.is; +Cc: josh@agliodbs.com; pgsql-performance

> On Mon, 2005-01-24 at 09:52 -0800, Josh Berkus wrote:
> > [about keeping connections open in web context]
> > Ah, clarity problem here.    I'm talking about connection pooling tools from 
> > the client (webserver) side, such as Apache::DBI, PHP's pg_pconnect, 
> > Jakarta's connection pools, etc.   Not pooling on the database server side, 
> > which is what pgPool provides.
> 
> note that these sometimes do not provide connection pooling as such,
> just persistent connections (Apache::DBI)

Right. Same thing can be said to pg_pconnect.

> > Most of these tools allocate a database connection to an HTTP/middleware 
> > client, and only release it after a specific period of inactivity.    This 
> > means that you *could* count on "web-user==connection" for purposes of 
> > switching back and forth to the master -- as long as the connection-recycling 
> > timeout were set higher than the pgPool switch-off period.
> 
> no. you can only count on web-server-process==connection, but not
> web-user==connection, unless you can garantee that the same user
> client always connects to same web-server process.

I have same opinion.

> am i missing something ?
--
Tatsuo Ishii



^ permalink  raw  reply  [nested|flat] 8+ messages in thread


end of thread, other threads:[~2005-01-26 01:09 UTC | newest]

Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-01-24 17:52 Re: PgPool changes WAS: PostgreSQL clustering VS MySQL clustering Josh Berkus <josh@agliodbs.com>
2005-01-24 18:24 ` Re: PgPool changes WAS: PostgreSQL clustering VS MySQL Ragnar Hafstað <gnari@simnet.is>
2005-01-24 23:45   ` Re: PgPool changes WAS: PostgreSQL clustering VS MySQL Josh Berkus <josh@agliodbs.com>
2005-01-25 09:52     ` Re: PgPool changes WAS: PostgreSQL clustering VS MySQL Ragnar Hafstað <gnari@simnet.is>
2005-01-25 16:49     ` Re: PgPool changes WAS: PostgreSQL clustering VS MySQL Peter Darley <pdarley@kinesis-cem.com>
2005-01-25 16:58       ` Re: PgPool changes WAS: PostgreSQL clustering VS MySQL Josh Berkus <josh@agliodbs.com>
2005-01-26 01:09         ` Re: PgPool changes WAS: PostgreSQL clustering VS MySQL Tatsuo Ishii <t-ishii@sra.co.jp>
2005-01-25 00:21   ` Re: PgPool changes WAS: PostgreSQL clustering VS MySQL Tatsuo Ishii <t-ishii@sra.co.jp>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox