book

High Performance MySQL

by Jeremy D. Zawodny, Derek J. Balling

April 2004

Intermediate to advanced

294 pages

8h 19m

English

O'Reilly Media, Inc.

Read now

Unlock full access

The Basic Layout of This BookBack to BasicsThings to Reference as You Read the Rest of the BookPlaces to Tune Your ApplicationScaling Upward After Making ChangesMake Sure All That Work Isn’t for NaughtThe MiscellanySoftware Versions and AvailabilityConventions Used in This BookUsing Code ExamplesHow to Contact UsAcknowledgmentsFrom JeremyFrom Derek
1.1. Binary Versus Compiled-From-Source Installations1.1.1. MySQL.com Binary Versus Distribution Binary1.2. Configuration Files1.2.1. File Locations1.2.2. File Format1.2.3. Sample Files1.2.4. Reconfiguration1.3. The SHOW Commands1.3.1. SHOW VARIABLES1.3.2. SHOW PROCESSLIST1.3.3. SHOW STATUS1.3.4. SHOW INNODB STATUS
2.1. MySQL Architecture2.2. Locking and Concurrency2.2.1. Read/Write Locks2.2.2. Lock Granularity2.2.2.1. Table locks2.2.2.2. Page locks2.2.2.3. Row locks2.2.3. Multi-Version Concurrency Control2.3. Transactions2.3.1. Benefits and Drawbacks2.3.2. Isolation Levels2.3.2.1. Read uncommitted2.3.2.2. Read committed2.3.2.3. Repeatable read2.3.2.4. Serializable2.3.3. Deadlocks2.3.4. Transaction Logging2.3.5. Transactions in MySQL2.3.5.1. AUTOCOMMIT2.3.5.2. Implicit commits2.3.5.3. Isolation levels2.3.5.4. Mixing storage engines in transactions2.3.5.5. Simulating transactions2.4. Selecting the Right Engine2.4.1. Considerations2.4.1.1. Transactions and concurrency2.4.1.2. Backups2.4.1.3. Special features2.4.2. Practical Examples2.4.2.1. Logging2.4.2.2. Read-only or read-mostly tables2.4.2.3. Order processing2.4.2.4. Stock quotes2.4.2.5. Bulletin boards and threaded discussion forums2.4.2.6. CD-ROM applications2.4.3. Table Conversions2.4.3.1. ALTER TABLE2.4.3.2. Dump and reimport2.4.3.3. CREATE and SELECT2.5. The Storage Engines2.5.1. MyISAM Tables2.5.1.1. Storage2.5.1.2. Other stuff2.5.2. Compressed MyISAM Tables2.5.3. RAID MyISAM Tables2.5.4. MyISAM Merge Tables2.5.5. InnoDB Tables2.5.5.1. Storage2.5.5.2. Locking and concurrency2.5.5.3. Special features2.5.6. Heap (In-Memory) Tables2.5.6.1. Limitations2.5.7. Berkeley DB (BDB) Tables
3.1. The Importance of Benchmarking3.2. Benchmarking Strategies3.3. Benchmarking Tools3.3.1. The MySQL Benchmark Suite3.3.2. MySQL super-smack3.3.2.1. Preparing test data3.3.2.2. Configuration3.3.3. MyBench: A Home-Grown Solution
4.1. Indexing Basics4.1.1. Index Concepts4.1.1.1. Partial indexes4.1.1.2. Multicolumn indexes4.1.1.3. Index order4.1.1.4. Indexes as constraints4.1.1.5. Clustered and secondary indexes4.1.1.6. Unique indexes versus primary keys4.1.1.7. Indexing NULLs4.2. Index Structures4.2.1. B-Tree Indexes4.2.2. Hash Indexes4.2.3. R-Tree Indexes4.3. Indexes and Table Types4.3.1. MyISAM Tables4.3.1.1. Delayed key writes4.3.2. Heap Tables4.3.3. BDB Tables4.3.4. InnoDB Tables4.3.5. Full-Text Indexes4.3.6. Index Limitations4.3.6.1. Wildcard matches4.3.6.2. Regular expressions4.3.6.3. Poor statistics or corruption4.3.6.4. Too many matching rows4.4. Index Maintenance4.4.1. Obtaining Index Information4.4.2. Refreshing Index Statistics
5.1. Query Processing Basics5.1.1. Query Cache5.1.2. Parsing, Analysis, and Optimization5.1.3. Using EXPLAIN5.1.3.1. Joins5.1.4. Execution5.2. Optimizer Features and Oddities5.2.1. Too Little Diversity5.2.2. Index-Based Ordering5.2.3. Impossible Queries5.2.4. Full-Text Instead of LIKE5.3. Identifying Slow Queries5.4. Influencing MySQL with Hints5.4.1. Join Order5.4.2. Index Usage5.4.3. Result Sizes5.4.4. Query Cache5.5. Stupid Query Tricks5.5.1. Two Is Better Than One5.5.2. Unions Instead of ORs
6.1. Performance-Limiting Factors6.1.1. Disks6.1.2. Memory6.1.2.1. MySQL’s buffers and caches6.1.3. Network6.2. RAID6.2.1. Mix and Match6.2.1.1. Sample configuration6.2.2. Hardware Versus Software6.2.3. IDE or SCSI?6.2.4. RAID on Slaves6.3. Operating System6.3.1. Filesystems6.3.1.1. Journaling6.3.1.2. Other features and tweaks6.3.1.3. Choosing a filesystem6.3.1.4. FreeBSD6.3.1.5. Do you need a filesystem at all?6.3.2. Swap6.3.3. Threading6.4. Techniques6.4.1. Solving I/O Bottlenecks6.4.1.1. Wrong index6.4.1.2. Temporary tables6.4.1.3. Caching6.4.1.4. Spread the load6.4.2. Solving CPU Bottlenecks6.4.3. Solving Memory Bottlenecks6.4.4. Solving Kernel Bottlenecks
7.1. Replication Overview7.1.1. Problems Solved with Replication7.1.1.1. Data distribution7.1.1.2. Load balancing7.1.1.3. Backup and recovery7.1.1.4. High availability and failover7.1.2. Problems Not Solved with Replication7.1.2.1. Real-time data transmission7.1.2.2. Online ordering7.1.3. Replication Performance7.2. Configuring Replication7.2.1. On a New Server7.2.1.1. Account creation7.2.1.2. Configuration file entries7.2.1.3. Restart master7.2.1.4. Restart slave7.2.2. On an Existing Server7.2.2.1. What needs to happen7.2.2.2. Snapshot or backup, then copy7.2.2.3. Online table copies7.2.2.4. Online copy and synchronize (MySQL 4.x only)7.3. Under the Hood7.3.1. Replication in 3.237.3.2. Replication in 4.07.3.3. Files and Settings Related to Replication7.3.3.1. Log files7.3.3.2. Log index files7.3.3.3. Status files7.3.3.4. Filtering7.4. Replication Architectures7.4.1. The Replication Rules7.4.2. Sample Configurations7.4.2.1. Master with slaves7.4.2.2. Slave with two masters7.4.2.3. Dual master7.4.2.4. Replication ring (multi-master)7.4.2.5. Pyramid7.4.2.6. Design your own7.5. Administration and Maintenance7.5.1. Monitoring7.5.1.1. Master status7.5.1.2. Slave status7.5.1.3. Replication heartbeat7.5.2. Log Rotation7.5.3. Changing Masters7.5.3.1. Using the right values7.5.4. Tools7.5.4.1. mysqlbinlog: Viewing data in logs7.5.4.2. check_repl: Ensuring that replication takes place7.5.4.3. fix_repl: Skipping a bad query to continue replication7.5.4.4. purge_binary_logs: Reclaiming space used by binary logs7.5.4.5. mysqldiff: Replication sanity checks7.5.4.6. write_heartbeat: Generating a periodic health check heartbeat7.5.4.7. read_heartbeat: Measuring replication log using heartbeat7.6. Common Problems7.6.1. Slave Data Changes7.6.2. Nonunique Server IDs7.6.3. Log Corruption or Partial Log Record7.6.4. Bulk-Loading Data7.6.5. Nonreplicated Dependencies7.6.6. Missing Temporary Tables7.6.7. Binary Log Out of Sync with Transaction Log7.6.8. Slave Wants to Connect to the Wrong Master7.7. The Future of Replication7.7.1. Eliminating the Snapshot7.7.2. Fail-Safe Replication7.7.3. Safe Multi-Master Replication7.7.3.1. Multipart auto-increment unique keys7.7.3.2. Partitioned auto-increment fields
8.1. Load Balancing Basics8.1.1. Differences Between MySQL and HTTP Load Balancing8.1.1.1. Requests8.1.1.2. Partitioning8.1.1.3. Connection pooling8.2. Configuration Issues8.2.1. Health Checks8.2.1.1. Determining health8.2.1.2. Connection limits8.2.2. Next-Connection Algorithms8.2.2.1. The consequences of poor algorithm choice8.3. Cluster Partitioning8.3.1. Role-Based Partitioning8.3.2. Data-Based Partitioning8.3.3. Filtering and Multicluster Partitioning8.3.3.1. Filtering8.3.3.2. Separate clusters8.4. High Availability8.4.1. Dual-Master Replication8.4.2. Shared Storage with Standby8.4.3. Commercial Solutions8.4.3.1. Veritas cluster a gent8.4.3.2. EMIC Networks
9.1. Why Backups?9.1.1. Disaster Recovery9.1.2. Auditing9.1.3. Testing9.2. Considerations and Tradeoffs9.2.1. Dump or Raw Backup?9.2.2. Online or Offline?9.2.3. Table Types and Consistency9.2.4. Storage Requirements9.2.5. Replication9.3. Tools and Techniques9.3.1. mysqldump9.3.1.1. Restoring9.3.2. mysqlhotcopy9.3.2.1. Restoring9.3.3. mysqlsnapshot9.3.3.1. Restoring9.3.4. InnoDB Hot Backup9.3.5. Offline Backups9.3.5.1. Restoring9.3.6. Filesystem Snapshots9.4. Rolling Your Own Backup Script

