The FromDual Blogs on FromDual GmbH

Short term notice: Percona XtraDB Cluster training in English 7/8 February 2018 in Germany

oli.sennhauser@fromdual.com (Oli Sennhauser) — Fri, 19 Jan 2018 17:05:29 +0000

FromDual offers short term a Percona XtraDB Cluster and MySQL Galera Cluster training (2 days) in English.

Xtrabackup in a nutshell

oli.sennhauser@fromdual.com (Oli Sennhauser) — Wed, 06 Nov 2013 14:23:50 +0000

Introduction

No one can deny that one of the most important and daily tasks for DBAs is performing backup and restore operations, we’re not required to perform backup and restore operations only when we want to add new replication slave, when we want to implement disaster recovery procedures or when we want to prepare testing or staging server for the running production system, but even if we’re going to make any changes to the database schema in order to enhance the database performance, it’s recommended to have fresh backup copy before making any live changes, so if backup and restore operations cannot be handled smoothly, we’re going to face many troubles in our daily work. If we’re going to talk about backup and restore operations, Xtrabackup tool will be strongly appeared.

Quick links to the MySQL documentation

oli.sennhauser@fromdual.com (Oli Sennhauser) — Mon, 15 Mar 2010 15:50:27 +0100

MySQL Statement Syntax

Data Definition Language (DDL) Statements

ALTER DATABASE	ALTER EVENT	ALTER LOGFILE GROUP	ALTER FUNCTION
ALTER PROCEDURE	ALTER SERVER	ALTER TABLE	ALTER TABLESPACE
ALTER VIEW	CREATE DATABASE	CREATE EVENT	CREATE FUNCTION
CREATE INDEX	CREATE LOGFILE GROUP	CREATE PROCEDURE	CREATE FUNCTION
CREATE SERVER	CREATE TABLE	CREATE TABLESPACE	CREATE TRIGGER
CREATE VIEW	DROP DATABASE	DROP EVENT	DROP FUNCTION
DROP INDEX	DROP LOGFILE GROUP	DROP PROCEDURE	DROP FUNCTION
DROP SERVER	DROP TABLE	DROP TABLESPACE	DROP TRIGGER
DROP VIEW	RENAME DATABASE	RENAME TABLE

Data Manipulation Language (DML) Statements

CALL	DELETE	DO	HANDLER
INSERT	LOAD DATA INFILE	REPLACE	SELECT
TRUNCATE TABLE	UPDATE

MySQL Utility Statements

MySQL Transactional and Locking Statements

START TRANSACTION	COMMIT	ROLLBACK	SAVEPOINT
ROLLBACK TO SAVEPOINT	LOCK TABLES	UNLOCK TABLES	SET TRANSACTION
XA Transactions

Account Management Statements

CREATE USER	DROP USER	GRANT	RENAME USER
REVOKE	SET PASSWORD

Table Maintenance Statements

ANALYZE TABLE	BACKUP TABLE	CHECK TABLE	CHECKSUM TABLE
OPTIMIZE TABLE	REPAIR TABLE	RESTORE TABLE

Plugin and User-Defined Function Statements

`SHOW` Statements

SHOW AUTHORS

Why is Naomi Cannon bashing MariaDB?

oli.sennhauser@fromdual.com (Oli Sennhauser) — Mon, 21 Sep 2026 15:25:00 +0200

10 days ago, an article by Naomi Cannon on Reddit caught my attention; it included a link to the full article on Medium.

Percona releases Galera Cluster Version 9.7

oli.sennhauser@fromdual.com (Oli Sennhauser) — Mon, 14 Sep 2026 11:33:00 +0200

Finally, some good news again!

What has happened so far

After MariaDB Corp. acquired Codership OY—and with it, Galera Cluster for MySQL—in May 2025 [ 1 ], then announced the discontinuation of support for Galera Cluster for MySQL 8.4 at the end of September 2026 [ 2 ], and subsequently stated that it would no longer support Galera Cluster (for MariaDB!) in the Community Edition [ 3 ], there was a loud outcry from the community. MariaDB Corp. then made a partial retreat (“We’ve thoroughly considered your feedback and decided that now is not the time for a major change,”). A clear commitment to continued support for Galera Cluster in the Community Edition sounds different.

Recovering PostgreSQL Data When Blocks Are Corrupted

oli.sennhauser@fromdual.com (Oli Sennhauser) — Tue, 01 Sep 2026 10:12:00 +0200

As part of our work on “PostgreSQL for Dolphins and Sea Lions,” I also enjoy reading the PostgreSQL mailing lists to see what kinds of problems come up and how they’re solved.

