Feed Aggregator

One feature that really excited me in the new MariaDB 11.4 LTS release is the shrinking of the system tablespace (ibdata1). I have been eagerly waiting for this feature since around 2006 and now it has finally arrived with MariaDB 11.4. Actually, this feature has been available since MariaDB 11.2 IR (June 2023).

Unfortunately, the announcement of this feature came a little too short. In the MariaDB release notes it says succinctly:

The reasons why this file can grow immeasurably have actually been known for a long time and the measures against it are also clear (see literature). But again and again we see MariaDB users out in the field who didn’t realise the problem or realised it too late and are now left with an ibdata1 file that is far too large…

How can the problem be provoked?

The problem can be provoked by creating a table in the system tablespace:

SQL> …

https://jira.mariadb.org/browse/MDEV-34412

Hi dcz01

No it is not solved… It is closed and “not a bug”… I try to intervene

Regards, Oli

Hi Oli, Thanks for your answer. The problem is already open and solved but not for me and its still not working: https://jira.mariadb.org/browse/MDEV-28172

Greetings dcz01

Hello dcz01

I would say, this is probably a bug. I can reproduce it on my system (MySQL 5.7 Master and MariaDB 10.6 Galera Cluster).

Please contact your MariaDB support contact… If you need assistance we are happy to sell you our remote-DBA services…

Regards, Oli

Hi, we are migrating some old mysql 5.7 servers to new mariadb 10.5 machines with new galera 4 and we’re doing the normal way of migration:

• Dumping all databases from old servers and importing to new galera cluster (no error)
• Configuration of traditional sql replication between old and new servers so that clients still can connect to servers without downtime (no errors)
• Starting replication and just creating a new database (error and galera nodes which are not the replication slave are dropping with inconsistency from cluster)

Do you know what could be the failure here? You described it here very good but its not working so easy.

Greetings dcz01

On this page we have summarised information about downgrading a MariaDB/MySQL database. We do not cover migrating from MySQL to MariaDB or vice versa. For migrations (sidegrade) please look here: MySQL - MariaDB migration and here: Migration between MySQL/Percona Server and MariaDB.

The downgrade process tends to be neglected because nobody really wants to do it. So we are in swampy territory here. Unforeseen situations can occur very easily and good preparation is essential.

I can think of the following possible options for the downgrade:

