Sammlung von Newsfeeds

This week I came across an old known issue reported in May 2010: Master/Slave Replication with binlog_format = ROW and tables without a Primary Key is a bad idea! Especially if these tables are huge.

Why this is a bad idea is described in the bug report #53375:

if one runs DML on a table that has no indexes, a full table scan is done. with RBR, the slave might need to scan the full table for *each* row changed.

The consequence of this behaviour is that the Slave starts lagging. It was further mentioned:

Worst part is that PROCESSLIST, etc provide absolutely NO obvious indication what is going on, for something that may take 12 hours, 3 days or even more...

Symptoms of this problem are described as follows:

Observe 78,278 row locks but only 10,045 undo log entries, so many more rows being scanned than changed. Also observer 16 row deletes per second but 600,754 row reads per second, same mismatch between counts suggesting unindexed accesses are happening.

You may also see "invalidating query cache entries (table)" as a symptom in the processlist. If you see that, check to see whether this is the possible root cause instead of giving full blame to only the query cache."

The suggested workaround is: add a primary key to the table.

But some user complain:

in my case, my only decent primary key is a surrogate key - and that's untenable because of the locking and lost concurrency (even with lock_mode = 2). Even if I solved that, I'd have to use the surrogate in partitioning - which more or less defeats the purpose of partitioning by lopsiding the partitions.

and others claim:

Adding an "otherwise usable (i.e. to improve query times)" PK is not really an option for them since there are no short unique columns.

A long composite key is also not an option because:

• In InnoDB tables, having a long PRIMARY KEY wastes a lot of space.
• In InnoDB, the records in nonclustered indexes (also called secondary indexes) contain the primary key columns for the row that are not in the secondary index. InnoDB uses this primary key value to search for the row in the clustered index. If the primary key is long, the secondary indexes use more space, so it is advantageous to have a short primary key.

And then comes a first suggestion for solving the issue:

So, we can create a normal short/auto-increment PK, but this is more or less the same as having the internal/hidden InnoDB PK (which does not seem to be used properly for RBR replication purposes).

As mentioned before, possibly the internal/hidden InnoDB PK can be used to resolve this bug.

Short after we get an important information and the learning of the day:

there's nothing that makes the InnoDB internal key consistent between a master and a slave or before and after backup and restore. Row with internal ID 1 can have completely different end user data values on different servers, so it's useless for the purpose being considered here, unfortunately.

Nor is there any prohibition on a slave having a unique key, which will be promoted to PK, even if there is no unique key on the master. They can even have different PKs and there can be good application reasons for doing that. Though we could require at least a unique key on all slaves that matches a master's PK without it hurting unduly.

It is possible to recommend at least _a_ key (not necessarily unique) on the slave and have replication try key-based lookups to narrow down the number of rows that must examined. That combined with batch processing should cut the pain a lot because we can reasonably ask for at least some non-unique but at least reasonably selective key to use. But this is only recommend, not require. If people want no key, we should let them have no key and be slow.

Then we got some further information why moving back to SBR is a bad idea:

It is my opinion that switching to SBR has way too many trade offs (if even for one table) to call it an acceptable workaround. The main crux for this argument being that just about the only time you run into this bug is when you have tables with a massive amount of rows - which is exactly where you start paying heavy penalties for SBR (Locking)."

And a new potential problem rises up:

As far as how the server should know which key to use - am I correct in assuming that it will use the optimizer to determine the index, and you are asking what would happen if the optimizer picked the wrong one?"

Another suggestion for improvement:

Batching looks promising, at least it would reduce the number of scans. But it would still be very painful if no key could be used. While using a key would be very painful if a high percentage of the rows in the table were being touched. So maybe some mixed solution that depends on the count of rows being touched might be best.

In about 2012 they had an implementation for batch jobs.

And you can force a Primary Key now in MySQL 8.0 since 2021 with sql_require_primary_key.

How MariaDB solves the problem you can find here: Row-based Replication With No Primary Key.

Taxonomy upgrade extras: primary keyreplicationRow Based Replication (RBR)Statement Based Replication (SBR)

This week I came across an old known issue reported in May 2010: Master/Slave Replication with binlog_format = ROW and tables without a Primary Key is a bad idea! Especially if these tables are huge.

Why this is a bad idea is described in the bug report #53375:

if one runs DML on a table that has no indexes, a full table scan is done. with RBR, the slave might need to scan the full table for *each* row changed.

The consequence of this behaviour is that the Slave starts lagging. It was further mentioned:

Worst part is that PROCESSLIST, etc provide absolutely NO obvious indication what is going on, for something that may take 12 hours, 3 days or even more...

Symptoms of this problem are described as follows:

Observe 78,278 row locks but only 10,045 undo log entries, so many more rows being scanned than changed. Also observer 16 row deletes per second but 600,754 row reads per second, same mismatch between counts suggesting unindexed accesses are happening.

You may also see "invalidating query cache entries (table)" as a symptom in the processlist. If you see that, check to see whether this is the possible root cause instead of giving full blame to only the query cache."

The suggested workaround is: add a primary key to the table.

But some user complain:

in my case, my only decent primary key is a surrogate key - and that's untenable because of the locking and lost concurrency (even with lock_mode = 2). Even if I solved that, I'd have to use the surrogate in partitioning - which more or less defeats the purpose of partitioning by lopsiding the partitions.

and others claim:

Adding an "otherwise usable (i.e. to improve query times)" PK is not really an option for them since there are no short unique columns.

A long composite key is also not an option because:

• In InnoDB tables, having a long PRIMARY KEY wastes a lot of space.
• In InnoDB, the records in nonclustered indexes (also called secondary indexes) contain the primary key columns for the row that are not in the secondary index. InnoDB uses this primary key value to search for the row in the clustered index. If the primary key is long, the secondary indexes use more space, so it is advantageous to have a short primary key.

And then comes a first suggestion for solving the issue:

So, we can create a normal short/auto-increment PK, but this is more or less the same as having the internal/hidden InnoDB PK (which does not seem to be used properly for RBR replication purposes).

As mentioned before, possibly the internal/hidden InnoDB PK can be used to resolve this bug.

Short after we get an important information and the learning of the day:

there's nothing that makes the InnoDB internal key consistent between a master and a slave or before and after backup and restore. Row with internal ID 1 can have completely different end user data values on different servers, so it's useless for the purpose being considered here, unfortunately.

Nor is there any prohibition on a slave having a unique key, which will be promoted to PK, even if there is no unique key on the master. They can even have different PKs and there can be good application reasons for doing that. Though we could require at least a unique key on all slaves that matches a master's PK without it hurting unduly.

It is possible to recommend at least _a_ key (not necessarily unique) on the slave and have replication try key-based lookups to narrow down the number of rows that must examined. That combined with batch processing should cut the pain a lot because we can reasonably ask for at least some non-unique but at least reasonably selective key to use. But this is only recommend, not require. If people want no key, we should let them have no key and be slow.

Then we got some further information why moving back to SBR is a bad idea:

It is my opinion that switching to SBR has way too many trade offs (if even for one table) to call it an acceptable workaround. The main crux for this argument being that just about the only time you run into this bug is when you have tables with a massive amount of rows - which is exactly where you start paying heavy penalties for SBR (Locking)."

And a new potential problem rises up:

As far as how the server should know which key to use - am I correct in assuming that it will use the optimizer to determine the index, and you are asking what would happen if the optimizer picked the wrong one?"

Another suggestion for improvement:

Batching looks promising, at least it would reduce the number of scans. But it would still be very painful if no key could be used. While using a key would be very painful if a high percentage of the rows in the table were being touched. So maybe some mixed solution that depends on the count of rows being touched might be best.

In about 2012 they had an implementation for batch jobs.

And you can force a Primary Key now in MySQL 8.0 since 2021 with sql_require_primary_key.

How MariaDB solves the problem you can find here: Row-based Replication With No Primary Key.

Taxonomy upgrade extras: primary keyreplicationRow Based Replication (RBR)Statement Based Replication (SBR)

We get ever and ever again customer requests concerning Deadlocks. First of all, Deadlocks are usually an application problem, not a database problem! The database itself manifests the application problem with the following message which is sent to the application as an error:

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

If your application receives this error message you know where in your application you have a problem. But a deadlock is always a problem between 2 different connections. So how you can find the other part of the problem?

The full Deadlock situation is shown with the following command:

SQL> SHOW ENGINE InnoDB STATUS\G ... ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2019-12-23 18:55:18 0x7f51045e3700 *** (1) TRANSACTION: TRANSACTION 847, ACTIVE 10 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1 MySQL thread id 46, OS thread handle 139985942054656, query id 839 localhost root Updating delete from t where id = 10 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 40 page no 3 n bits 80 index PRIMARY of table `test`.`t` trx id 847 lock_mode X locks rec but not gap waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 32 0: len 4; hex 8000000a; asc ;; 1: len 6; hex 00000000034e; asc N;; 2: len 7; hex 760000019c0495; asc v ;; *** (2) TRANSACTION: TRANSACTION 846, ACTIVE 25 sec starting index read mysql tables in use 1, locked 1 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1 MySQL thread id 39, OS thread handle 139985942361856, query id 840 localhost root Updating delete from t where id = 11 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 40 page no 3 n bits 80 index PRIMARY of table `test`.`t` trx id 846 lock_mode X locks rec but not gap Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 32 0: len 4; hex 8000000a; asc ;; 1: len 6; hex 00000000034e; asc N;; 2: len 7; hex 760000019c0495; asc v ;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 40 page no 3 n bits 80 index PRIMARY of table `test`.`t` trx id 846 lock_mode X locks rec but not gap waiting Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 32 0: len 4; hex 8000000b; asc ;; 1: len 6; hex 00000000034f; asc O;; 2: len 7; hex 770000019d031d; asc w ;; *** WE ROLL BACK TRANSACTION (2) ...

Unfortunately with the SHOW ENGINE INNODB STATUS command you can only show the last Deadlock error. If you want to log all the Deadlock errors in the MariaDB error log you can activate the Deadlock error reporting with the following configuration option in your MariaDB configuration file (my.cnf):

innodb_print_all_deadlocks = ON
How to solve Deadlocks?

Deadlocks always can occur in a transactional database system with fine-grained locking. So your application must be aware of Deadlocks and catch the Deadlock error and retry the transaction. The error message already indicates this. It is an advice for YOU as a developer:

try restarting transaction
How to avoid deadlocks?

Deadlocks are an hot spot on some rows. Hot spot means: Many (at least 2) connections do something (INSERT, UPDATE, REPLACE, DELETE, SELECT FOR UPDATE) on the same rows (which then are locked).

So how can you solve a Deadlock problem? Solving in the meaning of "reduce the probability of a Deadlock close to zero". Zero Deadlocks guarantee you can only achieve in no-concurrency scenarios.

• Look at the design of your application: Is it necessary that the 2 conflicting transactions are done in the way you are doing it right now? Can you change the behaviour of your application?
• Reduce locking time: As shorter the locks are hold as smaller the probability is that you run into a Deadlock situation. So make sure your conflicting transactions are running as fast as possible and the locks are hold as short as possible. Check that all the transactions are using perfect indexes.
• Reduce the amount of locks: The fewer rows your conflicting transactions lock the smaller is the chance for a Deadlock. For example: Several small INSERTs in several transactions instead of one huge INSERT in one big transaction should cause less locks and thus a smaller probability of Deadlocks. Only change (REPLACE, INSERT ON DUPLICATE KEY UPDATE) what is really needed to change. Check before you change, that rows need to be changed.
• Do your tasks less often. For example polling every 10 seconds instead of every second, or do refreshes of data less often.
• Timely defer concurrent transactions. For example 2 batch jobs: Do they really need to run at the exact same time?
• Reduce concurrency (parallelism), if possibly.
• Catch the Deadlock error and retry the transaction again a few milliseconds later. If this does not happen too often it is not a problem for your database.
• Change transaction isolation level to a level causing less locks. You can do that per session (SET SESSION ...) to reduce the impact on the whole system or globally (SET GLOBAL ...). But changing the isolation level globally potentially can have also an impact on other parts of your application or even completely different applications running on the same database.

And to be aware: You cannot completely avoid (100%) Deadlocks. They always can happen and you have to cope with it!

Do not mix up Deadlocks with Galera cluster conflicts which look similar at the first look but are not.

Literature

• Deadlocks, indexing and Primary Key's
• MariaDB Deadlocks
• MySQL Deadlocks in InnoDB

Taxonomy upgrade extras: deadlock

We get ever and ever again customer requests concerning Deadlocks. First of all, Deadlocks are usually an application problem, not a database problem! The database itself manifests the application problem with the following message which is sent to the application as an error:

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

If your application receives this error message you know where in your application you have a problem. But a deadlock is always a problem between 2 different connections. So how you can find the other part of the problem?

The full Deadlock situation is shown with the following command:

SQL> SHOW ENGINE InnoDB STATUS\G ... ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2019-12-23 18:55:18 0x7f51045e3700 *** (1) TRANSACTION: TRANSACTION 847, ACTIVE 10 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1 MySQL thread id 46, OS thread handle 139985942054656, query id 839 localhost root Updating delete from t where id = 10 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 40 page no 3 n bits 80 index PRIMARY of table `test`.`t` trx id 847 lock_mode X locks rec but not gap waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 32 0: len 4; hex 8000000a; asc ;; 1: len 6; hex 00000000034e; asc N;; 2: len 7; hex 760000019c0495; asc v ;; *** (2) TRANSACTION: TRANSACTION 846, ACTIVE 25 sec starting index read mysql tables in use 1, locked 1 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1 MySQL thread id 39, OS thread handle 139985942361856, query id 840 localhost root Updating delete from t where id = 11 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 40 page no 3 n bits 80 index PRIMARY of table `test`.`t` trx id 846 lock_mode X locks rec but not gap Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 32 0: len 4; hex 8000000a; asc ;; 1: len 6; hex 00000000034e; asc N;; 2: len 7; hex 760000019c0495; asc v ;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 40 page no 3 n bits 80 index PRIMARY of table `test`.`t` trx id 846 lock_mode X locks rec but not gap waiting Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 32 0: len 4; hex 8000000b; asc ;; 1: len 6; hex 00000000034f; asc O;; 2: len 7; hex 770000019d031d; asc w ;; *** WE ROLL BACK TRANSACTION (2) ...

Unfortunately with the SHOW ENGINE INNODB STATUS command you can only show the last Deadlock error. If you want to log all the Deadlock errors in the MariaDB error log you can activate the Deadlock error reporting with the following configuration option in your MariaDB configuration file (my.cnf):

innodb_print_all_deadlocks = ON
How to solve Deadlocks?

Deadlocks always can occur in a transactional database system with fine-grained locking. So your application must be aware of Deadlocks and catch the Deadlock error and retry the transaction. The error message already indicates this. It is an advice for YOU as a developer:

try restarting transaction
How to avoid deadlocks?

Deadlocks are an hot spot on some rows. Hot spot means: Many (at least 2) connections do something (INSERT, UPDATE, REPLACE, DELETE, SELECT FOR UPDATE) on the same rows (which then are locked).

So how can you solve a Deadlock problem? Solving in the meaning of "reduce the probability of a Deadlock close to zero". Zero Deadlocks guarantee you can only achieve in no-concurrency scenarios.

• Look at the design of your application: Is it necessary that the 2 conflicting transactions are done in the way you are doing it right now? Can you change the behaviour of your application?
• Reduce locking time: As shorter the locks are hold as smaller the probability is that you run into a Deadlock situation. So make sure your conflicting transactions are running as fast as possible and the locks are hold as short as possible. Check that all the transactions are using perfect indexes.
• Reduce the amount of locks: The fewer rows your conflicting transactions lock the smaller is the chance for a Deadlock. For example: Several small INSERTs in several transactions instead of one huge INSERT in one big transaction should cause less locks and thus a smaller probability of Deadlocks. Only change (REPLACE, INSERT ON DUPLICATE KEY UPDATE) what is really needed to change. Check before you change, that rows need to be changed.
• Do your tasks less often. For example polling every 10 seconds instead of every second, or do refreshes of data less often.
• Timely defer concurrent transactions. For example 2 batch jobs: Do they really need to run at the exact same time?
• Reduce concurrency (parallelism), if possibly.
• Catch the Deadlock error and retry the transaction again a few milliseconds later. If this does not happen too often it is not a problem for your database.
• Change transaction isolation level to a level causing less locks. You can do that per session (SET SESSION ...) to reduce the impact on the whole system or globally (SET GLOBAL ...). But changing the isolation level globally potentially can have also an impact on other parts of your application or even completely different applications running on the same database.

