Feed Aggregator

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 an [email](mailto:contact@fromdual.com?Subject=Bug report for fpmmm).

Any feedback, statements and testimonials are welcome as well! Please send them to [feedback@fromdual.com](mailto:feedback@fromdual.com?Subject=Feedback for fpmmm “Feedback for fpmmm”).

Monitoring as a Service (MaaS)

You do not want to set-up your Database monitoring yourself? No problem: Choose our MariaDB and MySQL Monitoring as a Service (Maas) program to safe costs!

Upgrade from 1.0.x to 1.0.2

shell> cd /opt
shell> …

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 way to travel:

• Die SBB steigt um auf Bahnstrom aus 100 Prozent erneuerbarer Energie
• Deutsche Bahn: 50 Prozent weniger CO2-Austoß bis 2030 • Fernverkehr ab 2018 mit 100 Prozent Ökostrom
• Deutsche Bahn steigert Ökostrom bis 2030 auf 80 Prozent

But some customers are located more than 7 to 8 hours far away by train. For these customers we have to take the plan which is not good for the climate at all. But at least we will compensate for our CO₂ emission via MyClimate.org:

<img …

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 [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 1.2.x to 2.1.0

brman 2.1.0 requires a new PHP package for ssh connections.

shell> sudo apt-get install php-ssh2

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

Changes in FromDual Backup and Recovery Manager 2.1.0

This release is a new …

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 download from the My Oracle Support (MOS) website. This release will be available on Oracle eDelivery (OSDC) after the next upload cycle. MySQL Enterprise Backup is a commercial extension to the MySQL family of products.

As an Open Source alternative Percona XtraBackup for MySQL databases is available.

Compatibility with MySQL Versions: 3.11, 3.12, 4.0, 4.1, 8.0.

MySQL Enterprise Backup User’s Guide: 3.11, 3.12, 4.0, 4.1, 8.0.

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 FromDual Enterprise Tools are falling under the FromDual commercial license:

• FromDual Backup and Recovery Manager for MariaDB and MySQL (brman)
• FromDual Operations Center for MariaDB and MySQL (focmm)
• FromDual Performance Monitor for MariaDB and MySQL (fpmmm)
• FromDual Nagios Plug-ins for MariaDB and MySQL

For commercial use a yearly fee is applicable. Please [ask FromDual for a quote](mailto:contact@fromdual.com?Subject=Quote for FromDual Enterprise Tools).

For all FromDual Enterprise Tools FromDual …

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:
  • Incompatibility Changes in MySQL 8.0
  • Removals:
    • Query Cache was removed.
    • No support for non-native partitioning (MyISAM, Archive, etc.)
    • Reserved keywords (application changes)
    • No own tables in MySQL schema
    • No Foreign Key Constraint names longer than 64 characters
    • No obsolete sql_mode definitions (requires application changes)
    • Check ENUM and SET length (
      < 255 characters/1020 bytes)
    • No tables in System Tablespace (ibdata1) or General Tablespaces!
    • GROUP BY with ASC or DESC is deprecated (application change) …

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

shell> cd ${HOME}/product
shell> tar xf /download/myenv-2.0.2.tar.gz
shell> rm -f myenv
shell> ln -s myenv-2.0.2 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 …

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 session name where only the first 9 characters are shown in the overview:

List available tmux sessions

To list the available tmux sessions we have the tmux list-sessions command:

shell> tmux list-sessions
1: 1 windows (created Sun Dec 23 13:35:37 2018) [117x33]
mariadb-104: 1 windows (created Sun Dec 23 13:13:46 2018) [130x41] (attached)

If there is no session available we will get the following error:

shell> tmux ls
failed to connect to server

tmux help

To get more information about tmux you can …

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 AUTO_INCREMENT PRIMARY KEY
, c01 VARCHAR(32) NOT NULL
, c02 VARCHAR(32) NOT NULL
, c03 VARCHAR(32) NOT NULL
, c04 VARCHAR(32) NOT NULL
, c05 VARCHAR(32) NOT NULL
, c06 VARCHAR(32) NOT NULL
, c07 VARCHAR(32) NOT NULL
, c08 VARCHAR(32) NOT NULL
, c09 VARCHAR(32) NOT NULL
, c10 VARCHAR(32) NOT NULL
, c11 VARCHAR(32) NOT NULL
, c12 VARCHAR(32) NOT NULL
, INDEX (c03)
, INDEX (c06)
, INDEX (c09)
);

