High performance with Modperl, Apache and MySql
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?
Subject
Written By
Posted
High performance with Modperl, Apache and MySql
October 14, 2008 05:21AM
Sorry, you can't reply to this topic. It has been closed.
This forum is currently read only. You can not log in or make any changes. This is a temporary situation.
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.