You are viewing a plain text version of this content. The canonical link for it is here.
Posted to modperl@perl.apache.org by jbiskofski <jb...@gmail.com> on 2014/04/22 16:02:54 UTC
Apache::DBI and Pgbouncer
I just want to confirm something with all you smart folks.
I recently separated my web servers from my database servers, before I was
using Apache::DBI to maintain persistent connections between Apache and
Postgres. With this new setup I had to install PgBouncer. Can I now safely
remove Apache::DBI from my application and use regular DBI ??
Thank you.
Re: Apache::DBI and Pgbouncer
Posted by John Dunlap <jo...@lariat.co>.
I'd be interested in hearing about this too.
On Tue, Apr 22, 2014 at 10:02 AM, jbiskofski <jb...@gmail.com> wrote:
> I just want to confirm something with all you smart folks.
>
> I recently separated my web servers from my database servers, before I was
> using Apache::DBI to maintain persistent connections between Apache and
> Postgres. With this new setup I had to install PgBouncer. Can I now safely
> remove Apache::DBI from my application and use regular DBI ??
>
> Thank you.
>
>
Re: Apache::DBI and Pgbouncer
Posted by Fred Moyer <fr...@redhotpenguin.com>.
Apache::DBI caches database connection per process so you avoid the cost of
creating a connection on each requests.
Pgbouncer pools database connections so that you don't tie up one
postmaster process per httpd process.
If you only have one webserver you may not have a real need for pgbouncer;
it really is most useful when you have more httpd processes than you have
postmaster processes. However you should be in the best position to grow
using both.
On Apr 22, 2014 7:03 AM, "jbiskofski" <jb...@gmail.com> wrote:
> I just want to confirm something with all you smart folks.
>
> I recently separated my web servers from my database servers, before I was
> using Apache::DBI to maintain persistent connections between Apache and
> Postgres. With this new setup I had to install PgBouncer. Can I now safely
> remove Apache::DBI from my application and use regular DBI ??
>
> Thank you.
>
>
Re: Apache::DBI and Pgbouncer
Posted by John Dunlap <jo...@lariat.co>.
These aren't formal benchmarks but, having just tried it on one of our
development systems, I can tell you that Apache2::DBI without pgbouncer is
slower than using pgbouncer without Apache2::DBI. Although, using both
seems to be marginally faster than either.
On Tue, Apr 22, 2014 at 12:36 PM, Perrin Harkins <ph...@gmail.com> wrote:
> Apache::DBI overrides DBI's connect() method so that you're using
> persistent connections when you use DBI directly. It may be that your
> performance improvement came from better management of Pg resources
> with PgBouncer than from reducing connection overhead. You could test
> it be removing Apache::DBI and benchmarking.
>
> - Perrin
>
> On Tue, Apr 22, 2014 at 12:33 PM, John Dunlap <jo...@lariat.co> wrote:
> > use Apache::DBI (); appears in our startup.pl but the application code
> uses
> > DBI directly.
> >
> >
> > On Tue, Apr 22, 2014 at 12:30 PM, Perrin Harkins <ph...@gmail.com>
> wrote:
> >>
> >> Thanks John. Were you using Apache::DBI before PgBouncer?
> >> Apache::DBI would also eliminate the overhead of establishing new
> >> connections.
> >>
> >> - Perrin
> >>
> >> On Tue, Apr 22, 2014 at 12:23 PM, John Dunlap <jo...@lariat.co> wrote:
> >> > I can speak to your final point. I recently deployed PGBouncer into
> our
> >> > production environment and, like the OP, we have separate web and
> >> > database
> >> > servers. With PGBouncer running on the web server(you could also run
> it
> >> > on
> >> > the database server if you wanted to) we noticed a dramatic increase
> in
> >> > performance. I haven't looked into it in detail but my best guess is
> >> > that
> >> > running the pool on the web server eliminates the overhead of
> >> > establishing
> >> > new connections(DNS lookups, establishing TCP connections,
> >> > authentication,
> >> > waiting for the database to spool up a new process, etc).
> >> >
> >> >
> >> > On Tue, Apr 22, 2014 at 12:18 PM, Perrin Harkins <ph...@gmail.com>
> >> > wrote:
> >> >>
> >> >> Interesting. Why did you have to install PgBouncer? Can't Postgres
> >> >> handle remote connections from your web server?
> >> >>
> >> >> I don't use Postgres, but reading the description of PgBouncer I can
> >> >> see some things you'd want to consider.
> >> >>
> >> >> First, Apache::DBI prevents you from making persistent connections
> >> >> before the parent process forks. If you don't use it, you should
> >> >> check your code to make sure that it closes any handles it opens
> >> >> during server startup.
> >> >>
> >> >> Second, there's the issue of what happens when your code throws an
> >> >> exception. Apache::DBI will issue a rollback on any active handles
> >> >> that aren't in autocommit mode after each request. If you don't use
> >> >> it, I'd suggest adding your own cleanup handler to do a rollback.
> >> >>
> >> >> Finally, there's the issue of performance. It's not clear whether
> DBI
> >> >> connects faster when using PgBouncer. You should probably benchmark
> >> >> that yourself. You may still get a significant speed boost from
> >> >> caching the connections (with Apache::DBI) on the client side.
> >> >>
> >> >> - Perrin
> >> >>
> >> >> On Tue, Apr 22, 2014 at 10:02 AM, jbiskofski <jb...@gmail.com>
> >> >> wrote:
> >> >> > I just want to confirm something with all you smart folks.
> >> >> >
> >> >> > I recently separated my web servers from my database servers,
> before
> >> >> > I
> >> >> > was
> >> >> > using Apache::DBI to maintain persistent connections between Apache
> >> >> > and
> >> >> > Postgres. With this new setup I had to install PgBouncer. Can I now
> >> >> > safely
> >> >> > remove Apache::DBI from my application and use regular DBI ??
> >> >> >
> >> >> > Thank you.
> >> >> >
> >> >
> >> >
> >
> >
>
Re: Apache::DBI and Pgbouncer
Posted by Perrin Harkins <ph...@gmail.com>.
Apache::DBI overrides DBI's connect() method so that you're using
persistent connections when you use DBI directly. It may be that your
performance improvement came from better management of Pg resources
with PgBouncer than from reducing connection overhead. You could test
it be removing Apache::DBI and benchmarking.
- Perrin
On Tue, Apr 22, 2014 at 12:33 PM, John Dunlap <jo...@lariat.co> wrote:
> use Apache::DBI (); appears in our startup.pl but the application code uses
> DBI directly.
>
>
> On Tue, Apr 22, 2014 at 12:30 PM, Perrin Harkins <ph...@gmail.com> wrote:
>>
>> Thanks John. Were you using Apache::DBI before PgBouncer?
>> Apache::DBI would also eliminate the overhead of establishing new
>> connections.
>>
>> - Perrin
>>
>> On Tue, Apr 22, 2014 at 12:23 PM, John Dunlap <jo...@lariat.co> wrote:
>> > I can speak to your final point. I recently deployed PGBouncer into our
>> > production environment and, like the OP, we have separate web and
>> > database
>> > servers. With PGBouncer running on the web server(you could also run it
>> > on
>> > the database server if you wanted to) we noticed a dramatic increase in
>> > performance. I haven't looked into it in detail but my best guess is
>> > that
>> > running the pool on the web server eliminates the overhead of
>> > establishing
>> > new connections(DNS lookups, establishing TCP connections,
>> > authentication,
>> > waiting for the database to spool up a new process, etc).
>> >
>> >
>> > On Tue, Apr 22, 2014 at 12:18 PM, Perrin Harkins <ph...@gmail.com>
>> > wrote:
>> >>
>> >> Interesting. Why did you have to install PgBouncer? Can't Postgres
>> >> handle remote connections from your web server?
>> >>
>> >> I don't use Postgres, but reading the description of PgBouncer I can
>> >> see some things you'd want to consider.
>> >>
>> >> First, Apache::DBI prevents you from making persistent connections
>> >> before the parent process forks. If you don't use it, you should
>> >> check your code to make sure that it closes any handles it opens
>> >> during server startup.
>> >>
>> >> Second, there's the issue of what happens when your code throws an
>> >> exception. Apache::DBI will issue a rollback on any active handles
>> >> that aren't in autocommit mode after each request. If you don't use
>> >> it, I'd suggest adding your own cleanup handler to do a rollback.
>> >>
>> >> Finally, there's the issue of performance. It's not clear whether DBI
>> >> connects faster when using PgBouncer. You should probably benchmark
>> >> that yourself. You may still get a significant speed boost from
>> >> caching the connections (with Apache::DBI) on the client side.
>> >>
>> >> - Perrin
>> >>
>> >> On Tue, Apr 22, 2014 at 10:02 AM, jbiskofski <jb...@gmail.com>
>> >> wrote:
>> >> > I just want to confirm something with all you smart folks.
>> >> >
>> >> > I recently separated my web servers from my database servers, before
>> >> > I
>> >> > was
>> >> > using Apache::DBI to maintain persistent connections between Apache
>> >> > and
>> >> > Postgres. With this new setup I had to install PgBouncer. Can I now
>> >> > safely
>> >> > remove Apache::DBI from my application and use regular DBI ??
>> >> >
>> >> > Thank you.
>> >> >
>> >
>> >
>
>
Re: Apache::DBI and Pgbouncer
Posted by John Dunlap <jo...@lariat.co>.
use Apache::DBI (); appears in our startup.pl but the application code uses
DBI directly.
On Tue, Apr 22, 2014 at 12:30 PM, Perrin Harkins <ph...@gmail.com> wrote:
> Thanks John. Were you using Apache::DBI before PgBouncer?
> Apache::DBI would also eliminate the overhead of establishing new
> connections.
>
> - Perrin
>
> On Tue, Apr 22, 2014 at 12:23 PM, John Dunlap <jo...@lariat.co> wrote:
> > I can speak to your final point. I recently deployed PGBouncer into our
> > production environment and, like the OP, we have separate web and
> database
> > servers. With PGBouncer running on the web server(you could also run it
> on
> > the database server if you wanted to) we noticed a dramatic increase in
> > performance. I haven't looked into it in detail but my best guess is that
> > running the pool on the web server eliminates the overhead of
> establishing
> > new connections(DNS lookups, establishing TCP connections,
> authentication,
> > waiting for the database to spool up a new process, etc).
> >
> >
> > On Tue, Apr 22, 2014 at 12:18 PM, Perrin Harkins <ph...@gmail.com>
> wrote:
> >>
> >> Interesting. Why did you have to install PgBouncer? Can't Postgres
> >> handle remote connections from your web server?
> >>
> >> I don't use Postgres, but reading the description of PgBouncer I can
> >> see some things you'd want to consider.
> >>
> >> First, Apache::DBI prevents you from making persistent connections
> >> before the parent process forks. If you don't use it, you should
> >> check your code to make sure that it closes any handles it opens
> >> during server startup.
> >>
> >> Second, there's the issue of what happens when your code throws an
> >> exception. Apache::DBI will issue a rollback on any active handles
> >> that aren't in autocommit mode after each request. If you don't use
> >> it, I'd suggest adding your own cleanup handler to do a rollback.
> >>
> >> Finally, there's the issue of performance. It's not clear whether DBI
> >> connects faster when using PgBouncer. You should probably benchmark
> >> that yourself. You may still get a significant speed boost from
> >> caching the connections (with Apache::DBI) on the client side.
> >>
> >> - Perrin
> >>
> >> On Tue, Apr 22, 2014 at 10:02 AM, jbiskofski <jb...@gmail.com>
> wrote:
> >> > I just want to confirm something with all you smart folks.
> >> >
> >> > I recently separated my web servers from my database servers, before I
> >> > was
> >> > using Apache::DBI to maintain persistent connections between Apache
> and
> >> > Postgres. With this new setup I had to install PgBouncer. Can I now
> >> > safely
> >> > remove Apache::DBI from my application and use regular DBI ??
> >> >
> >> > Thank you.
> >> >
> >
> >
>
Re: Apache::DBI and Pgbouncer
Posted by Perrin Harkins <ph...@gmail.com>.
Thanks John. Were you using Apache::DBI before PgBouncer?
Apache::DBI would also eliminate the overhead of establishing new
connections.
- Perrin
On Tue, Apr 22, 2014 at 12:23 PM, John Dunlap <jo...@lariat.co> wrote:
> I can speak to your final point. I recently deployed PGBouncer into our
> production environment and, like the OP, we have separate web and database
> servers. With PGBouncer running on the web server(you could also run it on
> the database server if you wanted to) we noticed a dramatic increase in
> performance. I haven't looked into it in detail but my best guess is that
> running the pool on the web server eliminates the overhead of establishing
> new connections(DNS lookups, establishing TCP connections, authentication,
> waiting for the database to spool up a new process, etc).
>
>
> On Tue, Apr 22, 2014 at 12:18 PM, Perrin Harkins <ph...@gmail.com> wrote:
>>
>> Interesting. Why did you have to install PgBouncer? Can't Postgres
>> handle remote connections from your web server?
>>
>> I don't use Postgres, but reading the description of PgBouncer I can
>> see some things you'd want to consider.
>>
>> First, Apache::DBI prevents you from making persistent connections
>> before the parent process forks. If you don't use it, you should
>> check your code to make sure that it closes any handles it opens
>> during server startup.
>>
>> Second, there's the issue of what happens when your code throws an
>> exception. Apache::DBI will issue a rollback on any active handles
>> that aren't in autocommit mode after each request. If you don't use
>> it, I'd suggest adding your own cleanup handler to do a rollback.
>>
>> Finally, there's the issue of performance. It's not clear whether DBI
>> connects faster when using PgBouncer. You should probably benchmark
>> that yourself. You may still get a significant speed boost from
>> caching the connections (with Apache::DBI) on the client side.
>>
>> - Perrin
>>
>> On Tue, Apr 22, 2014 at 10:02 AM, jbiskofski <jb...@gmail.com> wrote:
>> > I just want to confirm something with all you smart folks.
>> >
>> > I recently separated my web servers from my database servers, before I
>> > was
>> > using Apache::DBI to maintain persistent connections between Apache and
>> > Postgres. With this new setup I had to install PgBouncer. Can I now
>> > safely
>> > remove Apache::DBI from my application and use regular DBI ??
>> >
>> > Thank you.
>> >
>
>
Re: Apache::DBI and Pgbouncer
Posted by John Dunlap <jo...@lariat.co>.
I can speak to your final point. I recently deployed PGBouncer into our
production environment and, like the OP, we have separate web and database
servers. With PGBouncer running on the web server(you could also run it on
the database server if you wanted to) we noticed a dramatic increase in
performance. I haven't looked into it in detail but my best guess is that
running the pool on the web server eliminates the overhead of establishing
new connections(DNS lookups, establishing TCP connections, authentication,
waiting for the database to spool up a new process, etc).
On Tue, Apr 22, 2014 at 12:18 PM, Perrin Harkins <ph...@gmail.com> wrote:
> Interesting. Why did you have to install PgBouncer? Can't Postgres
> handle remote connections from your web server?
>
> I don't use Postgres, but reading the description of PgBouncer I can
> see some things you'd want to consider.
>
> First, Apache::DBI prevents you from making persistent connections
> before the parent process forks. If you don't use it, you should
> check your code to make sure that it closes any handles it opens
> during server startup.
>
> Second, there's the issue of what happens when your code throws an
> exception. Apache::DBI will issue a rollback on any active handles
> that aren't in autocommit mode after each request. If you don't use
> it, I'd suggest adding your own cleanup handler to do a rollback.
>
> Finally, there's the issue of performance. It's not clear whether DBI
> connects faster when using PgBouncer. You should probably benchmark
> that yourself. You may still get a significant speed boost from
> caching the connections (with Apache::DBI) on the client side.
>
> - Perrin
>
> On Tue, Apr 22, 2014 at 10:02 AM, jbiskofski <jb...@gmail.com> wrote:
> > I just want to confirm something with all you smart folks.
> >
> > I recently separated my web servers from my database servers, before I
> was
> > using Apache::DBI to maintain persistent connections between Apache and
> > Postgres. With this new setup I had to install PgBouncer. Can I now
> safely
> > remove Apache::DBI from my application and use regular DBI ??
> >
> > Thank you.
> >
>
Re: Apache::DBI and Pgbouncer
Posted by Perrin Harkins <ph...@gmail.com>.
Interesting. Why did you have to install PgBouncer? Can't Postgres
handle remote connections from your web server?
I don't use Postgres, but reading the description of PgBouncer I can
see some things you'd want to consider.
First, Apache::DBI prevents you from making persistent connections
before the parent process forks. If you don't use it, you should
check your code to make sure that it closes any handles it opens
during server startup.
Second, there's the issue of what happens when your code throws an
exception. Apache::DBI will issue a rollback on any active handles
that aren't in autocommit mode after each request. If you don't use
it, I'd suggest adding your own cleanup handler to do a rollback.
Finally, there's the issue of performance. It's not clear whether DBI
connects faster when using PgBouncer. You should probably benchmark
that yourself. You may still get a significant speed boost from
caching the connections (with Apache::DBI) on the client side.
- Perrin
On Tue, Apr 22, 2014 at 10:02 AM, jbiskofski <jb...@gmail.com> wrote:
> I just want to confirm something with all you smart folks.
>
> I recently separated my web servers from my database servers, before I was
> using Apache::DBI to maintain persistent connections between Apache and
> Postgres. With this new setup I had to install PgBouncer. Can I now safely
> remove Apache::DBI from my application and use regular DBI ??
>
> Thank you.
>