10.1. Account Basics10.1.1. Privileges10.1.1.1. Global privileges10.2. The Grant Tables10.2.1. Privilege Checks10.2.2. The user Table10.2.2.1. Host matching10.2.3. The host Table10.2.4. The db Table10.2.5. The tables_priv Table10.2.6. The columns_priv Table10.3. Grant and Revoke10.3.1. Grant Mechanics10.3.1.1. System administrator account10.3.1.2. Database administrator account10.3.1.3. Average employee account10.3.1.4. Logging, write-only access10.3.1.5. Operations and monitoring10.3.2. Common Problems and Limitations10.3.2.1. Can’t revoke specific privileges10.3.2.2. Host and database matching can’t exclude matches10.3.2.3. Privileges don’t vanish when objects do10.4. Operating System Security10.4.1. Guidelines10.5. Network Security10.5.1. Localhost-Only Connections10.5.2. Firewalling10.5.2.1. No default route10.5.3. MySQL in a DMZ10.5.4. Connection Encryption and Tunneling10.5.4.1. Virtual private networks10.5.4.2. SSL in MySQL10.5.4.3. SSH tunneling10.5.5. TCP Wrappers10.5.6. Automatic Host Blocking10.6. Data Encryption10.6.1. Hashing Passwords10.6.2. Encrypted Filesystems10.6.3. Application-Level Encryption10.6.3.1. Design issues10.6.4. Source Code Modification10.7. MySQL in a chrooted Environment
A.1. SHOW STATUSA.1.1. Thread and Connection StatisticsA.1.2. Command CountersA.1.3. Temporary Files and TablesA.1.4. Data Access PatternsA.1.5. MyISAM Key BufferA.1.6. File DescriptorsA.1.7. Query CacheA.1.8. SELECTsA.1.9. SortsA.1.10. Table LockingA.2. SHOW INNODB STATUS
B.1. OverviewB.1.1. Thread ViewB.1.2. Command ViewB.1.3. Status ViewB.2. Getting mytopB.2.1. RequirementsB.2.2. InstallationB.3. Configuration and UsageB.4. Common Tasks
C.1. The BasicsC.2. Practical ExamplesC.2.1. User MaintenanceC.2.2. Simple SQL CommandsC.2.3. Exporting and Downloading Data

