MySQL Forge

Resources for the MySQL Community

Category: UC2006

Certification Primer I: MySQL 5.0 DBA

Contents 1 Certification Primer I: MySQL 5.0 DBA 1.1 Prerequisites 1.2 Reminders 1.3 Slide 5: Server Architecture 1.4 Slide 6: Disk footprint 1.5 Slide 7: Memory Usage 1.6 Slide 8: Logging 1.7 Slide 9: Configuring MySQL 1.8 Slide 12: Requesting Locks 1.9 Slide 13: Locking 1.10 Slide 14: Explicit Locking 1.11 Slide 15+16: Storage Engines 1.12 Slide 17: MyISAM 1.13 Slide 18: MERGE Engine 1.14 Slide 19: InnoDB 1.15 Slide 20: MEMORY 1.16 Slide 21: FEDERATED 1.17 Slide 23: TABLE MAINTENANCE 1.18 Slide 24: Crash Recovery 1.19 Slide 25: Backup and Recovery 1.20 Slide 28: Information Schema

[edit] Certification Primer I: MySQL 5.0 DBA

A collection of notes on the individual slides. Please refer to the handouts while reading the notes (maybe someone can post a link to the online slides if they're available?)

[edit] Prerequisites

• Get a MySQL Certification ID if taking the certification exam: http://www.mysql.com/certification/signup
• Good only for the duration of the conference

[edit] Reminders

• Come early to the Napa rooms for Q&A sessions

[edit] Slide 5: Server Architecture

• Connection Thread Pool
  • Handle incoming connections
  • One user = one thread
  • Same user logged in several times = multiple threads

• Query Cache
  • Every query passed to the server is checked to see if it has been recently executed

• Parser
  • Syntax and security checks

• Optimizer
  • How best to execute it

• Storage Engine
  • Low-level storage

[edit] Slide 6: Disk footprint

• Databases
  • create a directory on the filesystem (case sensitive depending on the fs, but SQL is case-insensitive)

• Tables
  • create a .frm file that represents a table structure

• Triggers
  • create .trg and .trn files
  • Table can have up to 6 triggers (? needs verification)
  • .trg -- maps which table has which triggers

• Data and Indexes
  • storage engine-dependent (e.g. MyISAM = .MYD, .MYI; InnoDB = .BIN)

• Logs
  • in data directory by default

• Others
  • Data directory can be moved to some other location.
  • You can move tables/databases to different areas

[edit] Slide 7: Memory Usage

• Thread-specific memory settings:
  • read_buffer_size, sort_buffer_size, tmp_table_size
  • These settings affect on a per-user (thread) basis

• Global Memory Areas:
  • key_buffer_size, table_cache, thread_cache_size, etc.

[edit] Slide 8: Logging

• Error Log
  • enabled by default

• General Log
  • Enabled by --log parameter
  • This logs everything (e.g. SHOW, SELECT queries)
  • Gets very large (plain text)
  • Performance voverhead

• Binary Log
  • Enabled by --log-bin
  • Stored in binary format, can be read by mysqlbinlog
  • Used in recovery/replication
  • Can be used for point-in-time recovery (backups)

• Slow Query Log
  • Enabled by --log-slow-query
  • Any query which takes over n seconds to execute (Default: 10s)
  • Customized by --log-slow-queries
  • log-queries-not-using-indexes

[edit] Slide 9: Configuring MySQL

• Order of preference:

1. --defaults-file=/etc/my.cnf
2. run-time parameters
3. --defaults-extra-file=/some/other/file
4. Per-user configuration options (~/.my.cnf)

• Later in the order overrides the settings

[edit] Slide 12: Requesting Locks

• Locks are released on interruption/termination (for whatever reason: disconnect/network interruption/crash/etc)

[edit] Slide 13: Locking

• Table Locks (READ/WRITE lock)
  • little overhead
  • READ lock, aka SHARED lock: multiple readers, no writers
  • WRITE lock, aka EXCLUSIVE lock: one writer, no readers
  • Used by MyISAM

• Page Locks (BDB)
  • Group of rows are locked at a time
  • Allows users to read part of the table even if writes are being performed
  • Higher overhead because more locks are used
  • In a R/O environment, page locks create more work than is beneficial
  • Used by BerkeleyDB environment

• Row Locks (InnoDB)
  • Fine-grained locks: multiple writers, multiple readers
  • Unfortunately, above can lead to deadlocks (User1 locks Table A, User2 locks Table B, then User1 needs Table B and User2 needs Table A)
  • MySQL prevents the above by making each thread lock ALL tables needed before execution (but there are other deadlocks that this doesn't prevent)
  • Elevated to Table-Locks if the storage engine doesn't support it

• InnoDB uses deadlock detection and resolves the deadlock by rolling back the query with least transactions

[edit] Slide 14: Explicit Locking

• Table Locks:
  • LOCK/UNLOCK pair

• Row Locks:
  • SELECT ... [LOCK IN SHARE MODE | FOR UPDATE]
    • LOCK IN SHARE MODE = read lock
    • FOR UPDATE = exclusive lock *BUT* allows others to READ but NOT LOCK

• Use explicit locking when you know there are several changes that will be done (batch mode), because implicit locking will cause more work on a per-query basis. However, this may have a potential impact on readers.

[edit] Slide 15+16: Storage Engines

• Different storage engines implement features differently: locking, storage, backup, etc
• Changing storage engines has big impacts (CONSIDER IT DURING DESIGN STAGE)
• Default can be stored in config file: default-storage-engine | storage-engine = XXX

[edit] Slide 17: MyISAM

• Required by system tables for MySQL
• READ LOCAL lock: exclusive to MySQL, allows concurrent inserts (only works for SELECT+INSERT; DELETE implicit X-LOCK)
  • See different modes here: http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html

[edit] Slide 18: MERGE Engine

• Only works with MyISAM tables
• Max 128 Tables (prev. 64)
  • What practical use for this?

[edit] Slide 19: InnoDB

• Multi-Version Concurrency Control: Servers keep several versions of the row this allows SELECTs/UPDATEs to be done. The version is finalized on a COMMIT.

• Transaction Isolation Levels:
  • READ-COMMITED
  • READ-UNCOMMITTED
  • REPEATABLE-READ (default)
  • SERIALIZABLE

• See: http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-isolation.html

[edit] Slide 20: MEMORY

• Max size can be configured on a per-table basis (Default: 8M)
• No locking, no transaction support

[edit] Slide 21: FEDERATED

• New to 5.0
• Local server connects to a master mysql table (PULL vs PUSH (replication)?)

[edit] Slide 23: TABLE MAINTENANCE

• OPTIMIZE Table: relies on statistics generated by ANALYZE Table; takes W-LOCK

• mysqlcheck -- can do the different table maintenance routines

[edit] Slide 24: Crash Recovery

• InnoDB
  • automatic crash recovery based on binary logs

• MyISAM
  • manual, use myisam_recover if you want auto-fix on startup; can use REPAIR TABLE from within mysql client

- data consistency is NOT guaranteed

[edit] Slide 25: Backup and Recovery

• Physical backups -- per-file filesystem copy, data consistency issues, not applicable for all storage engines
• Logical -- mysqldump (more portable/flexible)
• Binary-log

[edit] Slide 28: Information Schema

• A more convenient alternative to SHOW commands since it allows you to use actual SQL queries (conditions, formatting, etc)
• Regular security privileges enforced (see only stuff you have access to)