FromDual TechFeed (en)

FromDual has the pleasure to announce the release of the new version 2.2.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. The FromDual Repositories were updated. 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 …

See also our former articles:

• Do not underestimate performance impacts of swapping on NUMA database systems
• MariaDB and MySQL swap analysis

When we do analysis of MariaDB Database servers we also check the memory (RAM and Swap) available:

# free --kilo --wide
              total        used        free      shared     buffers       cache   available
Mem:       16106252     4329952      703356      199008      307872    10765072    11042748
Swap:      31250428      528684    30721744

The values for …

The General Query Log in MariaDB is a Log (file or table) that logs every statement sent from the Application to the MariaDB Database Server from the Connect Command to the Quit Command. A simple example you can find here:

bin/mysqld, Version: 10.4.6-MariaDB-log (MariaDB Server). started with:
Tcp port: 3306  Unix socket: /var/run/mysqld/mysql.sock
Time                Id Command  Argument
190709 21:27:30   3872 Connect  root@localhost as anonymous on test
                  3872 Query    INSERT INTO test …

The story goes on here.

Ups, that was a quick reaction… https://jira.mariadb.org/browse/MDEV-9077

The MySQL sys Schema is a cool thing and was mostly developed by Mark Leith and Jesper Wisborg Krogh from Oracle/MySQL. The sys Schema is based on the somehow cryptic MySQL PERFORMANCE_SCHEMA which was developed by Oracle/MySQL as well.

The sys Schema is: “A collection of views, functions and procedures to help MySQL administrators get insight in to MySQL Database usage.”

Unfortunately the sys Schema was not ported from MySQL to MariaDB by the company MariaDB Corp. so far. So a good opportunity …

I could not resist! I have forked the MySQL sys Schema on GitHub.

• swappiness can have a value between 0 and 100.
• swappiness=0:
  • Kernel version 3.5 and newer: disables swappiness.
  • Kernel version older than 3.5: avoids swapping processes out of physical memory for as long as possible.
• swappiness=1:
  • Kernel version 3.5 and over: minimum swappiness without disabling it entirely.
• swappiness=100:
  • Tells the kernel to aggressively swap processes out of physical memory and move them to swap cache.

Source: …

It is Saturday night at 23:19. Time to go to bed after a hard migration day. Just a last clean-up query before finishing: Tap tap tap. Enter! - Oops!

SQL> UPDATE prospect_lists_prospects SET prospect_list_id = '73ae6cca-7b34-c4a3-5500-5d0e2674dbb6';
Query OK, 4686 rows affected (0.21 sec)
Rows matched: 5666  Changed: 4686  Warnings: 0

A verification query to make sure I am in the mess:

SQL> SELECT prospect_list_id, COUNT(*) FROM prospect_lists_prospects GROUP BY prospect_list_id; …

Why does Linux swap out pages when I have many pages cached and vm.swappiness is set to 0? Shouldn’t cached pages get resized and no swapping should occur?

https://www.quora.com/Why-does-Linux-swap-out-pages-when-I-have-many-pages-cached-and-vm-swappiness-is-set-to-0-Shouldnt-cached-pages-get-resized-and-no-swapping-should-occur

If your MariaDB or MySQL database system is swapping it can have a significant impact on your database query performance! Further it can also slow down your database shutdown and thus influence the whole reboot of your machine. This is especially painful if you have only short maintenance windows or if you do not want to spend the whole night with operation tasks.

When we do reviews of our customer MariaDB or MySQL database systems one of the items to check is Swap Space and swapping. With the free command …

PERFORMANCE_SCHEMA is invented/developed by Oracle/MySQL. So for MariaDB it is a foreign component and not supported too well...

First of all it is relevant which MariaDB version you are using and which (MySQL) PERFORMANCE_SCHEMA version is built into this MariaDB version. This information can be found with the following query:

SQL> SELECT @@version, plugin_name, plugin_auth_version
  FROM information_schema.plugins WHERE plugin_name = 'PERFORMANCE_SCHEMA'; …

Some applications have the bad behaviour to CREATE or DROP tables while our FromDual Backup Manager (bman) backup is running.

This leads to the following bman error message:

