MySQL and Performance Problems

Over lunch today I finally resolved the last blocker issue on the newly set up UFies.org box. Everything went in fine but I found while doing some testing on a site with large database tables that performance sucked! The problem could be narrowed down to a query which takes 0.1s on a heavily loaded debian unstable system running on an XP1800 with 1G of ram was taking 10-20s on an unloaded dual P3-1.13Ghz gentoo system with 2G of ram. I recompiled MySQL with different options until the cows came home, even tried the binary distribution from the webpage, with no changes in the performance problems…

Update: Looks like the indexing issue is working properly again in 4.0.17. So 4.0.14 good, 4.0.16 bad, 4.0.17 good. Odd.

At one point I thought I got it by running myisamchk on the tables in question, but it turns out that I had forgotten to start the database server, so the pages I was getting (using “time wget “<url>” for my testing) was full of mysql connect errors.

After some bitching, whining, complaining, and asking on various mailing lists it boiled down to this:

Running “explain” for the query on the old system:

+——-+——–+——————————————————–+—————–+———+—————+——-+————-+

| table | type   | possible_keys                                          | key             | key_len | ref           | rows  | Extra       |

+——-+——–+——————————————————–+—————–+———+—————+——-+————-+

| t1    | range  | w3t_Postsindex7,w3t_Postsindex8,ID_ndx,board_topic_ndx | w3t_Postsindex8 |       4 | NULL          | 19645 | Using where |

| t2    | eq_ref | indx1                                                  | indx1           |     100 | t1.B_Board    |     1 |             |

| t3    | ref    | PRIMARY,indx3                                          | indx3           |       4 | t1.B_PosterId |     1 |             |

+——-+——–+——————————————————–+—————–+———+—————+——-+————-+

And the new:

+——-+——–+——————————————————–+—————–+———+—————+——+—————————–+

| table | type   | possible_keys                                          | key             | key_len | ref           | rows | Extra                       |

+——-+——–+——————————————————–+—————–+———+—————+——+—————————–+

| t1    | ref    | w3t_Postsindex7,w3t_Postsindex8,ID_ndx,board_topic_ndx | w3t_Postsindex7 |       3 | const         | 6607 | Using where; Using filesort |

| t2    | eq_ref | indx1                                                  | indx1           |     100 | t1.B_Board    |    1 |                             |

| t3    | eq_ref | PRIMARY,indx3                                          | PRIMARY         |       4 | t1.B_PosterId |    1 |                             |

+——-+——–+——————————————————–+—————–+———+—————+——+—————————–+

The difference is that the first is using index8, and the slower second is using index7, and has “Using filesort” in the “Extra” column. Filesort is what happens if MySQL can’t sort the order with an index, and it resorts to being really slow and hard on the system. In searching to find out
how to avoid slow filesort there didn’t seem to be any real explaination 🙁

In the end I reverted back to MySQL 4.0.14 and voila!, it went back to working properly, using the right indexes, and executing the query in the 0.1s that I was expecting.

Maybe someone like Jeremy or one of the folks on the MySQL mailing list can explain this. Shouldn’t MySQL use the same keys from version to version?

I don’t like being a version (or three) behind, but if it’s a matter of a 100x performance hit, I’ll deal with it.