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
Advanced FPGA Design: Architecture, Implementation, and Optimization
Programming Windows Store Apps with HTML, CSS and JavaScript
Hello Raspberry Pi!: Python programming for kids and other beginners
Game Programming Algorithms and Techniques: A Platform-Agnostic Approach
(QPS) NOT EXISTS subquery 360 QPS LEFT OUTER JOIN 425 QPS Our benchmark found that the subquery is quite a bit slower! However, this isn’t always the case. Sometimes a subquery can be faster. For example, it can work well when you just want to see rows from one table that match rows in another table. Although that sounds like it describes a join perfectly, it’s not always the same thing. The following join, which is designed to find every film that has an actor, will return duplicates
replica), and restore it to the target machine. Then look in the backup for the correct position to start replication: If you took the backup from the new replica’s master, you can start replication from the position mentioned in the xtrabackup_binlog_pos_innodb file. If you took the backup from another replica, you can start replication from the position mentioned in the xtrabackup_slave_info file. Using InnoDB Hot Backup or MySQL Enterprise Backup, both covered in Chapter 15, is another good
mention later, such as tree replication or distribution masters, make it harder to figure out where in the logical sequence of events a replica is really replicating. Master-Master in Active-Active Mode Master-master replication (also known as dual-master or bidirectional replication) involves two servers, each configured as both a master and a replica of the other—in other words, a pair of co-masters. Figure 10-5 shows the setup. Figure 10-5. Master-master replication MySQL Does Not Support
index”This indicates that MySQL will use a covering index to avoid accessing the table. Don’t confuse covering indexes with the index access type. “Using where”This means the MySQL server will post-filter rows after the storage engine retrieves them. Many WHERE conditions that involve columns in an index can be checked by the storage engine when (and if) it reads the index, so not all queries with a WHERE clause will show “Using where.” Sometimes the presence of “Using where” is a hint that the
Schema At the time of writing, the Performance Schema tables introduced in MySQL 5.5 don’t support query-level profiling. The Performance Schema is rather new and in rapid development, with much more functionality in the works for future releases. However, even MySQL 5.5’s initial functionality can reveal interesting information. For example, here’s a query that shows the top causes of waiting in the system: mysql> SELECT event_name, count_star, sum_timer_wait -> FROM
