“Show Full Columns” problem with CFMX and MySQL solved


We’ve been performing some load testing on a new website we’ve developed and our helpful sysadmin noticed lots of queries happening on the DB that looked like this:


SHOW FULL COLUMNS FROM `dbname`.`tablename`

We weren’t intentionally doing these queries but they were coming from somewhere. A bit of detective work via google found a very enlightening article about this problem.

A quick read of this explained:

It turns out ColdFusion was asking Connector/J for the metadata on every field, which in turn triggered a SHOW FULL COLUMNS query for every varchar and text column returned.

It also went on to say that is had been fixed as of Connector/J 5.07.

On reading the release notes it mentions that the

Driver detects when it is running in a ColdFusion MX server (tested with version 7), and uses the configuration bundle coldFusion, which sets useDynamicCharsetInfo to false (see previous entry), and sets useLocalSessionState and autoReconnect to true.

However, from my testing it wasn’t doing it as we were still seeing the problem. However, we are running the MultiServer version of ColdFusion which sits atop Jrun4.

So I added the parameter directly to the querystring:

&useDynamicCharsetInfo=false

So it now looks like:

jdbc:mysql://127.0.0.1:3306/mydbname?allowMultiQueries=true&useDynamicCharsetInfo=false

This has stopped the problem, and given a significant performance gain. On one of the sites I was load testing it gave a 12% throughput increase and on another one which had less queries it gave a 4% increase.

Hope it helps.

Cheers,
Mark

Related Content


This post was posted in , , , , , , by on