MariaDB 10.3

1 .MariaDB 10.3 Colin Charles, Chief Evangelist, Percona Inc. colin.charles@percona.com / byte@bytebot.net http://bytebot.net/blog/ | @bytebot on Twitter Percona Webinar 26 June 2018 

2 .whoami • Chief Evangelist, Percona Inc • Focusing on the MySQL ecosystem (MySQL, Percona Server, MariaDB Server), as well as the MongoDB ecosystem (Percona Server for MongoDB) + 100% open source tools from Percona like Percona Monitoring & Management, Percona xtrabackup, Percona Toolkit, etc. Now supporting PostgreSQL too! “To champion unbiased open source database solutions” • Founding team of MariaDB Server (2009-2016) [Monty Program Ab, merged with SkySQL Ab, now MariaDB Corporation] • Formerly MySQL AB (exit: Sun Microsystems) • Past lives include The Fedora Project (FESCO), OpenOffice.org • MySQL Community Contributor of the Year Award winner 2014 

3 .License • Creative Commons BY-NC-SA 4.0 • https://creativecommons.org/licenses/by-nc-sa/4.0/legalcode 
 

4 .What is MariaDB Server? • https://mariadb.org/about/ 

5 .

6 .What is MariaDB TX 3.0? • A combination of all the offerings MariaDB Corporation & MariaDB Foundation work on • MariaDB Foundation: MariaDB Server (GPLv2), MariaDB Connectors for C/ Java/ODBC (LGPL) • MariaDB Corporation: services (remote DBA, migration, consulting, technical support) + MariaDB MaxScale proxy (Business Source License) + MariaDB Backup (fork of Percona XtraBackup), MariaDB Admin (SQLYog) + Monitor (Monyog) + Notifications (security alerts through a portal) • TX Cluster includes support for Galera Cluster; AX for MariaDB ColumnStore 

7 .Key focus points for MariaDB 10.3 • Oracle compatibility • More storage engines • Temporal data (system versioned tables) • Plus some of the features from 10.2+10.1+10.0+5.5+5.3+5.2+5.1 that may not be in stock MySQL 

8 .Release dates (and a basis for comparison) MySQL MariaDB 10.1: 17 October 2015 5.7: 21 October 2015 10.2: 23 May 2017 8.0: 19 April 2018 10.3: 25 May 2018 

9 .Let’s talk storage engines • Storage: how the data is stored on disk • Data types: Data types may be • Or in NDB (memory+disk), CassandraSE converted, MEMORY doesn’t support (access a Cassandra Cluster), SphinxSE TEXT, etc. (access the Sphinx daemon) • Caching: InnoDB caches data & indexes, • Indexes: improves search operations MyISAM caches indexes only (relying on OS disk cache for data) • Memory usage: improves data access for speed • Full-text search capability: MyISAM has this, InnoDB 5.6 got this • Transactions: protects the integrity of your data (Atomic-Consistent-Isolated- • GIS: MyISAM & Aria work (R-tree indexes Durable - ACID) exist), InnoDB 5.7 has this • Locking level: MyISAM (table locks), • Backups InnoDB (row locks), old BDB (page locks) • Foreign Keys 

10 .MariaDB storage engine offerings • MyRocks: for write-intensive • OQGRAPH: leaves algorithm workloads • note: requires libJudy • SPIDER: for scalability and • PARTITION: updates to make sharding SPIDER work better • InnoDB: default for read/write • Cassandra: still around, requires operations (no longer Percona libthrift XtraDB since MariaDB 10.2) • CONNECT: for ETL operations • ColumnStore: analytical purposes (not included in MariaDB Server • TokuDB: requires jemalloc and 10.3 — still a separate download) transparent hugepages to be never (not always) 

11 .MariaDB storage engine aims • To be a general purpose database, with purpose-built storage engines • Great focus on use cases • Maximise strengths, minimise weaknesses • InnoDB: good general purpose, balance B+-tree based engine • MyRocks: LSM-based, fast writes, great compression • SPIDER: sharding purposes 