CREATE TABLE big_null2 (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
, c01 VARCHAR(32) NOT NULL
, c02 …

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 that in general, if the optimizer is within 2x difference of the best query plan, things are ok. There are many factors affecting query execution speed (e.g. OS/engine cache population, correlation of conditions, etc) - it is not realistic to expect the optimizer to always pick the best plan. One should aim for query plans that are not more than N times slower than the best one, where the value of N is about 2 for simple queries and not more than 10 for complex queries).

If this is InnoDB related or not is …

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 engine: https://gist.github.com/spetrunia/5445ef362aa26207ccbf83c093d7135b . The optimizer here picks a full table scan for the query with "data = 'Some data to show if null works'", which is faster.

BR,
-- Sergei Petrunia

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 kibyte block size.

So next we want to analyse the InnoDB system tablespace ibdata1 file. For this we can use the tool innochecksum:

# innochecksum --page-type-summary ibdata1 
Error: Unable to lock file:: ibdata1
fcntl: Resource temporarily unavailable

But… the tool innochecksum throughs an error. It seems like it is not allowed to analyse the InnoDB system tablespace with a running database. So then let us stop the database first and try it again. Now we get a useful output:

# innochecksum …

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() 
);

INSERT INTO null_test VALUES (NULL, 'Some data to show if null works', NULL);
INSERT INTO null_test SELECT NULL, 'Some data to show if null works', NULL FROM null_test;
... up to 1 Mio rows

Then I modified the data according to my needs to see if the MariaDB Optimizer can make use of the index:

-- Set 0.1% of the rows to NULL
UPDATE null_test SET data = NULL WHERE ID % 1000 = 0;

ALTER TABLE null_test ADD INDEX (data);

ANALYZE TABLE null_test;

and finally I run the test (MariaDB 10.3.11):

EXPLAIN EXTENDED …

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 [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.0 to 2.0.1

shell> cd ${HOME}/product
shell> tar xf /download/myenv-2.0.1.tar.gz
shell> rm -f myenv
shell> ln -s myenv-2.0.1 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 …

# 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 -n -r

mysqld 29988 kB
plasmashell 19096 kB
kwin_x11 13444 kB
Xorg 13328 kB
krunner 11200 kB
knotes 5768 kB
kded5 5016 kB
korgac 4760 kB
kglobalaccel5 3288 kB
kwalletd 3148 kB
kaccess 3148 kB
polkit-kde-auth 2704 kB
xembedsniproxy 2660 kB
akonadi_control 2652 kB
kwalletd5 2616 kB
python 2572 kB
kactivitymanage 2508 kB
klauncher 2352 kB
kdeinit5 2324 kB
ksmserver 2256 kB
python 1480 kB
pulseaudio 1312 kB
sddm-helper 1172 kB
baloo_file 1172 kB
kscreen_backend 1132 kB
mission-control 1104 kB
python 992 kB …

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 --target=brman:******@127.0.0.1:3318 --type=full --mode=logical --policy=daily 

Options from command line
  target          = brman:******@127.0.0.1:3318
  type            = full
  mode            = logical
  policy          = daily

Resulting options
  config          = 
  target          = brman:******@127.0.0.1:3318
  type            = full
  mode            = logical
  policy          = daily
  log             = ./bman.log
  backupdir       = /home/mysql/bck
  catalog-name    = brman_catalog

Logging to …

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 [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 1.2.x to 2.0.0

brman 2.0.0 requires a new PHP package for ssh connections.

shell> sudo apt-get install php-ssh2

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

Changes in FromDual Backup and Recovery Manager 2.0.0

This release is a new …

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 sql_mode=ORACLE;

or you can make this setting persistent in your my.cnf MariaDB configuration file:

[mysqld]

sql_mode = ORACLE

To verify if the sql_mode is already set you can use the following statement:

mariadb> pager grep --color -i oracle
PAGER set to 'grep --color -i oracle'
mariadb> SELECT @@sql_mode;
| PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT |
mariadb> nopager

Nomen est omen

First of all I tried the …

Due to a strong customer demand FromDual offers 2018 two extra MySQL/MariaDB trainings with its Training partner The Linuxhotel in Essen (Germany). Those trainings are in English.

• MariaDB Performance Tuning on 5 and 6 September 2018 (2 days).
• Advanced MySQL/MariaDB training on 26 to 30 November 2018 (5 days).

More information about the contents of the trainings can be found at

Advanced MySQL and MariaDB training.

For conditions and booking: MariaDB Performance Tuning and Advanced MySQL Training.

For specific MariaDB or MySQL on-site Consulting or in-house Training please get in [contact](mailto:contact@fromdual.com?Subject=Specific MariaDB/MySQL on-site Consulting or in-house Training) with us.