Book Review – High Performance MySQL (3rd edition)

Last modified:

Imagine having a tool that can automatically detect JPA and Hibernate performance issues. Wouldn’t that be just awesome?

Well, Hypersistence Optimizer is that tool! And it works with Spring Boot, Spring Framework, Jakarta EE, Java EE, Quarkus, or Play Framework.

So, enjoy spending your time on the things you love rather than fixing performance issues in your production system on a Saturday night!

Introduction

I either have time for reading or writing, but not both. Now that the first edition of High-Performance Java Persistence is done, I can catch up on the many books I planned on reading but didn’t have time to do so.

In this post, I’m going to review High Performance MySQL by Baron Schwartz, Peter Zaitsev, and Vadim Tkachenkoa, which is a must-read book for anyone working with MySQL.

Audience

This book is useful for both DBA and backend developers alike. Knowing the internals of the database in use can make a huge difference when you need to scale your enterprise application, and MySQL is no different.

Content

High Performance MySQL has 770 pages, and the third edition, published in 2012, covers MySQL up to version 5.5. Although it would have been great to have a newer edition covering 5.6 and 5.7, a lot of content is still relevant even nowadays.

Chapter 1 – MySQL Architecture and History

This chapter offers a very nice introduction to MySQL architecture as well as to its different storage engines, such as InnoDB and MyISAM. It also provides some insight into how database concurrency control works and why MVCC is suitable for modern OLTP application use cases.

Chapter 2 – Benchmarking MySQL

As Kirk Pepperdine likes to say: you should Measure, Don’t Guess. Knowing how to benchmark a system properly so that you get meaningful results is not very easy.

This chapter wants to get you familiar with benchmarking tools, such as sysbench so that you can measure the performance and throughput of your MySQL installation.

Chapter 3 – Profiling Server Performance

After you benchmarked the MySQL installation and proved the maximum hardware capacity, you should turn your attention to your actual application and monitor and profile that instead. With tools such as EXPLAIN, SHOW PROFILE and pt-query-digest which is offered by Percona, you can get a better understanding why a given statement takes more than necessary.

Chapter 4 – Optimizing Schema and Data Types

This chapter presents how MySQL implements standard types like CHAR, VARCHAR or DATE, TIME, and TIMESTAMP types, and MySQL-specific DATETIME, ENUM or SET. While normalization is to be preferred, sometimes you need to denormalize a database schema to achieve the performance that otherwise would be impossible to achieve.

Chapter 5 – Indexing for High Performance

Without indexing, every access to the database would result in a full-table scan. Indexing allows us to better localize the data blocks we are interested in reading or writing.

MySQL imposes rules on database indexes, so you should be aware of them in order to make the most out of them.

Chapter 6 – Query Performance Optimization

After properly designing the schema and adding the database indexes your application can best take advantage of, it’s time to move to more advanced topics that are related to how MySQL parses and executes a given statement.

Chapter 7 – Advanced MySQL Features

This chapter covers many topics such as table partitioning, Views, Full-text Search, Character sets, XA Transactions, and Query cache, and, since all these have an impact on application performance, it’s very important to know how they work behind the scenes.

Chapter 8 – Optimizing Server Settings

MySQL comes with many configuration parameters, and the default settings are not suitable for a production environment. While innodb_buffer_pool_size and innodb_log_file_size are the first ones to start with, you should pay attention to other settings and tune them according to your underlying operating system and hardware capabilities.

Chapter 9 – Operating System and Hardware Optimization

All database systems require CPUs, memory, and I/O resources (disk and network). Choosing the right hardware according to your system needs is very important especially when you have a limited budget for hardware acquisition.

Chapter 10 – Replication

Replication is the reason why MySQL is so popular. This chapter covers the basics of Single Primary replication as well as many other more advanced topologies as well as tips related to monitoring and ensuring that replication is working properly.

Chapter 11 – Scaling MySQL

This chapter defines what scaling means as well as the Universal Scalability Law. While replication, partitioning, and sharding can help you scale, there are other solutions as well such a MySQL Cluster or Percona ExtraDB Cluster as well.

Chapter 12 – High Availability

100% availability is impossible, that’s why availability is defined in terms of the number of nines. Using redundant servers as well as synchronous replication is a good way to improve availability.

Chapter 13 – MySQL in the Cloud

This chapter covers various types of MySQL Cloud offerings, from service-based to infrastructure-based options. Even if things have changed significantly since the third edition was published, this chapter offers many insights into what might be limiting MySQL in the Cloud, so it’s still relevant to the day.

Chapter 14 – Application-Level Optimization

The data access layer is very important when it comes to application performance. Even if you have high-performance hardware and MySQL is tuned properly, you need to make sure you don’t execute unnecessary work due to bad data access design considerations.

Chapter 15 – Backup and Recovery

This chapter is very important because losing your data could undermine your business as well. Having a solid backup strategy that you exercise on a regular basis is the only way you can ensure that you can recover your data whenever something terribly bad has happened.

Chapter 16 – Tools for MySQL Users

This chapter enumerates some Percona tools which are very handy when working with MySQL, as well as monitoring tools such as Nagios, Ganglia or New Relic.

Why you should buy it?

As previously stated, this book is very comprehensive and useful no matter if you’re using MySQL or any other RDBMS. There are many topics like scalability, replication, backup, hardware provisioning that are applicable to any database system you might be using, even to NoSQL or NewSQL databases.