Tuesday, June 05, 2007

MySQL performance enhancements

I have been working on a new assignment this week, with the goal to improve performance in a .NET application using MySQL (3.21) for data storage. The application handles a lot of data in what is basically a single user system.

The main rason for the performance problems was heavy searches where indexes did not match. Simply switching the order of columns in the composite key fixed most of it.

In the old version they copied data to a temporary table, to solve the slow query problem. Once we had the indexes fixed, we could drop this solution and use the ase table in all cases. This also speeded it up a lot.

The change to MySQL 5.0 made a few other changes possible
- switch to shared memory for communication between database and application
- buffering of indexes
- query caching
which all ment further improved performance

All in all quite successful so far. Performance is OK now, and the bottleneck is not the database, so we will look at some other changes.

It has been great fun, .Net is a new environment for me, but it was quite easy to get into it.

No comments: