Taming the MySQL Vagabond

Transcript

1. TAMING THE MYSQL
   VAGABOND
   Madison PHP Conference 2015
   

   View Slide

2. I AM MIKE WILLBANKS
   • Father, Husband, Developer
   • VP of Development at
   Packet Power
   • Twitter: @mwillbanks
   

   View Slide

3. OVERVIEW
   • Performance Tuning
   • Hot Changes
   • Debugging Issues
   • Taming the Vagabond
   

   View Slide

4. PERFORMANCE TUNING
   

   View Slide

5. CONFIGURATION
   

   View Slide

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.
   

   View Slide

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.
   

   View Slide

8. BUFFER POOL
   • Caches data and indexes in
   memory.
   • Allows MySQL to work like
   a memory resident
   database
   • Up to 80% of your
   memory.
   

   View Slide

9. • innodb_buffer_pool_size = 80% available
   • Single most important variable.
   • innodb_buffer_pool_instances = n
   • Separate out buffer pools for read / write.
   

   View Slide

10. RAID
    • RAID0 or RAID10
    • Striping is better if you
    can afford it.
    • Replication and data
    consistency is a must!
    

    View Slide

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

    View Slide

12. FILE SYSTEM
    • Decide carefully!
    • XFS generally wins at
    around 10-30%
    • EXT4 is not ideal
    

    View Slide

13. • apt-get install xfsprogs
    • fdisk /dev/disk
    • create partition
    • mkfs.xfs -f /dev/disk
    • mount -t xfs /dev/disk /var/lib/mysql
    

    View Slide

14. DURABILITY
    • Do you need to recover on
    a disk level? If you do it
    drastically changes how you
    handle InnoDB.
    

    View Slide

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).
    

    View Slide

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.
    

    View Slide

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
    

    View Slide

18. • CREATE TABLE t1 (c1 INT PRIMARY KEY)
    DATA DIRECTORY = '/alternative/directory';
    

    View Slide

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;
    

    View Slide

20. QUERY CACHE
    • Generally worthless on
    larger databases especially
    write heavy workloads.
    • Disabled by default.
    • query_cache_type
    • 0 = disabled
    • 2 = explicit
    (SQL_CACHE)
    

    View Slide

21. • SELECT SQL_CACHE ….
    

    View Slide

22. ALL THINGS BUFFERS
    • Set to approximately 15%
    total of memory.
    • key-buffer-size
    • join-buffer-size
    • read-buffer-size
    • sort-buffer-size
    

    View Slide

23. INDEXING
    • Be selective
    • Partial indexing vs. Full
    index
    • log-queries-not-using-
    indexes
    • Useful for finding query
    issues for indexes.
    

    View Slide

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)
    

    View Slide

25. IDENTIFYING QUERY PERFORMANCE
    

    View Slide

26. EXPLAIN…
    • Identify query path
    execution.
    • Your bread and butter for
    understanding what is going
    on.
    

    View Slide

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)
    

    View Slide

28. DERIVED QUERIES
    • Bad unless using correctly.
    • Great for cross merging
    with limited results.
    • Select a small subset and
    join on the outer
    condition.
    

    View Slide

29. PARTITIONING
    • Must be done by a primary key
    column.
    • Separates tables spaces, but is not
    free…
    • Partition Types
    • RANGE
    • LIST
    • HASH
    • KEY
    

    View Slide

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
    

    View Slide

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.
    

    View Slide

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)
    

    View Slide

33. EVENTS
    • Most useful for scheduling
    tasks, rotating tables,
    rotating partitions,
    generating materialized
    views and more.
    

    View Slide

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
    

    View Slide

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

    View Slide

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
    

    View Slide

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.
    

    View Slide

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
    

    View Slide

39. FUNCTIONS
    • Can return values but
    execute queries.
    • Sometimes can find huge
    speed increases.
    

    View Slide

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;
    

    View Slide

41. VIEWS
    • Don’t do it.
    • Views operate as a derived
    query and are not indexed
    but can include indexes
    from existing tables.
    

    View Slide

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`))
    

    View Slide

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)
    

    View Slide

44. MATERIALIZED VIEWS
    • Leverage triggers and
    events.
    • Best practice is to
    regenerate once per day
    but apply deltas as they
    come in (triggers).
    

    View Slide

45. HOT DB CHANGES
    

    View Slide

46. HOT ENUM
    • Adding values to an ENUM
    does not require a table
    lock as long as you
    APPEND the list.
    

    View Slide

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');
    

    View Slide

48. HOT ALTER
    • pt-online-schema-change
    • Allows you to hot change
    most tables without
    downtime!
    

    View Slide

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

    View Slide

50. HOT BACKUP
    • mysql enterprise backup
    • xtrabackup
    

    View Slide

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
    

    View Slide

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

    View Slide

53. DEBUGGING
    

    View Slide

54. DEBUGGING QUERIES
    • Slow query log
    • General log
    • Non-indexed query log
    

    View Slide

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

    View Slide

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;;
    …
    

    View Slide

57. TAMING THE VAGABOND
    

    View Slide

58. TEMPORARY TABLE ISSUES
    • Internal table counter can
    be skipped causing
    assertion failure…
    

    View Slide

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

    View Slide

60. ORDERING
    • Order by performance lies,
    watch your primary keys.
    • DESC is faster than ASC.
    • Specify your primary key
    while adding sort by
    conditions.
    

    View Slide

61. SELECT * FROM LIMIT 10000;
    vs.
    SELECT * FROM ORDER BY PRIMARY DESC LIMIT 10000;
    

    View Slide

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().
    

    View Slide

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.
    

    View Slide

64. SAFER…
    • --safe-updates
    • sql_select_limit = 1000
    • sql_max_join_size =
    1000000
    • Ensures a where clause for
    updates / deletes.
    

    View Slide

65. STRICTER…
    • innodb_strict_mode = ON
    • Raises errors rather than
    warnings for stupidity in
    create table, alter table,
    create index.
    

    View Slide

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
    

    View Slide