Oct
02
2020
--

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. 

#Installation

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
     SUPPORT: YES
     COMMENT: Read only table stored in S3. Created by running ALTER TABLE table_name ENGINE=s3
TRANSACTIONS: NO
          XA: NO
  SAVEPOINTS: NO
1 row in set (0.000 sec)

#Implementation

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.

ColumnStore

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

Example:

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 ('::172.28.128.12');
Query OK, 1 row affected (0.002 sec)

MariaDB [jesus]> select * from inet6test;
+----+------------------------------+
| id | ipaddresses                  |
+----+------------------------------+
|  1 | 2001:db8:85b3::8a2e:370:7334 |
|  2 | ::172.28.128.12              |
+----+------------------------------+
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.

Example:

  • “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.

Example:

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).

And:

  • “REPLICATION CLIENT” is renamed to “BINLOG MONITOR”
  • “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.

  • INFORMATION_SCHEMA
  • PERFORMANCE_SCHEMA
  • 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.

Aug
14
2020
--

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.

Nov
01
2019
--

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=
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
owners.

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 ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED VIA unix_socket WITH GRANT OPTION |
| 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
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
owners.

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
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)

Success!

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
[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
owners.


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.

Conclusion

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.

Sep
13
2019
--

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.

Sep
13
2019
--

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.

May
07
2019
--

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

Mar
27
2019
--

Percona University Travels to South America

Percona University April 2019

Percona University April 2019We started hosting Percona University a few years back with the aim of sharing knowledge with the open source database community. The events are held in cities across the world. The next Percona University days will visiting Uruguay, Argentina, and Brazil, in a lightning tour at the end of April.

  • Montevideo, Tuesday, April 23 2019 from 8.30am to 6.30pm
  • Buenos Aires, Thursday, April 25 2019 from 1.30pm to 10.3pm
  • São Paulo, Saturday, April 27 2019 from 9.30am to 7.30pm

What is Percona University?

It is a technical educational event. We encourage people to join us at any point during these talks – we understand that not everyone can take off a half a day from their work or studies. As long as you register – that’s essential.

What is on the agenda for each of the events?

Full agendas and registration forms for the Montevideo, Buenos Aires, and São Paulo events can be accessed at their respective links.

Does the word “University” mean that we won’t cover any in-depth topics, and these events would only interest college/university students?

No, it doesn’t. We designed Percona University presentations for all kinds of “students,” including professionals with years of database industry experience. The word “University” means that this event series is about educating attendees on technical topics (it’s not a sales-oriented event, it’s about sharing knowledge with the community).

Does Percona University cover only Percona technology?

We will definitely mention Percona technology, but we will also focus on real-world technical issues and recommend solutions that work (regardless of whether Percona developed them).

Are there other Percona University events coming up besides these in South America?

We will hold more Percona University events in different locations in the future. Our newsletter is a good source of information about when and where they will occur. If you’d like to partner with Percona in organizing a Percona University event, contact Tom Basil who leads our community team… or Lorraine Pocklington our Community Manager. You can also check our list of technical webinars to get further educational insights.

These events are free and low-key! They aren’t meant to look like a full conference (like our Percona Live series). Percona University has a different format. We make it informal, so it’s designed to be perfect for learning and networking. This is an in-person database community gathering, so feel free to come with interesting cases and tricky questions!

Feb
12
2019
--

Debugging MariaDB Galera Cluster SST Problems – A Tale of a Funny Experience

MariaDB galera cluster starting time

MariaDB galera cluster starting timeRecently, I had to work on an emergency for a customer who was having a problem restarting a MariaDB Galera Cluster. After a failure in the cluster they decided to restart the cluster entirely following the right path: bootstrapping the first node, and then adding the rest of the members, one by one. Everything went fine until one of the nodes rejected the request to join the cluster.

Given this problem, the customer decided to ask us to help with the problematic node because none of the tests they did worked, and the same symptom repeated over and over: SST started, copied few gigs of data and then it just hanged (apparently) while the node remained out of the cluster.

Identifying the issue…

Once onboard with the issue, initially I just checked that the cluster was trying a SST: given the whole dataset was about 31GB I decided to go directly to a healthy solution: to clean up the whole datadir and start afresh. No luck at all, the symptom was exactly the same no matter what I tried:

After reviewing the logs I noticed few strange things. In the joiner:

2019-01-29 16:14:41 139996474869504 [Warning] WSREP: Failed to prepare for incremental state transfer: Local state UUID (00000000-0000-0000-0000-000000000000) does not match group state UUID (18153472-f958-11e8-ba63-fae8ac6c22f8): 1 (Operation not permitted)
at galera/src/replicator_str.cpp:prepare_for_IST():482. IST will be unavailable.
2019-01-29 16:14:41 139996262553344 [Note] WSREP: Member 3.0 (node1) requested state transfer from '*any*'. Selected 0.0 (node3)(SYNCED) as donor.
2019-01-29 16:14:41 139996262553344 [Note] WSREP: Shifting PRIMARY -> JOINER (TO: 4902465)
2019-01-29 16:14:41 139996474869504 [Note] WSREP: Requesting state transfer: success, donor: 0
2019-01-29 16:14:41 139996474869504 [Note] WSREP: GCache history reset: 00000000-0000-0000-0000-000000000000:0 -> 18153472-f958-11e8-ba63-fae8ac6c22f8:4902465
2019-01-29 16:14:42 139996270946048 [Note] WSREP: (9864c6ca, 'tcp://0.0.0.0:4567') connection to peer 9864c6ca with addr tcp://192.168.12.21:4567 timed out, no messages seen in PT3S
2019-01-29 16:14:42 139996270946048 [Note] WSREP: (9864c6ca, 'tcp://0.0.0.0:4567') turning message relay requesting off
2019-01-29 16:16:08 139996254160640 [ERROR] WSREP: Process was aborted.
2019-01-29 16:16:08 139996254160640 [ERROR] WSREP: Process completed with error: wsrep_sst_xtrabackup-v2 --role 'joiner' --address '192.168.12.21' --datadir '/var/lib/mysql/' --parent '8725' --binlog '/var/log/mysql/mariadb-bin' --binlog-index '/var/log/mysql/mariadb-bin.index': 2 (No such file or directory)

In the donor (output has been a obfuscated to avoid sharing private info and the times are not matching deliberately):

Jan 29 18:08:22 node3 -innobackupex-backup: 190129 18:08:22 >> log scanned up to (203524317205)
Jan 29 18:08:23 node3 -innobackupex-backup: 190129 18:08:23 >> log scanned up to (203524318337)
Jan 29 18:08:24 node3 -innobackupex-backup: 190129 18:08:24 >> log scanned up to (203524320436)
Jan 29 18:08:25 node3 -innobackupex-backup: 190129 18:08:25 >> log scanned up to (203524322720)
Jan 29 18:08:25 node3 nrpe[25546]: Error: Request packet type/version was invalid!
Jan 29 18:08:25 node3 nrpe[25546]: Client request was invalid, bailing out...
Jan 29 18:08:26 node3 -innobackupex-backup: 190129 18:08:26 >> log scanned up to (203524322720)
Jan 29 18:08:27 node3 -innobackupex-backup: 190129 18:08:27 >> log scanned up to (203524323538)
Jan 29 18:08:28 node3 -innobackupex-backup: 190129 18:08:28 >> log scanned up to (203524324667)
Jan 29 18:08:29 node3 -innobackupex-backup: 190129 18:08:29 >> log scanned up to (203524325358)
Jan 29 18:08:30 node3 -wsrep-sst-donor: 2019/01/29 18:08:30 socat[22843] E write(6, 0x1579220, 8192): Broken pipe
Jan 29 18:08:30 node3 -innobackupex-backup: innobackupex: Error writing file 'UNOPENED' (Errcode: 32 - Broken pipe)
Jan 29 18:08:30 node3 -innobackupex-backup: xb_stream_write_data() failed.
Jan 29 18:08:30 node3 -innobackupex-backup: innobackupex: Error writing file 'UNOPENED' (Errcode: 32 - Broken pipe)
Jan 29 18:08:30 node3 -innobackupex-backup: [01] xtrabackup: Error: xtrabackup_copy_datafile() failed.
Jan 29 18:08:30 node3 -innobackupex-backup: [01] xtrabackup: Error: failed to copy datafile.
Jan 29 18:08:30 node3 mysqld[27345]: 2019-01-29 18:08:30 140562136139520 [Warning] Aborted connection 422963 to db: 'unconnected' user: 'sstuser' host: 'localhost' (Got an error reading communication packets)
Jan 29 18:08:30 node3 -wsrep-sst-donor: innobackupex finished with error: 1. Check /var/lib/mysql//innobackup.backup.log
Jan 29 18:08:30 node3 -wsrep-sst-donor: Cleanup after exit with status:22

So SST starts correctly and then failed. I tried forcing different donors, checked firewall rules, etc. Nothing.

Additionally I noticed that the process was starting over and over, while monitoring,  .ssh folder was growing up to certain size (something around 7GB) and then would start over. The logs kept showing the same messages, the init script failed with an error but the process kept running either until I executed service mysql stop or kill -9 to all processes. It was getting stranger every minute.

At this point I was totally lost, scratching my head looking for solutions. More strange still was that trying a manual SST using netcat worked perfectly! So it was definitely a problem with the init script. Systemd journal was not providing any insight…

And then…

MariaDB Cluster dies in the SST process after 90 seconds

Suddenly I noticed that the failure was happening roughly 90 seconds after the start. A short googling later—doing more specific search—I found this page:
https://mariadb.com/kb/en/library/systemd/#ssts-and-systemd which explained precisely my problem.

The MariaDB init script has changed its timeout from 900 seconds to 90 while MySQL Community and Percona Server has this value set to 15 mins. Also it seems that this change has caused some major issues with nodes crashing as documented in MDEV-15607 — the bug is reported as fixed but we still can see timeout problems.

I observed that in case of failure, systemd was killing the mysqld process but not stopping the service. This results in an infinite SST loop that only stops when the service is killed or stopped via systemd command.

The fix was super easy, I just needed to create a file to be used by systemd that sets the timeout to a more useful value as follows:

sudo tee /etc/systemd/system/mariadb.service.d/timeoutstartsec.conf <<EOF
[Service]
TimeoutStartSec=900
EOF
sudo systemctl daemon-reload

As you may notice I set the timeout to 15 minutes but I could set it to any time. That was it, the next SST will have plenty of time to finish. Reference to this change is very well documented here

On reflection…

One could argue about this change, and I’m still having some internal discussions about it. In my opinion, a 90 seconds timeout is too short for a Galera cluster. It is very likely that almost any cluster will reach that timeout during SST. Even a regular MySQL server that suffers a crash with a high proportion of dirty pages or many operations to rollback, 90 seconds doesn’t seem to be an feasible time for crash recovery. Why the developers changed it to such a short timeout I have no idea. Luckily, it is very easy to fix now I know the reason.


Photo by Tim Gouw on Unsplash

Jan
31
2019
--

A New PMM Dashboard to Monitor Memory Usage!

Dashboard to Monitor Memory Usage in Linux

While the PMM team works hard on our PMM 2.0 release, we have been working on a few things in the background which we’d like to show off !  In particular we have developed a new dashboard that displays metrics related to memory usage on Linux systems. The dashboard leverages information collected by node_exporter. The graphs take advantage of  /proc filesystem files, specifically:

  • meminfo: Provides information about distribution and utilization of memory. This varies by architecture and compile options.
  • vmstat: Provides information about block IO and CPU activity in addition to memory.

The information is split into five sections:

  1. Total Memory
  2. VMM (Virtual Memory Manager) Statistics
  3. Memory Statistics
  4. Number and Dynamic of Pages
  5. Pages per Zone

The dashboard will be included as part of the PMM 2.0 release. For you early adopters, you can get it from GrafanaLab and install it alongside your existing Dashboards – it won’t overwrite anything!

Please notice that the dashboard can be imported by ID (9692) in Grafana versions since 5.4.2 or should be downloaded and imported manually in older Grafana versions.

Jan
18
2019
--

Replication Manager Works with MariaDB

Complex multi-cluster replication topology

Some time ago I wrote a script to manage asynchronous replication links between Percona XtraDB clusters. The original post can be found here. The script worked well with Percona XtraDB Cluster but it wasn’t working well with MariaDB®.  Finally, the replication manager works with MariaDB.

First, let’s review the purpose of the script. Managing replication links between Galera based clusters is a tedious task. There are many potential slaves and many potential masters. Furthermore, each replication link must have only a single slave. Just try to imagine how you would maintain the following replication topology:

A complex replication topology

The above topology consists of five clusters and four master-to-master links. The replication manager can easily handle this topology. Of course, it is not a fix to the limitations of asynchronous replication. You must make sure your writes are replication safe. You could want, for example, a global user list or to centralize some access logs. Just to refresh memories, here are some of the script highlights:

  • Uses the Galera cluster for Quorum
  • Configurable, arbitrarily complex topologies
  • The script stores the topology in database tables
  • Elects slaves automatically
  • Monitors replication links
  • Slaves can connect to a list of potential masters

As you probably know, MariaDB has a different GTID implementation and syntax for the multi-source replication commands. I took some time to investigate why the script was failing and fixed it. Now, provided you are using MariaDB 10.1.4+ with GTIDs, the replication manager works fine.

You can found the script here. Be aware that although I work for Percona, the script is not officially supported by Percona.

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