You are here

Shinguz's blog

Comparing Optimizer Results

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.

Migration of your data from one database to another

Taxonomy upgrade extras: 

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:

Linux Container with LXD for focmm unit testing

Currently we are in the testing phase of the next release of our Ops Center (focmm).

FromDual Performance Monitor for MariaDB/MySQL allows SNMP monitoring

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.

FromDual Performance Monitor for MariaDB 2.0.0 has been released

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.

MariaDB MaxScale Load Balancer with Master/Master Replication

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 (Ma) and one passive Master (Mp). Whereas the passive Master was set to read_only. As a replication user we used the user replication.

Streaming backup with MariaDB Backup

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

Sharding do-it-yourself

Taxonomy upgrade extras: 

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.

Containers and databases

Taxonomy upgrade extras: 

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:

Learning from the Bugs Database

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:

MariaDB Deadlocks

Taxonomy upgrade extras: 

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:

MariaDB Devroom at FOSDEM 2022 CfP is now open

Taxonomy upgrade extras: 

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.

MariaDB Connection ID

Taxonomy upgrade extras: 

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:

MariaDB / MySQL Advanced training end of October 2021

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.

Automated MariaDB restore tests

Taxonomy upgrade extras: 

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:

MariaDB/MySQL Environment MyEnv 2.0.3 has been released

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.

Query performance comparison between MariaDB ColumnStore and other Storage Engines

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.

This is how the table sales_fact looks like:

FromDual Ops Center for MariaDB, MySQL and compatible databases 1.1.0 has been released

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!

Limiting MySQL tmpdir size

Taxonomy upgrade extras: 

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.

Do not trust other peoples benchmarks!

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

What has changed:

MariaDB configuration analysis

Taxonomy upgrade extras: 

If we do customers database configuration analysis we check on one side if the most important MariaDB server system variables (innodb_buffer_pool_size, ...) are set appropriately but also if some MariaDB server system variables are configured completely wrong.

Fortunately MariaDB introduced in MariaDB 10.1 the INFORMATION_SCHEMA.SYSTEM_VARIABLES view where you can find all the relevant information. But one!


Subscribe to RSS - Shinguz's blog