Best MySQL performance tuning tool?

Question

Which is the best, user-friendliest performance tool for MySQL? I'd like help with pinpointing the bottle neck of my setup. Is the problem in the SQL statements, the settings variables, or something else?

Answer 1

The bad news: there are GUI tools to help with this, but its a skilled and wide ranging job. So they don't cover everything, its likely you will need to use command line stuff/sql statements etc to help. I've only really used the command line tools. I'll give a bit of an overview of things I know/have used:

First, you need a good database design. If the design is bad, you can only get so far. This includes normalisation, as well as using appropriate types for fields. I'll leave this point here, as I think its a bit of an aside, and not what you are after.

Make sure the MySQL Query Cache is set up and working and give it a bit more RAM if you can, and make sure that your important queries aren't doing anything that prevents mysql caching them. For example, using the NOW() function in queries does this - for obvious reasons - NOW changes every second! You can instead put a timestamp into the sql, and use the time to the nearest minute/hour/day (the largest period you can get away with) to allow mysql to get some caching benefit.

To begin optimising things: Sticking "EXPLAIN" in front of select is THE way to see how a query is being executed and idetify how to improve it. Learn to interpret the output: http://dev.mysql.com/doc/refman/5.0/en/using-explain.html You'll often be able to add new indexes/add columns to existing ones to improve things. But you will also encounter times that queries need to be restructured.

Starting out improving performance with MySQL (assuming you don't already know what the problem query is) is to check the slow query log - it logs to a file all queries taking longer than x seconds.

Overview, including config for if its not logging this already, is here: http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html - I've also found that setting long_query_time to 0 for a day or so, so that all queries are logged here with time taken, is a useful way to get an idea of exactly where the performance is going. But I wouldn't go there immediately! And don't leave it on, the logs can get massive.

Once you've got a few days of logging, I've found mysqlsla (mysql slow log analyser) from here: http://hackmysql.com/mysqlsla is a good tool.

It can do more than just slow query log analysis - read the manual. But to explain what it does for slow logs: the slow query log can contain a lot of data, so it can be hard to figure out which queries are the most expensive overall - eg: factor in how many times they run and when two queries are actually the same with a different id in a where clause.

MySQL sla does this all for you. It runs through the log, and can group queries that are the same/have different values in the where clauses. It then presents you (by default) the top 10 queries in terms of total execution time - which often has some surprises, but is usually the most productive starting point - take the most expensive query and use EXPLAIN on it and see if you can improve it.

Some queries take a long time, and can't easily be improved. In this case, can you get the data another way or at least cache it instead? You may even find that changing the DB schema is required. Similarly, some queries may be at the top of the mysqlsla output because you run them a lot (especially true if long_query_time is set to 0), even if they run pretty quick. Maybe time to add some caching to your app?

http://www.maatkit.org/ also looks promising - never used it, but the mk-query-profiler tool should be useful to further look into why queries slow.

A completely separate thing to look at as well: the "status" page in PHPMYADMIN (or you can run all the queries to generate this info ....) - it highlights things it thinks might be bad in red, and can help you see where you might get benefit from allocating system resources. I don't know that much on this - my approach has always been that if something is red and looks bad, to go and read up about it and decide if its important and whether I should do something (usually means allocating more resources to MySQL by changing config).

Recently I've found that running SHOW PROCESSLIST can also be useful on a server that is suffering. Whilst it only gives you live (well, a live snapshot) info, it can help you get a feel for what is going on at a given time, especially if you refresh a few times and observe the changes. I recently spotted a server using every available mysql connection to run an identical query using this method. Sure, it'd have been in the slow query log, but this as a really quick and obvious way to see what was up.

Answer 2

EXPLAIN is your friend. Other than that, you must rely on a mixture of ad-hoc tools, testing, and the OS tools to see exactly what's going on.

A lot of things can be seen effectively either from the OS tools (good for determing whether a situation is CPU or IO bound) or the various variables which can be seen in MySQL itself (show global variables, show engine innodb status etc).

I feel that it's really important to have a PERFORMANCE TEST ENVIRONMENT. Get a production-sized database with production-like load (using load simulation) running on production-grade hardware in your lab (expensive but essential).

Once you've done that you can tweak any parameters you want in complete safety, knowing that even if it melts the whole server you can just rebuild it without affecting anything.

Don't do specific things that people suggest unless you've tested them in your performance test environment and found them to be provably beneficial.

Essentially, performance tuning usually looks at three areas, in this order:

• Structure of your database itself (table structure, indexes etc)
• Queries
• Tuning the server parameters

The first two are often overlooked in favour of the last one, which can help but only after the first two are done.

Don't underestimate the importance of reducing the amount of work the server needs to do - using smaller fields, fewer rows, fewer columns etc, is always better.

Answer 3

I found MONyog to be a BIG help when i had a suffering mysql server.

Its a webbased system that analyses your system 24/7 and gives you decent suggestions for better values of system variables, cache sizes, etc...

It also has a built in mysqlsla type slow query analyzer feature.

Too bad it's not free.