Thread

Re: "Idle in Transaction" and hung connections

Gregory S. Williamson <gsw@globexplorer.com> — 2004-04-30T08:14:38Z

Sounds like an excellent suggestion ... we'll get a copy of this release.

Do you know of any incompatabilities with postgres 7.4 ?

 We can upgrade a server to 7.5 and the JDBC, put running some servers on 7.4/7.4 JDBC might be, uhm, difficult for me to sell operations (and we have only seen this problem in runtime). We might also be able to look at certain portions of the CVS code and see what changed and make backward patches ? (forwarding a suggestion from our engineering people)

Thanks,

Greg W.

-----Original Message-----
From:	Kris Jurka [mailto:books@ejurka.com]
Sent:	Thu 4/29/2004 7:27 PM
To:	Gregory S. Williamson
Cc:	Tom Lane; pgsql-general@postgresql.org
Subject:	Re: [GENERAL] "Idle in Transaction" and hung connections 

On Thu, 29 Apr 2004, Gregory S. Williamson wrote:

> Tom --
> 
> Thanks for the suggestion, and the rapid response on something which may
> not be truely a postgres issue (perhaps more a JDBC thing)!
> 

This behavior is fixed in the 7.5 cvs version of the JDBC driver if you'd
like to try it out.

Kris Jurka

Re: "Idle in Transaction" and hung connections

Kris Jurka <books@ejurka.com> — 2004-04-30T17:07:28Z

On Fri, 30 Apr 2004, Gregory S. Williamson wrote:

> 
> Sounds like an excellent suggestion ... we'll get a copy of this release.
> 
> Do you know of any incompatabilities with postgres 7.4 ?
> 
>  We can upgrade a server to 7.5 and the JDBC, put running some servers
> on 7.4/7.4 JDBC might be, uhm, difficult for me to sell operations (and
> we have only seen this problem in runtime). We might also be able to
> look at certain portions of the CVS code and see what changed and make
> backward patches ? (forwarding a suggestion from our engineering people)
> 

You don't need to upgrade a server to use the latest JDBC driver, it has 
support going back to at least 7.1.  I've produced a patch and set of 
binary drivers for the 7.4 series available here:

http://www.ejurka.com/pgsql/jars/transaction_state/

This code has not gotten a whole lot of testing so I'd be careful just 
dropping it into a production situation.

Kris Jurka

Load Balancing and Backup

CS Wagner <wagnercs@musc.edu> — 2004-04-30T17:17:16Z

I assume this issue has been solved repeatedly, but I cannot find any 
information on it...

I have a website that runs reports on data from a postgres database.  An 
average report takes about 10 minutes.  I'd like to load balance it so I 
can have multiple people run reports without causing the login request 
to take forever.  Also, it is rather important that data updated in the 
database be updated on all mirrors of it immediately - not with an 
hourly or daily update.

What I would most desire is a program that pretends to be a postgres 
server.  I can log into it with psql, a jdbc driver, or php's 
pg_connect.  When I do a select (no update) command, it will send that 
off to the least loaded mirror.  When I do an update/insert/delete, it 
hits all the mirrors.  Also, it can designate one database (most likely 
local to this pretend server) as the master so I can easily clone it to 
make more mirrors.

Does anything remotely similar to that exist?

-Chris

Re: Load Balancing and Backup

Dardo D Kleiner - CONTRACTOR <dkleiner@cmf.nrl.navy.mil> — 2004-05-05T16:40:26Z
```
http://c-jdbc.objectweb.org/
```

Re: Load Balancing and Backup

Steve Atkins <steve@blighty.com> — 2004-05-05T17:06:02Z

On Fri, Apr 30, 2004 at 01:17:16PM -0400, CS Wagner wrote:

> I assume this issue has been solved repeatedly, but I cannot find any 
> information on it...
> 
> I have a website that runs reports on data from a postgres database.  An 
> average report takes about 10 minutes.  I'd like to load balance it so I 
> can have multiple people run reports without causing the login request 
> to take forever.  Also, it is rather important that data updated in the 
> database be updated on all mirrors of it immediately - not with an 
> hourly or daily update.
> 
> What I would most desire is a program that pretends to be a postgres 
> server.  I can log into it with psql, a jdbc driver, or php's 
> pg_connect.  When I do a select (no update) command, it will send that 
> off to the least loaded mirror.  When I do an update/insert/delete, it 
> hits all the mirrors.  Also, it can designate one database (most likely 
> local to this pretend server) as the master so I can easily clone it to 
> make more mirrors.
> 
> Does anything remotely similar to that exist?