12 .MyRocks • RocksDB (Facebook) is a fork of LevelDB (Google). MyRocks is the interface to it from MySQL/MariaDB • Write optimised • Focus on the endurance of flash devices to gain better lifetime (10x less write amplification) • Better compression than InnoDB (at least 2x) • Ability to load data fast, avoiding compaction overheads • Read-free replication (no random reads for updating secondary keys, only for unique indexes; RFR does away with it all, with row-based binlog) • Recommended read: https://mariadb.com/kb/en/library/differences-between- myrocks-variants/ 

13 .SPIDER • Transparent sharding and re-sharding via SQL • Partition by range/key/hash/list • vertical partitioning engine, allows partition by columns • Condition pushdown to the storage engine layer • JOIN, GROUP BY done internally (on the data nodes/shards) • direct updates/deletes (pushdown to data nodes) • direct aggregates (sums, min, max, avg through partition engine) • Partition improvements: full-text support, multi-range read (MRR) • Read the docs, please! https://mariadb.com/kb/en/library/spider-storage- engine-overview/ 

14 .

15 .So the benefit of storage engines… • mixing and matching • sure, there are limitations… • e.g. Galera Cluster only works with the InnoDB backend • Feel free to mix InnoDB and MyRocks. Or get data from CONNECT and load it into InnoDB. And so on… 

