Seize the deal!
Caching Best Practices
Imagine having a tool that can automatically detect if you are using JPA and Hibernate properly. Hypersistence Optimizer is that tool!
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.
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.
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.
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.
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.
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
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.
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.
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.
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.
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.
MySQL comes with many configuration parameters, and the default settings are not suitable for a production environment. While
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
I strongly recommend you buy this book. Although I read it, I’m sure that I’ll come back to it from time to time.