Efficient MySQL Performance

Kup polskie wydanie:

Spis treści ebooka

• Preface
  • Conventions Used in This Book
  • Using Code Examples
  • OReilly Online Learning
  • How to Contact Us
  • Acknowledgments
• 1. Query Response Time
  • A True Story of False Performance
  • North Star
  • Query Reporting
    • Sources
    • Aggregation
    • Reporting
      • Query profile
      • Query report
  • Query Analysis
    • Query Metrics
      • Query time
      • Lock time
      • Rows examined
      • Rows sent
      • Rows affected
      • Select scan
      • Select full join
      • Created tmp disk tables
      • Query count
    • Metadata and the Application
    • Relative Values
    • Average, Percentile, and Maximum
  • Improving Query Response Time
    • Direct Query Optimization
    • Indirect Query Optimization
  • When to Optimize Queries
    • Performance Affects Customers
    • Before and After Code Changes
    • Once a Month
  • MySQL: Go Faster
  • Summary
  • Practice: Identify Slow Queries
• 2. Indexes and Indexing
  • Red Herrings of Performance
    • Better, Faster Hardware!
    • MySQL Tuning
  • MySQL Indexes: A Visual Introduction
    • InnoDB Tables Are Indexes
    • Table Access Methods
      • Index lookup
      • Index scan
      • Table scan
    • Leftmost Prefix Requirement
    • EXPLAIN: Query Execution Plan
    • WHERE
    • GROUP BY
    • ORDER BY
    • Covering Indexes
    • Join Tables
  • Indexing: How to Think Like MySQL
    • Know the Query
    • Understand with EXPLAIN
    • Optimize the Query
    • Deploy and Verify
  • It Was a Good Index Until
    • Queries Changed
    • Excessive, Duplicate, and Unused
    • Extreme Selectivity
    • Its a Trap! (When MySQL Chooses Another Index)
  • Table Join Algorithms
  • Summary
  • Practice: Find Duplicate Indexes
• 3. Data
  • Three Secrets
    • Indexes May Not Help
      • Index scan
      • Finding rows
      • Joining tables
      • Working set size
    • Less Data Is Better
    • Less QPS Is Better
  • Principle of Least Data
    • Data Access
      • Return only needed columns
      • Reduce query complexity
      • Limit row access
      • Limit the result set
      • Avoid sorting rows
    • Data Storage
      • Only needed rows are stored
      • Every column is used
      • Every column is compact and practical
      • Every value is compact and practical
        • Minimize
        • Encode
        • Deduplicate
      • Every secondary index is used and not a duplicate
      • Only needed rows are kept
  • Delete or Archive Data
    • Tools
    • Batch Size
    • Row Lock Contention
    • Space and Time
    • The Binary Log Paradox
  • Summary
  • Practice: Audit Query Data Access
• 4. Access Patterns
  • MySQL Does Nothing
  • Performance Destabilizes at the Limit
  • Toyota and Ferrari
  • Data Access Patterns
    • Read/Write
    • Throughput
    • Data Age
    • Data Model
    • Transaction Isolation
    • Read Consistency
    • Concurrency
    • Row Access
    • Result Set
  • Application Changes
    • Audit the Code
    • Offload Reads
      • MySQL replica
      • Cache server
    • Enqueue Writes
    • Partition Data
    • Dont Use MySQL
  • Better, Faster Hardware?
  • Summary
  • Practice: Describe an Access Pattern
• 5. Sharding
  • Why a Single Database Does Not Scale
    • Application Workload
    • Benchmarks Are Synthetic
    • Writes
    • Schema Changes
    • Operations
  • Pebbles, Not Boulders
  • Sharding: A Brief Introduction
    • Shard Key
    • Strategies
      • Hash
      • Range
      • Lookup
    • Challenges
      • Transactions
      • Joins
      • Cross-shard queries
      • Resharding
      • Rebalancing
      • Online schema changes
  • Alternatives
    • NewSQL
    • Middleware
    • Microservices
    • Dont Use MySQL
  • Summary
  • Practice: Four-Year Fit
• 6. Server Metrics
  • Query Performance Versus Server Performance
  • Normal and Stable: The Best Database Is a Boring Database
  • Key Performance Indicators
  • Field of Metrics
    • Response Time
    • Rate
    • Utilization
    • Wait
    • Error
    • Access Pattern
    • Internal
  • Spectra
    • Query Response Time
    • Errors
    • Queries
      • QPS
      • TPS
      • Read/write
      • Admin
      • SHOW
    • Threads and Connections
    • Temporary Objects
    • Prepared Statements
    • Bad SELECT
    • Network Throughput
    • Replication
    • Data Size
    • InnoDB
      • History list length (metric)
      • Deadlock
      • Row lock
      • Data throughput
      • IOPS
      • Buffer pool efficiency
      • Page flushing
        • Pages
        • Page flushing
      • Transaction log
  • Monitoring and Alerting
    • Resolution
    • Wild Goose Chase (Thresholds)
    • Alert on User Experience and Objective Limits
    • Cause and Effect
  • Summary
  • Practice: Review Key Performance Indicators
  • Practice: Review Alerts and Thresholds
• 7. Replication Lag
  • Foundation
    • Source to Replica
    • Binary Log Events
    • Replication Lag
  • Causes
    • Transaction Throughput
    • Post-Failure Rebuild
    • Network Issues
  • Risk: Data Loss
    • Asynchronous Replication
    • Semisynchronous Replication
  • Reducing Lag: Multithreaded Replication
  • Monitoring
  • Recovery Time
  • Summary
  • Practice: Monitor Subsecond Lag
• 8. Transactions
  • Row Locking
    • Record and Next-Key Locks
    • Gap Locks
    • Secondary Indexes
    • Insert Intention Locks
  • MVCC and the Undo Logs
  • History List Length
  • Common Problems
    • Large Transactions (Transaction Size)
    • Long-Running Transactions
    • Stalled Transactions
    • Abandoned Transactions
  • Reporting
    • Active Transactions: Latest
    • Active Transactions: Summary
    • Active Transaction: History
    • Committed Transactions: Summary
  • Summary
  • Practice: Alert on History List Length
  • Practice: Examine Row Locks
• 9. Other Challenges
  • Split-Brain Is the Greatest Risk
  • Data Drift Is Real but Invisible
  • Dont Trust ORM
  • Schemas Always Change
  • MySQL Extends Standard SQL
  • Noisy Neighbors
  • Applications Do Not Fail Gracefully
  • High Performance MySQL Is Difficult
  • Practice: Identify the Guardrails that Prevent Split-Brain
  • Practice: Check for Data Drift
  • Practice: Chaos
• 10. MySQL in the Cloud
  • Compatibility
  • Management (DBA)
  • Network and StorageLatency
  • Performance Is Money
  • Summary
  • Practice: Try MySQL in the Cloud
• Index

pokaż cały spis treści

ukryj recenzje