On Thu, Jan 10, 2002 at 08:32:03AM -0600, Rick Emery wrote:
> Jeremy,
>
> Late last year, you were creating the Table of Contents for a book
> about MySQL...how's it coming? I know that many on this list were
> anxious to get a sneak peak, and perhaps suggest additional topics,
> such as advantages and conditions for using INNODB and BDB tables
> versus MYISAM. And how to install, configure and use MySQL ODBC
> drivers. Perhaps even how to access MS Access databases through
> MySQL.
Hi Rick,
I'll more information about the book on the web site I'm setting up
for it (advanced-mysql.com). The domain is being registered today, so
it'll be up over the weekend.
In the meantime, here's the last TOC outline I had put together.
Comments are welcome, of course.
---snip---
Title: Advanced MySQL (tentative)
Chapter 0: Introduction
Goals of this book
* explain how mysql works "under the hood"
* understand why it is fast what can slow it down (at all levels)
* discuss real-world problems and solutions
* expose good practices for use in large or demanding environments
* discuss MySQL's weaknesses and how to work around them
What you need to know (and why)
* basic sql
* mysql installation and administration
* a scripting language (such as perl or python)
What you won't read about (and why)
* programming languages
* the mysql api
* extending or embedding mysql
* clustering
* commercial load-balancing solutions (there's a book on that)
Chapter Overview
Chapter 1: Configuration Basics (see Questions below)
my.cnf, MySQL's Configuration File
MySQL's Startup Options
SHOW VARIABLES and SHOW STATUS
Chapter 2: MySQL's Table Types (or Handlers?)
MySQL's Modular Architecture
* core services
- sql parser
- functions
- logging
* table handlers
- data access
- functionality differences
* design benefits
Locking and Concurrency
* table vs. page vs. block vs. row (granularity)
* read vs. write (exclusivity)
* multi-versioning
Transactional vs Non-Transactional
* acid features
- atomicity
- consistency
- isolation
- durability
* tradeoffs
- concurrency
- performance
* simulating transactions with table locks (maybe a sidebar?)
* isolation levels
The Table Handlers
* MyISAM (compressed MyISAM, and MERGE too)
* HEAP
* BDB (Berkeley DB)
* InnoDB
* Gemini
* (possibly include a table which summarizes the features of all?)
Selecting the Right Table Type
Chapter 3: Indexes
What are Indexes?
* unique / non-unique indexes
* unique non-indexes (in 4.x according to monty)
* primary keys
* partial indexes
* compound (multi-part) indexes
* packed keys
How MySQL Stores and Maintains Indexes
* storage requirements
* common prefixes and space compression
* where indexes are stored (for each table type)
* hash indexes on heap tables
* the myisam key buffer and innodb/gemini buffer pools
* performance impact: updates vs. reads
- discuss delayed key writes for myisam
When Does MySQL Use an Index?
* the most specific index
* exact matches
* ranges
* the 40% rule
* left-most prefixes
* finding unused indexes
* order by and group by queries
* distinct queries
* queries using only indexed columns
When MySQL Never Uses and Index
* regular expressions
* wildcard prefix matches
Understanding and Maintaining Your Indexes
* Index File Sizes
* SHOW KEYS
* Cardinality
* ANALYZE TABLE
* Index Performance Counters
* Index Buffers
Full-text Indexing
Chapter 4: Making Queries Fast
How MySQL Processes Queries
* query cache (new in 4.0 tree)
* parsing
* optimization & planning
* execution
Identifying Slow Queries
* the slow query log
* using mysqldumpslow
* using explain_log (new in 4.0)
Using and Understanding EXPLAIN
* single table select with index
* single table select with no index
* multi-way joins
Influencing MySQL's Choices
* straight joins
* index hints in queries
* analyze tables for better statistics
* query hints
- SQL_BIG_RESULT
- SQL_SMALL_RESULT
- SQL_BUFFER_RESULT
* avoiding the query cache (from monty)
Built-in Optimizations
* the 40% rule
* index only selects
* table-specific
- innodb primary keys
- gemini indexes
* others (must ask monty & others what to include)
* distinct and order by
* join order optimizations (from monty)
Chapter 5: Application Design and Performance
Factors Affecting Application Performance
* database design (normalization)
* fast/slow queries (and index usage)
* language choice
* table types: locking/concurrency
* server performance problems (see chapter 7)
Normalization and Good Design
* what is a good design (refer to chapter in other book)
* why normalization helps
* when to de-normalize for the sake of performance
- fixed and variable row sizes
- dealing with blob data
* living with a poor design
Choosing the Right Language
* compiled vs. scripting
* data conversion overhead (hashes, arrays)
* prepared queries
Choosing the Right Table Type
* what do you really need?
* mixing and matching
* general recommendations
Using Transactions for Increased Performance
Data Partitioning
Misc Tips and Tricks (need to re-org this)
* compressed tables
* delayed key writes
* application-level caching
* temporary and heap tables
* networking and connectivity
* heap tables
* delayed and low priority inserts
* bulk inserts (new in 4.0)
* re-creating indexes via alter table or load data
* (whatever else doesn't fit elsewhere?)
Chapter 6: Server Performance
Factors Affecting Server Performance
* available memory
* dedicated or shared
* os choice
* filesystem type and layout
* disks and io
* cpu speed
* network setup
Hardware Often isn't the Solution
How MySQL Uses Memory
* Global Memory
* Per-Thread Memory
Tunable Parameters
put list here of the common ones, discuss each. refer to the
appendix for the rest (monty has some in mind after the 4.0.2
release)
Filesystem Choices
* journaling? (reiserfs, ext3, jfs, xfs, veritas, soft updates)
* raw partitions
* directory sizes
* running mysql over nfs
To RAID or not to RAID?
* depends on the workload
* data vs. logs
* software or hardware?
* raid 0, 1, 0+1, 5 (similar to IMAP book discussion)
Chapter 7: Benchmarking and Analysis
Why Benchmark?
What to Watch
* Server
- memory
- cpu
- disk and network i/o
- swapping
* MySQL
- SHOW STATUS
- key cache
- slow queries
- table scans
- open/closed tables
* Application
- latency
- overall performance
Tools
* mysql-super-smack
* mysqlbench
* mysqlbinlog
* mytop
* fork_big.pl
Scenarios
* General Benchmarking
- checks overall settings
- determine which table type is faster
* Single-User vs. Multi-User
* Stress Testing
- how do things scale?
- what breaks first
- what to change?
* Application Testing
- real users vs. simulation
* Border Cases and "Hot Spots"
Chapter 8: Replication
What is replication?
When is it useful?
* Load Balancing (sort of)
* Data Distribution
* Backups
* High(er) Availability
- other options
- lvs, shared scsi, etc.
What isn't it useful?
* Solving update performance problems
* Real-time, high performance needs
- replication is async.
- latency is highly variable
* (more in the "Known Problems" section)
How does it work?
* On the Master
- binary log
- server-id
- filtering
- SHOW MASTER STATUS
* On the Slave
- master.info
- connect to master, wait for updates
- server-id
- filtering
- SHOW SLAVE STATUS
* Master Elections (in version 4.x)
Replication Architectures
* Single Master/One or More Slaves
* Tree
* Two-master
* Multi-master
Monitoring Replication
* Using a Heartbeat
* Watching Logs
Switching Masters
Maintenance
* Log rotation and purging
* Snapshots
* Fixing Problems
Related Tools
* mysqlsnapshot
* mysqlbinlog
* mysqlmanager (in version 4.x)
Known Problems
* single threaded / slow query bottleneck
* silent death
* lack of read-ahead on slaves
* filtering is client side
* single log on the master
* master.info confusion
* multi-master isn't ready
Future Enhancements
* multi-master
* read-ahead on the slaves
* failsafe replication
* master elections
Chapter 9: Multi-Daemon Setups
* Many "Virtual" MySQL Servers
* Sharing the same Data Directory
Chapter 10: Maintenance and Upgrades
Automating Maintenance
* Optimizing Tables
* Checking Tables for Errors
* Log Rotation
- error and query logs
- binary logs
Why Upgrade?
* Minor Upgrades
* Major Upgrades
Before Upgrading
* Reading the Changelog
* Testing
* Backups
Upgrade Methods
* Server Only
* Server and Data
* Minimizing Down-Time with Symlinks
Chapter 11: Backup and Recovery
Selecting a Backup Strategy
* Frequency
* Automation
* Trade-offs (space/recovery speed, etc)
* (pull important bits from the other book for here)
Backup Issues
* Consistency
* On-line or Off-line
* Table Types
- MyISAM
- HEAP
- BDB
- InnoDB
- Gemini
* Replication
* Filesystem Snapshots (Veritas, NetApp, LVM)
Tools
* mysqldump
* mysqlhotcopy
* mysqlsnapshot
Recovery
Chapter 12: Security
Managing accounts
* How the Privilege System Works
* Granting Only Necessary Privileges
OS Permissions
* Using MySQL Account
* Configuration Files
* Data Files and Logs
Encrypting Connections
* SSL support (in version 4.x)
- certificate installation
- other certificate issues
* Tunneling with SSH
* Tunneling STunnel
Encrypting Data
* Filesystem-Based Encryption
* Encrypting Fields
* Hashing
chroot()ed MySQL
* Why chroot?
* Setting Up the Jail
* Configuring and Building MySQL
Appendix A: MySQL Server Variables
List and describe the various variables that can be adjusted for
MySQL. Make recommendations. Possibly contribute this back to
the MySQL manual.
Appendix B: MySQL Benchmark Results
List the detailed results from the benchmarks used elsewhere in
the book.
Appendix C: MySQL Tools
If necessary, describe some of the tools used in the book. I
don't know if this is necessary or not, but it can't hurt to plan
for right now.
Questions
---------
1. Monty suggested that I include a few pages of "refresher" material.
It's stuff that folks ought to know already but may not. So I'm
not sure where to include it quite yet. I've added a short
"Chapter 1.5" (between 1 and 2) which contains the material for
now.
2. How to cover "common problems" in a way that folks can find them
without reading the whole book? Maybe this is just an indexing
issue. An item that Monty brought up is MySQL's non-graceful way
of handles running out of disk space. That'll come up somewhere,
but where would someone expect to find it? Should there be a list
of things that every MySQL DBA needs to know? Maybe a short list
that says "read chapter XX to learn more about this topic" and
"read chapter YY to learn more about that topic"?
3. Chapter 9 needs a better name. And a bit more thought. (Those
aren't questions, exactly.) But I'm partly waiting to see what the
group attempting this at Yahoo finds out. Is it okay to leave it
slightly up in the air at this point?
Notes
-----
1. Each chapter will lead off with a paragraph or two (or more) about
what the chapter covers, what someone will learn from it, and so
on.
---snip---
Jeremy
--
Jeremy D. Zawodny, <[EMAIL PROTECTED]>
Technical Yahoo - Yahoo Finance
Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936
MySQL 3.23.41-max: up 7 days, processed 190,145,153 queries (288/sec. avg)
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
