FromDual TechFeed (en)

We see at various customers the request for consolidating their MariaDB and MySQL infrastructure. The advantage of such a measure is clear in the first step: Saving costs! And this request comes typically from managers. But what we unfortunately see rarely is to question this request from the IT engineering perspective. Because it comes, as anything in life, with some “costs”. So, saving costs with consolidation on one side comes with “costs” for operation complexity on the other …

FromDual has the pleasure to announce the release of the new version 1.0.2 of its popular Database Performance Monitor for MariaDB, MySQL, Galera Cluster and Percona Server fpmmm.

The new FromDual Performance Monitor for MariaDB and MySQL (fpmmm) can be downloaded from here. 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 Manager for MariaDB and MySQL please report it the FromDual Bugtracker or just send us …

Since January 2019 FromDual tries to contribute actively a little bit against global warming too.

The best for the climate would be to NOT travel to the customer at all! For this cases we have our FromDual remote-DBA services for MariaDB and MySQL.

But sometimes customer wants or needs us on-site for our FromDual in-house trainings or our FromDual on-site consulting engagements. In these cases we try to travel by train. Travelling by train is after walking or travelling by bicycle the most climate friendly …

FromDual has the pleasure to announce the release of the new version 2.1.0 of its popular Backup and Recovery Manager for MariaDB and MySQL (brman).

The new FromDual Backup and Recovery Manager can be downloaded from here. How to install and use the Backup and Recovery Manager is describe 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 …

MySQL Enterprise Backup (MEB) is a bit limited related to support of older MySQL versions. So you should consider the following release matrix:

* MySQL Enterprise Backup 8.0.15 only supports MySQL 8.0.15. For earlier versions of MySQL 8.0, use the MySQL Enterprise Backup version with the same version number as the server.

MySQL Enterprise Backup is available for …

The FromDual Enterprise Tools are licensed under a FromDual commercial license. The one exception is MyEnv which is licensed under GPLv2.

MyEnv

MyEnv is licensed under GPLv2.

FromDual Enterprise Tools

The FromDual Enterprise Tools are free of costs for non-commercial and non-profit use. If FromDual considers your usage as non-commercial or non-profit must be discussed. Just testing the functionality of the software for evaluation or learning purposes FromDual considers as non-commercial usage.

The following …

Early adopters of our customers are considering to upgrade to MySQL 8.0. To be prepared we have created a check-list:

• RTFM: Upgrading MySQL. Really! This time is different than all the years before…
• Upgrade Path:
  • Only Upgrade from 5.7 to 8.0 is supported (skipping 5.7 is NOT supported!). So Upgrade to 5.7 first!
  • Only Upgrade between GA releases (5.7.9+, 8.0.11+) is supported.
  • Recommended: Upgrade to newest 5.7 first (5.7.25).
• Check for Incompatibility Changes, Deprecated and Removed Features: …

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

See also Byobu.

See also our older article: Using screen for support and/or surveillance.

First simple steps

The command tmux starts a tmux server and opens a new session with a (pseudo) terminal:

shell> tmux

To leave a tmux session again just type Ctrl+d inside your tmux session or:

tmux> exit

If you want to give a tmux session a specific name you can start tmux as follows to created a named session:

shell> tmux new -s mariadb

or if you are already inside tmux:

tmux> Ctrl+b $

followed by a …

As we already stated in earlier articles in this blog
[1 and 2
] it is a good idea to use NULL values properly in MariaDB and MySQL.

One of my Mantras in MariaDB performance tuning is: Smaller tables lead to faster queries! One consequence out of this is to store NULL values instead of some dummy values into the columns if the value is not known (NULL: undefined/unknown).

To show how this helps related to space used by a table we created a little example:

CREATE TABLE big_null1 (
  id INT UNSIGNED NOT NULL …

Yes, I am aware that this is not a NULL issue. I hope my blog did not tell that this is be cause of NULL.

> Well, its title is "Indexing of NULL values", so if one takes that at face value, I'm afraid they will think it's about special treatment of NULL values :-).

The problem is, that MariaDB (and MySQL) chooses the wrong query execution plan for columns with low cardinality and chooses a 3 times slower plan (Index Lookup instead of FTS).

> Yes, agree.
(For me it is 2.2x slower. I would say …

Hello,

Just wanted to say that the post misses the point. The problem is not NULL values as such. the problem is:

• A) InnoDB returns very optimistic estimates for low-cardinality values and
• B) cost model is not fully adequate.

Here is an example demonstrating the same effect with a non-NULL value: https://gist.github.com/spetrunia/2c6ea05ed6b0c1ffa14fa8eac6b72029 . We still get 400K rows as an estimate (instead if 990K) . The estimate is provided by InnoDB.

Let's try it with MyRocks storage …

We see sometimes at customers that they have very big InnoDB system tablespace files (ibdata1) although they have set innodb_file_per_table = 1.

So we want to know what else is stored in the InnoDB system tablespace file ibdata1 to see what we can do against this unexpected growth.

First let us check the size of the ibdata1 file:

# ll ibdata1 
-rw-rw---- 1 mysql mysql 109064486912 Dez  5 19:10 ibdata1

The InnoDB system tablespace is about 101.6 Gibyte in size. This is exactly 6'656'768 InnoDB blocks of 16 …

In the recent MariaDB DBA advanced training class the question came up if MariaDB can make use of an index when searching for NULL values… And to be honest I was not sure any more. So instead of reading boring documentation I did some little tests:

Search for NULL

First I started with a little test data set. Some of you might already know it:

CREATE TABLE null_test (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
, data VARCHAR(32) DEFAULT NULL
, ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP() 
); …

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

# free
              total        used        free      shared  buff/cache   available
Mem:       16106332     3914228      367072      296508    11825032    11441608
Swap:      31250428      182924    31067504

# cat /proc/meminfo | grep ^Swap
SwapCached:        10056 kB
SwapTotal:      31250428 kB
SwapFree:       31067504 kB

# for file in /proc/*/status ; do
  awk '/VmSwap|Name/{
    if ( $2 != "0" )
      printf $2 " " $3
  }END{ print ""}' $file
done | grep kB | sort -k 2 …

A while ago we released our FromDual Backup and Recovery Manager (brman) 2.0.0 for MariaDB and MySQL. So what are the new cool features of this new release?

First of all brman 2.0.0 is compatible with MariaDB 10.3 and MySQL 8.0:

shell> bman --target=brman:secret@127.0.0.1:3318 --type=full --mode=logical --policy=daily

Reading configuration from /etc/mysql/my.cnf
Reading configuration from /home/mysql/.my.cnf
No bman configuration file.

Command line: /home/mysql/product/brman-2.0.0/bin/bman.php …

FromDual has the pleasure to announce the release of the new version 2.0.0 of its popular Backup and Recovery Manager for MariaDB and MySQL (brman).

The new FromDual Backup and Recovery Manager can be downloaded from here. How to install and use the Backup and Recovery Manager is describe 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 …

MariaDB Bug number 16476: https://jira.mariadb.org/browse/MDEV-16476. Seems like it will be fixed soon…?

MariaDB 10.3 was released GA a few weeks ago. One of the features which interests me most is the MariaDB Oracle PL/SQL compatibility mode.

So its time to try it out now…

Enabling Oracle PL/SQL in MariaDB

Oracle PL/SQL syntax is quite different from old MySQL/MariaDB SQL/PSM syntax. So the old MariaDB parser would through some errors without modification. The activation of the modification of the MariaDB PL/SQL parser is achieved by changing the sql_mode as follows:

mariadb> SET SESSION …