How 3 Companies Used Percona to Boost Performance, Availability, and Market Value

Percona Maintains Optimal Customer Health

At Percona, we love collaborating with companies to achieve their goals – no matter how ambitious. With each new customer, we get the chance to apply our open source expertise and technology to solve complex and fascinating database challenges. We’re grateful to work with so many amazing companies and want to share some recent success stories.


Appsuite is a full-scale SaaS provider for hospitality businesses. The company serves as a customer relationship management solution, offering point-of-sale integrated CRM; loyalty, reward, and gift card programs; and online ordering applications. At the onset of COVID-19, Appsuite recognized that, in addition to helping their existing customers adjust to rapid changes resulting from the pandemic, there were new opportunities to create value as a SaaS provider. To expand their business and serve new segments, they needed to scale their operations to support customers with a maximum of 30 locations to those with 800 locations or more. 

The Percona Consulting team guided Appsuite through the design and implementation of a scalable, highly reliable open source database architecture. This included a move to Percona’s open source database software, Percona Server for MySQL. Appsuite continues to collaborate with Percona on critical support projects for their production database systems.

“Percona has the best engineers in the world. Percona provides us with immediate 24×7 access to world-class MySQL engineering support. Avoiding the need to directly hire a skilled DBA has saved us a considerable amount of money.”Ross Hunton, Appsuite CTO

Otto Office

Otto Office is one of the largest business-to-business distance retailers for office supplies in Germany, specializing in mail-order sales, communications equipment, and office furniture to businesses and consumers. Their MySQL database cluster is the central point of their online sales system and requires a fast and reliable system architecture.

Otto Office was already using cluster functionality via Percona XtraDB Cluster to provide quick data synchronization over their different data centers, but they also wanted to improve database latency and architecture. Otto Office again partnered with Percona, this time to review their database environment and recommend improvements for making their cluster more stable, performant, and highly available, including tolerance of data center failure. Following this consulting engagement, Otto Office chose Percona to provide database support in case of an emergency.

“Percona consultants are database experts that focus on stable solutions. The expertise of the Percona consultants fitted perfectly with our team and led to good exchanges that found fast solutions.” – Sven Jacobsen, Director IT & eCommerce, Otto Office


BlaBlaCar is the world’s leading community-based travel network. Headquartered in Paris, France, The company enables more than 100 million members to share rides across 22 countries via its website and mobile apps. To support customer demand for flexible commute scheduling, BlaBlaCar must maintain a fast and reliable system architecture.

Working closely with the Percona Support team, BlaBlaCar used MariaDB to implement performance improvements to increase the uptime of their mission-critical applications. BlaBlaCar chose Percona because of its transparency, unbiased nature, and dedication to the open source database world, believing that the DNA of Percona matches their philosophy of always “choosing the right tool for the job.”

“BlaBlaCar’s Database Reliability Engineering team and Percona share the same belief that unbiased open source database expertise provides great products and attractive communication. Percona Support offers us a guarantee to run our business safely and allows us to be focused on high-value areas.” – Maxime Fouilleul Engineering Manager for BlaBlaCar’s DBRE Team

Get unbiased open source database expertise

In summary, Percona’s open source expertise enabled:

  • Appsuite to use MySQL to scale their business and expand their value in the market.
  • Otto Office to make their MySQL-based central sales system more stable, performant, and highly available.
  • BlaBlaCar to use MariaDB to improve their mission-critical application performance and uptime.

So what do you want to do? Our unbiased open source database experts are ready to roll up their sleeves and help you meet your goals too. Learn more about Percona support and services here



MariaDB 10.9 Quick Peek

MariaDB 10.9 Quick Peek

