Upgrade to Pro — share decks privately, control downloads, hide ads and more …

15 pro-tips for MySQL users - PFCongress 2012

Joshua Thijssen
September 17, 2011
160

15 pro-tips for MySQL users - PFCongress 2012

Joshua Thijssen

September 17, 2011
Tweet

Transcript

  1. 1 16 Pro-tips for MySQL Users PFCongress - 17 september

    2011 Utrecht - Netherlands woensdag 25 april 12
  2. whoami Joshua Thijssen Senior Software Engineer @ Enrise Development in

    PHP, Python, Perl, C, Java.... Blog: http://adayinthelifeof.nl Email: [email protected] Twitter: @jaytaph 2 woensdag 25 april 12
  3. What are we going to discuss? ‣ QUESTIONS? RAISE YOUR

    HAND OR YELL LOUD 3 woensdag 25 april 12
  4. What are we going to discuss? ‣ QUESTIONS? RAISE YOUR

    HAND OR YELL LOUD ‣ 12 (to 16) MySQL Pro-tips 3 woensdag 25 april 12
  5. What are we going to discuss? ‣ QUESTIONS? RAISE YOUR

    HAND OR YELL LOUD ‣ 12 (to 16) MySQL Pro-tips ‣ Taken from the battlefield. 3 woensdag 25 april 12
  6. What are we going to discuss? ‣ QUESTIONS? RAISE YOUR

    HAND OR YELL LOUD ‣ 12 (to 16) MySQL Pro-tips ‣ Taken from the battlefield. ‣ Starting simple - ending “complex” 3 woensdag 25 april 12
  7. Tip 0 ‣ MySQL 5.0 != 5.5 0) Use the

    correct MySQL version 4 woensdag 25 april 12
  8. Tip 0: Use the correct MySQL version ‣ RHEL 5-7:

    5.0.77 ‣ RHEL 6-1: 5.1.52 ‣ Debian (lenny) 5.0.51a ‣ Debian (squeeze): 5.1.49 ‣ Debian (sid): 5.1.58 5 http://distrowatch.com/table.php?distribution=redhat http://distrowatch.com/table.php?distribution=debian woensdag 25 april 12
  9. Tip 1 ‣ EXPLAIN IS YOUR BESTEST FRIEND 1) Know

    how to use explain (and profiler) 6 woensdag 25 april 12
  10. Tip 1: Know your EXPLAIN (1) ‣ I will not

    show you how to use EXPLAIN. 7 woensdag 25 april 12
  11. Tip 1: Know your EXPLAIN (1) ‣ I will not

    show you how to use EXPLAIN. ‣ Use EXPLAIN and EXPLAIN EXTENDED/ SHOW WARNINGS; 7 woensdag 25 april 12
  12. Tip 1: Know your EXPLAIN (2) 8 mysql> desc varchartest;

    +----------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | 0 | | | name | varchar(255) | NO | MUL | NULL | | | utf8name | varchar(255) | NO | MUL | NULL | | +----------+--------------+------+-----+---------+-------+ 3 rows in set (0.01 sec) woensdag 25 april 12
  13. Tip 1: Know your EXPLAIN (3) 9 mysql> EXPLAIN EXTENDED

    -> SELECT * FROM varchartest WHERE name LIKE 'joshua'; +----+-------------+-------------+-------+---------------+----------+---------+------+------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------------+-------+---------------+----------+---------+------+------+----------+-------------+ | 1 | SIMPLE | varchartest | range | idx_name | idx_name | 257 | NULL | 1 | 100.00 | Using where | +----+-------------+-------------+-------+---------------+----------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.01 sec) woensdag 25 april 12
  14. Tip 1: Know your EXPLAIN (4) 10 mysql> SHOW WARNINGS\g

    +-------+------ +--------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------ +--------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------+ | Note | 1003 | select `zend`.`varchartest`.`id` AS `id`,`zend`.`varchartest`.`name` AS `name`,`zend`.`varchartest`.`utf8name` AS `utf8name` from `zend`.`varchartest` where (`zend`.`varchartest`.`name` like 'joshua') | +-------+------ +--------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------+ woensdag 25 april 12
  15. Tip 1: Know your PROFILER (1) 11 mysql> SET profiling=1;

    mysql> SELECT * FROM table; mysql> SHOW PROFILE CPU, BLOCK IO; +--------------------------------+----------+----------+------------+--------------+---------------+ | Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | +--------------------------------+----------+----------+------------+--------------+---------------+ | starting | 0.000930 | 0.000000 | 0.000000 | 0 | 8 | | checking query cache for query | 0.000547 | 0.000000 | 0.000000 | 0 | 0 | | checking permissions | 0.000045 | 0.000000 | 0.000000 | 0 | 0 | | Opening tables | 0.000142 | 0.000000 | 0.000000 | 0 | 0 | | System lock | 0.000030 | 0.000000 | 0.000000 | 0 | 0 | | Table lock | 0.000045 | 0.000000 | 0.000000 | 0 | 0 | | init | 0.000207 | 0.000000 | 0.000000 | 0 | 0 | | optimizing | 0.000007 | 0.000000 | 0.000000 | 0 | 0 | | statistics | 0.000068 | 0.000000 | 0.000000 | 0 | 0 | | preparing | 0.001393 | 0.004000 | 0.000000 | 0 | 0 | | Creating tmp table | 0.001658 | 0.000000 | 0.000000 | 0 | 8 | | executing | 0.000005 | 0.000000 | 0.000000 | 0 | 0 | | Copying to tmp table | 0.000834 | 0.000000 | 0.004001 | 0 | 8 | | Sorting result | 0.000179 | 0.000000 | 0.000000 | 0 | 0 | | Sending data | 0.000089 | 0.000000 | 0.000000 | 0 | 0 | | end | 0.000022 | 0.000000 | 0.000000 | 0 | 0 | | removing tmp table | 0.000089 | 0.000000 | 0.000000 | 0 | 0 | | end | 0.000005 | 0.000000 | 0.000000 | 0 | 0 | | query end | 0.000004 | 0.000000 | 0.000000 | 0 | 0 | | freeing items | 0.000844 | 0.000000 | 0.000000 | 0 | 0 | | logging slow query | 0.000023 | 0.000000 | 0.000000 | 0 | 0 | | logging slow query | 0.000060 | 0.000000 | 0.000000 | 0 | 8 | | cleaning up | 0.000007 | 0.000000 | 0.000000 | 0 | 0 | +--------------------------------+----------+----------+------------+--------------+---------------+ woensdag 25 april 12
  16. Tip 2 ‣ THERE ARE ONLY A FEW “BASIC” ONES.

    2) Know the most basic my.cnf settings 12 woensdag 25 april 12
  17. Tip 2: My.cnf settings (1) Know the most important ones:

    key_buffer_size, innodb_buffer_pool_size, sort_buffer_size, max_connections 13 woensdag 25 april 12
  18. Tip 2: My.cnf settings (2) ‣ Some settings work on

    global level, some per connection! 14 woensdag 25 april 12
  19. Tip 2: My.cnf settings (2) ‣ Some settings work on

    global level, some per connection! ‣ Know some quirks: (max_heap_table_size vs tmp_table_size, binlog- do-db, replicate-ignore-db etc) 14 woensdag 25 april 12
  20. Tip 2: My.cnf settings (3) ‣ http://www.omh.cc/mycnf/ ‣ http://rackerhacker.com/mysqltuner/ ‣

    http://www.day32.com/MySQL/ ‣ phpmyadmin 15 woensdag 25 april 12
  21. Tip 3 ‣ RESTORING JUST ONE TABLE CAN BE PAINFUL

    OTHERWISE 3) Backup on table level 16 woensdag 25 april 12
  22. Tip 3: Backup on table level (1) ‣ COULD YOU

    RESTORE TABLE x? YES! YES I CAN! 17 woensdag 25 april 12
  23. Tip 3: Backup on table level (1) ‣ COULD YOU

    RESTORE TABLE x? YES! YES I CAN! ‣ mysqldump can dump per database OR by table. 17 woensdag 25 april 12
  24. Tip 3: Backup on table level (1) ‣ COULD YOU

    RESTORE TABLE x? YES! YES I CAN! ‣ mysqldump can dump per database OR by table. ‣ Simple scripts to scan/dump tables. 17 woensdag 25 april 12
  25. Tip 3: Backup on table level (1) ‣ COULD YOU

    RESTORE TABLE x? YES! YES I CAN! ‣ mysqldump can dump per database OR by table. ‣ Simple scripts to scan/dump tables. ‣ Easy restore for single table (or part of table) 17 woensdag 25 april 12
  26. Tip 4 ‣ DON’T ASK WHAT YOU DON’T NEED 4)

    Don’t use “SELECT *” when you only need one or two fields. 18 woensdag 25 april 12
  27. Tip 4: Select * (1) ‣ DON’T ASK WHAT YOU

    DON’T NEED 19 woensdag 25 april 12
  28. Tip 4: Select * (1) ‣ DON’T ASK WHAT YOU

    DON’T NEED ‣ Much more data to be read from disk 19 woensdag 25 april 12
  29. Tip 4: Select * (1) ‣ DON’T ASK WHAT YOU

    DON’T NEED ‣ Much more data to be read from disk ‣ Much more data will be send over, thus slower (blobs/texts) 19 woensdag 25 april 12
  30. Tip 4: Select * (1) ‣ DON’T ASK WHAT YOU

    DON’T NEED ‣ Much more data to be read from disk ‣ Much more data will be send over, thus slower (blobs/texts) ‣ Cannot use covering indices 19 woensdag 25 april 12
  31. 20 Tip 4: Select * (2) mysql> SHOW FULL COLUMNS

    FROM `covering`; +--------------+------------------+------------------+------+-----+---------+----------------+ | Field | Type | Collation | Null | Key | Default | Extra | +--------------+------------------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NULL | NO | PRI | NULL | auto_increment | | email | varchar(255) | ascii_general_ci | NO | MUL | NULL | | | want_mailing | tinyint(1) | NULL | NO | MUL | NULL | | | extra_info | varchar(255) | ascii_general_ci | NO | MUL | NULL | | +--------------+------------------+------------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) mysql> SHOW INDEXES FROM `covering`; +----------+------------+------------+--------------+--------------+-----------+-------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | +----------+------------+------------+--------------+--------------+-----------+-------------+ | covering | 0 | PRIMARY | 1 | id | A | 3 | | covering | 1 | idx_email | 1 | email | A | 3 | | covering | 1 | idx_email | 2 | want_mailing | A | 3 | | covering | 1 | idx_email2 | 1 | want_mailing | A | 1 | | covering | 1 | idx_email2 | 2 | email | A | 3 | +----------+------------+------------+--------------+--------------+-----------+-------------+ 5 rows in set (0.01 sec) woensdag 25 april 12
  32. 21 Tip 4: Select * (2) mysql> EXPLAIN SELECT want_mailing

    FROM `covering` WHERE email LIKE '[email protected]'; +----+-------------+----------+-------+---------------+-----------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+-------+---------------+-----------+---------+------+------+--------------------------+ | 1 | SIMPLE | covering | range | idx_email | idx_email | 257 | NULL | 1 | Using where; Using index | +----+-------------+----------+-------+---------------+-----------+---------+------+------+--------------------------+ 1 row in set (0.00 sec) woensdag 25 april 12
  33. Tip 5: Triggers and stored procedures (1) ‣ ENFORCE CONSISTENCY

    ‣ 6 triggers per table (insert, update, delete, before and after the mutation) 23 woensdag 25 april 12
  34. Tip 5: Triggers and stored procedures (1) ‣ ENFORCE CONSISTENCY

    ‣ 6 triggers per table (insert, update, delete, before and after the mutation) ‣ 3rd party tools (phpmyadmin etc) can also use the database without loosing data consistency. 23 woensdag 25 april 12
  35. Tip 5: Triggers and stored procedures (1) ‣ ENFORCE CONSISTENCY

    ‣ 6 triggers per table (insert, update, delete, before and after the mutation) ‣ 3rd party tools (phpmyadmin etc) can also use the database without loosing data consistency. ‣ Watch out with (phpmyadmin) table dumps! 23 woensdag 25 april 12
  36. Tip 6 ‣ THERE ARE MUCH BETTER SOLUTIONS 6) Don’t

    use FULLTEXT searches 24 woensdag 25 april 12
  37. Tip 6: Don’t use FULLTEXT search (1) ‣ THERE ARE

    MUCH BETTER SOLUTIONS 25 woensdag 25 april 12
  38. Tip 6: Don’t use FULLTEXT search (1) ‣ THERE ARE

    MUCH BETTER SOLUTIONS ‣ They only work for MyISAM tables. 25 woensdag 25 april 12
  39. Tip 6: Don’t use FULLTEXT search (1) ‣ THERE ARE

    MUCH BETTER SOLUTIONS ‣ They only work for MyISAM tables. ‣ Not compatible with other DB’s. 25 woensdag 25 april 12
  40. Tip 6: Don’t use FULLTEXT search (1) ‣ THERE ARE

    MUCH BETTER SOLUTIONS ‣ They only work for MyISAM tables. ‣ Not compatible with other DB’s. ‣ Slow (especially compared to Solr, Sphinx). 25 woensdag 25 april 12
  41. Tip 6: Don’t use FULLTEXT search (1) ‣ THERE ARE

    MUCH BETTER SOLUTIONS ‣ They only work for MyISAM tables. ‣ Not compatible with other DB’s. ‣ Slow (especially compared to Solr, Sphinx). ‣ No extra features (faceted search, spell checking etc). 25 woensdag 25 april 12
  42. Tip 7 ‣ IT LOOKS LIKE YOU NEED MORE ADVANCED

    SEARCH? 7) Some wildcard searches (%item%) are bad for performance 26 woensdag 25 april 12
  43. Tip 7: Wildcard searches (1) ‣ THERE ARE MUCH BETTER

    SOLUTIONS 27 woensdag 25 april 12
  44. Tip 7: Wildcard searches (1) ‣ THERE ARE MUCH BETTER

    SOLUTIONS ‣ MySQL cannot use indexing! 27 woensdag 25 april 12
  45. Tip 7: Wildcard searches (1) ‣ THERE ARE MUCH BETTER

    SOLUTIONS ‣ MySQL cannot use indexing! ‣ Revert your data: search for ‘moc.esirne@%’ instead of ‘%@enrise.com’. 27 woensdag 25 april 12
  46. Tip 7: Wildcard searches (1) ‣ THERE ARE MUCH BETTER

    SOLUTIONS ‣ MySQL cannot use indexing! ‣ Revert your data: search for ‘moc.esirne@%’ instead of ‘%@enrise.com’. ‣ Use a better solution (solr, sphinx). You probably want it (no, really) 27 woensdag 25 april 12
  47. Tip 8 ‣ MAKE CACHING AND LOCKING HAPPY AGAIN 8)

    Shard your volatile and non-volatile data. 28 woensdag 25 april 12
  48. Tip 8: Sharding (1) 29 mysql> SHOW FULL COLUMNS FROM

    `pages`; +------------+------------------+-------------------+------+-----+-------------------+-------+ | Field | Type | Collation | Null | Key | Default | Extra | +------------+------------------+-------------------+------+-----+-------------------+-------+ | page_id | int(10) unsigned | NULL | NO | PRI | NULL | | | created_dt | timestamp | NULL | NO | | CURRENT_TIMESTAMP | | | creator_id | int(11) | NULL | NO | | NULL | | | title | varchar(100) | latin1_swedish_ci | NO | | NULL | | | contents | text | latin1_swedish_ci | NO | | NULL | | | hit_count | int(11) | NULL | NO | | 0 | | +------------+------------------+-------------------+------+-----+-------------------+-------+ 6 rows in set (0.01 sec) woensdag 25 april 12
  49. ‣ Remember: an update on a table will invalidate ALL

    caches referring to that table. Tip 8: Sharding (2) 30 woensdag 25 april 12
  50. ‣ Remember: an update on a table will invalidate ALL

    caches referring to that table. ‣ UPDATE pages SET hit_count = hit_count + 1; Tip 8: Sharding (2) 30 woensdag 25 april 12
  51. ‣ Remember: an update on a table will invalidate ALL

    caches referring to that table. ‣ UPDATE pages SET hit_count = hit_count + 1; ‣ Thus: page table will NEVER be cached. Tip 8: Sharding (2) 30 woensdag 25 april 12
  52. ‣ Define hot data (volatile, changes often) and cold data

    (static, changes never or infrequently) Tip 8: Sharding (3) 31 woensdag 25 april 12
  53. ‣ Define hot data (volatile, changes often) and cold data

    (static, changes never or infrequently) ‣ move to different tables Tip 8: Sharding (3) 31 woensdag 25 april 12
  54. ‣ Define hot data (volatile, changes often) and cold data

    (static, changes never or infrequently) ‣ move to different tables ‣ UPDATE page_stats SET hit_count = hit_count + 1; Tip 8: Sharding (3) 31 woensdag 25 april 12
  55. ‣ Define hot data (volatile, changes often) and cold data

    (static, changes never or infrequently) ‣ move to different tables ‣ UPDATE page_stats SET hit_count = hit_count + 1; ‣ Query cache is happy again Tip 8: Sharding (3) 31 woensdag 25 april 12
  56. ‣ MySQL 5.1 and higher has got partitioning (but you

    should shard anyway). Tip 8: Sharding (4) 32 woensdag 25 april 12
  57. Tip 9 ‣ PK’S ARE ON EVERY INDEX 9) Don’t

    use a large primary key for InnoDB tables. 33 woensdag 25 april 12
  58. Tip 9: Large primary keys (1) ‣ InnoDB adds the

    primary key to EACH index. 34 woensdag 25 april 12
  59. Tip 9: Large primary keys (1) ‣ InnoDB adds the

    primary key to EACH index. ‣ No primary key given? It uses an internal 6(!)-byte key. 34 woensdag 25 april 12
  60. Tip 10 ‣ COUNT(*) => MYISAM = FAST ‣ COUNT(*)

    => INNODB = SLOW 10) Don’t “SELECT COUNT(*) FROM TABLE” on InnoDB. 35 woensdag 25 april 12
  61. Tip 10: SELECT COUNT(*) (1) ‣ InnoDB implements MVCC (multi-

    version concurrency control). 36 woensdag 25 april 12
  62. Tip 10: SELECT COUNT(*) (1) ‣ InnoDB implements MVCC (multi-

    version concurrency control). ‣ COUNT(*) must be counted and is not fetched from metadata. 36 woensdag 25 april 12
  63. ‣ What do you want to COUNT(*)? Tip 10: SELECT

    COUNT(*) (2) 37 woensdag 25 april 12
  64. ‣ What do you want to COUNT(*)? ‣ Just for

    displaying purposes (there are X amount of pages): do you need the EXACT amount? (guess &| cache) Tip 10: SELECT COUNT(*) (2) 37 woensdag 25 april 12
  65. Tip 11 ‣ IT ISN’T THAT VARIABLE AS YOU MIGHT

    THINK 11) Don’t rely on the VARCHAR() 38 woensdag 25 april 12
  66. ‣ DON’T WORRY ABOUT THE UTF-8, I’LL BASH THAT LATER

    Tip 11: VARCHAR() (3) 40 mysql> SHOW FULL COLUMNS FROM `varchartest`; +----------+--------------+-------------------+------+-----+---------+-------+ | Field | Type | Collation | Null | Key | Default | Extra | +----------+--------------+-------------------+------+-----+---------+-------+ | id | int(11) | NULL | NO | PRI | 0 | | | name | varchar(255) | latin1_swedish_ci | NO | MUL | NULL | | | utf8name | varchar(255) | utf8_general_ci | NO | MUL | NULL | | +----------+--------------+-------------------+------+-----+---------+-------+ 3 rows in set (0.01 sec) mysql> SHOW INDEXES FROM `varchartest`; +-------------+------------+--------------+--------------+-------------+-----------+-------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | +-------------+------------+--------------+--------------+-------------+-----------+-------------+ | varchartest | 0 | PRIMARY | 1 | id | A | 3 | | varchartest | 1 | idx_name | 1 | name | A | 3 | | varchartest | 1 | idx_utf8name | 1 | utf8name | A | 3 | +-------------+------------+--------------+--------------+-------------+-----------+-------------+ 3 rows in set (0.00 sec) woensdag 25 april 12
  67. ‣ DON’T WORRY ABOUT THE UTF-8, I’LL BASH THAT LATER

    Tip 11: VARCHAR() (3) 41 mysql> EXPLAIN SELECT * FROM `varchartest` WHERE name LIKE 'jthijssen'; +----+-------------+-------------+-------+---------------+----------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+-------+---------------+----------+---------+------+------+-------------+ | 1 | SIMPLE | varchartest | range | idx_name | idx_name | 257 | NULL | 1 | Using where | +----+-------------+-------------+-------+---------------+----------+---------+------+------+-------------+ 1 row in set (0.00 sec) woensdag 25 april 12
  68. Tip 12 ‣ DON’T EXCHANGE ONE PROBLEM FOR ANOTHER 12)

    UTF-8 is not the enemy, but it certainly isn’t your friend. 42 woensdag 25 april 12
  69. Tip 12: UTF-8 (1) ‣ Dr Jeckyl and Mr Hyde

    ‣ Solves all your multi-language problems! 43 woensdag 25 april 12
  70. Tip 12: UTF-8 (1) ‣ Dr Jeckyl and Mr Hyde

    ‣ Solves all your multi-language problems! ‣ But gives back performance issues. 43 woensdag 25 april 12
  71. Tip 12: UTF-8 (1) 44 mysql> SHOW FULL COLUMNS FROM

    `varchartest`; +----------+--------------+-------------------+------+-----+---------+-------+ | Field | Type | Collation | Null | Key | Default | Extra | +----------+--------------+-------------------+------+-----+---------+-------+ | id | int(11) | NULL | NO | PRI | 0 | | | name | varchar(255) | latin1_swedish_ci | NO | MUL | NULL | | | utf8name | varchar(255) | utf8_general_ci | NO | MUL | NULL | | +----------+--------------+-------------------+------+-----+---------+-------+ 3 rows in set (0.01 sec) mysql> SHOW INDEXES FROM `varchartest`; +-------------+------------+--------------+--------------+-------------+-----------+-------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | +-------------+------------+--------------+--------------+-------------+-----------+-------------+ | varchartest | 0 | PRIMARY | 1 | id | A | 3 | | varchartest | 1 | idx_name | 1 | name | A | 3 | | varchartest | 1 | idx_utf8name | 1 | utf8name | A | 3 | +-------------+------------+--------------+--------------+-------------+-----------+-------------+ 3 rows in set (0.00 sec) woensdag 25 april 12
  72. Tip 12: UTF-8 (1) 45 mysql> EXPLAIN SELECT * FROM

    `varchartest` WHERE name LIKE 'jthijssen'; +----+-------------+-------------+-------+---------------+----------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+-------+---------------+----------+---------+------+------+-------------+ | 1 | SIMPLE | varchartest | range | idx_name | idx_name | 257 | NULL | 1 | Using where | +----+-------------+-------------+-------+---------------+----------+---------+------+------+-------------+ 1 row in set (0.00 sec) mysql> EXPLAIN SELECT * FROM `varchartest` WHERE utf8name LIKE 'jthijssen'; +----+-------------+-------------+-------+---------------+--------------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+-------+---------------+--------------+---------+------+------+-------------+ | 1 | SIMPLE | varchartest | range | idx_utf8name | idx_utf8name | 767 | NULL | 1 | Using where | +----+-------------+-------------+-------+---------------+--------------+---------+------+------+-------------+ 1 row in set (0.01 sec) woensdag 25 april 12
  73. ALL temporary buffers are allocated for worst-case scenario’s. This means

    a varchar(255) in UTF-8 uses 255*3 + 2 = 767 bytes PER row, even if you have only 1 single char inside. Tip 12: UTF-8 (1) 46 woensdag 25 april 12
  74. Tip 13: Cardinality & Selectivity (1) ‣ Cardinality: the number

    of unique entries inside the index. 48 woensdag 25 april 12
  75. Tip 13: Cardinality & Selectivity (1) ‣ Cardinality: the number

    of unique entries inside the index. ‣ Selectivity: percentage of unique entries. 48 woensdag 25 april 12
  76. Tip 13: Cardinality & Selectivity (1) ‣ Cardinality: the number

    of unique entries inside the index. ‣ Selectivity: percentage of unique entries. ‣ S(I) = cardinality / count * 100% 48 woensdag 25 april 12
  77. Tip 13: Cardinality & Selectivity (3) 50 country_id (max +-200,

    but effectively +- 50, maybe less) woensdag 25 april 12
  78. ‣ with 10 records: 5/10 * 100% = 50% Tip

    13: Cardinality & Selectivity (3) 50 country_id (max +-200, but effectively +- 50, maybe less) woensdag 25 april 12
  79. ‣ with 10 records: 5/10 * 100% = 50% ‣

    with 1000 records: 75/1000 * 100 = 7.5% Tip 13: Cardinality & Selectivity (3) 50 country_id (max +-200, but effectively +- 50, maybe less) woensdag 25 april 12
  80. ‣ with 10 records: 5/10 * 100% = 50% ‣

    with 1000 records: 75/1000 * 100 = 7.5% ‣ with 10.000 records: 200/10000 * 100% = 2% Tip 13: Cardinality & Selectivity (3) 50 country_id (max +-200, but effectively +- 50, maybe less) woensdag 25 april 12
  81. ‣ A selectivity < 30% ? Full table scan! ‣

    ANALYZE TABLE frequently. Tip 13: Cardinality & Selectivity (4) 51 woensdag 25 april 12
  82. ‣ Adding records changes your cardinality and thus selectivity. Tip

    13: Cardinality & Selectivity (5) 52 woensdag 25 april 12
  83. ‣ Adding records changes your cardinality and thus selectivity. ‣

    Develop against a “real” dataset. Tip 13: Cardinality & Selectivity (5) 52 woensdag 25 april 12
  84. Tip 14 ‣ NOW(), RAND(), UUID(), CONNECTION_ID() ETC.. 14) Non-deterministic

    functions do not go well with query caching 53 woensdag 25 april 12
  85. Tip 14: Query caching (1) 54 mysql> show status like

    '%qcache%'; +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 16768400 | | Qcache_hits | 3860 | | Qcache_inserts | 975 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 486 | | Qcache_queries_in_cache | 0 | | Qcache_total_blocks | 1 | +-------------------------+----------+ 8 rows in set (0.00 sec) mysql> select * from varchartest; +----+------+----------+ | id | name | utf8name | +----+------+----------+ | 1 | j | joshua | | 2 | j | jeroen | | 3 | d | david | +----+------+----------+ 3 rows in set (0.00 sec) mysql> show status like '%qcache%'; +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 16766864 | | Qcache_hits | 3860 | | Qcache_inserts | 976 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 486 | | Qcache_queries_in_cache | 1 | | Qcache_total_blocks | 4 | +-------------------------+----------+ 8 rows in set (0.00 sec) woensdag 25 april 12
  86. Tip 14: Query caching (2) 55 mysql> select * from

    varchartest; +----+------+----------+ | id | name | utf8name | +----+------+----------+ | 1 | j | joshua | | 2 | j | jeroen | | 3 | d | david | +----+------+----------+ 3 rows in set (0.00 sec) mysql> show status like '%qcache%'; +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 16766864 | | Qcache_hits | 3861 | | Qcache_inserts | 976 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 486 | | Qcache_queries_in_cache | 1 | | Qcache_total_blocks | 4 | +-------------------------+----------+ 8 rows in set (0.00 sec) mysql> select * from varchartest; +----+------+----------+ | id | name | utf8name | +----+------+----------+ | 1 | j | joshua | | 2 | j | jeroen | | 3 | d | david | +----+------+----------+ 3 rows in set (0.00 sec) mysql> show status like '%qcache%'; +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 16766864 | | Qcache_hits | 3860 | | Qcache_inserts | 976 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 486 | | Qcache_queries_in_cache | 1 | | Qcache_total_blocks | 4 | +-------------------------+----------+ 8 rows in set (0.00 sec) woensdag 25 april 12
  87. Tip 14: Query caching (3) 56 mysql> select * from

    varchartest; +----+------+----------+ | id | name | utf8name | +----+------+----------+ | 1 | j | joshua | | 2 | j | jeroen | | 3 | d | david | +----+------+----------+ 3 rows in set (0.00 sec) mysql> show status like '%qcache%'; +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 16766864 | | Qcache_hits | 3861 | | Qcache_inserts | 976 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 486 | | Qcache_queries_in_cache | 1 | | Qcache_total_blocks | 4 | +-------------------------+----------+ 8 rows in set (0.00 sec) mysql> select * from varchartest ORDER BY RAND(); +----+------+----------+ | id | name | utf8name | +----+------+----------+ | 2 | j | jeroen | | 1 | j | joshua | | 3 | d | david | +----+------+----------+ 3 rows in set (0.05 sec) mysql> show status like '%qcache%'; +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 16766864 | | Qcache_hits | 3861 | | Qcache_inserts | 976 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 487 | | Qcache_queries_in_cache | 1 | | Qcache_total_blocks | 4 | +-------------------------+----------+ 8 rows in set (0.00 sec) woensdag 25 april 12
  88. SELECT * FROM table WHERE YEAR(created_dt) < YEAR(NOW()); vs SELECT

    * FROM table WHERE YEAR(created_dt) < ‘2010’; Tip 14: Query caching (4) 57 woensdag 25 april 12
  89. Tip 15 ‣ AND GET SOME NICE TITLES WHILE YOU’RE

    AT IT... 15) Certify yourself as a DBA and/or DBE. 58 woensdag 25 april 12
  90. Tip 15: Certify yourself (1) ‣ THEY ARE NOT VERY

    EASY EXAMS, BUT WELL WORTH IT ‣ Oracle Certified MySQL Associate ‣ Oracle Certified Professional MySQL 5.0 Developer ‣ Oracle Certified Professional MySQL 5.0 Database Administrator ‣ Oracle Certified Expert, MySQL 5.1 Cluster Database Administrator ‣ “Old”, but still, get them all! 59 woensdag 25 april 12
  91. Let’s summarize (1) Know how to use explain. (2) Know

    the most basic my.cnf settings. (3) Backup on table level. (4) Don’t use “SELECT *” when you only need 1 or 2 fields. (5) Use triggers and stored procedures. (6) Don’t use FULLTEXT searches. (7) Wildcard searches (%item%) are bad for performance. (8) Shard your volatile and non-volatile data. (9) Don’t use a large Primary Key for InnoDB tables. (10) Don’t “Select COUNT(*)” on InnoDB. (11) Don’t rely on the VARCHAR(). (12) UTF-8 is not the enemy, but it certainly isn’t your friend. (13) Know your cardinality & selectivity. (14) Non-deterministic functions do not go well with query caching. (15) Certify yourself as a DBA and/or DBE. 60 woensdag 25 april 12
  92. ‣ THANK YOU FOR YOUR ATTENTION 62 ‣ Please rate

    my talk: http://joind.in/3662 woensdag 25 april 12