OK, So I’ve just set up a benchmark on our system using real data, comparing the latest Percona-Server with the latest Mysql 5.1 - I’ll admit it’s not the most scientific of tests but I wanted to test our real-world queries on the different servers.
The query I am running is set to SQL_NO_CACHE and is a tricky one for us as it compares lat/long so doesnt tend to work with indexes very well so a good one to benchmark as it reflects a commonly used query
SELECT SQL_NO_CACHE R., (((acos(sin((52.769pi()/180)) * sin((Latitudepi()/180)) + cos((52.769pi()/180)) * cos((Latitude*pi()/180)) * cos(((-2.385 - Longitude)*pi()/180))))180/pi())601.1515) as Distance FROM restaurants R group by R.sk_restaurantid HAVING (Distance1609.344) <=16090 order by hits desc LIMIT 15
This is an INNODB table.
Our server is :
Freebsd 8.1 AMD64
2.66Ghz Quad Core QPi (8 virtual cores)
4 x 73Gb raid 6
It’s a new server, so no production usage. We waited until all CPU/IO stats were back to 0% before running each test
I’ve used the exact same /etc/my.cnf for both tests, except I specified innodb_io_capacity = 400 when using xtradb
Results (figures are Qieries per sec from SuperSmack):
Clients Percona Mysql 5.1100 6134.05 6467.55150 6071.11 6247.34200 5920.65 6182.7250 5974.97 6137.74300 5955.5 6154.72349 5913.35 6140.48400 5907.92 6014.77499 5938.17 6038.96