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
鄂公網(wǎng)安備 42010302001612號(hào) 