High Performance MySQL. Optimization, Backups, Replication, Load Balancing & More Jeremy D. Zawodny, Derek J. Balling

Spis treści książki

• High Performance MySQL
  • SPECIAL OFFER: Upgrade this ebook with OReilly
  • Preface
    • The Basic Layout of This Book
      • Back to Basics
      • Things to Reference as You Read the Rest of the Book
      • Places to Tune Your Application
      • Scaling Upward After Making Changes
      • Make Sure All That Work Isnt for Naught
      • The Miscellany
    • Software Versions and Availability
    • Conventions Used in This Book
    • Using Code Examples
    • How to Contact Us
    • Acknowledgments
      • From Jeremy
      • From Derek
  • 1. Back To Basics
    • 1.1. Binary Versus Compiled-From-Source Installations
      • 1.1.1. MySQL.com Binary Versus Distribution Binary
    • 1.2. Configuration Files
      • 1.2.1. File Locations
      • 1.2.2. File Format
      • 1.2.3. Sample Files
      • 1.2.4. Reconfiguration
    • 1.3. The SHOW Commands
      • 1.3.1. SHOW VARIABLES
      • 1.3.2. SHOW PROCESSLIST
      • 1.3.3. SHOW STATUS
      • 1.3.4. SHOW INNODB STATUS
  • 2. Storage Engines (Table Types)
    • 2.1. MySQL Architecture
    • 2.2. Locking and Concurrency
      • 2.2.1. Read/Write Locks
      • 2.2.2. Lock Granularity
        • 2.2.2.1. Table locks
        • 2.2.2.2. Page locks
        • 2.2.2.3. Row locks
      • 2.2.3. Multi-Version Concurrency Control
    • 2.3. Transactions
      • 2.3.1. Benefits and Drawbacks
      • 2.3.2. Isolation Levels
        • 2.3.2.1. Read uncommitted
        • 2.3.2.2. Read committed
        • 2.3.2.3. Repeatable read
        • 2.3.2.4. Serializable
      • 2.3.3. Deadlocks
      • 2.3.4. Transaction Logging
      • 2.3.5. Transactions in MySQL
        • 2.3.5.1. AUTOCOMMIT
        • 2.3.5.2. Implicit commits
        • 2.3.5.3. Isolation levels
        • 2.3.5.4. Mixing storage engines in transactions
        • 2.3.5.5. Simulating transactions
    • 2.4. Selecting the Right Engine
      • 2.4.1. Considerations
        • 2.4.1.1. Transactions and concurrency
        • 2.4.1.2. Backups
        • 2.4.1.3. Special features
      • 2.4.2. Practical Examples
        • 2.4.2.1. Logging
        • 2.4.2.2. Read-only or read-mostly tables
        • 2.4.2.3. Order processing
        • 2.4.2.4. Stock quotes
        • 2.4.2.5. Bulletin boards and threaded discussion forums
        • 2.4.2.6. CD-ROM applications
      • 2.4.3. Table Conversions
        • 2.4.3.1. ALTER TABLE
        • 2.4.3.2. Dump and reimport
        • 2.4.3.3. CREATE and SELECT
    • 2.5. The Storage Engines
      • 2.5.1. MyISAM Tables
        • 2.5.1.1. Storage
        • 2.5.1.2. Other stuff
      • 2.5.2. Compressed MyISAM Tables
      • 2.5.3. RAID MyISAM Tables
      • 2.5.4. MyISAM Merge Tables
      • 2.5.5. InnoDB Tables
        • 2.5.5.1. Storage
        • 2.5.5.2. Locking and concurrency
        • 2.5.5.3. Special features
      • 2.5.6. Heap (In-Memory) Tables
        • 2.5.6.1. Limitations
      • 2.5.7. Berkeley DB (BDB) Tables
  • 3. Benchmarking
    • 3.1. The Importance of Benchmarking
    • 3.2. Benchmarking Strategies
    • 3.3. Benchmarking Tools
      • 3.3.1. The MySQL Benchmark Suite
      • 3.3.2. MySQL super-smack
        • 3.3.2.1. Preparing test data
        • 3.3.2.2. Configuration
      • 3.3.3. MyBench: A Home-Grown Solution
  • 4. Indexes
    • 4.1. Indexing Basics
      • 4.1.1. Index Concepts
        • 4.1.1.1. Partial indexes
        • 4.1.1.2. Multicolumn indexes
        • 4.1.1.3. Index order
        • 4.1.1.4. Indexes as constraints
        • 4.1.1.5. Clustered and secondary indexes
        • 4.1.1.6. Unique indexes versus primary keys
        • 4.1.1.7. Indexing NULLs
    • 4.2. Index Structures
      • 4.2.1. B-Tree Indexes
      • 4.2.2. Hash Indexes
      • 4.2.3. R-Tree Indexes
    • 4.3. Indexes and Table Types
      • 4.3.1. MyISAM Tables
        • 4.3.1.1. Delayed key writes
      • 4.3.2. Heap Tables
      • 4.3.3. BDB Tables
      • 4.3.4. InnoDB Tables
      • 4.3.5. Full-Text Indexes
      • 4.3.6. Index Limitations
        • 4.3.6.1. Wildcard matches
        • 4.3.6.2. Regular expressions
        • 4.3.6.3. Poor statistics or corruption
        • 4.3.6.4. Too many matching rows
    • 4.4. Index Maintenance
      • 4.4.1. Obtaining Index Information
      • 4.4.2. Refreshing Index Statistics
  • 5. Query Performance
    • 5.1. Query Processing Basics
      • 5.1.1. Query Cache
      • 5.1.2. Parsing, Analysis, and Optimization
      • 5.1.3. Using EXPLAIN
        • 5.1.3.1. Joins
      • 5.1.4. Execution
    • 5.2. Optimizer Features and Oddities
      • 5.2.1. Too Little Diversity
      • 5.2.2. Index-Based Ordering
      • 5.2.3. Impossible Queries
      • 5.2.4. Full-Text Instead of LIKE
    • 5.3. Identifying Slow Queries
    • 5.4. Influencing MySQL with Hints
      • 5.4.1. Join Order
      • 5.4.2. Index Usage
      • 5.4.3. Result Sizes
      • 5.4.4. Query Cache
    • 5.5. Stupid Query Tricks
      • 5.5.1. Two Is Better Than One
      • 5.5.2. Unions Instead of ORs
  • 6. Server Performance Tuning
    • 6.1. Performance-Limiting Factors
      • 6.1.1. Disks
      • 6.1.2. Memory
        • 6.1.2.1. MySQL's buffers and caches
      • 6.1.3. Network
    • 6.2. RAID
      • 6.2.1. Mix and Match
        • 6.2.1.1. Sample configuration
      • 6.2.2. Hardware Versus Software
      • 6.2.3. IDE or SCSI?
      • 6.2.4. RAID on Slaves
    • 6.3. Operating System
      • 6.3.1. Filesystems
        • 6.3.1.1. Journaling
        • 6.3.1.2. Other features and tweaks
        • 6.3.1.3. Choosing a filesystem
        • 6.3.1.4. FreeBSD
        • 6.3.1.5. Do you need a filesystem at all?
      • 6.3.2. Swap
      • 6.3.3. Threading
    • 6.4. Techniques
      • 6.4.1. Solving I/O Bottlenecks
        • 6.4.1.1. Wrong index
        • 6.4.1.2. Temporary tables
        • 6.4.1.3. Caching
        • 6.4.1.4. Spread the load
      • 6.4.2. Solving CPU Bottlenecks
      • 6.4.3. Solving Memory Bottlenecks
      • 6.4.4. Solving Kernel Bottlenecks
  • 7. Replication
    • 7.1. Replication Overview
      • 7.1.1. Problems Solved with Replication
        • 7.1.1.1. Data distribution
        • 7.1.1.2. Load balancing
        • 7.1.1.3. Backup and recovery
        • 7.1.1.4. High availability and failover
      • 7.1.2. Problems Not Solved with Replication
        • 7.1.2.1. Real-time data transmission
        • 7.1.2.2. Online ordering
      • 7.1.3. Replication Performance
    • 7.2. Configuring Replication
      • 7.2.1. On a New Server
        • 7.2.1.1. Account creation
        • 7.2.1.2. Configuration file entries
        • 7.2.1.3. Restart master
        • 7.2.1.4. Restart slave
      • 7.2.2. On an Existing Server
        • 7.2.2.1. What needs to happen
        • 7.2.2.2. Snapshot or backup, then copy
        • 7.2.2.3. Online table copies
        • 7.2.2.4. Online copy and synchronize (MySQL 4.x only)
    • 7.3. Under the Hood
      • 7.3.1. Replication in 3.23
      • 7.3.2. Replication in 4.0
      • 7.3.3. Files and Settings Related to Replication
        • 7.3.3.1. Log files
        • 7.3.3.2. Log index files
        • 7.3.3.3. Status files
        • 7.3.3.4. Filtering
    • 7.4. Replication Architectures
      • 7.4.1. The Replication Rules
      • 7.4.2. Sample Configurations
        • 7.4.2.1. Master with slaves
        • 7.4.2.2. Slave with two masters
        • 7.4.2.3. Dual master
        • 7.4.2.4. Replication ring (multi-master)
        • 7.4.2.5. Pyramid
        • 7.4.2.6. Design your own
    • 7.5. Administration and Maintenance
      • 7.5.1. Monitoring
        • 7.5.1.1. Master status
        • 7.5.1.2. Slave status
        • 7.5.1.3. Replication heartbeat
      • 7.5.2. Log Rotation
      • 7.5.3. Changing Masters
        • 7.5.3.1. Using the right values
      • 7.5.4. Tools
        • 7.5.4.1. mysqlbinlog: Viewing data in logs
        • 7.5.4.2. check_repl: Ensuring that replication takes place
        • 7.5.4.3. fix_repl: Skipping a bad query to continue replication
        • 7.5.4.4. purge_binary_logs: Reclaiming space used by binary logs
        • 7.5.4.5. mysqldiff: Replication sanity checks
        • 7.5.4.6. write_heartbeat: Generating a periodic health check heartbeat
        • 7.5.4.7. read_heartbeat: Measuring replication log using heartbeat
    • 7.6. Common Problems
      • 7.6.1. Slave Data Changes
      • 7.6.2. Nonunique Server IDs
      • 7.6.3. Log Corruption or Partial Log Record
      • 7.6.4. Bulk-Loading Data
      • 7.6.5. Nonreplicated Dependencies
      • 7.6.6. Missing Temporary Tables
      • 7.6.7. Binary Log Out of Sync with Transaction Log
      • 7.6.8. Slave Wants to Connect to the Wrong Master
    • 7.7. The Future of Replication
      • 7.7.1. Eliminating the Snapshot
      • 7.7.2. Fail-Safe Replication
      • 7.7.3. Safe Multi-Master Replication
        • 7.7.3.1. Multipart auto-increment unique keys
        • 7.7.3.2. Partitioned auto-increment fields
  • 8. Load Balancing and High Availability
    • 8.1. Load Balancing Basics
      • 8.1.1. Differences Between MySQL and HTTP Load Balancing
        • 8.1.1.1. Requests
        • 8.1.1.2. Partitioning
        • 8.1.1.3. Connection pooling
    • 8.2. Configuration Issues
      • 8.2.1. Health Checks
        • 8.2.1.1. Determining health
        • 8.2.1.2. Connection limits
      • 8.2.2. Next-Connection Algorithms
        • 8.2.2.1. The consequences of poor algorithm choice
    • 8.3. Cluster Partitioning
      • 8.3.1. Role-Based Partitioning
      • 8.3.2. Data-Based Partitioning
      • 8.3.3. Filtering and Multicluster Partitioning
        • 8.3.3.1. Filtering
        • 8.3.3.2. Separate clusters
    • 8.4. High Availability
      • 8.4.1. Dual-Master Replication
      • 8.4.2. Shared Storage with Standby
      • 8.4.3. Commercial Solutions
        • 8.4.3.1. Veritas cluster a gent
        • 8.4.3.2. EMIC Networks
  • 9. Backup and Recovery
    • 9.1. Why Backups?
      • 9.1.1. Disaster Recovery
      • 9.1.2. Auditing
      • 9.1.3. Testing
    • 9.2. Considerations and Tradeoffs
      • 9.2.1. Dump or Raw Backup?
      • 9.2.2. Online or Offline?
      • 9.2.3. Table Types and Consistency
      • 9.2.4. Storage Requirements
      • 9.2.5. Replication
    • 9.3. Tools and Techniques
      • 9.3.1. mysqldump
        • 9.3.1.1. Restoring
      • 9.3.2. mysqlhotcopy
        • 9.3.2.1. Restoring
      • 9.3.3. mysqlsnapshot
        • 9.3.3.1. Restoring
      • 9.3.4. InnoDB Hot Backup
      • 9.3.5. Offline Backups
        • 9.3.5.1. Restoring
      • 9.3.6. Filesystem Snapshots
    • 9.4. Rolling Your Own Backup Script
  • 10. Security
    • 10.1. Account Basics
      • 10.1.1. Privileges
        • 10.1.1.1. Global privileges
    • 10.2. The Grant Tables
      • 10.2.1. Privilege Checks
      • 10.2.2. The user Table
        • 10.2.2.1. Host matching
      • 10.2.3. The host Table
      • 10.2.4. The db Table
      • 10.2.5. The tables_priv Table
      • 10.2.6. The columns_priv Table
    • 10.3. Grant and Revoke
      • 10.3.1. Grant Mechanics
        • 10.3.1.1. System administrator account
        • 10.3.1.2. Database administrator account
        • 10.3.1.3. Average employee account
        • 10.3.1.4. Logging, write-only access
        • 10.3.1.5. Operations and monitoring
      • 10.3.2. Common Problems and Limitations
        • 10.3.2.1. Can't revoke specific privileges
        • 10.3.2.2. Host and database matching can't exclude matches
        • 10.3.2.3. Privileges don't vanish when objects do
    • 10.4. Operating System Security
      • 10.4.1. Guidelines
    • 10.5. Network Security
      • 10.5.1. Localhost-Only Connections
      • 10.5.2. Firewalling
        • 10.5.2.1. No default route
      • 10.5.3. MySQL in a DMZ
      • 10.5.4. Connection Encryption and Tunneling
        • 10.5.4.1. Virtual private networks
        • 10.5.4.2. SSL in MySQL
        • 10.5.4.3. SSH tunneling
      • 10.5.5. TCP Wrappers
      • 10.5.6. Automatic Host Blocking
    • 10.6. Data Encryption
      • 10.6.1. Hashing Passwords
      • 10.6.2. Encrypted Filesystems
      • 10.6.3. Application-Level Encryption
        • 10.6.3.1. Design issues
      • 10.6.4. Source Code Modification
    • 10.7. MySQL in a chrooted Environment
  • A. The SHOW STATUS and SHOW INNODB STATUS Commands
    • A.1. SHOW STATUS
      • A.1.1. Thread and Connection Statistics
      • A.1.2. Command Counters
      • A.1.3. Temporary Files and Tables
      • A.1.4. Data Access Patterns
      • A.1.5. MyISAM Key Buffer
      • A.1.6. File Descriptors
      • A.1.7. Query Cache
      • A.1.8. SELECTs
      • A.1.9. Sorts
      • A.1.10. Table Locking
    • A.2. SHOW INNODB STATUS
  • B. mytop
    • B.1. Overview
      • B.1.1. Thread View
      • B.1.2. Command View
      • B.1.3. Status View
    • B.2. Getting mytop
      • B.2.1. Requirements
      • B.2.2. Installation
    • B.3. Configuration and Usage
    • B.4. Common Tasks
  • C. phpMyAdmin
    • C.1. The Basics
    • C.2. Practical Examples
      • C.2.1. User Maintenance
      • C.2.2. Simple SQL Commands
      • C.2.3. Exporting and Downloading Data
  • About the Authors
  • Colophon
  • SPECIAL OFFER: Upgrade this ebook with OReilly

pokaż cały spis treści

ukryj recenzje