Book Image

Advanced MySQL 8

By : Eric Vanier, Birju Shah, Tejaswi Malepati
Book Image

Advanced MySQL 8

By: Eric Vanier, Birju Shah, Tejaswi Malepati

Overview of this book

Advanced MySQL 8 teaches you to enhance your existing database infrastructure and build various tools to improve your enterprise applications and overall website performance. The book starts with the new and exciting MySQL 8.0 features and how to utilize them for maximum efficiency. As you make your way through the chapters, you will learn to optimize MySQL performance using indexes and advanced data query techniques for large queries. You will also discover MySQL Server 8.0 settings and work with the MySQL data dictionary to boost the performance of your database. In the concluding chapters, you will cover MySQL 8.0 Group Replication, which will enable you to create elastic, highly available, and fault-tolerant replication topologies. You will also explore backup and recovery techniques for your databases and understand important tips and tricks to help your critical data reach its full potential. By the end of this book, you’ll have learned about new MySQL 8.0 security features that allow a database administrator (DBA) to simplify user management and increase the security of their multi-user environments.
Table of Contents (13 chapters)
11
Advanced MySQL Performance Tips and Techniques

Tips and techniques

The following is a list of variables, parameters, or simply comments to improve a particular situation or context:

  • Use innodb_flush_method=O_DIRECT to avoid a double buffer when writing.
  • Setting innodb_buffer_pool_size to load the entire InnoDB data into memory can help to avoid reading from the disk.
  • Do not make innodb_log_file_size too high; with faster disks, flushing often is not bad and lowers the recovery time during crashes from your server.
  • Avoid mixing these two variables: innodb_thread_concurrency and thread_concurrency. This will have a better impact on MySQL performance.
  • Allocate only max_connections. When we use too many connections, the impact is that MySQL will use a lot of your RAM and needlessly slow down your MySQL server.
  • Keep thread_cache at a relatively high setting; a minimum of 16 should be good enough to prevent slowness when opening...