Chapter 7. MySQL 8 Best Practices

In the previous chapter, you learned how to use replication to build highly available solutions. It covered a lot of interesting aspects such as failovers, group replications, clustering, and so on. In this chapter, we will go through the best practices of MySQL 8, which is a much-awaited version that promises to address many of the shortfalls of the prior versions and has exciting new features. MySQL 8 promises not to be just a standalone database, but also will play a significant role in various areas including big data solutions.

Topics that we will be covering in this chapters are mentioned below:

• MySQL benchmarks and configurations
• Best practices for MySQL queries
• Best practices for the Memcached configuration
• Best practices for replication

Due to prominent optimizations and changes, MySQL 8 advanced its version directly from the release of MySQL 5.7. MySQL 8 will not have the limitation of files that was previously restricting the number of databases that...

MySQL benchmarks and configurations

We have gone through various new features and improvements MySQL 8 is coming up with. It makes us more excited as performance is always what we crave for. MySQL 8 not being generally available yet, Oracle hasn't published its benchmark results. We didn't wait for it and did analysis on our own in a few areas.

Configuration best practices of MySQL is the cherry on the cake; without the cherry, the cake seems incomplete. In addition to configurations, benchmarking helps us validate and find bottlenecks and address them. Let's look at a few specific areas that would help us understand best practices for configuration and performance benchmarking.

Resource utilization

IO activity, CPU, and memory usage is something that you should not miss out. These metrics help us know how the system is performing while doing benchmarking and at the time of scaling. It also helps us derive impact per transaction.

Stretch your timelines of benchmarks

We often would like to have...

Best practices for MySQL queries

It would be difficult to write the best queries for reference and reuse. It will always vary based on the nature of your application, architecture, design, table structure, and so on. However, precautions can be taken while writing MySQL queries for better performance, scalability, and integrity.

Let's go through a few of the best practices that we should keep in mind while designing or writing MySQL queries.

Data types

A database table would consist multiple columns having data types such as numeric or string. MySQL 8 provides various data types than just limiting to numeric or string.

• Small is good. As MySQL loads data in memory, a large data size would adversely impact performance. Smaller sets can accommodate more data in memory and reduce overheads of resource utilization.
• Fix your length. If you don't fix the data type length, it would have to go and fetch the required information each time it needs. So, wherever it's possible, you can limit data length...

Best practices for the Memcached configuration

We have already gone through Memcached setup and configuration along with using it with different APIs in Chapter 4, Using Memcached with MySQL 8. The key takeaways that you would like to consider for Memcached configuration best practices are what we will be going through now.

Resource allocation

Memory allocation for Memcached shouldn't be allocated over the available physical memory or without considering other resources that would be utilizing memory. If we overallocate memory, there are high chances that Memcached would have memory allocated from swapspace. This may lead to delay while inserting or fetching values because swap space is stored on disk, which is slower than in-memory.

Operating system architecture

With operating system architecture having 32 bits, one needs to be cautious. As we know, there are limitations to provision resources in 32-bit operating system architecture. Similarly, Memcached having 4GB RAM with 32-bit operating...

Best practices for replication

MySQL 8 has done some great improvements in the replication part. The purpose of MySQL 8 is all about scalability, performance, and security with utmost integrity of data, which is expected to be a game-changer in big data too.

Throughput in group replication

Group replication basically takes care of committing transactions once most of the members in group replication have acknowledged the transaction received concurrently. This helps in a better throughput if the overall number of writes is not exceeding the capacity of the members in group replication. If there is a case where capacity is not planned appropriately, you would notice lags on affected members as compared to other members in the group.

Infrastructure sizing

Infrastructure sizing is a common success factor for performance and the best practices checklist. If infrastructure sizing is not proper or uneven across the nodes in group replication, it might adversely impact replication fundamentals topology...

Summary

I am sure that while reading the chapter, you would be keeping in mind the things to be taken care of or recollecting if there's anything missing in your MySQL 8 implementation. In these chapter we discussed about best practices for MySQL 8 which would be helpful at various stages like implementation, usage, management, and troubleshooting and would act as pointers for Best Practices of MySQL 8; it might vary based in different use-cases. Proper testing and verification would help affirm on the benefits of having best practices implemented.

We have broadly covered some exciting topics about MySQL 8 benchmarks and a few configuration parameters. MySQL queries pointers were also discussed with caching using Memcached best practices. Lastly, we discussed MySQL replication best practices in which we went through a few critical pointers. Anything written in this chapter would be less, but pointers provided are necessary.

By now, we have a good implementation completed for MySQL 8; it's...