MariaDB 10.9 Quick PeekMariaDB 10.9 is a preview release of the popular open source database server and is considered alpha level code (pronounced: Not for production). It offers a glimpse of the evolution of the product and introduces some new features, at least for MariaDB.  Since I took a peek at the MySQL 8.0.28 release notes recently, it is time to see what MariaDB announced (https://mariadb.com/kb/en/mdb-1090-rn/) for their next release. My own comments are in italics and do not reflect anyone else’s opinion. 

The 10.9 server is offered in a few varieties, ala the old MySQL Labs releases where you can try some of the new features.  This allows them to independently develop the new features without having to integrate them too. This iteration has four choices. 

MariaDB 10.9 Alpha Download Options

You have a few choices for testing new MariaDB features to choose from for this alpha version of the database.


The TL;DR synopsis is that there is a lot of work being done on MariaDB and this is a first glimpse of what will become 10.9 and as such you probably will want to wait to download and evaluate unless one of the highlights below catches your fancy. There is nothing here that seems revolutionary but it is the first step of an evolutionary process for the MariaDB 10.9 server.

The New Stuff

JSON_OVERLAPS() is a new function that returns true if there is any commonality between two JSON documents. And JSON_PATH() gets both range notation and negative indexes. This will be interesting to contrast the long-existing MySQL versions.

SHOW EXPLAIN adds a JSON formatted output and EXPLAIN FOR CONNECTION gains syntax support for SHOW EXPLAIN.

Write to the redo log can be written asynchronously. Might want to test his under simulated duress just in case as the redo log looks like a good thing to keep synchronous. But this may work ‘well enough’ in most cases.

Better GTID filtering for uses of mysqlbinlog by adding the –do-domain-ids, –ignore-domain-ids, and –ignore-server-ids options. This should be handy for point-in-time recovery.

Local temporary tables now appear in information_schema.tables.table_type.

The merger of the old to old_mode sql variable. Hopefully, there will be no realy_old_mode in the future.

There is a Vault Key Management Plugin for Hashicorp’s vault.

There is now a JSON file interface to wsrep node state / SST progress logging. Apparently, Codership is adding a new feature to Galera cluster nodes to allow access to some wsrep status variables from a dedicated JSON file, that then can be read by an external monitoring tool. Or a human for that matter. I probably will not want to be that human.

The innodb_log_file_size to be changed dynamically. Handy and long desired.

Please send comments and questions to the author.


Deploying Any Version of Leading Open Source Databases for Tests and Experiments

Deploying Any Version of Leading Open Source Databases

Deploying Any Version of Leading Open Source DatabasesI want to present a tool for running a specific version of open source databases in a single instance, replication setups, and Kubernetes. AnyDbVer deploys MySQL/MariaDB/MongoDB/PostgreSQL for testing and experiments.

It Could Be Started By…

Docker (or Podman) or dbdeployer (MySQL-Sandbox successor) could also start a specific database version, but such installations are significantly different from production setups.

Ansible Playbook

There is an Ansible playbook with few roles configurable by environment variables.

Bash Scripts

You may ignore the Ansible layer because the ./anydbver bash script hides it.

In LXD containers

It’s not practical to reserve physical servers or virtual machines for all tasks. Frequently you need just a few database instances and the ability to restart it with systemd and check logs with journalctl. AnydbVer spawns one or more Linux Containers managed by Canonical (Ubuntu) LXD, and LXD containers support systemd not using a layered filesystem setup.

Best Performance with Linux Running on Hardware Directly

Linux Containers is not an emulation layer. From the “host” system, you can see all processes and files created by containers. Thus the performance is very similar to the same database running on a physical server.

You Can Run Vagrant + VirtualBox as Well, For Other OS

LXD setup is relatively easy, but sometimes you may need to run AnydbVer just with a few commands. Vagrant could start an unmodified Ubuntu virtual machine and automatically configure AnydbVer for you.

You can find the required files and setup instructions at https://github.com/ihanick/anydbver.

Clone the https://github.com/ihanick/anydbver repository to setup LXD or start Vagrant.

git clone https://github.com/ihanick/anydbver.git
cd anydbver
vagrant up
vagrant ssh
cd anydbver

Single Instance Usage

Imagine that you need the exact CentOS 7 package version: Percona Server for MySQL 5.6.43-rel84.3:

$ ./anydbver deploy percona-server:5.6.43-rel84.3
$ ./anydbver ssh
$ mysql
mysql> select version();

You are not limited to using full version specification. To use the latest matching version, reduce 5.6.43-rel84.3 down to 5.6.43 or even 5.6. To run other databases, replace percona-server with:

  • pxc: Percona XtraDB Cluster
  • mysql: Oracle MySQL Community Server
  • mariadb: MariaDB
  • mariadb-cluster: MariaDB Galera Cluster
  • pg: Postgres build from https://www.postgresql.org/
  • ppg: Percona Distribution for PostgreSQL
  • psmdb: Percona Server for MongoDB

Multiple Instances

Several containers are not consuming a significant amount of resources. Actually five MySQL container instances will consume the same resources as five individual processes running on the same host. The syntax is:

$ ./anydbver deploy <default node definition> node1 <node1 definition> node2 <node2 definition> ...

For example, run two independent MySQL instances:

$ ./anydbver mysql node1 mysql

SSH access to default node:

$ ./anydbver ssh
# or
$ ./anydbver ssh default

All other nodes (replace node1 with other node names):

$ ./anydbver ssh node1


You may have a server hostname specified. For example let’s run two Percona Server for MySQL instances:

$ ./anydbver deploy ps:5.7 hostname:leader.percona.local node1 ps:5.7 hostname:follower.percona.local
$ ./anydbver ssh leader
or ./anydbver ssh leader.percona or leader.percona.local
[root@leader ~]# mysql --host follower.percona.local --prompt '\h mysql>'
follower.percona.local mysql>


The most interesting part of modern open-source databases is replication. Even active-active replication setups are starting from a single server (leader or master/primary). Start the first node normally and attach additional nodes with master:nodename or leader:nodename. PXC or Galera servers could participate in both synchronous and asynchronous replication. Thus, for Galera clusters, you need galera-master or galera-leader syntax.

Start a 3 node Percona XtraDB cluster (latest 5.7):

./anydbver deploy pxc:5.7 node1 pxc:5.7 galera-master:default node2 pxc:5.7 galera-master:default

Run master and two async slaves with Percona Server for MySQL and add all three servers to ProxySQL setup:

./anydbver deploy ps:5.7 node1 ps:5.7 master:default node2 ps:5.7 master:default node3 proxysql master:default

Setup physical replication with slots for Postgresql 12.3:

./anydbver deploy pg:12.3 node1 pg:12.3 master:default

Make a Mongo replica set named rs0:

./anydbver deploy psmdb replica-set:rs0 node1 psmdb master:default replica-set:rs0 node2 psmdb master:default replica-set:rs0

MongoDB Sharding

MongoDB sharding setup requires several server types: servers with data (shardsrv), configuration servers (configsrv), and mongos server:

./anydbver deploy \
psmdb:4.2 replica-set:rs0 shardsrv \
node1 psmdb:4.2 master:default replica-set:rs0 shardsrv \
node2 psmdb:4.2 master:default replica-set:rs0 shardsrv \
node3 psmdb:4.2 configsrv replica-set:cfg0 \
node4 psmdb:4.2 configsrv replica-set:cfg0 master:node3 \
node5 psmdb:4.2 configsrv replica-set:cfg0 master:node3 \
node6 psmdb:4.2 mongos-cfg:cfg0/node3,node4,node5 mongos-shard:rs0/default,node1,node2

Containers and Orchestration

The fact that we are already using containers (LXD) shouldn’t confuse you. We can still run docker images inside our nodes. Nested containers and Podman makes it possible.

Run Percona Monitoring and Management Docker containers

Let’s deploy the default node with Podman and run the Percona Monitoring and Management (PMM) server docker container in it. Percona Server for MySQL 5.7 with PMM client will run on node1:

./anydbver deploy pmm node1 ps:5.7 pmm-client pmm-server:default

Run multi-node Kubernetes cluster

Kubernetes also could utilize nested containers. There are several small Kubernetes distributions: minikube, microk8s, k3s, k0s. The simplest fully functional Kubernetes could be implemented with k3s.

The first k3s LXD container executes the API server and workers. Additional LXD containers could run more workers. Multiple workers are important to run complex HA applications with hostname anti-affinity.

./anydbver deploy k3s node1 k3s-master:default node2 k3s-master:default
./anydbver ssh

On a default node, we can execute kubectl or helm:

$ kubectl get nodes
ihanick-node2 Ready <none> 14m v1.19.2+k3s1
ihanick-default Ready master 17m v1.19.2+k3s1
ihanick-node1 Ready <none> 15m v1.19.2+k3s1

Percona Kubernetes Operator for Percona XtraDB Cluster

The Kubernetes cluster allows running stateful applications, like databases, services (similar to Amazon AWS S3), or monitoring solutions. Let’s start:

  • 4 node cluster
  • MinIO (implements S3 api) for backups
  • Percona XtraDB Cluster (managed by the operator)
  • Percona Monitoring and Management
./anydbver deploy k3s \
node1 k3s-master:default \
node2 k3s-master:default \
node3 k3s-master:default \
default k8s-minio k8s-pmm k8s-pxc backup
./anydbver ssh
kubectl get pods
svclb-monitoring-service-n5nsx 1/1 Running 0 20m
svclb-monitoring-service-htssw 1/1 Running 0 20m
svclb-monitoring-service-n9kt4 1/1 Running 0 20m
svclb-monitoring-service-7btbh 1/1 Running 0 20m
minio-service-6db6667fb9-tk69n 1/1 Running 0 20m
monitoring-0 1/1 Running 0 20m
percona-xtradb-cluster-operator-7886ccf6b5-rtwxc 1/1 Running 0 18m
cluster1-pxc-0 2/2 Running 2 17m
cluster1-haproxy-0 2/2 Running 0 17m
cluster1-haproxy-1 2/2 Running 0 12m
cluster1-haproxy-2 2/2 Running 0 12m
cluster1-pxc-1 2/2 Running 1 13m
cluster1-pxc-2 2/2 Running 1 10m

Percona Kubernetes Operator for Percona Server for MongoDB

You can install and configure the database in the same way, regardless of the architecture:

./anydbver deploy k3s \
  node1 k3s-master:default \
  node2 k3s-master:default \
  node3 k3s-master:default \
  default k8s-minio k8s-mongo backup

Zalando Postgres Operator

You are not limited to Percona-only products and can run other K8s operators:

./anydbver deploy k3s node1 k3s-master:default node2 k3s-master:default node3 k3s-master:default default k8s-pg


AnyDbVer is a useful deployment tool for experiments with:

AnyDbVer restrictions:

  • It is not a production deployment tool.
  • The deployment process takes a long time. The tool downloads and installs all packages from OS and Vendor repositories.

Webinar January 20: MariaDB Observability

MariaDB Observability

MariaDB ObservabilityDon’t miss out! Join Peter Zaitsev, Percona CEO, as he discusses MariaDB observability!

A broken MariaDB means a broken application, so maintaining insights in MariaDB operational performance is critical. Thankfully MariaDB offers a lot in terms of observability to quickly resolve problems and get great insights into optimization opportunities.

In this webinar, we will cover the most important observability improvements in MariaDB, ranging from Performance Schema and Information Schema to enhanced error logging and optimizer trace.

If you’re a Developer or DBA passionate about observability or want to be empowered to resolve MariaDB problems quickly and efficiently, you should attend this talk.

Please join Peter Zaitsev, Percona CEO, on Wednesday, January 20th at 11:00 AM EST for his webinar on MariaDB Observability.

Register for Webinar

If you can’t attend, sign up anyway, and we’ll send you the slides and recording afterward.


Exciting and New Features in MariaDB 10.5

New Features in MariaDB 10.5

New Features in MariaDB 10.5MariaDB 10.5 was released in June 2020 and it will be supported until June 2025. This is the current stable version and comes with more exciting new features. In this blog, I am going to explain the new and exciting features involved in MariaDB 10.5. 

  • Amazon S3 engine
  • Column Store
  • INET 6 data type
  • Binaries name changed to mariadb
  • More granular privileges
  • Galera with full GTID support
  • InnoDB refactoring

Amazon S3 Engine

S3 engine is a nice feature in MariaDB 10.5. Now, you can directly move your table from a local device to Amazon S3 using the ALTER. Still, your data is accessible from MariaDB clients using the standard SQL commands. This is a great solution to those who are looking to archive data for future references at a low cost. I have written a blog about this feature – MariaDB S3 Engine: Implementation and Benchmarking – which has more insights on this. 


MariaDB [(none)]> install soname 'ha_s3';
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> select * from information_schema.engines where engine = 's3'\G
*************************** 1. row ***************************
      ENGINE: S3
     COMMENT: Read only table stored in S3. Created by running ALTER TABLE table_name ENGINE=s3
          XA: NO
1 row in set (0.000 sec)


MariaDB [s3_test]> alter table percona_s3 engine=s3;
Query OK, 0 rows affected (1.934 sec)              
Records: 0  Duplicates: 0  Warnings: 0

  • The S3 engine tables are completely read-only.
  • COUNT(*) is pretty fast on s3 engine tables.


MariaDB ColumnStore 1.5 is available with MariaDB 10.5 community server. It brings a high-performance, open source, distributed, SQL compatible analytics solution. Before MariaDB 10.5, ColumnStore was available as a separate fork of MariaDB. But with MariaDB 10.5, ColumnStore is now completely integrated. All you need to do is install the package for ColumnStore “MariaDB-columnstore-engine.x86_64”.

[root@mariadb ~]# yum list installed | grep -i columnstore
MariaDB-columnstore-engine.x86_64   10.5.5-1.el7.centos         @mariadb-main

MariaDB [jesus]> select plugin_name,plugin_status,plugin_library,plugin_version from information_schema.plugins where plugin_name like 'columnstore%'; 
| plugin_name         | plugin_status | plugin_library    | plugin_version |
| Columnstore         | ACTIVE        | ha_columnstore.so | 1.5            |
| COLUMNSTORE_COLUMNS | ACTIVE        | ha_columnstore.so | 1.5            |
| COLUMNSTORE_TABLES  | ACTIVE        | ha_columnstore.so | 1.5            |
| COLUMNSTORE_FILES   | ACTIVE        | ha_columnstore.so | 1.5            |
| COLUMNSTORE_EXTENTS | ACTIVE        | ha_columnstore.so | 1.5            |
5 rows in set (0.002 sec)

MariaDB [jesus]> create table hercules(id int, name varchar(16)) engine = ColumnStore;
Query OK, 0 rows affected (0.503 sec)

MariaDB [jesus]> show create table hercules\G
*************************** 1. row ***************************
       Table: hercules
Create Table: CREATE TABLE `hercules` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(16) DEFAULT NULL
) ENGINE=Columnstore DEFAULT CHARSET=latin1
1 row in set (0.000 sec)

