MariaDB Amsterdam Roadshow: 19 September, 2024

Presentation from MariaDB's Amsterdam roadshow on September 19, 2024.

1. 19 September
MariaDB Roadshow 2024

Amsterdam

2. Agenda

09:30 - 09:50 Welcome and introduction
09:50 - 10:30 Product Updates: MariaDB Enterprise Server and MaxScale
10:30 - 11:00 Kubernetes and the MariaDB Kubernetes Operator
11:00 - 11:30 Coffee Break
11:30 - 12:00 Customer flatexDEGIRO: How and why we migrated from MySQL to a cluster solution with MariaDB
12:00 - 12:45 Building a Highly Available Database Architecture
12:45 - 13:00 Q&A
13:00 - 14:00 Lunch
14:00 End of the event

3. Speakers

4. MariaDB plc
mariadb.com
Amdocs
CCV
Deutsche Bank
Development Bank of Singapore
Nokia
Samsung
SelectQuote
ServiceNow
Virgin Media O2
Market Leadership
75%
Of Fortune 500 companies use
MariaDB

1B+
Docker Hub downloads

2.5B+
Reach via Linux distros

200K+
Open source contributions

200+ Employees600+ Customers
Around the World
Proven leadership team

World class relational database
engineering team, including the original
core MySQL team

Dual headquartered
●Europe: Dublin, Ireland
●USA: Silicon Valley, California

5. MariaDB Enterprise:
An Introduction

6. MariaDB Enterprise
Enterprise Server Support & ServicesEnterprise Availability

7. Support
30-minute S1 SLA 24x7 Technical Support Remote DBAs

8. Enterprise Features
Firewall & Data Masking Non-blocking BackupEnd to end Encryption Improved Auditing

9. MaxScale
Database Protection Database Ease of UseIntelligent Load Balancing

10. Services Offerings
Enterprise
Architecture
Consulting Training

11. MariaDB Enterprise:
Product Update

12. MariaDB Enterprise Server

13. MARIADB ENTERPRISE SERVER
MariaDB
Community Server
MariaDB
Cluster
MariaDB
Audit
MariaDB
Enterprise Server
MariaDB
Enterprise
Cluster
MariaDB
Enterprise
Audit
Community testing
Extensive QA
Production-ready defaults
Enterprise plugins & tools
Non-GA plugins disabled
●Launched in 2019
●Geared towards enterprise-grade customers
●Joint effort with key customers
●Feature backports

14. RELEASE MODEL
Geared towards non-mission critical
workloads that favor rapid innovation
and development.
●New release series with new
features every quarter


MariaDB Community Server
Geared towards mission critical
workloads that favor stability and
robustness.
●New release series approx. every
two years
●Long maintenance cycle
●New features backported from
community server


MariaDB Enterprise Server

15. MARIADB ENTERPRISE SERVER TIMELINES
Version Stable (GA) Date End of Standard Support

End Of Life Date
10.4 02 July 2019 02 July 2022 02 July 2024
10.5 16 July 2020 16 July 2024 16 July 2025
10.6 23 August 2021 23 August 2026 23 August 2029
11.4 Fall 2024 Fall 2029 Fall 2032
… … … …
3 + 2 Years

4 + 1 Years


5 + 3 Years
11.4 Enterprise Server
10.4 Enterprise Server
10.5 Enterprise Server
10.3 Enterprise Server
Extended Support
Q1Q2Q3
2020
Q4Q1Q2Q3
2021
Q4Q1Q2Q3
2022
Q4Q1Q2Q3
2023
Q4Q1Q2Q3
2024
Q4Q1Q2Q3
2025
Q4Q1Q2Q3
2026
Q4Q1Q2Q3
2027
Q4Q1Q2Q3
2028
Q4Q1Q2Q3
2029
Q4
Extended Support
10.6 Enterprise Server Extended Support
Extended Support ->
Enterprise Server

16. MARIADB ENTERPRISE ONLY FEATURES
Feature ES 10.4ES 10.5ES 10.6
Enterprise Backup + ⎷ ⎷
Enterprise Audit + ⎷ ⎷
Index Limit Increased to 128 Indexes + ⎷ ⎷
Enterprise Cluster Data-at-Rest Encryption + ⎷ ⎷
Enterprise Cluster Non-blocking DDL operations + ⎷
Enterprise Cluster XA Support +
Enterprise Cluster TLS certificate expiration monitoring +
Enterprise Cluster SSL/TLS enabled by default +
Millisecond Precision for MariaDB Enterprise Audit logging +
< Backported
+ Added
⎷ Included

