Laurel Fan

Here are some links to things mentioned at the MySQL Performance Tuning class that I’ve been attending this past week.

Day 1 Morning: MySQL website, basics, upsell for more training classes and certifications

• Documentation pages for all options and variables and the system variables page (which indicates which ones are dynamic).

Day 1 Afternoon: architecture overview

• Documentation for the innodb transaction model
  • multiple versions of uncommitted rows are stored on disk
• SHOW TABLE STATUS shows statistics like table size, number of rows (it’s actually an approximation)

Day 2 Morning: data types, benchmarking, logs, admin tools, indexes

• slow query log and mysqldumpslow (which summarizes the information in the slow query log)
• PROCEDURE ANALYSE, which tries to figure out the optimal data type for the columns of a table
• Benchmarking tools: mysqlslap, which comes with 5.1 and might not work with 4.1 and Super Smack
• mysqlreport, a more human readable version of SHOW STATUS
• mytop, a top-view of SHOW PROCESSLIST
• innotop monitors all kinds of innodb stuff
• Maatkit contains essential command-line tools for MySQL, such as table checksums, a query profiler, and a visual EXPLAIN tool. It provides missing features such as checking whether slaves have the same data as the master.

Day 2 Afternoon: statement tuning, query cache

• EXPLAIN EXTENDED and then SHOW WARNINGS will show you the actual SQL being executed (which can be different if the optimizer wants to rewrite it).
• Query cache documentation

Day 3 Morning: server configuration

• table_cache setting (aka table_definition_cache and table_open_cache in 5.1) how many file descriptors are used to open table files.
• max_connections
• open_files_limit
• thread_cache_size

Day 3 Afternoon: MyISAM

• MyISAM storage engine
• spatial index for efficiently searching geographical data
• MyISAM key cache for caching MyISAM indexes
• MERGE storage engine concatenates (like UNION ALL not UNION) more than one MyISAM table with the same schema

Day 4 Morning: InnoDB, Transactions, Locking

• InnoDB storage engine
• Configuring InnoDB to use a separate file per table (instead of the default of one shared ibdata file for all tables)
• Partitioning (new in 5.1), partitions a table over multiple files.
• Advisory locks are semaphores implemented in MySQL (not InnoDB specific). These don’t affect table locks.
• Next key/gap locks means that when a locking SELECT on a range of indexed values is done, the table is also locked for INSERTs within that range (and the gap before that range!).
• doublewrite setting
• innodb_flush_log_at_trx_commit
• Disk I/O
• InnoDB status and monitors that print information about the InnoDB internal state
• More about SHOW INNODB STATUS

Day 4 Afternoon: Other Storage Engines: Memory, Blackhole, CSV, Falcon

• Memory is a table that’s stored completely in memory
• Blackhole is like /dev/null
• CSV yes, Comma Separated Value file
• Falcon the 6.0 storage engine that will solve all of our perf problems
• Example for writing your own storage engine