16 .Compression • Row compression (ROW_FORMAT=COMPRESSED), to page compression (PAGE_COMPRESSED=1), now to column compression • Bonus? Storage engine independent CREATE TABLE `users` ( `id` int(11) NOT NULL, `name` varchar(100) DEFAULT NULL, `blurb` text /*!100301 COMPRESSED*/ DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=ROCKSDB DEFAULT CHARSET=latin1 

17 .Compression show status like 'column_%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | Column_compressions | 1 | | Column_decompressions | 0 | +-----------------------+-------+ 

18 .Invisible columns CREATE TABLE `user` ( select * from user; `id` int(11) NOT NULL, +----+-------+ `name` varchar(100) DEFAULT NULL, | id | name | `secrets` varchar(10) INVISIBLE DEFAULT +----+-------+ NULL, | 1 | colin | PRIMARY KEY (`id`) +——+-------+ ) ENGINE=InnoDB DEFAULT CHARSET=latin1 select id,name,secret from user; insert into user (id,name,secret) values +----+-------+--------+ ("1","colin","yes"); | id | name | secret | +----+-------+--------+ | 1 | colin | yes | +----+-------+--------+ 

19 .INSTANT ADD COLUMN • contributed by Tencent to both MariaDB and MySQL • https://mysqlserverteam.com/mysql-8-0-innodb-now-supports- instant-add-column/ + https://mariadb.com/kb/en/instant-add- column-for-innodb/ • inserts a hidden row in the table, updates the data dictionary, only works with the last column 

20 .System versioned tables • SQL 2011 standard. Stores history of all changes. • Can alter a table to enable/disable/remove system versioned data • Queries? • AS OF to select data as of a point in time • BETWEEN .. AND to select data between two points in time • Partition data BY SYSTEM_TIME • Just ALTER .. ADD SYSTEM VERSIONING or create a table WITH SYSTEM VERSIONING 

21 .System versioned tables create table employees (name varchar(10), salary int, +-------+--------+------------ department varchar(10)) with system versioning; +---------------------------- +----------------------------+ insert into employees values ("colin", 1000, “mktg"); | name | salary | department | ROW_START | ROW_END | +-------+--------+------------ update employees set salary=10000 where name=“colin"; +---------------------------- +----------------------------+ update employees set department="eng" where | colin | 10000 | eng | 2018-06-26 name=“colin" 13:00:53.772241 | 2038-01-19 03:14:07.999999 | | colin | 1000 | mktg | 2018-06-26 select * from employees where name="colin"; 13:00:03.656662 | 2018-06-26 13:00:24.251594 | +-------+--------+------+ | colin | 10000 | mktg | 2018-06-26 13:00:24.251594 | 2018-06-26 13:00:53.772241 | | name | salary | dept | +-------+--------+------------ +-------+--------+------+ +---------------------------- | colin | 10000 | eng | +----------------------------+ +———+--------+------+ select *, ROW_START, ROW_END from employees for SYSTEM_TIME ALL; 

22 .AS OF example SELECT * FROM employees FOR SYSTEM_TIME AS OF TIMESTAMP'2018-06-26 13:00:24'; +-------+--------+------------+ | name | salary | department | +-------+--------+------------+ | colin | 1000 | mktg | +-------+--------+------------+ 

23 .Oracle compatibility - Sequences • Sequences to create a sequence of numeric values • Not to be confused with replacing AUTO_INCREMENT, is an alternative to creating unique identifiers • With a sequence, you can compute the last number created by all existing sequences, whereas AUTO_INCREMENT can only compute its own last number created 

24 .Sequences create sequence seq start | 20 | +--------------+ with 10 increment by 10; +--------------+ | 30 | select nextval(seq); select nextval(seq); +--------------+ +--------------+ +--------------+ select previous value for | nextval(seq) | | nextval(seq) | seq; +--------------+ +--------------+ +------------------------+ | 10 | | 30 | | previous value for seq | +--------------+ +--------------+ +------------------------+ select nextval(seq); select lastval(seq); | 30 | +--------------+ +--------------+ +------------------------+ | nextval(seq) | | lastval(seq) | +--------------+ 

25 .Oracle PL/SQL • PL/SQL compatibility parser added for easier migration from Oracle to MariaDB • sql_mode=‘oracle’ • Data types (have synonyms in MariaDB): VARCHAR2 (VARCHAR), NUMBER (DECIMAL), DATE (DATETIME), RAW (VARBINARY), BLOB (LONGBLOB), CLOB (LONGTEXT) • CURRVAL, NEXTVAL • EXECUTE IMMEDIATE • Existing stored procedures, triggers • ROW datatype for stored routines • Cursors with parameters • Packages • https://mariadb.com/kb/en/library/sql_modeoracle-from-mariadb-103/ 

26 .Proxy support • Client can connect to MariaDB 10.3 via a proxy without the need to define user privileges based on the host of the proxy • Proxy protocol allows the proxy to provide the client IP to the server • With audit plugin, logged client IP is now real client IP not proxy IP 

27 .Let’s not forget the goodness of MariaDB 10.2, 10.1 • Window Functions • DECIMAL increased to 38 digits • Recursive Common Table • EXECUTE IMMEDIATE Expressions • New user management functions • JSON, GeoJSON functions (SHOW CREATE USER) • Time delayed replication • Information schema supports • Restrict speed of reading binlog user defined variables from master • Binary log based Flashback for • Compressed binlog DML statements • CHECK CONSTRAINT • Indexes for virtual columns 

28 .Other bits • INTERSECT and EXCEPT to UNION • Stored aggregate functions - functions that are computed over a sequence of rows and return one result for the sequence of rows • Idle transaction timeouts • idle_transaction_timeout, idle_readonly_transaction_timeout, idle_write_transaction_timeout 

29 .Some gotchas if you’re in MySQL 8 land • JSON is not stored as a binary data type • No optimiser hints, optimiser trace • GTIDs are different in MariaDB (e.g. no • Threadpool in MariaDB! GTID in OK packet) • PAM/GSSAPI/SSPI authentication • No X Protocol, mysqlsh support • AWS Key Management Plugin • No group replication • Table elimination! • PERFORMANCE_SCHEMA from MySQL • User statistics 5.6 • Dynamic columns • No caching_sha256_password (ed25519) • No SET PERSIST • mysql.user.password now is mysql.user.authentication_string • InnoDB comes from MySQL 5.7 • No password expiry, last changed, etc. however there is cracklib_password_check