MySQL パフォーマンスチューニング&ベンチマーク - MySQLカンファレンス2007


  • An introduction to Benchmarking
  • data structures
  • query optimisation and query cache
  • etc..

Why Benchmark?

  • Allows tracking of performance over time
    • application
    • SQL snippet
    • ..
  • You get load and stress information
  • ever wondered if for the job InnoDB or MyISAM would be better? Or if running on Linux or FreeBSD made more sense?

The Good Scientists Guide to Benchmarking

  • The scientific method suggests changing only one variable at a time
  • The scientific method suggests repetition, more than once to verify results. If results vary greatly, think about taking averages
    • Repeat, rinse , repeat , rinse!
    • 少なくとも3回は。

The Good Scientists Guide to Benchmarking II

  • Isolate your environment
  • Use a different MySQL instance
  • Savle all configurations!

Benchmarking Tools

  • super-smack
    • Flexible tool for measuring SQL script performance
  • mysqlslap (like ab; in MySQL 5.1)
  • MyBench
  • SysBench
    • For raw comparisons of different MySQL versions/platforms
  • Apache Bench

Benchmarking Tools II

  • SHOW commands in MySQL
    • SHOW PROFILE - in 5.0.37 and above, Community Contribution, Linux only
  • EXPLAIN and the Slow Query Log
  • MyTop
  • vmstat/ps/top/gprof/oprofile (and contents of procinfo)

Slow Query Log

  • log_slow_queries=/var/lib/mysql/slow-queries.log
  • long_query_time=2
  • Then, use mysqldumpslow
  • In 5.1, you can log these details ...


EXPLAIN basics

  • Provides the execution plan chosen by the MySQL optimiser for a...


EXPLAIN columns

  • select_type
  • table
  • ...


Scans and seeks

  • A seek, jumps into a random place to fetch needed data.
  • A scan will jump to the start of the data...

When do you get a full table scan?

  • No WHERE conditions
  • No index on any field in WHERE condition
  • When your range returns a large number of rows, i.e. too many records in WHERE condition
  • Pre-MySQL 5, using OR in a WHERE clause


  • Don't use them; replace with a JOIN
    • unique_subquery: results are known to be distinct
    • index_subquery: otherwise
  • Co-related subqueries are worse
    • ...


  • Covering index ...
  • Remember that when using InnoDB, use a small PK value
  • Alwaays, add a Primary Key

Good Schema Practice

  • Use small data types
    • Is a BIGINT really required?
  • Small data types allow more index and data recoreds to fit into a single block of memory
  • Normalise first, de-normalise later
    • Generally, 3NF works pretty well


Storing IP addresses

  • IP addresses always become an INT UNSIGNED
  • Each subnet corresponds to an 8-byte division of the underlying INT UNSIGNED
  • From string to int? Use INET_ATON()
  • From into to ...

Query Cache

  • スライド
    • たくさんのストレージエンジンがある
  • Understand your applications read/write ratio for most effective use
  • Compromise between CPU usage and read performance
  • Remember that the bigger your query cache, you may not see better performance, even if your application is read heavy

Query Cache Invalidation


  • Coarse invalidation designed to prevent CPU overuse
  • ...
  • Thusm any modification to any table referenced in the SELECT will invali...

Choosing a Storage Engine

  • MySQLs strong point: many engines MySQLはたくさんエンジンがあるよ
  • Use InnoDB for most operations (esp. OLTP), except:
    • big, read only tables
    • high volume streaming tables(logging)
    • specialised needs (have special engines)
  • Tune InnoDB wisely


  • MyISAM
    • Good for logging, auditing, data warehousing
  • Archive
    • Very fast insert and table scan performance めちゃ速いよ
    • Read only. Good for archiving, audit logging
    • 内部的にgzipしてるよ
  • Memory

Quick InnoDB Tuning Tips

  • innodb_file_per_table
  • innodb_buffer_pool_size = (memory * 0.80)
  • innodb_flush_log_at_trx_commit
  • innodb_log_file_size - keep it high, however recovery time increases (4GB が最大)

Real World MySQL Use (RWMU)

  • Run many servers
    • Your serious application cannot run on "the server"
  • "Shared nothing" architecture
    • make no single point of contention in the system
    • Scales well, just by adding cheap nodes
    • If it works for Google, it will work for you!

RWMU: State and Session Information

  • Dont keep state within the application server
  • Key to being stateless: session data
  • Cookies are best validated by checksums and timestamps(encrypting is a waste of CPU cycles)

RWMU: Caching

  • 動的コンテンツには向かない
  • Cache full pages, all in application, and include the cookie
  • Use mod_cache, squid , and the Expires header to control cache times
  • A novel way: cache partial pages!
    • pre-generate static page snippets, then bind them in with dynamic content ...

RWMU : Data Partitioning

  • replication is great for read heavy applications
  • Write intensive applications should look at partitioning
  • Partition with a global master server in mind
    • Give out global PKs and cache heavily (memcached)
    • ...
  • Consider the notion of summary databases

RWMU: Blobs

  • Large binary object storage is interesting
    • Image data is best kept in the filesystem, just use metadata in DB to reference server and path to filename
    • Try the Amazon S3 storage engine?
  • ...


  • Unicode - use it Unicodeはもう当たり前
    • ブログで使われる言語は日本語が最も多いから
  • Use UTC for time
    • Think about replication across geographical boundaries
  • sql_mode might as well be strict
  • Keep configuration in version control
    • Then consider puppet or slack for management of various servers


  • MySQL Forge and the Forge Wiki
  • MySQL Performance Blog
  • Planet MySQL
  • #mysql-dev on
    • chat with developers, and knowledgeable community members



  • Email me:
  • Cathe me on IRC, at #mysql-dev: ccharles