Based on morgo’s suggestion, I ran some small tests on the query and dataset mentioned in the article “Impact of column types on MySQL JOIN performance,” but discovered another issue at play: response time (aka MySQL versions).
The Answer
In short, as a good consultant would say, these conclusions all come with caveats 🙂
The Test
- SELECT *
- FROM a
- JOIN b ON b.a_id = a.id
- WHERE a.id BETWEEN 10000 AND 15000
- ;
All the tests below are based on the same query statement.
The MySQL-related parameters are set as follows. Do I also need to consider join buffer or other per-session buffers (read_buffer_size, read_rnd_buffer_size, join_buffer_size)?
- innodb_buffer_pool_size = 768M
- innodb_buffer_pool_instances = 1
- innodb_file_per_table = 1
The Results
Conclusion
Do not trust benchmarks. They are mostly meaningless for your specific workload and are pure marketing activities… including the one mentioned above! 😉
Database vendors (Oracle, MySQL, Percona, MariaDB) primarily focus on throughput and features. Basically, benchmarks reflect the performance cost of a single query.
MySQL users like Facebook, LinkedIn, Google, Wikipedia, Booking.com, Yahoo! are more interested in throughput than single query performance (I assume so). But the majority of MySQL users (95%) do not have throughput problems; they have query performance problems (I assume this is also true for Oracle, DB2, MS-SQL Server, PostgreSQL, etc.).
So database vendors’ products mainly serve not the masses, but specific users/customers (who might actually pay for them).
Let me get back to discussing the data:
The first assumption: “The good old days were always better” is absolutely incorrect. MySQL 4.0 and 4.1 were just exceptions. The rough trend based on MySQL 5.0 is: single query performance gets worse over time (with newer versions). I think this also applies to other databases…
Claims like “We have the fastest MySQL” or “We have hired the entire optimization team” do not necessarily reflect in single query performance, at least not for a specific query.
Therefore, in summary: If you upgrade (MySQL <-> Percona <-> MariaDB), you need to test very carefully! The pitfalls are unpredictable. Newer MySQL versions might increase your application’s performance. Child, do not trust the marketing too much.
Some Illusions
During our little test, we have discovered some illusions:
Optimizations introduced in MySQL 5.0 (were they not optimizations?!) greatly sped up specific single queries.
MariaDB 5.2 and 5.3 performed terribly on this specific query.
I do not know why the Galera cluster showed 5.5 as the best version. This was not intentional or manipulated! The result was just bad luck. But I like it! 🙂
MySQL 5.6 seems to have some issues with these queries. Maybe Oracle brought too many improvements to MySQL? (╯‵□′)╯︵┻━┻
Percona 5.6 sometimes performed better than regular MySQL on these queries, but sometimes whatever Oracle optimizations caused Percona to slow down significantly. Thus showing particularly bad results. I do not know why. My first reaction was external influence. But I was able to reproduce this messy situation (once). So I think there must be something inside Percona (e.g., AHI?).
Finally
Don’t shoot the messenger!
If you are unhappy with the calculation results published here and want to recalculate, or if something is missing, please let me know.
If you disagree with this conclusion, please also tell me. I would like to review the old to learn the new.
Today’s test was interesting. My MyEnv provided a lot of help for this test as well.
If you need us to run this test for you, please contact us. Our consulting team will be very happy to provide answers to your various questions.
Original link: http://www.fromdual.com/mysql-single-query-performance-the-truth
Translation link: http://www.oschina.net/translate/mysql-single-query-performance-the-truth