• Restore the backup that was made BEFORE the upgrade began.
• Binary inplace downgrade (which is NOT supported in many cases).
• Dump (mariadb-dump/mysqldump) of the upgraded database and restore (mariadb/mysql) to the old version (logical backup/restore).
• Master/slave replication (with Statement Based Replication (SBR) or Row Based Replication (RBR)) and fallback to the slave with the old version.
• Partial Backup/Restore with mariadb-backup/xtrabackup (Transportable Tablespace …

FromDual is pleased to announce the release of the new version 1.2.2 of the 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 DBAs and system administrators to better manage their MariaDB and MySQL databases and Galera Cluster farms. Ops Center makes life easier for DBAs and Admins!

The main purpose of the Ops Center is to help you with your daily MariaDB and MySQL operations. More information about FromDual Ops Center can be found in the General Information section.

Download now

The new FromDual Ops Center for MariaDB and MySQL (focmm) can be downloaded from our download page. How to install and use focmm is documented in the Ops Center User Guide.

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

Any feedback, …

• Ops Center 1.2.2 Release Notes, Release Date: 25 April 2024
• Ops Center 1.2.1 Release Notes, Release Date: 7 Februray 2023
• Ops Center 1.2.0 Release Notes, Release Date: 17 January 2023
• Ops Center 1.1.0 Release Notes, Release Date: 7 May 2021
• Ops Center 1.0.0 Release Notes, Release Date: 7 May 2020
• Ops Center 0.9.3 Release Notes, Release Date: 18 February 2020
• Ops Center 0.9.2 Release Notes, Release Date: 13 August 2019
• Ops Center 0.9.1 Release Notes, Release Date: 7 May 2019
• Ops Center 0.9.0 Release Notes, Release Date: 16 April 2019
• Ops Center 0.3.0 Release Notes, Release Date: 13 January 2016
• Ops Center 0.2.0 Release Notes, Release Date: 29 May 2014
• Ops Center 0.1.0 Release Notes, Release Date: 02 January 2014
• Ops Center 0.0.1 Release Notes, Release Date: 01 January 2014

Table of contents

• Review
• One month later
• Size of the tables
• Process list
• Global variables
• Metadata Lock and InnoDB Transaction Lock
• Global status

Review

After we introduced dbstat for MariaDB (and MySQL) a good 5 weeks ago, we naturally also rolled it out on our systems to test the behaviour in daily use (eat your own dog food).

This went quite well until we came up with the idea of activating dbstat on the passive dbstat node on our MariaDB active/passive master/master replication cluster (a similar situation would also occur with a Galera cluster). We realised that the design of dbstat still had potential. After this problem was fixed (v0.0.2 and v0.0.3) and the problem of how to activate events on master AND slave was solved (MDEV-33782: Event is always disabled on slave), everything seemed fine at first glance. Unfortunately, we did not realise that the data also had to be adjusted. As a result, our replication came to a complete stop over the Easter holidays, which then led to another problem when …

Due to an application error, our replication stopped for 5 days (over Easter). After the problem was solved, the replication was supposed to catch up, which turned out to be very slow. All the usual tricks (innodb_flush_log_at_trx_commit, sync_binlog, etc.) had already been exhausted. So we tried our hand at parallel replication of the MariaDB server.

Parallel replication is deactivated by default:

SQL> SHOW GLOBAL VARIABLES LIKE '%parallel%';
+-------------------------------+------------+
| Variable_name                 | Value      |
+-------------------------------+------------+
| slave_domain_parallel_threads | 0          |
| slave_parallel_max_queued     | 131072     |
| slave_parallel_mode           | optimistic |
| slave_parallel_threads        | 0          |
| slave_parallel_workers        | 0          |
+-------------------------------+------------+

Parallel replication is activated by setting the server variables slave_parallel_threads:

SQL> SET GLOBAL slave_parallel_threads = 8;
ERROR 1198 …

Recently I had to test a new MariaDB feature that was developed at our request (MDEV-33782). To test this feature I had to build the MariaDB server myself from source, which I have not done for a long time. So a new challenge, especially with CMake…

I followed the MariaDB documentation Get, Build and Test Latest MariaDB the Lazy Way to build the server.

On Ubuntu 22.04 it did not work for me, for reasons unknown to me. So I cloned an Ubuntu 23.04 (Lunar Lobster) LXC container and built the MariaDB server in it.

To make the whole thing work, however, the package sources had to be added to the file /etc/apt/sources.list in the container first:

deb-src http://de.archive.ubuntu.com/ubuntu lunar main restricted universe multiverse
deb-src http://de.archive.ubuntu.com/ubuntu lunar-updates main restricted universe multiverse
deb-src http://de.archive.ubuntu.com/ubuntu lunar-security main restricted universe multiverse
deb-src http://de.archive.ubuntu.com/ubuntu lunar-backports main restricted universe …

Table of contents

• Overview
• Preparations
• Activate MaxScale configuration synchronisation
• Change MaxScale parameters
• Add new slave and make MaxScale known
• Remove old slave and make MaxScale known
• How is the configuration synchronised?
• What happens in the event of a conflict?
• Tests
• Deactivating MaxScale configuration synchronisation again
• Literature/sources

Overview

A feature that I recently discovered while browsing is the MaxScale configuration synchronisation functionality.

This is not primarily about a MariaDB replication cluster or a MariaDB Galera cluster, but about a cluster consisting of two or more MaxScale nodes. Or more precisely, the exchange of the configuration between these MaxScale nodes.

Pon Suresh Pandian has already written a blog article about this feature in 2022, which is even more detailed than this post here.

Preparations

An Incus container environment was prepared, consisting of 3 database containers (deb12-n1 (10.139.158.33), deb12-n2 (10.139.158.178), deb12-n3 (10.139.158.39)) and 2 …

Table of contents

• Overview
• Preparation of the shards (MariaDB database instances)
  • Create test data
  • Creating roles and users
  • MaxScale Monitor User
  • MaxScale Admin User
  • Create application role and accounts
  • Proxy protocol
• MaxScale SchemaRouter configuration
• Starting and stopping the MaxScale Load Balancer
• Application tests
  • Simple application tests
  • New command show shards
  • More general test
  • Less simple tests
  • More complex application tests
  • Cross-shard tests
• Operation of a MaxScale sharding system
  • Do-on-all-shards
  • Invalidating the database map cache
  • How to change SchemaRouter variables dynamically?
  • Adding and removing a tenant
  • Moving a tenant
  • Adding or removing a shard
  • Customising the configuration files
  • Maintenance work on the shard
• Observation / monitoring of a MariaDB MaxScale sharding system
• Literature / Sources

Overview

This feature should more or less work with MariaDB MaxScale 6.x.y, 22.08.x, 23.02.x, 23.08.x and 24.02.x. We have tested it with the latest MaxScale version 23.08.05, as we encountered …

Table of contents

• Functionality of dbstat
• How does dbstat work
• How to install dbstat
• Query dbstat
  • table_size
  • processlist
  • trx_and_lck
  • metadata_lock
  • global_variables
  • global_status
• Testing
• Sources

An idea that I have been thinking about for a long time and have now, thanks to a customer, finally tackled is dbstat for MariaDB/MySQL. The idea is based on sar/sysstat by Sebastien Godard:

sar - Collect, report, or save system activity information.

and Oracle Statspack:

Statspack is a performance tuning tool … to quickly gather detailed analysis of the performance of that database instance.

Functionality of dbstat

Although we have had the performance schema for some time, it does not cover some points that we see as a problem in practice and that are requested by customers:

• The table_size module collects data on the growth of tables. This allows statements to be made about the growth of individual tables, databases, future MariaDB Catalogs or the entire instance. This is interesting for users who are …

FromDual has the pleasure to announce the release of the new version 2.1.0 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](mailto:feedback@fromdual.com?Subject=Feedback for MyEnv).

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