MariaDB ColumnStore 1.5 comes with two .xml utilities, which greatly helps with configuration management.

  • mcsGetConfig : Used to display the current configurations
  • mcsSetConfig : Used to change the configuration
[root@mariadb vagrant]# mcsGetConfig -a | grep CrossEngineSupport.Pass
CrossEngineSupport.Password = 
[root@mariadb vagrant]# mcsSetConfig CrossEngineSupport Password "hercules7sakthi"
[root@mariadb vagrant]# mcsGetConfig -a | grep CrossEngineSupport.Pass
CrossEngineSupport.Password = hercules7sakthi

INET6 Data Type

Usually, INET6 refers to the IPv6 family.

  • INET6 data type is introduced to store the IPv6 addresses.
  • INET6 data type also can be used to store the IPv4 addresses assuming conventional mapping of IPv4 addresses into IPv6 addresses.
  • Internally storage engine see the INET6 as BINARY(16) and clients see the INET6 as CHAR(39)
  • Values are stored as a 16-byte fixed-length binary string


MariaDB [jesus]> create table inet6test (id int primary key auto_increment, ipaddresses INET6);
Query OK, 0 rows affected (0.005 sec)

MariaDB [jesus]> insert into inet6test (ipaddresses) values ('2001:0db8:85b3:0000:0000:8a2e:0370:7334');
Query OK, 1 row affected (0.001 sec)

