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

Preface
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. Back To Basics
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. Storage Engines (Table Types)
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. Benchmarking
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. Indexes
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. Query Performance
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. Server Performance Tuning
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. Replication
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. Load Balancing and High Availability
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. Backup and Recovery
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. Security
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. The SHOW STATUS and SHOW INNODB STATUS Commands
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. mytop
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. phpMyAdmin
C.1. The BasicsC.2. Practical ExamplesC.2.1. User MaintenanceC.2.2. Simple SQL CommandsC.2.3. Exporting and Downloading Data
About the Authors
Colophon
Copyright

Content preview from High Performance MySQL

Appendix C. phpMyAdmin

There are a number of third-party user interfaces to MySQL that make it easier to access and alter the data stored in your MySQL databases. The most popular of these, by far, is phpMyAdmin , a web-based application written in PHP.

To install phpMyAdmin, you need first make sure you have a web server running PHP 4.x or later that either includes or has been configured to include MySQL database support. You will also need network connectivity to a MySQL server, even if that MySQL server happens to be on the same host as the web server running phpMyAdmin. The phpMyAdmin package can be downloaded from http://www.phpmyadmin.net/, or your Unix/Linux distribution might make a binary package available through its native package management system. Debian Linux users, for example, can simply run apt-get install phpmyadmin.

The Basics

To use phpMyAdmin to access your database, you need a username and password that are valid for connections from your web server. Your web server might be on the same machine as your MySQL server, in which case, obviously, the user only needs to be able to access the server from localhost.

Once you have logged in using a valid user account, you will see something that looks like Figure C-1.

Figure C-1. phpMyAdmin start page

As you can see in Figure C-2, there are some links to basic server information. Via the Status link, phpMyAdmin provides a ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 0596003064Errata Page

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

High Performance MySQL

by Jeremy D. Zawodny, Derek J. Balling

Appendix C. phpMyAdmin

The Basics

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.