MySQL Performance Blog

Try number two. We have moved to the new server yet again, now it is server hosted by ServerBeach as recommended by Kevin Burton and few other guys.

Lets hope this will run stable and we’ll not need to move it back in emergency in less than a week as we had to last time.

Up to this point it all was running pretty well, with only minor issues. We got CentOS 5 on the box as we wanted, however we could not request custom partitioning - I really prefer to keep all important data on LVM volume so it is easy to backup.

About three months ago I announced ClickAider to become available to general public. And I think it is about the time to write about the progress we have with this project for those who interested.

It is submission time now, with about one week left till proposal submission deadline. Both me and Vadim had submitted few talks to the Grand Jury which will make elite selection of sessions for MySQL Users Conference and we plan to submit few more.

One of the first rules you would learn about MySQL Performance Optimization is to avoid using functions when comparing constants or order by. Ie use indexed_col=N is good. function(indexed_col)=N is bad because MySQL Typically will be unable to use index on the column even if function is very simple such as arithmetic operation. Same can apply to order by, if you would like that to use the index for sorting. There are however some interesting exception.

The other day I had a case with an awful performance of a rather simple join. It was a join on tb1.vid = CONCAT('prefix-', tb2.id) with tb1.vid - indexed varchar(100) and tb2.id - int(11) column. No matter what I did - forced it to use key, forced a different join order, it did not want to use tb1.vid index for it. And no surprise it was way too slow, the number of rows analyzed was really huge:

We many times wrote about InnoDB scalability problems, this time We are faced with one for MyISAM tables. We saw that several times in synthetic benchmarks but never in production, that's why we did not escalate MyISAM scalability question. This time working on the customer system we figured out that box with 1 CPU Core is able to handle more queries per second than identical box, but with 4 CPU Cores.

The main query which showed this problem was similar to this:

Almost two weeks have passed since HighLoad 2007 conference in Moscow, Russia so I’m pretty late with feedback and my only excuse I was pretty busy to spare some time for it.

About one month ago we posted a call for interns to work with us on various MySQL Performance related things.

We got good number of replies and a lot of people wondered what tasks exactly we could offer as surely this sort of involvement should be fun and should teach you something.

Have you ever had a question about Innodb internal design or behavior which is not well covered in MySQL manual ? I surely had.
Now you have a great chance to have them answered !

Heikki Tuuri, Creator of Innodb will answer your Questions about Innodb at MySQL Performance Blog.

Please leave your questions as comments to this post by 5th of October and I will pass them to Heikki to reply merging with questions I have myself.

It always surprised me how little Innodb team seems to think about product usability/ease of use, when it comes to settings, performance management etc.

26th of September, just after HighLoad conference I will have full day MySQL master class.

Quite typical query for reporting applications is to find top X values. If you analyze Web Site logs you would look at most popular web pages or search engine keywords which bring you most of the traffic. If you're looking at ecommerce reporting you may be interested in best selling product or top sales people. This information may often need simple select query, however what if you would like to show percents not just absolute value ?

For illustration purposes I've created a syntetic table filled with some 30mil rows evenly spread in 10.000 groups.