Sumário Itens Encontrados: 423MySQLâs Logical Architecture 1Connection Management and Security 2Optimization and Execution 3Concurrency Control 3Read/Write Locks 4Lock Granularity 4Transactions 6Isolation Levels 7Deadlocks 9Transaction Logging 10Transactions in MySQL 10Multiversion Concurrency Control 12MySQLâs Storage Engines 13The InnoDB Engine 15The MyISAM Engine 17Other Built-in MySQL Engines 19Third-Party Storage Engines 21Selecting the Right Engine 24Table Conversions 28A MySQL Timeline 29MySQLâs Development Model 332. Benchmarking MySQL 35Why Benchmark? 35Benchmarking Strategies 37What to Measure 38Benchmarking Tactics 40Designing and Planning a Benchmark 41How Long Should the Benchmark Last? 42Capturing System Performance and Status 44Getting Accurate Results 45Running the Benchmark and Analyzing Results 47The Importance of Plotting 49Benchmarking Tools 50Full-Stack Tools 51Single-Component Tools 51Benchmarking Examples 54http_load 54MySQL Benchmark Suite 55sysbench 56dbt2 TPC-C on the Database Test Suite 61Perconaâs TPCC-MySQL Tool 643. Profiling Server Performance 69Introduction to Performance Optimization 69Optimization Through Profiling 72Interpreting the Profile 74Profiling Your Application 75Instrumenting PHP Applications 77Profiling MySQL Queries 80Profiling a Serverâs Workload 80Profiling a Single Query 84Using the Profile for Optimization 91Diagnosing Intermittent Problems 92Single-Query Versus Server-Wide Problems 93Capturing Diagnostic Data 97A Case Study in Diagnostics 102Other Profiling Tools 110Using the USER_STATISTICS Tables 110Using strace 1114. Optimizing Schema and Data Types 115Choosing Optimal Data Types 115Whole Numbers 117Real Numbers 118String Types 119Date and Time Types 125Bit-Packed Data Types 127Choosing Identifiers 129Special Types of Data 131Schema Design Gotchas in MySQL 131Normalization and Denormalization 133Pros and Cons of a Normalized Schema 134Pros and Cons of a Denormalized Schema 135A Mixture of Normalized and Denormalized 136Cache and Summary Tables 136Materialized Views 138Counter Tables 139Speeding Up ALTER TABLE 141Modifying Only the .frm File 142Building MyISAM Indexes Quickly 1435. Indexing for High Performance 147Indexing Basics 147Types of Indexes 148Benefits of Indexes 158Indexing Strategies for High Performance 159Isolating the Column 159Prefix Indexes and Index Selectivity 160Multicolumn Indexes 163Choosing a Good Column Order 165Clustered Indexes 168Covering Indexes 177Using Index Scans for Sorts 182Packed (Prefix-Compressed) Indexes 184Redundant and Duplicate Indexes 185Unused Indexes 187Indexes and Locking 188An Indexing Case Study 189Supporting Many Kinds of Filtering 190Avoiding Multiple Range Conditions 192Optimizing Sorts 193Index and Table Maintenance 194Finding and Repairing Table Corruption 194Updating Index Statistics 195Reducing Index and Data Fragmentation 1976. Query Performance Optimization 201Why Are Queries Slow? 201Slow Query Basics: Optimize Data Access 202Are You Asking the Database for Data You Donât Need? 202Is MySQL Examining Too Much Data? 204Ways to Restructure Queries 207Complex Queries Versus Many Queries 207Chopping Up a Query 208Join Decomposition 209Query Execution Basics 210The MySQL Client/Server Protocol 210The Query Cache 214The Query Optimization Process 214The Query Execution Engine 228Returning Results to the Client 228Limitations of the MySQL Query Optimizer 229Correlated Subqueries 229UNION Limitations 233Index Merge Optimizations 234Equality Propagation 234Parallel Execution 234Hash Joins 234Loose Index Scans 235MIN() and MAX() 237SELECT and UPDATE on the Same Table 237Query Optimizer Hints 238Optimizing Specific Types of Queries 241Optimizing COUNT() Queries 241Optimizing JOIN Queries 244Optimizing Subqueries 244Optimizing GROUP BY and DISTINCT 244Optimizing LIMIT and OFFSET 246Optimizing SQL_CALC_FOUND_ROWS 248Optimizing UNION 248Static Query Analysis 249Using User-Defined Variables 249Case Studies 256Building a Queue Table in MySQL 256Computing the Distance Between Points 258Using User-Defined Functions 2627. Advanced MySQL Features 265Partitioned Tables 265How Partitioning Works 266Types of Partitioning 267How to Use Partitioning 268What Can Go Wrong 270Optimizing Queries 272Merge Tables 273Views 276Updatable Views 278Performance Implications of Views 279Limitations of Views 280Foreign Key Constraints 281Storing Code Inside MySQL 282Stored Procedures and Functions 284Triggers 286Events 288Preserving Comments in Stored Code 289Cursors 290Prepared Statements 291Prepared Statement Optimization 292The SQL Interface to Prepared Statements 293Limitations of Prepared Statements 294User-Defined Functions 295Plugins 297Character Sets and Collations 298How MySQL Uses Character Sets 298Choosing a Character Set and Collation 301How Character Sets and Collations Affect Queries 302Full-Text Searching 305Natural-Language Full-Text Searches 306Boolean Full-Text Searches 308Full-Text Changes in MySQL 5.1 310Full-Text Tradeoffs and Workarounds 310Full-Text Configuration and Optimization 312Distributed (XA) Transactions 313Internal XA Transactions 314External XA Transactions 315The MySQL Query Cache 315How MySQL Checks for a Cache Hit 316How the Cache Uses Memory 318When the Query Cache Is Helpful 320How to Configure and Maintain the Query Cache 323InnoDB and the Query Cache 326General Query Cache Optimizations 327Alternatives to the Query Cache 3288. Optimizing Server Settings . 331How MySQLâs Configuration Works 332Syntax,Scope,and Dynamism 333Side Effects of Setting Variables 335Getting Started 337Iterative Optimization by Benchmarking 338What Not to Do 340Creating a MySQL Configuration File 342Inspecting MySQL Server Status Variables 346Configuring Memory Usage 347How Much Memory Can MySQL Use? 347Per-Connection Memory Needs 348Reserving Memory for the Operating System 349Allocating Memory for Caches 349The InnoDB Buffer Pool 350The MyISAM Key Caches 351The Thread Cache 353The Table Cache 354The InnoDB Data Dictionary 356Configuring MySQLâs I/O Behavior 356InnoDB I/O Configuration 357MyISAM I/O Configuration 369Configuring MySQL Concurrency 371InnoDB Concurrency Configuration 372MyISAM Concurrency Configuration 373Workload-Based Configuration 375Optimizing for BLOB and TEXT Workloads 375Optimizing for Filesorts 377Completing the Basic Configuration 378Safety and Sanity Settings 380Advanced InnoDB Settings 3839. Operating System and Hardware Optimization 387What Limits MySQLâs Performance? 387How to Select CPUs for MySQL 388Which Is Better: Fast CPUs or Many CPUs? 388CPU Architecture 390Scaling to Many CPUs and Cores 391Balancing Memory and Disk Resources 393Random Versus Sequential I/O 394Caching,Reads,and Writes 395Whatâs Your Working Set? 395Finding an Effective Memory-to-Disk Ratio 397Choosing Hard Disks 398Solid-State Storage 400An Overview of Flash Memory 401Flash Technologies 402Benchmarking Flash Storage 403Solid-State Drives (SSDs) 404PCIe Storage Devices 406Other Types of Solid-State Storage 407When Should You Use Flash? 407Using Flashcache 408Optimizing MySQL for Solid-State Storage 410Choosing Hardware for a Replica 414RAID Performance Optimization 415RAID Failure,Recovery,and Monitoring 417Balancing Hardware RAID and Software RAID 418RAID Configuration and Caching 419Storage Area Networks and Network-Attached Storage 422SAN Benchmarks 423Using a SAN over NFS or SMB 424MySQL Performance on a SAN 424Should You Use a SAN? 425Using Multiple Disk Volumes 427Network Configuration 429Choosing an Operating System 431Choosing a Filesystem 432Choosing a Disk Queue Scheduler 434Threading 435Swapping 436Operating System Status 438How to Read vmstat Output 438How to Read iostat Output 440Other Helpful Tools 441A CPU-Bound Machine 442An I/O-Bound Machine 443A Swapping Machine 444An Idle Machine 44410. Replication 447Replication Overview 447Problems Solved by Replication 448How Replication Works 449Setting Up Replication 451Creating Replication Accounts 451Configuring the Master and Replica 452Starting the Replica 453Initializing a Replica from Another Server 456Recommended Replication Configuration 458Replication Under the Hood 460Statement-Based Replication 460Row-Based Replication 460Statement-Based or Row-Based: Which Is Better? 461Replication Files 463Sending Replication Events to Other Replicas 465Replication Filters 466Replication Topologies 468Master and Multiple Replicas 468Master-Master in Active-Active Mode 469Master-Master in Active-Passive Mode 471Master-Master with Replicas 473Ring Replication 473Master,Distribution Master,and Replicas 474Tree or Pyramid 476Custom Replication Solutions 477Replication and Capacity Planning 482Why Replication Doesnât Help Scale Writes 483When Will Replicas Begin to Lag? 484Plan to Underutilize 485Replication Administration and Maintenance 485Monitoring Replication 485Measuring Replication Lag 486Determining Whether Replicas Are Consistent with the Master 487Resyncing a Replica from the Master 488Changing Masters 489Switching Roles in a Master-Master Configuration 494Replication Problems and Solutions 495Errors Caused by Data Corruption or Loss 495Using Nontransactional Tables 498Mixing Transactional and Nontransactional Tables 498Nondeterministic Statements 499Different Storage Engines on the Master and Replica 500Data Changes on the Replica 500Nonunique Server IDs 500Undefined Server IDs 501Dependencies on Nonreplicated Data 501Missing Temporary Tables 502Not Replicating All Updates 503Lock Contention Caused by InnoDB Locking Selects 503Writing to Both Masters in Master-Master Replication 505Excessive Replication Lag 507Oversized Packets from the Master 511Limited Replication Bandwidth 511No Disk Space 511Replication Limitations 512How Fast Is Replication? 512Advanced Features in MySQL Replication 514Other Replication Technologies 51611. Scaling MySQL 521What Is Scalability? 521A Formal Definition 523Scaling MySQL 527Planning for Scalability 527Buying Time Before Scaling 528Scaling Up 529Scaling Out 531Scaling by Consolidation 547Scaling by Clustering 548Scaling Back 552Load Balancing 555Connecting Directly 556Introducing a Middleman 560Load Balancing with a Master and Multiple Replicas 56412. High Availability 567What Is High Availability? 567What Causes Downtime? 568Achieving High Availability 569Improving Mean Time Between Failures 570Improving Mean Time to Recovery 571Avoiding Single Points of Failure 572Shared Storage or Replicated Disk 573Synchronous MySQL Replication 576Replication-Based Redundancy 580Failover and Failback 581Promoting a Replica or Switching Roles 583Virtual IP Addresses or IP Takeover 583Middleman Solutions 584Handling Failover in the Application 58513. MySQL in the Cloud 589Benefits,Drawbacks,and Myths of the Cloud 590The Economics of MySQL in the Cloud 592MySQL Scaling and HA in the Cloud 593The Four Fundamental Resources 594MySQL Performance in Cloud Hosting 595Benchmarks for MySQL in the Cloud 598MySQL Database as a Service (DBaaS) 600Amazon RDS 600Other DBaaS Solutions 60214. Application-Level Optimization 605Common Problems 605Web Server Issues 608Finding the Optimal Concurrency 609Caching 611Caching Below the Application 611Application-Level Caching 612Cache Control Policies 614Cache Object Hierarchies 616Pregenerating Content 617The Cache as an Infrastructure Component 617Using HandlerSocket and memcached Access 618Extending MySQL 618Alternatives to MySQL 61915. Backup and Recovery 621Why Backups? 622Defining Recovery Requirements 623Designing a MySQL Backup Solution 624Online or Offline Backups? 625Logical or Raw Backups? 627What to Back Up 629Storage Engines and Consistency 632Replication 634Managing and Backing Up Binary Logs 634The Binary Log Format 635Purging Old Binary Logs Safely 636Backing Up Data 637Making a Logical Backup 637Filesystem Snapshots 640Recovering from a Backup 647Restoring Raw Files 648Restoring Logical Backups 649Point-in-Time Recovery 652More Advanced Recovery Techniques 653InnoDB Crash Recovery 655Backup and Recovery Tools 658MySQL Enterprise Backup 658Percona XtraBackup 658mylvmbackup 659Zmanda Recovery Manager 659mydumper 659mysqldump 660Scripting Backups 66116. Tools for MySQL Users 665Interface Tools 665Command-Line Utilities 666SQL Utilities 667Monitoring Tools 667Open Source Monitoring Tools 668Commercial Monitoring Systems 670Command-Line Monitoring with Innotop 672A. Forks and Variants of MySQL 679B. MySQL Server Status . 685C. Transferring Large Files 715D. Using EXPLAIN 719E. Debugging Locks 735F. Using Sphinx with MySQL 745