![Page 1: MySQL vs MariaDB - Amazon S3€¦ · InnoDB InnoDB MyISAM DDL Performance Smart Doublewrite Dynamic Buffer Pool More Transportable InnoDB as Default Spatial Indexes Improved Crash](https://reader034.vdocuments.us/reader034/viewer/2022042613/5f98a440cceaa613500e4c1e/html5/thumbnails/1.jpg)
![Page 2: MySQL vs MariaDB - Amazon S3€¦ · InnoDB InnoDB MyISAM DDL Performance Smart Doublewrite Dynamic Buffer Pool More Transportable InnoDB as Default Spatial Indexes Improved Crash](https://reader034.vdocuments.us/reader034/viewer/2022042613/5f98a440cceaa613500e4c1e/html5/thumbnails/2.jpg)
MySQL vs MariaDBWhere are we now?
![Page 3: MySQL vs MariaDB - Amazon S3€¦ · InnoDB InnoDB MyISAM DDL Performance Smart Doublewrite Dynamic Buffer Pool More Transportable InnoDB as Default Spatial Indexes Improved Crash](https://reader034.vdocuments.us/reader034/viewer/2022042613/5f98a440cceaa613500e4c1e/html5/thumbnails/3.jpg)
Hey!
![Page 4: MySQL vs MariaDB - Amazon S3€¦ · InnoDB InnoDB MyISAM DDL Performance Smart Doublewrite Dynamic Buffer Pool More Transportable InnoDB as Default Spatial Indexes Improved Crash](https://reader034.vdocuments.us/reader034/viewer/2022042613/5f98a440cceaa613500e4c1e/html5/thumbnails/4.jpg)
#cPConf
A BRIEF HISTORY OF THE UNIVERSE
(of MySQL and MariaDB)
![Page 5: MySQL vs MariaDB - Amazon S3€¦ · InnoDB InnoDB MyISAM DDL Performance Smart Doublewrite Dynamic Buffer Pool More Transportable InnoDB as Default Spatial Indexes Improved Crash](https://reader034.vdocuments.us/reader034/viewer/2022042613/5f98a440cceaa613500e4c1e/html5/thumbnails/5.jpg)
#cPConf
Herman Hollerith
![Page 6: MySQL vs MariaDB - Amazon S3€¦ · InnoDB InnoDB MyISAM DDL Performance Smart Doublewrite Dynamic Buffer Pool More Transportable InnoDB as Default Spatial Indexes Improved Crash](https://reader034.vdocuments.us/reader034/viewer/2022042613/5f98a440cceaa613500e4c1e/html5/thumbnails/6.jpg)
#cPConf
1981(~1979)
Unireg Begins
Essentially, the origin of what we know MySQL as today, establishing its code base.
![Page 7: MySQL vs MariaDB - Amazon S3€¦ · InnoDB InnoDB MyISAM DDL Performance Smart Doublewrite Dynamic Buffer Pool More Transportable InnoDB as Default Spatial Indexes Improved Crash](https://reader034.vdocuments.us/reader034/viewer/2022042613/5f98a440cceaa613500e4c1e/html5/thumbnails/7.jpg)
#cPConf
1995Unireg Begins
Essentially, the origin of what we know MySQL as today, establishing its code base.
MySQL is released
In 1995, MySQL was first released for usage under dual licensing.
1981(~1979)
![Page 8: MySQL vs MariaDB - Amazon S3€¦ · InnoDB InnoDB MyISAM DDL Performance Smart Doublewrite Dynamic Buffer Pool More Transportable InnoDB as Default Spatial Indexes Improved Crash](https://reader034.vdocuments.us/reader034/viewer/2022042613/5f98a440cceaa613500e4c1e/html5/thumbnails/8.jpg)
#cPConf
1995
2000
Unireg Begins
Essentially, the origin of what we know MySQL as today, establishing its code base.
MySQL Goes Open
The MySQL platform goes Open Source, and begins to follow the terms of the GPL.
MySQL is released
In 1995, MySQL was first released for usage under dual licensing.
1981(~1979)
![Page 9: MySQL vs MariaDB - Amazon S3€¦ · InnoDB InnoDB MyISAM DDL Performance Smart Doublewrite Dynamic Buffer Pool More Transportable InnoDB as Default Spatial Indexes Improved Crash](https://reader034.vdocuments.us/reader034/viewer/2022042613/5f98a440cceaa613500e4c1e/html5/thumbnails/9.jpg)
#cPConf
1995
2000
2008Unireg Begins
Essentially, the origin of what we know MySQL as today, establishing its code base.
MySQL Goes Open
The MySQL platform goes Open Source, and begins to follow the terms of the GPL.
MySQL is released
In 1995, MySQL was first released for usage under dual licensing.
Sun Acquires MySQL
Sun Microsystems purchases MySQL AB for $1 billion (only to be acquired by Oracle in 2009 for $7.4 billion)
1981(~1979)
![Page 10: MySQL vs MariaDB - Amazon S3€¦ · InnoDB InnoDB MyISAM DDL Performance Smart Doublewrite Dynamic Buffer Pool More Transportable InnoDB as Default Spatial Indexes Improved Crash](https://reader034.vdocuments.us/reader034/viewer/2022042613/5f98a440cceaa613500e4c1e/html5/thumbnails/10.jpg)
#cPConf
1995
2000
2008
2009
Unireg Begins
Essentially, the origin of what we know MySQL as today, establishing its code base.
MySQL Goes Open
The MySQL platform goes Open Source, and begins to follow the terms of the GPL.
MariaDB is founded
As Oracle begins drifting further away from open source, Monty founds MariaDB designed as a pluggable replacement.
MySQL is released
In 1995, MySQL was first released for usage under dual licensing.
Sun Acquires MySQL
Sun Microsystems purchases MySQL AB for $1 billion (only to be acquired by Oracle in 2009 for $7.4 billion)
1981(~1979)
![Page 11: MySQL vs MariaDB - Amazon S3€¦ · InnoDB InnoDB MyISAM DDL Performance Smart Doublewrite Dynamic Buffer Pool More Transportable InnoDB as Default Spatial Indexes Improved Crash](https://reader034.vdocuments.us/reader034/viewer/2022042613/5f98a440cceaa613500e4c1e/html5/thumbnails/11.jpg)
#cPConf
1995
2000
2008
2009
2015Unireg Begins
Essentially, the origin of what we know MySQL as today, establishing its code base.
MySQL Goes Open
The MySQL platform goes Open Source, and begins to follow the terms of the GPL.
MariaDB is founded
As Oracle begins drifting further away from open source, Monty founds MariaDB designed as a pluggable replacement.
MySQL is released
In 1995, MySQL was first released for usage under dual licensing.
Sun Acquires MySQL
Sun Microsystems purchases MySQL AB for $1 billion (only to be acquired by Oracle in 2009 for $7.4 billion)
cPanel-supported
In response to industry shifts indicating a move towards MariaDB, cPanel officially introduced support for it into the 11.48 release.
1981(~1979)
![Page 12: MySQL vs MariaDB - Amazon S3€¦ · InnoDB InnoDB MyISAM DDL Performance Smart Doublewrite Dynamic Buffer Pool More Transportable InnoDB as Default Spatial Indexes Improved Crash](https://reader034.vdocuments.us/reader034/viewer/2022042613/5f98a440cceaa613500e4c1e/html5/thumbnails/12.jpg)
#cPConf
1995
2000
2008
2009
2015
Now & Later
Unireg Begins
Essentially, the origin of what we know MySQL as today, establishing its code base.
MySQL Goes Open
The MySQL platform goes Open Source, and begins to follow the terms of the GPL.
MariaDB is founded
As Oracle begins drifting further away from open source, Monty founds MariaDB designed as a pluggable replacement.
MySQL is released
In 1995, MySQL was first released for usage under dual licensing.
Sun Acquires MySQL
Sun Microsystems purchases MySQL AB for $1 billion (only to be acquired by Oracle in 2009 for $7.4 billion)
cPanel-supported
In response to industry shifts indicating a move towards MariaDB, cPanel officially introduced support for it into the 11.48 release.
The battle continues
While MariaDB made major progress for the industry, MySQL 5.7 and 8.0 have brought new drive to the competition.
1981(~1979)
![Page 13: MySQL vs MariaDB - Amazon S3€¦ · InnoDB InnoDB MyISAM DDL Performance Smart Doublewrite Dynamic Buffer Pool More Transportable InnoDB as Default Spatial Indexes Improved Crash](https://reader034.vdocuments.us/reader034/viewer/2022042613/5f98a440cceaa613500e4c1e/html5/thumbnails/13.jpg)
#cPConf
WHAT’S THE DIFFERENCE?
![Page 14: MySQL vs MariaDB - Amazon S3€¦ · InnoDB InnoDB MyISAM DDL Performance Smart Doublewrite Dynamic Buffer Pool More Transportable InnoDB as Default Spatial Indexes Improved Crash](https://reader034.vdocuments.us/reader034/viewer/2022042613/5f98a440cceaa613500e4c1e/html5/thumbnails/14.jpg)
#cPConf
MySQL 5.7 MARIADB 10.2JSON DATATYPE JSON DATATYPE (KIND
ENGINE IMPROVEMENTSENGINE IMPROVEMENTS
SECURITY IMPROVEMENTS SECURITY IMPROVEMENTS
ADDED SYNTAX/QUERIESADDED SYNTAX/QUERIES
![Page 15: MySQL vs MariaDB - Amazon S3€¦ · InnoDB InnoDB MyISAM DDL Performance Smart Doublewrite Dynamic Buffer Pool More Transportable InnoDB as Default Spatial Indexes Improved Crash](https://reader034.vdocuments.us/reader034/viewer/2022042613/5f98a440cceaa613500e4c1e/html5/thumbnails/15.jpg)
#cPConf
MySQL 5.7 MARIADB 10.2JSON DATATYPE JSON DATATYPE (KIND
Stores data in binary Stores data as LONGTEXT
25 JSON functions 26 JSON functions
JSON_COMPAC JSON_DETAILE
JSON_EXISTS JSON_LOOSE
JSON_QUERY JSON_VALUE-> ->>JSON_EXTRACT JSON_UNQUOTE(JSON_EX
JSON_PRETTY JSON_STORAGE_
JSON_MERGE_PATC JSON_MERGE_PRE
SELECT JSON_EXTRACT(c, “$.id”)
SELECT c->"$.id"
![Page 16: MySQL vs MariaDB - Amazon S3€¦ · InnoDB InnoDB MyISAM DDL Performance Smart Doublewrite Dynamic Buffer Pool More Transportable InnoDB as Default Spatial Indexes Improved Crash](https://reader034.vdocuments.us/reader034/viewer/2022042613/5f98a440cceaa613500e4c1e/html5/thumbnails/16.jpg)
#cPConf
MySQL 5.7 MARIADB 10.2ENGINE IMPROVEMENTS ENGINE IMPROVEMENTS
InnoDB
MyISAM
DDL Performance Smart Doublewrite
Dynamic Buffer Pool More Transportable
mysql> SHOW STATUS WHERE Variable_name='InnoDB_buffer_pool_resize_status';+----------------------------------+----------------------------------+| Variable_name | Value |+----------------------------------+----------------------------------+| Innodb_buffer_pool_resize_status | Resizing also other hash tables. |+----------------------------------+----------------------------------+
![Page 17: MySQL vs MariaDB - Amazon S3€¦ · InnoDB InnoDB MyISAM DDL Performance Smart Doublewrite Dynamic Buffer Pool More Transportable InnoDB as Default Spatial Indexes Improved Crash](https://reader034.vdocuments.us/reader034/viewer/2022042613/5f98a440cceaa613500e4c1e/html5/thumbnails/17.jpg)
#cPConf
MySQL 5.7 MARIADB 10.2ENGINE IMPROVEMENTS ENGINE IMPROVEMENTS
InnoDB InnoDB
MyISAM
DDL Performance Smart Doublewrite
Dynamic Buffer Pool More Transportable
InnoDB as Default
Spatial Indexes Improved Crash Recovery
![Page 18: MySQL vs MariaDB - Amazon S3€¦ · InnoDB InnoDB MyISAM DDL Performance Smart Doublewrite Dynamic Buffer Pool More Transportable InnoDB as Default Spatial Indexes Improved Crash](https://reader034.vdocuments.us/reader034/viewer/2022042613/5f98a440cceaa613500e4c1e/html5/thumbnails/18.jpg)
#cPConf
MySQL 5.7 MARIADB 10.2ENGINE IMPROVEMENTS ENGINE IMPROVEMENTS
InnoDB InnoDB
MyISAM
DDL Performance Smart Doublewrite
Dynamic Buffer Pool More Transportable
InnoDB as Default
Spatial Indexes Improved Crash Recovery
MyISAM
Segmented Key Caching
Adjustable Hash Size
key_cache_segments •Description: The number of segments in a key cache.•Commandline: --key-cache-segments=#•Scope: Global•Dynamic: Yes•Type: numeric•Default Value: 0 (non-segmented)•Range: 0 to 64
![Page 19: MySQL vs MariaDB - Amazon S3€¦ · InnoDB InnoDB MyISAM DDL Performance Smart Doublewrite Dynamic Buffer Pool More Transportable InnoDB as Default Spatial Indexes Improved Crash](https://reader034.vdocuments.us/reader034/viewer/2022042613/5f98a440cceaa613500e4c1e/html5/thumbnails/19.jpg)
#cPConf
mysql> SHOW STATUS WHERE Variable_name='InnoDB_buffer_pool_resize_status';+----------------------------------+----------------------------------+| Variable_name | Value |+----------------------------------+----------------------------------+| Innodb_buffer_pool_resize_status | Resizing also other hash tables. |+----------------------------------+----------------------------------+
MySQL 5.7 MARIADB 10.2ENGINE IMPROVEMENTS ENGINE IMPROVEMENTS
InnoDB
MyISAMMyRocks
Aria
CONNECT
MongoDB
DDL Performance Smart Doublewrite
Dynamic Buffer Pool More Transportable
InnoDB
MyISAM
![Page 20: MySQL vs MariaDB - Amazon S3€¦ · InnoDB InnoDB MyISAM DDL Performance Smart Doublewrite Dynamic Buffer Pool More Transportable InnoDB as Default Spatial Indexes Improved Crash](https://reader034.vdocuments.us/reader034/viewer/2022042613/5f98a440cceaa613500e4c1e/html5/thumbnails/20.jpg)
#cPConf
MySQL 5.7 MARIADB 10.2SECURITY IMPROVEMENTS SECURITY IMPROVEMENTS
“Secure by Default”
User table constraints
Single Root Require Secure Transport
Command-Line Format--require-secure-transport[={OFF|ON}]Introduced: 5.7.8System Variable: require_secure_transportScope: GlobalDynamic: YesType: BooleanDefault Value: OFF
![Page 21: MySQL vs MariaDB - Amazon S3€¦ · InnoDB InnoDB MyISAM DDL Performance Smart Doublewrite Dynamic Buffer Pool More Transportable InnoDB as Default Spatial Indexes Improved Crash](https://reader034.vdocuments.us/reader034/viewer/2022042613/5f98a440cceaa613500e4c1e/html5/thumbnails/21.jpg)
#cPConf
Command-Line Format--require-secure-transport[={OFF|ON}]Introduced: 5.7.8System Variable: require_secure_transportScope: GlobalDynamic: YesType: BooleanDefault Value: OFF
MySQL 5.7 MARIADB 10.2SECURITY IMPROVEMENTS SECURITY IMPROVEMENTS
“Secure by Default” ed25519
User table constraints
OpenSSL 1.1 & LibreSSL
AWS Key Management
Single Root Require Secure Transport
![Page 22: MySQL vs MariaDB - Amazon S3€¦ · InnoDB InnoDB MyISAM DDL Performance Smart Doublewrite Dynamic Buffer Pool More Transportable InnoDB as Default Spatial Indexes Improved Crash](https://reader034.vdocuments.us/reader034/viewer/2022042613/5f98a440cceaa613500e4c1e/html5/thumbnails/22.jpg)
#cPConf
MySQL 5.7 MARIADB 10.2ADDED SYNTAX/QUERIES ADDED SYNTAX/QUERIES
EXPLAIN
No Temp Tables on UNION ALL
MySQL [(none)]> explain for connection 1;+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+| 1 | SIMPLE | orders | ALL | NULL | NULL | NULL | NULL | 922880 | Using where |+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
![Page 23: MySQL vs MariaDB - Amazon S3€¦ · InnoDB InnoDB MyISAM DDL Performance Smart Doublewrite Dynamic Buffer Pool More Transportable InnoDB as Default Spatial Indexes Improved Crash](https://reader034.vdocuments.us/reader034/viewer/2022042613/5f98a440cceaa613500e4c1e/html5/thumbnails/23.jpg)
#cPConf
SHOW CREATE USER foo4@test\G *************************** 1. row *************************** CREATE USER 'foo4'@'test' REQUIRE ISSUER 'foo_issuer' SUBJECT 'foo_subject' CIPHER 'text'
MySQL 5.7 MARIADB 10.2ADDED SYNTAX/QUERIES ADDED SYNTAX/QUERIES
EXPLAIN
No Temp Tables on UNION ALL
SHOW CREATE USER
CHECK CONSTRAINT
FOLLOWS/PRECEDES
![Page 24: MySQL vs MariaDB - Amazon S3€¦ · InnoDB InnoDB MyISAM DDL Performance Smart Doublewrite Dynamic Buffer Pool More Transportable InnoDB as Default Spatial Indexes Improved Crash](https://reader034.vdocuments.us/reader034/viewer/2022042613/5f98a440cceaa613500e4c1e/html5/thumbnails/24.jpg)
#cPConf
SO WHAT’S THE PUBLIC DOING?
![Page 25: MySQL vs MariaDB - Amazon S3€¦ · InnoDB InnoDB MyISAM DDL Performance Smart Doublewrite Dynamic Buffer Pool More Transportable InnoDB as Default Spatial Indexes Improved Crash](https://reader034.vdocuments.us/reader034/viewer/2022042613/5f98a440cceaa613500e4c1e/html5/thumbnails/25.jpg)
#cPConf
![Page 26: MySQL vs MariaDB - Amazon S3€¦ · InnoDB InnoDB MyISAM DDL Performance Smart Doublewrite Dynamic Buffer Pool More Transportable InnoDB as Default Spatial Indexes Improved Crash](https://reader034.vdocuments.us/reader034/viewer/2022042613/5f98a440cceaa613500e4c1e/html5/thumbnails/26.jpg)
#cPConf
![Page 27: MySQL vs MariaDB - Amazon S3€¦ · InnoDB InnoDB MyISAM DDL Performance Smart Doublewrite Dynamic Buffer Pool More Transportable InnoDB as Default Spatial Indexes Improved Crash](https://reader034.vdocuments.us/reader034/viewer/2022042613/5f98a440cceaa613500e4c1e/html5/thumbnails/27.jpg)
#cPConf
DROPPING IN?
![Page 28: MySQL vs MariaDB - Amazon S3€¦ · InnoDB InnoDB MyISAM DDL Performance Smart Doublewrite Dynamic Buffer Pool More Transportable InnoDB as Default Spatial Indexes Improved Crash](https://reader034.vdocuments.us/reader034/viewer/2022042613/5f98a440cceaa613500e4c1e/html5/thumbnails/28.jpg)
#cPConf
THE RISK OF MOVING TO MARIADB SERVER IF YOU AREN’T USING NEWER MYSQL FEATURES MAY BE MINIMAL, BUT THE RISK OF MOVING OUT OF MARIADB SERVER TO MYSQL IS VERY PREVALENT.
https://www.percona.com/blog/2017/11/02/mysql-vs-mariadb-reality-check/Percona
![Page 29: MySQL vs MariaDB - Amazon S3€¦ · InnoDB InnoDB MyISAM DDL Performance Smart Doublewrite Dynamic Buffer Pool More Transportable InnoDB as Default Spatial Indexes Improved Crash](https://reader034.vdocuments.us/reader034/viewer/2022042613/5f98a440cceaa613500e4c1e/html5/thumbnails/29.jpg)
#cPConf
It depends…
5.5 5.6 5.7 8.0
5.5 10.0 10.1 10.2 10.3 10.4?
MYSQL MARIADB
![Page 30: MySQL vs MariaDB - Amazon S3€¦ · InnoDB InnoDB MyISAM DDL Performance Smart Doublewrite Dynamic Buffer Pool More Transportable InnoDB as Default Spatial Indexes Improved Crash](https://reader034.vdocuments.us/reader034/viewer/2022042613/5f98a440cceaa613500e4c1e/html5/thumbnails/30.jpg)
#cPConf
5.7 -> 10.1 Incompatibilities
• MariaDB 10.1 does not support MySQL 5.7's online undo tablespace truncation. However, this feature was added to MariaDB 10.2.
• MariaDB 10.1 does not support MySQL 5.7's transportable tablespaces for partitioned InnoDB tables. ALTER TABLE ... {DISCARD|IMPORT} PARTITION is not supported.
• MariaDB 10.1 does not support the use of multiple triggers of the same type for a table. This feature was introduced in MariaDB 10.2.2.
![Page 31: MySQL vs MariaDB - Amazon S3€¦ · InnoDB InnoDB MyISAM DDL Performance Smart Doublewrite Dynamic Buffer Pool More Transportable InnoDB as Default Spatial Indexes Improved Crash](https://reader034.vdocuments.us/reader034/viewer/2022042613/5f98a440cceaa613500e4c1e/html5/thumbnails/31.jpg)
#cPConf
5.7 -> 10.2 Incompatibilities
• MariaDB 10.2 does not support MySQL's SELECT MAX_STATEMENT_TIME = N ... syntax
• MariaDB 10.2 does not support MySQL 5.7's “native” InnoDB partitioning handler.
• MariaDB 10.2 does not support the MySQL X plugin.• MariaDB 10.2 does not support the MySQL Memcached plugin.• Users created with MySQL's SHA256 password algorithm cannot
be used in MariaDB 10.2.• MariaDB's GTID is not compatible with MySQL's.
![Page 32: MySQL vs MariaDB - Amazon S3€¦ · InnoDB InnoDB MyISAM DDL Performance Smart Doublewrite Dynamic Buffer Pool More Transportable InnoDB as Default Spatial Indexes Improved Crash](https://reader034.vdocuments.us/reader034/viewer/2022042613/5f98a440cceaa613500e4c1e/html5/thumbnails/32.jpg)
#cPConf
mysql> SHOW STATUS WHERE Variable_name='InnoDB_buffer_pool_resize_status';+----------------------------------+----------------------------------+| Variable_name | Value |+----------------------------------+----------------------------------+| Innodb_buffer_pool_resize_status | Resizing also other hash tables. |+----------------------------------+----------------------------------+
![Page 33: MySQL vs MariaDB - Amazon S3€¦ · InnoDB InnoDB MyISAM DDL Performance Smart Doublewrite Dynamic Buffer Pool More Transportable InnoDB as Default Spatial Indexes Improved Crash](https://reader034.vdocuments.us/reader034/viewer/2022042613/5f98a440cceaa613500e4c1e/html5/thumbnails/33.jpg)
What’d you think?events.bizzabo.com/polls/011721
Rate this presentation on the Conference app or by navigating to the above link in your browser.
![Page 34: MySQL vs MariaDB - Amazon S3€¦ · InnoDB InnoDB MyISAM DDL Performance Smart Doublewrite Dynamic Buffer Pool More Transportable InnoDB as Default Spatial Indexes Improved Crash](https://reader034.vdocuments.us/reader034/viewer/2022042613/5f98a440cceaa613500e4c1e/html5/thumbnails/34.jpg)
#cPConfOr, without the app: events.bizzabo.com/polls/011721