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.

Ef8a4161c1e7ce34ea50c491ad99a67e?s=128

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
  2. EffectiveMySQL.com #mysql @RonaldBradford Effective MySQL Architecture and Design Practices INFORMATION

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

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

    is design? 4
  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
  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
  7. Effective MySQL Architecture and Design Practices #mysql @RonaldBradford EffectiveMySQL.com MySQL

    Warnings 7 1
  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
  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;
  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 | +---------+------+
  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
  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; ✔
  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????
  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
  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
  16. EffectiveMySQL.com #mysql @RonaldBradford Effective MySQL Architecture and Design Practices truncation

    Scope Not limited to Integers Strings, Dates, Float, Decimal 16
  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 | +------------+
  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 | +------------+
  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 | +---------+------+-------------------------------------------------------+
  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
  21. Effective MySQL Architecture and Design Practices #mysql @RonaldBradford EffectiveMySQL.com MySQL

    Configuration 21 2
  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
  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
  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';
  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
  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 | +---------+------+
  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
  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
  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”
  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
  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
  32. Effective MySQL Architecture and Design Practices #mysql @RonaldBradford EffectiveMySQL.com MySQL

    Versions 32 3
  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
  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/...
  35. Effective MySQL Architecture and Design Practices #mysql @RonaldBradford EffectiveMySQL.com MySQL

    Replication 35 4
  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
  37. EffectiveMySQL.com #mysql @RonaldBradford Effective MySQL Architecture and Design Practices reality

    37
  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
  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 repl@99.99.99.99 IDENTIFIED BY 'somepassword'; GRANT REPLICATION SLAVE ON *.* TO repl@99.99.99.99;
  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+
  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
  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
  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
  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
  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
  46. Effective MySQL Architecture and Design Practices #mysql @RonaldBradford EffectiveMySQL.com MySQL

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

    BIT TINYINT/BOOLEAN, INT,BIGINT, SMALLINT,MEDIUMINT FLOAT DOUBLE DECIMAL 47
  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
  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
  50. EffectiveMySQL.com #mysql @RonaldBradford Effective MySQL Architecture and Design Practices blob

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

    DATE TIME YEAR DATETIME TIMESTAMP 51 Millisecond support (5.6+)
  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
  53. EffectiveMySQL.com #mysql @RonaldBradford Effective MySQL Architecture and Design Practices constraints

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

    NOT NULL Define columns NOT NULL first Consider NULL when unknown 54
  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
  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
  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
  58. Effective MySQL Architecture and Design Practices #mysql @RonaldBradford EffectiveMySQL.com What

    next? 58 ?
  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
  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
  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
  62. EffectiveMySQL.com #mysql @RonaldBradford Effective MySQL Architecture and Design Practices Conclusion

    Copies of these slides can be found at http://effectiveMySQL.com ronald@EffectiveMySQL.com 62