High performance with Modperl, Apache and MySql

[Marko Ivanic]

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?