Query to Test MySQL Performance

I need to upgrade our MySQL 5.0.27 database to 5.开发者_开发技巧5. I understand there are some significant performance increases even between 5.0.27 and 5.1.

Before I complete the upgrade I would like to have a base for performance testing before and after the upgrade.

I have several tables with 500k+ rows and one with 5.8m+ rows.

Is there a query or method I can use to get a good reliable performance metric from my own data before and after the upgrade?

You should run benchmark tests with the queries that your system currently runs, there is no point in profiling queries that your database won't likely run when under a real world load as it won't let you see how your actual system will run, which ultimately is the only thing that matters.

I'm going to be bold and assume that you're only interested in the performance gains for your application. So I would suggest that you first copy the current environment and settings (so that's MySQL 5.0.27 et all) on a test server and simulate standard load and user interaction. Then upgrade to MySQL 5.1 or 5.5 on the test server and perform the same simulation, that way you get the real numbers you are interested in and you can practice on rolling out all these updates.

You can use something as http://loadimpact.com/ for a reliable benchmark of your website. But always do multiple runs and take the average, min, and max to compare it.