Midgard MySQL optimization tricks

Bergie asked me to write some things here as well on how to make Midgard faster by making the underlying MySQL faster.

Update 2006-09-20: I've put some of this and some more to Midgard documentation, another page there also covers the Squid setup I promised.

Quick and Dirty things

in my.cnf make sure query cache is enabled:

query_cache_type = 1
query_cache_size = 10M
query_cache_limit = 1M

There are other caches to consider as well, see the example my-xxx.cnf -files, usually included in the install. Also tuning the caches to your specific situation may require some work.

Especially if your filesystem is slow for some reason, make sure the atime doesn't get stored for MySQL database files, if you have MySQL datadir as it's own partition (you should), then in /etc/mtab add noatime to the mount options and remount. If not you can use chattr to set the flags on the files via the following command chatttr +A -R /path/to/mysql.

More about it

Here's a few articles on optimizing mysql, O'Reilly has a good book about it as well.

• Optimizing MySQL: Hardware and the Mysqld Variables (esp the variables part)
• MySQL's Query Cache
• MySQL Presentations: Optimizing MySQL

With 1.8 a lot of things have changed so we should rethink the indexes Midgard uses, I and Piotras have this under work but suggestions on the dev list are welcome.

As a side note, if it's a public site you can also run Squid as reverse proxy in front of it making things a lot faster, I'll try to find time to write about it while I still remember the issues I ran into when doing it on two occasions in the last few weeks.

Other things

My N93 has been in repair for slightly over a week now, I guess it really had faulty memory (though the repair people have not yet confirmed anything else than that they had to send it to central repair, which suggests a difficult-to-fix HW problem), I cursed this on monday when going to training I saw a true classic parked next to the F-Secure office: