Taming the MySQL Vagabond

Transcript

1. TAMING THE MYSQL VAGABOND Madison PHP Conference 2015

2. I AM MIKE WILLBANKS • Father, Husband, Developer • VP

   of Development at Packet Power • Twitter: @mwillbanks

3. OVERVIEW • Performance Tuning • Hot Changes • Debugging Issues

   • Taming the Vagabond

4. PERFORMANCE TUNING

5. CONFIGURATION

6. RAM & DISK • RAM - as much as you

   can afford. • DISK - as speedy as possible. • CPU - not as high of a priority as the others.

7. • RAM is the most important thing to your database,

   quickly followed by disk. • Not having fast enough disk can cause your ibdata file to grow uncontrollably for undo logs and more. • CPU is important for the purposes of sorting quickly.

8. BUFFER POOL • Caches data and indexes in memory. •

   Allows MySQL to work like a memory resident database • Up to 80% of your memory.

9. • innodb_buffer_pool_size = 80% available • Single most important variable.

   • innodb_buffer_pool_instances = n • Separate out buffer pools for read / write.

10. RAID • RAID0 or RAID10 • Striping is better if

    you can afford it. • Replication and data consistency is a must!

11. • mdadm --create --verbose /dev/md0 --level=stripe --raid- devices=2 /dev/disk1 /dev/disk2

12. FILE SYSTEM • Decide carefully! • XFS generally wins at

    around 10-30% • EXT4 is not ideal

13. • apt-get install xfsprogs • fdisk /dev/disk • create partition

    • mkfs.xfs -f /dev/disk • mount -t xfs /dev/disk /var/lib/mysql

14. DURABILITY • Do you need to recover on a disk

    level? If you do it drastically changes how you handle InnoDB.

15. • innodb_flush_method = O_DIRECT • avoids cache pollution and double

    caching. • innodb_flush_log_at_trx_commit = 2 • Flush once per second vs. full ACID compliance (value of 1).

16. TEMPORARY TABLES • innodb_temp_data_file_path • Separate onto different disks; if

    possible use ram disk. • Create large enough to handle your larger tables for sorting, manual creation of tables, etc.

17. FILE PER TABLE • innodb_file_per_table ON • reclaim disk space

    vs. massive idb table space. • Can separate tables to different data directories. • Necessary if using O_DIRECT

18. • CREATE TABLE t1 (c1 INT PRIMARY KEY) DATA DIRECTORY

    = '/alternative/directory';

19. DON’T USE TRUNCATE TABLE • CREATE TABLE t1_new LIKE t1;

    • RENAME TABLE t1 TO t1_old, t1_new TO t1; • DROP TABLE t1_old;

20. QUERY CACHE • Generally worthless on larger databases especially write

    heavy workloads. • Disabled by default. • query_cache_type • 0 = disabled • 2 = explicit (SQL_CACHE)

21. • SELECT SQL_CACHE ….

22. ALL THINGS BUFFERS • Set to approximately 15% total of

    memory. • key-buffer-size • join-buffer-size • read-buffer-size • sort-buffer-size

23. INDEXING • Be selective • Partial indexing vs. Full index

    • log-queries-not-using- indexes • Useful for finding query issues for indexes.

24. • ALTER TABLE t1 ADD INDEX (charcol(4)) • Look at

    carnality and size appropriately. mysql> SHOW INDEXES FROM t1; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | t1 | 0 | charcol | 1 | charcol | A | 4916 | NULL | NULL | | BTREE | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 5 rows in set (0.00 sec)

25. IDENTIFYING QUERY PERFORMANCE

26. EXPLAIN… • Identify query path execution. • Your bread and

    butter for understanding what is going on.

27. mysql> explain select * from user where is_user = 1\G

    *************************** 1. row *************************** id: 1 select_type: SIMPLE table: user type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 25638013 Extra: Using where 1 row in set (0.00 sec) mysql> explain select * from user where is_user = 1 and first_name like 'Mike' and last_name like 'Willbanks%'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: user type: range possible_keys: first_name key: first_name key_len: 276 ref: NULL rows: 3 Extra: Using index condition; Using where 1 row in set (0.00 sec)

28. DERIVED QUERIES • Bad unless using correctly. • Great for

    cross merging with limited results. • Select a small subset and join on the outer condition.

29. PARTITIONING • Must be done by a primary key column.

    • Separates tables spaces, but is not free… • Partition Types • RANGE • LIST • HASH • KEY

