Save 37% off PRO during our Black Friday Sale! »

15 protips for mysql - 4developers

1761ecd7fe763583553dde43e62c47bd?s=47 Joshua Thijssen
April 12, 2013
4.9k

15 protips for mysql - 4developers

1761ecd7fe763583553dde43e62c47bd?s=128

Joshua Thijssen

April 12, 2013
Tweet

Transcript

  1. 15 Pro-tips for MySQL Users 4Developers - 12 april 2013

    Warsaw - Poland 16
  2. Joshua Thijssen Freelance consultant, developer and trainer @ NoxLogic Founder

    of the Dutch Web Alliance Development in PHP, Python, Perl, C, Java. Lead developer of Saffire. Blog: http://adayinthelifeof.nl Email: jthijssen@noxlogic.nl Twitter: @jaytaph
  3. “Pro”-tips for MySQL. Rough estimation: 147 seconds per “tip”. So

    let’s get going.
  4. http://distrowatch.com/table.php?distribution=redhat http://distrowatch.com/table.php?distribution=debian 0: Use an up-to-date version RHEL 5.7: 5.0.77

    RHEL 6.1: 5.1.52 RHEL 6.4: 5.1.67 Debian (Lenny): 5.0.51a Debian (Squeeze): 5.1.49 Debian (Wheezy) 5.5.30
  5. 0: Use an up-to-date version MariaDB is a very good

    (maybe better?) option too!
  6. 1: Know how to use explain Not enough time to

    talk about EXPLAIN today
  7. 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) 1: Know how to use explain
  8. 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) 1: Know how to use explain
  9. 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') | +-------+------ +--------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------+ 1: Know how to use explain
  10. 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 | +--------------------------------+----------+----------+------------+--------------+---------------+ 1: Know how to use explain
  11. 2: Know the most basic my.cnf settings

  12. Know the most important ones: key_buffer_size, innodb_buffer_pool_size, sort_buffer_size, max_connections 2:

    Know the most basic my.cnf settings
  13. 2: Know the most basic my.cnf settings 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. http://www.omh.cc/mycnf/ https://github.com/rackerhacker/ MySQLTuner-perl http://www.day32.com/MySQL/ PHPMyAdmin 2: Know the most basic

    my.cnf settings
  15. 3: Backup on table level mysqldump can dump per database

    OR by table. Simple scripts to scan/dump tables. Easy restore for single table (or part of table)
  16. Don’t use “SELECT *” when you only need one or

    two fields. 4: Don’t select 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
  17. 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) 4: Don’t select what you don’t need
  18. mysql> EXPLAIN SELECT want_mailing FROM `covering` WHERE email LIKE 'joshua@noxlogic.nl';

    +----+-------------+----------+-------+---------------+-----------+---------+------+------+--------------------------+ | 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) 4: Don’t select what you don’t need
  19. 5: Use triggers and stored procedures for DB logic 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!
  20. 6: Don’t use FULLTEXT searches 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). You want to move to Lucene/Solr/ElasticSearch
  21. Some wildcard searches (%item%) are bad for performance 7: Be

    careful with %wildcards% MySQL cannot use indexing! Revert your data: search for ‘ln.cigolxon@%’ instead of ‘%@noxlogic.nl’. Use a better solution (solr, sphinx). You probably want it (no, really)
  22. 8: Shard your volatile and non-volatile data

  23. 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) 8: Shard your volatile and non-volatile data
  24. 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. 8: Shard your volatile and non-volatile data
  25. 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 Bulk update (hitcount += 100) 8: Shard your volatile and non-volatile data
  26. MySQL 5.1 and higher has got partitioning (but you should

    shard anyway). 8: Shard your volatile and non-volatile data
  27. 9: Don’t use large PKs for InnoDB tables InnoDB adds

    the primary key to EACH index. No primary key given? It uses an internal 6 byte key.
  28. 10: Don’t select count(*) on InnoDB

  29. InnoDB implements MVCC (multi-version concurrency control). COUNT(*) must be counted

    and is not fetched from metadata. 10: Don’t select count(*) on InnoDB
  30. What do you want to COUNT(*)? Just for displaying purposes

    (there are X amount of pages): do you need the EXACT amount? (guess &| cache) 10: Don’t select count(*) on InnoDB
  31. 11: Don’t rely on the VARCHAR() VARCHAR() is a storage

    solution, not a processing solution.
  32. 11: Don’t rely on the VARCHAR()

  33. 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) 11: Don’t rely on the VARCHAR()
  34. 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) 11: Don’t rely on the VARCHAR()
  35. UTF-8 isn’t the enemy, but it’s certainly not your friend.

    Dr Jeckyl and Mr Hyde Solves all your multi-language problems! But gives back performance issues. 12: Don’t trust UTF-8
  36. 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) 12: Don’t trust UTF-8
  37. 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) 12: Don’t trust UTF-8
  38. 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. 12: Don’t trust UTF-8
  39. 13: Know your cardinality and selectivity Cardinality: the number of

    unique entries inside the index. Selectivity: percentage of unique entries. S(I) = cardinality / count * 100%
  40. 13: Know your cardinality and selectivity

  41. with 10 records: 5/10 * 100% = 50% with 1000

    records: 75/1000 * 100 = 7.5% with 10.000 records: 200/10000 * 100% = 2% country_id (max +-200, but effectively +- 50, maybe less) 13: Know your cardinality and selectivity
  42. A selectivity < 30% ? Full table scan! ANALYZE TABLE

    frequently. Adding records changes your cardinality and thus selectivity. Develop against a “real” dataset. 13: Know your cardinality and selectivity
  43. 14: Non-deterministic functions do not go well with query caching

    SELECT .. FROM table ORDER BY RAND()
  44. SELECT * FROM table WHERE YEAR(created_dt) < YEAR(NOW()); vs SELECT

    * FROM table WHERE YEAR(created_dt) < ‘2010’; 14: Non-deterministic functions do not go well with query caching
  45. 15: Certify yourself as a DBA and/or DBE. 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!
  46. (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.
  47. Questions?

  48. Find me on twitter: @jaytaph Find me for development or

    training: www.noxlogic.nl Find me on email: jthijssen@noxlogic.nl Find me for blogs: www.adayinthelifeof.nl THANK YOU!