Feed Aggregator

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 buffers and especially for cache can be sometimes quite big. In this case they use about 10 GiB. So let us have a look what these things called buffers and cache are, using our valuable RAM… When we check the man pages of free we will find:

# man free
...
buffers    Memory used by kernel buffers (Buffers in /proc/meminfo)
cache      Memory used by the page cache and slabs (Cached and Slab in /proc/meminfo)
buff/cache Sum of buffers and cache

So let us check a more fine grained information in /proc/meminfo …

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 VALUES (NULL, CONCAT('Test data insert from laptop5 on ', @@hostname), CURRENT_TIMESTAMP())
                  3872 Quit

The General Query Log can be enabled dynamically with the following command:

SQL> SET GLOBAL general_log = 1;

and is written either to a file (defined with general_log_file) or a table (mysql.general_log, defined with log_output=TABLE).

But unfortunately the General Query Log logs all Commands from all Connections. So it can grow quickly and you loose overview over what happens… …

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 to act as a community…

In the beginning the sys Schema was hosted on GitHub: mysql/mysql-sys. So we could take the sys Schema from there for MariaDB. But since 3 years ago no more changes appeared on GitHub. It seems like the project was abandoned.

According to a well informed source the project on GitHub is not dead but the developers have just been working on other priorities. And the source announced another release soon (they are working on it at the moment). This statement was made in March 2018 …

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: How do I configure swappiness?

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;
+--------------------------------------+----------+
| prospect_list_id                     | count(*) |
+--------------------------------------+----------+
| 73ae6cca-7b34-c4a3-5500-5d0e2674dbb6 |     5666 |
+--------------------------------------+----------+

And certainly I did not enter the START TRANSACTION; command before. So no ROLLBACK!

Next look at the backup:

$ ll backup/daily/bck_schema_crm_2019-06*
-rw-rw-r-- 1 mysql mysql  7900060 Jun  1 02:13 backup/daily/bck_schema_crm_2019-06-01_02-13-01.sql.gz
-rw-rw-r-- …

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 you can find if your system has Swap Space enabled at all and how much of your Swap Space is used:

# free
              total        used        free      shared  buff/cache   available
Mem:       16106252     3300424      697284      264232    12108544    12011972
Swap:      31250428     1701792    29548636

With the command:

# swapon --show
NAME      TYPE       SIZE USED PRIO
/dev/sdb2 partition 29.8G 1.6G   -1

you can show on which disk drive your Swap Space is physically located. And with the following …

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';
+---------------------+--------------------+---------------------+
| @@version           | plugin_name        | plugin_auth_version |
+---------------------+--------------------+---------------------+
| 10.2.25-MariaDB-log | PERFORMANCE_SCHEMA | 5.6.40              |
+---------------------+--------------------+---------------------+

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

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
ERROR: /opt/mysql/product/5.7.26/bin/mysqldump command failed (rc=253).
mysqldump: [Warning] Using a password on the command line interface can be insecure.
Error: Couldn't read status information for table m_report_builder_cache_157_20190521035354 ()
mysqldump: Couldn't execute 'show create table `m_report_builder_cache_157_20190521035354`': Table 'totara.m_report_builder_cache_157_20190521035354' doesn't exist (1146)

There are various strategies to work around this problem:

• If the table is only …

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 Center for MariaDB and MySQL (focmm) can be downloaded from here. How to install and use focmm is documented in the Ops Center User Guide.

In the inconceivable case that you find a bug in the FromDual Ops Center for MariaDB and MySQL please report it to the FromDual bug tracker or just send us an [email](mailto:contact@fromdual.com?Subject=Bug report for focmm).

Any feedback, statements and testimonials are welcome as well! Please send them to …

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 the release of the new version 0.9 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 Center for MariaDB and MySQL (focmm) can …

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 rows affected (0.010 sec)
Records: 3  Duplicates: 0  Warnings: 0
SQL> DEALLOCATE PREPARE stmt1;
SQL> SELECT * FROM test;
+----+------+---------------------+
| id | data | ts                  |
+----+------+---------------------+
|  1 | Bli  | 2019-04-15 17:26:22 |
|  2 | Bla  | 2019-04-15 17:26:22 |
|  3 | Blub | 2019-04-15 17:26:22 |
+----+------+---------------------+

Prepared Statements and Transactions

SQL> SET SESSION autocommit=Off;
SQL> START TRANSACTION;
SQL> PREPARE stmt2 FROM …

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       |
+-----------+-----------------------------------+------------+
|        14 | thread/innodb/page_cleaner_thread | BACKGROUND |
|        19 | thread/innodb/srv_master_thread   | BACKGROUND |
+-----------+-----------------------------------+------------+

The number of InnoDB Page Cleaner Threads is defined by the server variable innodb_page_cleaners:

SQL> SHOW GLOBAL VARIABLES LIKE 'innodb_page_cleaners';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| …

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 MySQL 5.7 to MariaDB 10.6 on Windows

More and more MySQL users want to switch from MySQL/Percona Server to MariaDB over time or, more rarely, the other way around. This is mostly caused by the change of the default in the Linux Distributions (RHEL/CentOS 7, SLES 12, Debian 8) to MariaDB.
Up to MySQL/MariaDB 5.5 everything was quite easy, both Branches claim to be a drop-in replacement of each other. But after the separation into different forks (MariaDB 10.0 ff. vs. MySQL 5.6 ff.) we expect more and more …

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: view(view_id(PRIM,0e0a2851,2) memb {
2019-03-07 16:16:23 [Note] WSREP: view(view_id(PRIM,0e0a2851,3) memb {
2019-03-07 16:55:56 [Note] WSREP: view(view_id(NON_PRIM,0e0a2851,3) memb {
2019-03-07 16:56:04 [Note] WSREP: view(view_id(PRIM,6d80bb1a,5) memb {
2019-03-07 17:00:28 [Note] WSREP: view(view_id(NON_PRIM,6d80bb1a,5) memb {
2019-03-07 17:01:11 [Note] WSREP: view(view_id(PRIM,24f67954,7) memb {
2019-03-07 17:18:58 [Note] WSREP: view(view_id(NON_PRIM,24f67954,7) memb {
2019-03-07 17:19:31 [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 detached
Process 16697 detached
% time     seconds  usecs/call     calls    errors syscall
------ ----------- ----------- --------- --------- ----------------
 66.85    1.349700         746      1810           io_getevents
 25.91    0.523055        1298       403       197 futex
  4.45    0.089773        1069        84        22 read
  2.58    0.052000       13000         4         3 restart_syscall
  0.19    0.003802        1901         2           select
  0.01    0.000235           3        69         1 …

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

To give you some arguments for arguing with managers we collected some topics to consider before consolidating:

• Bigger Database Instances are more demanding in handling than smaller ones:
  • Backup and Restore time takes longer. Copying files around takes longer, etc.
  • Possibly your logical backup with mysqldump does not restore any longer in a reasonable amount of time (Mean Time to Repair/Recover (MTTR) is not met any more). You have to think about some physical backup methods including MariaDB or MySQL …