Seriously, do you still use MySQL?

Presentation at the Web Engineering Düsseldorf Meetup about MySQL & Databases

Transcript

1. Seriously, do you still use MySQL ? Web Engineering Düsseldorf – Wolfgang Gassler - 2017-04-05 1
2. #php #mysql @freelance @consulting #procrastination #research #databases #recommendersystems @uniinnsbruck #talking #listening and #coffee @trivago_tech @schafele Wolfgang Gassler 2
3. MySQL is an address book
 which is able to understand SQL 3
4. MySQL is an address book
 which is able to understand SQL 3
5. Paradigm of the 70s 4
6. Paradigm of the 70s Edgar F. Codd: The Relational Model IBM System R: first implementation Tables, Indexes, SQL… still used in MySQL, Oracle, PostgreSQL,... 4
8. Ingres -> (Sybase -> Microsoft SQL Server) Postgres -> Informix (CTO) -> PostgreSQL C-Store -> Vertica H-Store -> VoltDB SciDB -> Paradigm4 @University of California, Berkeley @MIT Turing Award 2015 Michael Stonebraker 5
9. The End of an Architectural Era
 (It’s Time for a Complete Rewrite)
 
 
 Stonebraker, Michael and Madden, Samuel and Abadi, Daniel J. and Harizopoulos, Stavros and Hachem, Nabil and Helland, Pat
 VLDB ’07, September 23-28, 2007, Vienna, Austria.
 Copyright 2007 VLDB Endowment, ACM 978-1-59593-649-3/07/09. 6
10. One Size Does Not Fit All We conclude that the current RDBMS code lines, while attempting to be a “one size fits all” solution, in fact, excel at nothing. Hence, they are 25 year old legacy code lines that should be retired in favor of a collection of “from scratch” specialized engines. The DBMS vendors (and the research community) should start with a clean sheet of paper and design systems for tomorrow’s requirements, not continue to push code lines and architectures designed for yesterday’s needs. 7
11. One Size Does Not Fit All (Stonebraker) • Main Memory • Multi-threading, Multi-Core • Shared-nothing architecture/ grid-computing • High availability (single node architecture) • No knobs (personnel costs vs. hardware costs) • Transaction Management, Replication and Recovery (all needed?) 8
12. One Size Does Not Fit All (Stonebraker) • Main Memory • Multi-threading, Multi-Core • Shared-nothing architecture/ grid-computing • High availability (single node architecture) • No knobs (personnel costs vs. hardware costs) • Transaction Management, Replication and Recovery (all needed?) 8
13. MySQL is outdated… why NoSQL? • New hardware/paradigms • in memory • multi-core/performance • SSDs optimization • Maintenance/usage/happy admin • Schemaless • JSON • Simple configuration/no knobs 9
14. Join us at scale @ MongoDB 10
15. MySQL Master/Slave Scaling Replication • Horizontal scaling / sharding? • High availability / automated failover? 11
16. Seriously, do you still use MySQL? • Paradigms of the 70s • No new technologies for new hardware • No new cool features like schemaless or JSON • No scaling • No out of the box configuration 12
17. Seriously, do you still use MySQL? • Paradigms of the 70s • No new technologies for new hardware • No new cool features like schemaless or JSON • No scaling • No out of the box configuration 12
18. MySQL lame? 13
19. Example NoSQL Graph-Stores, e.g. Neo4j • Graph model introduced in the 1960s • First graph database IDS developed by Charles Bachman@General Electric in 60s (Turing Award 1973). • IMS 1966 for the Apollo program 14
20. Example NoSQL Graph-Stores, e.g. Neo4j • Graph model introduced in the 1960s • First graph database IDS developed by Charles Bachman@General Electric in 60s (Turing Award 1973). • IMS 1966 for the Apollo program 14
22. Why does everybody love MySQL?
 Outdated paradigms of the 70s 16
23. Why does everybody love MySQL?
 Outdated paradigms of the 70s 16
24. Why does everybody love MySQL?
 Outdated paradigms of the 70s m ature 16