MariaDB [jesus]> insert into inet6test (ipaddresses) values ('::');
Query OK, 1 row affected (0.002 sec)

MariaDB [jesus]> select * from inet6test;
| id | ipaddresses                  |
|  1 | 2001:db8:85b3::8a2e:370:7334 |
|  2 | ::              |
2 rows in set (0.000 sec)

Binaries Name Changed to mariadb

All binaries are now changed to “mariadb” from “mysql”, with symlinks for the corresponding mysql command.


  • “mysql” is now “mariadb”
  • “mysqldump” is now “mariadb-dump”
  • “mysqld” is now “mariadbd”
  • “mysqld_safe” is now “mariadbd-safe”

Using “mariadb” client:

[root@mariadb ~]# mariadb -e "select @@version, @@version_comment"
| @@version      | @@version_comment |
| 10.5.5-MariaDB | MariaDB Server    |

Using “mariadb-dump”:

[root@mariadb ~]# mariadb-dump mysql > mysql.sql
[root@mariadb ~]# less mysql.sql | head -n5
-- MariaDB dump 10.17  Distrib 10.5.5-MariaDB, for Linux (x86_64)
-- Host: localhost    Database: mysql
-- ------------------------------------------------------
-- Server version 10.5.5-MariaDB

MariaDB server startup via systemd service will be started using the mariadbd binary. This is applicable for mariadbd-safe wrapper script as well. Even when called via the mysqld_safe symlink, it will start the actual server process as mariadbd, not mysqld.


Using startup service:

[root@mariadb ~]# service mysql start
Redirecting to /bin/systemctl start mysql.service
[root@mariadb ~]# ps -ef | grep -i mysql
mysql     9002     1  1 01:23 ?        00:00:00 /usr/sbin/mariadbd
root      9021  8938  0 01:23 pts/0    00:00:00 grep --color=auto -i mysql

Using mariadbd-safe:

[root@mariadb ~]# mariadbd-safe --user=mysql &
[root@mariadb ~]# 200806 01:30:43 mysqld_safe Logging to '/var/lib/mysql/mariadb.err'.
200806 01:30:43 mysqld_safe Starting mariadbd daemon with databases from /var/lib/mysql
[root@mariadb ~]# 
[root@mariadb ~]# ps -ef | grep -i mysql
root      9088  8938  0 01:30 pts/0    00:00:00 /bin/sh /bin/mariadbd-safe --user=mysql
mysql     9162  9088  1 01:30 pts/0    00:00:00 //sbin/mariadbd --basedir=/ --datadir=/var/lib/mysql --plugin-dir=//lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql/mariadb.err --pi

Using mysqld_safe:

[root@mariadb ~]# mysqld_safe --user=mysql &
[root@mariadb ~]# 200806 01:31:40 mysqld_safe Logging to '/var/lib/mysql/mariadb.err'.
200806 01:31:40 mysqld_safe Starting mariadbd daemon with databases from /var/lib/mysql
[root@mariadb ~]# ps -ef | grep -i mysql
root      9179  8938  0 01:31 pts/0    00:00:00 /bin/sh /bin/mysqld_safe --user=mysql
mysql     9255  9179  0 01:31 pts/0    00:00:00 //sbin/mariadbd --basedir=/ --datadir=/var/lib/mysql --plugin-dir=//lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql/mariadb.err --pid-file=mariadb.pid

From the above examples, you can see that all the MariaDB server startup is using the “mariadbd”.

More Granular Privileges

