High Performance MySQL: Optimization, Backups, and Replication

High Performance MySQL: Optimization, Backups, and Replication

Baron Schwartz

Language: English

Pages: 826

ISBN: 1449314287

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













based on its server ID is the server that created it. In general, rings are brittle and best avoided, no matter how clever you are. Figure 10-9. A replication ring topology You can mitigate some of the risk of a ring replication setup by adding replicas to provide redundancy at each site, as shown in Figure 10-10. This merely protects against the risk of a server failing, though. A loss of power or any other problem that affects any connection between the sites will still break the entire ring.

for replacing a master with one of its replicas. The first is when it’s a planned promotion; the second is when it’s unplanned. Planned promotions Promoting a replica to a master is conceptually simple. Briefly, here are the steps involved: Stop writes to the old master. Optionally let its replicas catch up in replication (this makes the subsequent steps simpler). Configure a replica to be the new master. Point replicas and write traffic to the new master, then enable writes on it. The

downtime, for example. The problem is that these terms don’t mean the same things to everyone. Some tools even use the word “hot” in their names, but definitely don’t perform what we consider to be hot backups. We try to avoid these terms and instead tell you how much a specific technique or tool interrupts your server. Two other confusing words are restore and recover. We use them in specific ways in this chapter. Restoring means retrieving data from a backup and either loading it into MySQL or

users, load test data into the database, and populate tables with randomly generated data. Benchmarks are contained in “smack” files, which use a simple language to define clients, tables, queries, and so on. Database Test SuiteThe Database Test Suite, designed by The Open Source Development Labs (OSDL) and hosted on SourceForge at http://sourceforge.net/projects/osdldbt/, is a test kit for running benchmarks similar to some industry-standard benchmarks, such as those published by the

to allocate the maximum possible space for each value even when it’s a variable-length field.[43] However, the padding and trimming behavior is consistent across storage engines, because the MySQL server itself handles that. The sibling types for CHAR and VARCHAR are BINARY and VARBINARY, which store binary strings. Binary strings are very similar to conventional strings, but they store bytes instead of characters. Padding is also different: MySQL pads BINARY values with \0 (the zero byte)

Download sample