25. New Features (MySQL 5.7) • Performance • Optimizer • JSON • InnoDB improvements / Replication • Performance & SYS schema • Security • GIS 17
26. Sysbench Benchmark: SQL Point Selects 18 Source: http://www.mysql.com/why-mysql/benchmarks/, HW: Intel(R) Xeon(R) CPU E7-8890 v3 (codename Haswell-EX) 4 CPU sockets x 18 cores-HT (144 CPU threads) 2.5 Ghz, 512GB RAM
27. Sysbench Benchmark: OLTP Read Only 19 Source: http://www.mysql.com/why-mysql/benchmarks/, HW: Intel(R) Xeon(R) CPU E7-8890 v3 (codename Haswell-EX) 4 CPU sockets x 18 cores-HT (144 CPU threads) 2.5 Ghz, 512GB RAM
28. Sysbench Benchmark: OLTP Read Write 20 Source: http://www.mysql.com/why-mysql/benchmarks/, HW: Intel(R) Xeon(R) CPU E7-8890 v3 (codename Haswell-EX) 4 CPU sockets x 18 cores-HT (144 CPU threads) 2.5 Ghz, 512GB RAM
29. New features: e.g. JSON • Native JSON type • Parsed and validated during insertion/update • Binary format (read optimized) • Built-in JSON functions • JSON comparators for SQL • Indexes on JSON fields (generated columns) 21
30. Schemaless & no SQL 22
31. Scale? 23
32. Scaling/Sharding is fucking hard 24
33. Scaling/Sharding is fucking hard 24
34. Scaling/Sharding is fucking hard 24
35. Scaling/Sharding is fucking hard • https://www.mongodb.com/presentations/ black-box-mongodb-running-mongodb-scale- parse • http://highscalability.com/blog/2014/3/5/10- things-you-should-know-about-running- mongodb-at-scale.html • https://jepsen.io/ • https://aphyr.com/posts/327-jepsen-mariadb- galera-cluster • https://aphyr.com/posts/322-jepsen-mongodb- stale-reads • https://aphyr.com/posts/284-jepsen-mongodb 24
36. Great community & tooling 25
37. Simple Configuration Wizard 26
38. • MySQL storage engine • Space efficiency (3-4x better compression) • Writing efficiency (10x less write amplification, flash storage lifetime) • RocksDB • LevelDB fork • LSM (log structured merge tree) structure • Key/Value store 27
39. • MySQL storage engine • Space efficiency (3-4x better compression) • Writing efficiency (10x less write amplification, flash storage lifetime) • RocksDB • LevelDB fork • LSM (log structured merge tree) structure • Key/Value store 27
40. • Application layer proxy • Scaling • Connection pooling • Read/Write split • Read/Write sharding • High availability • Failover • Load balancing • Query handling • Query caching • Query rewriting • Query blocking • Query mirroring • Query throttling 28
41. MySQL Orchestrator 29 https://github.com/outbrain/orchestrator
42. MySQL Ecosystem 30
43. Seriously, do you still use MySQL? • MySQL, yes, we can! • Mature, very reliable, ACID • Open for extensions (e.g. new technologies RocksDB, optimized for SSDs) • New features, e.g. JSON, generated columns, GIS extension • Scaling is well supported • Great community & tooling (e.g. Percona tooling, ProxySQL, etc.) • End of an architectural era • Stay hungry, stay foolish! • Try it out, use it if beneficial • But do not use it because it is new 31
44. Seriously, do you still use MySQL? • MySQL, yes, we can! • Mature, very reliable, ACID • Open for extensions (e.g. new technologies RocksDB, optimized for SSDs) • New features, e.g. JSON, generated columns, GIS extension • Scaling is well supported • Great community & tooling (e.g. Percona tooling, ProxySQL, etc.) • End of an architectural era • Stay hungry, stay foolish! • Try it out, use it if beneficial • But do not use it because it is new 31
45. Sources & Co • https://www.facebook.com/MySQLatFacebook/ • https://www.percona.com/live/data-performance-conference-2016/sessions/vitess-complete-story • http://vitess.io/overview/scaling-mysql.html • https://www.percona.com/live/data-performance-conference-2016/sessions/vitess-complete-story • http://lefred.be/content/ha-with-mysql-group-replication-and-proxysql/ • https://www.mongodb.com/presentations/black-box-mongodb-running-mongodb-scale-parse • http://highscalability.com/blog/2014/3/5/10-things-you-should-know-about-running-mongodb-at- scale.html • https://jepsen.io/ • https://aphyr.com/posts/327-jepsen-mariadb-galera-cluster • https://aphyr.com/posts/322-jepsen-mongodb-stale-reads • https://aphyr.com/posts/284-jepsen-mongodb 32