So lets say you upgraded from MySQL 5.1 to Percona Server 5.5 and instead of expected performance improvement you see your performance being worse. What should you do ?
First if you followed MySQL upgrade best practices such as testing your workload with pt-upgrade the chances of this happening are rather slim. But lets assume you have not followed these recommendations to the book or some things just slipped through.
First lets talk about what you should not do. You should not panic and go change all kind of configuration options in case you get lucky. Sometimes you do but more often you just waste your time. Instead try to understand what has changed and what exactly changed for worse.
I am speaking about MySQL upgrade – version change but in a lot of cases this will happen when settings are changed at the same time, operating system is upgraded or system is moved to “better” hardware. It is best if you can isolate the problem to one of these – make sure changing only MySQL version on the same hardware with same settings causes regression. You can always improve them later after you got well performing baseline. Do not change storage engine at the same time eather.
Make sure you’re looking at fully warmed up system. Rather often people panic and what thought to be slow down with version upgrade is rather system warming up. You need to compare apples to apples such as both systems after restart or even better both systems fully warmed up.
To spot what exactly is causing the problem it is good to use pt-query-digest to compare workloads on servers. Best if you can analyze full query logs but if this is not possible at least look at network traffic from tcpdump. This should help you to spot queries which are a lot slower on new MySQL version.
Once you have the queries you will most likely able to repeat the problem by running query on old and new MySQL Server version and observing the difference. Sometimes though it might not show performance difference ran alone on idle system – in this case you might be dealing with concurrency problem and you might need to create a more complicated test which runs this query concurrently many times or even workload consisting of several query types. Though it is rather rare for this to be needed so lets not go there.
It is ideal if you can repeat the problem on “test server” where you can analyze it without side load. Setting up 2 MySQL Servers side by side (for example with MySQL Sandbox) can especially be helpful.
Once you have spotted the query which performs differently between MySQL Server versions you should:
Check Query Plan Run EXPLAIN to see if plans for the query are the same. Changing Query Execution plans is the most common regression problem. If plan has changed check
if you can get the plan manually to the old one by using hints such as STRAIGHT_JOIN, FORCE INDEX, BIG_RESULT/SMALL_RESULT. Check whatever stats are the same (run SHOW INDEXES FROM