FactorySQL performance enhancement with MySQL 5

[size=150]IMPORTANT: The following applies to FactorySQL versions EARLIER than 4.1. FactorySQL now includes a new library that works well without modification[/size]

Since we’ve been pushing MySQL 5 lately, I wanted to post this message for the benefit of all:

Using the native driver to connect to mysql, there are 2 optional parameters that you can include that will greatly enhance performance: cacheserverconfiguration and resetpooledconnections. They can be set to true and false respectively to reduce the amount of work that the connector does.
More precisely, when configuring your connection, you can place the following in the “Extra Parameters”:


Now, the warning. As per an advisory on the MySQL .Net connector forum, these options can be a bit dangerous, and now connections are not being reset on every open. This means that any temporary tables, user variables, ect, will not be cleared, and other instances might interact with them. In the context of FactorySQL, however, this is not a concern. Additionally, when using MySQL on a large project (thousands of tags), the performance benefit can be crucial. I’ve personally seen these parameters take the average group execution time from 200+ milliseconds to a mere 7ms. CPU usage can drop from 70% to only 5%. The bulk of the benefit comes from the fact that the connector is no longer performing a “ping” and downloading all of the server variables before every query.

As always, feel free to post with any questions/comments!


IMPORTANT NEWS (about the mysql connector performance flags)

For some reason, the mysql .net connector team (guy) decided to throw backwards compatibility to the wind and totally change this…

First off, the keywords “CacheServerConfiguration” and “ResetPooledConnections” no longer exist. Instead, the connector always functions as if “cacheserverconfig” were TRUE- good for performance, bad for installs using this keyword, because now every connection will fail with an “invalid connection string error”.

Next, “ResetPooledConnections” was changed to “connection reset”. The functionality appears to be the same.

At this point, though, I want to point out that we’ve made accomodations for this in FactorySQL 3.0 so that all of our customers projects will continue to function after upgrade. I just wanted to post this so that going forward people are aware of the issue, especially if they ever try to upgrade the connector themselves from the MySQL website (this should not be common, but every so often a bug is found due to the connector and an upgrade is necessary).

Hope this helps someone,

Thanks Colby. I’ll pass this on to integrators and end users as it comes up.