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.
[size=150]IMPORTANT: The following applies to versions EARLIER than 4.1. New versions include 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”:
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,
As always, don’t forget to index keys that you may use in your WHERE clause, such as t_stamp.