shell> cd ${HOME}/product
shell> tar xf /download/myenv-2.1.0.tar.gz
shell> rm -f myenv
shell> ln -s myenv-2.1.0 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 …

• MyEnv 3.0.0 Release Notes, Release Date: 23 March 2026
• MyEnv 2.1.1 Release Notes, Release Date: 12 December 2025
• MyEnv 2.1.0 Release Notes, Release Date: 28 February 2024
• MyEnv 2.0.3 Release Notes, Release Date: 2 July 2021
• MyEnv 2.0.2 Release Notes, Release Date: 17 January 2019
• MyEnv 2.0.1 Release Notes, Release Date: 3 October 2018
• MyEnv 2.0.0 Release Notes, Release Date: 15 March 2018
• MyEnv 1.3.1 Release Notes, Release Date: 3 August 2016
• MyEnv 1.3.0 Release Notes, Release Date: 31 January 2016
• MyEnv 1.2.2 Release Notes, Release Date: 12 October 2015
• MyEnv 1.2.1 Release Notes, Release Date: 27 July 2015
• MyEnv 1.2.0 Release Notes, Release Date: 18 April 2015
• MyEnv 1.1.4 Release Notes, Release Date: 11 February 2015
• MyEnv 1.1.3 Release Notes: Skipped, see v1.1.4, Release Date: 30 January 2015
• MyEnv 1.1.2 Release Notes, Release Date: 23 October 2014
• MyEnv 1.1.1 Release Notes, Release Date: 8 September 2014
• MyEnv 1.1.0 Release Notes, Release Date: 23 August 2014
• MyEnv 1.0.5 Release Notes, Release Date: 13 …

The design of a data warehouse differs from relational design. Data warehouses are often designed according to the concept of the star schema.

When building a data warehouse, you usually put the cart before the horse:

• What questions should my data warehouse be able to answer?
• How do I have to design my model so that my questions can be answered easily?
• Where do I get the data to populate the model?
• How do I fill my model with the data?

For training purposes, we have investigated an issue that arises from time to time with our support team: The system suddenly and unexpectedly starts to behave unusually, nobody has done anything and nobody knows why. Example with a customer last week: The system starts to become unstable at 3 pm, is then restarted hard and then stabilises again from 4 pm…

The easiest thing to do in such a case would be to quickly look at the database with the SHOW PROCESSLIST command and then it often becomes immediately clear where the problem lies. But customers often forget this …

FromDual has the pleasure to announce the release of the new version 2.1.0 of its popular Database Performance Monitor for MariaDB and Galera Cluster fpmmm.

The FromDual Performance Monitor for MariaDB (fpmmm) enables DBAs and System Administrators to monitor and understand what is going on inside their MariaDB database instances and on the machines where the databases reside.

More detailed information you can find in the fpmmm Installation Guide.

Download

The new FromDual Performance Monitor for MariaDB (fpmmm) can be downloaded from here or you can use our FromDual repositories. How to install and use fpmmm is documented in the fpmmm Installation Guide.

In the inconceivable case that you find a bug in the FromDual Performance Monitor for MariaDB please report it to the FromDual Bug-tracker or just send us an [email](mailto:contact@fromdual.com?Subject=Bug report for fpmmm).

Any feedback, statements and testimonials are welcome as well! Please send them [to us](mailto:feedback@fromdual.com?Subject=Feedback …

Translated by deepl.com

Introduction

Two points in advance:

1. A deadlock is a state in which two different transactions are no longer able to continue working because each transaction holds a lock that the other transaction would need. Because both transactions are now waiting for the other transaction to release their locks, neither transaction will release their respective locks. And that would last forever. To avoid this, the MariaDB instance intervenes and kills the transaction that has done less work. The application then receives a deadlock error message of this type:

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

2. A general mantra in the MariaDB ecosystem is that a SELECT does not cause locks (exception: FOR UPDATE or LOCK IN SHARE MODE) and therefore cannot be part of a deadlock.

The problem

A long-standing customer comes to the FromDual remote DBA team with a request to explain a deadlock situation:

Hello FromDual Team, I need your expertise on the subject …