17. MARIADB ENTERPRISE ONLY BACKPORTS
Feature from ES 10.4ES 10.5ES 10.6
Hashicorp Encryption plugin ES 10.5 < + ⎷
Slow master shutdown default variable ES 10.5 < + ⎷
Spider ODBC wrapper ES 10.5 < + ⎷
Object filter for MariaDB Enterprise Audit ES 10.6 < < +
Time-invalidated cache for hashicorp plugin ES 10.6 < < +
Sybase SQL mode for extended aliases ES 10.6 < +
Performance schema tables for Enterprise Cluster ES 10.6 < < +
< Backported
+ Added
⎷ Included

18. MARIADB ENTERPRISE SERVER BACKPORTS
Feature from ES 10.4ES 10.5ES 10.6
S3 Storage Engine CS 10.5 < + ⎷
GTID support for Galera CS 10.5 < + ⎷
Crash recovery for semi-synchronous replication CS 10.6 < < +
mariadb-dump option --as-of for system versioned tables CS 10.7 < < <
New JSON functions JSON_EQUALS, JSON_NORMALIZE CS 10.7 < < <
New functions NATURAL_SORT_KEY(), SFORMAT() CS 10.7 < <
Password reuse prevention Plugin CS 10.7 < < <
CONVERT PARTITION / CONVERT TABLE CS 10.7 <
New UUID data type CS 10.7 <
< Backported
+ Added
⎷ Included

19. MARIADB ENTERPRISE SERVER BACKPORTS
Feature from ES 10.4ES 10.5ES 10.6
GTID Support for mariadb-binlog CS 10.8 < <
New JSON function JSON_OVERLAPS CS 10.9 < < <
Option for SQL thread to limit maximum execution time per query CS 10.10 < <
Allow innodb_undo_tablespaces to be changed after database creation CS 10.11 < <
New JSON function JSON_SCHEMA_VALID CS 11.1 < < <
Make optimizer handle UCASE(varchar_col)=... CS 11.3 <
Add timezone information to DATE_FORMAT CS 11.3 <
Easier way to retrieve all users that have privileges on a specific table CS 11.4 <
< Backported
+ Added
⎷ Included

20. MariaDB Enterprise Server
Next Release Series 11.4

21. MARIADB ENTERPRISE SERVER 11.4
●The next release series after MariaDB Enterprise 10.6
●Features added from our MariaDB Community Server release series 10.7 to
11.4
●Will get features backported to it after reaching GA
○Based on customer requests
○Only when new features have reached a high level of maturity

22. KEY NEW FEATURES
●New JSON functions
●New function KDF()
●General support of packages for stored routines
●Time zone for DATE_FORMAT
●Base 62 conversion for CONV()

Development
●Improved optimizer cost-model
●Semi-join optimizations for UPDATE/DELETE
●Improved InnoDB tablespace management
●Logical backup and restore for system versioning
●Security
○SSL Encryption by default
○New privilege SHOW CREATE ROUTINE
●Replication
○Global limitation of space used by binary logs
○New “SQL_BEFORE_GTIDS” and
“SQL_AFTER_GTIDS” for START REPLICA UNTIL
..”
Operations
●Storage engine agnostic Online Schema Change
●Partitioning Improvements
○Exchange partitions WITHOUT VALIDATION

Schema Maintenance

23. IMPROVED OPTIMIZER
●Improved cost-based model
●Costs calculated based on state of the art SSD disks
●Different characteristics of storage engines taken into account
●Fine-tuning possible by changing costs for different metrics

24. NEW FOR SCHEMA MANAGEMENT
●Partitioning Improvements
○Easier to manage
■ALTER TABLE ... CONVERT PARTITION .. TO TABLE
■ALTER TABLE ... CONVERT TABLE ... TO PARTITION
○Auto-create history partitions for system versioned tables
○Exchange partition or convert a table WITHOUT VALIDATION
●Build-In Online Schema Change
○Non-locking ALTER TABLE for all engines and operations

