foreword
preface
1. mysql architecture and history
mysql's logical architecture
connection management and security
optimization and execution
concurrency control
read/write locks
lock granularity
transactions
isolation levels
deadlocks
transaction logging
transactions in mysql
multiversion concurrency control
mysql's storage engines
the innodb engine
the mylsam engine
other built-in mysql engines
third-party storage engines
selecting the right engine
table conversions
a mysql timeline
mysql's development model
summary
2. benchmarking mysql
why benchmark?
benchmarking strategies
what to measure
benchmarking tactics
designing and planning a benchmark
how long should the benchmark last?
capturing system performance and status
getting accurate results
running the benchmark and analyzing results
the importance of plotting
benchmarking tools
full-stack tools
single-component tools
benchmarking examples
httpload
mysql benchmark suite
sysbench
dbt2 tpc-c on the database test suite
percona's tpcc-mysql tool
summary
3. profiling server performance
introduction to performance optimization
optimization through profiling
interpreting the profile
profiling your application
instrumenting php applications
profiling mysql queries
profiling a server's workload
profiling a single query
using the profile for optimization
diagnosing intermittent problems
single-query versus server-wide problems
capturing diagnostic data
a case study in diagnostics
other profiling tools
using the user_statistics tables
using strace
summary
4. optimizing schema and data types
choosing optimal data types
whole numbers
real numbers
string types
date and time types
bit-packed data types
choosing identifiers
special types of data
schema design gotchas in mysql
normalization and denormalization
pros and cons of a normalized schema
pros and cons of a denormalized schema
a mixture of normalized and denormalized
cache and summary tables
materialized views
counter tables
speeding up alter table
modifying only thefrm file
building mylsam indexes quickly
summary
5. indexing for high performance
indexing basics
types of indexes
benefits of indexes
indexing strategies for high performance
isolating the column
prefix indexes and index selectivity
multicolumn indexes
choosing a good column order
clustered indexes
covering indexes
using index scans for sorts
packed (prefix-compressed) indexes
redundant and duplicate indexes
unused indexes
indexes and locking
an indexing case study
supporting many, kinds of filtering
avoiding multiple range conditions
optimizing sorts
index and table maintenance
finding and repairing table corruption
updating index statistics
reducing index and data fragmentation
summary
6. query performance optimization
why are queries slow?
slow query basics: optimize data access
are you asking the database for data you don't need?
is mysql examining too much data?
ways to restructure queries
complex queries versus many queries
chopping up a query
join decomposition
query execution basics
the mysql client/server protocol
the query cache
the query optimization process
the query execution engine
returning results to the client
limitations of the mysql query optimizer
correlated subqueries
union limitations
index merge optimizations
equality propagation
parallel execution
hash joins
loose index scans
min0 and max()
select and update on the same table
query optimizer hints
optimizing specific types of queries
optimizing count() queries
optimizing join queries
optimizing subqueries
optimizing group by and distinct
optimizing limit and offset
optimizing sql_calc_found_rows
optimizing union
static query analysis
using user-defined variables
case studies
building a queue table in mysql
computing the distance between points
using user-defined functions
summary
7. advanced my$ql features
partitioned tables
how partitioning works
types of partitioning
how to use partitioning
what can go wrong
optimizing queries
merge tables
views
updatable views
performance implications of views
limitations of views
foreign key constraints
storing code inside mysql
stored procedures and functions
triggers
events
preserving comments in stored code
cursors
prepared statements
prepared statement optimization
the sql interface to prepared statements
limitations of prepared statements
user-defined functions
plugins
character sets and collations
how mysql uses character sets
choosing a character set and collation
how character sets and collations affect queries
full-text searching
natural-language full-text searches
boolean full-text searches
full-text changes in mysql 5.1
full-text tradeoffs and workarounds
full-text configuration and optimization
distributed (xa) transactions
internal xa transactions
external xa transactions
the mysql query cache
how mysql checks for a cache hit
how the cache ues memory
when the query cache is helpful
how to configure and maintain the query cache
innodb and the query cache
general query cache optimizations
alternatives to the query cache
summary
8. optimizing server settings
how mysql's configuration works
syntax, scope, and dynamism
side effects of setting variables
getting started
iterative optimization by benchmarking
what not to do
creating a mysql configuration file
inspecting mysql server status variables
configuring memory usage
how much memory can mysql use?
per-connection memory needs
reserving memory for the operating system
allocating memory for caches
the innodb buffer pool
the myisam key caches
the thread cache
the table cache
the innodb data dictionary
configuring mysql's i/o behavior
innodb i/o configuration
myisam i/o configuration
configuring mysql concurrency
innodb concurrency configuration
mylsam concurrency configuration
workload-based configuration
optimizing for blob and text workloads
optimizing for filesorts
completing the basic configuration
safety and sanity settings
advanced innodb settings
summary
9. operating system and hardware optimization
what limits mysql's performance?
how to select cpus for mysql
which is better: fast cpus or many cpus?
cpu architecture
scaling to many cpus and cores
balancing memory and disk resources
random versus sequential i/o
caching, reads, and writes
what's your working set?
finding an effective memory-to-disk ratio
choosing hard disks
solid-state storage
an overview of flash memory
flash technologies
benchmarking flash storage
solid-state drives (ssds)
pcie storage devices
other types of solid-state storage
when should you use flash?
using flashcache
optimizing mysql for solid-state storage
choosing hardware for a replica
raid performance optimization
raid failure, recovery, and monitoring
balancing hardware raid and software raid
raid configuration and caching
storage area networks and network-attached storage
san benchmarks
using a san over nfs or smb
mysql performance on a san
should you use a san?
using multiple disk volumes
network configuration
choosing an operating system
choosing a filesystem
choosing a disk queue scheduler
threading
swapping
operating system status
how to read vmstat output
how to read iostat output
other helpful tools
a cpu-bound machine
an i/o-bound machine
a swapping machine
an idle machine
summary
10. replication.
replication overview
problems solved by replication
how replication works
setting up replication
creating replication accounts
configuring the master and replica
starting the replica
initializing a replica from another server
recommended replication configuration
replicationunder the hood
statement-based replication
row-based replication
statement-based or row-based: which is better?
replication files
sending replication events to other replicas
replication filters
replication topologies
master and multiple replicas
master-master in active-active mode
master-master in active-passive mode
master-master with replicas
ring replication
master, distribution master, and replicas
tree or pyramid
custom replication solutions
replication and capacity planning
why replication doesn't help scale writes
when will replicas begin to lag?
plan to underutilize
replication administration and maintenance
monitoring replication
measuring replication lag
determining whether replicas are consistent with the master
resyncing a replica from the master
changing masters
switching roles in a master-master configuration
replication problems and solutions
errors caused by data corruption or loss
using nontransactional tables
mixing transactional and nontransactional tables
nondeterministic statements
different storage engines on the master and replica
data changes on the replica
nonunique server ids
undefined server ids
dependencies on nonreplicated data
missing temporary tables
not replicating all updates
lock contention caused by innodb locking selects
writing to both masters in master-master replication
excessive replication lag
oversized packets from the master
limited replication bandwidth
no disk space
replication limitations
how fast is replication?
advanced features in mysql replication
other replication technologies
summary
11. scaling mysql
what is scalability?
a formal definition'
scaling mysql
planning for scalability
buying time before scaling
scaling up
scaling out
scaling by consolidation
scaling by clustering
scaling back
load balancing
connecting directly
introducing a middleman
load balancing with a master and multiple replicas
summary
12. high availability
what is high availability?
what causes downtime?
achieving high availability
improving mean time between failures
improving mean time to recovery
avoiding single points of failure
shared storage orreplicated disk
synchronous mysql replication
replication-based redundancy
failover and failback
promoting a replica or switching roles
virtual ip addresses or ip takeover
middleman solutions
handling failover in the application
summary
13. mysql in the cl0ud
benefits, drawbacks, and myths of the cloud
the economics of mysql in the cloud
mysql scaling and ha in the cloud
the four fundamental resources
mysql performance in cloud hosting
benchmarks for mysql in the cloud
mysql database as a service (dbaas)
amazon rds
other dbaas solutions
summary
14. application-level optimization
common problems
web server issues
finding the optimal concurrency
caching
caching below the application
application-level caching
cache control policies
cache object hierarchies
pregenerating content
the cache as an infrastructure component
using handlersocket and memcached access
extending mysql
alternatives to mysql
summary
15. backup and recovery
why backups?
defining recovery requirements
designing a mysql backup solution
online or offiine backups?
logical or raw backups?
what to back up
storage engines and consistency
replication
managing and backing up binary logs
the binary log format
purging old binary logs safely
backing up data
making a logical backup
filesystem snapshots
recovering from a backup
restoring raw files
restoring logical backups
point-in-time recovery
more advanced recovery techniques
innodb crash recovery
backup and recovery tools
mysql enterprise backup
percona xtrabackup
mylvmbackup
zmanda recovery manager
mydumper
mysqldump
scripting backups
summary
16. tools for mysql users
interface tools
command-line utilities
sql utilities
monitoring tools
open source monitoring tools
commercial monitoring systems
command-line monitoring with innotop
summary
a. forks and variants of mysql
b. mysql server status
c. transferring large files
d. using explain
e. debugging locks
f. using sphinx with mysql
index
鄂公网安备 42010302001612号 