confessions of a dba: worst and best things i've done in production - open source bridge 2014
DESCRIPTION
http://opensourcebridge.org/sessions/1275TRANSCRIPT
Confessions of a DBA: worst and best things I've
done in productionEmily Slocombe
Open Source Bridge 2014
High and Low lights:• at it since 1999!
• I’ve used the blackhole storage engine a few awesome times
• I’ve seriously broken things
• I’ve confessed breaking things to rather famous mysql people
• They remember these stories for years
Breaking things = learning
image: http://www.boredpanda.org/lets-see-who-can-post-the-cutest-animal-photo/#post0
Adventures in:• Engine=blackhole
• data migration
• ‘Ghetto RAID’: mistakes in innodb
• the Hammer that is mysqltuner.pl
• MyISAM
• bad and worse
• Benchmarking!
Engine=blackhole
set sql_log_bin=0; alter table blah engine=blackhole;
1. Master was for bin logs only on this one table.
2. Slave could not keep up with replication and this giant table with giant text blobs was IO pain!
image from: http://www.bearlakereserve.com/what-are-our-furry-friends-up-to/
Data Migration
• no sleep for 1 week!
• lived on energy drinks, greek yogurt, and chocolate covered espresso beans.!
• couldn’t eat vegetables for 1 year
ssh tunnel(s)!
Sad, cheap RAID
• 5.1.47 MySQL
• slow disks, long wait for more fast SSD / FC
• 1 SSD
• InnoDB: could symlink folders, not files
Sad, cheap RAID
• I should have tested this.
• BTW: InnoDB is ‘sparse file type’
• oops
I am not a DBA
• mysqltuner.pl seems like a great idea
• max heap / query cache
I am not a DBARECOMMENDATIONS: version less then 5.1, upgrade! version less then 5.1.30 (the first GA release of 5.1), upgrade! The MySQL manual only is accurate for official MySQL binaries, Percona documentation is at http://www.percona.com/docs/wiki/ The query cache is not being fully utilized. Increase query_cache_size -‐-‐ there are too many low memory prunes. Too many sorts are causing temporary tables. Consider increasing\nsort_buffer_size and/or read_rnd_buffer_size There are lots of rows being sorted. Consider using indexes in more queries to avoid sorting too often. There are too many joins without indexes -‐-‐ this means that joins are doing full table scans. The rate of reading the first index entry is high; this usually indicates frequent full index scans. The rate of reading data from a fixed position is high; this indicates many queries need to sort results and/or do a full table scan, including join queries that do not use indexes. The rate of reading the next table row is high; this indicates many queries are doing full table scans. Too many temporary tables are being written to disk. Increase max_heap_table_size and tmp_table_size. Too many intermediate temporary tables are being created; consider increasing sort_buffer_size (sorting), read_rnd_buffer_size (random read buffer, ie, post-‐sort), read_buffer_size (sequential scan). MyISAM key buffer (index cache) % used is low. You may need to decrease the size of key_buffer_size, re-‐examine your tables to see if indexes have been removed, or examine queries and expectations about what indexes are being used. MyISAM key buffer (index cache) % used is low. You may need to decrease the size of key_buffer_size, re-‐examine your tables to see if indexes have been removed, or examine queries and expectations about what indexes are being used. Size of the table cache InnoDB log file size is not an appropriate size, in relation to the InnoDB buffer pool. Consider changing either\ninnodb_log_file_size or innodb_buffer_pool_size
MyISAM
• drupal node table
• nearly read-only
• why not?
MyISAM• 200+G table on EBS
• internally corrupt
• version 5.0.92
• upgrade version
• need to fix table
• add disk space
MyISAM, now InnoDBI’d rather be blaming MyISAM, but I cannot.
MyISAM, now InnoDB
Benchmarking
Breaking things = learning
Emily Slocombe!Twitter: @NinjaBunny_ Work: [email protected]
Photocredits: piggie: http://www.boredpanda.org/lets-see-who-can-post-the-cutest-animal-photo/#post0 squirrel: http://www.bearlakereserve.com/what-are-our-furry-friends-up-to/
!Other credits:
mysqltuner.pl: https://launchpad.net/mysqltuner