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 <phark...@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 <j...@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 <phark...@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 <j...@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 <phark...@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 <jbiskof...@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. > >> >> > > >> > > >> > > > > > >
