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: [email protected] Twitter: @jaytaph
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
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
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)
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)
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
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) 4: Don’t select what you don’t need
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!
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
Some wildcard searches (%item%) are bad for performance 7: Be careful with %wildcards% MySQL cannot use indexing! Revert your data: search for ‘[email protected]%’ instead of ‘%@noxlogic.nl’. Use a better solution (solr, sphinx). You probably want it (no, really)
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
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
InnoDB implements MVCC (multi-version concurrency control). COUNT(*) must be counted and is not fetched from metadata. 10: Don’t select count(*) on InnoDB
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
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()
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
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
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%
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
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
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!
(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.
Find me on twitter: @jaytaph Find me for development or training: www.noxlogic.nl Find me on email: [email protected] Find me for blogs: www.adayinthelifeof.nl THANK YOU!
jaytaph
schacon
panda_program
chrislema
danielanewman
hatefulcrawdad
scottboms
bryan
jensimmons
chriscoyier
addyosmani
davidbonilla
eileencodes