Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Taming the MySQL Vagabond

Taming the MySQL Vagabond

Mike Willbanks

November 14, 2015
Tweet

More Decks by Mike Willbanks

Other Decks in Technology

Transcript

  1. TAMING THE MYSQL
    VAGABOND
    Madison PHP Conference 2015

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  4. PERFORMANCE TUNING

    View full-size slide

  5. CONFIGURATION

    View full-size 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 full-size 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 full-size 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 full-size 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 full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size 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 full-size slide

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

    View full-size 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 full-size 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 full-size 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 full-size slide

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

    View full-size 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 full-size 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 full-size slide

  21. • SELECT SQL_CACHE ….

    View full-size 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 full-size slide

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

    View full-size 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 full-size slide

  25. IDENTIFYING QUERY PERFORMANCE

    View full-size slide

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

    View full-size 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 full-size 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 full-size 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 full-size 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 full-size 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 full-size 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 full-size slide

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

    View full-size 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 full-size slide

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

    View full-size 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 full-size 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 full-size 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 full-size slide

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

    View full-size 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 full-size 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 full-size 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 full-size 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 full-size 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 full-size slide

  45. HOT DB CHANGES

    View full-size slide

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

    View full-size 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 full-size slide

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

    View full-size slide

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

    View full-size slide

  50. HOT BACKUP
    • mysql enterprise backup
    • xtrabackup

    View full-size 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 full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  55. ------------------------
    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 full-size slide

  56. TAMING THE VAGABOND

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  61. 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 full-size slide

  62. 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 full-size slide

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

    View full-size slide

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

    View full-size slide

  65. • 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 full-size slide