30. CREATE TABLE `node_values` ( `channelId` int(11) unsigned NOT NULL DEFAULT

    '0', `nodeId` bigint(20) unsigned NOT NULL DEFAULT '0', `timeStamp` bigint(20) NOT NULL DEFAULT '0', PRIMARY KEY (`channelId`,`nodeId`,`timeStamp`), KEY `nfvn_nodeIndex` (`nodeId`), KEY `nfvn_tsIndex` (`timeStamp`) ) ENGINE=InnoDB PARTITION BY KEY (channelId) PARTITIONS 50

31. PARTITION ISSUES • Partitions must have the partition key inside

    of the primary key declaration. • Issue happens with ranges when it’s not a primary participant.

32. CREATE TABLE `alert_policy_history` ( `historyId` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

    `refType` enum('alert','policy') NOT NULL, `refId` bigint(20) unsigned NOT NULL, `timeStamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `endTimeStamp` timestamp NULL DEFAULT NULL, `layoutId` bigint(20) unsigned DEFAULT NULL, `cabinetId` bigint(20) unsigned DEFAULT NULL, `nodeId` bigint(20) unsigned DEFAULT NULL, `channelId` mediumint(8) DEFAULT NULL, `value` double NOT NULL, PRIMARY KEY (`historyId`,`timeStamp`), KEY `refId` (`refId`,`refType`), KEY `cabinetId` (`cabinetId`), KEY `nodeId` (`nodeId`) ) ENGINE=InnoDB PARTITION BY RANGE ( UNIX_TIMESTAMP(`timeStamp`)) (PARTITION p2015 VALUES LESS THAN (1451606400) PARTITION p2016 VALUES LESS THAN (1483228800) PARTITION p2017 VALUES LESS THAN (1514764800) PARTITION p2018 VALUES LESS THAN (1546300800) PARTITION p2019 VALUES LESS THAN (1577836800) PARTITION p2020 VALUES LESS THAN (1609459200)

33. EVENTS • Most useful for scheduling tasks, rotating tables, rotating

    partitions, generating materialized views and more.

34. CREATE EVENT `rotate_cube` ON SCHEDULE EVERY 1 DAY DO BEGIN

    DECLARE partitionSchema VARCHAR(64); DECLARE partitionToDrop VARCHAR(100); SELECT DATABASE() INTO partitionSchema; SELECT CONCAT('ALTER TABLE `values_cube` ADD PARTITION (PARTITION p', DATE_FORMAT(NOW() + INTERVAL 1 DAY, '%Y%m%d'), ' VALUES LESS THAN (', UNIX_TIMESTAMP(CONCAT(MAKEDATE(YEAR(NOW()), DAYOFYEAR(NOW()) + 2), ' 00:00:00')), '))') INTO @stmt; PREPARE pStmt FROM @stmt; EXECUTE pStmt; DEALLOCATE PREPARE pStmt; SELECT `PARTITION_NAME` INTO partitionToDrop FROM INFORMATION_SCHEMA.PARTITIONS WHERE `TABLE_SCHEMA` = partitionSchema AND `TABLE_NAME` = ‘values_cube' AND `PARTITION_ORDINAL_POSITION` = 1; SELECT CONCAT('ALTER TABLE `values_cube` DROP PARTITION `', partitionToDrop, '`') INTO @stmt; PREPARE pStmt FROM @stmt; EXECUTE pStmt; DEALLOCATE PREPARE pStmt; END

35. TRIGGERS • Insanely useful to clean up application logic.

36. CREATE TRIGGER `trigger_alert_policy_history` AFTER UPDATE ON `policy_violations` FOR EACH ROW

    BEGIN IF (OLD.`state` <> 'ACTIVE' AND NEW.`state` = 'ACTIVE') THEN INSERT INTO `alert_policy_history` SELECT … WHERE `pv`.`state` = 'ACTIVE' AND `pv`.`policyId` = NEW.`policyId`; END IF; IF (OLD.`state` = 'ACTIVE' AND NEW.`state` <> 'ACTIVE') THEN UPDATE `alert_policy_history` SET `endTimeStamp` = NOW() WHERE `refType` = 'policy' AND `refId` = NEW.`policyId` AND `endTimeStamp` IS NULL; END IF; END

37. STORED PROCEDURES • Procedures can be faster than doing all

    of our work in our programming language of choice. • Great for iterating over data and transforming.

38. CREATE PROCEDURE `cubeLoadDaily`(IN fromTime BIGINT, IN toTime BIGINT) BEGIN declare

    l_loop bigint default 0; set l_loop :=fromTime div (24 * 3600) * (24 * 3600); loop1: loop replace into value_cube_daily(…) select …. from value_cube where timeStamp >= l_loop and timeStamp < (l_loop + 24 * 3600) ; set l_loop := l_loop + 24 * 3600; IF l_loop >= toTime THEN leave loop1; end if; end loop loop1; END

39. FUNCTIONS • Can return values but execute queries. • Sometimes

    can find huge speed increases.

40. CREATE FUNCTION `cubeSum`(fromTime bigint , toTime bigint) RETURNS double BEGIN

    declare v double; set @leftHrTime := fromTime div 3600 * 3600; set @rightHrTime := toTime div 3600 * 3600; set @leftDayTime := @leftHrTime div @DAY * @DAY; set @rightDayTime := @rightHrTime div @DAY * @DAY; set @leftDayTime := if(@leftDayTime < @leftHrTime, @leftDayTime + @DAY, @leftDayTime); set v = 0; select sum(value) as value into v from ( # left hourly margin select sum(value) as value from node_values_cube where timeStamp >= @leftHrTime and timeStamp < LEAST(@leftDayTime, @rightHrTime) union # daily core select sum(value) as value from node_values_ddcube where timeStamp >= @leftDayTime and timeStamp < @rightDayTime union # right hourly margin select sum(value) as value from node_values_cube where timeStamp >= GREATEST(@rightDayTime, @leftHrTime) and timeStamp < @rightHrTime ) as x; return v; END;

41. VIEWS • Don’t do it. • Views operate as a

    derived query and are not indexed but can include indexes from existing tables.

42. CREATE VIEW `t1` AS SELECT `m`.`msgId` AS `msgId` ,`m`.`msg` AS

    `msg` ,`t`.`msg` AS `tran` from (`i18n_messages` `m` join `i18n_translations` `t`) where ((`m`.`msgId` = `t`.`msgId`) or isnull(`t`.`msgId`))

43. explain select * from t1 where msg = 'Foo'\G ***************************

    1. row *************************** id: 1 select_type: SIMPLE table: m type: const possible_keys: PRIMARY,msg key: msg key_len: 257 ref: const rows: 1 Extra: Using index *************************** 2. row *************************** id: 1 select_type: SIMPLE table: t type: ref possible_keys: msgId key: msgId key_len: 4 ref: const rows: 8 Extra: 2 rows in set (0.00 sec)

44. MATERIALIZED VIEWS • Leverage triggers and events. • Best practice

    is to regenerate once per day but apply deltas as they come in (triggers).

45. HOT DB CHANGES

46. HOT ENUM • Adding values to an ENUM does not

    require a table lock as long as you APPEND the list.

47. CREATE TABLE `foo` ( `id` INT(10) NOT NULL AUTO_INCREMENT, `foo`

    ENUM('foo'), PRIMARY KEY `id` ); ALTER TABLE `foo` MODIFY COLUMN `foo` ENUM('foo','bar'); # wrong ALTER TABLE `foo` MODIFY COLUMN `foo` ENUM('bar','foo');

48. HOT ALTER • pt-online-schema-change • Allows you to hot change

    most tables without downtime!

49. pt-online-schema-change --alter "ADD COLUMN c1 INT" D=mydb,t=mytable

50. HOT BACKUP • mysql enterprise backup • xtrabackup

51. /usr/bin/mysqlbackup --user=mysqlbackup --backup-dir=/backup/mysql --log-bin-index=/ var/lib/mysql/mysql-bin.index --read-threads=1 --write-threads=1 --process-threads=2 --limit-memory=4096 --no-locking

    --sleep=500 backup if [ $? -ne 0 ]; then echo 'Something went wrong!' exit 1 fi exit 0 /usr/bin/mysqlbackup --backup-dir=/backup/mysql --limit-memory=4096 apply-log if [ $? -ne 0 ]; then echo 'Unable to apply log!' exit 1 fi

52. xtrabackup --backup --datadir=/var/lib/mysql/ —target-dir=/backup/mysql/ xtrabackup --prepare --target-dir=/backup/mysql/

53. DEBUGGING

54. DEBUGGING QUERIES • Slow query log • General log •

    Non-indexed query log

55. DEBUG LOCK CONTENTION • Deadlock detection innodb params • innodb_print_all_deadlocks

56. ------------------------ LATEST DETECTED DEADLOCK ------------------------ 151114 15:26:07 *** (1) TRANSACTION:

    TRANSACTION 6305BF9D4, ACTIVE 1 sec starting index read mysql tables in use 9, locked 9 LOCK WAIT 236 lock struct(s), heap size 31160, 1858 row lock(s) MySQL thread id 2307727, OS thread handle 0x7f4124b3d700, query id 15395148787 event_scheduler Copying to tmp table INSERT IGNORE INTO `mytable` SELECT … FROM other_table *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 881 page no 1576 n bits 224 index `PRIMARY` of table `mydb`.`other_table ` trx id 6305BF9D4 lock mode S waiting Record lock, heap no 49 PHYSICAL RECORD: n_fields 6; compact format; info bits 0 0: len 8; hex 170000000000214d; asc !M;; 1: len 4; hex 80000001; asc ;; 2: len 6; hex 0006305bf9cb; asc 0[ ;; 3: len 7; hex 3400010014335b; asc 4 3[;; 4: len 8; hex 8000000056475279; asc VGRy;; 5: len 8; hex 650c107eb14e9041; asc e ~ N A;; …

57. TAMING THE VAGABOND

58. TEMPORARY TABLE ISSUES • Internal table counter can be skipped

    causing assertion failure…

59. CREATE TEMPORARY TABLE …; …DO THINGS… DROP TEMPORARY TABLE …;

60. ORDERING • Order by performance lies, watch your primary keys.

    • DESC is faster than ASC. • Specify your primary key while adding sort by conditions.

61. SELECT * FROM LIMIT 10000; vs. SELECT * FROM ORDER

    BY PRIMARY DESC LIMIT 10000;

62. BIG INTEGER ISSUES • Passing a big integer otherwise known

    as a 64- bit integer in the query string will cause floating point precision loss. • Pass as a string and use CONVERT().

63. CORRUPTION • Prepare for a lot of downtime. • Generally

    inside of a range of a table. • Causes segfaults and failures if not fixed. • innodb_force_recovery = 1-6 • Dump all data and reload.

64. SAFER… • --safe-updates • sql_select_limit = 1000 • sql_max_join_size =

    1000000 • Ensures a where clause for updates / deletes.

65. STRICTER… • innodb_strict_mode = ON • Raises errors rather than

    warnings for stupidity in create table, alter table, create index.

66. • https://pixabay.com/en/oldtimer-auto-classic-775668/ • https://pixabay.com/en/glass-cognac-smoke-full-empty-300558/ • https://pixabay.com/en/footprint-sand-beach-wave-93482/ • https://pixabay.com/en/sheep-crazy-animal-explosive-blow-160370/ • http://sero.nmfs.noaa.gov/protected_resources/section_7/protected_species_educational_signs/documents/protect_dolphin_sign_feeding.pdf

    • https://pixabay.com/en/folder-of-files-binders-office-428299/ • https://pixabay.com/en/road-fog-signs-speed-limit-690347/ • https://pixabay.com/en/remote-control-configuring-sound-374058/ • https://pixabay.com/en/tree-fungus-mushroom-nature-autumn-13225/ • https://pixabay.com/en/arches-architecture-building-city-945495/ • https://pixabay.com/en/list-names-table-personal-data-428312/ • https://pixabay.com/en/concert-performance-audience-336695/ • https://pixabay.com/en/white-board-startup-start-up-593309/ • https://pixabay.com/en/activity-backhoe-big-blue-borrow-216921/ • https://pixabay.com/en/holzstapel-wood-wall-partition-wall-984925/ • https://pixabay.com/en/king-kong-wax-museum-wax-figure-472134/ • https://pixabay.com/en/doctor-technology-transplant-659896/ • https://pixabay.com/en/smart-watch-apple-technology-style-821559/ • https://pixabay.com/en/building-reflection-window-glass-922529/ • https://pixabay.com/en/test-testing-bubble-form-test-form-986935/ • https://pixabay.com/en/carbine-rope-hook-backup-climbing-7105/ • https://pixabay.com/en/sewing-machine-sewing-precision-262454/ • https://pixabay.com/en/hippo-mammal-wildlife-nature-wild-783522/ • https://pixabay.com/en/lifeguard-lake-rescue-tower-water-983910/ • https://pixabay.com/en/police-crime-scene-blue-light-862341/ • https://pixabay.com/en/gummib%C3%A4rchen-fruit-gums-bear-359950/ • https://pixabay.com/en/padlock-door-lock-key-hole-macro-172770/ • https://pixabay.com/en/bovist-mushroom-dust-crush-spores-184495/ • https://pixabay.com/en/forge-blacksmith-hammer-iron-fire-411923/ • https://pixabay.com/en/filbert-weevil-bug-insect-close-up-106114/ • https://pixabay.com/en/man-homeless-homeless-man-poverty-937665/ • https://pixabay.com/en/man-homeless-homeless-man-poverty-937665/ • https://pixabay.com/en/repair-glue-fix-adhesive-891422/ • https://pixabay.com/en/sheep-bighorn-wildlife-nature-718020/ • https://pixabay.com/en/portobello-notting-hill-london-778864/ Image Credits THANK YOU! http://joind.in/talk/view/16016