Feed Aggregator

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 2.1.0 Release Notes: MariaDB/MySQL Environment MyEnv 2.1.0 has been released (2024-02-28)
• MyEnv 2.0.3 Release Notes: MariaDB/MySQL Environment MyEnv 2.0.3 has been released (2021-07-02)
• MyEnv 2.0.2 Release Notes: MariaDB/MySQL Environment MyEnv 2.0.2 has been released (2019-01-17)
• MyEnv 2.0.1 Release Notes: MariaDB/MySQL Environment MyEnv 2.0.1 has been released (2018-10-03)
• MyEnv 2.0.0 Release Notes: MySQL Environment MyEnv 2.0.0 has been released (2018-03-15)
• MyEnv 1.3.1 Release Notes: MySQL Environment MyEnv 1.3.1 has been released (2016-08-03)
• MyEnv 1.3.0 Release Notes: MySQL Environment MyEnv 1.3.0 has been released (2016-01-31)
• MyEnv 1.2.2 Release Notes: MySQL Environment MyEnv 1.2.2 has been released (2015-10-12)
• MyEnv 1.2.1 Release Notes: MySQL Environment MyEnv 1.2.1 has been released (2015-07-27)
• MyEnv 1.2.0 Release Notes: MySQL Environment MyEnv 1.2.0 has been released (2015-04-18)
• MyEnv 1.1.4 Release Notes: MySQL Environment MyEnv 1.1.4 has been released (2015-02-11)
• MyEnv 1.1.2 Release …

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 …

Sie haben sicher schon davon gehört, dass MySQL 5.7 im Oktober 2023 das End of Life (EOL) erreicht hat. In diesem Webinar zeigen wir Ihnen, dass die Migration von MySQL 5.7 Galera Cluster nicht schwierig ist. MySQL 8.0 ist seit 5 Jahren allgemein verfügbar, und das Galera Cluster für MySQL 8.0 hat sich seit über 3 Jahren im Markt bewährt. Es ist also wirklich an der Zeit, sich auf die Migration vorzubereiten.

Im ersten Webinar dieser Reihe werden wir uns mit den neuen Funktionen von Galera Cluster mit MySQL 8 befassen:

• Die neuen Funktionen von Galera Cluster für MySQL 8, von denen Sie profitieren können, einschliesslich der in der Galera Cluster Enterprise Edition (EE) verfügbaren Funktionen
• Wie man eine Migration von MySQL 5.7 auf MySQL 8.0 plant
• Dinge, die vor der Migration getestet werden sollten
• Häufige Fallstricke bei einer solchen Migration
• Wie Sie sicherstellen, dass Ihr Galera Cluster während der Migration ohne Ausfallzeiten weiterläuft

Das Webinar findet am Freitag, 17. November 2023, 9:00 - 10:00 …

Scenario: MariaDB Community Server is release every 3 months. The exact date is not known but it is typically about 2 to 3 weeks after the MySQL/Oracle CPU.

We upgrade regularly customer systems, also their MariaDB Galera Cluster systems. Because we made in the past some very bad experience upgrading production MariaDB Galera Clusters short after release date we became a bit more careful. Our new policy is: We wait a few weeks before installing a new release.

This week we had the situation that on Monday a new MariaDB release came out and on Wednesday we had to do the Cluster upgrade. So only 2 days in between. Too short time for my taste. But we did not want to omit the upgrade because it was already more than 6 months since the last upgrade…

So we decided to install the pre-latest MariaDB release. Technically it means: Customer was on 10.6.9, 10.6.12 is out (and already available in the repositories) but we want to upgrade to 10.6.11. Additionally we also wanted to upgrade the O/S (Debian 11). So how …

FromDual has the pleasure to announce the release of the new version 1.2.1 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 and MySQL databases and Galera Cluster farms. Ops Center makes DBA and Admins life easier!

The main task of Ops Center is to support you in your daily MariaDB and MySQL operation tasks. More information about FromDual Ops Center you can find under General Information.

Download

The new FromDual Ops Center for MariaDB and MySQL (focmm) can be downloaded from our download site. 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 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, …

FromDual has the pleasure to announce the release of the new version 1.2.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 and MySQL databases and Galera Cluster farms. Ops Center makes DBA and Admins life easier!

The main task of Ops Center is to support you in your daily MariaDB and MySQL operation tasks. More information about FromDual Ops Center you can find under General Information.

Download

The new FromDual Ops Center for MariaDB and MySQL (focmm) can be downloaded from our download site. 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 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, …

Table of Contents