Privileges are more granular now. SUPER privilege is split now with more small privileges, similar to MySQL 8 dynamic privileges.  Security-wise this is a very good implementation to avoid unwanted privileges allocation to users.

  • BINLOG ADMIN – Enables administration of the binary log, including the PURGE BINARY LOGS
  • BINLOG REPLAY – Enables replaying the binary log with the BINLOG statement
  • CONNECTION ADMIN – Enables administering connection resource limit options. This includes ignoring the limits specified by max_connections, max_user_connections, and max_password_errors
  • FEDERATED ADMIN – Execute CREATE SERVER, ALTER SERVER, and DROP SERVER statements. Added in MariaDB 10.5.2.
  • READ_ONLY ADMIN – User can set the read_only system variable and allows the user to perform write operations, even when the read_only option is active. Added in MariaDB 10.5.2.
  • REPLICATION MASTER ADMIN – Permits administration of primary servers, including the SHOW REPLICA HOSTS statement, and setting the gtid_binlog_state, gtid_domain_id, master_verify_checksum, and server_id system variables. Added in MariaDB 10.5.2.
  • REPLICATION SLAVE ADMIN – Permits administering replica servers, including START SLAVE, STOP SLAVE, CHANGE MASTER, SHOW SLAVE STATUS, SHOW RELAYLOG EVENTS statements (new in MariaDB 10.5.2).
  • SET USER – Enables setting the DEFINER when creating triggers, views, stored functions, and stored procedures (new in MariaDB 10.5.2).


  • “SHOW MASTER STATUS” command is now renamed to “SHOW BINLOG STATUS”
MariaDB [jesus]> show binlog status;
| File        | Position | Binlog_Do_DB | Binlog_Ignore_DB |
| herc.000003 |      525 |              |                  |
1 row in set (0.000 sec)

Galera With Full GTID Support

Galera is now completely supported with GTID from MariaDB 10.5. It will greatly help the cluster + Async replication environment. With this feature, all nodes in a cluster will have the same GTID for replicated events originating from the cluster.

MariaDB 10.5 also has the new SESSION variable “wsrep_gtid_seq_no”. With this variable, we can manually update the WSREP GTID sequence number in the cluster ( like gtid_seq_no for non WSREP transactions ).

MariaDB [jesus]> show variables like 'wsrep_gtid_seq_no';        
| Variable_name     | Value |
| wsrep_gtid_seq_no | 0     |
1 row in set (0.001 sec)

InnoDB Refactoring

There are some notable changes in InnoDB engine, which makes MariaDB more divergent from MySQL.

Apart from this, MariaDB 10.5 has more improvements on the following topics as well.

  • JSON
  • Query Optimizer
  • Binary logs with more metadata

I am looking forward to experimenting with the new MariaDB 10.5 features and how they are going to help in the production environments. I am also planning to write blogs on some of these topics, so stay tuned! 

Your mission-critical applications depend on your MariaDB database environment. What happens if your database goes down? Contact Percona MariaDB Database Support! Percona is the premier support provider for open source databases, including MariaDB, the most well-known fork of MySQL.


Evaluating Performance Improvements in MariaDB 10.5.5

Performance Improvements in MariaDB 10.5.5

Performance Improvements in MariaDB 10.5.5Recently, I published a series of posts on MySQL and MariaDB, where MariaDB 10.5.4 does not necessarily perform well in some scenarios:

In comments left on How MySQL and MariaDB Perform on NVMe Storage, I was told that MariaDB 10.5.4 is not the best version, as there are already known performance bugs that will be fixed in MariaDB 10.5.5, and it would be better to test MariaDB 10.5.5. And now, MariaDB 10.5.5 comes with fixes:

I made a promise that I will test the new version, so this is me fulfilling my promise.

Instead of posting multiple posts, let’s review all scenarios at once. Just to refresh the material, I’ve tested sysbench-tpcc 1000W, which is about 100GB in size in the following setups:

  • Data is located on SATA SSD, innodb_buffer_pool_size=140GB (CPU-bound scenario)
  • Data is located on NVMe, innodb_buffer_pool_size=140GB (CPU-bound scenario)
  • Data is located on SATA SSD, innodb_buffer_pool_size=25GB (IO-bound scenario)
  • Data is located on NVMe, innodb_buffer_pool_size=25GB (IO-bound scenario)

Data is located on SATA SSD, innodb_buffer_pool_size=140GB (CPU-bound scenario)

(The original results are here – Checkpointing in MySQL and MariaDB)

For this re-test round, I compare only MariaDB 10.5.4 vs MariaDB 10.5.5:

MariaDB 10.5.4 vs MariaDB 10.5.5

After the difference in the warm-up behavior, the result is about the same, and we can confirm this with boxplots for the last 2500 sec:

MariaDB 10.5.4 vs MariaDB 10.5.5 2500 sec

Data is located on SATA SSD, innodb_buffer_pool_size=25GB (IO-bound scenario)

This one is interesting, and actually this is where we can see improvements in MariaDB 10.5.5.

improvements in MariaDB 10.5.5

To see individual variations in details, let’s separate charts:

And then again compare the throughput for the last 2500 sec:

compare the throughput for the last 2500 sec

What’s interesting is not only has MariaDB 10.5.5 improved the results, but it also shows a better average throughput than MySQL 8.0.21, though with a higher variation.

Data is located on NVMe, innodb_buffer_pool_size=140GB (CPU-bound scenario)

There we still see a weird initial drop followed by recovery, and the throughput is similar to MariaDB 10.5.4, but something a little worse.

Data is located on NVMe, innodb_buffer_pool_size=25GB (IO-bound scenario)

throughput is similar to MariaDB 10.5.4

This case is an absolute disaster for MariaDB 10.5.5; there is bug https://jira.mariadb.org/browse/MDEV-23399. Most of the time MariaDB 10.5.5 shows the throughput under 100 tps (while MySQL averages above 5000 tps).

I personally think this is a catastrophic bug and MariaDB 10.5.5 with this performance should never have been released to the public, but instead, stay in the internal testing stage.


Use MySQL Without a Password (And Still Be Secure)

Use MySQL Without a Password

Use MySQL Without a PasswordSome say that the best password is the one you don’t have to remember. That’s possible with MySQL, thanks to the auth_socket plugin and its MariaDB version unix_socket.

Neither of these plugins is new, and some words have been written about the auth_socket on this blog before, for example: how to change passwords in MySQL 5.7 when using plugin: auth_socket. But while reviewing what’s new with MariaDB 10.4, I saw that the unix_socket now comes installed by default and is one of the authentication methods (one of them because in MariaDB 10.4 a single user can have more than one authentication plugin, as explained in the Authentication from MariaDB 10.4 document).

