High Performance MySQL: Optimization, Backups, and Replication
Format: PDF / Kindle (mobi) / ePub
How can you bring out MySQL’s full power? With High Performance MySQL, you’ll learn advanced techniques for everything from designing schemas, indexes, and queries to tuning your MySQL server, operating system, and hardware to their fullest potential. This guide also teaches you safe and practical ways to scale applications through replication, load balancing, high availability, and failover.
Updated to reflect recent advances in MySQL and InnoDB performance, features, and tools, this third edition not only offers specific examples of how MySQL works, it also teaches you why this system works as it does, with illustrative stories and case studies that demonstrate MySQL’s principles in action. With this book, you’ll learn how to think in MySQL.
- Learn the effects of new features in MySQL 5.5, including stored procedures, partitioned databases, triggers, and views
- Implement improvements in replication, high availability, and clustering
- Achieve high performance when running MySQL in the cloud
- Optimize advanced querying features, such as full-text searches
- Take advantage of modern multi-core CPUs and solid-state disks
- Explore backup and recovery strategies—including new tools for hot online backups
the index covers the query completely, the server needs to look up the full rows in the table, and that causes random I/O a row at a time over a very large space, which will just kill query response times. The cost of maintaining the index (disk space, I/O operations) is also very high. Systems such as Infobright acknowledge this and throw B-Tree indexes out entirely, opting for something coarser-grained but less costly at scale, such as per-block metadata over large blocks of data. This is what
conflicts with servers that have no explicit server IDs. A common practice is to use the final octet of the server’s IP address, assuming it doesn’t change and is unique (i.e., the servers belong to only one subnet). You should choose some convention that makes sense to you and follow it. If binary logging wasn’t already specified in the master’s configuration file, you’ll need to restart MySQL. To verify that the binary log file is created on the master, run SHOW MASTER STATUS and check that
manage large volumes of data. Appendix D shows you how to really use and understand the all-important EXPLAIN command. Appendix E shows you how to decipher what’s going on when queries are requesting locks that interfere with each other. And finally, Appendix F is an introduction to Sphinx, a high-performance, full-text indexing system that can complement MySQL’s own abilities. Software Versions and Availability MySQL is a moving target. In the years since Jeremy wrote the outline for the first
webservicecalls -> JOIN webservicecalls USING(day, account, service, method);We varied this query to join the VARCHAR and ENUM columns in different combinations. Table 4-1 shows the results. Table 4-1. Speed of joining VARCHAR and ENUM columns Test Queries per second VARCHAR joined to VARCHAR 2.6 VARCHAR joined to ENUM 1.7 ENUM joined to VARCHAR 1.8 ENUM joined to ENUM 3.5 The join is faster after converting the columns to ENUM, but joining the ENUM columns to VARCHAR columns is
the IN() trick and scrap the (sex, country, age) and (sex, country, region, age) indexes. If they’re not specified in the search form, we can ensure the index prefix has equality constraints by specifying a list of all countries, or all regions for the country. (Combined lists of all countries, all regions, and all sexes would probably be too large.) These indexes will satisfy the most frequently specified search queries, but how can we design indexes for less common options, such as