• SystemD Notizen
• InnoDB NUMA interleave with MariaDB
• Checking Galera Cluster SST timeout with SystemD
• Kill MariaDB Galera node under SystemD
• MariaDB Memory locking (--memlock) with SystemD
• Who is restarting my mariadbd?
• Location of SystemD unit files

SystemD Notizen

SystemD paged per default nach less. Das ist ungewohnt und doof, weil es gegen allgemeines Linux-Verhalten verstösst. Abhilfe schafft:

shell> systemctl --no-pager status
shell> alias systemctl='systemctl --no-pager'

Das selbe können wir auch verwenden, wenn wir bei MariaDB start/stop Problemen im SystemD Journal nachschauen müssen:

shell> journalctl --no-pager -xe --unit=mariadb

Eine weitere Herausforderung könnte die Anpassung des MariaDB Unit Files sein:

shell> update-alternatives --config editor
shell> export SYSTEMD_EDITOR=vi
shell> export EDITOR=vi
shell> export VISUAL=vi
shell> export SELECTED_EDITOR="/usr/bin/vim.basic"
# C-x C-e
# /etc/systemd/system/mariadb.service.d/override.conf …

A while ago I read a paper about Optimizer Benchmarks: How Good Are Query Optimizers, Really? by Viktor Leis from Fakultät für Informatik of the Technische Universität München.

Because we have from time to time problems with slow queries of customers especially after upgrading to new database releases it is interesting for me, how different optimizers cope with a query. Sometimes it is not clear to me why the query is slow or how I can make it faster. So the mentioned paper inspired me to compare the same query with the same dataset among different optimizers or optimizer versions. The most recent query from our customer we were testing against MariaDB 10.6, 10.9, 10.10, MySQL 8.0 and PostgreSQL 15.1.

The test data

The table we were using is our generic test table which many people already know from our MariaDB and MySQL trainings:

CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `data` varchar(128) DEFAULT NULL,
  `ts` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE …

Before you consider migrating your data from MySQL to another database you have to know which objects have to be migrated.

With this query you will find the objects to consider:

SELECT TABLE_SCHEMA AS `SCHEMA`, IF(TABLE_TYPE = 'BASE TABLE', 'TABLE', TABLE_TYPE) AS OBJECT_TYPE, TABLE_NAME AS `OBJECT_NAME`
     , IFNULL(ENGINE, '') AS ENGINE, IFNULL(TABLE_ROWS, '') AS `ROWS`
     , IFNULL(DATA_LENGTH, '') AS DATA_SIZE, IFNULL(INDEX_LENGTH, '') AS INDEX_SIZE
  FROM information_schema.tables
 WHERE TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
UNION
SELECT '', IF(is_role = 'Y', 'ROLE', 'USER'), CONCAT("'", user, "'", '@', "'", host, "'") AS OBJECT_TYPE, '', '', '', ''
  FROM mysql.user
UNION
SELECT db, type, name, '', '', '', ''
  FROM mysql.proc
 WHERE db NOT IN ('sys', 'information_schema', 'performance_schema', 'mysql')
UNION
SELECT db, 'EVENT', name, '', '', '', ''
  FROM mysql.event
UNION
SELECT trigger_schema, 'TRIGGER', …

If you do not feel at home on the command line, you may prefer to use a graphical user interface (GUI). These also have advantages, for example when displaying data or structures, or they can simplify and therefore speed up work processes.

There are two main types of graphical user interfaces: On the one hand, the Integrated Development Environments (IDE), and on the other hand, tools that support the administration of the database.

This list contains all known graphical user interfaces for MariaDB and MySQL:

Sometimes SQL developers ask me if there is a difference between putting the Filter in the WHERE Clause and putting the Filter in the ON Clause and if yes, what the difference is. To explain it the best I have stolen a little example (Source at the end):

CREATE TABLE department (
  department_id   INT UNSIGNED NOT NULL
, department_name VARCHAR(14)
, location        VARCHAR(13)
, PRIMARY KEY (department_id)
);

INSERT INTO department (department_id, department_name, location)
VALUES
  (10, 'ACCOUNTING', 'NEW YORK')
, (20, 'RESEARCH',   'DALLAS')
, (30, 'SALES',      'CHICAGO')
, (40, 'OPERATIONS', 'BOSTON')
;

CREATE TABLE employee (
  employee_id   INT UNSIGNED NOT NULL
, employee_name VARCHAR(10)
, job           VARCHAR(9)
, manager_id    INT
, hire_date     DATE
, salary        NUMERIC(7, 2)
, commission    NUMERIC(7, 2)
, department_id INT UNSIGNED NOT NULL
, PRIMARY KEY (employee_id)
);

INSERT INTO employee (employee_id, employee_name, job, manager_id, hire_date, salary, commission, department_id) …