MySQL Duh-Optimization

I’ve been optimizing MySQL on Apache for years. Know all sorts of tricks. But obviously I got a little cocky. I missed two very obvious things and now feel like an idiot.
I found out about these two obvious things by running the script that is popular now. Google it.
The first thing I noticed was that some of the settings it said I had weren’t the settings I thought I had. For instance, max_connections was set to 0. Huh? I had it set to 500!
So I looked at the my.cnf file and saw that I did have max_connections=500, but it was after a [mysqld_safe] line. I always assumed that the [] lines were comments of some sort, so you could organize your settings. But a light bulb went off and I suddenly realized that they are more important than that. I moved the max_connections and other lines to under the [mysqld] and restarted MySQL and then checked to make sure max_connections and my other settings were now set right.
Oh, to think of the wasted processor cycles over the last year!
The next thing I did was to check my slow queries log. I set that up a while back, but hadn’t checked it. Sure enough, one particular query was the bulk of all slow queries. It was a select with a single condition, but on a very large table. Some research suggested the obvious: that I should make the condition column an index if I could. Duh! I could certainly do that in this case. I could also refine its data type a bit.
Anyway, the result of that is the query no longer even makes an appearance in my slow queries log.
OK. So this goes to show that no matter how much you think you know, you can always learn more.

Posted on March 9, 2009 at 10:58 am by Gary Rosenzweig · Permalink
In: General