As already mentioned this is not news, and even when one installs MySQL using the .deb packages maintained by the Debian team, the root user is created so it uses the socket authentication. This is true for both MySQL and MariaDB:

root@app:~# apt-cache show mysql-server-5.7 | grep -i maintainers
Original-Maintainer: Debian MySQL Maintainers <pkg-mysql-maint@lists.alioth.debian.org>
Original-Maintainer: Debian MySQL Maintainers <pkg-mysql-maint@lists.alioth.debian.org>

Using the Debian packages of MySQL, the root is authenticated as follows:

root@app:~# whoami
root@app:~# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.27-0ubuntu0.16.04.1 (Ubuntu)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select user, host, plugin, authentication_string from mysql.user where user = 'root';
| user | host      | plugin | authentication_string |
| root | localhost | auth_socket |                       |
1 row in set (0.01 sec)

Same for the MariaDB .deb package:

10.0.38-MariaDB-0ubuntu0.16.04.1 Ubuntu 16.04

MariaDB [(none)]> show grants;
| Grants for root@localhost                                                                      |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION                                  |
2 rows in set (0.00 sec)

For Percona Server, the .deb packages from the official Percona Repo are also setting the root user authentication to auth_socket. Here is an example of Percona Server for MySQL 8.0.16-7 and Ubuntu 16.04:

root@app:~# whoami
root@app:~# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.16-7 Percona Server (GPL), Release '7', Revision '613e312'

Copyright (c) 2009-2019 Percona LLC and/or its affiliates
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select user, host, plugin, authentication_string from mysql.user where user ='root';
| user | host      | plugin | authentication_string |
| root | localhost | auth_socket |                       |
1 row in set (0.00 sec)

So, what’s the magic? The plugin checks that the Linux user matches the MySQL user using the SO_PEERCRED socket option to obtain information about the user running the client program. Thus, the plugin can be used only on systems that support the SO_PEERCRED option, such as Linux. The SO_PEERCRED socket option allows retrieving the uid of the process that is connected to the socket. It is then able to get the user name associated with that uid.

Here’s an example with the user “vagrant”:

vagrant@mysql1:~$ whoami
vagrant@mysql1:~$ mysql
ERROR 1698 (28000): Access denied for user 'vagrant'@'localhost'

Since no user “vagrant” exists in MySQL, the access is denied. Let’s create the user and try again:

MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'vagrant'@'localhost' IDENTIFIED VIA unix_socket;
Query OK, 0 rows affected (0.00 sec)

vagrant@mysql1:~$ mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 45
Server version: 10.0.38-MariaDB-0ubuntu0.16.04.1 Ubuntu 16.04
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show grants;
| Grants for vagrant@localhost                                                    |
| GRANT ALL PRIVILEGES ON *.* TO 'vagrant'@'localhost' IDENTIFIED VIA unix_socket |
1 row in set (0.00 sec)


Now, what about on a non-debian distro, where this is not the default? Let’s try it on Percona Server for MySQL 8 installed on a CentOS 7:

mysql> show variables like '%version%comment';
| Variable_name   | Value                                   |
| version_comment | Percona Server (GPL), Release 7, Revision 613e312 |
1 row in set (0.01 sec)

mysql> CREATE USER 'percona'@'localhost' IDENTIFIED WITH auth_socket;
ERROR 1524 (HY000): Plugin 'auth_socket' is not loaded

Failed. What is missing? The plugin is not loaded:

mysql> pager grep socket
PAGER set to 'grep socket'
mysql> show plugins;
47 rows in set (0.00 sec)

Let’s add the plugin in runtime:

mysql> nopager
PAGER set to stdout
mysql> INSTALL PLUGIN auth_socket SONAME 'auth_socket.so';
Query OK, 0 rows affected (0.00 sec)

mysql> pager grep socket; show plugins;
PAGER set to 'grep socket'
| auth_socket                     | ACTIVE | AUTHENTICATION | auth_socket.so | GPL     |
48 rows in set (0.00 sec)

We got all we need now. Let’s try again:

mysql> CREATE USER 'percona'@'localhost' IDENTIFIED WITH auth_socket;
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'percona'@'localhost';
Query OK, 0 rows affected (0.01 sec)

And now we can log in as the OS user “percona”.

[percona@ip-192-168-1-111 ~]$ whoami
[percona@ip-192-168-1-111 ~]$ mysql -upercona
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 19
Server version: 8.0.16-7 Percona Server (GPL), Release 7, Revision 613e312

Copyright (c) 2009-2019 Percona LLC and/or its affiliates
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select user, host, plugin, authentication_string from mysql.user where user ='percona';
| user    | host   | plugin   | authentication_string |
| percona | localhost | auth_socket |                       |
1 row in set (0.00 sec)

Success again!

Question: Can I try to log as the user percona from another user?

[percona@ip-192-168-1-111 ~]$ logout
[root@ip-192-168-1-111 ~]# mysql -upercona
ERROR 1698 (28000): Access denied for user 'percona'@'localhost'

No, you can’t.


MySQL is flexible enough in several aspects, one being the authentication methods. As we see in this post, one can achieve access without passwords by relying on OS users. This is helpful in several scenarios, but just to mention one: when migrating from RDS/Aurora to regular MySQL and using IAM Database Authentication to keep getting access without using passwords.


Percona Toolkit 3.1.0 Is Now Available

Percona Toolkit

Percona announces the release of Percona Toolkit 3.1.0 on September 13, 2019.

Percona Toolkit is a collection of advanced open-source command-line tools, developed and used by the Percona technical staff, that are engineered to perform a variety of MySQL®, MongoDB®, PostgreSQL® and system tasks that are too difficult or complex to perform manually. With over 1,000,000 downloads, Percona Toolkit supports Percona Server for MySQL, MySQL, MariaDB, PostgreSQL, Percona Server for MongoDB, and MongoDB.

