An introduction to mysql Performance Optimization (2008)

An introduction to mysql Performance Optimization (2008)

• 1.
  An Introduction To MySQLPerformance Optimization Luis Rei luis.rei@gmail.com http://luisrei.com Barcamp Portugal 2008 September 6, 2008
• 2.
  Performance Problems • Don’tMake Guesses • PROFILE THE APPLICATION!!! • Database? Identify The Problem • Fix It, Optimize It, Buy It, ... • Silver Bullets IT IS A capital mistake, to theorize before one has data. One begins to twist facts to suit theories, instead of theories to suit facts. (Sherlock Holmes)
• 3.
  Tools • SHOW Commands •mytop • Benchmarks • MySQL Benchmark Suite • Super Smack
• 4.
  SHOWVARIABLES;
• 5.
  SHOW PROCESSLIST; SHOW STATUS;
• 6.
  TOP & MYTOP
• 7.
  Benchmarks • Why? • Strategies •One change at a time • Repeat tests • Use real data when possible (query optimizer) • Run the benchmark on a different machine
• 8.
  The MySQL Benchmark Suite
• 9.
  Super Smack • MoreControl • Simulates multiple clients • Stress tests • Allows more realistic scenarios • Requires a lot of configuration
• 10.
  Table Design • Usethe Appropriate data type • Smallest possible (TINYINT FTW) • Normalize • Denormalize (massive JOINs FTL) • Very large tables aren’t great either...
• 11.
  MySQL Architecture
• 12.
  Storage Engines • IndexTypes • Transactions And Concurrency • Data types • Workload-specific Characteristics • MyISAM, InnoDB, Memory,Archive, and many others
• 13.
  MyISAM • No Transactions,Not Crash Safe,Table Locks • Compact data storage (HD & RAM) • Does not support foreign keys • TEXT and BLOB indexes • Read Only or Mostly Read (SELECT) • not always faster than InnoDB • Fast Writes (INSERT/UPDATE) • don’t mix with Reads in the same table • Slow crash recovery • Good for many webapps and data crunching
• 14.
  InnoDB • De-facto standard •Transactions,ACID, Crash Safe, Row Locks • MVCC (Multi-version Concurrency Control) • Tables and indexes are much larger (2-5x) • Support for foreign keys • High read/write concurrency • Faster crash recovery • Use the plugin from innodb.com
• 15.
  Memory • No transactions,TableLocks • Stores data in memory • contents are lost on restart (table remains) • can be populated at startup • Does not support foreign keys • Very fast for storing temporary results • No TEXT or BLOB :( • Can have non-unique keys (problem for hash indexes)
• 16.
  Indexes • Make rowslookup faster • SELECT ...WHERE lastname = ‘Rei’ • If this is a common query, make an index on lastname - ALTER TABLE ...ADD INDEX(lastname) • Space vs Speed • Partial Indexes - ALTER TABLE ...ADD INDEX(lastname(3)) • Multicolumn • SELECT ...WHERE lastname = ‘Rei’ and firstname = ‘Luis’ • ALTER TABLE ...ADD INDEX(lastname, firstname) • Clustered (records are stored in primary-key order -InnoDB) • Full-text (quickly retrieve every word in a field - MyISAM) • SELECT ...WHERE body = "%luisrei.com%"
• 17.
  Queries • Query Cache •Less data is better - don’t SELECT * • Single large query vs Multiple small queries • Index based ordering • SELECT ...WHERE lastname = ‘Rei’ ORDER BY birthdate DESC; • ALTER TABLE ... DROP INDEX lastname,ADD INDEX (lastname, birthdate); • Slow query log • Hints • SELECT ... USE INDEX myfavindex ... (IGNORE, FORCE) • SELECT * FROM Table1 STRAIGHT_JOIN Table2 ...
• 18.
  EXPLAIN
• 19.
  Other Stuff -Google It • Partitions • MySQL Cluster • Sharding • Replication • Cache (Memcache)
• 20.
  References • MySQL ReferenceManual • Books • Derek J. Balling and Jeremy Zawodny (2004) High Performance MySQL, O’Reilly • Paul DuBois (2005) MySQL (3rd Edition), Sams • Blogs • MySQL Performance Blog - http://www.mysqlperformanceblog.com • Presentations • Various presentations from Percona available at http://www.percona.com