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 Video Library
Refcards
Trend Reports

Events

View Events Video Library

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

Generative AI has transformed nearly every industry. How can you leverage GenAI to improve your productivity and efficiency?

SBOMs are essential to circumventing software supply chain attacks, and they provide visibility into various software components.

Related

  • Building an SQL to DataFrame Converter With ANTLR
  • Master SQL Performance Optimization: Step-by-Step Techniques With Case Studies
  • MySQL Formatter: How to Make Beautiful Code and Why You Need It
  • Useful System Table Queries in Relational Databases

Trending

  • Building an AI Nutrition Coach With OpenAI, Gradio, and gTTS
  • The Rise of the Intelligent AI Agent: Revolutionizing Database Management With Agentic DBA
  • 10 Predictions Shaping the Future of Web Data Extraction Services
  • Build Real-Time Analytics Applications With AWS Kinesis and Amazon Redshift
  1. DZone
  2. Data Engineering
  3. Databases
  4. How Binary Logs Affect MySQL 8.0 Performance

How Binary Logs Affect MySQL 8.0 Performance

Learn how the recently released MySQL 8.0 performance is affected in similar scenarios, especially as binary logs are enabled by default.

By 
Vadim Tkachenko user avatar
Vadim Tkachenko
·
May. 10, 18 · Analysis
Likes (2)
Comment
Save
Tweet
Share
6.3K Views

Join the DZone community and get the full member experience.

Join For Free

As part of my benchmarks of binary logs, I've decided to check how the recently released MySQL 8.0 performance is affected in similar scenarios, especially as binary logs are enabled by default. It is also interesting to check how MySQL 8.0 performs against the claimed performance improvements in redo logs subsystem.

I will use a similar setup as in my last article with MySQL 8.0 using the utf8mb4 charset.

I have a few words about MySQL 8.0 tuning. Dimitri recommends in his blog posts to use innodb_undo_log_truncate=off and innodb_doublewrite=0. However, in my opinion, using these settings is the same as participating in a car race without working brakes: you will drive very fast, but it will end badly. So, contrary to Dimitri's recommendations, I used innodb_undo_log_truncate=on and innodb_doublewrite=1.

Servers Comparison

For the first run, let's check the results without binary logs vs. with binary logs enabled, but with sync_binlog=1 for Percona Server for MySQL 5.7 vs. MySQL 8.0.

Image title

In tabular form:

Image title

Binary Log Effect

In tabular form:

Image title

Conclusions

It seems that binary logs have quite an effect MySQL 8.0, and we see up to a 30% performance penalty as opposed to the 13% for Percona Server for MySQL 5.7.

In general, for in-memory workloads, Percona Server for MySQL 5.7 outperforms MySQL 8.0 by 10-20% with binary logs enabled, and 4-9% without binary logs enabled.

For io-bound workloads (buffer pool size <= 30GB), the performance numbers for Percona Server for MySQL and MySQL are practically identical.

Hardware Spec

Supermicro server:

  • Intel(R) Xeon(R) CPU E5-2683 v3 @ 2.00GHz
  • 2 sockets / 28 cores / 56 threads
  • Memory: 256GB of RAM
  • Storage: SAMSUNG SM863 1.9TB Enterprise SSD
  • Filesystem: ext4/xfs
  • Percona-Server-5.7.21-20
  • OS: Ubuntu 16.04.4, kernel 4.13.0-36-generic

Extra Raw Results, Scripts, and Config

My goal is to provide fully repeatable benchmarks. I have shared all scripts and settings I used in this GitHub repo.

MySQL

Published at DZone with permission of Vadim Tkachenko, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Related

  • Building an SQL to DataFrame Converter With ANTLR
  • Master SQL Performance Optimization: Step-by-Step Techniques With Case Studies
  • MySQL Formatter: How to Make Beautiful Code and Why You Need It
  • Useful System Table Queries in Relational Databases

Partner Resources

×

Comments

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

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

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • [email protected]

Let's be friends: