Feed Aggregator

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:

$ systemctl --no-pager status
$ 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:

$ journalctl --no-pager -xe --unit=mariadb

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

$ update-alternatives --config editor
$ export SYSTEMD_EDITOR=vi
$ export EDITOR=vi
$ export VISUAL=vi
$ export SELECTED_EDITOR="/usr/bin/vim.basic"
# C-x C-e
# /etc/systemd/system/mariadb.service.d/override.conf
$ systemctl show mariadb | grep NOFILE
$ systemctl edit mariadb …

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) …

Currently we are in the testing phase of the next release of our Ops Center (focmm). This testing is quite complex because it includes testing the interaction of various different components like a MariaDB Galera Cluster, a Galera Load Balancer, a virtual IP, etc.

Recently I was in the Linuxhotel for a Galera Cluster Training and there one of the other trainers was romanticising about Linux Containers and Incus. So I had a short look, if Incus could be useful to simplify our unit testing. Some old knowledge from a previous Docker PoC was quite helpful to start with…

Prepare an Incus container for Galera Load Balancer

This did NOT work as expected because the Debian image was lacking IPv4 addresses and I did not find on the quick how to change that. So I used the Ubuntu 22.04 image.

$ incus remote list
$ incus image list images: ubuntu/22.04 amd64

$ INSTANCE='qa-glb'

$ incus launch images:ubuntu/jammy ${INSTANCE}
$ incus list *glb* …

I used to maintain Flexviews, but I ported it to use a native MySQL syntax. Now CREATE INCREMENTAL MATERIALIZED VIEW … is a reality. Inner join and all aggregation functions are supported. Check out http://www.leapdb.com

The newest release of the Performance Monitor for MariaDB and MySQL v2.0.0 (fpmmm) allows you to monitor your MariaDB and MySQL databases via SNMP (Simple Network Management Protocol).

This feature enables you to report your Database Metrics into Enterprise Monitoring solutions from Microsoft, IBM or into Oracle Cloud Control.

In this article we will show you the few simple steps it needs to send the data from your fpmmm Agent to your SNMP Manager.

Check if SNMP Manager can be reached

To make sure fpmmm can send data to the SNMP Manager at all you can test the connection with the following command to send SNMP Notifications to the SNMP Manager:

# 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=$(mariadb --user=root --execute="SELECT variable_value FROM information_schema.global_status WHERE variable_name LIKE 'threads_running'<br>G" | grep variable_value | cut -d' ' -f2)

# snmptrap -v 2c -c …

FromDual has the pleasure to announce the release of the new version 2.0.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 …

For this how-to we were working with a MariaDB 10.6.5 database server and the newest MariaDB MaxScale 6.3.0 (aka 2.6) load balancer/proxy.

As a starting point we had a working MariaDB Master/Master replication with one active Master (M_a) and one passive Master (M_p). Whereas the passive Master was set to read_only. As a replication user we used the user replication.

Creating database accounts for the MaxScale Load Balancer

The MaxScale load balancer connects itself with the application users to the database. To do this it needs to retrieve the available users from the database first. To get these users the MaxScale account needs some privileges:

CREATE USER 'maxscale_admin'@'%' IDENTIFIED BY 'secret';

GRANT SELECT ON mysql.user TO 'maxscale_admin'@'%';
GRANT SELECT ON mysql.db TO 'maxscale_admin'@'%';
GRANT SELECT ON mysql.tables_priv TO 'maxscale_admin'@'%';
GRANT SELECT ON mysql.columns_priv TO 'maxscale_admin'@'%';
GRANT SELECT ON mysql.procs_priv TO 'maxscale_admin'@'%';
GRANT SELECT ON mysql.proxies_priv …

Because the original MariaDB documentation is a little bit sparse about this topic here are some notes about how to do a streaming MariaDB backup:

# mariadb-backup --user=root --backup --parallel=4 --stream=mbstream | ssh root@target_machine "/usr/bin/mbstream -x -C /var/lib/mysql/"

# mariadb-backup --user=root --prepare --target-dir=/var/lib/mysql

FromDual has the pleasure to announce the release of the new version 2.2.5 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](mailto:contact@fromdual.com?Subject=Bug report for brman).

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

Upgrade from 2.x to 2.2.5

shell> cd ${HOME}/product
shell> tar xf /download/brman-2.2.5.tar.gz
shell> rm -f brman
shell> ln -s brman-2.2.5 brman

Changes in FromDual Backup and Recovery Manager 2.2.5

This release is a new minor release. It contains mainly bug fixes. We have tried to …

Are containers so popular because application developers do not know how to write multi-threaded applications well? With containers you can circumvent this problem a bit. You ramp up man single threaded applications in containers and thus get a multi-threaded behaviour.
Databases ARE already multi-threaded. So no need to containerize them.

As already mentioned earlier, we roughly have a hand full of customers which are playing with the though of sharding solutions. They typically have many different customers (clients, tenants) and the number of customers becomes so huge (thousands to millions) that one machine cannot cope with the load any more.

So splitting the load by customers to different machines makes sense. This is quite easy when customers are separated per schema. In the good old times of Open Source our customers have implemented those solutions themselves. But nowadays it looks like do-it-yourself is not sexy any more. It seems like this core competence of a business advantage must be outsourced. So some vendors have already made some solutions available to solve this need: Sharding Solutions.

My question here is: Can a generic sharding solution build exactly what your business needs? Are you still capable to optimize your business process in the way you need it when you buy a 3^rd party solution?

And: If you use another product, you …

In the last months we got more and more requests for supporting MariaDB/MySQL/Galera Cluster in (mostly Docker) containers.

Because of its additional layer and added complexity I do not like containers much. Containers are more complicated during troubleshooting and debugging problems.

Other people have already written more than enough about the advantages of containers. What is more difficult to find are the disadvantages of technologies. Thus I focus on those:

Wrong technology?

Container solutions were designed to deal with stateless applications that have ephemeral data. Containers spin up a quick microservice and then destroy it. This includes all the components of that container (including its cache and data). The transient nature of containers is because all of the components and services of that container are considered to be part of the container (essentially it is all or nothing). Serving the container a data volume owned by the underlying O/S by punching a hole through the container can be very …

Once in a year or so we get a request of a customer about MariaDB/MySQL sharding solutions. So here we have a list of sharding solutions we are currently aware of:

• MySQL Fabric: A Brief Introduction to MySQL Fabric This product is discontinued by MySQL in the meanwhile!
• Spider Storage Engine for MariaDB: Spider Storage Engine Overview
• MariaDB MaxScale: Simple Sharding with Two Servers and Schema Router
• ProxySQL: Sharding in ProxySQL
• PingCap TiDB (Titan DB): Release Candidate der verteilten Datenbank TiDB veröffentlicht and PingCap
• Vitess: What is Vitess

What does it need for Schema sharding

• Metadata database: Where you record which customer schema is located in which shard (with a locking mechanism).
• Your application connector or a “sharding facility” must do the (re-)direction.
• A re-sharding mechanism for “load balancing”.
• A good Monitoring per schema and per shard so you know which schema causes more load and which shard is soon overloaded.

See also our blog post: Sharding …