Trouble shooting slow queries on MySQL 5 with CFMX - Solved
Figuring if I optimised this I would cut a good 15 seconds from the page time. After running the same queries directly on mysql I narrowed down that it was not a MySQL problem.
This left the JDBC driver which was being used - after some investigation I found the following Adobe Technote.
The driver in use was the 3.1.13 one which was reported to have some performance issues compared to the 3.1.10 one recommended in the technote.
However, I'm not one to go backwards without good reason so I've installed the 5.0.4 driver using the same instructions.
So far it has worked out great - the queries in question dropped from ~800ms to ~20ms. That's a huge increase in performance. If I come across any issues I'll post them here but so far so good.
Love to hear any positive or negative experiences with these drivers.
I am currently trying to get CFMX 6.1 to talk to MySQL 5.0.27 on Redhat 3.0. I was able to get it working on our development environment using SuSE, but not on our live servers.
We have been using MySQL 4.1.15 with JDBC 5.0.4 for awhile now with not problems, as long as we use this connections string
jdbc:mysql://xxx.xxx.xxx.xxx:3306/mydb?noDatetimeStringSync=true&zeroDateTimeBehavior=convertToNull&dumpMetadataOnColumnNotFound=true&jdbcCompliantTruncation=false&autoReconnect=true
But as soon as I try switching to 5.0.27, CF gives me the following message (I tried this with and without a password and using an ip and the % wildcard in mysql.users):
Attempted reconnect 3 times. Giving up.
Server connection failure during transaction. Due to underlying exception: 'java.sql.SQLException: Access denied for user 'anybodycf'@'10.236.41.133' (using password: NO)'.
** BEGIN NESTED EXCEPTION **
java.sql.SQLException
MESSAGE: Access denied for user 'anybodycf'@'xxx.xxx.xxx.xxx' (using password: NO)
STACKTRACE:
java.sql.SQLException: Access denied for user 'anybodycf'@'xxx.xxx.xxx.xxx' (using password: NO)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2870)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:812)
at com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:3269)
at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1182)
at com.mysql.jdbc.Connection.createNewIO(Connection.java:2818)
at com.mysql.jdbc.Connection.<init>(Connection.java:1531)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:266)
at coldfusion.server.j2ee.sql.pool.JDBCPool.createPhysicalConnection(JDBCPool.java:559)
at coldfusion.server.j2ee.sql.pool.ConnectionRunner$RunnableConnection.run(ConnectionRunner.java:64)
at java.lang.Thread.run(Thread.java:534)
** END NESTED EXCEPTION **
Attempted reconnect 3 times. Giving up.
CF and MysQL are running on separate boxes. I was also able to communicate to MySQL using Query Browser and Administrator from a remote location.
Is there maybe something wrong with my connection string or is it something else? I want to try testing this with perl or php, but i am too tired at the moment. Thanks for any insight you can give me.
I haven't seen a problem like this before but here are a couple of thoughts.
I have never used any options on the end of the querystring - so it typically looks like this:
jdbc:mysql://xxx.xxx.xxx.xxx:3306/mydb
Also, one other issue I came across recently is that there is a 16 character limit on the password in ColdFusion. It's not a Mysql limit just a CF admin limit. Check that your password is less than 16 characters.
Hope it helps,
Mark
It turned out CF was trying to read the databases in /usr/local/mysql5/data, when I had them installed in /var/lib/mysql. I find this weird, because other clients I used (mysql command line, Query Browser and Adminsitrator), had no problems connecting to the same exact databases. I created symbolic links to each of the databases from /usr/local/mysql5/data to /var/lib/mysql and it started working. I have a theory right now that it has to do with the /tmp/mysql.sock file, because and i change it to use /tmp/mysql5.sock and CF is trying to use the former, but i don't care testing this at the moment.
Anyway, this was 2 days ago. Today I started running into long running query problems that were timing out my scripts. This actually happened yesterday too, but they went away after I shut off query cache and innodb. I couldn't figure out what was wrong today, so I decided to revert back to an older driver mysql-connector-java-3.0.17-ga-bin.jar and now everything seems to be running ok again.
I'm going to try using the 3.1 drivers when there is less traffic on the servers tonight, and hopefully they will be fine. Then i'll try turning the other stuff back on i turned off in mysql.
I wish I had the same luck with you and the 5.0.4 drivers. BTW, what JVM and CF version are you using? I'm using 1.4.2_09-b05 and 6.1.
Thank you again. I'll try to followup later on with my findings.
Using CF 7.0.2 and whatever the default jvm is. An upgrade to 7 may well help.
Cheers,
Mark