Feed Aggregator

Table of Contents

• Hardware requirements
• O/S (Linux) settings
• Installation
• Configuration
• Cross Engine Join User
• Start ColumnStore
• Create the Cross Engine Join User in the database
• MariaDB ColumnStore schemas
• Server System Variables and Server Status Variables
• MariaDB ColumnStore on the file system
• Creating our first table
• Querying and changing data in MariaDB ColumnStore
• MariaDB ColumnStore using sequences instead of AUTO_INCREMENT
• MariaDB ColumnStore Query Execution Plan

For a long time I wanted to investigate a bit more into the MariaDB ColumnStore architecture. Now I took the time to start with.

MariaDB ColumnStore is an interesting technology if you want to do reports (OLAP, data warehouse (DWH), BI) over only a few columns on a huge amount of rows and if you have only or mostly batch writes. These data are ideally spread (sharded) over several machines and the results where calculated on those different machines an aggregated. Because the data are stored in columns the locality of your data should be …

FromDual has the pleasure to announce the release of the new version 1.1.0 of its popular FromDual Ops Center focmm, a Graphical User Interface (GUI) for MariaDB, MySQL and compatible databases.

The FromDual Ops Center for MariaDB and MySQL (focmm) helps DBA’s and System Administrators to better manage their MariaDB, MySQL and compatible databases farms. Ops Center makes DBA and Admins life easier!

The main task of Ops Center is to support you in your daily MariaDB, MySQL and compatible databases operation tasks. More information about FromDual Ops Center you can find here.

Download

The new FromDual Ops Center for MariaDB, MySQL and compatible databases (focmm) can be downloaded from here. How to install and use focmm is documented in the Ops Center User Guide.