/opt/mysql/product/5.7.26/bin/mysqldump --user=dba --host=migzm96i --port=3306 --all-databases --quick --single-transaction --flush-logs --triggers --routines --hex-blob --events | tee >(md5sum --binary >/tmp/checksum.23357.md5) | gzip -1
to Destination: /var/mysql/dumps/mysql96i/daily/bck_mysql96i_full_2019-05-22_06-50-01.sql.gz …

FromDual has the pleasure to announce the release of the new version 0.9.1 of its popular FromDual Ops Center for MariaDB and MySQL focmm.

The FromDual Ops Center for MariaDB and MySQL (focmm) helps DBA’s and System Administrators to manage MariaDB and MySQL database farms. Ops Center makes DBA and Admins life easier!

The main task of Ops Center is to support you in your daily MySQL and MariaDB operation tasks. More information about FromDual Ops Center you can find here.

Download

The new FromDual Ops …

Caution: We have introduced an evil bug which prohibits installation of focmm. Sorry! Somehow it did pass our QA. To fix this bug update file lib/Upgrade.inc on Line 1965 as follows:

- $sql = sprintf("REPLACE INTO `focmm_configuration` (`key`, `value`) VALUES ('%s', '%s'), ('%s', '%s'), ('%s', '%s')"
+ $sql = sprintf("REPLACE INTO `focmm_configuration` (`key`, `value`) VALUES ('%s', '%s'), ('%s', '%s')"

In the meanwhile we prepare a new release.

FromDual has the pleasure to announce …

Last week at the MariaDB/MySQL Developer Training we had one participant asking some tricky questions I did not know the answer by heart.

Also MariaDB documentation was not too verbose (here and here).

So time to do some experiments:

Prepared Statements and Multi-Row Inserts

SQL> PREPARE stmt1 FROM 'INSERT INTO `test`.`test` (`data`) VALUES (?), (?), (?)';
Statement prepared
SQL> SET @d1 = 'Bli';
SQL> SET @d2 = 'Bla';
SQL> SET @d3 = 'Blub';
SQL> EXECUTE stmt1 USING @d1, @d2, @d3;
Query OK, 3 …

InnoDB Page Cleaner Thread

The InnoDB Page Cleaner Thread is an InnoDB background thread that flushes dirty pages from the InnoDB Buffer Pool to disk. Prior MySQL 5.6 this action was performed by the InnoDB Master Thread.

SQL> SELECT thread_id, name, type
  FROM performance_schema.threads
 WHERE name LIKE 'thread/innodb/page%'
    OR name LIKE 'thread/innodb/srv_master%';
+-----------+-----------------------------------+------------+
| thread_id | name                              | type       | …

Table of Contents

• General findings
• Migration from MySQL 5.x to MariaDB 5.5
• Migration from MySQL 5.0 to MariaDB 10.3
• Migration from MariaDB 10.0 to Percona Server 5.6
• Migration from MySQL/Percona 5.6 to MariaDB 10.0
• Migration from MySQL 5.7 to MariaDB 10.2
• Migration from MySQL 5.7 to MariaDB 10.3
• Migration from MySQL 5.7 to MariaDB 10.4
• Migration from Percona XtraDB Cluster 5.5 to MariaDB Galera Cluster 10.4
• Migration from MySQL 5.7 to MariaDB 10.5
• Migration from MySQL 5.7 to MariaDB 10.6
• Migration from …

A while ago somebody on Google Groups asked for the Uptime of a Galera Cluster. The answer is easy… Wait, no! Not so easy… The uptime of a Galera Node is easy (or not?). But Uptime of the whole Galera Cluster?

My answer then was: “Grep the error log.” My answer now is still: “Grep the error log.” But slightly different:

$ grep 'view(view_id' *
2019-03-07 16:10:26 [Note] WSREP: view(view_id(PRIM,0e0a2851,1) memb {
2019-03-07 16:14:37 [Note] WSREP: …

We had the problem today that a MySQL Galera Cluster node with the multi-tenancy pattern caused a lot of system time (sy 75%, load average about 30 (you really must read this article by Brendan Gregg, it is worth it!)) so we wanted to find what system calls are being used to see what could cause this issue (to verify if it is a TOC or a TDC problem:

$ sudo strace -c -p $(pidof -s mysqld) -f -e trace=all
Process 5171 attached with 41 threads
Process 16697 attached
^C
Process 5171 detached
...
Process 5333 …