DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
  1. DZone
  2. Data Engineering
  3. Databases
  4. Is MySQL 5.6 slower than MySQL 5.5?

Is MySQL 5.6 slower than MySQL 5.5?

Peter Zaitsev user avatar by
Peter Zaitsev
·
Feb. 19, 13 · Interview
Like (0)
Save
Tweet
Share
5.66K Views

Join the DZone community and get the full member experience.

Join For Free

there have been a number reports/benchmarks showing mysql 5.6 to be slower than mysql 5.5 on variety of workloads. there are many possible reasons and i believe we will learn about many of them in the next few weeks and months as mysql 5.6 is starting to get production battle tested and there is inflow of real world production performance data hitting mysql support team at oracle. for number of simple workloads i would expect to see some slowdown – mysql 5.6 optimizer got a lot smarter and the more plans have to be considered for the query the more time the query optimization is likely to take. i also would expect newer code to benefit from “polishing” and clean up to achieve the best performance possible. at percona we surely will be doing fine tuning for our percona server 5.6 release.

another thing to remember about mysql 5.6 is – it comes with performance schema enabled by default. even though performance schema overhead was reduced in mysql 5.6 it is still not free and it will cause an overhead, yet hopefully having more information about system operation at your fingertips will help you to achieve gains higher than the slowdown it causes.

i decided to do my own quick check into the problem using sysbench. it is by no means reflection of any production workload but we found it is a good measure of the overhead, especially for simple queries. i ran small (1m rows) benchmark so data was well fitting in memory and read only one to simplify things and get more repeatable results without spending too much time on it. i used ubuntu 12.04 on some old dell poweredge 2850 server. the questions i tried to answer are how much slower 5.6 is for such simple workload ? how much of this slowness comes from performance schema ? whenever we get difference higher or lower with high concurrency compared to single thread ?

note sysbench is simple enough it does not take advantage of any of new mysql 5.6 optimizations so it is geared towards worse case scenario for mysql 5.6 performance.

here is sysbench command i used:

root@dpe01:/mnt/data/sandboxes/msb_5_6_10# sysbench --test=oltp --mysql-socket=/tmp/mysql_sandbox5610.sock  --mysql-user=msandbox_rw --mysql-password=msandbox --oltp-table-size=1000000 --num-threads=1  --oltp-read-only=on --init-rng=on --max-requests=0 --max-time=300 run

56vs55_single_thread

for single thread mysql 5.5 is 11% faster than mysql 5.6. if you disable performance schema in mysql 5.6 comes to just about 3% showing performance schema itself is responsible for 7.5% overhead in this case. this is quite reasonable result and i do not think 3% difference will be noticed in most production cases.

56vx55_64_threads

for benchmark with 64 threads mysql 5.5 is 26% faster compared to mysql 5.6 and if you disable performance schema the difference is still 11% with 13% difference between mysql 5.6 with performance schema enabled and disabled. the difference of over 25% is rather substantial and if fair amount of workloads will see similar kind of overhead i expect a lot of grief.

it is also unfortunate to see the difference for this specific benchmark actually growing with increased contention even though mysql 5.6 is positioned as improving scalability over mysql 5.5. also number of expected slowdowns such as more work done at optimizer phase should not increase with contention.

granted i have not tuned mysql 5.6 for best performance going with defaults outside of few variables mysql sandbox sets explicitly but yet this is likely course of action for many mysql users out there, also was not change of defaults in mysql 5.6 had more reasonable defaults as its goal ?

summary: well, to be frank i expected more from mysql 5.6 release – more of cleaned up code and better out of the box performance. yet i’m still very excited about all architecture changed implemented in mysql 5.6 and new feature and i believe when we look at real workloads we’ll see more cases when mysql 5.6 excels. i also expect some analyses and cleanup done over next few months which should help to improve mysql 5.6 performance.

MySQL

Published at DZone with permission of Peter Zaitsev, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • Bye-Bye, Regular Dev [Comic]
  • ChatGPT: The Unexpected API Test Automation Help
  • What Should You Know About Graph Database’s Scalability?
  • Top Five Tools for AI-based Test Automation

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends: