MySQL Forums
Forum List  »  Perl

High performance with Modperl, Apache and MySql
Posted by: Marko Ivanic
Date: October 14, 2008 05:21AM

I'm using Apache, modperl and MySQL in a high performance environment with several hundred req/s.

I noticed that my connections did a lot of unnecessary "set autocommit=1". It was issued for every script executed on the apache server even though it reused the same persistent db connection. Set autocommit=1 query should only be needed for the initial connect, not for cached and reused connections.

I assumed that this is mysql’s way to do a “ping” to check of the connection is still valid/active. So I wanted to only ping the database if the connection hasn’t been used for 10 seconds, so I modified /etc/httpd/conf/startup.perl and added:
Apache::DBI->setPingTimeOut($datasource, 10);

I also set “$Apache::DBI::DEBUG = 2;” to be able to see exactly what’s going on.

After these changes. the apache error log shows “need ping: no”, followed by “already connected to…”. So I was quite satisfied.

But then I used snort on the database server and I could see that all my persistent connections still did the “set autocommit=1”. This is an unnecessary roundtrip and performance degrading both for network and database.

I’ve fixed this for my Oracle connections and it works perfectly ok. There it disabled the DBD::Oracle ping() function which used to do excessive “select 1 from dual” towards my oracle database. But in the DBD::Mysql I can’t seem to find a ping function at all! Is this implemented in the driver itself?

Is this a bug? Does anyone know how to get the mysql client to only do "set autocommit=1" for new connections?

Options: ReplyQuote

Written By
High performance with Modperl, Apache and MySql
October 14, 2008 05:21AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.