25. BUILT-IN ONLINE SCHEMA CHANGE
Traditional ALTER TABLE New ALTER ONLINE TABLE
Locked for DML
ALTER TABLE ALTER ONLINE TABLE
Create new structure Create new structure
Use new structure
Drop old structure
Drop old structure
Use new structure
Copy data
Copy data
Capture
changes
Create change buffer
Apply change buffer
Current CurrentAltered Altered

26. STREAMLINED INNODB TABLESPACE MANAGEMENT
●Symplified process to import a tablespace
○ALTER TABLE IMPORT TABLESPACE does not need a table to be created and a
tablespace to be dropped
●Improved control of log files and data files via new dynamic system variables
●InnoDB system tablespace can now be reclaimed via an auto shrink attribute

[mariadb]
...
innodb_data_file_path=ibdata1:12M;ibdata2:50M:autoextend: autoshrink
●InnoDB tablespaces for temporary tables can be shrinked without server restart

SET GLOBAL innodb_truncate_temporary_tablespace_now=1;

27. ZERO-CONFIGURATION ENCRYPTED CONNECTIONS
●SSL encrypted by default
○Refuse unencrypted connect requests
○No manual SSL/TLS configuration required
●Client can verify the server self-signed certificate without configuration
●MariaDB Server and clients use password to verify that the SSL certificate is
coming from the server
●MariaDB clients can verify the server certificate if it’s self-signed
●Connections done via a secure local transport are possible without SSL/TLS

28. CHANGES TO THE PRIVILEGE SYSTEM
●New Privilege SHOW CREATE ROUTINE
○No need of SELECT privilege for the mysql.procs table
○No need to be the definer of the Stored Procedure
●New view privileges_by_table_by_level in the SYS schema that lists the
privilege and privilege level per user, schema and table

SELECT * FROM sys.privileges_by_table_by_level WHERE GRANTEE NOT LIKE "'root'@'%'";
+--------------+------------+-------------+-----------+--------+
| TABLE_SCHEMA | TABLE_NAME | GRANTEE | PRIVILEGE | LEVEL |
+--------------+------------+-------------+-----------+--------+
| test | t1 | 'user1'@'%' | SELECT | GLOBAL |
| test | t1 | 'user1'@'%' | UPDATE | GLOBAL |
| test | t1 | 'user2'@'%' | SELECT | SCHEMA |
| test | t1 | 'user3'@'%' | SELECT | TABLE |
+--------------+------------+-------------+-----------+--------+

29. Expanded JSON Support


●JSON_SCHEMA_VALID
●JSON_KEY_VALUE
●JSON_ARRAY_INTERSECT
●JSON_OBJECT_TO_ARRAY
●JSON_OBJECT_FILTER_KEYS
●JSON_ARRAY_INTERSECT
●JSON_OBJECT_TO ARRAY
●JSON_FILTER_KEYS
●JSON_KEY_VALUE
JSON_ARRAY JSON_INSERT JSON_QUERY
JSON_ARRAYAGG JSON_KEYS JSON_QUOTE
JSON_ARRAY_APPEND JSON_LENGTH JSON_REMOVE
JSON_ARRAY_INSERT JSON_LOOSE JSONE_REPLACE
JSON_COMPACT JSON_MERGE JSON_SERCH
JSON_CONTAINS JSON_MERGE_PATCH JSON_SET
JSON_CONTAINS_PATH JSON_MERGE_PRESERVE JSON_TABLE
JSON_DEPTH JSON_NORMALIZE JSON_TYPE
JSON_DETAILED JSON_OBJECT JSON_UNQUOTE
JSON_EQUALS JSON_OBJECTAGG JSON_VALUE
JSON_EXISTS JSON_OVERLAPS ST_AsGeoJSON
JSON_EXTRACT JSON_PRETTY ST_GemFromGeoJSON
JSON Path
●negative index
●range notation

30. MariaDB MaxScale

31. Workload Capture and Replay
The introduction of the Workload Capture and Replay feature in MaxScale is a significant step forward for our enterprise
clients. This feature is specifically designed to address the growing need for tools necessary to adequately support
enterprise customers.

By enabling the capture and subsequent replay of workloads, companies can thoroughly test and validate system
performance under various scenarios. This is crucial for businesses looking to adopt new versions of our software, as it
directly contributes to enhancing user confidence and system reliability. The implementation of this feature is expected to
significantly increase the adoption of new versions and improve overall customer confidence in our systems.

32. MariaDB Enterprise:
High Availability

