Skip to content

The CONNECT Storage engine implement the concept of a table made of multiple tables. These underlying tables can be distributed remotely. For example the underlying remote tables can be of ODBC or MySQL table type. this allows to execute distributed queries. What is nice is that we can execute this distributed query with parallelism.

How does it work ?

To explain it let us suppose we have 4 nodes : Node0 and Node1 Node2 Node3
Node0 has a MariaDB 10.0.4 installed with the CONNECT storage engine activated.

MariaDB [dbt3]> install plugin connect  soname 'ha_connect.so';

On the 3 other nodes we have MariaDB or plain MySQL installed.
the 'lineitem' table of the dbt3 benchmark has been created and loaded with one chunk of the 'lineitem' table on each node. This is a sharded table. For example to generate the second chunk of 3 chunks 'lineitem' table:

./dbgen -T L -fF -q -b dists.dss -s 1 -C 3 -S 2

Now on node0 we can create a CONNECT table. This table definition embed a SRCDEF parameter that force the computation of an aggregate on the remote server :

create Table lineitem1   ENGINE=CONNECT TABLE_TYPE=MYSQL
SRCDEF='select l_suppkey, sum(l_quantity) qt from dbt3.lineitem3 group by l_suppkey'
connection='mysql://proxy:manager1@node1:3306/dbt3/lineitem3';

if we do a select on this table on node0 we get the result data that has been aggregated on node1. We access only one shard of the data.

MariaDB [dbt3]> select * from lineitem1;
+-----------+------+
| l_suppkey | qt   |
+-----------+------+
|         1 | 2302 |
|         2 | 1960 |
...
|      9999 | 1908 |
|      1000 | 1756 |
+-----------+------+
10000 rows in set (26.82 sec)

Let us now do the same on for node2 and node3 :

create Table lineitem2   ENGINE=CONNECT TABLE_TYPE=MYSQL
SRCDEF='select l_suppkey, sum(l_quantity) qt from dbt3.lineitem3 group by l_suppkey'
connection='mysql://proxy:manager1@node2:3306/dbt3/lineitem3';
 
create Table lineitem3   ENGINE=CONNECT TABLE_TYPE=MYSQL
SRCDEF='select l_suppkey, sum(l_quantity) qt from dbt3.lineitem3 group by l_suppkey'
connection='mysql://proxy:manager1@node3:3306/dbt3/lineitem3';

Now we can use the ability of the the CONNECT storage engine to build a table that allows to see the 3 shards as a single table :

create Table alllineitem   (`l_suppkey` INT(4) NOT NULL,`qt` DOUBLE ) ENGINE=CONNECT
TABLE_TYPE=TBL
table_list='lineitem1,lineitem2,lineitem3';

We can now query this table. All the aggregation computation will be done on the remote nodes and not on node0:

MariaDB [dbt3]> select * from alllineitem;
+-----------+------+
| l_suppkey | qt   |
+-----------+------+
|         1 | 2302 |
|         2 | 1960 |
...
|      9998 | 2395 |
|      9999 | 1908 |
|      1000 | 1756 |
+-----------+------+
30000 rows in set (1 min 19.23 sec)

This basically takes 3 times longer than querying a single shard. This is because the request is executed sequentially on node 1 2 and 3.

We can request the CONNECT storage engine to do the job in parallel by adding the extra option : option_list='thread=1';

create Table alllineitem2   (`l_suppkey` INT(4) NOT NULL,`qt` DOUBLE ) ENGINE=CONNECT
TABLE_TYPE=TBL
table_list='lineitem1,lineitem2,lineitem3' option_list='thread=1';

If we rerun the query we get

MariaDB [dbt3]> select * from alllineitem2;
+-----------+------+
| l_suppkey | qt   |
+-----------+------+
|         1 | 2302 |
|         2 | 1960 |
...
|      9999 | 1908 |
|      1000 | 1756 |
+-----------+------+
30000 rows in set (26.69 sec)

We go back to the same execution time as with a single shard ! Is not that cool. I am sure you have a lot of objections, ideas about this approach. I do. But it is nice anyways.