Sounds like dbbalancer, but I've no idea whether that's ready for
production use. I supect not.

http://sqlrelay.sourceforge.net/sqlrelay/ may be worth a look for the
"select" side of things. It's high performance and lightweight, and
supports most database access APIs.

To keep the databases in sync you'll need some form of replication to
keep the data consistent on all the mirrors. There are several
master-slave replication solutions for postgresql. Slony is one I've
seen mentioned a lot recently. If you're careful about the update
queries you make you could also replicate at the application level,
which has some different tradeoffs compared to master-slave database
replication.

Cheers,
  Steve

Re: Load Balancing and Backup

scott.marlowe <scott.marlowe@ihs.com> — 2004-05-05T17:58:09Z

On Fri, 30 Apr 2004, CS Wagner wrote:

> I assume this issue has been solved repeatedly, but I cannot find any 
> information on it...
> 
> I have a website that runs reports on data from a postgres database.  An 
> average report takes about 10 minutes.  I'd like to load balance it so I 
> can have multiple people run reports without causing the login request 
> to take forever.  Also, it is rather important that data updated in the 
> database be updated on all mirrors of it immediately - not with an 
> hourly or daily update.
> 
> What I would most desire is a program that pretends to be a postgres 
> server.  I can log into it with psql, a jdbc driver, or php's 
> pg_connect.  When I do a select (no update) command, it will send that 
> off to the least loaded mirror.  When I do an update/insert/delete, it 
> hits all the mirrors.  Also, it can designate one database (most likely 
> local to this pretend server) as the master so I can easily clone it to 
> make more mirrors.
> 
> Does anything remotely similar to that exist?

http://sqlrelay.sourceforge.net/

Re: Load Balancing and Backup

Richard Huxton <dev@archonet.com> — 2004-05-05T20:41:33Z

Steve Atkins wrote:
> On Fri, Apr 30, 2004 at 01:17:16PM -0400, CS Wagner wrote:
>>
>>What I would most desire is a program that pretends to be a postgres 
>>server.  I can log into it with psql, a jdbc driver, or php's 
>>pg_connect.  When I do a select (no update) command, it will send that 
>>off to the least loaded mirror.  When I do an update/insert/delete, it 
>>hits all the mirrors.  Also, it can designate one database (most likely 
>>local to this pretend server) as the master so I can easily clone it to 
>>make more mirrors.
>>
>>Does anything remotely similar to that exist?
> 
> 
> Sounds like dbbalancer, but I've no idea whether that's ready for
> production use. I supect not.

Possibly pgpool too. See the mailing-list archives for pg-announce (last 
month) for the details.

-- 
   Richard Huxton
   Archonet Ltd

Re: Load Balancing and Backup

John Sidney-Woollett <johnsw@wardbrook.com> — 2004-05-05T21:50:16Z

Dardo D Kleiner - CONTRACTOR said:
> http://c-jdbc.objectweb.org/

If you're not using java, this may be an interesting alternative solution
which was recently announced (on this list). It seems to meet quite a few
of your requirements.

John Sidney-Woollett

<quote>
pgpool 1.0, yet another open source replication software for
PostgreSQL is now available at:

ftp://ftp.sra.co.jp/pub/cmd/postgres/pgpool/pgpool-1.0.tar.gz

pgpool is a single master/query based/synchronous replication
server. It acts as a proxy server between PostgreSQL client and
PostgreSQL server. No application change is needed to use pgpool.
pgpool's features include:

o connection pooling. This will reduce the connection establishing
  overhead.

o pre-forking child processes. Like Apache, pgpool pre-forks child
  processes to provide faster service startup.

o degeneration. In the replication mode, if one of PostgreSQL goes
  down, it detaches the broken server and continues operation with the
  surviving server.

o fail over. In the connection pool server mode, if master PostgreSQL
  goes down, it detaches the broken server and continues operation
  with the stand-by server.

pgpool can work with PostgreSQL 7.0 to 7.4. It's small and easy to
install. All you need is gcc. You even do not need PostgreSQL source
tree.

Enjoy,
--
Tatsuo Ishii

</quote>