Making the PostgreSQL Documentation Even Better

oli.sennhauser@fromdual.com (Oli Sennhauser) — Tue, 11 Aug 2026 08:40:00 +0200

While working on our project “PostgreSQL for Dolphins and Sea Lions,” I pored over the PostgreSQL documentation on replication.

Artificially Delayed Replication with PostgreSQL

oli.sennhauser@fromdual.com (Oli Sennhauser) — Mon, 03 Aug 2026 09:53:00 +0200

While working on our project titled “PostgreSQL for Dolphins and Sea Lions,” I pored over the PostgreSQL documentation on replication. What struck me was that PostgreSQL does NOT support artificially delayed replication—at least according to this documentation.

PostgreSQL Point-in-Time Recovery with Oops-Queries

oli.sennhauser@fromdual.com (Oli Sennhauser) — Wed, 22 Jul 2026 17:26:00 +0200

While working on the topic “PostgreSQL for Dolphins and Sea Lions,” I discovered that in the PostgreSQL universe, the topic of point-in-time recovery tends to be sidestepped when it comes to specific statements or transactions. And when examples are provided, they primarily focus on DDL statements rather than DML statements.

What is the quickest way to load data into the database?

oli.sennhauser@fromdual.com (Oli Sennhauser) — Wed, 11 Feb 2026 10:04:00 +0100

We had some really exciting problems to solve for the last customer! Especially because the database wasn’t exactly small.

How much space does NULL need?

oli.sennhauser@fromdual.com (Oli Sennhauser) — Sun, 08 Feb 2026 16:15:00 +0100

The last time I consulted a customer, he came up to me beaming with joy and said that he had taken my advice and changed all the primary key columns from BIGINT (8 bytes) to INT (4 bytes) and that had made a big difference! His MySQL 8.4 database is now 750 Gbyte smaller (from 5.5 Tbyte). Nice!

Someone is deleting my shared memory segments!

oli.sennhauser@fromdual.com (Oli Sennhauser) — Sun, 08 Feb 2026 06:27:00 +0100

When we work with PostgreSQL under our myEnv, we regularly get shared memory segment errors. Example:

Load CSV files into the database

oli.sennhauser@fromdual.com (Oli Sennhauser) — Fri, 06 Feb 2026 18:04:00 +0100

Recently, I wanted to display the places of residence of the members of my club on a map for a personal gimmick (IGOC members). I knew the addresses of the club members. But not the coordinates of their places of residence.

We build a data warehouse from the General Query Log

oli.sennhauser@fromdual.com (Oli Sennhauser) — Wed, 31 Jan 2024 16:41:13 +0100

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

Linux Container with Incus for focmm unit testing

oli.sennhauser@fromdual.com (Oli Sennhauser) — Thu, 13 Oct 2022 19:11:40 +0200

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.

Data Warehouse Design

oli.sennhauser@fromdual.com (Oli Sennhauser) — Wed, 16 Jun 2021 23:02:43 +0200

This is my cheat sheet for dimensional modelling design techniques of a data warehouse (DWH) according to Kimball/Ross.

Query performance comparison between MariaDB ColumnStore and other Storage Engines

oli.sennhauser@fromdual.com (Oli Sennhauser) — Thu, 03 Jun 2021 15:53:51 +0200

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 ColumnStore stores all the same attributes (columns) of the rows together in chunks.

Create a single-node MariaDB ColumnStore test installation

oli.sennhauser@fromdual.com (Oli Sennhauser) — Wed, 19 May 2021 19:37:50 +0200

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

Do not trust other peoples benchmarks!

oli.sennhauser@fromdual.com (Oli Sennhauser) — Tue, 06 Apr 2021 13:26:06 +0200

Because they do NOT reflect your problems.

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

MySQL Binary Log Filter does not work

oli.sennhauser@fromdual.com (Oli Sennhauser) — Wed, 21 Oct 2015 05:23:36 +0200

Problem

We use the following binary log filter for filtering out some schemata.

MySQL Replication Slave is lagging more and more

oli.sennhauser@fromdual.com (Oli Sennhauser) — Fri, 18 Sep 2015 05:36:36 +0200

Problem

My MySQL Replication Slave is lagging more and more and I have no clue why. This happens since last weekends application change. I see that one Core is running at 100% user time but the I/O system is nearly completely idle. Any help is appreciated!

MySQL Replication Slave with corrupt Relay Logs

oli.sennhauser@fromdual.com (Oli Sennhauser) — Thu, 16 Jul 2015 19:19:36 +0200

Problem