33. Zero Downtime…
… of the Data Layer!

34. But First…

35. MaxScale

36. High Availability
Minimising Database Service Downtime

37. Transaction Replay
Automatic Failover
Connection MigrationPrimary Election Session Restore

38. Architecture

39. What does good look like?

40. … and a good data layer?

41. … and a good data layer?

42. … and a good data layer?

43. … and a good data layer?

44. … and a good data layer?

45. … and a good data layer?

46. … and a good data layer?

48. Things I cannot help with

49. Other things that you need to consider:
●Availability of Zones / Regions / Data Centres
○And how your application and users are routed and connected
●Hybrid of On Prem and Cloud or a Mix of Cloud Providers
○Host your database over different providers
●If it’s your own Hardware, not putting everything on the same server
○Dual Power Supplies
○Dual Network Cards / Switches / Firewalls / Internet Connectivity
●Storage
●User Error
○Someone deleting data
○Trashing a server or its file system
●And many other things…

50. Enough of boring slides…

Lets see a DEMO !

51. A Coffee Break
… back in 30 minutes

52. MariaDB migration
How and why we migrated from MySQL
to a cluster solution with MariaDB!

53. Manuel Buchmann
Manager Team Lead - ITOP Database
Administration
[email protected]

54. Agenda
1) Key advantages of the new database
2) Scope of the project
3) Maxscale Loadbalancer Setup
4) Logical Component Overview PROD
5) Summary
6) Q&A

55. Key advantages of the new database




1)Improvement of DR capabilities + high availability
2)Improved replication and reduced replication lag
3)Fast recovery time from backup
4)Horizontal scaling
5)Less work to maintain env (30 servers migrated to 12 servers)
6)Security & maintainability (network segmentation & better patching)
7)Advanced monitoring & audit logging
8)Regulatory requirements

56. Scope of the project




1)Brand new architecture
2)Hardware renewal
3)Commercially supported DB version
4)Dedicated set of test environments
5)Testing for full scope of the apps
6)Clean up of legacy issues (primary key
issues, suboptimal queries, accounts
clean-up…)
7)Migration preparation & Dry Run
8)Migration execution

60. 60
OPTION 1 – stay with MySQL
Description
OPTION 2 – migrate to MariaDB and
change the architecture
Pros Cons
▪No changes in DB
▪MySQL 5.7 supported not longer than Oct
2023, migration to MySQL 8.0 (or other
DB) necessary to have support in the
future


▪Build a database infrastructure that is
reliable, scalable and maintainable
that considers data growth,
technology choices, upgrade paths &
availability
▪Change the architecture from master –
slaves to two clusters
▪Migrate from MySQL 5.7 to MariaDB 10.4
▪Focus on other topics (ITOP + tribes) spending no
additional effort on DB architecture improvement
▪Extended community support available for MySQL 8.0
till Apr 2026 but migration still needed
▪No support after Oct 2023 for MySQL 5.7 – migration
necessary anyway
▪no DR improvement is possible – existing DB failover
mechanism requires about 8 hours for switch-over
and re-enable replication to second DC
▪Performance of MySQL is worse than MariaDB
▪Risk of delay in replication that affect business
▪Additional effort on tribes’ side for migration to
MySQL 8.0 without significant performance or DR
improvement



▪Community support till June 2024 (MariaDB 10.4) will
be further extended with commercial support
▪Performance of MariaDB is far better than MySQL
▪Improvement of DR capabilities for database
▪Improved replication using cluster-based replication
and lower number of master/slave replication points
▪Improved availability via correct load balancing
▪Much faster recovery time from backup snapshot
▪Horizontal Scaling
▪No single point of failure on business-critical data



▪Significant effort needed on both sides – ITOPs and
Development Tribes (Development tribes to analyze
non-PK tables and adjust them accordingly, migrate
JSON to longtext, test applications & failover
scenarios before production launch)


Summar
y

61. Q&A

62. MariaDB Enterprise:
MariaDB Operator for
Kubernetes

63. Let’s just go and build a Kubernetes
Environment, with Terraform:


make init-demo
make plan-demo
make apply-demo

64. WHAT IS DOCKER
Docker is different from Kubernetes.

Docker is a set of platform-as-a-service products that use OS-level virtualisation to deliver
software in packages called containers. The service has both free and premium tiers. The
software that hosts the containers is called Docker Engine. It was first released in 2013 and
it was developed by Docker, Inc.