If you want to know more do not forget to attend to the MariaDB CONNECT Storage Engine webinar :
November 7, 2013 - 5 pm CET / 4 PM UTC / 8 AM PST Register

and if you want to give it a try you are welcome and the doc to help is here:
MariaDB Connect Storage Engine documentation
Your help is welcome : bug reports, documentation fixes, usage feedback, suggestions.
Be indulgent it is still Alpha software 😉

This week I will have the opportunity to deliver a webinar on the MariaDB CONNECT Storage Engine.
The MariaDB CONNECT Storage Engine allows to access various file formats (CSV, XML, Excel, etc). It give access to any ODBC data sources (Oracle, DB2, SQLServer, SQLite etc). It also allows to access remote MySQL tables. A CONNECT table itself can be a set of remote MySQL tables. This opens the door to interesting distributed architectures that can help to address big data.
This webinar is a technical overview of the MariaDB CONNECT Storage Engine and it will show you typical use cases to help you get benefits from your existing data sources.
We will see how to use the MariaDB CONNECT Storage Engine.

Register for this webinar to learn what benefits you can get from the MariaDB CONNECT Storage Engine :
November 7, 2013 - 5 pm CET / 4 PM UTC / 8 AM PST Register

MySQL Fabric is a very promising sharding framework. If I take Ulf Wendel definition of MySQL Fabric :

MySQL Fabric is an administration tool to build large “farms” of MySQL servers. In its most basic form, a farm is a collection of MySQL Replication clusters. In its most advanced form, a farm is a collection of MySQL Replication clusters with sharding on top.

So MySQL Fabric takes care of two very orthogonal features :

  • High availability of servers
  • Sharding of data

Let us forget about sharding and look at the High availability infrastructure.

