MariaDB 10.3 vs MySQL 8.0 Tyler Duzan, Product Manager Percona Who - - PowerPoint PPT Presentation

MariaDB 10.3 vs MySQL 8.0

Tyler Duzan, Product Manager Percona

2

Who Am I?

• My name is Tyler Duzan
• Formerly an operations engineer for

more than 12 years focused on security and automation

• Now a Product Manager at Percona
• Have used Puppet, Chef, Ansible,

Saltstack, and Terraform professionally in the past

3

There is no “best” database

• Percona is the unbiased champion of open source databases
• We provide services around both MariaDB and MySQL
• Both databases have use cases where they are the right choice
• Purpose of this talk is to help you understand the differences so you can

make the right choice for your application

4

MySQL / MariaDB History

• MariaDB first release in 2009 based on MySQL 5.1, following Oracle

acquisition of Sun (who previously acquired MySQL AB)

• Through MariaDB 5.5, MariaDB versions were based on and maintained

compatibility with MySQL versions

• Starting with MariaDB 10.0, released in 2015 new feature development

and changes created the beginnings of significant divergence

• Newest GA major versions are:
• MySQL 8.0(.11), released April 19th, 2018
• MariaDB 10.3(.7), release May 25th, 2018

5

Disclaimer

• My presentation is not an attempt to be exhaustive, I’m discussing only

the things I found specifically interesting

• Some features were present in MariaDB first but are now available in

MySQL, so while they are new in MySQL 8.0 and important they were really relevant for this comparison. Examples:

• Windows Functions
• Common Table Expressions
• I’m primarily focusing on comparing the community releases of both

servers, which means some features may not be mentioned as they’re Enterprise only in one and not present in the other.

Feature Incompatibilities and Differences

7

GTID / Replication

• ”The big one”
• GTIDs introduced in MariaDB Server 10.0.2, introduced in MySQL 5.6.5,

but the implementations differ and are incompatible

• MariaDB Server maintains partial compatibility with conversion for MySQL

GTIDs allowing you to replicate from a MySQL Master to a MariaDB Slave, but you cannot use a MySQL Slave with a MariaDB Master

• In MySQL a GTID is Server UUID + Sequence Number
• In MariaDB a GTID Domain ID + Server ID + Sequence Number

8

Encryption / Key Management

• Both MariaDB 10.3 and MySQL 8.0 offer data-at-rest encryption features,

however these implementations differ and are not compatible with one another.

• MariaDB 10.3 provides support for encrypting binary logs, temporary

tables, and encrypting Aria data, which is absent in MySQL 8.0

• While MySQL 8.0 does offer enterprise versions of these features,

MariaDB Server 10.3 provides key management with AWS KMS and support for encrypted file-based keyrings

9

Authentication

• Starting with MySQL 8.0, the new authentication method is based on

SHA2 passwords

• Old MySQL clients, and the current implementation in MariaDB server are

based on the prior implementation of MySQL authentication which uses

10

InnoDB Changes

• “Because the InnoDB implementation in MariaDB has diverged from

MySQL, it is not meaningful to report a MySQL version number for InnoDB any more.” https://jira.mariadb.org/browse/MDEV-16172

• Many features of MySQL InnoDB exist in MariaDB, but often MariaDB

implemented them first and the MySQL implementation differs

• Some features of MySQL InnoDB were chosen not to be merged into
• MariaDB. Most 5.7 features are merged, 8.0 are not.

MariaDB Features Not in MySQL

12

Enterprise Features

• There are several features that are present in MariaDB Server 10.3 which

are only available in enterprise editions of MySQL

• Threadpool
• Audit Logging
• PAM-based Authentication
• AWS KMS Keyring Management

13

Invisible Columns

• Introduced in MariaDB Server 10.3
• This feature allows you specify columns in your schema which do not

show up in SELECT * statements and are not required to have a value present in an INSERT statement

• Adds the INVISIBLE attribute available in DDL statements
• When specifically referenced in a SELECT, will still behave normally
• Useful for making schema changes without breaking legacy applications,

as an example

• Enables the feature of System Versioned Tables also in 10.3

14

System Versioned Tables

• Introduced in MariaDB Server 10.3
• Based on a SQL:2011 standard implementation
• System Versioned Tables store the history of all changes, not only the

current set of data.

• This enables auditing or analysis from any point in time, which can be

useful for forensic data analysis, meeting compliance requirements, or performing cherry-picked point in time recovery

• Extends CREATE TABLE and ALTER TABLE syntax to allow the addition
• f versioning
• Internally implemented on top of Invisible Columns
• Additionally supports transaction-precise history in InnoDB

