The last two weeks I have been working on a .NET application using MySQL (3.21) for data storage. The application is basically a stand-alone PC application where MySql is used to store a lot of data from test equipment.
This is a bit different from what I spend most of my time on. No web site with lost of concurrent session, but instead a lot of installations of the software on local PC's. The also means we can not tune parameters on a live system, we have to set them before delivering to customers. Basically this is the main problem, since we don't know how the system will be run.
Well, if the scenario is different, the most important action was the same. Changing the field order in one of the indexes to match the queries better was our number one action, and the impact was larger than all other actions together. If your indexes are not righ, you should fix it before anything else.
An upgrade to MySql 5.41 and .net connector 5.0.7 was another obviuos task. I am a bit surprised that it was so easy. The only real problem we had was with merge tables. The initially did not work, but after we made a ALTER TABLE on all underlying tables even the merge tables were OK.
Performance improvement after upgrading was not large, but when we switched shared memory on, there was a noticeable performance improvement. The application transfers a lot of data between MySql to the applcation, mainly to produce graphs, so this should be important. We also switched both key cache and query cache on, but since we do not know how the application will be used, it is hard to determine the effect of this.
We had some problems with connections not being closed initially. But turning off connection pooling fixed this. Turning it on from the start was simply a reflex of an old web programmer, I'm afraid. Connecion pooling is not relevant in this scenario.
All in all a quite successful project. Performance is OK now, and the bottleneck is not the database, so we will look at some other changes, mainy in the graph package.
No comments:
Post a Comment