65. WHAT IS A CONTAINER
A container is a standard unit of software that packages up code and all its dependencies
so the application runs quickly and reliably from one computing environment to another. A
Docker container image is a lightweight, standalone, executable package of software that
includes everything needed to run an application: code, runtime, system tools, system
libraries and settings.

There are various providers of containers, Docker is the best known, but actually, there are
better alternatives that people are now starting to use. Kubernetes supports these
alternatives and is not dependent on Docker to function. Some examples include:
containerd, CRI-O and Mirantes Container Runtime.

66. WHAT IS A KUBERNETES OPERATOR
A Kubernetes Operator is a software extension that makes use of the Kubernetes custom
resources. Operators must follow the Kubernetes principles. The main aim of an operator is
to replicate the human operator and to automate the systems, for example how to deploy
and how to resolve problems.

People who run workloads on Kubernetes often like to use automation to take care of
repeatable tasks. The operator pattern captures how you can write code to automate a task
beyond what Kubernetes itself provides.

67. MARIADB OPERATOR AT A GLANCE

Fast build &
destroy
Reduced DBA
resource
Test & Dev on
lower spec
Lower
Cost
Helm Install
Manage
Complex
Environments
Declarative
YAML
Easier
Upgrades
User
Management
Resizing
Faster
Recovery
Backups
Version
Control
Repea
table

68. MARIADB OPERATOR AWARENESS

●RedHat UBI container for MaxScale
●MaxScale Read/Write Splitting Support
●MaxScale HA / LB Support
●MaxScale REST API Support
●MaxScale GUI Console Support

Firewall & Data Masking Non-blocking BackupEnd to end Encryption Improved Auditing
Database Protection Database Ease of UseIntelligent Load Balancing

69. MARIADB OPERATOR // MONITORING

70. Infrastructure as Code

71. INFRASTRUCTURE AS CODE (IaC)

“... Infrastructure as code is the
process of managing and provisioning
computer data center resources
through machine-readable definition
files, rather than physical hardware
configuration or interactive
configuration tools…”
[Confidentiality Level]

72. INFRASTRUCTURE AS CODE (IaC)

“...Your Git version-controlled manifest files can be deployed repeatedly into any
Kubernetes environment, ensuring that test, staging, pre-production, and production
environments can be easily replicated and repeated…”

●Store Version Controlled Infrastructure Settings in GIT
●Repeat the same infrastructure in many different environments
●Changes to the infrastructure can be done by modifying a file
●Deploy to Kubernetes with one command
●The same file can be applied to ANY Kubernetes environment.

73. INFRASTRUCTURE AS CODE (IaC)


replicas: 3
Simply Declare how many replica’s you require:
galera:
enabled: true
Create a Galera Cluster:
metrics:
enabled: true
Enable Metrics:

74. INFRASTRUCTURE AS CODE (IaC)

apiVersion: k8s.mariadb.com/v1alpha1
kind: MaxScale
metadata:
name: maxscale-galera
spec:
replicas: 3
servers:
- name: mariadb-0
address: 172.18.0.140
maintenance: false
- name: mariadb-1
address: 172.18.0.141
- name: mariadb-2
address: 172.18.0.142
Build a MaxScale Layer:

75. Use Cases

76. USE CASES
The MariaDB Operator is perfectly
suited for microservice
environments, where many small
API backed services are separated
by Schema. The operator allows
many separate clusters to be
deployed and version controlled to
back each individual service,
simply by declarative code.
When using one large system, it
sometimes becomes hard or
impossible to upgrade and manage
the database layer. The operator
allows a highly available clustered
database to be deployed,
managed, upgraded, supported
and backed up, simply and
efficiently on top of Kubernetes.
The same IaC can be used by
developers on their local laptops,
and replicated into testing
environments to ensure test and
development happen in the same
environment. Once tested they can
be sure the tested infrastructure will
be repeated in production onto a
production-grade installation.
Microservices Monolithic Installations Dev / Test / Prod

77. mariadb.com

78. Place Image Here
Get Started Today …
•Download The Ultimate Guide to High
Availability with MariaDB
•Download MariaDB MaxScale
•Download MariaDB Community Server
•Download MariaDB Enterprise Server
for evaluation

79. Questions and answers

80. mariadb.com