Slide 1

Slide 1 text

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

Slide 2

Slide 2 text

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

Slide 3

Slide 3 text

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

Slide 4

Slide 4 text

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

Slide 5

Slide 5 text

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

Slide 6

Slide 6 text

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

Slide 7

Slide 7 text

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

Slide 8

Slide 8 text

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

Slide 9

Slide 9 text

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;

Slide 10

Slide 10 text

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 | +---------+------+

Slide 11

Slide 11 text

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

Slide 12

Slide 12 text

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

Slide 13

Slide 13 text

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????

Slide 14

Slide 14 text

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

Slide 15

Slide 15 text

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

Slide 16

Slide 16 text

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

Slide 17

Slide 17 text

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 | +------------+

Slide 18

Slide 18 text

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 | +------------+

Slide 19

Slide 19 text

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 | +---------+------+-------------------------------------------------------+

Slide 20

Slide 20 text

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

Slide 21

Slide 21 text

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

Slide 22

Slide 22 text

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

Slide 23

Slide 23 text

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

Slide 24

Slide 24 text

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

Slide 25

Slide 25 text

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

Slide 26

Slide 26 text

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 | +---------+------+

Slide 27

Slide 27 text

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

Slide 28

Slide 28 text

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

Slide 29

Slide 29 text

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”

Slide 30

Slide 30 text

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

Slide 31

Slide 31 text

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

Slide 32

Slide 32 text

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

Slide 33

Slide 33 text

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

Slide 34

Slide 34 text

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

Slide 35

Slide 35 text

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

Slide 36

Slide 36 text

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

Slide 37

Slide 37 text

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

Slide 38

Slide 38 text

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

Slide 39

Slide 39 text

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

Slide 40

Slide 40 text

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+

Slide 41

Slide 41 text

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

Slide 42

Slide 42 text

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

Slide 43

Slide 43 text

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

Slide 44

Slide 44 text

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

Slide 45

Slide 45 text

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

Slide 46

Slide 46 text

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

Slide 47

Slide 47 text

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

Slide 48

Slide 48 text

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

Slide 49

Slide 49 text

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

Slide 50

Slide 50 text

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

Slide 51

Slide 51 text

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

Slide 52

Slide 52 text

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

Slide 53

Slide 53 text

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

Slide 54

Slide 54 text

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

Slide 55

Slide 55 text

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

Slide 56

Slide 56 text

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

Slide 57

Slide 57 text

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

Slide 58

Slide 58 text

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

Slide 59

Slide 59 text

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

Slide 60

Slide 60 text

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

Slide 61

Slide 61 text

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

Slide 62

Slide 62 text

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