MySQL Replication Slave stuck because its disk run full. After restarting the Slave MySQL replication was broken with the following error:

Support policy for MariaDB, MySQL and PostgreSQL

oli.sennhauser@fromdual.com (Oli Sennhauser) — Tue, 23 Dec 2014 15:46:54 +0100

Distribution maintenance policies
MariaDB Lifetime Support Matrix for MariaDB Releases
MariaDB Corp Enterprise Support Subscriptions
MySQL Lifetime Support Matrix for MySQL Releases
Oracle Corp Enterprise Support Subscriptions for MySQL
PostgreSQL Versioning Policy
Database Support by FromDual

Distribution maintenance policies

Maintenance policies for Long Term Support (LTS) releases of common distributions:

MariaDB and MySQL PERFORMANCE_SCHEMA Hints

oli.sennhauser@fromdual.com (Oli Sennhauser) — Sun, 14 Jul 2013 09:29:09 +0200

Accounts not properly closing connections
Unused indexes
Who created temporary (disk) tables
Accounts which never connected since last start-up
Users which never connected since last start-up
Totally unused accounts (never connected since last restart and not used to check Stored Program or View privileges) since last start-up
SHOW FULL PROCESSLIST
Storage Engines per schema
Tables without a Primary Key
Bad SQL queries of users
SHOW PROFILE in PERFORMANCE_SCHEMA
SELECT, INSERT, UPDATE and DELETE per table
Top long running queries
Tables never written to
DML, DDL and DCL per account
Finding syntactically wrong/erroneous MySQL queries
Finding queries served by MySQL Query Cache
Grant access to sys schema to another user
Grant access to PERFORMANCE_SCHEMA schema to another user
Find InnoDB Locks
Find Metadata Locks
What did a Connection before sleeping?
How many statements did another (sleeping) connection?
MyISAM locking
Connections per user
Connections per user and IP
Accounts which could not connect or with connection errors
Temporary tables filling my disk
Who is writing to the binary log
Average binlog transaction size
Result size of SELECT statement
Find open long running transactions
Duplicate and redundant indices

MariaDB

On MariaDB you have to enable the Performance Schema first before use:

Be careful when using SAN

oli.sennhauser@fromdual.com (Oli Sennhauser) — Sun, 06 Mar 2011 10:13:39 +0000

Be careful when using SAN (Storage Area Networks) or similar shared storage solutions (and any other virtualization, consolidation or cloud solutions).

The battle against Oracle is probably over but has the real war begun yet?

oli.sennhauser@fromdual.com (Oli Sennhauser) — Wed, 20 Jan 2010 09:36:00 +0100

According to different sources from the web the decision about the Oracle - Sun merger has been approved by the European commission soon. So at least in the West it is clear what is going on. Let us see what the East decides… [ 1 ], [ 2 ].

The FromDual Blogs on FromDual GmbH

Short term notice: Percona XtraDB Cluster training in English 7/8 February 2018 in Germany

Xtrabackup in a nutshell

Table of Contents

Introduction

Quick links to the MySQL documentation

MySQL Statement Syntax

Data Definition Language (DDL) Statements

Data Manipulation Language (DML) Statements

MySQL Utility Statements

MySQL Transactional and Locking Statements

Account Management Statements

Table Maintenance Statements

Plugin and User-Defined Function Statements

SHOW Statements

Why is Naomi Cannon bashing MariaDB?

Percona releases Galera Cluster Version 9.7

What has happened so far

Recovering PostgreSQL Data When Blocks Are Corrupted

Making the PostgreSQL Documentation Even Better

Artificially Delayed Replication with PostgreSQL

PostgreSQL Point-in-Time Recovery with Oops-Queries

What is the quickest way to load data into the database?

How much space does NULL need?

Someone is deleting my shared memory segments!

Load CSV files into the database

We build a data warehouse from the General Query Log

Linux Container with Incus for focmm unit testing

Data Warehouse Design

Query performance comparison between MariaDB ColumnStore and other Storage Engines

Create a single-node MariaDB ColumnStore test installation

Table of Contents

Do not trust other peoples benchmarks!

MySQL Binary Log Filter does not work

Problem

MySQL Replication Slave is lagging more and more

Problem

MySQL Replication Slave with corrupt Relay Logs

Problem

Support policy for MariaDB, MySQL and PostgreSQL

Table of Contents

Distribution maintenance policies

MariaDB and MySQL PERFORMANCE_SCHEMA Hints

Table of Contents

MariaDB

Be careful when using SAN

The battle against Oracle is probably over but has the real war begun yet?

`SHOW` Statements