Percona Toolkit, like all Percona software, is free and open source. You can download packages from the website or install from official repositories.

This release includes the following changes:

New features and improvements:

  • PT-1696: the new pt-pg-summary tool supports PostgreSQL data collection in a way similar to other PT summary tools. The following is a fragment of the report that the tool produces:
    • ##### --- Database Port and Data_Directory --- ####
      |         Name         |                      Setting                       |
      | data_directory       | /var/lib/postgresql/9.5/main                       |
      ##### --- List of Tablespaces ---- ######
      |         Name         |         Owner        |               Location                             |
      | pg_default           | postgres             |                                                    |
      | pg_global            | postgres             |                                                    |
      ##### --- Cluster Information --- ####
       Usename        : postgres                                                           
       Time           : 2019-09-13 08:30:42.272582 -0400 EDT                                     
       Client Address : ::1                                             
       Client Hostname:                         
       Version        : PostgreSQL 9.5.18 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1                                      
       Started        : 2019-09-13 08:29:43.175138 -0400 EDT                                  
       Is Slave       : false                                              
      ##### --- Databases --- ####
      |       Dat Name       |    Size    |
      | template1            |    6841 kB |
  • PT-1663: pt-stalk has two new options limiting the amount of disk space it can consume: --retention-size option makes pt-stalk to store less than the specified amount of megabytes, while --retention-count option limits the number of runs for which data are kept. Following simple example illustrates how these two parameters can be passed to the tool (here pt-stalk just collects the information and exits):
    pt-stalk --no-stalk --retention-count=3 --retention-size=100M -- --defaults-file=./my.default.cnf
  • PT-1741: Migration to a new MongoDB driver was done.
  • PT-1761: pt-online-schema-change will not run under MySQL 8.0.14 .. 8.0.17 if the table has foreign keys
    Important note: There is an error in MySQL from versions 8.0.14 up to the current 8.0.17 that makes MySQL die under certain conditions when trying to rename a table. Since the last step for pt-online-schema-change is to rename the tables to swap the old and new ones, we have added a check that prevents running pt-online-schema-change if the conditions for this error are met.

Bug fixes:

  • PT-1114: pt-table-checksum failed when the table was empty
  • PT-1344: pt-online-schema-change failed to detect hostnames with a specified port number
  • PT-1575: pt-mysql-summary did not print the PXC section for PXC 5.6 and 5.7
  • PT-1630: pt-table-checksum had a regression which prevented it from working with Galera cluster
  • PT-1633: pt-config-diff incorrectly parsed variables with numbers having K, M, G or T suffix (Thanks to Dieter Adriaenssens)
  • PT-1709: pt-upgrade generated “Use of uninitialized value in concatenation (.) or string” error in case of invalid MySQL packets
  • PT-1720: pt-pmp exited with an error in case of any unknown option in a common PT configuration file
  • PT-1728: pt-table-checksum failed to scan small tables that get wiped out often
  • PT-1734: pt-stalk did non-strict matching for ‘log_error’, resulting in wider filtering
  • PT-1746: pt-diskstats didn’t work for newer Linux kernels starting from 4.18

Help us improve our software quality by reporting any bugs you encounter using our bug tracking system.


Webinar 9/17: Percona XtraBackup vs Mariabackup vs MySQL Enterprise Backup

Webinar Percona XtraBackup vs Mariabackup vs MySQL Enterprise Backup

Please join Percona Senior Support Engineer Juan Pablo Arruti as he presents his talk “Percona XtraBackup vs Mariabackup vs MySQL Enterprise Backup” on Tuesday, September 17th, 2019 at 10:00 AM PDT (UTC-7) / 1:00 PM EDT (UTC-4).

Register Now

There are few ways to take a backup. Some of the most used tools are Percona Xtrabackup, MariaBackup, and MySQL Enterprise Backup.

In this talk, the audience will get an in-depth overview of:

– Differences between the tools
– Comparison of features
– Which tool works on which MySQL/MariaDB flavor
– Supported Storage Engines
– Limitations

If you can’t attend, sign up anyways we’ll send you the slides and recording afterward.


MariaDB Track at Percona Live

MariaDB track at Percona Live 2019

mariadb logoLess than one month left until Percona Live. This time the Committee work was a bit unusual. Instead of having one big committee for the whole conference we had a few mini-committees, each responsible for a track. Each independent mini-committee, in turn, had a leader who was responsible for the whole process. I led the MariaDB track. In this post, I want to explain how we worked, which topics we have chosen, and why.

For MariaDB, we had seven slots: five for 50-minutes talks, two for 25-minutes talks and 19 submissions. We had to reject two out of three proposals. We also had to decide how many topics the program should cover. My aim here was to use the MariaDB track to demonstrate as many MariaDB unique features as possible. I also wanted to have as many speakers as possible, considering the number of slots we had available.

The committee agreed, and we tried our best for the program to cover the various topics. If someone sent us two or more proposals, we choose only one to allow more speakers to attend.

We also looked to identify gaps in submitted sessions. For example, if we wanted for a topic to be covered and no one sent a proposal with such a subject, we invited potential speakers and asked them to submit with that topic in mind. Or we asked those who already submitted similar talks to improve them.

In the end, we have five 50-minutes sessions, one MariaDB session in the MySQL track, two 25-minutes sessions, one tutorial, and one keynote. All of them are by different speakers.

The Program

The first MariaDB event will be a tutorial: “Expert MariaDB: Harness the Strengths of MariaDB Server” by Colin Charles on Tuesday, May 28

Colin started his MySQL career as a Community Engineer back in the MySQL AB times. He worked on numerous MySQL events, both big and small, including Percona Live’s predecessor, O’Reilly’s MySQL Conference and Expo. Colin joined Monty Program Ab, and MariaDB Corporation as a Chief Evangelist, then spent two years as Chief Evangelist at Percona. Now he is an independent consultant at his own company GrokOpen.

Colin will not only talk about unique MariaDB features up to version 10.4, but will also help you try all of them out. This tutorial is a must-attend for everyone interested in MariaDB.

Next day: Wednesday, May 29 – the first conference day – will be the MariaDB Track day.