15

Oracle Compatibility

• Introduced in MariaDB Server 10.3
• Builds on top of many features in MariaDB Server which correspond to SQL

standards, such as Common Table Expressions, Window Functions, and others

• Implements a subset of PL/SQL which can be enabled using

SQL_MODE=ORACLE

• Provides support for native support for parsing Oracle Stored Procedures

directly into MariaDB

• Materialized Views not yet natively supported, but can be adapted on top of the

PL/SQL support with tools like Flexviews

• Additionally supports Oracle style packages (CREATE PACKAGE, CREATE

PACKAGE BODY, SHOW CREATE PACKAGE)

16

Storage-Engine Independent Column Compression

• Introduced in MariaDB 10.3(.2)
• Provides a new column attribute COMPRESSED
• Supports TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB, TINYTEXT,

TEXT, MEDIUMTEXT, LONGTEXT, VARCHAR and VARBINARY column types

• Currently only supports zlib
• Compressed columns cannot be indexed

17

Flashback

• Available since MariaDB 10.2.4
• Contributed by Alibaba
• Supports all storage engines within MariaDB via full image binary logs

(row logging)

• Only supports DML statements (INSERT, UPDATE, DELETE) currently
• Allows instances, databases, or tables to be rolled back to previous

snapshot

• Provides binary-log based point in time recovery mechanism
• When using this feature, converted binary log events are stored in

memory prior to execution, so may require large amounts of RAM.

18

Additional Storage Engines

• MyRocks GA as of 10.3.7
• SpiderSE GA as of 10.3.7
• TokuDB GA as of 10.0.10

19

Semi-Synchronous Replication

• Previously was a plugin, introduced in MariaDB Server 10.1(.13)
• Plugin is also available in MySQL since 5.7 and can be installed with the

appropriate INSTALL PLUGIN statement.

• Merged into the server directly in MariaDB 10.3, which eliminates the

need to install and eliminates plugin locks.

• Improvements and server merge contributed by Alibaba in MDEV-13073
• In semi-sync replication a slave does not send acknowledgement until

after an event has been written out to the relay log and then flushed. This combines with a timeout to cause synchronous replication to fail to asynchronous replication until slave lag is eliminated at which point semi- sync resumes.

MySQL Features Not in MariaDB

21

Transactional Data Dictionary

• Introduced in MySQL 8.0
• Data dictionary is a collection of metadata about the contents of your

database

• Prior to 8.0 the data dictionary was stored in a mixture of files and tables,

many of which were in MyISAM (non-transactional)

• The data dictionary from 8.0 onward is stored in a series of tables

(mysql.*) using InnoDB

• Conveys numerous benefits, but does make upgrading to MySQL 8.0 a
• ne way change.
• InnoDB in 5.7 had internal data dictionary (SYS_*) which was dropped as

part of this transition

22

Transactional Data Dictionary

• Improves performance for INFORMATION_SCHEMA queries (typical 30x

performance improvement over MySQL 5.7)

• Improves stability my eliminating inconsistencies that were previously

possible in the data dictionary

• Enables the existence of atomic DDL operations
• Makes DDLs and the data dictionary generally crash-safe
• Simplifies replication of DDLs
• Enables a single uniform API to access data dictionary information
• Provides a simple way to generate serialized dictionary information as

JSON to enable simplified data migration processes

23

JSON Data Types, Operators, and Functions

• Starting in MySQL 5.7, there is a JSON data_type and operators
• In MySQL 8.0, there are many new JSON functions added
• MariaDB implemented an alias for the JSON data_type which maps to

LONGTEXT allowing import via mysqldump

• MariaDB does provide some JSON functions for working with JSON data,

but fundamentally treats this data as normal strings

• MySQL 8.0 adds capabilities to MySQL to use it as a document store so

had significantly advanced JSON support comparatively.

• MySQL 8.0 added improvements internally to how JSON is interacted with

internally allowing partial updates to JSON objects

24

MySQL X Protocol / MySQL Shell

• Introduced in MySQL 5.7, Enhanced in MySQL 8.0
• MySQL Shell provides a command line client for MySQL that also has

advanced Javascript and Python based scripting functionality

• Combines with the X API to provide support for utilizing MySQL as a

document store using its native JSON data types and functions

• Adds in AdminAPI functions provided directly in the shell to deploy and

manage InnoDB Cluster/Group Replication installations

• Provides upgrade advising for moving from 5.7 to 8.0
• Autocompletion in the shell

25

Sys Schema

• Introduced in MySQL 5.7
• New set of objects and functions which help DBAs utilize Performance