Content preview from High Performance MySQL

Appendix B. mytop

This appendix is a basic reference for Version 1.4 of mytop, a tool you can use to monitor various aspects of MySQL. mytop began as a simple Perl script that Jeremy wrote back in 2000 after getting sick of repeatedly running SHOW FULL PROCESSLIST and SHOW STATUS in an attempt to get a handle on what a MySQL was doing. After a bit of hacking on it, he realized that it would be useful it the tool felt a bit like the Unix top utility. Since then it has evolved to become quite a bit more popular and powerful. It is especially useful when tracking down problematic queries or trying to figure out what’s keeping your server so busy.

mytop is an evolving piece of software. Be sure to check the mytop web site (http://jeremy.zawodny.com/mysql/mytop/) for mytop news, downloads, and information about the mailing list. It’s likely that new features have been added since Version 1.4.

Note that when discussing “queries” in this chapter (and many other places in the book), we’re doing so in a general sense: SELECT, INSERT, UPDATE, and DELETE.

Overview

mytop does much of the hard work involved in summarizing MySQL performance data. There are three primary display modes in mytop. The default, thread view (or top view), closely resembles the Unix top command, as seen in Figure B-1. It produces a multiline summary at the top of the screen followed by a listing of threads in MySQL. The command view aggregates the data from MySQL’s Com_* command counters (see Appendix A), as seen in ...