And to be aware: You cannot completely avoid (100%) Deadlocks. They always can happen and you have to cope with it!

Do not mix up Deadlocks with Galera cluster conflicts which look similar at the first look but are not.

Literature

• Deadlocks, indexing and Primary Key's
• MariaDB Deadlocks
• MySQL Deadlocks in InnoDB

Taxonomy upgrade extras: deadlock

Also in 2022 there will be a FOSDEM (Free and Open source Software Developers' European Meeting) on 5 and 6 February 2022. This time again online from Brussels (Belgium).

MariaDB has again its own Devroom and the Call for Papers (CfP) is now open for your submissions. We are looking for interesting topics about your daily business, technical presentations, war stories, point of views of management, etc. The deadline for the CfP is before 21 December 2021.

For further information about how to submit a presentation please look here: https://mariadb.org/cfp-for-the-mariadb-devroom-fosdem-2022-now-open/

If you need any help or if you have any question please let us know...

Taxonomy upgrade extras: fosdem2022developersocial event

Also in 2022 there will be a FOSDEM (Free and Open source Software Developers' European Meeting) on 5 and 6 February 2022. This time again online from Brussels (Belgium).

MariaDB has again its own Devroom and the Call for Papers (CfP) is now open for your submissions. We are looking for interesting topics about your daily business, technical presentations, war stories, point of views of management, etc. The deadline for the CfP is before 21 December 2021.

For further information about how to submit a presentation please look here: https://mariadb.org/cfp-for-the-mariadb-devroom-fosdem-2022-now-open/

If you need any help or if you have any question please let us know...

Taxonomy upgrade extras: fosdem2022developersocial event

The MariaDB Connection ID exists since long ago. So why bother about the Connection ID? Because it is interesting and you can do some interesting things with the Connection ID like tracking statements in your connections and find where they come from your application code.

The MariaDB Connection ID is a strictly monotonic increasing number starting with 1 at server restart:

shell> mariadb --user=root --execute='SELECT CONNECTION_ID()\G' | grep CONNECTION_ID CONNECTION_ID(): 2372 shell> mariadb --user=root --execute='SELECT CONNECTION_ID()\G' | grep CONNECTION_ID CONNECTION_ID(): 2373 shell> mariadb --user=root --execute='SELECT CONNECTION_ID()\G' | grep CONNECTION_ID CONNECTION_ID(): 2374

The MariaDB documentation states [1]:

Returns the connection ID (thread ID) for the connection. Every thread (including events) has an ID that is unique among the set of currently connected clients.

The MariaDB documentation is only partly correct because the thread ID is something different and the term is used ambiguous. See further down.

The maximum number of connections created can be shown with:

SQL> SHOW GLOBAL STATUS LIKE 'Connections'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Connections | 2322 | +---------------+-------+

But where else can I find the Connection ID?

Processlist

The Connection ID is shown in many different places inside your MariaDB database server. First of all you will find it in the process list:

SQL> SELECT id AS connection_id, user, host, IFNULL(CONCAT(SUBSTR(info, 1, 32), '...'), '') AS query FROM information_schema.processlist; +---------------+-------------+-----------+-------------------------------------+ | connection_id | user | host | query | +---------------+-------------+-----------+-------------------------------------+ | 2383 | root | localhost | SELECT id AS processlist_id, use... | | 6 | system user | | | +---------------+-------------+-----------+-------------------------------------+

Unfortunately it is named there just id so you have to know what it means.

You can easily show what all connections are doing while filtering out your own connection:

SQL> SELECT * FROM information_schema.processlist WHERE id != CONNECTION_ID(); +------+-------------+-----------------+------+-----------+--------+-------+------+---------------+-------+-----------+----------+-------------+-----------------+---------------+----------+-------------+------+ | ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO | TIME_MS | STAGE | MAX_STAGE | PROGRESS | MEMORY_USED | MAX_MEMORY_USED | EXAMINED_ROWS | QUERY_ID | INFO_BINARY | TID | +------+-------------+-----------------+------+-----------+--------+-------+------+---------------+-------+-----------+----------+-------------+-----------------+---------------+----------+-------------+------+ | 2398 | app | localhost:35512 | NULL | Sleep | 4 | | NULL | 4771.722 | 0 | 0 | 0.000 | 81784 | 81784 | 0 | 14030 | NULL | 6946 | | 2392 | root | localhost | NULL | Sleep | 36 | | NULL | 36510.867 | 0 | 0 | 0.000 | 81784 | 81784 | 0 | 14021 | NULL | 3850 | +------+-------------+-----------------+------+-----------+--------+-------+------+---------------+-------+-----------+----------+-------------+-----------------+---------------+----------+-------------+------+

Note: The information_schema.processlist view is a superset of the command SHOW FULL PROCESSLIST and can be used with SQL means. So it has some advantages to retrieve the data from there...

InnoDB Monitor

Also in the InnoDB Monitor you can see the MariaDB Connection ID in at least 3 different places. The InnoDB Monitor is called as follows:

SQL> SHOW ENGINE INNODB STATUS\G

In the following 3 sections you will find the Connection ID:

------------------------ LATEST DETECTED DEADLOCK ------------------------ 2021-06-26 11:05:21 13c0 *** (1) TRANSACTION: TRANSACTION 17064867, ACTIVE 17 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 8 lock struct(s), heap size 3112, 7 row lock(s), undo log entries 2 MySQL thread id 7778, OS thread handle 0xb9c, query id 23386973 10.0.0.6 WPFieldUser updating Update wp_schema.trackfield_table Set reasonForrejection=4 where track_id='1406250843353122' *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 241 page no 1541 n bits 680 index `track_id_FK` of table `wp_schema`.`trackfield_table` trx id 170648 67 lock_mode X waiting Record lock, heap no 139 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 16; hex 31343036323530383433333533313232; asc 1406250843353122;; 1: len 4; hex 8000a4d8; asc ;; *** (2) TRANSACTION: TRANSACTION 17063837, ACTIVE 173 sec starting index read, thread declared inside InnoDB 5000 mysql tables in use 1, locked 1 14 lock struct(s), heap size 3112, 22 row lock(s), undo log entries 10 MySQL thread id 7765, OS thread handle 0x13c0, query id 23387033 10.0.0.6 WPFieldUser updating Update wp_schema.trackfield_table Set reasonForrejection=4 where track_id='1406251613333122' *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 241 page no 1541 n bits 680 index `track_id_FK` of table `wp_schema`.`trackfield_table` trx id 170638 37 lock_mode X Record lock, heap no 139 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 16; hex 31343036323530383433333533313232; asc 1406250843353122;; 1: len 4; hex 8000a4d8; asc ;; ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 2021-08-19 15:09:19 7fbb6c328700 Transaction: TRANSACTION 543875059, ACTIVE 0 sec inserting mysql tables in use 1, locked 14 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 1 MySQL thread id 124441421, OS thread handle 0x7fbb6c328700, query id 7822461590 192.168.1.42 fronmdual update INSERT INTO contact (user_id,kontact_id) VALUES (62486, 63130) Foreign key constraint fails for table `test`.`contact`: , CONSTRAINT `FK_contact_user_2` FOREIGN KEY (`contact_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE Trying to add in child table, in index `contact_id` tuple: DATA TUPLE: 2 fields; ... But in parent table `test`.`user`, in index `PRIMARY`, the closest match we can find is record: PHYSICAL RECORD: n_fields 41; compact format; info bits 0 ... ------------ TRANSACTIONS ------------ Trx id counter 2499 Purge done for trx's n:o < 2486 undo n:o < 0 state: running History list length 12 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION (0x7f70d6b93330), ACTIVE 3 sec mysql tables in use 1, locked 0 0 lock struct(s), heap size 1128, 0 row lock(s) MariaDB thread id 2398, OS thread handle 140122589112064, query id 14075 localhost 127.0.0.1 app Sending data select * from test.test Trx read view will not see trx with id >= 2499, sees < 2499

Unfortunately the Connection ID here is called thread id in all 3 different places. But we know at least which connection is causing Deadlock errors, Foreign Key errors or long running transactions.

PERFORMANCE_SCHEMA threads view

In the PERFORMANCE_SCHEMA.threads view you can also find the MariaDB Connection ID, called processlist_id. But here starts confusion again. MySQL introduced a new column thread_id which is mostly used in the PERFORMANCE_SCHEMA and contains the ID of a thread (not a connection):

SQL> SELECT thread_id, processlist_id FROM performance_schema.threads WHERE processlist_id = CONNECTION_ID(); +-----------+----------------+ | thread_id | processlist_id | +-----------+----------------+ | 2369 | 2321 | +-----------+----------------+

The processlist_id can be found also in the views: session_account_connect_attrs and session_connect_attrs in the PERFORMANCE_SCHEMA.

Via the thread_id you can match now your connection to various other views in the PERFORMANCE_SCHEMA: events_stages_*, events_statements_*, events_transactions_*, events_waits_*, memory_summary_by_thread_by_event_name, socket_instances, status_by_thread and user_variables_by_thread

There are related thread_ids in: threads.PARENT_THREAD_ID, metadata_locks.OWNER_THREAD_ID, mutex_instances.LOCKED_BY_THREAD_ID, prepared_statements_instances.OWNER_THREAD_ID, table_handles.OWNER_THREAD_ID and rwlock_instances.WRITE_LOCKED_BY_THREAD_ID.

sys Schema

Also in the sys Schema you find the thread_id (but not the connection_id) in the following views: io_by_thread_by_latency, latest_file_io, memory_by_thread_by_current_bytes, processlist, schema_table_lock_waits and session_ssl_status.

MariaDB Log files MariaDB Error Log file

Also in your MariaDB Error Log file you find the Connection ID. The Connection ID is the 2nd position in the Log. And sometimes you see the Connection ID also in the error message itself:

2021-11-25 16:22:34 1796 [Warning] Hostname 'chef' does not resolve to '192.168.56.1'. 2021-11-25 16:22:34 1796 [Note] Hostname 'chef' has the following IP addresses: 2021-11-25 16:22:34 1796 [Note] - 192.168.1.142 2021-11-25 16:22:34 1796 [Warning] Aborted connection 1796 to db: 'unconnected' user: 'unauthenticated' host: '192.168.56.1' (This connection closed normally without authentication) 2021-11-26 16:09:14 2397 [Warning] Access denied for user 'app'@'localhost' (using password: YES)
MariaDB General Query Log

The most important use of the Connection ID I see in the MariaDB General Query Log. Here you can find ALL the queries sent through connections to the database. You can easily search for a specific Connection ID and you will see exactly what a connection does or did:

211108 22:18:02 4568 Connect fpmmm_agent@localhost on using TCP/IP 4568 Query SET NAMES utf8 4568 Query SHOW GRANTS 4568 Query SELECT "focmm" 4568 Query SHOW GLOBAL VARIABLES 4568 Query SHOW /*!50000 GLOBAL */ STATUS 4568 Quit
MariaDB Slow Query Log

You can also find the Connection ID in the MariaDB Slow Query Log. But here again it is called Thread_id:

# Time: 210729 9:22:57 # User@Host: root[root] @ localhost [] # Thread_id: 34 Schema: test QC_hit: No # Query_time: 0.003995 Lock_time: 0.000114 Rows_sent: 2048 Rows_examined: 2048 # Rows_affected: 0 Bytes_sent: 79445 SET timestamp=1627543377; select * from test;
MariaDB SQL Error Log

Unfortunately the Connection ID is missing in the MariaDB SQL Error Log output:

shell> tail sql_errors.log 2021-11-26 16:58:36 app[app] @ localhost [127.0.0.1] ERROR 1046: No database selected : select * from test limt 10

We opened a feature request for this: SQL Error Log plug-in lacks Connection ID (MDEV-27129).

MariaDB Binary Log

Also in the MariaDB Binary Log the Connection ID is missing.

MariaDB Audit Plugin

But in the MariaDB Audit Plugin we will find again the Connection ID in the 5th column:

20211126 17:19:01,chef,app,localhost,2477,18407,QUERY,,'SELECT DATABASE()',0 20211126 17:19:01,chef,app,localhost,2477,18409,QUERY,test,'show databases',0 20211126 17:19:01,chef,app,localhost,2477,18410,QUERY,test,'show tables',0 20211126 17:19:02,chef,app,localhost,2477,18423,QUERY,test,'select * from test limt 10',1064 20211126 17:19:05,chef,app,localhost,2477,18424,READ,test,test, 20211126 17:19:05,chef,app,localhost,2477,18424,QUERY,test,'select * from test limit 10',0 20211126 17:19:38,chef,app,localhost,2477,18426,QUERY,test,'select connection_id()',0 20211126 17:19:52,chef,app,localhost,2477,0,DISCONNECT,test,,0
INFORMATION_SCHEMA

Also in some INFORMATION_SCHEMA views we will find the Connection ID: In THREAD_POOL_QUEUES.CONNECTION_ID, METADATA_LOCK_INFO.THREAD_ID and INNODB_TRX.trx_mysql_thread_id.

Other related topics to MariaDB Connection ID Thread Cache

When using the MariaDB Thread Cache it looks like the thread_id (and also the Connection ID) is changed each time a new connection is created. This is not what I expected, at least for the Thread ID. If I take a thread from the pool I would expect the same or at least another old thread_id again.

Connection Pooling

If you are using application side Connection Pooling different application connection handles will share the same DB connection. So you have to expect traffic from different application parts under the same Connection ID on the database side.

Pseudo thread ID

Since MySQL 8.0.14 there is a variable called pseudo_thread_id. It is for internal server use. Changing the variable on session level also changes the value of the function CONNECTION_ID(). I have no idea what this variable is used for.

Other related information

• Abbrechende MariaDB/MySQL Verbindungen
• Failed MySQL DDL commands and Galera replication

Taxonomy upgrade extras: connectionmax_used_connectionsgeneral query log

The MariaDB Connection ID exists since long ago. So why bother about the Connection ID? Because it is interesting and you can do some interesting things with the Connection ID like tracking statements in your connections and find where they come from your application code.

The MariaDB Connection ID is a strictly monotonic increasing number starting with 1 at server restart:

shell> mariadb --user=root --execute='SELECT CONNECTION_ID()\G' | grep CONNECTION_ID CONNECTION_ID(): 2372 shell> mariadb --user=root --execute='SELECT CONNECTION_ID()\G' | grep CONNECTION_ID CONNECTION_ID(): 2373 shell> mariadb --user=root --execute='SELECT CONNECTION_ID()\G' | grep CONNECTION_ID CONNECTION_ID(): 2374

The MariaDB documentation states [1]:

Returns the connection ID (thread ID) for the connection. Every thread (including events) has an ID that is unique among the set of currently connected clients.

The MariaDB documentation is only partly correct because the thread ID is something different and the term is used ambiguous. See further down.

The maximum number of connections created can be shown with:

SQL> SHOW GLOBAL STATUS LIKE 'Connections'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Connections | 2322 | +---------------+-------+

But where else can I find the Connection ID?

Processlist

The Connection ID is shown in many different places inside your MariaDB database server. First of all you will find it in the process list:

SQL> SELECT id AS connection_id, user, host, IFNULL(CONCAT(SUBSTR(info, 1, 32), '...'), '') AS query FROM information_schema.processlist; +---------------+-------------+-----------+-------------------------------------+ | connection_id | user | host | query | +---------------+-------------+-----------+-------------------------------------+ | 2383 | root | localhost | SELECT id AS processlist_id, use... | | 6 | system user | | | +---------------+-------------+-----------+-------------------------------------+

Unfortunately it is named there just id so you have to know what it means.

You can easily show what all connections are doing while filtering out your own connection:

SQL> SELECT * FROM information_schema.processlist WHERE id != CONNECTION_ID(); +------+-------------+-----------------+------+-----------+--------+-------+------+---------------+-------+-----------+----------+-------------+-----------------+---------------+----------+-------------+------+ | ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO | TIME_MS | STAGE | MAX_STAGE | PROGRESS | MEMORY_USED | MAX_MEMORY_USED | EXAMINED_ROWS | QUERY_ID | INFO_BINARY | TID | +------+-------------+-----------------+------+-----------+--------+-------+------+---------------+-------+-----------+----------+-------------+-----------------+---------------+----------+-------------+------+ | 2398 | app | localhost:35512 | NULL | Sleep | 4 | | NULL | 4771.722 | 0 | 0 | 0.000 | 81784 | 81784 | 0 | 14030 | NULL | 6946 | | 2392 | root | localhost | NULL | Sleep | 36 | | NULL | 36510.867 | 0 | 0 | 0.000 | 81784 | 81784 | 0 | 14021 | NULL | 3850 | +------+-------------+-----------------+------+-----------+--------+-------+------+---------------+-------+-----------+----------+-------------+-----------------+---------------+----------+-------------+------+

Note: The information_schema.processlist view is a superset of the command SHOW FULL PROCESSLIST and can be used with SQL means. So it has some advantages to retrieve the data from there...

InnoDB Monitor

Also in the InnoDB Monitor you can see the MariaDB Connection ID in at least 3 different places. The InnoDB Monitor is called as follows:

SQL> SHOW ENGINE INNODB STATUS\G

In the following 3 sections you will find the Connection ID:

------------------------ LATEST DETECTED DEADLOCK ------------------------ 2021-06-26 11:05:21 13c0 *** (1) TRANSACTION: TRANSACTION 17064867, ACTIVE 17 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 8 lock struct(s), heap size 3112, 7 row lock(s), undo log entries 2 MySQL thread id 7778, OS thread handle 0xb9c, query id 23386973 10.0.0.6 WPFieldUser updating Update wp_schema.trackfield_table Set reasonForrejection=4 where track_id='1406250843353122' *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 241 page no 1541 n bits 680 index `track_id_FK` of table `wp_schema`.`trackfield_table` trx id 170648 67 lock_mode X waiting Record lock, heap no 139 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 16; hex 31343036323530383433333533313232; asc 1406250843353122;; 1: len 4; hex 8000a4d8; asc ;; *** (2) TRANSACTION: TRANSACTION 17063837, ACTIVE 173 sec starting index read, thread declared inside InnoDB 5000 mysql tables in use 1, locked 1 14 lock struct(s), heap size 3112, 22 row lock(s), undo log entries 10 MySQL thread id 7765, OS thread handle 0x13c0, query id 23387033 10.0.0.6 WPFieldUser updating Update wp_schema.trackfield_table Set reasonForrejection=4 where track_id='1406251613333122' *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 241 page no 1541 n bits 680 index `track_id_FK` of table `wp_schema`.`trackfield_table` trx id 170638 37 lock_mode X Record lock, heap no 139 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 16; hex 31343036323530383433333533313232; asc 1406250843353122;; 1: len 4; hex 8000a4d8; asc ;; ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 2021-08-19 15:09:19 7fbb6c328700 Transaction: TRANSACTION 543875059, ACTIVE 0 sec inserting mysql tables in use 1, locked 14 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 1 MySQL thread id 124441421, OS thread handle 0x7fbb6c328700, query id 7822461590 192.168.1.42 fronmdual update INSERT INTO contact (user_id,kontact_id) VALUES (62486, 63130) Foreign key constraint fails for table `test`.`contact`: , CONSTRAINT `FK_contact_user_2` FOREIGN KEY (`contact_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE Trying to add in child table, in index `contact_id` tuple: DATA TUPLE: 2 fields; ... But in parent table `test`.`user`, in index `PRIMARY`, the closest match we can find is record: PHYSICAL RECORD: n_fields 41; compact format; info bits 0 ... ------------ TRANSACTIONS ------------ Trx id counter 2499 Purge done for trx's n:o < 2486 undo n:o < 0 state: running History list length 12 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION (0x7f70d6b93330), ACTIVE 3 sec mysql tables in use 1, locked 0 0 lock struct(s), heap size 1128, 0 row lock(s) MariaDB thread id 2398, OS thread handle 140122589112064, query id 14075 localhost 127.0.0.1 app Sending data select * from test.test Trx read view will not see trx with id >= 2499, sees < 2499

Unfortunately the Connection ID here is called thread id in all 3 different places. But we know at least which connection is causing Deadlock errors, Foreign Key errors or long running transactions.

PERFORMANCE_SCHEMA threads view

In the PERFORMANCE_SCHEMA.threads view you can also find the MariaDB Connection ID, called processlist_id. But here starts confusion again. MySQL introduced a new column thread_id which is mostly used in the PERFORMANCE_SCHEMA and contains the ID of a thread (not a connection):

SQL> SELECT thread_id, processlist_id FROM performance_schema.threads WHERE processlist_id = CONNECTION_ID(); +-----------+----------------+ | thread_id | processlist_id | +-----------+----------------+ | 2369 | 2321 | +-----------+----------------+

The processlist_id can be found also in the views: session_account_connect_attrs and session_connect_attrs in the PERFORMANCE_SCHEMA.

Via the thread_id you can match now your connection to various other views in the PERFORMANCE_SCHEMA: events_stages_*, events_statements_*, events_transactions_*, events_waits_*, memory_summary_by_thread_by_event_name, socket_instances, status_by_thread and user_variables_by_thread

There are related thread_ids in: threads.PARENT_THREAD_ID, metadata_locks.OWNER_THREAD_ID, mutex_instances.LOCKED_BY_THREAD_ID, prepared_statements_instances.OWNER_THREAD_ID, table_handles.OWNER_THREAD_ID and rwlock_instances.WRITE_LOCKED_BY_THREAD_ID.

sys Schema

Also in the sys Schema you find the thread_id (but not the connection_id) in the following views: io_by_thread_by_latency, latest_file_io, memory_by_thread_by_current_bytes, processlist, schema_table_lock_waits and session_ssl_status.

MariaDB Log files MariaDB Error Log file

Also in your MariaDB Error Log file you find the Connection ID. The Connection ID is the 2nd position in the Log. And sometimes you see the Connection ID also in the error message itself:

2021-11-25 16:22:34 1796 [Warning] Hostname 'chef' does not resolve to '192.168.56.1'. 2021-11-25 16:22:34 1796 [Note] Hostname 'chef' has the following IP addresses: 2021-11-25 16:22:34 1796 [Note] - 192.168.1.142 2021-11-25 16:22:34 1796 [Warning] Aborted connection 1796 to db: 'unconnected' user: 'unauthenticated' host: '192.168.56.1' (This connection closed normally without authentication) 2021-11-26 16:09:14 2397 [Warning] Access denied for user 'app'@'localhost' (using password: YES)
MariaDB General Error Log

The most important use of the Connection ID I see in the MariaDB General Query Log. Here you can find ALL the queries sent through connections to the database. You can easily search for a specific Connection ID and you will see exactly what a connection does or did:

211108 22:18:02 4568 Connect fpmmm_agent@localhost on using TCP/IP 4568 Query SET NAMES utf8 4568 Query SHOW GRANTS 4568 Query SELECT "focmm" 4568 Query SHOW GLOBAL VARIABLES 4568 Query SHOW /*!50000 GLOBAL */ STATUS 4568 Quit
MariaDB Slow Query Log

You can also find the Connection ID in the MariaDB Slow Query Log. But here again it is called Thread_id:

# Time: 210729 9:22:57 # User@Host: root[root] @ localhost [] # Thread_id: 34 Schema: test QC_hit: No # Query_time: 0.003995 Lock_time: 0.000114 Rows_sent: 2048 Rows_examined: 2048 # Rows_affected: 0 Bytes_sent: 79445 SET timestamp=1627543377; select * from test;
MariaDB SQL Error Log

Unfortunately the Connection ID is missing in the MariaDB SQL Error Log output:

shell> tail sql_errors.log 2021-11-26 16:58:36 app[app] @ localhost [127.0.0.1] ERROR 1046: No database selected : select * from test limt 10

We opened a feature request for this: SQL Error Log plug-in lacks Connection ID (MDEV-27129).

MariaDB Binary Log

Also in the MariaDB Binary Log the Connection ID is missing.

MariaDB Audit Plugin

But in the MariaDB Audit Plugin we will find again the Connection ID in the 5th column:

20211126 17:19:01,chef,app,localhost,2477,18407,QUERY,,'SELECT DATABASE()',0 20211126 17:19:01,chef,app,localhost,2477,18409,QUERY,test,'show databases',0 20211126 17:19:01,chef,app,localhost,2477,18410,QUERY,test,'show tables',0 20211126 17:19:02,chef,app,localhost,2477,18423,QUERY,test,'select * from test limt 10',1064 20211126 17:19:05,chef,app,localhost,2477,18424,READ,test,test, 20211126 17:19:05,chef,app,localhost,2477,18424,QUERY,test,'select * from test limit 10',0 20211126 17:19:38,chef,app,localhost,2477,18426,QUERY,test,'select connection_id()',0 20211126 17:19:52,chef,app,localhost,2477,0,DISCONNECT,test,,0
INFORMATION_SCHEMA

Also in some INFORMATION_SCHEMA views we will find the Connection ID: In THREAD_POOL_QUEUES.CONNECTION_ID, METADATA_LOCK_INFO.THREAD_ID and INNODB_TRX.trx_mysql_thread_id.

Other related topics to MariaDB Connection ID Thread Cache

When using the MariaDB Thread Cache it looks like the thread_id (and also the Connection ID) is changed each time a new connection is created. This is not what I expected, at least for the Thread ID. If I take a thread from the pool I would expect the same or at least another old thread_id again.

Connection Pooling

If you are using application side Connection Pooling different application connection handles will share the same DB connection. So you have to expect traffic from different application parts under the same Connection ID on the database side.

Pseudo thread ID

Since MySQL 8.0.14 there is a variable called pseudo_thread_id. It is for internal server use. Changing the variable on session level also changes the value of the function CONNECTION_ID(). I have no idea what this variable is used for.

Other related information

• Abbrechende MariaDB/MySQL Verbindungen
• Failed MySQL DDL commands and Galera replication

Taxonomy upgrade extras: connectionmax_used_connections

Das FromDual Seminarprogramm für 2022 steht. Mit unseren Schulungspartnern Linuxhotel in Essen, GfU Cyrus in Köln sowie der Heinlein Akademie in Berlin bieten wir auch 2022 wieder zahlreiche MariaDB und MySQL Schulungen an:

• MariaDB und MySQL für Fortgeschrittene
• Galera Cluster für MariaDB und MySQL

Im Seminar für Fortgeschrittene nehmen wir uns Themen wie Backup/Restore, Master/Slave Replikation, Galera Cluster sowie Datenbank-Konfigurations-Tuning und SQL Query Tuning vor und üben die einzelnen Punkte praktisch.

Im Galera Cluster Seminar nehmen wir alle Aspekte des Aufbaus und des Betriebs eines Galera Clusters durch und praktizieren das Ganz anschliessend ausführlich.

Den Umständen entsprechend werden die Seminare entweder vor Ort, remote oder hybrid durchgeführt.

Bei allfälligen Fragen bitten wir Sie, mit uns oder unseren Schulungspartnern Kontakt aufzunehmen und diese zu klären.

Taxonomy upgrade extras: trainingschulungseminarmysqlmariadbgalera2022replikation

Das FromDual Seminarprogramm für 2022 steht. Mit unseren Schulungspartnern Linuxhotel in Essen, GfU Cyrus in Köln sowie der Heinlein Akademie in Berlin bieten wir auch 2022 wieder zahlreiche MariaDB und MySQL Schulungen an:

• MariaDB und MySQL für Fortgeschrittene
• Galera Cluster für MariaDB und MySQL

Im Seminar für Fortgeschrittene nehmen wir uns Themen wie Backup/Restore, Master/Slave Replikation, Galera Cluster sowie Datenbank-Konfigurations-Tuning und SQL Query Tuning vor und üben die einzelnen Punkte praktisch.

Im Galera Cluster Seminar nehmen wir alle Aspekte des Aufbaus und des Betriebs eines Galera Clusters durch und praktizieren das Ganz anschliessend ausführlich.

Den Umständen entsprechend werden die Seminare entweder vor Ort, remote oder hybrid durchgeführt.

Bei allfälligen Fragen bitten wir Sie, mit uns oder unseren Schulungspartnern Kontakt aufzunehmen und diese zu klären.

Taxonomy upgrade extras: trainingschulungseminarmysqlmariadbgalera2022replikation

From 25 to 29 October 2021 (calendar week 43) we will have another MariaDB / MySQL advanced training in the Linuxhotel in Essen (Germany). The training is in German and will take place on-site (3G!). There are still some places free!

More details about the training you can find here.

From 25 to 29 October 2021 (calendar week 43) we will have another MariaDB / MySQL advanced training in the Linuxhotel in Essen (Germany). The training is in German and will take place on-site (3G!). There are still some places free!

More details about the training you can find here.

Nearly everybody does backups. But nobody needs backups! What everybody wants and needs is a working restore not a working backup...

So how to make sure that your backup is working for the restore? There are a few things you can do already during your backup:

• Check that your backup was running fine. For example by checking the return code of your backup.
• Check the runtime of your backup. If the runtime of your backup significantly changed, it is worth to have a closer look at the backup.
• Check the size of your backup. If the size of your backup significantly changed, it is worth to have a closer look at your backup.
• And finally make your monitoring system aware if the backup was NOT running at all and if you are sure your backup is really triggered...

Backup test with FromDual Enterprise Tools

All this functionality is integrated in the newest releases of FromDual Backup and Recovery Manager for MariaDB and MySQL (brman) and the FromDual Performance Monitor for MariaDB and MySQL (fpmmm) in combination with the great monitoring solution Zabbix.

You have to run Backup Manager with the options --fpmmm-hostname and --fpmmm-cache-file:

shell> bman --target=brman:secret@127.0.0.1:3306 --type=full --policy=daily \ --fpmmm-hostname=mariadb-106 --fpmmm-cache-file=/var/cache/fpmmm/fpmmm.FromDual.mariadb-106.cache

then Backup Manager knows that it has to collect the metrics: return code, backup time and backup size and deposits them for the next Performance Monitor run. The next fpmmm run then will automatically gather these data and send it to the Zabbix Server.

In the Zabbix Monitor then you can see how your backup behaves:

and you get alerts if backups failed or did not even happen:

Restore Tests with FromDual Enterprise Tools

If your backup was running fine, returned with a zero return code, had the usual size and was running in a normal amount of time it does not necessarily mean that you also can do a restore with this backup!

For the sissies among us we also should do a restore test. To justify restore tests we have worked out 2 different concepts:

Staging systems restore test

Many of us have different stages of systems: Production, Quality Assurance, Integration, Testing and Development. All those systems need to be provisioned from time to time with new and fresh data from production. These systems would be ideal candidates for your daily restore tests. Also here the FromDual Enterprise Tools can help you: The backup, done with Backup Manager is shipped automatically to the other system where the Recovery Manager is restoring the database. Booth tools report their results to the Performance Monitor which sends the results to Zabbix. And Zabbix sends complains to the administrators if the backup or the restore did not happen or failed:

Spider system restore test

If you have many systems to do restore test or if you do not want to expose your precious data to other stages than production we recommend you the restore spider concept: Every production database does its daily backup on its local backup store. And additionally you have a centralized restore system which has access to the backup of each database. So this central spider restore system grabs now the backup of each database one after the other and does a restore on its restore system. The result will be reported to the Performance Monitor again and you get notice by Zabbix is the Restore did not happen, not work or was talking longer than a predefined time:

Taxonomy upgrade extras: BackupRestorebrmanfpmmmmonitoring

FromDual has the pleasure to announce the release of the new version 2.2.4 of its popular Backup and Recovery Manager for MariaDB and MySQL (brman).

The new FromDual Backup and Recovery Manager can be downloaded from here. The FromDual Repositories were updated. How to install and use the Backup and Recovery Manager is described in FromDual Backup and Recovery Manager (brman) installation guide.

In the inconceivable case that you find a bug in the FromDual Backup and Recovery Manager please report it to the FromDual Bugtracker or just send us an email.

Any feedback, statements and testimonials are welcome as well! Please send them to feedback@fromdual.com.

Upgrade from 2.x to 2.2.4 shell> cd ${HOME}/product shell> tar xf /download/brman-2.2.4.tar.gz shell> rm -f brman shell> ln -s brman-2.2.4 brman
Changes in FromDual Backup and Recovery Manager 2.2.4

This release is a new minor release. It contains mainly bug fixes. We have tried to maintain backward-compatibility with the 1.2, 2.0 and 2.1 release series. But you should test the new release seriously!

You can verify your current FromDual Backup Manager version with the following command:

shell> fromdual_bman --version shell> bman --version shell> rman --version
General

• Redhat 8 RPM package added to FromDual repository.
• Stripped unnecessary files from tarball.
• From now on we fully support MySQL 5.7 to 8.0.
• From now on we fully support PHP 8.
• From now on we fully support MariaDB 10.3 to 10.6.
• All old PHP 5 stuff was removed.
• Library from myEnv updated.
• Distribution distinguishing code was cleaned-up and Ubuntu, Rocky Linux, AlmaLinux and CloudLinux should pass checks correctly now.

FromDual Backup Manager

• Schema backup was not ignoring SYS schema when doing test for non-transactional (Aria) tables. This test is done correct now.

FromDual Recovery Manager

• Restore return code and restore time hook added for integration into fpmmm monitoring. Automated restore tests are better supported now.

FromDual brman Catalog

• none

FromDual brman Data masking / data obfuscating

• none

Testing

• All tests passed for MySQL 8.0.
• All tests passed with PHP 8.
• Various tests added.
• All tests passed for MariaDB 10.6.

Subscriptions for commercial use of FromDual Backup and Recovery Manager you can get from from us.

Taxonomy upgrade extras: BackupRestoreRecoverypitrbrmanreleasebmanrmanFromDual Backup and Recovery Manager

Table of Contents

• What is SNMP?
• SNMP Agent (snmptrap)
• SNMP Manager (snmptrapd)
• Test the SNMP Agent
• Creating your own MIB
• Sending MariaDB SNMP traps from PHP
• Literature

What is SNMP?

A customer recently had the question if an how his MariaDB database can be easily monitored with SNMP?

SNMP means Simple Network Management Protocol. It is a widely used and standardized protocol for monitoring the health of network and other devices (including services). In principle you can monitor nearly everything with SNMP.

On Linux a common implementation of SNMP is Net-SNMP, a suite of applications used to implement SNMP v1, SNMP v2c and SNMP v3 using both IPv4 and IPv6.

SNMP is a typical client-server architecture: The client which is collecting and sending the monitoring data is called agent and the server collecting all the monitoring data is called manager.

Source: Wikipedia: SNMP

An agent can be polled by the manager to collect the monitoring data (Request/Responses) or it can send monitoring data on its own (Trap). The latter one is called a SNMP Trap.

Source: Cisco: Understanding Simple Network Management Protocol (SNMP) Traps

Each measuring event type get its own Object Identifier (OID) which looks for example as follows: 1.3.6.1.4.1.57800.1.1.1. This is a representation of a tree hierarchy called MIB (Management Information Base):

Source: DPS Telecom SNMP OID: Introduction for Industry Professionals

An OID can also be represented in a human readable textual form which looks for example as follows: FromDual-fpmmm-MIB::fpmmmStart

SNMP Agent (snmptrap)

In this project we concentrate on the SNMP trap agent (snmptrap). Which sends an asynchronous notification to the manager (snmptrapd). To install it on Debian Linux you first have to activate the Debian non-free repository:

$ echo 'deb http://ftp.us.debian.org/debian/ buster main non-free' >> /etc/apt/sources.list.d/non-free.list $ apt-get update $ apt-get install snmp snmp-mibs-downloader

These 2 packages contain:

snmpSNMP (Simple Network Management Protocol) applicationssnmp-mibs-downloaderinstall and manage Management Information Base (MIB) files

To accept and load the MIBs the configuration has to be adapted. It is made so complicated because of some legal reasons:

$ sed -i 's/mibs :/# mibs :/g' /etc/snmp/snmp.conf
SNMP Manager (snmptrapd)

There are 2 different types of SNMP managers. The SNMP daemon (snmpd) and the SNMP trap daemon (snmptrapd). We concentrate on the later one in this project. To install it on Debian Linux you first have to activate the Debian non-free repository:

$ echo 'deb http://ftp.us.debian.org/debian/ buster main non-free' >> /etc/apt/sources.list.d/non-free.list $ apt-get update $ apt-get install snmptrapd snmp-mibs-downloader

These 2 packages contain:

snmptrapdNet-SNMP notification receiversnmp-mibs-downloaderinstall and manage Management Information Base (MIB) files

To accept and load the MIBs the configuration has to be adapted. It is so complicated because of some legal problems:

$ sed -i 's/mibs :/# mibs :/g' /etc/snmp/snmp.conf $ sed -i 's/export MIBS=/# export MIBS=/g' /etc/default/snmpd

For our tests we use the following configuration file:

# cat /etc/snmp/snmptrapd.conf disableAuthorization yes authCommunity log,execute,net public createUser myuser MD5 mypassword DES myotherpassword [snmp] logOption s 2 [snmp] logOption f /var/log/snmptrapd-direct.log format2 %V\n% Agent Address: %A \n Agent Hostname: %B \n Date: %H - %J - %K - %L - %M - %Y \n Enterprise OID: %N \n Trap Type: %W \n Trap Sub-Type: %q \n Community/Infosec Context: %P \n Uptime: %T \n Description: %W \n PDU Attribute/Value Pair Array:\n%v \n -------------- \n _EOF

Then the SNMP trap daemon has to be (re-)started:

$ systemctl start snmptrapd.service

The log messages then can be found in /var/log/snmptrapd-direct.log or otherwise like this: grep snmptrap /var/log/syslog.

If you write your own MIBs they can be located here: /usr/share/snmp/mibs.

Test the SNMP Agent

An SNMP trap is send as follows:

$ COMMUNITY='public' $ MANAGER='192.168.56.102' $ PORT='162' $ TRAP_OID='1.3.6.1.4.1.57800.1.1.2' $ OID='1.3.6.1.4.1.57800.1.1.1' $ TYPE='c' $ VALUE=$(date "+%s") $ snmptrap -v 2c -c ${COMMUNITY} ${MANAGER}:${PORT} '' ${TRAP_OID} ${OID} ${TYPE} "${VALUE}"

And then you will see in the SNMP trap daemon error log:

Agent Address: 0.0.0.0 Agent Hostname: chef.rebenweg Date: 22 - 7 - 20 - 30 - 1 - 4461326 Enterprise OID: . Trap Type: Cold Start Trap Sub-Type: 0 Community/Infosec Context: TRAP2, SNMP v2c, community public Uptime: 0 Description: Cold Start PDU Attribute/Value Pair Array: DISMAN-EVENT-MIB::sysUpTimeInstance = Timeticks: (72136167) 8 days, 8:22:41.67 SNMPv2-MIB::snmpTrapOID.0 = OID: 1.3.6.1.4.1.57800.1.1.2 1.3.6.1.4.1.57800.1.1.1 = Counter32: 1628864995

Or more MariaDB specific:

$ VALUE=$(mariadb --user=root --execute="SELECT variable_value FROM information_schema.global_status WHERE variable_name LIKE 'threads_running'\G" | grep variable_value | cut -d' ' -f2) $ snmptrap -v 2c -c ${COMMUNITY} ${MANAGER}:${PORT} '' ${TRAP_OID} ${OID} ${TYPE} "${VALUE}"
Creating your own MIB

How you write your own MIBs you can find here: Writing your own MIBs.

MIBs can/should be located under $HOME/.snmp/mibs or /usr/local/share/snmp/mibs. The MIB search path can be found with this command:

$ snmptranslate -Dinit_mib .1.3 2>&1 | grep MIBDIRS $ ll /root/.snmp/mibs /usr/share/snmp/mibs /usr/share/snmp/mibs/iana /usr/share/snmp/mibs/ietf /usr/share/mibs/site /usr/share/snmp/mibs /usr/share/mibs/iana /usr/share/mibs/ietf /usr/share/mibs/netsnmp

A tool for checking your MIB is smilint:

$ apt-get install smitools $ smilint snmp/FromDual-fpmmm-MIB.mib --level=6 snmp/FromDual-fpmmm-MIB.mib:90: warning: node `fpmmmLastrun' must be contained in at least one conformance group

If you want to extend the MIB search path you can modify the MIBDIRS environment variable:

$ export MIBDIRS=/home/oli/fromdual_devel/fpmmm/snmp:/home/oli/.snmp/mibs:/usr/share/snmp/mibs:/usr/share/snmp/mibs/iana:/usr/share/snmp/mibs/ietf:/usr/share/mibs/site:/usr/share/snmp/mibs:/usr/share/mibs/iana:/usr/share/mibs/ietf:/usr/share/mibs/netsnmp

To check if your MIB is correctly translated into an OID and vice versa you can use the tool snmptranslate:

$ snmptranslate -DFromDual-fpmmm-MIB.mib -m +FromDual-fpmmm-MIB 1.3.6.1.4.1.57800.1.1.1 registered debug token FromDual-fpmmm-MIB.mib, 1 FromDual-fpmmm-MIB::fpmmmLastrun $ snmptranslate -On FromDual-fpmmm-MIB::fpmmmLastrun .1.3.6.1.4.1.57800.1.1.1

And if the translation works you can send an SNMP trap with the MIB instead of the OID:

$ COMMUNITY='public' $ MANAGER='192.168.56.102' $ PORT='162' $ TRAP_OID="FromDual-fpmmm-MIB::fpmmmStart" $ OID="FromDual-fpmmm-MIB::fpmmmLastrun" $ TYPE='c' $ VALUE=$(date "+%s") $ snmptrap -v 2c -c ${COMMUNITY} ${MANAGER}:${PORT} '' ${TRAP_OID} ${OID} ${TYPE} "${VALUE}"

and this should also be translated correctly in the snmptrapd error log:

Agent Address: 0.0.0.0 Agent Hostname: chef.rebenweg Date: 22 - 7 - 20 - 30 - 1 - 4461326 Enterprise OID: . Trap Type: Cold Start Trap Sub-Type: 0 Community/Infosec Context: TRAP2, SNMP v2c, community public Uptime: 0 Description: Cold Start PDU Attribute/Value Pair Array: DISMAN-EVENT-MIB::sysUpTimeInstance = Timeticks: (72176488) 8 days, 8:29:24.88 SNMPv2-MIB::snmpTrapOID.0 = OID: FromDual-fpmmm-MIB::fpmmmStart FromDual-fpmmm-MIB::fpmmmLastrun = Counter32: 1628864995
Sending MariaDB SNMP traps from PHP

It looks like the PHP native SNMP functions do not provide anything for sending SNMP traps. But luckily there is the FreeSDx/SNMP PHP library by ChadSikorra on GitHub which can do the job.

After installing PHP composer installing of the FreeDSx/SNMP library was no problem:

$ php composer.phar require freedsx/snmp Using version ^0.4.0 for freedsx/snmp ./composer.json has been updated Running composer update freedsx/snmp Loading composer repositories with package information Updating dependencies Lock file operations: 3 installs, 0 updates, 0 removals - Locking freedsx/asn1 (0.4.4) - Locking freedsx/snmp (0.4.0) - Locking freedsx/socket (0.3.1) Writing lock file Installing dependencies from lock file (including require-dev) Package operations: 3 installs, 0 updates, 0 removals - Downloading freedsx/socket (0.3.1) - Downloading freedsx/asn1 (0.4.4) - Downloading freedsx/snmp (0.4.0) - Installing freedsx/socket (0.3.1): Extracting archive - Installing freedsx/asn1 (0.4.4): Extracting archive - Installing freedsx/snmp (0.4.0): Extracting archive 2 package suggestions were added by new dependencies, use `composer suggest` to see details. Generating autoload files

This we need for adding SNMP support to our FromDual Performance Monitor for MariaDB and MySQL (fpmmm). A simple PHP SNMP trap example you can find as follows:

$aAutoload = require_once('vendor/autoload.php'); use FreeDSx\Snmp\SnmpClient; use FreeDSxph\Snmp\Exception\SnmpRequestException; use FreeDSx\Snmp\Oid; $snmp = new SnmpClient([ 'host' => '192.168.56.102' , 'community' => 'public' , 'version' => 2 , 'port' => 162 , ]); try { $date = time(); $trapOid = '1.3.6.1.4.1.57800.1.1.2'; // FromDual-fpmmm-MIB::fpmmmStart $Oid = '1.3.6.1.4.1.57800.1.1.1'; // FromDual-fpmmm-MIB::fpmmmLastrun # The parameters are: # 1. The system uptime (in seconds) # 2. The trap OID # 3. The OIDs and their values $snmp->sendTrap(60, $trapOid, Oid::fromCounter($Oid, $date)); } catch ( SnmpRequestException $e ) { printf('Unable to send trap: %s', $e->getMessage()); }
Literature

• RFC 1157: A Simple Network Management Protocol (SNMP)
• RFC 1213: Management Information Base for Network Management of TCP/IP-based internets: MIB-II
• RFC 2580: Conformance Statements for SMIv2
• IANA: Structure of Management Information (SMI) Numbers (MIB Module Registrations)
• IANA: Private Enterprise Number (PEN) Request
• Net-SNMP: Writing your own MIBs
• Net-SNMP: Using and loading MIBS tutorial
• Net-SNMP: Examples MIB definitions
• Net-SNMP: snmptrap tutorial
• Net-SNMP: snmptrap man pages
• Net-SNMP: snmpcmd man pages
• Net-SNMP: snmptrapd man pages
• Linux Journal: SNMP
• Ubuntu-Users: SNMP
• Wikipedia: SNMP
• SNMP tutorial
• What is SNMP?
• Cisco: Understanding Simple Network Management Protocol (SNMP) Traps

Taxonomy upgrade extras: SNMPmonitoring

FromDual has the pleasure to announce the release of the new version 2.0.3 of its popular MariaDB, Galera Cluster and MySQL multi-instance environment MyEnv.

The new MyEnv can be downloaded here. How to install MyEnv is described in the MyEnv Installation Guide.

In the inconceivable case that you find a bug in the MyEnv please report it to the FromDual bug tracker.

Any feedback, statements and testimonials are welcome as well! Please send them to feedback@fromdual.com.

Upgrade from 1.1.x to 2.0

Please look at the MyEnv 2.0.0 Release Notes.

Upgrade from 2.0.x to 2.0.3 shell> cd ${HOME}/product shell> tar xf /download/myenv-2.0.3.tar.gz shell> rm -f myenv shell> ln -s myenv-2.0.3 myenv
Plug-ins

If you are using plug-ins for showMyEnvStatus create all the links in the new directory structure:

shell> cd ${HOME}/product/myenv shell> ln -s ../../utl/oem_agent.php plg/showMyEnvStatus/
Upgrade of the instance directory structure

From MyEnv 1.0 to 2.0 the directory structure of instances has fundamentally changed. Nevertheless MyEnv 2.0 works fine with MyEnv 1.0 directory structures.

Changes in MyEnv 2.0.3 MyEnv

• MariaDB 10.6 my.cnf hash added and bug in stop instance for fqdn fixed.
• Function print replaced by output() function.
• Function checkDataseOld function removed.
• Functions checkDatabase, startDatabase and stopDatabase replaced by their Instance analogon.
• Feature: CDPATH added to variables.conf.template pointing to instancedir.
• Function print replaced by output in function checkMyEnvRequirements.
• log_warnings commented out in my.cnf.template to make it work with MySQL 8.0.
• Debug information improved.
• my.cnf updated with new 8.0 findings.
• Ubuntu tag fixed.
• Variable gcache.recover added to template.
• Error handling changed from procedural style to OO style.
• Naming of return values fixed.
• Function my_exec made variable naming more clear.
• InnoDB monitor enabled by default.
• Constants made more human readable.
• Variable sync_binlog in my.cnf template adjusted to reasonable value.
• my.cnf template updated to newest state.
• New MySQL 5.7 Ubuntu repo hash for my.cnf added.
• Package php-posix replaced by php-process on Redhat/CentOS.
• Branch Homebrew added to extract Branch for MacOS.
• Function formatTime added.
• Comments changed from php5 to php7.
• New MySQL 8.0 tmp schema #innodb_temp added to hideschema parameter.
• Socket is right now when port is not 3306.
• New interfaces for start/stopping instances and code cleaned up.

MyEnv Installer

• Skipping init script is possible now in installMyEnv.
• Instance proposal is now checked if it already exists in add instance in installMyEnv.
• On Debian systems now systemd unit file advice is shown as well instead of initV files advice in installMyEnv.

MyEnv Utilities

• Using a password on the command line hack replace by better solution for some utilities.
• Utility pcs_standby_node.sh added.
• Utilities: connect_times.php and galera_monitor.sh added.
• Utilities: mixed_test.php, binlog_push.php and binlog_apply.php added.
• VIP v2 start stop script added (vip2.sh).
• Utility netstat.php added.
• Utility insert_test.bat added and is working now like on Linux.
• insert_test.sh made more flexible to run modify column command.
• insert_test.php made ready for new 5.7/8.0 timestamp behaviour.
• Utility connect_test.php added.
• MySQL Group Replication monitor mad configurable and output made nicer.

For subscriptions of commercial use of MyEnv please get in contact with us.

Taxonomy upgrade extras: MyEnvmulti-instancevirtualizationconsolidationSaaSOperationsreleasemysqld_multi

Storage Engines like InnoDB, Aria and MyISAM are Row Stores. They store rows one after the other in blocks or even directly in a single file (MyISAM). On the other hand a Column Store like MariaDB Column Store stores all the same attributes (columns) of the rows together in chunks.

This is how the table sales_fact looks like:

CREATE TABLE `sales_fact` ( `product_id` int(11) NOT NULL, `time_id` int(11) NOT NULL, `customer_id` int(11) NOT NULL, `promotion_id` int(11) NOT NULL, `store_id` int(11) NOT NULL, `store_sales` decimal(10,2) NOT NULL, `store_cost` decimal(10,4) NOT NULL, `unit_sales` int(11) NOT NULL ) ENGINE=ColumnStore DEFAULT CHARSET=utf8; CREATE TABLE `sales_fact` ( `product_id` int(11) NOT NULL, `time_id` int(11) NOT NULL, `customer_id` int(11) NOT NULL, `promotion_id` int(11) NOT NULL, `store_id` int(11) NOT NULL, `store_sales` decimal(10,2) NOT NULL, `store_cost` decimal(10,4) NOT NULL, `unit_sales` int(11) NOT NULL, KEY `i_sales_customer_id` (`customer_id`), KEY `i_sales_product_id` (`product_id`), KEY `i_sales_promotion_id` (`promotion_id`), KEY `i_sales_store_id` (`store_id`), KEY `i_sales_time_id` (`time_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

As you may have noted the Column Store table does NOT contain indexes!

And this shows how it is stored in memory and on disk (left Row Store, right Column Store; in reality it is a bit more complicated):

     

Considering this and depending on your queries a Column Store can have some significant response time advantages compared to a Row Store. And the other way around as well!

Comparison with MyISAM

So let us compare some queries with the same set of data. We are still working with one single-node MariaDB Column Store as described here.

We have chosen MyISAM for comparison with MariaDB Column Store because it is the fastest storage engine we have so far for low concurrency SELECT statements. As data set we have used the good old foodmart schema prepared for Column Store. We concentrated on the sales_fact table because this is the table with the biggest amount of rows. The MyISAM key_buffer_size was set to 128 Mibyte which is big enough for all the indices.

Simple SELECT queries SELECT COUNT(*) FROM table

As experienced MyISAM users know already for this query MyISAM is unbeatable fast:

SQL> SELECT COUNT(*) FROM foodmart_cs.sales_fact; 1 row in set (0.040 sec) SQL> SELECT COUNT(*) FROM foodmart_myisam.sales_fact; 1 row in set (0.000 sec)

This query is mainly to make sure that both tables have the same amount of data...

SELECT * FROM table or CHECKSUM TABLE table

In these queries we do a full table scans. This is probably the worst pattern you can do to Column Store. And MyISAM is significantly faster in this:

SQL> SELECT * FROM foodmart_cs.sales_fact; 1078880 rows in set (1.833 sec) SQL> SELECT * FROM foodmart_myisam.sales_fact; 1078880 rows in set (0.607 sec)

But this pattern is NOT what a Column Store is made for...

The Column Store architecture consists of 2 different types of modules: The User Module (UM) consisting of the MariaDB Server (mysqld), the Execution Manager (ExeMgr) and the Distribution Managers (DMLProc, DDLProc and cpimport). These processes are responsible for parsing SQL and distributing and executing the SQL statements.

The other type of module is the Performance Module (PM) consisting of the Managing and Monitoring Process (ProcMgr and ProcMon), the Primary Process (PrimProc) which handles the query execution and the Performance Module process which handles loads and writes (WriteEngineServer and cpimport). The Performance Module basically performs the work and does the I/O operations.

So we have the communication from the MariaDB server to the User Module to the Performance Module and this in normal situation over a network. So it is obvious that this costs a lot of time for huge data sets.

This can be shown when we execute the CHECKSUM TABLE command which does similar things like SELECT * FROM table but does NOT return the full result set:

SQL> CHECKSUM TABLE foodmart_cs.sales_fact; +------------------------+------------+ | Table | Checksum | +------------------------+------------+ | foodmart_cs.sales_fact | 2218293488 | +------------------------+------------+ 1 row in set (1.370 sec) SQL> CHECKSUM TABLE foodmart_myisam.sales_fact; +----------------------------+------------+ | Table | Checksum | +----------------------------+------------+ | foodmart_myisam.sales_fact | 2218293488 | +----------------------------+------------+ 1 row in set (0.853 sec)

It is interesting that Column Store is less slower here compared to the full table scan. It looks like it can already parallelize some of the work in this step already?

SELECT min(column), max(column) FROM table

Now let us come to more data warehouse (DWH) like queries:

SQL> SELECT MIN(time_id), MAX(time_id) FROM foodmart_cs.sales_fact; 1 row in set (0.104 sec) SQL> SELECT MIN(time_id), MAX(time_id) FROM foodmart_myisam.sales_fact; 1 row in set (0.001 sec)

Also here MyISAM is horribly fast because it can short cut:

SQL> EXPLAIN SELECT MIN(time_id), MAX(time_id) FROM foodmart_myisam.sales_fact; +------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+ | id | SELECT_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away | +------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+

It would be interesting to compare those queries with InnoDB which cannot do these short cuts.

SELECT column, COUNT(*) FROM table GROUP BY column

This is a query where MyISAM cannot shortcut and we see already an advantage for MariaDB Columns Store.

SQL> SELECT time_id, COUNT(*) FROM foodmart_cs.sales_fact GROUP BY time_id; 673 rows in set (0.099 sec) SQL> SELECT time_id, COUNT(*) FROM foodmart_myisam.sales_fact GROUP BY time_id; 673 rows in set (0.138 sec)

It would be interesting to see those number with really huge amount of rows (109 to 1010 rows) which do not fit into caches any more...

SELECT column, COUNT(*) FROM table GROUP BY column ORDER BY column

The last result was pretty much chaotic. So let us test an ordered result:

SQL> SELECT time_id, COUNT(*) FROM foodmart_cs.sales_fact GROUP BY time_id ORDER BY time_id; 673 rows in set (0.100 sec) SQL> SELECT time_id, COUNT(*) FROM foodmart_myisam.sales_fact GROUP BY time_id ORDER BY time_id; 673 rows in set (0.136 sec)

The ORDER BY is probably executed in the MariaDB Server. So for this small data set the network communication can be ignored. No difference observed.

SELECT COUNT(*) FROM table where column >= value

This SELECT queries data from a very big range (99%). We know MyISAM uses the index by doing an index-only-scan. MariaDB Column Store does NOT have indexes:

SQL> SELECT COUNT(*) FROM foodmart_cs.sales_fact WHERE time_id >= 400; 1 row in set (0.054 sec) SQL> SELECT COUNT(*) FROM foodmart_myisam.sales_fact WHERE time_id >= 400; 1 row in set (0.159 sec) SQL> EXPLAIN SELECT COUNT(*) FROM foodmart_myisam.sales_fact WHERE time_id >= 400; +------+-------------+------------+-------+-----------------+-----------------+---------+------+---------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+------------+-------+-----------------+-----------------+---------+------+---------+--------------------------+ | 1 | SIMPLE | sales_fact | range | i_sales_time_id | i_sales_time_id | 4 | NULL | 1055594 | Using where; Using index | +------+-------------+------------+-------+-----------------+-----------------+---------+------+---------+--------------------------+

Column Store outperforms MyISAM by factor of 3 already with a small data set.

The same query with a very small range of data (1%):

SQL> SELECT COUNT(*) FROM foodmart_cs.sales_fact WHERE time_id >= 1090; 1 row in set (0.042 sec) SQL> SELECT COUNT(*) FROM foodmart_myisam.sales_fact WHERE time_id >= 1090; 1 row in set (0.005 sec)

Column Store becomes only slightly faster with the smaller result set but here MyISAM key usage has a dramatic impact. Would be interesting to see the difference if the MyISAM data/key cannot be kept in memory any more.

SELECT SUM(column3) FROM table WHERE column1 = value AND column2 BETWEEN value AND value SQL> SELECT SUM(store_sales), SUM(unit_sales) FROM foodmart_cs.sales_fact WHERE customer_id = 42 AND time_id BETWEEN 300 AND 1000; 1 row in set (0.072 sec) SQL> SELECT SUM(store_sales), SUM(unit_sales) FROM foodmart_myisam.sales_fact WHERE customer_id = 42 AND time_id BETWEEN 300 AND 1000; 1 row in set (0.002 sec)

As soon a MyISAM has an index with high cardinality and a strong filter it outperforms Column Store.

Let us go a step back to the query with the big range (99%). But forcing MyISAM to do a table access instead of an index-only-scan:

SQL> SELECT SUM(store_sales) FROM foodmart_cs.sales_fact WHERE time_id >= 400; 1 row in set (0.117 sec) SQL> SELECT SUM(store_sales) FROM foodmart_myisam.sales_fact WHERE time_id >= 400; 1 row in set (0.133 sec)

Ohh! Here the MariaDB optimizer was clever enough to see that a full table scan is cheaper than accessing the index:

SQL> EXPLAIN SELECT SUM(store_sales) FROM foodmart_myisam.sales_fact WHERE time_id >= 400; +------+-------------+------------+------+-----------------+------+---------+------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+------------+------+-----------------+------+---------+------+---------+-------------+ | 1 | SIMPLE | sales_fact | ALL | i_sales_time_id | NULL | NULL | NULL | 1078880 | Using where | +------+-------------+------------+------+-----------------+------+---------+------+---------+-------------+

So MyISAM is only slightly slower than Column Store. But if we force MyISAM to use the index it becomes dramatically slow (about 9 times):

SQL> SELECT SUM(store_sales) FROM foodmart_myisam.sales_fact FORCE INDEX (i_sales_time_id) WHERE time_id >= 400; 1 row in set (1.040 sec)

So here again: MariaDB Column store starts making fun if a huge amount of data is used...

SELECT SUM(column3) FROM table WHERE column1 = value OR column2 = value

We know that WHERE clauses with OR are always bad for the optimizer. So let us try this:

SQL> SELECT SUM(store_sales), SUM(unit_sales) FROM foodmart_cs.sales_fact WHERE promotion_id = 0 OR store_id = 13; 1 row in set (0.209 sec) MariaDB [(none)]> SELECT calGetTrace()\G *************************** 1. row *************************** calGetTrace(): Desc Mode Table TableOID ReferencedColumns PIO LIO PBE Elapsed Rows BPS PM sales_fact 4995 (promotion_id,store_id,store_sales,unit_sales) 0 3163 0 0.172 132 TAS UM - - - - - - 0.153 1 TNS UM - - - - - - 0.000 1

Column Store has to touch 4 out of 8 column (50%) to get the result. How the Elapsed time is calculated I have to investigate some more...

SQL> SELECT SUM(store_sales), SUM(unit_sales) FROM foodmart_myisam.sales_fact WHERE promotion_id = 0 OR store_id = 13; 1 row in set (0.873 sec) SQL> EXPLAIN SELECT SUM(store_sales), SUM(unit_sales) FROM foodmart_myisam.sales_fact WHERE promotion_id = 0 OR store_id = 13; +------+-------------+------------+-------------+---------------------------------------+---------------------------------------+---------+------+--------+-----------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+------------+-------------+---------------------------------------+---------------------------------------+---------+------+--------+-----------------------------------------------------------------+ | 1 | SIMPLE | sales_fact | index_merge | i_sales_promotion_id,i_sales_store_id | i_sales_promotion_id,i_sales_store_id | 4,4 | NULL | 706357 | Using union(i_sales_promotion_id,i_sales_store_id); Using where | +------+-------------+------------+-------------+---------------------------------------+---------------------------------------+---------+------+--------+-----------------------------------------------------------------+

MyISAM tries to short cut by an INDEX MERGE operation which we know is not super fast but still better than a full table scan. But still MariaDB Column Store is about 4 times faster than MyISAM.

More complex SELECT queries from DWH benchmarks.

I borrowed these queries from Øystein's blog:

SELECT SUM(sf.store_sales) AS tot_yearly FROM foodmart_cs.sales_fact AS sf JOIN foodmart_cs.store AS s ON s.store_id = sf.store_id WHERE s.store_name = 'Store 13' AND sf.unit_sales < (SELECT 0.2 * AVG(isf.unit_sales) FROM foodmart_cs.sales_fact AS isf WHERE isf.store_id = s.store_id ) ; ERROR 1815 (HY000): Internal error: IDB-3012: Scalar filter and semi join are not from the same pair of tables. SELECT SUM(sf.store_sales) AS tot_yearly FROM foodmart_myisam.sales_fact AS sf JOIN foodmart_myisam.store AS s ON s.store_id = sf.store_id WHERE s.store_name = 'Store 13' AND sf.unit_sales < (SELECT 0.2 * AVG(isf.unit_sales) FROM foodmart_myisam.sales_fact AS isf WHERE isf.store_id = s.store_id ) ; 1 row in set (0.184 sec)

So some more complex queries seems not to work with Column Store yet. I have to figure out yet what to do in this case...

So let us try some more complex queries from Sergei:

SELECT SUM(store_sales) FROM foodmart_cs.sales_fact AS sf JOIN foodmart_cs.customer AS c ON c.customer_id = sf.customer_id WHERE c.total_children BETWEEN 1 AND 2 AND sf.unit_sales BETWEEN 2 AND 4 ; 1 row in set, 1 warning (0.210 sec)

We get a warning when using JOINs:

SQL> show warnings; +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Note | 9999 | Query Stats: MaxMemPct-0; NumTempFiles-0; TempFileSpace-0B; ApproxPhyI/O-0; CacheI/O-2651; BlocksTouched-2651; PartitionBlocksEliminated-0; MsgBytesIn-41KB; MsgBytesOut-74KB; Mode-Distributed | +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Seems to be some statistics about how the query/join was executed...

SELECT SUM(store_sales) FROM foodmart_myisam.sales_fact AS sf JOIN foodmart_myisam.customer AS c ON c.customer_id = sf.customer_id WHERE c.total_children BETWEEN 1 AND 2 AND sf.unit_sales BETWEEN 2 AND 4 ; 1 row in set (0.412 sec)

With JOINs I was a bit more sceptic because of my former experience with NDB but it looks like also with JOINs Column Store outperforms MyISAM with already a small amount of data.

The table customer is a typical dimension table so this would probably be a candidate for hybrid approach (HTAP)?

Converting the customer table to MyISAM did NOT make it significantly slower:

1 row in set, 1 warning (0.215 sec) +------+---------------+-------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+---------------+-------+------+---------------+------+---------+------+------+-------+ | 1 | PUSHED SELECT | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | +------+---------------+-------+------+---------------+------+---------+------+------+-------+ Desc Mode Table TableOID ReferencedColumns PIO LIO PBE Elapsed Rows CES UM - - - - - - 0.013 4093 BPS PM sf 4995 (customer_id,store_sales,unit_sales) 0 2636 0 0.182 132 HJS PM sf-c 4995 - - - - ----- - TAS UM - - - - - - 0.153 1 TNS UM - - - - - - 0.000 1
Converting the foodmart DWH schema to MariaDB Columns Store

Converting the existing foodmart schema to MariaDB Column Store was a bit cumbersome...

We used the normal mysqldump and replace the storage engine:

$ zcat foodmart_dump.sql.gz | sed 's/ENGINE=InnoDB/ENGINE=ColumnStore/' | mysql --user=root foodmart_cs ERROR 1178 (42000) at line 25: The storage engine for the table doesn't support The syntax or the data type(s) is not supported by Columnstore. Please check the Columnstore syntax guide for supported syntax or data types.

The command ALTER TABLE ... ENGINE = ColumnStore seems to be a bit more flexible but it is horribly slow...

The foodmart dump optimized for MariaDB Column Store can be found here.

Problems between MariaDB server and Column Store dictionary SQL> RENAME TABLE customer TO customer_a; Query OK, 0 rows affected (0.137 sec) SQL> CREATE TABLE customer LIKE customer_a; Query OK, 0 rows affected (0.018 sec) SQL> ALTER TABLE customer ENGINE = ColumnStore; ERROR 1815 (HY000): Internal error: CAL0009: Internal create table error for foodmart_cs.customer : table already exists (your schema is probably out-of-sync) SQL> DROP TABLE customer; Query OK, 0 rows affected (0.014 sec) SQL> ALTER TABLE customer ENGINE = ColumnStore; ERROR 1146 (42S02): Table 'foodmart_cs.customer' doesn't exist SQL> CREATE TABLE customer LIKE customer_a; Query OK, 0 rows affected (0.017 sec) SQL> ALTER TABLE customer ENGINE = ColumnStore; ERROR 1815 (HY000): Internal error: CAL0009: Internal create table error for foodmart_cs.customer : table already exists (your schema is probably out-of-sync)

To fix this the following DDL command will help:

SQL> CREATE TABLE customer (id INT) ENGINE = ColumnStore COMMENT='SCHEMA SYNC ONLY'; Query OK, 0 rows affected (0.006 sec)

See also: Recovering from error "Cannot create table: table already exists (your schema is probably out-of-sync)"

Conclusion

MariaDB Column Store can outperform MyISAM already with a single-node set-up and a small dataset if you are choosing the right queries. It would be interesting to see the performance gains with a multi-node set-up and a huge data set. I was told that MariaDB Column Store makes sense from 100 Gibyte upwards... Our data set was about 100 Mibyte!

Taxonomy upgrade extras: mariadbdwhColumnStorequeryperformancemyisam

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 Column Store on the file system
• Creating our first table
• Querying and changing data in MariaDB ColumnStore
• MariaDB ColumnStore using sequences instead of AUTO_INCREMENT
• MariaDB Column Store 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, 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 better and thus a more efficient caching (in-memory) should result in faster response times for you reporting queries.

MariaDB Column Store can be deployed as a single-node or a multi-node set-up. The first one is considered for testing, the later one for production purposes.

For setting up a testing system I have chosen a single-node set-up on a VirtualBox VM first.

MariaDB ColumnStore hardware requirements

There are different informations available about the minimal recommended hardware resources. The range lasts from 8 to 32 physical cores with 16 to 64 Gibyte of RAM, HDD disks are fine because the system is optimized for block streaming (sequential read and write). At least an 1 Gbit network is recommended. [ 1 ]

For our first test we rely on 1 virtual Machine with 4 Gibyte of RAM (possibly with 2 Gibyte of RAM it still would work?), 10 Gibyte of HDD disk space and 1 vCPU.

O/S (Linux) settings

Check Linux kernel settings:

$ sysctl -a | grep -e '^vm.swap' -e '^vm.vfs' -e '^net.core..mem_max' -e '^net.ipv4.tcp_.mem' -e '^net.ipv4.tcp_no_metrics' -e '^net.core.netdev_max' net.core.netdev_max_backlog = 1000 net.core.rmem_max = 212992 net.core.wmem_max = 212992 net.ipv4.tcp_no_metrics_save = 0 net.ipv4.tcp_rmem = 4096 131072 6291456 net.ipv4.tcp_wmem = 4096 16384 4194304 vm.swappiness = 60 vm.vfs_cache_pressure = 100

Check network speed:

$ ethtool enp0s9 | grep Speed Speed: 1000Mb/s

Persist the Linux kernel settings:

$ cat >/etc/sysctl.d/90-mariadb-enterprise-columnstore.conf <<_EOF # # /etc/sysctl.d/90-mariadb-enterprise-columnstore.conf # # minimize swapping vm.swappiness = 10 # optimize Linux to cache directories and inodes vm.vfs_cache_pressure = 10 # Increase the TCP max buffer size net.core.rmem_max = 16777216 net.core.wmem_max = 16777216 # Increase the TCP buffer limits # min, default, and max number of bytes to use net.ipv4.tcp_rmem = 4096 87380 16777216 net.ipv4.tcp_wmem = 4096 65536 16777216 # don't cache ssthresh from previous connection net.ipv4.tcp_no_metrics_save = 1 # for 1 GigE, increase this to 2500 # for 10 GigE, increase this to 30000 net.core.netdev_max_backlog = 2500 _EOF

and make them active:

$ sysctl --load=/etc/sysctl.d/90-mariadb-enterprise-columnstore.conf

Disable AppArmor (for installation only?):

$ systemctl status apparmor $ systemctl stop apparmor $ systemctl disable apparmor $ aa-status

Configure Character Enconding (why exactly?):

$ localedef -i en_US -f UTF-8 en_US.UTF-8
Installation of MariaDB ColumnStore

The first problem I had was finding a good an reliable information about where to download the software. Finally I ended up here: MariaDB ColumnStore download. But MariaDB ColumnStore is the wrong tab. You have to choose the MariaDB Community Server tab.

If you prefer, the software can also be downloaded from the command line:

$ wget https://dlm.mariadb.com/1623874/MariaDB/mariadb-10.5.10/repo/ubuntu/mariadb-10.5.10-ubuntu-bionic-amd64-debs.tar

and there are a lot of packages contained in this tar-ball:

$ tar xf mariadb-10.5.10-ubuntu-bionic-amd64-debs.tar -rw-rw-r-- 1 oli oli 9059256 May 7 04:34 galera-4_26.4.8-bionic_amd64.deb -rw-rw-r-- 1 oli oli 5955264 May 7 04:34 galera-arbitrator-4_26.4.8-bionic_amd64.deb -rw-rw-r-- 1 oli oli 1698 May 7 04:35 InRelease -rw-rw-r-- 1 oli oli 149208 May 7 04:34 libmariadb3_10.5.10+maria~bionic_amd64.deb -rw-rw-r-- 1 oli oli 3276 May 7 04:34 libmariadb3-compat_10.5.10+maria~bionic_amd64.deb -rw-rw-r-- 1 oli oli 3248 May 7 04:34 libmariadbclient18_10.5.10+maria~bionic_amd64.deb -rw-rw-r-- 1 oli oli 5130428 May 7 04:34 libmariadbd19_10.5.10+maria~bionic_amd64.deb -rw-rw-r-- 1 oli oli 6226020 May 7 04:34 libmariadbd-dev_10.5.10+maria~bionic_amd64.deb -rw-rw-r-- 1 oli oli 159556 May 7 04:34 libmariadb-dev_10.5.10+maria~bionic_amd64.deb -rw-rw-r-- 1 oli oli 3452 May 7 04:34 libmariadb-dev-compat_10.5.10+maria~bionic_amd64.deb -rw-rw-r-- 1 oli oli 3260 May 7 04:34 libmysqlclient18_10.5.10+maria~bionic_amd64.deb -rw-rw-r-- 1 oli oli 6483096 May 7 04:34 mariadb-backup_10.5.10+maria~bionic_amd64.deb -rw-rw-r-- 1 oli oli 4672 May 7 04:35 MariaDB-C74CD1D8-public.asc -rw-rw-r-- 1 oli oli 1604684 May 7 04:34 mariadb-client-10.5_10.5.10+maria~bionic_amd64.deb -rw-rw-r-- 1 oli oli 3088 May 7 04:34 mariadb-client_10.5.10+maria~bionic_all.deb -rw-rw-r-- 1 oli oli 784824 May 7 04:34 mariadb-client-core-10.5_10.5.10+maria~bionic_amd64.deb -rw-rw-r-- 1 oli oli 4428 May 7 04:34 mariadb-common_10.5.10+maria~bionic_all.deb -rw-rw-r-- 1 oli oli 5811572 May 7 04:34 mariadb-plugin-columnstore_10.5.10-5.5.2+maria~bionic_amd64.deb -rw-rw-r-- 1 oli oli 558288 May 7 04:34 mariadb-plugin-connect_10.5.10+maria~bionic_amd64.deb -rw-rw-r-- 1 oli oli 5216 May 7 04:34 mariadb-plugin-cracklib-password-check_10.5.10+maria~bionic_amd64.deb -rw-rw-r-- 1 oli oli 6172 May 7 04:34 mariadb-plugin-gssapi-client_10.5.10+maria~bionic_amd64.deb -rw-rw-r-- 1 oli oli 7592 May 7 04:34 mariadb-plugin-gssapi-server_10.5.10+maria~bionic_amd64.deb -rw-rw-r-- 1 oli oli 1187228 May 7 04:34 mariadb-plugin-mroonga_10.5.10+maria~bionic_amd64.deb -rw-rw-r-- 1 oli oli 63820 May 7 04:34 mariadb-plugin-oqgraph_10.5.10+maria~bionic_amd64.deb -rw-rw-r-- 1 oli oli 3122524 May 7 04:34 mariadb-plugin-rocksdb_10.5.10+maria~bionic_amd64.deb -rw-rw-r-- 1 oli oli 845428 May 7 04:34 mariadb-plugin-s3_10.5.10+maria~bionic_amd64.deb -rw-rw-r-- 1 oli oli 354276 May 7 04:34 mariadb-plugin-spider_10.5.10+maria~bionic_amd64.deb -rw-rw-r-- 1 oli oli 4562128 May 7 04:34 mariadb-server-10.5_10.5.10+maria~bionic_amd64.deb -rw-rw-r-- 1 oli oli 3216 May 7 04:34 mariadb-server_10.5.10+maria~bionic_all.deb -rw-rw-r-- 1 oli oli 6932836 May 7 04:34 mariadb-server-core-10.5_10.5.10+maria~bionic_amd64.deb -rw-rw-r-- 1 oli oli 3260 May 7 04:34 mysql-common_10.5.10+maria~bionic_all.deb -rw-rw-r-- 1 oli oli 42897 May 7 04:34 Packages -rw-rw-r-- 1 oli oli 2278 May 7 04:35 README -rw-rw-r-- 1 oli oli 816 May 7 04:34 Release -rw-rw-r-- 1 oli oli 833 May 7 04:35 Release.gpg -rwxrwxr-x 1 oli oli 1010 May 7 04:35 setup_repository*

Installation of MariaDB ColumnStore was straight forward:

$ apt-get install gnupg2 $ ./setup_repository $ apt-get update $ apt-get install mariadb-server mariadb-plugin-columnstore libjemalloc1 mariadb-backup mariadb-client
Configuration of MariaDB ColumnStore

The minimum recommended MariaDB configuration for ColumnStore looks like this:

$ cat >/etc/mysql/mariadb.conf.d/zz-fromdual.cnf <<_EOF # # /etc/mysql/mariadb.conf.d/zz-fromdual.cnf # [mariadb] log_error = mariadbd-error.log character_set_server = utf8 collation_server = utf8_general_ci columnstore_use_import_for_batchinsert = ALWAYS _EOF

In the MariaDB ColumnStore documentation you can find all other Server System Variables and Options (columnstore*).

Cross Engine Join User

For Cross Engine Joins a special user is required. These credentials are stored in the file /etc/columnstore/Columnstore.xml and can be queried as follows:

$ mcsGetConfig -v -a | grep Cross CrossEngineSupport.Host = 127.0.0.1 CrossEngineSupport.Port = 3306 CrossEngineSupport.User = root CrossEngineSupport.Password = CrossEngineSupport.TLSCA = CrossEngineSupport.TLSClientCert = CrossEngineSupport.TLSClientKey =

and changed as follows:

$ mcsSetConfig CrossEngineSupport Host 127.0.0.1 $ mcsSetConfig CrossEngineSupport Port 3306 $ mcsSetConfig CrossEngineSupport User cross_engine $ mcsSetConfig CrossEngineSupport Password secret

Caution: Querying the Cross Engine Join User password can be done by an non-privileged O/S user. Thus I consider this as a security relevant bug. This is known already since 2018 and should be fixed in version 5.6.1 :-( (MCOL-1175, MCOL-4714).

Start the ColumnStore Processes

Starting MariaDB ColumnStore after the configuration changes are quite intuitive:

$ systemctl restart mariadb $ systemctl restart mariadb-columnstore

After these commands the mariadbd and some ColumnStore processes are started:

$ ps aux | grep -e mysql -e VSZ USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND mysql 1380 0.0 2.4 1274896 99020 ? Ssl 11:20 0:00 /usr/sbin/mariadbd mysql 1499 0.0 0.4 267392 16904 ? Sl 11:21 0:00 /usr/bin/workernode DBRM_Worker1 mysql 1507 0.0 0.2 530448 11648 ? Sl 11:21 0:00 /usr/bin/controllernode mysql 1522 0.0 0.2 2391288 10560 ? Sl 11:21 0:00 /usr/bin/PrimProc mysql 1545 0.0 0.3 300192 14116 ? Sl 11:21 0:00 /usr/bin/WriteEngineServer mysql 1560 0.0 0.2 212452 10188 ? Sl 11:21 0:00 /usr/bin/ExeMgr mysql 1571 0.0 0.4 342364 18704 ? Sl 11:21 0:00 /usr/bin/DMLProc mysql 1592 0.0 0.3 193640 12444 ? Sl 11:21 0:00 /usr/bin/DDLProc

It looks like each of those processes works already multi-threaded:

$ ps -eLf | grep -e mysql -e PID UID PID PPID LWP C NLWP STIME TTY TIME CMD mysql 1380 1 1380 0 10 11:20 ? 00:00:00 /usr/sbin/mariadbd mysql 1380 1 1381 0 10 11:20 ? 00:00:00 /usr/sbin/mariadbd mysql 1380 1 1382 0 10 11:20 ? 00:00:00 /usr/sbin/mariadbd mysql 1380 1 1383 0 10 11:20 ? 00:00:00 /usr/sbin/mariadbd mysql 1380 1 1384 0 10 11:20 ? 00:00:00 /usr/sbin/mariadbd mysql 1380 1 1385 0 10 11:20 ? 00:00:00 /usr/sbin/mariadbd mysql 1380 1 1389 0 10 11:20 ? 00:00:00 /usr/sbin/mariadbd mysql 1380 1 1391 0 10 11:20 ? 00:00:00 /usr/sbin/mariadbd mysql 1380 1 1392 0 10 11:20 ? 00:00:00 /usr/sbin/mariadbd mysql 1380 1 1407 0 10 11:20 ? 00:00:00 /usr/sbin/mariadbd mysql 1499 1 1499 0 5 11:21 ? 00:00:00 /usr/bin/workernode DBRM_Worker1 mysql 1499 1 1501 0 5 11:21 ? 00:00:00 /usr/bin/workernode DBRM_Worker1 mysql 1499 1 1502 0 5 11:21 ? 00:00:00 /usr/bin/workernode DBRM_Worker1 mysql 1499 1 1503 0 5 11:21 ? 00:00:00 /usr/bin/workernode DBRM_Worker1 mysql 1499 1 1504 0 5 11:21 ? 00:00:00 /usr/bin/workernode DBRM_Worker1 mysql 1507 1 1507 0 6 11:21 ? 00:00:00 /usr/bin/controllernode mysql 1507 1 1567 0 6 11:21 ? 00:00:00 /usr/bin/controllernode mysql 1507 1 1573 0 6 11:21 ? 00:00:00 /usr/bin/controllernode mysql 1507 1 1580 0 6 11:21 ? 00:00:00 /usr/bin/controllernode mysql 1507 1 1581 0 6 11:21 ? 00:00:00 /usr/bin/controllernode mysql 1507 1 1596 0 6 11:21 ? 00:00:00 /usr/bin/controllernode mysql 1522 1 1522 0 18 11:21 ? 00:00:00 /usr/bin/PrimProc mysql 1522 1 1523 0 18 11:21 ? 00:00:00 /usr/bin/PrimProc mysql 1522 1 1524 0 18 11:21 ? 00:00:00 /usr/bin/PrimProc mysql 1522 1 1525 0 18 11:21 ? 00:00:00 /usr/bin/PrimProc mysql 1522 1 1526 0 18 11:21 ? 00:00:00 /usr/bin/PrimProc mysql 1522 1 1527 0 18 11:21 ? 00:00:00 /usr/bin/PrimProc mysql 1522 1 1528 0 18 11:21 ? 00:00:00 /usr/bin/PrimProc mysql 1522 1 1529 0 18 11:21 ? 00:00:00 /usr/bin/PrimProc mysql 1522 1 1530 0 18 11:21 ? 00:00:00 /usr/bin/PrimProc mysql 1522 1 1531 0 18 11:21 ? 00:00:00 /usr/bin/PrimProc mysql 1522 1 1532 0 18 11:21 ? 00:00:00 /usr/bin/PrimProc mysql 1522 1 1533 0 18 11:21 ? 00:00:00 /usr/bin/PrimProc mysql 1522 1 1534 0 18 11:21 ? 00:00:00 /usr/bin/PrimProc mysql 1522 1 1535 0 18 11:21 ? 00:00:00 /usr/bin/PrimProc mysql 1522 1 1561 0 18 11:21 ? 00:00:00 /usr/bin/PrimProc mysql 1522 1 1563 0 18 11:21 ? 00:00:00 /usr/bin/PrimProc mysql 1522 1 1586 0 18 11:21 ? 00:00:00 /usr/bin/PrimProc mysql 1522 1 1593 0 18 11:21 ? 00:00:00 /usr/bin/PrimProc mysql 1545 1 1545 0 2 11:21 ? 00:00:00 /usr/bin/WriteEngineServer mysql 1545 1 1546 0 2 11:21 ? 00:00:00 /usr/bin/WriteEngineServer mysql 1560 1 1560 0 6 11:21 ? 00:00:00 /usr/bin/ExeMgr mysql 1560 1 1562 0 6 11:21 ? 00:00:00 /usr/bin/ExeMgr mysql 1560 1 1564 0 6 11:21 ? 00:00:00 /usr/bin/ExeMgr mysql 1560 1 1565 0 6 11:21 ? 00:00:00 /usr/bin/ExeMgr mysql 1560 1 1572 0 6 11:21 ? 00:00:00 /usr/bin/ExeMgr mysql 1560 1 1577 0 6 11:21 ? 00:00:00 /usr/bin/ExeMgr mysql 1571 1 1571 0 3 11:21 ? 00:00:00 /usr/bin/DMLProc mysql 1571 1 1587 0 3 11:21 ? 00:00:00 /usr/bin/DMLProc mysql 1571 1 1588 0 3 11:21 ? 00:00:00 /usr/bin/DMLProc mysql 1592 1 1592 0 3 11:21 ? 00:00:00 /usr/bin/DDLProc mysql 1592 1 1594 0 3 11:21 ? 00:00:00 /usr/bin/DDLProc mysql 1592 1 1595 0 3 11:21 ? 00:00:00 /usr/bin/DDLProc

In the database you can run this SELECT query to check which MariaDB ColumnStore version is running:

SQL> SELECT plugin_name, plugin_status, plugin_type_version, plugin_library_version, plugin_license, plugin_maturity, plugin_auth_version FROM information_schema.plugins WHERE plugin_library LIKE 'ha_column%'; +---------------------+---------------+---------------------+------------------------+----------------+-----------------+---------------------+ | plugin_name | plugin_status | plugin_type_version | plugin_library_version | plugin_license | plugin_maturity | plugin_auth_version | +---------------------+---------------+---------------------+------------------------+----------------+-----------------+---------------------+ | Columnstore | ACTIVE | 100510.0 | 1.14 | GPL | Stable | 5.5.2 | | COLUMNSTORE_COLUMNS | ACTIVE | 100510.0 | 1.14 | GPL | Stable | 5.5.2 | | COLUMNSTORE_TABLES | ACTIVE | 100510.0 | 1.14 | GPL | Stable | 5.5.2 | | COLUMNSTORE_FILES | ACTIVE | 100510.0 | 1.14 | GPL | Stable | 5.5.2 | | COLUMNSTORE_EXTENTS | ACTIVE | 100510.0 | 1.14 | GPL | Stable | 5.5.2 | +---------------------+---------------+---------------------+------------------------+----------------+-----------------+---------------------+ 5 rows in set (0.002 sec)

or if the MariaDB ColumnStore is enabled at all with:

SQL> SHOW ENGINES; +--------------------+---------+-------------------------------------------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+-------------------------------------------------------------------------------------------------+--------------+------+------------+ | Columnstore | YES | ColumnStore storage engine | YES | NO | NO | | MRG_MyISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | Aria | YES | Crash-safe tables with MyISAM heritage. Used for internal temporary tables and privilege tables | NO | NO | NO | | MyISAM | YES | Non-transactional engine with good performance and small data footprint | NO | NO | NO | | SEQUENCE | YES | Generated tables filled with sequential values | YES | NO | YES | | InnoDB | DEFAULT | Supports transactions, row-level locking, foreign keys and encryption for tables | YES | YES | YES | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | CSV | YES | Stores tables as CSV files | NO | NO | NO | +--------------------+---------+-------------------------------------------------------------------------------------------------+--------------+------+------------+
Create the Cross Engine Join User in the database

The Cross Engine Join User specified above must also be created inside the database:

SQL> CREATE USER 'cross_engine'@'127.0.0.1' IDENTIFIED BY 'secret'; SQL> CREATE USER 'cross_engine'@'localhost' IDENTIFIED BY 'secret'; SQL> GRANT SELECT ON *.* TO 'cross_engine'@'127.0.0.1'; SQL> GRANT SELECT ON *.* TO 'cross_engine'@'localhost';

This is basically all you have to do for a running single-node MariaDB ColumnStore installation.

MariaDB ColumnStore schemas

MariaDB ColunStore creates 3 new schemas in the database:

SQL> SHOW SCHEMAS; +---------------------+ | Database | +---------------------+ | calpontsys | | columnstore_info | | infinidb_querystats | | information_schema | | mysql | | performance_schema | +---------------------+

We will investigate later what they are used for.

Server System Variables and Server Status Variables

A detailed description of the Server System Variables (columnstore_*) can be found in the MariaDB documentation (old source: infinidb_*).

SQL> SHOW GLOBAL STATUS LIKE 'columnstore%'; +-------------------------+--------+ | Variable_name | Value | +-------------------------+--------+ | Columnstore_commit_hash | source | | Columnstore_version | 5.5.2 | +-------------------------+--------+ 2 rows in set (0.001 sec)
SQL> SHOW GLOBAL VARIABLES LIKE 'columnstore%'; +-------------------------------------------------+--------+ | Variable_name | Value | +-------------------------------------------------+--------+ | columnstore_cache_flush_threshold | 500000 | | columnstore_cache_inserts | OFF | | columnstore_compression_type | SNAPPY | | columnstore_decimal_scale | 8 | | columnstore_derived_handler | ON | | columnstore_diskjoin_bucketsize | 100 | | columnstore_diskjoin_largesidelimit | 0 | | columnstore_diskjoin_smallsidelimit | 0 | | columnstore_double_for_decimal_math | OFF | | columnstore_group_by_handler | ON | | columnstore_import_for_batchinsert_delimiter | 7 | | columnstore_import_for_batchinsert_enclosed_by | 17 | | columnstore_local_query | 0 | | columnstore_orderby_threads | 16 | | columnstore_ordered_only | OFF | | columnstore_replication_slave | OFF | | columnstore_select_handler | ON | | columnstore_select_handler_in_stored_procedures | ON | | columnstore_string_scan_threshold | 10 | | columnstore_stringtable_threshold | 20 | | columnstore_um_mem_limit | 0 | | columnstore_use_decimal_scale | OFF | | columnstore_use_import_for_batchinsert | ALWAYS | | columnstore_varbin_always_hex | OFF | +-------------------------------------------------+--------+ 24 rows in set (0.003 sec)

MariaDB ColumnStore also writes to the MariaDB error log:

$ cat mariadbd-error.log 210511 11:20:47 Columnstore: Started; Version: 5.5.2-2

The MariaDB ColumnStore configuration is stored in /etc/columnstore. Those files can be read by everybody on the system by default. So it possibly makes sense to prohibit access:

$ chown mysql: /etc/columnstore/* $ chmod o-rw /etc/columnstore/*

We have not seen yet an negative impact after this "hardening":

$ ll total 100 drwxr-xr-t 2 mysql mysql 4096 May 11 11:17 ./ drwxr-xr-x 78 root root 4096 May 11 10:12 ../ -rw-r----- 1 mysql mysql 19169 May 11 11:17 Columnstore.xml -rw-r----- 1 mysql mysql 19145 May 11 10:12 Columnstore.xml-20210511 -rw-rw---- 1 mysql mysql 19153 May 11 11:17 Columnstore.xml.columnstoreSave -rw-r----- 1 mysql mysql 15372 May 5 23:00 ErrorMessage.txt -rw-r----- 1 mysql mysql 5619 May 5 23:00 MessageFile.txt -rw-r----- 1 mysql mysql 7456 May 5 23:03 storagemanager.cnf
MariaDB Column Store on the file system

It is also interesting to know where on the filesystem the MariaDB ColumnStore files are located. By default they are located under /var/lib/columnstore (Note: Can they be located somewhere else and how?).

$ tree * data1 ├── 000.dir │   └── 000.dir │   ├── 003.dir │   │   ├── 233.dir │   │   │   └── 000.dir │   │   │   └── FILE000.cdf ... │   │   └── 255.dir │   │   └── 000.dir │   │   └── FILE000.cdf │   ├── 004.dir │   │   ├── 000.dir │   │   │   └── 000.dir │   │   │   └── FILE000.cdf ... │   │   └── 018.dir │   │   └── 000.dir │   │   └── FILE000.cdf │   ├── 007.dir │   │   ├── 209.dir │   │   │   └── 000.dir │   │   │   └── FILE000.cdf │   │   └── 212.dir │   │   └── 000.dir │   │   └── FILE000.cdf │   ├── 008.dir │   │   ├── 013.dir │   │   │   └── 000.dir │   │   │   └── FILE000.cdf ... │   │   └── 028.dir │   │   └── 000.dir │   │   └── FILE000.cdf │   ├── 011.dir │   ├── 012.dir │   ├── 013.dir │   ├── 014.dir │   ├── 015.dir │   ├── 016.dir │   ├── 017.dir │   ├── 018.dir │   │   ├── 164.dir │   │   │   └── 000.dir │   │   │   └── FILE000.cdf ... │   │   └── 255.dir │   │   └── 000.dir │   │   └── FILE000.cdf │   └── 019.dir │   ├── 000.dir │   │   └── 000.dir │   │   └── FILE000.cdf ... ... │   └── 221.dir │   └── 000.dir │   └── FILE000.cdf ├── bulkRollback ├── dbroot1-lock ├── systemFiles │   └── dbrm │   ├── BRM_savesA_em │   ├── BRM_savesA_vbbm │   ├── BRM_savesA_vss │   ├── BRM_savesB_em │   ├── BRM_savesB_vbbm │   ├── BRM_savesB_vss │   ├── BRM_saves_current │   ├── BRM_saves_em │   ├── BRM_saves_journal │   ├── BRM_saves_vbbm │   ├── BRM_saves_vss │   ├── oidbitmap │   ├── SMTxnID │   └── tablelocks └── versionbuffer.cdf local └── module storagemanager └── storagemanager-lock 688 directories, 353 files
Creating our first MariaDB ColumnStore table

For the very first tests we use our well known test table. But for MariaDB ColumnStore tables you should NOT use the test schema: ColumnStore tables should not be created in the mysql, information_schema, calpontsys or test databases. (Why not test?).
In other documents about MariaDB ColumnStore we can clearly see, that the one who was writing the article was using the test schema [ 2 ]. An other restriction is: ColumnStore stores all object names in lower case. So probably CamelCase table names will not work as expected or they will be converted to lower case.

SQL> SET SESSION default_storage_engine = ColumnStore; SQL> CREATE TABLE test ( id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY , data VARCHAR(128) DEFAULT NULL , ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); ERROR 1069 (42000): Too many keys specified; max 0 keys allowed

Oppps! What is this? After some searching I found that MariaDB ColumnStore does not know indexes: There is no need for indexing. See also MCOL-1080. As such indexes typically used to optimize query access for row based systems do not make sense since selectivity is low for such queries.

After removing the Primary Key the next try:

SQL> CREATE TABLE test ( id INT UNSIGNED NOT NULL AUTO_INCREMENT , data VARCHAR(128) DEFAULT NULL , ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

Also AUTO_INCREMENT seems not to be supported:

SQL> CREATE TABLE test ( id INT UNSIGNED NOT NULL , data VARCHAR(128) DEFAULT NULL , ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); ERROR 1178 (42000): The storage engine for the table doesn't support The syntax or the data type(s) is not supported by Columnstore. Please check the Columnstore syntax guide for supported syntax or data types.

Hmmm. What is wrong now? Checking the MariaDB ColumnStore datatypes does not show any obvious problem. Also the the page ColumnStore Create Table was not helpful. Relying on intuition I tried this:

SQL> CREATE TABLE test ( id INT UNSIGNED NOT NULL , data VARCHAR(128) DEFAULT NULL , ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ); ERROR 1815 (HY000): Internal error: The default value is out of range for the specified data type.

Becoming stressed a bit now this:

SQL> CREATE TABLE test ( id INT UNSIGNED NOT NULL , data VARCHAR(128) DEFAULT NULL , ts TIMESTAMP NOT NULL ); ERROR 1815 (HY000): Internal error: Config::Config: error accessing config file /etc/columnstore/Columnstore.xml

OK. This makes sense. We were a bit to restrictive in the first run above when we were hardening the MariaDB ColumnStore configuration file.

After fixing this we tried again and it finally works...

Query OK, 0 rows affected (1.755 sec)

For comparison only: This CREATE TABLE DDL statement with InnoDB takes about 20 ms.

Migrating MariaDB (InnoDB, MyISAM, Aria) tables is not so straight forward as it could be. So the MariaDB ColumnStore seems to be still a bit picky! Interesting is, that if we look at the CREATE TABLE statement it is shown in a non usable form:

SQL> SHOW CREATE TABLE test\G *************************** 1. row *************************** Table: test Create Table: CREATE TABLE `test` ( `id` int(10) unsigned NOT NULL, `data` varchar(128) DEFAULT NULL, `ts` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() ) ENGINE=Columnstore DEFAULT CHARSET=utf8 1 row in set (0.001 sec)

See also: MCOL-4716, MCOL-1039 and MCOL-4039. This problem seems to be known since a while yet...

This also means that if you want to use mysqldump for migrating the table structure it will not work for restore without editing:

$ mysqldump --user=root --no-data --skip-lock-tables test > /tmp/test_structure_dump.sql
Querying and changing data in MariaDB ColumnStore

Note: This is NOT the preferred way how you should add a massive amount of data into MariaDB ColumnStore!

SQL> INSERT INTO test VALUES (1, 'some data', NULL); Query OK, 1 row affected (0.224 sec) SQL> INSERT INTO test VALUES (2, 'some data', NULL); Query OK, 1 row affected (0.137 sec) SQL> INSERT INTO test VALUES (3, 'some data', NULL); Query OK, 1 row affected (0.145 sec) SQL> SELECT * FROM test; +----+-----------+---------------------+ | id | data | ts | +----+-----------+---------------------+ | 1 | some data | 2021-05-11 14:01:31 | | 2 | some data | 2021-05-11 14:01:35 | | 3 | some data | 2021-05-11 14:01:38 | +----+-----------+---------------------+ 3 rows in set (0.104 sec) SQL> DELETE FROM test WHERE id = 2; Query OK, 1 row affected (0.194 sec) SQL> SELECT * FROM test; +----+-----------+---------------------+ | id | data | ts | +----+-----------+---------------------+ | 1 | some data | 2021-05-11 14:01:31 | | 3 | some data | 2021-05-11 14:01:38 | +----+-----------+---------------------+ 2 rows in set (0.022 sec) SQL> UPDATE test SET data = 'new data' WHERE id = 3; Query OK, 1 row affected (0.222 sec) Rows matched: 1 Changed: 1 Warnings: 0 SQL> SELECT * FROM test; +----+-----------+---------------------+ | id | data | ts | +----+-----------+---------------------+ | 1 | some data | 2021-05-11 14:01:31 | | 3 | new data | 2021-05-11 14:03:11 | +----+-----------+---------------------+ 2 rows in set (0.018 sec)

So basic functionality (INSERT, UPDATE, DELETE and SELECT) works. But as we can see, response times for these tiny data sets and single row DML commands are fare above as we are used with other MariaDB Storage Engines (InnoDB, Aria, MyISAM), which is expected... But at least it works. So this is not the best use case for MariaDB ColumnStore. And SELECT * FROM <table> is probably one of the worst things you can do to a ColumnStore in general.

MariaDB ColumnStore using sequences instead of AUTO_INCREMENT

Because AUTO_INCREMENT is not supported in MariaDB ColumnStore we try to use a SEQUENCE instead:

SQL> CREATE SEQUENCE test_s START WITH 5 INCREMENT BY 1; SQL> INSERT INTO test SELECT NEXT VALUE FOR test_s, 'Some new data', CURRENT_TIMESTAMP(); Query OK, 1 row affected (1.403 sec) Records: 1 Duplicates: 0 Warnings: 0 SQL> INSERT INTO test SELECT NEXT VALUE FOR test_s, 'Some new data', CURRENT_TIMESTAMP() FROM test; Query OK, 4 rows affected (1.210 sec) Records: 4 Duplicates: 0 Warnings: 0

Querying the data shows that sequences work but not as expected. They are evaluated once per query and not once per row:

SQL> SELECT * FROM test; +----+---------------+---------------------+ | id | data | ts | +----+---------------+---------------------+ | 1 | Some data | 2021-05-19 16:58:09 | | 2 | Some data | 2021-05-19 16:58:14 | | 3 | Some data | 2021-05-19 16:58:18 | | 5 | Some new data | 2021-05-19 16:59:00 | | 6 | Some new data | 2021-05-19 16:59:20 | | 6 | Some new data | 2021-05-19 16:59:20 | | 6 | Some new data | 2021-05-19 16:59:20 | | 6 | Some new data | 2021-05-19 16:59:20 | +----+---------------+---------------------+ 8 rows in set (0.028 sec)

And if we do this for many rows, we can suddenly see that this becomes much faster than with other MariaDB Storage Engines:

SQL> INSERT INTO test SELECT NEXT VALUE FOR test_s, 'Some new data', CURRENT_TIMESTAMP() FROM test; Query OK, 131072 rows affected (1.221 sec) Records: 131072 Duplicates: 0 Warnings: 0 SQL> INSERT INTO test SELECT NEXT VALUE FOR test_s, 'Some new data', CURRENT_TIMESTAMP() FROM test; Query OK, 262144 rows affected (2.236 sec) Records: 262144 Duplicates: 0 Warnings: 0 SQL> INSERT INTO test SELECT NEXT VALUE FOR test_s, 'Some new data', CURRENT_TIMESTAMP() FROM test; Query OK, 524288 rows affected (2.266 sec) Records: 524288 Duplicates: 0 Warnings: 0 SQL> INSERT INTO test SELECT NEXT VALUE FOR test_s, 'Some new data', CURRENT_TIMESTAMP() FROM test; Query OK, 1048576 rows affected (4.316 sec) Records: 1048576 Duplicates: 0 Warnings: 0 SQL> INSERT INTO test SELECT NEXT VALUE FOR test_s, 'Some new data', CURRENT_TIMESTAMP() FROM test; Query OK, 2097152 rows affected (8.367 sec) Records: 2097152 Duplicates: 0 Warnings: 0 SQL> INSERT INTO test SELECT NEXT VALUE FOR test_s, 'Some new data', CURRENT_TIMESTAMP() FROM test; Query OK, 4194304 rows affected (15.472 sec) Records: 4194304 Duplicates: 0 Warnings: 0

To compare with InnoDB: About above 1 Mio rows seems to be the break even for MariaDB ColumnStore:

SQL> INSERT INTO test_innodb SELECT NEXT VALUE FOR test_s, 'Some new data', CURRENT_TIMESTAMP() FROM test_innodb; Query OK, 1048576 rows affected (3.499 sec) Records: 1048576 Duplicates: 0 Warnings: 0 SQL> INSERT INTO test_innodb SELECT NEXT VALUE FOR test_s, 'Some new data', CURRENT_TIMESTAMP() FROM test_innodb; Query OK, 2097152 rows affected (10.100 sec) Records: 2097152 Duplicates: 0 Warnings: 0 SQL> INSERT INTO test_innodb SELECT NEXT VALUE FOR test_s, 'Some new data', CURRENT_TIMESTAMP() FROM test_innodb; Query OK, 4194304 rows affected (21.766 sec) Records: 4194304 Duplicates: 0 Warnings: 0
MariaDB Column Store Query Execution Plan

If we want to see how the queries are executed the usual EXPLAIN command does not help much:

SQL> EXPLAIN SELECT * FROM test; +------+---------------+-------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+---------------+-------+------+---------------+------+---------+------+------+-------+ | 1 | PUSHED SELECT | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | +------+---------------+-------+------+---------------+------+---------+------+------+-------+

Instead we should do it in MariaDB ColumnStore like this:

SQL> SELECT calSetTrace(1); +----------------+ | calSetTrace(1) | +----------------+ | 0 | +----------------+ SQL> SELECT * FROM test; ... 2048 rows in set, 1 warning (0.090 sec) SQL> SELECT calGetTrace()\G *************************** 1. row *************************** calGetTrace(): Desc Mode Table TableOID ReferencedColumns PIO LIO PBE Elapsed Rows BPS PM test 5086 (data,id,ts) 69 44 0 0.066 2048 TNS UM - - - - - - 0.000 2048 1 row in set (0.000 sec)

If we run the query a second time we can see that the physical I/O (PIO) has been reduced, so everything comes out of memory:

SQL> SELECT * FROM test; ... 2048 rows in set, 1 warning (0.021 sec) SQL> SELECT calGetTrace()\G *************************** 1. row *************************** calGetTrace(): Desc Mode Table TableOID ReferencedColumns PIO LIO PBE Elapsed Rows BPS PM test 5086 (data,id,ts) 0 44 0 0.000 2048 TNS UM - - - - - - 0.000 2048 1 row in set (0.000 sec)

See also: Query execution and Viewing the ColumnStore query plan.

Taxonomy upgrade extras: dwhColumnStoreData Martmariadbreporting

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.

Any feedback, statements and testimonials are welcome as well! Please send them to feedback@fromdual.com.

Installation of Ops Center 1.1.0

A complete guide on how to install FromDual Ops Center you can find in the Ops Center User Guide.

Upgrade from 0.9.x to 1.1.0

Upgrade from 0.9.x to 1.1.0 should happen automatically. Please do a backup of your Ops Center Instance before you upgrade! Please also check Upgrading.

Changes in Ops Center 1.1.0 Machine

• Machine check error messages improved.

Instance

• Function check_instance made more robust against system outages.
• Safe account error message improved.
• Instance restart prepared.
• read_only in repo check added.
• Old function removed.
• Replication check moved from cluster check to instance check.
• Instance checks for IO and SQL thread added.
• Function testNode renamed to testInstance.
• Code clean-up and fix in instance check.
• Bug in account creation fixed for MariaDB 10.1 and MariaDB 10.0.
• Instance check improved.
• Target info was added in edit instance.
• Check master_log_slave_updates fixed.
• Function: chanageVariabelsInConfiguatrion added.
• Functions makeInstanceReadOnly and makeInstanceReadWrite redesigned and cleaned-up.
• Instance.inc renamed to TargetOperations.inc
• Database.inc renamed to Target.inc
• log_slave_updates check added to instance.

Cluster

• Failover bugs fixed, error messages improved.
• m/s cluster failover.
• Change master to can now copy masters file and pos to change master to command.
• Functionality switchover added.
• failover_slave_id renamed to failover_instance_id, back button in cluster made correct, check on stopped instance fixed.
• Cluster operations simplified and A_I inc and A_I offset added.
• Adding and deleting cluster fixed.
• read_only is also deployed during failover.
• Final error failover fixed and log_slave_status check added to cluster.
• Show cluster overview fixed.
• Function getMasterInfo fixed for MySQL and MariaDB.
• Missing read_only check on old master was added.
• Failover took wrong user.
• Cluster failover for mm cluster fixed.
• Cluster operations made more colorful to show important operations better.
• Function checkVipUser made ready for 3 node cluster.
• New master is set correctly now.
• Added more information for failover.
• M/s and m/m failover should work now.
• Error messages made more proper.
• Bug in readonly in repo check fixed.
• active_master_id added.
• Failover vip in cluster only if vip is present.
• Reattach slaves implemented.
• Failover slave feature implemented.
• Cluster was only shown if VIP was there, fixed
• One test only true for m/m was also done on m/s, fixed.
• Check instance should not interfere with cluster failover any more.
• read_only and activity added to cluster operations view.
• Cluster failover is moved from VIP to cluster.
• Cluster operations simplified.
• Cluster failover implemented.
• Failover rules moved from vip to cluster.
• Cluster test finished.
• m/m a/a and a/p introduced.
• Cluster vip check added.
• Unique server_id check implemented for cluster.
• Slave read_only in repo check implemented for repl cluster.
• Slave activity on cluster implemented.
• Cluster tests for m/s and m/m properly improved.
• Replication check moved from cluster check to instance check.
• Slave is shown in cluster overview.
• Cluster checks improved and checks display made unique.
• VIP is shown in replication cluster.
• Bug in cluster handling and usability improved.
• HTML code clean-up.
• Some clean-up and M/M cluster fixed.

Load-Balancer

• No changes.

Virtual IP (VIP)/Floating IP

• Some VIP cosmetics fixed.
• Amount of error messages reduced during vip start.
• Bug in check for new VIP fixed.
• VIP add check improved.
• Bug in VIP check fixed, various smaller code clean-up.
• VIP failover improved.
• Arping command not found behaviour improved.
• VIP code cleaned-up.
• VIP interface check added.
• VIP nonsense interface already used check removed.
• Column of primary and failover machine added to VIP overview.
• Edit VIP machines are sorted in drop down.
• Key shortcuts added to VIP buttons and html made nicer according to our standards.

Tools

• Error logging in file transfer improved.
• Crontab title changed from hr to hour.
• Crontab error 3800 relaxed to warning.
• Temporay crontab is now cleaned-up to not leave any remains.
• Bug in tools function fixed.
• File transfer bugs fixed.
• Run Crontab Entry now was implemented.
• Adding Crontab entries with keyboard shortcuts is possible now.
• Bug in Crontab with htmlspecialcharacters fixed.
• Jobs: Error messages made more verbose in regular jobs.
• Jobs: Jobs are sorted by start_ts DESC now.
• Jobs: Made job deletion dramatically faster.
• Jobs: Clean-up job remaining.
• Jobs: Button in Job Tool provisioned with keys.
• Jobs: Job checks improved.
• Jobs: Os_user added to job. This means job can be started under every user now!
• Jobs: Special characters from crontab inherited to jobs fixed.
• Jobs: More verbose error messages in job state preparing.
• Jobs: Job gets a start timestamp as soon as it moves to state Preparing.
• Jobs: Code cleaned-up and some bugs fixed.
• Jobs: Function getJobsWithFilter replaces by readJobs.
• Jobs: Function addJob replaced by createJob.
• Crontab: User in crontab can now be chosen, but not in jobs yet!
• Crontab: Special characters from crontab inherited to jobs fixed.
• Crontab: Only log output when error returned, not OK.

Configuration

• Configuration: If focmm crontab is not activated complaints are shown on prominent place.
• Configuration: Delete focmm crontab added.

Database-as-a-Service (DBaaS)

• No changes.

Building and Packaging

• Files directory added to package build instructions to make file copy work.
• rpmlint warning removed.
• Some lintian warnings implemented.
• rpmlint warning removed.

Themes / UI

• Nasty CSS error removed.

General

• myEnv lib updated.
• myEnv library updated.
• Documentation improved and code cosmetics fixed.
• Various minor bugs fixed.
• CHANGELOG updated, license key changed.
• myEnv library updated.
• Library jquery updated from 3.5.0 to 3.6.0.
• CHANGELOG updated.
• Return codes made unique.
• Some minor bugs fixed.
• Status in menu on the left fixed.
• PoC added.
• Compare tables PoC added.
• Smaller code fixes.
• Copyright updated from 2020 to 2021.
• Better error message, GUI test improved.
• Minor bug fixed and typos fixed.
• Function run remote command improved.
• Wording improved.
• Accesskey added to SubmitButton and ContinueButton.
• Constant LOG_ERROR added.
• ssh function run on remote server optimized.
• Typos fixed and page titles made more precise.

Repository

• Node.inc renamed to Catalog.inc.

Taxonomy upgrade extras: OperationsreleaseFromDual Ops Centerops centerdbaasfocmm

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 tmpdir volume/file and not the whole / (root) filesystem.

# fallocate -l 4G /mysql-tmpdir # mkfs.ext4 /mysql-tmpdir # mke2fs 1.44.1 (24-Mar-2018) Discarding device blocks: done Creating filesystem with 1048576 4k blocks and 262144 inodes Filesystem UUID: 74c51e5c-bed8-4a7d-8f1b-e89669726e1d Superblock backups stored on blocks: 32768, 98304, 163840, 229376, 294912, 819200, 884736 Allocating group tables: done Writing inode tables: done Creating journal (16384 blocks): done Writing superblocks and filesystem accounting information: done # grep loop /proc/mounts # mount | grep loop # mkdir /var/lib/mysql-tmpdir # mount -o loop=/dev/loop0 /mysql-tmpdir /var/lib/mysql-tmpdir # df -h | grep -e mysql-tmpdir -e Size Filesystem Size Used Avail Use% Mounted on /dev/loop0 3.9G 16M 3.7G 1% /var/lib/mysql-tmpdir # umount /var/lib/mysql-tmpdir # chown -R mysql: /var/lib/mysql-tmpdir

Add the entry to your /etc/fstab:

/mysql-tmpdir /var/lib/mysql-tmpdir ext4 loop 0 0

Test the fstab entry:

# mount /mysql-tmpdir # sudo -u mysql touch /var/lib/mysql-tmpdir/test

Configure your MySQL database accordingly (my.cnf):

tmpdir = /var/lib/mysql-tmpdir

and after database restart:

SQL> SHOW GLOBAL VARIABLES LIKE 'tmpdir'; +---------------+-----------------------+ | Variable_name | Value | +---------------+-----------------------+ | tmpdir | /var/lib/mysql-tmpdir | +---------------+-----------------------+

Then create some implicit temporary tables on disk:

# ls -la drwx------ 2 mysql mysql 16K Apr 29 16:41 lost+found -rw-rw---- 1 mysql mysql 8.0K Apr 29 17:09 '#sql-temptable-50a1-6-23.MAD' -rw-rw---- 1 mysql mysql 8.0K Apr 29 17:09 '#sql-temptable-50a1-6-23.MAI'

and check if tmpdir is really capped at 4 Gibyte:

# dd if=/dev/zero of=/var/lib/mysql-tmpdir/tmp bs=1M count=5000 dd: error writing '/var/lib/mysql-tmpdir/tmp': No space left on device 3731+0 records in 3730+0 records out 3912126464 bytes (3.9 GB, 3.6 GiB) copied, 13.708 s, 285 MB/s
Literature

• Use A File As A Linux Block Device

Taxonomy upgrade extras: myisamtmpdirtemporary table