FactorySQL Performance increase with MySQL 5

Archived Topics
1

Colby posted this to the Knowledge base. I’ve had several users ask about it and it’s relevant to many of our FSQL users.

here’s a link.

2

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”:

cacheserverconfiguration=true;resetpooledconnections=false;

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!

Regards,

3

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,

4

FURTHER IMPORTANT NEWS - Concerning these flags and SQLTags

As of the current release (4.0.0), it is very important that these flags ARE NOT used on the connection that is running SQLTags. Due to how the driver deallocates prepared statements, using these flags will cause the statement count to increase until it reaches the max allowed, causing further queries to fail. This will cause SQLTags to stop updating.

If you need the flags in order to maintain reasonable performance on your overall project, I suggest simply creating another “SQLTags only” connection without them.

We’ll be looking into what we can do to fix this shortly.

Regards,