MariaDB Foundation Bronze SponsorshipMariaDB talks will start from the keynote by Vicentiu Ciorbaru about new MariaDB features in version 10.4. He will highlight all the significant additions in this version.

Vicentiu started his career at MariaDB Foundation as a very talented Google Summer of Code student. His first project was Roles. Then he worked a lot on MariaDB Optimizer, bug fixes, and code maintenance. At the same time, he discovered a talent for public speaking, and now he is the face of MariaDB Foundation.

We at the committee had a hard choice: either to accept his 50-minutes session proposal or ask him to make a keynote. This decision was not easy, because a keynote is shorter than 50 minutes. At the same time, though, everyone at the conference will be able to see it. Brand new features of version 10.4 are a very important topic. Therefore, we decided that it would be best to have Vicentiu as a keynote speaker.

Morning sessions

virtualhealthSessions will start with a talk by Alexander Rubin “Opensource Column Store Databases: MariaDB ColumnStore vs. ClickHouse” Alex began his MySQL career as a web developer, then joined MySQL AB as a consultant. He then moved to Percona as Principal Architect. It was our loss when he left Percona to start applying his recommendations himself on behalf of a medical startup VirtualHealth! During his career as a MySQL consultant, he tried all the sexiest database products, loaded terabytes of data into them, ran the deadly intensive loads. He is the one who knows best about database strengths and weaknesses. I would recommend his session to everyone who is considering a column store solution.

codership_720_156Next talk is “Galera Cluster New Features” by Seppo Jaakola. This session is about the long-awaited Galera 4 library. Seppo is one of three founders of Codership Oy: the company which brought us Galera library. Before the year 2007, when the Galera library was first released, MySQL users had to choose between asynchronous replication and asynchronous replication (that’s not a typo). Seppo brought us a solution which allowed us to continue using InnoDB in the style we were used to using while writing to all nodes. The Galera library looks after the data consistency. After more than ten years the product is mature and leaving its competitors far behind. The new version brings us streaming replication technology and other improvements which relax usage limitations and make Galera Cluster more stable. I recommend this session for everyone who looks forward to a synchronous replication future.

Afternoon sessions

Walmart LogoAfter the lunch break, we will meet MariaDB users Sandeep Jangra and Andre Van Looveren who will show how they use MariaDB at Walmart in their talk “Lessons Learned Building a Fully Automated Database Platform as a Service Using Open Source Technologies in the Cloud”. Sandeep and Andre manage more than 6000 MariaDB installations. In addition to setting up automation, they have experience with migration and upgrade. This talk will be an excellent case study, which I recommend to attend everyone who is considering implementing automation for a farm of MariaDB or MySQL servers.MariaDB Foundation

Next topic is “MariaDB Security Features and Best Practices” by Robert Bindar.  Robert is a server Developer at MariaDB Foundation. He will cover best security practices for MariaDB deployment, including the latest security features, added to version 10.4

At 4:15 pm we will have two MariaDB topics in parallel

MariaDB Foundation Bronze Sponsorship“MariaDB and MySQL – What Statistics Optimizer Needs Or When and How Not to Use Indexes” by Sergei Golubchik – a Member of the MariaDB Foundation Board – discovers optimization techniques which are often ignored in favor of indexes. Sergei worked on MySQL, and then on MariaDB, from their very first days. I’ve known him since 2006 when I joined the MySQL team. Each time when I am in trouble to find out how a particular piece of code works, just a couple of words from Sergei help to solve the issue! He has an encyclopedic knowledge on both MariaDB and MySQL databases. In this session, Sergei will explain which statistics optimizer we can use in addition to indexes. While he will focus on specific MariaDB features he will cover MySQL too. Spoiler: these are not only histograms!

Backups in the MySQL track…

In the parallel MySQL track, Iwo Panowicz and Juan Pablo Arruti will speak about backups in their “Percona XtraBackup vs. Mariabackup vs. MySQL Enterprise Backup” Iwo and Juan Pablo are Support Engineers at Percona. Iwo joined Percona two years ago, and now he is one of the most senior engineers in the EMEA team. Linux, PMM, analyzing core files, engineering best practices: Iwo is well equipped to answer all these and many more questions. Juan Pablo works in the American Support team for everything around MariaDB and MySQL: replication, backup, performance issues, data corruption… Through their support work, Iwo and Juan Pablo have had plenty of chances to find out strengths and weaknesses of different backup solutions.

Three tools, which they will cover in the talk, can be used to make a physical backup of MySQL and MariaDB databases, and this is the fastest and best recommended way to work with an actively used server. But what is the difference? When and why should you prefer one instrument over another? Iwo and Juan Pablo will answer these questions.

At the end of the day we will have two 25-minute sessions

Alibaba CloudJim Tommaney will present “Tips and Tricks with MariaDB ColumnStore”. Unlike Alex Rubin, who is an end user of ColumnStore databases, Jim is from another side: development. Thus his insights into MariaDB ColumnStore could be fascinating. If you are considering ColumnStore: this topic is a must-go!

Daniel Black will close the day with his talk “Squash That Old Bug”. This topic is the one I personally am looking forward to the most! Not only because I stick with bugs. But, well… the lists of accepted patches which Daniel’s posts to MariaDB and to MySQL servers are impressive. Especially when you know how strict is the quality control for external patches in MariaDB and MySQL! IBMIn his talk, Daniel is going to help you to start contributing yourself. And to do it successfully, so your patches are accepted. This session is very important for anyone who has asked themselves why one or another MariaDB or MySQL bug has not been fixed for a long time. I do not know a single user who has not asked that question!

MariaDB track at Percona Live 2019Conclusion

This blog about MariaDB track at Percona Live covers eight sessions, one keynote, one tutorial, 12 speakers, seven mini-committee members – two of whom are also speakers. We worked hard, and continue to work hard, to bring you great MariaDB program.

I cannot wait for the show to begin!

Photo by shannon VanDenHeuvel on Unsplash

Powered by WordPress | Theme: Aeros 2.0 by TheBuckmaker.com