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

MySQL Architecture and Design Practices

MySQL Architecture and Design Practices

Correctly designing a MySQL architecture has a huge impact on the growth of your system for future needs.

In this presentation, we will cover.

- The strengths and weaknesses of MySQL
- What MySQL scale-out architecture means
- High Availability considerations
- The importance of changing key system default configurations
- How to integrate MySQL with existing and new data sources

From this presentation we will create a checklist of what key business and technology decisions should be asked and discussed before building a MySQL based system.

Ronald Bradford

May 09, 2015
Tweet

More Decks by Ronald Bradford

Other Decks in Technology

Transcript

  1. Effective MySQL Architecture and Design Practices
    #mysql @RonaldBradford EffectiveMySQL.com
    Effective MySQL
    Architecture and
    Design Practices
    1
    Ronald Bradford
    May 2015

    View full-size slide

  2. EffectiveMySQL.com
    #mysql @RonaldBradford
    Effective MySQL Architecture and Design Practices
    INFORMATION
    These slides can be found at
    http://effectiveMySQL.com
    2

    View full-size slide

  3. Effective MySQL Architecture and Design Practices
    #mysql @RonaldBradford EffectiveMySQL.com
    What is architecture?
    3

    View full-size slide

  4. Effective MySQL Architecture and Design Practices
    #mysql @RonaldBradford EffectiveMySQL.com
    What is design?
    4

    View full-size slide

  5. EffectiveMySQL.com
    #mysql @RonaldBradford
    Effective MySQL Architecture and Design Practices
    1. The MySQL warning
    2. Do not start with default configuration
    3. Start with MySQL 5.5 or newer
    4. Design with replication in mind
    5. Know the right data types
    5
    Effective TIPS

    View full-size slide

  6. EffectiveMySQL.com
    #mysql @RonaldBradford
    Effective MySQL Architecture and Design Practices
    25+ years Experience
    6
    MySQL has many great features
    that are abused by “lack of good
    defaults,” “poor open source
    reference examples” and “lack of
    experienced software developers.”
    Ronald Bradford
    August 2014

    View full-size slide

  7. Effective MySQL Architecture and Design Practices
    #mysql @RonaldBradford EffectiveMySQL.com
    MySQL Warnings
    7
    1

    View full-size slide

  8. EffectiveMySQL.com
    #mysql @RonaldBradford
    Effective MySQL Architecture and Design Practices
    8
    CREATE SCHEMA IF NOT EXISTS test;
    USE test;
    DROP TABLE IF EXISTS stats;
    CREATE TABLE stats(
    counter TINYINT(4) UNSIGNED NOT NULL,
    val CHAR(1) NULL
    );
    Simple table
    one mandatory integer column
    one optional character column

    View full-size slide

  9. EffectiveMySQL.com
    #mysql @RonaldBradford
    Effective MySQL Architecture and Design Practices
    9
    CREATE SCHEMA IF NOT EXISTS test;
    USE test;
    DROP TABLE IF EXISTS stats;
    CREATE TABLE stats(
    counter TINYINT(4) UNSIGNED NOT NULL,
    val CHAR(1) NULL
    );
    START TRANSACTION;
    INSERT INTO stats(counter) VALUES(-1);
    INSERT INTO stats(counter) VALUES(100);
    INSERT INTO stats(counter) VALUES(999);
    COMMIT;
    SELECT * FROM stats;

    View full-size slide

  10. EffectiveMySQL.com
    #mysql @RonaldBradford
    Effective MySQL Architecture and Design Practices
    10
    START TRANSACTION;
    INSERT INTO stats(counter) VALUES(-1);
    INSERT INTO stats(counter) VALUES(100);
    INSERT INTO stats(counter) VALUES(999);
    COMMIT;
    SELECT * FROM stats;
    +---------+------+
    | counter | val |
    +---------+------+
    | -1 | NULL |
    | 100 | NULL |
    | 999 | NULL |
    +---------+------+
    +---------+------+
    | counter | val |
    +---------+------+
    | 0 | NULL |
    | 100 | NULL |
    | 255 | NULL |
    +---------+------+
    +---------+------+
    | counter | val |
    +---------+------+
    | 100 | NULL |
    +---------+------+

    View full-size slide

  11. EffectiveMySQL.com
    #mysql @RonaldBradford
    Effective MySQL Architecture and Design Practices
    11
    CREATE SCHEMA IF NOT EXISTS test;
    USE test;
    DROP TABLE IF EXISTS stats;
    CREATE TABLE stats(
    counter TINYINT(4) UNSIGNED NOT NULL,
    val CHAR(1) NULL
    );
    TINYINT 1 byte = 8 bits
    (4) meaningless - for display only
    UNSIGNED (non negative)
    2^8 (256 values) Range 0 - 255
    Discuss in
    Point 5

    View full-size slide

  12. EffectiveMySQL.com
    #mysql @RonaldBradford
    Effective MySQL Architecture and Design Practices
    12
    +---------+------+
    | counter | val |
    +---------+------+
    | -1 | NULL |
    | 100 | NULL |
    | 999 | NULL |
    +---------+------+
    +---------+------+
    | counter | val |
    +---------+------+
    | 0 | NULL |
    | 100 | NULL |
    | 255 | NULL |
    +---------+------+
    +---------+------+
    | counter | val |
    +---------+------+
    | 100 | NULL |
    +---------+------+
    TINYINT 1 byte = 8 bits
    UNSIGNED (non negative)
    Range 0 - 255
    ✘ ✘ ✔
    START TRANSACTION;
    ... VALUES(-1);
    ... VALUES(100);
    ... VALUES(999);
    COMMIT;
    SELECT * FROM stats;

    View full-size slide

  13. EffectiveMySQL.com
    #mysql @RonaldBradford
    Effective MySQL Architecture and Design Practices
    Three states
    13
    Default MySQL Behavior for SQL
    ERROR
    Success
    Success, but with warnings
    90+% developers (and products) ignore
    warnings
    Why????

    View full-size slide

  14. EffectiveMySQL.com
    #mysql @RonaldBradford
    Effective MySQL Architecture and Design Practices
    SHOW WARNINGS
    14
    mysql> INSERT INTO stats(counter) VALUES(-1);
    Query OK, 1 row affected, 1 warning (0.03 sec)
    mysql> SHOW WARNINGS\G
    ************************* 1. row *************************
    Level: Warning
    Code: 1264
    Message: Out of range value for column 'counter' at row 1
    1 row in set (0.00 sec)
    mysql> SELECT * FROM stats;
    +---------+------+
    | counter | val |
    +---------+------+
    | 0 | NULL |
    +---------+------+
    Complete loss of Data
    Integrity. Unrecoverable.
    -1 != 0

    View full-size slide

  15. EffectiveMySQL.com
    #mysql @RonaldBradford
    Effective MySQL Architecture and Design Practices
    DEFAULT OPERATION
    15
    “MySQL by default performs silent
    truncations, making a best guess of
    what the value of data should be
    based on the schema design,
    rather than enforcing,
    data in equals data out.”
    Ronald Bradford
    August 2014

    View full-size slide

  16. EffectiveMySQL.com
    #mysql @RonaldBradford
    Effective MySQL Architecture and Design Practices
    truncation Scope
    Not limited to Integers
    Strings, Dates, Float, Decimal
    16

    View full-size slide

  17. EffectiveMySQL.com
    #mysql @RonaldBradford
    Effective MySQL Architecture and Design Practices
    string example
    String Example
    17
    CREATE TABLE tour_locations (place VARCHAR(10));
    INSERT INTO tour_locations VALUES
    ('Helsinki, Finland'),
    ('Stockholm, Sweden'),
    ('Riga, Latvia'),
    ('Tallinn, Estonia');
    SELECT place FROM tour_locations;
    +------------+
    | place |
    +------------+
    | Helsinki, |
    | Stockholm, |
    | Riga, Latv |
    | Tallinn, E |
    +------------+

    View full-size slide

  18. EffectiveMySQL.com
    #mysql @RonaldBradford
    Effective MySQL Architecture and Design Practices
    date example
    String Example
    18
    CREATE TABLE tour_dates(event_date DATE);
    INSERT INTO tour_dates VALUES ('2014-02-28'),
    ('2014-02-29'),('2014-03-00'),('2014-31-07');
    SELECT event_date FROM tour_dates;
    +------------+
    | event_date |
    +------------+
    | 2014-02-28 |
    | 0000-00-00 |
    | 2014-03-00 | * ZERO DAY STORED
    | 0000-00-00 |
    +------------+

    View full-size slide

  19. EffectiveMySQL.com
    #mysql @RonaldBradford
    Effective MySQL Architecture and Design Practices
    other warnings
    19
    SELECT cnt AS methods, COUNT(*) AS cnt
    ...
    GROUP BY cnt
    +---------+------+----------------------------------------------+
    | Level | Code | Message |
    +---------+------+----------------------------------------------+
    | Warning | 1052 | Column 'cnt' in group statement is ambiguous |
    +---------+------+----------------------------------------------+
    +---------+------+-------------------------------------------------------+
    | Level | Code | Message |
    +---------+------+-------------------------------------------------------+
    | Warning | 1364 | Field 'password' doesn't have a default value |
    | Warning | 1364 | Field 'salt' doesn't have a default value |
    | Warning | 1364 | Field 'token' doesn't have a default value |
    +---------+------+-------------------------------------------------------+

    View full-size slide

  20. EffectiveMySQL.com
    #mysql @RonaldBradford
    Effective MySQL Architecture and Design Practices
    ideal operation
    MySQL defaults can be changed
    MySQL can and does support good
    data and referential integrity
    MySQL defaults change and improve
    with newer versions
    20

    View full-size slide

  21. Effective MySQL Architecture and Design Practices
    #mysql @RonaldBradford EffectiveMySQL.com
    MySQL Configuration
    21
    2

    View full-size slide

  22. EffectiveMySQL.com
    #mysql @RonaldBradford
    Effective MySQL Architecture and Design Practices
    Observation
    22
    “Using MySQL configuration
    defaults leads to loss of data
    integrity that can be unrecoverable.
    Code that will fail when good
    defaults are enabled.”
    Huge cost to business.
    Ronald Bradford
    August 2014

    View full-size slide

  23. EffectiveMySQL.com
    #mysql @RonaldBradford
    Effective MySQL Architecture and Design Practices
    SERVER SQL MODE
    23
    "Modes define what SQL
    syntax MySQL should support
    and what kind of data
    validation checks it should
    perform."
    http://dev.mysql.com/doc/refman/5.5/en/sql-mode.html
    Better practices exist

    View full-size slide

  24. Via Configuration
    Dynamic
    EffectiveMySQL.com
    #mysql @RonaldBradford
    Effective MySQL Architecture and Design Practices
    CONFIGURATION
    [mysqld]
    sql_mode=
    'STRICT_ALL_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,
    NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,ONLY_FULL_GR
    OUP_BY'
    24
    mysql > SET GLOBAL
    sql_mode='STRICT_ALL_TABLES,NO_ENGINE_SUBSTITUTION,NO_Z
    ERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,ONL
    Y_FULL_GROUP_BY';

    View full-size slide

  25. EffectiveMySQL.com
    #mysql @RonaldBradford
    Effective MySQL Architecture and Design Practices
    sql_mode
    NO_ENGINE_SUBSTITUTION
    STRICT_ALL_TABLES
    NO_ZERO_DATE
    NO_ZERO_IN_DATE
    ERROR_FOR_DIVISION_BY_ZERO
    ONLY_FULL_GROUP_BY
    25
    sql_mode options
    5.6
    5.7
    deprecated in 5.7

    View full-size slide

  26. EffectiveMySQL.com
    #mysql @RonaldBradford
    Effective MySQL Architecture and Design Practices
    STRICT_ALL_TABLES
    String Example
    26
    SET SESSION sql_mode='STRICT_ALL_TABLES';
    START TRANSACTION;
    INSERT INTO stats(counter) VALUES(-1);
    ERROR 1264 (22003): Out of range value for column
    'counter' at row 1
    INSERT INTO stats(counter) VALUES(100);
    INSERT INTO stats(counter) VALUES(999);
    ERROR 1264 (22003): Out of range value for column
    'counter' at row 1
    COMMIT;
    SELECT * FROM stats;
    +---------+------+
    | counter | val |
    +---------+------+
    | 100 | NULL |
    +---------+------+

    View full-size slide

  27. EffectiveMySQL.com
    #mysql @RonaldBradford
    Effective MySQL Architecture and Design Practices
    application logic
    27
    SELECT * FROM stats;
    +---------+------+
    | counter | val |
    +---------+------+
    | 100 | NULL |
    +---------+------+
    $ mysql < stats.sql
    $ mysql -vvv -e "SELECT * FROM stats"
    SELECT * FROM stats
    --------------
    Empty set (0.00 sec)

    ?
    mysql interactive mode
    An application would ROLLBACK on ERROR

    View full-size slide

  28. EffectiveMySQL.com
    #mysql @RonaldBradford
    Effective MySQL Architecture and Design Practices
    GROUP BY example
    String Example
    28
    SELECT customer_id,sum(amount)
    FROM order
    WHERE payment_method='CreditCard';
    ...
    SET SESSION sql_mode='ONLY_FULL_GROUP_BY';
    SELECT customer_id,sum(amount)
    FROM order
    WHERE payment_method='CreditCard';
    ERROR 1140 (42000): Mixing of GROUP columns
    (MIN(),MAX(),COUNT(),...) with no GROUP columns is
    illegal if there is no GROUP BY clause

    View full-size slide

  29. EffectiveMySQL.com
    #mysql @RonaldBradford
    Effective MySQL Architecture and Design Practices
    Observation
    29
    “Poor defaults are not limited
    to SQL syntax, but data
    recoverability, data consistency,
    backup and recovery abilities,
    data access and locking”

    View full-size slide

  30. InnoDB
    innodb_log_file_size ~ 256M - 1G
    depending on version, buffer size
    EffectiveMySQL.com
    #mysql @RonaldBradford
    Effective MySQL Architecture and Design Practices
    CONFIGURATION
    [mysqld]
    default_storage_engine=InnoDB # now default in 5.5
    innodb_file_per_table # now default in 5.6
    innodb_log_file_size= N # default of 5M
    innodb_flush_method = O_DIRECT
    30

    View full-size slide

  31. EffectiveMySQL.com
    #mysql @RonaldBradford
    Effective MySQL Architecture and Design Practices
    mysqldump
    DEFAULT
    --lock-tables
    31
    Anti defaults
    Discussed in Web
    Operations
    presentation
    NOT DEFAULT
    --single-transaction
    --master-data
    --dump-slave (5.5)
    --routines

    View full-size slide

  32. Effective MySQL Architecture and Design Practices
    #mysql @RonaldBradford EffectiveMySQL.com
    MySQL Versions
    32
    3

    View full-size slide

  33. EffectiveMySQL.com
    #mysql @RonaldBradford
    Effective MySQL Architecture and Design Practices
    Version 5.5 GA (12/2010)
    Version 5.6 GA (3/2013)
    Version 5.7 DMR (3/2014)
    Release cycle around every 2 years
    33
    Oracle supplied

    View full-size slide

  34. EffectiveMySQL.com
    #mysql @RonaldBradford
    Effective MySQL Architecture and Design Practices
    Repos
    ORACLE PROVIDED
    RedHat/CentOS/OL Yum
    Debian/Ubuntu Apt
    3rd PARTY
    Legacy mysql.com rpm’s
    Others
    34
    Migration
    complexities
    Server/Connectors/
    Workbench/Utilities/...

    View full-size slide

  35. Effective MySQL Architecture and Design Practices
    #mysql @RonaldBradford EffectiveMySQL.com
    MySQL Replication
    35
    4

    View full-size slide

  36. EffectiveMySQL.com
    #mysql @RonaldBradford
    Effective MySQL Architecture and Design Practices
    basics
    36
    http://dev.mysql.com/doc/refman/5.6/en/replication.html
    1 2
    3 4
    5

    View full-size slide

  37. EffectiveMySQL.com
    #mysql @RonaldBradford
    Effective MySQL Architecture and Design Practices
    reality
    37

    View full-size slide

  38. EffectiveMySQL.com
    #mysql @RonaldBradford
    Effective MySQL Architecture and Design Practices
    Not configured ready by default
    Essential in all production systems
    Asynchronous (default)
    Delay
    Drift
    Semi-Synchronous (5.6+)
    38
    MySQL Replication

    View full-size slide

  39. Master Config (Required)
    Master user privileges (Required)
    Master Config (Recommended)
    EffectiveMySQL.com
    #mysql @RonaldBradford
    Effective MySQL Architecture and Design Practices
    CONFIGURATION
    [mysqld]
    server_id=N
    log-bin
    39
    binlog_format=MIXED
    expire_logs_days=14
    CREATE USER [email protected] IDENTIFIED BY 'somepassword';
    GRANT REPLICATION SLAVE ON *.* TO [email protected];

    View full-size slide

  40. Server UUID
    Auto created on startup
    EffectiveMySQL.com
    #mysql @RonaldBradford
    Effective MySQL Architecture and Design Practices
    5.6 CONFIGURATION
    $ cat [datadir]/auto.cnf
    [auto]
    server-uuid=6fc5b8e6-1b43-11e4-b9f8-4f3c408e0a83
    40
    Be wary when
    cloning a slave. You
    must remove this file
    Only for 5.6+

    View full-size slide

  41. Slave (required)
    Slave (Recommended)
    Slave (MySQL MHA)
    EffectiveMySQL.com
    #mysql @RonaldBradford
    Effective MySQL Architecture and Design Practices
    CONFIGURATION
    [mysqld]
    server_id=M
    41
    read_only=TRUE
    master-info-file=/mysql/binlog/master.info
    relay-log=/mysql/binlog/mysql-relay-bin
    relay-log-index=/mysql/binlog/mysql-relay-bin.index
    relay-log-info-file=/mysql/binlog/relay-log.info
    relay_log_purge=0

    View full-size slide

  42. EffectiveMySQL.com
    #mysql @RonaldBradford
    Effective MySQL Architecture and Design Practices
    delay
    Replication is Asynchronous
    Slaves pull from the Master
    Delay can occur
    A single long running query
    SQL apply is single threaded
    5.6+ Multi-threading options
    Not slave crash safe (5.6 improvements)
    42

    View full-size slide

  43. EffectiveMySQL.com
    #mysql @RonaldBradford
    Effective MySQL Architecture and Design Practices
    drift
    STATEMENT format
    ROW/MIXED format (from 5.1)
    Not Read Only (--read_only)
    SUPER privilege
    --skip-slave-errors
    SQL_SLAVE_SKIP_COUNTER
    43

    View full-size slide

  44. EffectiveMySQL.com
    #mysql @RonaldBradford
    Effective MySQL Architecture and Design Practices
    replication modes
    Classic Replication
    Transaction Based Replication (5.6+)
    GTID
    Transaction based
    Improved consistency
    Simplified replication management
    44

    View full-size slide

  45. EffectiveMySQL.com
    #mysql @RonaldBradford
    Effective MySQL Architecture and Design Practices
    More reading
    Common problems
    Improving features
    Multi-master replication
    Replication tools
    Extending replication
    Configuration
    Monitoring
    45

    View full-size slide

  46. Effective MySQL Architecture and Design Practices
    #mysql @RonaldBradford EffectiveMySQL.com
    MySQL Data Types
    46
    5

    View full-size slide

  47. EffectiveMySQL.com
    #mysql @RonaldBradford
    Effective MySQL Architecture and Design Practices
    Numbers
    BIT
    TINYINT/BOOLEAN,
    INT,BIGINT, SMALLINT,MEDIUMINT
    FLOAT
    DOUBLE
    DECIMAL
    47

    View full-size slide

  48. EffectiveMySQL.com
    #mysql @RonaldBradford
    Effective MySQL Architecture and Design Practices
    money
    How do you store money?
    FLOAT/DOUBLE
    DECIMAL(13,2)
    BIGINT
    SIGNED or UNSIGNED
    48
    ISO Standard 13 digits for
    cents

    View full-size slide

  49. EffectiveMySQL.com
    #mysql @RonaldBradford
    Effective MySQL Architecture and Design Practices
    character
    CHAR
    VARCHAR
    TEXT,TINYTEXT,MEDIUMTEXT,LON
    GTEXT
    ENUM, SET
    49
    TEXT stores 64K

    View full-size slide

  50. EffectiveMySQL.com
    #mysql @RonaldBradford
    Effective MySQL Architecture and Design Practices
    blob
    VARBINARY
    BLOB,TINYBLOB,MEDIUMBLOB,LO
    NGBLOB
    50

    View full-size slide

  51. EffectiveMySQL.com
    #mysql @RonaldBradford
    Effective MySQL Architecture and Design Practices
    date/time
    DATE
    TIME
    YEAR
    DATETIME
    TIMESTAMP
    51
    Millisecond support
    (5.6+)

    View full-size slide

  52. EffectiveMySQL.com
    #mysql @RonaldBradford
    Effective MySQL Architecture and Design Practices
    Timestamp
    Seconds since EPOCH
    Supports DEFAULT and ON UPDATE
    CURRENT_TIMESTAMP syntax
    Complexities with sql_mode and zero
    dates
    52

    View full-size slide

  53. EffectiveMySQL.com
    #mysql @RonaldBradford
    Effective MySQL Architecture and Design Practices
    constraints
    UNSIGNED
    Free negative number constraint
    53

    View full-size slide

  54. EffectiveMySQL.com
    #mysql @RonaldBradford
    Effective MySQL Architecture and Design Practices
    constraints
    NOT NULL
    Define columns NOT NULL first
    Consider NULL when unknown
    54

    View full-size slide

  55. EffectiveMySQL.com
    #mysql @RonaldBradford
    Effective MySQL Architecture and Design Practices
    constraints
    ENUM
    Set of known values
    1 Byte length, yet offers long value
    results
    55

    View full-size slide

  56. EffectiveMySQL.com
    #mysql @RonaldBradford
    Effective MySQL Architecture and Design Practices
    constraints
    ENUM
    56
    CREATE TABLE weather(
    season ENUM('Spring','Summer','Autumn','Winter') NOT NULL
    );
    SET SESSION SQL_MODE='';
    INSERT INTO weather(season) VALUES ('Fall');
    Query OK, 1 row affected, 1 warning (0.00 sec)
    SELECT * FROM weather;
    +--------+
    | season |
    +--------+
    | |
    +--------+
    SET SESSION SQL_MODE='STRICT_ALL_TABLES';
    ERROR 1265 (01000): Data truncated for column 'season' at row 1

    View full-size slide

  57. EffectiveMySQL.com
    #mysql @RonaldBradford
    Effective MySQL Architecture and Design Practices
    autoincrement
    Use INT UNSIGNED not BIGINT
    4 bytes v 8 bytes
    Doubles size of every secondary index
    57

    View full-size slide

  58. Effective MySQL Architecture and Design Practices
    #mysql @RonaldBradford EffectiveMySQL.com
    What next?
    58
    ?

    View full-size slide

  59. EffectiveMySQL.com
    #mysql @RonaldBradford
    Effective MySQL Architecture and Design Practices
    More discussion
    Why you need a System Architect
    Using SSL (0.9.8 v 1.0.1)
    High Availability (HA) options
    Cloud Usage/Variants/Other solutions
    Integration with other products
    Development tools,resources,techniques
    ...
    59

    View full-size slide

  60. EffectiveMySQL.com
    #mysql @RonaldBradford
    Effective MySQL Architecture and Design Practices
    Is data integrity important?
    Scale out and HA can be complex
    Develop your developers
    Get to Version 5.5/5.6 ASAP
    60
    Top Tips

    View full-size slide

  61. EffectiveMySQL.com
    #mysql @RonaldBradford
    Effective MySQL Architecture and Design Practices
    25+ years Experience
    61
    “To right certain wrongs we must
    look at the foundations. Simple and
    correct decisions have a
    momentous impact in the
    productivity of resources and
    success of business systems”.
    Ronald Bradford
    August 2014

    View full-size slide

  62. EffectiveMySQL.com
    #mysql @RonaldBradford
    Effective MySQL Architecture and Design Practices
    Conclusion
    Copies of these slides can be found at
    http://effectiveMySQL.com
    [email protected]
    62

    View full-size slide