Schema to perform diagnostics more easily

• Provides new summary views of performance schema data
• Stored procedures and stored functions for interacting with performance

schema

• Enabled by default in new installations if --initialize is called, is created

and populated when mysql_upgrade is performed

26

CREATE TABLESPACE

• Syntax provided for InnoDB in MySQL 5.7
• Allows the creation of InnoDB general table spaces
• Like file-per-table table spaces, general table spaces allow you to gain

advantages in storage and data management by allowing you to store tablespace data independently from the MySQL data directory

• General table spaces can confer a memory efficiency advantage over file-

per-table table spaces because a general tables space can contain multiple tables. Multiple tables in fewer general table spaces means less table space metadata must be stored in memory

• Has no dependence on InnoDB file format settings and supports all file

and row formats

27

Geographic Functions

• Starting with MySQL 8.0 there are many new or enhanced geographic functions

which can make use of spatial data types. In addition, MySQL 8.0 added support for spatial indexes which previously existed in MariaDB

• Examples of functions available in MySQL 8.0 which are not present in MariaDB

10.3:

• ST_DISTANCE_SPHERE
• ST_GeoHash
• ST_IsValid
• ST_LatFromGeoHash
• ST_LongFromGeoHash
• ST_PointFromGeoHash
• ST_SIMPLIFY
• ST_VALIDATE

28

Native Partitioning

• Introduced in MySQL 5.7, forced in MySQL 8.0
• Confers same advantages as partitioning typically does, reducing size of

indexes for a particular data set aligned by partition keys

• Moved implementation of partitioning out of the server and into the

storage engine, which reduces server code complexity

• Currently only supported for InnoDB and NDB, removing partitioning

support for MyISAM altogether in 8.0

• Will potentially enable more complex feature sets in storage engines on

top of partitioning. Currently InnoDB still does not support foreign keys in partitioned tables, but could do so now in theory

29

Resource Groups

• Introduced in MySQL 8.0
• Advanced optimization feature
• Allows a DBA to associate virtual CPUs with resource groups and then assign

particular MySQL threads to those resource groups

• In practice, this means per connection, since absent connection pooling or

multiplexing, each connection is represented by its own thread

• Rather than assigning threads to resource groups after the fact, which could be

potentially dangerous, you can specify them in the query

• SELECT /*+ RESOURCE_GROUP(NAME OF THE RG) */ id, millid,

date,active,kwatts_s FROM sbtest29 WHERE id=44

• More reading: https://www.percona.com/blog/2018/08/27/mysql-8-load-fine-

tuning-with-resource-groups/

30

SET PERSIST

• Introduced in MySQL 8.0
• Over time, database server hardware resources and therefore cache sizes have
• increased. As they have gotten larger, server restarts have become much more

expensive.

• Historically it would require you to edit your configuration files then restart the

MySQL server in order for configuration changes to be persisted and be in effect

• With SET PERSIST, any GLOBAL DYNAMIC value can be set via a SQL

statement and will be written out to mysqld-auto.cnf so that it will persist through restarts

• As with generally setting a value, it immediately takes effect
• Allows persistent configuration changes without requiring shell access (DBaaS)

Conclusions

32

When You Should Use MariaDB Server 10.3?

• Consider MariaDB if you’re interested in getting supported versions of

community features more quickly (examples: Flashback, Instant ADD COLUMN, Semi-sync replication)

• Consider MariaDB if you’re planning a migration from an Oracle

database, which may be eased by the Oracle compatibility features

• Consider MariaDB if you’re deploying on AWS and want to use encryption

functionality for compliance

• Consider MariaDB if you want the included enterprise features at no cost

33

When You Should Use MySQL 8.0?

• Consider MySQL if maintaining the broadest replication compatibility with
• ther services is important (such as replication compatibility with MySQL

based DBaaS)

• Consider MySQL if you are primarily working JSON data or need to

combine traditional relational use cases with document store use cases

• Consider MySQL for increased stability thanks to the transactional data

dictionary

• Consider MySQL if you are going to be working with GIS/SRS data
• Consider MySQL if you are interested in using Group Replication

34

Percona Server for MySQL 8.0

• Provides all of the MySQL 8.0 features from Community Edition
• Adds in Enterprise features
• Threadpool
• Audit Logging
• PAM-based authentication
• Has enhanced encryption capabilities with Hashicorp Vault integration
• Provides TokuDB and MyRocks as additional storage engines
• Supports Native Partitioning in TokuDB and MyRocks

Questions?

36

Thank You Sponsors!!

37

Rate My Session