Servers are included in groups, called "High Availability Groups" when we talk about HA.
Each Server has an associated Status (or Role): primary secondary, spare
Each Server has also a mode : Offline, Read-only, and Read-Write.
The implementation has been made to allow various HA implementation patterns.
The most common HA pattern is the Master/Slave HA group ( in that case we should call it a "replica set" which is the terminology used in MongoDB or Facebook MySQL Pool Scanner (MPS).

Mats Kindahl in his blog post on MySQL Fabric High Availability Groups mentioned that other HA solutions are possible for an availability group :

  • Shared Storage with SAN or NAS
  • Replicated storage like DRBD
  • MySQL Cluster shared nothing cluster

In the case of a HA group based on MySQL Cluster the group is self-managing regarding HA and MySQL Fabric does not handles the failover. With the "Shared Storage" and "Replicated storage" availability groups the secondary servers will be offline.

So one of my ideas that I hope is feasible would be to use MariaDB Galera Cluster as another HA solution with MySQL Fabric. The main advantage of this solution relates to the characteristics of MariaDB Galera Cluster. MariaDB Galera Cluster is an Active-active multi-master topology with synchronous replication. MariaDB Galera Cluster being innoDB based does not carry all the usage limitations associated with MySQL Cluster (main one being limited join capabilities).

Regarding to MySQL fabric the behavior of an availability group based on MariaDB Galera Cluster is identical to MySQL Cluster. It is a self-managing availability group.

MariaDB Galera Cluster

MariaDB Galera Cluster
Getting Started with MariaDB Galera Cluster

MySQL Fabric

MySQL Fabric: A new kid in the MySQL sharding world  2013-10-09 Serge Frezefond
MySQL Fabric: High Availability Groups  2013-10-21 Mats Kindahl
A Brief Introduction to MySQL Fabric  2013-09-21 Mats Kindahl
MySQL Fabric - Sharding - Introduction  2013-09-21 VN (Narayanan Venkateswaran)
MySQL Fabric - Sharding - Simple Example  2013-09-22 VN (Narayanan Venkateswaran)
MySQL Fabric - Sharding - Shard Maintenance  2013-09-27 VN
MySQL Fabric - Sharding - Migrating From an Unsharded to a Sharded Setup  2013-09-22 VN
Installing MySQL Fabric on Windows  2013-10-03 Todd Farmer
MySQL 5.7 Fabric: any good?  2013-09-23 Ulf Wendel

Writing a Fault-tolerant Database Application using MySQL Fabric  2013-09-21 Alfranio Junior
Sharding PHP with MySQL Fabric  2013-10-09 Johannes Schlüter
MySQL Fabric support in Connector/Python  2013-09-22 Geert Vanderkelen
MySQL Connector/J with Fabric Support  2013-09-21 Jess Balint

Like TokuDB, InfiniDB is now a fully open source server product. In the past infiniDB was "almost open source". The open source version was an old release with no access to the advance functions like MPP multi-server execution. This is no more the case. With InfiniDB 4 the open source version is the latest release giving access to all the advanced functionalities.

This is a really great move for the MariaDB / MySQL ecosystem. InfiniDB and TokuDB were two unique pieces of technology in the MySQL ecosystem. Having them both open source will trigger a broader adoption that will benefit to their enterprise releases.

Having in the MySQL ecosystem a column oriented database specifically designed for big data analytics is filling a real customer need. Column stores overcome the query limitations that exist in traditional RDBMS. InfiniDB is extremely good at using multicore server and massively parallel processing with multi-servers. InfiniDB can scale up on multi-cores server and scale out on a distributed architecture.

So thanks a lot to the InfiniDB team for their move. This will greatly benefit to them and to the dynamism of the MySQL / MariaDB ecosystem.

InfiniDB community web site
InfiniDB 4 sources
InfiniDB Enterprise web site

MySQL Connect 2013 has been a great edition. There was of course a lot of nice announcements of improvements in the the core MySQL server technology. One of the major announcement that received a lot of buzz was MySQL Fabric. MySQL Fabric is an infrastructure component aimed at simplifying construction of a highly available, sharded, MySQL server based architecture.

Horizontal scale out for MySQL is a hard problem. The MySQL sharding requirement has up till now only be addressed in a non general solution way. Using MySQL at Scale remains a big challenge.

All the big actors of the web have been faced with this scale out issue. They all have developed their own tools/framework to address this need. The new cloud providers have also been faced with this requirement when trying to offer database as a service solutions around MySQL with transparent elasticity.

We can mention the following Sharding solutions davelopped by the key players of the web :

  • Google/Youtube has built Vitess (in Go) and open sourced it
  • Twitter has built Gizzard (in Scala) and open sourced it
  • Tumblr has built Jetpants (in Ruby) and open sourced it
  • Facebook has also it own sharding framework which is a range based model. The HA is addressed with MHA. they have presented their architecture at various events

Theses frameworks cover the needs:

  • Master promotions,
  • Cloning slaves,
  • Supports a range-based sharding scheme for MySQL
  • Rebalancing shards,
  • Split a range-based shard into N new shards

High Availability is also addressed by most of these frameworks. MySQL Fabric also handles HA. All the improvements made around replication have helped handle correctly the HA part of the architecture in a more resilient way.

So now with the introduction of MySQL Fabric there is new sharding framework available. So what ? Interesting ?
Yes it is very interesting.

First, MySQL fabric has been written in python which in my opinion is a good point as devops love python.
Second, MySQL fabric has been released under the GPL license which is also a very good point.
Last MySQL Fabric is aimed as a general purpose sharding framework.

 

MySQL fabric is quite simple :

  • A Fabric Server holding and serving the sharding metadata.
  • A set of commands to do various action (split shard ,…)
  • Specific connectors : Java, Python to request shard location to the Fabric server through XMLRPC call.

Two questions for me:

  • What is the status of the PHP and C drivers regarding MySQL Fabric ?
  • Is it possible to fully abstract the client code from the fact that the data is sharded. Some connector config could specify the fabric server. The client code would then not be changed at all.

======

A Brief Introduction to MySQL Fabric  2013-09-21 Mats Kindahl
MySQL Fabric - Sharding - Introduction  2013-09-21 VN (Narayanan Venkateswaran)
MySQL Fabric - Sharding - Simple Example  2013-09-22 VN (Narayanan Venkateswaran)
MySQL Fabric - Sharding - Shard Maintenance  2013-09-27 VN (Narayanan Venkateswaran)
MySQL Fabric - Sharding - Migrating From an Unsharded to a Sharded Setup  2013-09-22 VN (Narayanan Venkateswaran)
Installing MySQL Fabric on Windows  2013-10-03 Todd Farmer
MySQL 5.7 Fabric: any good?  2013-09-23 Ulf Wendel
---
Writing a Fault-tolerant Database Application using MySQL Fabric  2013-09-21 Alfranio Junior
MySQL Fabric support in Connector/Python  2013-09-22 Geert Vanderkelen
MySQL Connector/J with Fabric Support  2013-09-21 Jess Balint

Syslog is widely used for logging. It allows distributed logging. Having MySQL/MariaDB audit data logged to a remote Syslog server is a strong guaranty regarding security of the audit data. PCI compliance requires separation of duties. The separation of duties between DBA profiles and a security officer is a way to guaranty that Audit data is tamper-proof from the DBA.

To set up the MariaDB Audit Plugin to log to remotely syslog is quite simple. First you install the MariaDB Audit Pluggin : You download the MariaDB audit plugin, you copy it to lib/plugin in your MySQL/MariaDB install directory and you activate it :

MariaDB [(none)]> INSTALL PLUGIN server_audit SONAME 'server_audit.so';
MariaDB [test]> SET GLOBAL server_audit_output_type=SYSLOG;
MariaDB [test]> SET GLOBAL server_audit_events='CONNECT,QUERY ';
MariaDB [test]> SET GLOBAL server_audit_logging=on;

To have the audit logging data sent to a remote server you first need to configure the remote syslog server to accept request from the network(here on port 514) by editing /etc/rsyslog.conf

# Provides UDP syslog reception
$ModLoad imudp
$UDPServerRun 514
 
# Provides TCP syslog reception
$ModLoad imtcp
$InputTCPServerRun 514

Restart the syslog daemon :

service syslog restart

To check that your syslog system accept data from a remote source we verify that it is listening on the configured 514 port :

[root@centos2 etc]# netstat -anp|grep 514
tcp        0      0 0.0.0.0:514                 0.0.0.0:*                   LISTEN      11467/rsyslogd      
tcp        0      0 :::514                      :::*                        LISTEN      11467/rsyslogd      
udp        0      0 0.0.0.0:514                 0.0.0.0:*                               11467/rsyslogd      
udp        0      0 :::514                      :::*                                    11467/rsyslogd

On the source server where your MariaDB / MySQL server produce audit entries you should configure syslog to push log entries to the remote system here 192.168.56.11. You edit /etc/rsyslog.conf that way:

*.info;mail.none;authpriv.none;cron.none                @192.168.56.11

And on the target system you now get the audit records tagged withe the originating system:

Sep 21 00:52:37 centos1 kernel: imklog 5.8.10, log source = /proc/kmsg started.
Sep 21 00:52:37 centos1 rsyslogd: [origin software="rsyslogd" swVersion="5.8.10" x-pid="1647" x-info="http://www.rsyslog.com"] start
Sep 21 00:52:59 centos1 mysql-server_auditing:  centos1.localdomain,root,localhost,1,19,QUERY,test,'show tables',0
Sep 21 00:53:14 centos1 mysql-server_auditing:  centos1.localdomain,root,localhost,1,20,QUERY,test,'show tables',0

So this is quite simple to setup. Of course you can have multiple MariaDB/MySQL servers sending audit data to a single syslog server.

Syslog is widely used for logging. It allows separation of the storage of generated messages from the system that generated them. A lot of solution exists to aggregate, monitor, query, analyze syslog data. Syslog separation of data from the source is a key element for security auditing as it make it more difficult to repudiate or wipe out logging data. Syslog data can be consolidated in a central repository with make it easier to correlate various events. Consolidated logs can go through reporting, analytics or artificial intelligence algorithms to detect patterns and alert customers of problems.

To set up the MariaDB Audit Plugin to logging to syslog is quite simple :

You download the MariaDB audit plugin (a single share library file) from :
http://www.skysql.com/downloads/mariadb-audit-plugin-beta
You copy the server_audit.so file to the lib/plugin directory in your MySQL/MariaDB install directory and activate it through :

MariaDB [(none)]> INSTALL PLUGIN server_audit SONAME 'server_audit.so';

By default login is done to file and you need to switch it to syslog :

MariaDB [test]> SET GLOBAL server_audit_output_type=SYSLOG;
MariaDB [test]> SET GLOBAL server_audit_events='CONNECT,QUERY';
MariaDB [test]> SET GLOBAL server_audit_logging=on;

You can then see all the variable allowing to configure it :

MariaDB [test]> show variables like '%audit%';
+-------------------------------------+-----------------------+
| Variable_name                       | Value                 |
+-------------------------------------+-----------------------+
| server_audit_events                 | CONNECT,QUERY         |
| server_audit_excl_users |           |                       |
| server_audit_file_path              | server_audit.log      |
| server_audit_file_rotate_now        | OFF                   |
| server_audit_file_rotate_size       | 1000000               |
| server_audit_file_rotations         | 9                     |
| server_audit_incl_users             |                       |
| server_audit_logging                | ON                    |
| server_audit_mode                   | 0                     |
| server_audit_output_type            | syslog                |
| server_audit_syslog_facility        | LOG_USER              |
| server_audit_syslog_ident           | mysql-server_auditing |
| server_audit_syslog_info            |                       |
| server_audit_syslog_priority        | LOG_INFO              |
+-------------------------------------+-----------------------+
14 rows in set (0.00 sec)

you get a few status values:

MariaDB [test]> show status like '%audit%';
+------------------------------ +--------------+
| Variable_name                 | Value        |
+----------------------------- -+--------------+
| server_audit_active           | ON           |
| server_audit_current_log      | [SYSLOG]     |
| server_audit_last_error       |              |
| server_audit_writes_failed    | 0            |
+-------------------------------+--------------+
4 rows in set (0.00 sec)

Check that rsyslog is running :

[root@centos1 log]# service rsyslog restart
Shutting down system logger: [ OK ]
Starting system logger: [ OK ]

Now all connect and query MariaDB actions are written to the audit log in syslog.

[root@centos1 log]# tail -f /var/log/messages
Sep 21 00:07:07 centos1 mysql-server_auditing: centos1.localdomain,root,localhost,1,10,QUERY,,'set global server_audit_logging=on',0
Sep 21 00:07:11 centos1 mysql-server_auditing: centos1.localdomain,root,localhost,1,11,QUERY,,'show status like \'%audit%\'',0
Sep 21 00:07:21 centos1 mysql-server_auditing: centos1.localdomain,root,localhost,1,12,QUERY,,'show variables like \'%audit%\'',0
Sep 21 00:10:06 centos1 mysql-server_auditing: centos1.localdomain,root,localhost,1,13,QUERY,,'set global server_audit_events=\'CONNECT,QUERY\'',0
Sep 21 00:13:09 centos1 mysql-server_auditing: centos1.localdomain,root,localhost,1,14,QUERY,,'SELECT DATABASE()',0
Sep 21 00:13:09 centos1 mysql-server_auditing: centos1.localdomain,root,localhost,1,16,QUERY,test,'show databases',0
Sep 21 00:13:09 centos1 mysql-server_auditing: centos1.localdomain,root,localhost,1,17,QUERY,test,'show tables',0
Sep 21 00:13:14 centos1 mysql-server_auditing: centos1.localdomain,root,localhost,1,18,QUERY,test,'show tables',0

The fact that the log entries goes to /var/log/messages comes from the /etc/rsyslog.conf config file line

*.info;mail.none;authpriv.none;cron.none /var/log/messages

To log to a different file you just add a line to the /etc/rsyslog.conf config file

if $programname == 'mysql-server_auditing' then /var/log/mariadbaudit1

Using MariaDB Audit Plugin with the syslog system is a good choice for security and sys admins.

 

By going to the download section of  SkySQL website  some users have noticed "MariaDB Audit Plugin". This auditing feature for MySQL has been requested by more and more customers. Legal constraints make it mandatory for more and more companies to keep logging information about database access and activity.

It is very important for the MySQL community to have an open source audit plugin available. MariaDB team has always stick to the principle of keeping MySQL 100% open source and has developed the MariaDB Audit Plugin according to these principles. The MariaDB Audit Plugin has been developed using the standard MySQL Audit Plugin API. Being based on standard API makes it run both with MariaDB and with Oracle MySQL Server. The MariaDB Audit Plugin also has some unique features only available for MariaDB.

To develop this plugin we have sticked to the principle of listening to our big customer to define the specifications. This is the first version and some more improvements will come. We will be pleased to hear your feedbacks. All bug reports and critics are welcomed. The MariaDB Audit Plugin already covers the main requests that have been expressed. But we have more to come and your improvement requests are welcomed.

The purpose of the MariaDB Audit Plugin is to log the server's activity. Who connected to the server, what queries ran and what tables were touched is stored to a rotating log file. The MariaDB Audit Plugin also supports output to the widely used syslogd system. It offers the option to include/exclude users from auditing.

Oracle has released an audit plugin available through its MySQL Enterprise Server version. This make it mandatory to have an active subscription. The version you run is then a fully closed source server under a commercial license. In that case you cannot rely on third party support like SkySQL or Percona.

The MariaDB Audit Plugin is delivered as a very light shared library that can be very easily downloaded and installed on any MariaDB/MySQL server.

Where to download the MariaDB Audit Plugin ?
http://www.skysql.com/downloads/mariadb-audit-plugin-beta

Source Code of the MariaDB Audit Plugin :
https://code.launchpad.net/~maria-captains/maria/5.5-noga-hf

Pointers :
MariaDB Audit Plugin Getting started guide
MariaDB Audit Plugin documentation

My blog post on other auditing techniques
Oracle MySQL Audit Plugin
Oracle MySQL Audit Plugin doc
Macfee Audit Plugin
Macfee Audit Plugin doc

 

Engine condition pushdown is a MySQL internal mechanism that is intended to avoid to send non matching rows from the storage engine to the SQL layer. This is very important when the storage engine involves traffic over the network.
This mechanism was initially created to optimize MySQL Cluster (NDB) behavior.
For the NDB storage engine it avoids a lot of round trips through the network. Part of the filtering is done on the remote data node. Without engine condition pushed down the filtering is done by the sql layer after rows have been sent by the storage engine.

The MariaDB CONNECT Storage Engine implement various table type. Some of these table type correspond to remote access. This is the case of the ODBC and MySQL table type. These types can be considered as a more powerful evolution of the federated storage Engine.
In both case the engine condition pushdown gives a great benefit by avoiding network roundtrips.

The conditions are trapped by the storage engine handler through this API implementation:
const COND *ha_connect::cond_push(const COND *cond)

How to use engine condition pushdown with the MariaDB Connect Storage Engine.

The "engine condition pushdown" mechanism should not be confused with the "index condition pushdown" optimization introduced in MariaDB 5.5 and MySQL 5.6. The index condition pushdown was introduced to use as much as possible the index to filter rows.

As Stewart mentioned in in post where-are-they-now-mysql-storage-engines : "Federated It’s still there… but is effectively unmaintained and dead. There’s even FederatedX in MariaDB which is an improvement, but still, the MySQL server really doesn’t lend itself kindly to this type of engine… it’s always been an oddity only suitable for very specific tasks."

The CONNECT storage engine is lifting the FEDERATED(X) limitations. It is no more a MySQL table only stuff.
It gives the MySQL access to local or remote MySQL table : in that case you can use a subset of columns, reorder them and have the limit clause correctly applied directly on the target.
But you now also have the capability to do an ODBC access to a data source.
This data source can be a local data source accessed through a DSN : Excel, Access, firebird . in that case the multiple ODBC tables concept apply. The concept of multiple table allows ODBC tables that are physically represented by multiple files. For instance to Excel or Access tables you ca views a set of monthly files as a single table.
This can also be any remote data source for which there is an ODBC driver : ORACLE, SQL Server, DB2 ...

What is nice is that these MySQL or ODBC type CONNECT tables can be mixed with the CONNECT TBL Type (Table List) table. This allow to execute queries distributed on multiple remote or local servers.
This is a very powerfull extension of the concept of MERGE table. Table can be of any type. Subset of columns can be used.