FAQ
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.

Search Discussions

  • John Dunlap at Apr 22, 2014 at 2:12 pm
    I'd be interested in hearing about this too.

    On Tue, Apr 22, 2014 at 10:02 AM, jbiskofski 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.
  • Fred Moyer at Apr 22, 2014 at 4:16 pm
    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" 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.
  • Perrin Harkins at Apr 22, 2014 at 4:18 pm
    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 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.
  • John Dunlap at Apr 22, 2014 at 4:23 pm
    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 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 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.
  • Perrin Harkins at Apr 22, 2014 at 4:31 pm
    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 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 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 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.
  • John Dunlap at Apr 22, 2014 at 4:33 pm
    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 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 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 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 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.
  • Perrin Harkins at Apr 22, 2014 at 4:36 pm
    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 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 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 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 <pharkins@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 <jbiskofski@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.
  • John Dunlap at Apr 22, 2014 at 6:02 pm
    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 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 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 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 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 <pharkins@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 <jbiskofski@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.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupmodperl @
categoriesmodperl, perl
postedApr 22, '14 at 2:03p
activeApr 22, '14 at 6:02p
posts9
users4
websiteperl.apache.org

People

Translate

site design / logo © 2018 Grokbase