In the inconceivable case that you find a bug in the FromDual Ops Center for MariaDB, MySQL and compatible databases please report it to the FromDual bug tracker or just send us an [email](mailto:contact@fromdual.com?Subject=Bug report for …

InnoDB NUMA interleave with MariaDB also read here.

Today a customer gave me the idea of using a separate file as a volume for limiting the MySQL tmpdir size. On some not so well set-up Linux systems the MySQL tmpdir is located under /tmp which is the same mount point as / (root). This can lead to troubles in case the tmpdir is filled up with implicit temporary MyISAM tables which fills up the / (root) directory of the O/S as well.

MySQL itself has no possibility to limit explicitly the total size nor the number of implicit temporary tables. So this can happen easily if your application runs amok or you do not have your application under control.

An sometimes there is no possibility to have an extra mount point for tmpdir because the disk is completely used by volumes etc. But you have still some space in the file system.

In this case you can, similar to a swap file, use a file in the file system as volume and mount it in a way you can use it as an separate mount point for your tmpdir directory. So in case your application runs amok it just fills up your …

MariaDB Enterprise Platform

The MariaDB Enterprise Platform is available on-premises and on the MariaDB SkySQL DBaaS (Cloud database platform). MariaDB Enterprise Platform consists of:

MariaDB Enterprise Server (MariaDB database server)

MariaDB Xpand (distributed database, scaling writes, former ClustrixDB)

MariaDB Enterprise ColumnStore (former InfiniDB used for data warehouse (DWH), BI, Reporting)

MariaDB MaxScale (Load Balancer)

MariaDB Connectors

• MariaDB Connector/C++
• MariaDB Connector/R2DBC (Reactive Relational Database Connectivity, Java?)
• MariaDB Connector/ODBC
• MariaDB Connector/Python
• MariaDB Connector/Node.js

MariaDB integration connectors

Kafka

Spark

Pentaho Kettle

Power BI

MariaDB tools

IDERA SQL Diagnostic Manager

SQLyog Ultimate

MariaDB Support

MariaDB Enterprise Server Features

MariaDB Enterprise Server comes with more features than MariaDB Community Server:

• Enterprise Cluster
• Enterprise Audit
• Enterprise Federation
• Xpand Storage Engine
• Hashicorp Vault
• InnoDB enhancements
• Replication …

Because they do NOT reflect your problems.

One of our customers upgraded last month from MariaDB 10.2 to MariaDB 10.5. In the same change he also converted all his data warehouse (DWH)/BI tables from MyISAM to Aria. An all this, naturally, without testing. And it miserably failed! And then we were under heavy time pressure to make things working again…

What has changed:

• MariaDB version: MariaDB optimizer got a lot of changes between these 4 major release series (10.2, 10.3, 10.4 and 10.5)!
• Storage Engine change from MyISAM to Aria.
• MariaDB Server System Variable aria_pagecache_buffer_size was not tested and sized properly. In combination with a MariaDB documentation bug.
• A newly introduced MariaDB bug (MDEV-25308)? caused also some confusion.

Literature research

Instead of testing and benchmarking on his own our customer relied on benchmarks done by some other people:

• Benchmarking Aria: These benchmarks, which are older than 2016, claim, that MariaDB is partly faster than MyISAM for internal …

If we do customers database configuration analysis we check on one side if the most important MariaDB server system variables (innodb_buffer_pool_size, …) are set appropriately but also if some MariaDB server system variables are configured completely wrong.

Fortunately MariaDB introduced in MariaDB 10.1 the INFORMATION_SCHEMA.SYSTEM_VARIABLES view where you can find all the relevant information. But one!

Since MariaDB 10.5 we can also see from which file the MariaDB server system variable configuration is coming from. This makes it easier to find and fix wrong configurations.

MariaDB server system variables which are NOT default

A general assumption is that the defaults set by MariaDB are in most cases OK and if you change the defaults you need a good justification for the changes. “I do not know.” is NOT a good justification!

SQL> SELECT VARIABLE_NAME, GLOBAL_VALUE, DEFAULT_VALUE
  FROM information_schema.SYSTEM_VARIABLES
 WHERE GLOBAL_VALUE != DEFAULT_VALUE
   AND GLOBAL_VALUE NOT …

Many customers come to us and ask us whether to use MariaDB or MySQL. The answer is not so simple. FromDual is a neutral and vendor independent MariaDB/MySQL consulting company. So we should not have (in the meaning of neutral) a clear preference. For us internally we have chosen our strategy according to some clearly defined criteria. But what we have chosen for us is not necessarily the right choice for you.

So what we want to show you here is a tool which helps you to choose the right strategy for your own company or situation. In this case a tool to use is the decision matrix
[ 1
]. We tried to build such a decision matrix for your choice between MariaDB and MySQL. You can fill in your ratings into the table and decide yourself:

We just came today across the mysqlfrm utility which helps to extract the DDL statement from the .frm file. This is exactly what is needed to automatize and thus simplify the partial restore from MariaDB full backup.

See also feature request here: MDEV-18827.

MariaDB has some time ago introduced or reused the sql_mode = 'oracle'. What they basically try to do is to implement a subset of the Oracle PL/SQL language. Because we receive more and more request from customers about MariaDB’s Oracle PL/SQL it is worth investigating a bit more in this feature and summarize the state of the art of this topic in this article.

See also our former articles about the MariaDB sql_mode = 'oracle':

• Select Hello World FromDual with MariaDB PL/SQL
• MariaDB PL/SQL Examples

Items found in the MariaDB Jira database

If you look at the items in the MariaDB Jira database you can get some valuable information and see some trends.

It is a bit tricky to search the database because of the various different labels (Compatibility, Oracle, PL/SQL) and keywords. You will not find all items in one search. Please let us know if you find some more items we do not track yet!

In MySQL 8 the MySQL Data Dictionary was placed inside the InnoDB Storage Engine. This has some impact on multi-tenant applications:

Parameters used:

Sometimes customers want to have a very simple Galera Cluster set-up. They do not want to invest into machines and build up the know-how for load balancers in front of the Galera Cluster.

For this type of customers there is a possibility to just run a VIP controlled by Corosync/Pacemaker in front of the Galera Cluster moving an IP address from one node to the other. But this is just an active/passive/passive set-up and reads and writes are only possible to one node at the time.
So you loose the scaling read/write and load-balancing functionality and just have the high availability feature left.

Corosync/Pacemaker

A few words upfront about Corosync/Pacemaker:

Pacemaker is a Cluster Resource Manager (CRM) (similar to InitV or SystemD). It “is the thing that starts and stops services (like your database or mail server) and contains logic for ensuring both that they are running, and that they are only running in one location (to avoid data corruption).”
[ 1
]

Corosync on the other hand is the thing …

Thank you very much for your reply. I have not found an error in my conversion process. My DB is in latin1 and I am going to convert to utf8 or utf8mb4. I was unsure if the conversion would in its self cause errors in the text and binary data already present in the DB. You have, I believed, answered that. It should not. Your examples are interesting. I will need to study them to understand them better.

Thank you again for replying!

Cheers, Hal

Hello Hal

I do not really get your question. Do you have a good and reproducible example for your question?

The MySQL documentation states in Changing the Character Set:

The CONVERT TO operation converts column values between the original and named character sets. This is not what you want if you have a column in one character set (like latin1) but the stored values actually use some other, incompatible character set (like utf8).

So the conversion SHOULD happen without an error. Everything else I consider a bug and should be filed as such.

But it theoretically CAN result in errors... Do you have an example for such an error?

I would say, that latin1 is technically not an exact sub-set of utf8mb4 because of the following example:

SQL> INSERT INTO test VALUES (NULL, 'äöü', NULL);

SQL> SELECT HEX(data) FROM test;
+--------------+
| hex(data)    |
+--------------+
| C3A4C3B6C3BC |
+--------------+

SQL> ALTER TABLE test CONVERT TO CHARACTER SET utf8mb4;

SQL> SELECT HEX(data) FROM test; …

Can altering the table character set to utf8mb4 cause error or special characters to appear in existing text or binary (blob) data. That is when ALTER TABLE test CONVERT TO CHARACTER SET utf8mb4 is executed will existing latin1 text be converted properly. Perhaps this is a mute point as I believe latin1 is part of UTF8MB4, but I am not sure.

Thanks for any insight!!

Hal Richman

We see quite often customers complaining that their Galera Cluster is not stable and “crashes” from time to time. As always one has to investigate before rating.

What comes out quite often is that the customer (or better their developers) are running huge transactions.

In general transactional database do NOT like huge transactions because of various reasons (MVCC, ROLLBACK, UNDO, Locking etc.). They can do it. But they are not quite good in doing it and they do not like it. Instead you should better do many smaller transactions which you can run in parallel to keep the throughput. But: This causes more work for the one who should doing this transactions and needs more intelligence in the code…

Galera Cluster itself has some hard limits:

SQL> SHOW GLOBAL VARIABLES LIKE 'wsrep%ws%';
+-------------------+------------+
| Variable_name     | Value      |
+-------------------+------------+
| wsrep_max_ws_rows | 0          |
| wsrep_max_ws_size | 2147483647 |
+-------------------+------------+ …

The receipt above seems also to work with Percona Xtrabackup:

First you have to disabled the PXC strict mode: pxc_strict_mode = disabled. Then you have to copy the *.{exp|cfg|ibd} files to the right location and import the tablespaces again.

We further found, that an --export on an already prepared backup seems to be possible with xtrabackup.

Source: Github: codership / documentation

Codership Blog for Galera Cluster with release announcements.

Galera Plugin version 4 Series

Galera plug-in 26.4.21 Release Notes, Release Date: 12 December 2024

Galera plug-in 26.4.20 Release Notes, Release Date: 2 August 2024

Galera plug-in 26.4.19 Release Notes, Release Date: 1 July 2024

Galera plug-in 26.4.18 Release Notes, Release Date: 26 March 2024

Galera plug-in 26.4.17 Release Notes, Release Date: 8 January 2024

Galera plug-in 26.4.16 Release Notes, Release Date: 18 August 2023

Galera plug-in 26.4.15 Release Notes, Release Date: 16 June 2023

Galera plug-in 26.4.14 Release Notes, Release Date: 8 March 2023

Galera plug-in 26.4.13 Release Notes, Release Date: 24 November 2022

Galera plug-in 26.4.12 Release Notes, Release Date: 16 May 2022

Galera plug-in 26.4.11 Release Notes, Release Date: 14 February 2022

Galera plug-in 26.4.10 Release Notes, Release Date: 15 November 2021

Galera plug-in 26.4.9 Release Notes, Release Date: 26 July 2021

Galera plug-in 26.4.8 …

Some of my colleagues mentioned Window Functions. Maybe it works. But I fear that window functions are not fast because they do some materialization in between? I have to test...

For a fancy application we want to query a chunk of rows from a table and therefore we need the minimum and the maximum of the Primary Key of these rows.
Because InnoDB is an Index Organized Table or Index Clustered Table we know that this access will use the Primary Key. But to be sure and to be compliant with the standard (and compatible) we use and ORDER BY on the Primary Key.

MySQL 5.7

First we create some test data:

mysql> CREATE TABLE t_my (
  ID CHAR(32) NOT NULL PRIMARY KEY
) ENGINE = InnoDB;

mysql> INSERT INTO t_my
SELECT MD5(RAND())
FROM t_my;
... create more than 10 rows

mysql> SELECT id FROM t_my ORDER BY id LIMIT 11;
+----------------------------------+
| id                               |
+----------------------------------+
| 01a6e76643c83c91867636ce90a8def5 |
| 0ea1b1670343b4e70dd449207c720957 |
| 141ec92e809c1d6af83d27e8a3e74fe7 |
| 1605890e2c0244b019e6f66cc94790f2 |
| 19826d67b6013ed3bc1105b9708959c4 |
| 1a9ffd320187831df939d596c9a50aa1 |
| 24ae3a883803f5ae8416754593cd881c |
| …