News

MariaDB Push Replication

Shinguz - Mon, 2021-01-11 17:29

Table of Contents

How to make MariaDB Pull Replication as secure as possible
MariaDB Push Replication
Pushing data with the FederatedX Storage Engine and Triggers
Traffic mirroring with MariaDB MaxScale or ProxySQL

How to make MariaDB Pull Replication as secure as possible

A normal MariaDB Replication is a Pull Replication. This means that a Slave connects to its Master and gathers or better requests Binary Log information from the Master and applies them in a streaming way.

In some set-ups the Slave is located in a less secure network zone and the Master is located in a more secure network zone. So from the security point of view a permanent connection from the less secure zone to the more secure zone is sometimes not acceptable. We had those discussions already 2 times in the last few months with Chief Security Officers (CSO) of our clients.

Arguing for the MariaDB Pull Replication How can you secure the Master/Slave set-up in this case:

On the Master:
- There has to be a user with the REPLICATION SLAVE privilege and no other privileges. This means that this user is only allowed to request Binary Logs and nothing else.
- This user can additionally be restricted to the IP address of the Slave machine (e.g. 'replication'@'192.168.1.42'). And thus only from this Slave machine this user can access to its Master.
- Additionally firewall rules (iptables) can further restrict access from source IP (Slave) to destination IP (Master) on Port (3306) and Protocol (TCP) between Master and Slave. So nobody can get anywhere else from the non secure zone.
- Only allow SSL connections from the Slave to the Master (require_secure_transport or REQUIRE SSL).
- The use of secure password goes without saying.
On the Slave:
- Access only via SSL to the Master (this can be enforced on the Master). So nobody can listen to the Slave → Master → Slave communication. This can be enforced globally or per account. If this is not sufficient the whole set-up can be secured with VPN (stability?).
If you upgrade Master and Slave on a regular base every 3 months (MariaDB/Oracle CPU) the chances are very small to be hurt by potential security holes.
With an intrusion detection system and an data integrity tools you can further secure your Slave (and Master) system and detect potential manipulations or attacks.
Security features like SElinux (Rocky Linux, RedHat and SuSE) and AppArmor (Debian and Ubuntu) additionally can be activated (or better should not be disabled at all!).
If somebody manages to take over the machine of your Slave in the less secure zone (with O/S user root) you have:
- Done something wrong.
- Lost anyway.

If this arguments are not sufficient to convince your Chief Security Officer (CSO) we have some other ideas how to deal with the problem:

MariaDB Push Replication

The first idea is that we do a Push Replication from the more secure network zone to the less secure network zone instead of a Pull Replication. But MariaDB does not provide this feature natively. So we have to build it ourself. For a Proof of Concept (PoC) we wrote 2 little programs:

One for pushing the Binary Logs from the Master to Slave (binlog_push.php) and
another program for applying the Binary Logs (binlog_apply.php) on the Slave.

These 2 programs are started every minute via crontab. So we get a pulsating Push Replication with a maximum lag of about 3 minutes. With this rhythm after barely 2 years the 6-digit Binary Log numbers will overflow. But Monty stated that the Binary Log numbers then just become 7-digit long.

We were running this PoC with our new mixed test workload (mixed_test.php) and it looks like this way of Push Replication is working correctly (data on Master and Slave were the same). This mechanism behaves similarly like the normal MariaDB Master/Slave Pull Replication: The push program will throw errors if there is a problem and the apply program will also throw an error and stop if it cannot apply the Binary Log events.

Current limitations are:

Only one Slave is supported.
Only full Binary Log Push is supported. Partial Binary Log Push could be implemented.
GTID based Slave set-up is not considered (or better tested) yet.

The push program on the Master is simply started like this (as alternative to crontab):

shell> watch -n 60 ./binlog_push.php

The Slave is set-up as normal and the Binary Log applier program on the Slaves ist started like this:

shell> mariadb-dump --user=root --master-data=1 --single-transaction --all-databases | mariadb --user=root shell> ./binlog_apply.php --start-logfile=binlog.000001 --start-position=678901234 shell> watch -n 60 ./binlog_apply.php

Pushing data with the FederatedX Storage Engine and Triggers

An other possibility to transfer the data from the Master to the Slave is using the FederatedX Storage Engine and Triggers to move the data from the original tables to the FederatedX tables.

This method is a bit less convenient if you want to transfer the data of many or all tables to the Slave. If you want to transfer only a few tables this might work quite well.

Creating the FederatedX tables with federated Server as data source: SQL> SELECT plugin_name, plugin_version, plugin_maturity FROM information_schema.plugins WHERE plugin_type = 'STORAGE ENGINE' AND plugin_name = 'FEDERATED' ; +-------------+----------------+-----------------+ | plugin_name | plugin_version | plugin_maturity | +-------------+----------------+-----------------+ | FEDERATED | 2.1 | Stable | +-------------+----------------+-----------------+ SQL> INSTALL SONAME 'ha_federatedx';
SQL> CREATE SERVER 'mysql-57' FOREIGN DATA WRAPPER 'mysql' OPTIONS ( HOST '127.0.0.1' , PORT 3320 , SOCKET '' , USER 'app' , PASSWORD 'secret' , DATABASE 'test' ); SQL> SELECT * FROM mysql.servers; +-------------+-----------+------+----------+----------+------+--------+---------+-------+ | Server_name | Host | Db | Username | Password | Port | Socket | Wrapper | Owner | +-------------+-----------+------+----------+----------+------+--------+---------+-------+ | mysql-57 | 127.0.0.1 | test | app | secret | 3320 | | mysql | | +-------------+-----------+------+----------+----------+------+--------+---------+-------+ SQL> CREATE TABLE `test_fed` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `data` varchar(128) DEFAULT NULL, `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE="FEDERATED" DEFAULT CHARSET=latin1 CONNECTION='mysql-57' ; ERROR 1434 (HY000): Can't create federated table. Foreign data src error: database: 'test' username: 'app' hostname: '127.0.0.1' SQL> show warnings; +---------+------+-----------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------------------------------------------------------------------------------+ | Error | 1434 | Can't create federated table. Foreign data src error: database: 'test' username: 'app' hostname: '127.0.0.1' | | Warning | 1030 | Got error 1 "Operation not permitted" from storage engine FEDERATED | +---------+------+-----------------------------------------------------------------------------------------------------------------+

There reason for this error was just, that the underlying table on the remote system did not exist! But when we found out what was the problem we realized that a SERVER is possibly not the way we want to do it because the tables on Master and Slave must be named the same. Probably it is better to use direct connections because then we can have different table names on Master an Slave.

It would be a nice feature to have some kind of rewrite for tables in the SERVER.

Creating the FederatedX tables with a direct connection: SQL> CREATE TABLE `test_fed` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `data` varchar(128) DEFAULT NULL, `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE="FEDERATED" DEFAULT CHARSET=latin1 CONNECTION='mysql://app:secret@127.0.0.1:3320/test/test' ; SQL> SELECT * FROM test_fed; +----+----------------------------------+---------------------+ | id | data | ts | +----+----------------------------------+---------------------+ | 1 | Test data insert | 2021-01-06 09:27:22 | | 2 | Test data insert | 2021-01-06 09:27:03 | | 3 | Test data insert | 2021-01-06 09:25:37 | +----+----------------------------------+---------------------+

The FederateX variable federated_pushdown does currently not work properly for me and is buggy (MDEV-2453):

SQL> SHOW VARIABLES LIKE '%federat%'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | federated_pushdown | OFF | +--------------------+-------+ SQL> SET GLOBAL federated_pushdown = on; SQL> SELECT id, data FROM test_fed WHERE id = 1; ERROR 1030 (HY000): Got error 10000 "Unknown error 10000" from storage engine FEDERATED
Creating the Triggers to feed the FederatedX tables

Now we have the connection between Master and Slave but somehow the data must come from the main table (test) to the FerderatedX table (test_fed). This will be achieved by Triggers:

SQL> DELIMITER // CREATE TRIGGER test_insert AFTER INSERT ON test FOR EACH ROW INSERT INTO test_fed VALUES (NEW.id, NEW.data, NEW.ts) ; // CREATE TRIGGER test_update AFTER UPDATE ON test FOR EACH ROW UPDATE test_fed SET id = NEW.id, data = NEW.data, ts = NEW.ts WHERE id = OLD.id ; // CREATE TRIGGER test_delete AFTER DELETE ON test FOR EACH ROW DELETE FROM test_fed WHERE id = OLD.id ; // DELIMITER ; SQL> SELECT CONCAT(trigger_schema, '.', trigger_name) AS 'trigger', event_manipulation AS event, CONCAT(event_object_schema, '.', event_object_table) AS 'table', action_timing AS timing FROM information_schema.triggers; +------------------+--------+-----------+--------+ | trigger | event | table | timing | +------------------+--------+-----------+--------+ | test.test_insert | INSERT | test.test | AFTER | | test.test_update | UPDATE | test.test | AFTER | | test.test_delete | DELETE | test.test | AFTER | +------------------+--------+-----------+--------+

After running our mixed_test.php again the comparison of the data was fine. Number and content was the same.

Literature

Traffic mirroring with MariaDB MaxScale or ProxySQL

The last idea, which comes to my mind, is mirroring the traffic with a Proxy for example MariaDB MaxScale or ProxySQL as described in the article: Traffic mirroring with MariaDB MaxScale.

The disadvantage here is, at least under high pace, that we loose some information (statements) on the tee'd instance. At least for MariaDB MaxScale. It was further suggested to use the Mirror Router instead of the Tee Filter. If this also happens with ProxySQL we cannot say yet. So this method is possibly not ideal for reliably pushing data from a Master to a Slave right now.

There is an open Bug which is currently under investigation: Tee filter loses statements if branch target is slower.

Literature

Taxonomy upgrade extras: mariadb replication security

VSZ behaviour with MariaDB MEMORY tables

Shinguz - Tue, 2021-01-05 17:08

We recently had the situation that a customer complained about the Oom killer terminating the MariaDB database instance from time to time. The MariaDB database configuration was sized quit OK (about 50% of RAM was used for the database) but they did not have swap configured.

When we checked the memory for the specific mysqld process we found that VSZ was about 80 Gibyte (on a 64 Gibyte machine) and the RSS size was about 42 Gibyte. The very high VSZ value in combination with a lacking swap space and Oom killer let the alarm bells ring.

This customer was using a significant amount of (temporary) MEMORY tables (instead of TEMPORARY TABLE ... ENGINE = MEMORY) which are suspect to be the evildoer.

To verify if this could be the reason for the odd behaviour we have to know how MEMORY tables behave related to VSZ from the O/S point of view.

Creation of MEMORY table 1 (12 - 14):

SQL> SET GLOBAL max_heap_table_size = 1024*1024*1024; SQL> SET SESSION max_heap_table_size = 1024*1024*1024; SQL> CREATE TABLE test_m1 LIKE test; SQL> ALTER TABLE test_m1 ENGINE = MEMORY; SQL> INSERT INTO test_m1 SELECT * FROM test; SQL> INSERT INTO test_m1 SELECT NULL, data, NULL FROM test_m1; ... ERROR 1114 (HY000): The table 'test_m1' is full

Creation of MEMORY table 2 (32 - 38):

SQL> CREATE TABLE test_m2 LIKE test_m1; SQL> INSERT INTO test_m2 SELECT NULL, data, NULL FROM test_m1 LIMIT 100000; ... ERROR 1114 (HY000): The table 'test_m2' is full

Creation of MEMORY table 3 (45 - 48):

SQL> CREATE TABLE test_m3 like test_m1; SQL> INSERT INTO test_m3 SELECT NULL, data, NULL FROM test_m1 LIMIT 500000; ... ERROR 1114 (HY000): The table 'test_m3' is full

Truncation of all 3 MEMORY tables (57 - 58):

SQL> TRUNCATE TABLE test_m1; SQL> TRUNCATE TABLE test_m2; SQL> TRUNCATE TABLE test_m3; Drop of all 3 MEMORY tables had no effect (ca. 65): SQL> DROP TABLE test_m1; SQL> DROP TABLE test_m2; SQL> DROP TABLE test_m3;

Restart of the database process releases the memory (71 - 74):

shell> restart ... SUCCESS! Timeout is 60 seconds: . SUCCESS!

Taxonomy upgrade extras: memory table memory oom swap

Categories: FromDual all (en), FromDual TechFeed (en), MySQL Tech-Feed (en)

Traffic mirroring with MariaDB MaxScale

Shinguz - Thu, 2020-12-24 16:27

Recently we had the case that a customer claimed that MariaDB 10.3 Binary Log is using 150% more space on disk than MySQL 5.7 Binary Log. Because I never observed something similar, but to be honest, I did not look to intensively for this situation, we had to do some clarifications.

First we checked the usual variables which could be candidates for such a behaviour:

binlog_format = ROW binlog_row_image = FULL binlog_rows_query_log_events = OFF # MySQL only binlog_annotate_row_events = OFF # MariaDB equivalent log_bin_compress = OFF # MariaDB only

Those were all equal on MariaDB and MySQL. So is was not a trivial case to solve.

The customer did not like the suggestion to just increase the disk space. So we had to dig further...

In the MariaDB Enterprise support ticket we have noticed that the MariaDB support engineer tried to use MariaDB MaxScale to reproduce our problem (without success by the way). So time to try it out ourself because we have some other scenarios where this could be useful as well.

Installation

For our test set-up we were using MariaDB MaxScale version 2.5.6:

shell> maxscale --version MaxScale 2.5.6

The MariaDB MaxScale version seems to be quite important because MariaDB changed a lot in MaxScale in the past and it is not always backwards compatible!

Because MariaDB does not provide binary tar-balls for MaxScale we extracted them ourself from the DEB packages. To make it work we have to set the LD_LIBRARY_PATH and the PATH environment variables:

shell> BASEDIR='/home/mysql/product/maxscale' shell> export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:${BASEDIR}/lib/x86_64-linux-gnu/maxscale shell> export PATH=${PATH}:${BASEDIR}/bin shell> maxscale --help
Configuration

MariaDB MaxScale documentation is not really complete and some actual and good examples are missing a bit. So we had to do some experiments. After these experiments we came to a configuration which worked well for our case (please let me know if there are better ways to do it):

# # /home/mysql/etc/maxscale_load_split.cnf # [Load-Split-Listener] type=listener service=Split-Service protocol=MariaDBClient address=0.0.0.0 port=3392 [Split-Service] type=service router=readconnroute servers=mariadb-105 filters=TeeFilter user=maxscale password=secret [TeeFilter] type=filter module=tee target=mysql-57 match=/.*/ # exclude=/truncate*/ [Monitor] type=monitor module=mariadbmon servers=mariadb-105,mysql-57 user=maxscale password=secret monitor_interval=60000 [mariadb-105] type=server address=192.168.1.108 port=3357 protocol=MariaDBBackend [mysql-57] type=server address=192.168.1.108 port=3320 protocol=MariaDBBackend

Caution: This configuration probably only works for MaxScale 2.5 and newer. For details see: [ 2 ].

Starting MariaDB MaxScale

First we did a check of the configuration file:

shell> maxscale --nodaemon --config=/home/mysql/etc/maxscale_load_split.cnf \ --log=stdout --libdir=${BASEDIR}/lib/x86_64-linux-gnu/maxscale \ --persistdir=/home/mysql/tmp --datadir=/home/mysql/tmp --logdir=/home/mysql/log \ --piddir=/home/mysql/tmp --cachedir=/home/mysql/tmp/cache \ --config-check

Here we hit a bug. Or at least a bug in my opinion. But MariaDB support decided, that it is not a bug: [ 7 ]. Then we started MariaDB MaxScale:

shell> maxscale --nodaemon --config=/home/mysql/etc/maxscale_load_split.cnf \ --log=stdout --libdir=${BASEDIR}/lib/x86_64-linux-gnu/maxscale \ --persistdir=/home/mysql/tmp --datadir=/home/mysql/tmp --logdir=/home/mysql/log \ --piddir=/home/mysql/tmp --cachedir=/home/mysql/tmp/cache
Creating users

Then we found out (it was not very well documented) that we need a user for MaxScale with the following privileges:

SQL> CREATE USER 'maxscale'@'%' IDENTIFIED BY 'secret'; SQL> GRANT SELECT ON mysql.user TO 'maxscale'@'%'; SQL> GRANT SELECT ON mysql.db TO 'maxscale'@'%'; SQL> GRANT SELECT ON mysql.tables_priv TO 'maxscale'@'%'; SQL> GRANT SELECT ON mysql.roles_mapping TO 'maxscale'@'%'; SQL> GRANT SHOW DATABASES ON *.* TO 'maxscale'@'%'; SQL> GRANT SELECT ON mysql.columns_priv TO 'maxscale'@'%'; SQL> GRANT SELECT ON mysql.procs_priv TO 'maxscale'@'%'; SQL> GRANT SELECT ON mysql.proxies_priv TO 'maxscale'@'%';

And we also need an application user for doing the tests:

SQL> CREATE USER 'app'@'%' IDENTIFIED BY 'secret'; SQL> GRANT ALL ON test.* TO 'app'@'%';
Testing and observations

We were running some simple manual tests, than our famous insert test and last our new mixed test. On the first look it looks like everything was working fine. The load was split on both servers (I checked with the General Query Log) and they executed all the queries simultaneously. I knew that the second server was configured in a way it was processing the queries slower than the first one and thus I was wondering what happens.

If one back-end is lagging, in our case it was lagging more than 450 seconds, we found that the disconnect was not done properly. Then we found, that some rows were missing. So it seems like we have some Statement Cache overflow.

On the main instance (mariadb-105):

SQL> SELECT COUNT(*) FROM test.test; +----------+ | count(*) | +----------+ | 221056 | +----------+

The General Query Log looks as follows:

... 3655 Query INSERT INTO test (id, data, ts) values (NULL, "Test data insert", CURRENT_TIMESTAMP()) 3655 Query INSERT INTO test (id, data, ts) values (NULL, "Test data insert", CURRENT_TIMESTAMP()) 3655 Quit

On the tee'd instance (mysql-57):

SQL> SELECT COUNT(*) FROM test.test; +----------+ | count(*) | +----------+ | 190466 | +----------+

The General Query Log looks as follows:

... 2020-12-24T08:19:24.497319Z 4322 Query INSERT INTO test (id, data, ts) values (NULL, "Test data insert", CURRENT_TIMESTAMP()) 2020-12-24T08:19:25.430806Z 4322 Query INSERT INTO test (id, data, ts) values (NULL, "Test data insert", CURRENT_TIMESTAMP())

So we lost about 30k rows on the tee'd instance!!! And this without any error or warning. For this behaviour we filed a bug [ 4 ]. And thus this feature is not usable for production IMHO atm. If somebody has a solution for this, please let me know (documentation did not state anything).

Beside of loss of data we further found, that the data were not 100% equal. Because the statements are routed asynchronously it can be, that some statement are executed at different times:

On the main instance:

| 910 | Test data insert | 2020-12-24 09:23:36 |

On the tee'd instance:

| 910 | Test data insert | 2020-12-24 09:23:37 |

One second difference! We did not investigate further... Other functions like RAND(), NOW(), etc. will behave similarly.

When we throttled the pace from 10 µs sleep between statements to 10 ms between statements we have not seen losses any more (number of rows and checksum was correct). But we cannot know for sure (because no warnings).

What about the original Binary Log problem?

We have not observed the described behaviour with a mixed INSERT, UPDATE and DELETE workload:

On the main instance:

| chef_mariadb-105_binlog.000103 | 3017223 |

On the tee'd instance:

| chef_mysql-57-binlog.000601 | 4633484 |

In contrary: the MySQL Binary Logs were about 50% bigger than the MariaDB Binary Logs. So our customer must have hit a special query pattern where MariaDB behaves worse related to Binary Log size than MySQL.

Literature

1 MXS-1267: Refactor tee filter
2 MXS-2029: Tee directly to backend server
3 MXS-16: Tee filter: statement router loses statements when other router gets enough ahead
4 MXS-3353: tee filter looses statements without any warning
5 ProxySQL Mirroring
6 Tee Filter
7 MXS-3349: maxscale does not consider log location

Taxonomy upgrade extras: mariadb maxscale binary log load balancer

Categories: FromDual all (en), FromDual TechFeed (en), MySQL Tech-Feed (en)

MariaDB Galera Cluster Upgrade Path

Shinguz - Fri, 2020-12-18 10:47

Because we conduct many customers in MariaDB Galera Cluster upgrades and because these customers sometimes have pretty old MariaDB Galera Cluster set-ups I think it is good to have a rough MariaDB Galera Cluster Upgrade Path.

For an Upgrade Path we have to consider a few things:

We face different MariaDB Galera Cluster version (5.5, 10.0, 10.1, 10.2, 10.3, 10.4, 10.5 and soon 10.6).
We face different Galera plug-in versions (v2, v3 and v4). Direct upgrade from v2 to v4 is not possible. Upgrade from latest lower version to higher version is supported.
We should upgrade to the newest version of a Release Series first before upgrading to the next major Release Series.
MariaDB supports skipping of Major Release Series in general but we should consider the Galera plug-in as well.
New versions in a Release Series can have new bugs.
We should make sure that also application side MariaDB connectors are ready for the new version and not only the MariaDB database server. In MariaDB this is a bit less of an issue than with MySQL but it can happen as well (see 10.4.17).
Upgrading with dump/restore (without the mysql schema) should always work also skipping Major Release Series or Galera versions.
Following the Upgrade Path can take 3 to 7 steps. So it makes sense to not postpone upgrades too long into the future!
To reduce impact on service and having better testing possibilities use traditional Master (old) → Slave (new) Replication.
To not get a kick in the teeth you should test your MariaDB Galera Cluster Upgrade carefully (see for example 10.3.27, 10.4.16 and 10.4.17 (MDEV-24229, MDEV-23851 and MDEV-24406)).
Do NOT forget to always run mysql_upgrade/mariadb-upgrade after each and every upgrade!

MariaDB Galera Cluster Upgrade from 5.5 to 10.0

At these times we had the change from Galera 2 to Galera 3. You should upgrade to the newest MariaDB 5.5 version first and then do the upgrade to MariaDB 10.0:

If you are upgrading from the most recent MariaDB Galera Cluster 5.5 release to MariaDB Galera Cluster 10.0, then the versions will be compatible. The latest releases of MariaDB Galera Cluster 5.5 and MariaDB Galera Cluster 10.0 use Galera 3, so they should be compatible. [ 2 ]

If a direct upgrade from 5.5 to 10.3 (or any intermediate major version) is working you should test carefully!

MariaDB Galera Cluster Upgrade from 10.0 to 10.1

MariaDB 10.1 is Galera ready by default. This is not so much of a database issue but more of an installation issue:

Since MariaDB 10.1, the MySQL-wsrep patch has been merged into MariaDB Server. Therefore, in MariaDB 10.1 and above, the functionality of MariaDB Galera Cluster can be obtained by installing the standard MariaDB Server packages and the Galera wsrep provider library package. [ 3 ] MariaDB Galera Cluster Upgrade from 10.1 to 10.2 and 10.2 to 10.3

Here we have no huge changes so we do not expect any significant problems.

MariaDB Galera Cluster Upgrade from 10.3 to 10.4

In MariaDB 10.4 we have the new Galera version 4 plug-in with the new streaming replication functionality. An upgrade is possible from newest MariaDB 10.3 to MariaDB 10.4 as follows:

If you are upgrading from the most recent MariaDB 10.3 release to MariaDB 10.4, then the versions will be compatible. MariaDB 10.3 uses Galera 3, and MariaDB 10.4 uses Galera 4. This means that upgrading to MariaDB 10.4 also upgrades the system to Galera 4. However, Galera 3 and Galera 4 should be compatible for the purposes of a rolling upgrade, as long as you are at least MariaDB 10.4.4 or later. [ 6 ] MariaDB Galera Cluster Upgrade from 10.4 to 10.5

Here again we have no huge changes so we do not expect any significant problems.

Skipping MariaDB Major Release Series

About skipping Major Releases MariaDB documentation states:

You should be able to trivially upgrade from ANY earlier MariaDB version to the latest one (for example MariaDB 5.5.x to MariaDB 10.5.x), usually in a few seconds. [ 9 ] MariaDB Galera Cluster Upgrade Path

And now the visual MariaDB Galera Cluster Upgrade Path:

Literature

Taxonomy upgrade extras: mariadb galera cluster upgrade

Categories: FromDual all (en), FromDual TechFeed (en), MySQL Tech-Feed (en)

How to force InnoDB Buffer Pool flushing

Shinguz - Thu, 2020-12-10 17:38

InnoDB tries to keep pages in Buffer Pool to be fast. If a page is changed by a DML statement (INSERT, UPDATE, DELETE) this change will be done in InnoDB Buffer Pool and not directly on disk. But those changed InnoDB pages residing in InnoDB Buffer Pool must be flushed sooner or later to disk to become persistent. This is done by the InnoDB background writer thread(s) (default 4).

InnoDB flushes the dirty pages with a pace of innodb_io_capactiy (default 200) pages/s. This variable should be set depending on the rate you are dirtying pages and on the capacity of your I/O system. 1 single server HDD has an I/O capacity of about 200 IOPS, a SSD between 1000 and 50000 IOPS.
The rate of dirtying pages depends on the number of DML statements and the locality of the changes in your database blocks (random vs. sequential, AUTO_INCREMENT vs. UUID).

Keeping many dirty pages in InnoDB Buffer Pool is good from performance point of view. But in certain cases you want to have the number of dirty pages small or even close to zero. This case is during Backups done with MEB. LVM snapshots should in theory not have this problem (otherwise MySQL/InnoDB would not be crash-safe) and MariaDB Backup (mariabackup) and Percona Xtrabackup (xtrabackup) can deal with the problem yet. See: Mariabackup - Concurrent DDL and Backup Issues.

With the new optimized (without redo logging) DDL operations in MySQL 5.7 we were running into the following problems with MEB:

ERROR: InnoDB: An optimized(without redo logging) DDL operation has been performed. All modified pages may not have been flushed to the disk yet. MEB will not be able take a consistent backup. Retry the backup operation.

We found out, that systems with a smaller InnoDB Buffer Pool had this problem much less frequent than systems with a bigger InnoDB Buffer Pool. Thus we came to the idea instead of shrinking the InnoDB Buffer Pool to reduce the number of dirty pages in the Buffer Pool.

This is similar to a Checkpoint how it is called in other RDBMS. But I found that the term checkpoint does not always mean the exact same thing in different RBDMS. Further details you can find in Literature.

To force a Checkpoint you can lower the variable innodb_max_dirty_pages_pct variable to 0 before doing a MEB backup and then increasing it again to its original value after the MEB backup. This should reduce the probability of running into the error mentioned above.
Reducing innodb_max_dirty_pages_pct to 0 will possibly lead to an I/O burst on the disk. So lowering the value could be done in smaller decrements.

An other possibility, which is probably much less intrusive, would be to increase innodb_io_capacity to a higher value. But this only works if your I/O system is capable to deal with the higher amount of I/O.

Literature

Taxonomy upgrade extras: innodb buffer pool flushing meb MySQL Enterprise Backup Backup

Categories: FromDual all (en), FromDual TechFeed (en), MySQL Tech-Feed (en)

Upgrading from MariaDB 10.4 to MariaDB 10.5 Galera Cluster

Shinguz - Sat, 2020-11-21 20:58

Because upgrading from MariaDB 10.4 to MariaDB 10.5 (non-clustered) seems not to be a problem [ 1 ] we take the challenge and try to create a receipt based on the MariaDB 10.3 to MariaDB 10.4 Galera Cluster upgrade documentation [ 3 ]:

Before you start

Before you begin with the upgrade you should consider a few things:

Downgrade is officially not supported! [ 4 ] It might work, or not.
So you should have taken a proper an clean backup before you start with the upgrade and you should be sure the restore works as well!
It is recommended to upgrade to the newest MariaDB minor release first [ 5 ] before you upgrade to a new major release. This reduces the risk that you run into already known and fixed bugs.
Take a look at Upgrading from MariaDB 10.4 to MariaDB 10.5 [ 1 ] to see what has changed between the major versions.
New MariaDB Major Release may behave differently than older MariaDB Major Release. Thus you should test the new major release series first before putting it into production!
Ideally, you want to have a large enough Galera Cache to avoid a State Snapshot Transfer (SST) during the rolling upgrade. The Galera Cache size can be configured by setting the Galera variable gcache.size.
For example: wsrep_provider_options = "gcache.size=2G"

Performing a Rolling Upgrade

The following steps can be used to perform a rolling upgrade from MariaDB 10.4 to MariaDB 10.5 when using Galera Cluster. In a rolling upgrade, each node is upgraded individually, so the cluster is always operational. There is no downtime from the application's perspective.

For each node, perform the following steps:

Modify the repository configuration, so the system's package manager installs MariaDB 10.5.
If you use a load balancer such as MaxScale, ProxySQL or HAProxy, make sure to drain the node from the pool so it does not receive any new connections.
Stop the MariaDB node.
Uninstall the old version of MariaDB and the Galera wsrep provider library.
Install the new version of MariaDB and the Galera wsrep provider library.
Make any desired changes to configuration options in option files, such as my.cnf. This includes removing any system variables or options that are no longer supported.
On Linux distributions that use systemd you may need to increase the service start-up timeout as the default timeout of 90 seconds may not be sufficient.
Start the MariaDB node.
Run mariadb-upgrade with the --skip-write-binlog option (I personally think this option is not necessary because it is the default).
mariadb-upgrade does two things:
- 1. Ensures that the system tables in the mysql database are fully compatible with the new version.
- 2. Does a very quick check of all tables and marks them as compatible with the new version of MariaDB.

When this process is done for one node, move onto the next node.

Note: When upgrading the Galera wsrep provider library, sometimes the Galera protocol version can change. The Galera wsrep provider should not start using the new protocol version until all cluster nodes have been upgraded to the new version, so this is not generally an issue during a rolling upgrade.
However, this can cause issues if you restart a non-upgraded node in a cluster where the rest of the nodes have been upgraded already.

License

This page is licensed as follows: CC-BY-SA / GNU FDL/

Literature

Taxonomy upgrade extras: mariadb upgrade cluster galera cluster rolling upgrade 10.4 10.5

Categories: FromDual all (en), FromDual TechFeed (en), MySQL Tech-Feed (en)

Partial Restore of a Table into a MariaDB Galera Cluster

Shinguz - Fri, 2020-11-20 15:08

In my former Blog Post Partial Table or Schema restore from mariabackup full backup we worked out the basics of a partial restore of a table into a MariaDB database instance.

An now we use this know-how to try the same procedure on a Galera Cluster.

The backup is done in the exact same way as described in the mentioned article. We can even use the backup made there.

For the restore we use the following procedure:

Prepare and Restore a table # BACKUPDIR="/home/mysql/bck/qamariadb105/daily" # DATADIR="/home/mysql/database/magal-105-a/data" # SCHEMA="world" # TABLE="City" # mariabackup --prepare --export \ --databases="${SCHEMA}" \ --tables="${TABLE}" \ --datadir=${DATADIR} \ --target-dir=${BACKUPDIR}

But now comes the little difference to a simple MariaDB database instance. The following operations have to be done on ALL nodes of the Galera Cluster:

SQL> ALTER TABLE `world`.`City` DISCARD TABLESPACE;

Restore all the files from the backup:

# scp ${BACKUPDIR}/${SCHEMA}/${TABLE}.ibd mysql@node[1-3]:${DATADIR}/${SCHEMA}/ # scp ${BACKUPDIR}/${SCHEMA}/${TABLE}.cfg mysql@node[1-3]:${DATADIR}/${SCHEMA}/

Then re-import the tablespace again:

SQL> ALTER TABLE `world`.`City` IMPORT TABLESPACE;

And finally clean-up:

# rm -f ${DATADIR}/${SCHEMA}/${TABLE}.cfg
Taxonomy upgrade extras: Backup Restore schema database physical backup mariabackup table restore schema restore partial restore database restore

Categories: FromDual all (en), FromDual TechFeed (en), MySQL Tech-Feed (en)

MariaDB/MySQL Datenbank-Administrator/in gesucht

Shinguz - Fri, 2020-11-20 09:02

Ausschreibungszeitraum: Q4 2020 bis Q2 2021. Später bitte nicht mehr melden.

Einer unserer Kunden sucht eine/n erfahrene/n MariaDB/MySQL Datenbank-Administrator/in. Arbeitspensum: 80 bis 100% in Festanstellung. Arbeitsort: Hauptstadt Bern (Schweiz).
Erfahrung im Betrieb von MariaDB/MySQL Datenbanken im Enterprise-Umfeld sind erforderlich sowie gute MariaDB/MySQL sowie Galera Cluster Kenntnisse notwendig. Einsatzfeld hochkritische, produktive MariaDB Galera Cluster.

Auszug aus der Original-Stellenausschreibung:

Aufgaben:

Aufsetzen, Testen, Betreiben, Warten und Dokumentieren von Datenbanken und Datenbankservern im Entwicklungs-, Abnahme- und Produktivumfeld inkl. Log und Backup
Überwachen und Optimieren der Datenbanken und Datenbankservern hinsichtlich Sicherheit und Performance
Unterhalten der Dokumentationen im DB-Umfeld
Unterstützung des 3rd-Level-Supports für die von uns betriebenen Webanwendungen, Services und Datenbanken
Unterstützen der Softwareentwicklerinnen/ Softwareentwickler bei datenbankbezogenen Fragen und Problemen

Kompetenzen:

Hochschulausbildung im Bereich IT oder grosse Praxiserfahrung in der Datenbankadministration
Fundierte Erfahrungen im Konzipieren, Optimieren, Administrieren und Betreiben (überwachen, absichern, testen) von SQL und NoSQL-Datenbanken und Datenbankclustern im Webumfeld
Freude an neuen Technologien und Bereitschaft, sich in neue Themen einzuarbeiten und nach agilen Vorgehensweisen zu arbeiten
Sehr gute Ausdrucksfähigkeit in Wort und Schrift sowie gute Englischkenntnisse

Wer Interesse hat, soll sich bei mir (Oli Sennhauser) melden, damit ich ihn/sie mit unserem Kunden kurzschliessen kann. FromDual hat kein direktes finanzielles Interesse an dieser Stellenausschreibung!

Taxonomy upgrade extras: mariadb mysql job description job

Categories: FromDual all (en), FromDual TechFeed (en), MySQL Tech-Feed (en)

Partial Table or Schema restore from mariabackup full backup

Shinguz - Wed, 2020-11-11 21:59

For me it was for a long time not clear if a mariabackup full backup can be used to do partial table or schema restores. Now we faced this challenge with a customer. So time to try it out...

This test was made with MariaDB 10.5.5. So it may not work with some older MariaDB releases...

Backup

Because I do not know during the backup if I need a full or a partial restore I always want to do a full mariabackup backup!

The full backup can be done as normal but the prepare should not be done yet during the backup (or I am not sure if the prepare can be done twice, first without --export during the backup and next with --export during the restore). Further I am not sure yet if a backup treated with --export can later be used for a full restore once more. Further research has to be done in this area...

For a partial table or schema restore we need the CREATE TABLE statements as well. So it makes sense to also backup the table structures already during backups. This avoids troubles or cumbersome and time consuming extracting operations during restore.

# BACKUPDIR="/home/mysql/bck/qamariadb105/daily" # DATADIR="/home/mysql/database/qamariadb105/data" # # Clean-up # rm -rf ${BACKUPDIR}/* # mariabackup --backup --user=root \ --datadir=${DATADIR} \ --target-dir=${BACKUPDIR} # # Backup also table structure for partial table/schema restore # mysqldump --user=root --no-data --all-databases > ${BACKUPDIR}/full_structure_dump.sql
Prepare and Restore one Schema # BACKUPDIR="/home/mysql/bck/qamariadb105/daily" # DATADIR="/home/mysql/database/mariadb-105/data" # SCHEMA="world" # mariabackup --prepare --export \ --databases="${SCHEMA}" \ --datadir=${DATADIR} \ --target-dir=${BACKUPDIR}

Additionally you can use the --tables option to only restore some tables: --tables='bla*bla'. The --export option creates the *.cfg files but further does not touch the *.ibd or *.frm files but ibdata?, ib_logfile? and aria_log* files!!! So I guess a backup treated like this cannot be used for a full restore any more... As mentioned above further research has to be done in this area.

-rw-rw---- 1 mysql mysql 551 Nov 11 20:41 world/CountryLanguage.cfg -rw-rw---- 1 mysql mysql 1215 Nov 11 20:41 world/Country.cfg -rw-rw---- 1 mysql mysql 578 Nov 11 20:41 world/City.cfg

From the structure dump we have to extract the CREATE DATABASE and the CREATE TABLE statements.

SQL> DROP SCHEMA world; SQL> CREATE SCHEMA world; SQL> CREATE TABLE ...;

Or more easy:

# mysql --user=root < ${BACKUPDIR}/${SCHEMA}_structure_dump.sql

Then we have to discard all the tablespaces we want to restore:

SQL> ALTER TABLE `world`.`City` DISCARD TABLESPACE; SQL> ALTER TABLE `world`.`Country` DISCARD TABLESPACE; SQL> ALTER TABLE `world`.`CountryLanguage` DISCARD TABLESPACE;

Restore all the files from the backup:

# cp ${BACKUPDIR}/${SCHEMA}/*.ibd ${DATADIR}/${SCHEMA}/ # cp ${BACKUPDIR}/${SCHEMA}/*.cfg ${DATADIR}/${SCHEMA}/

And then re-import the tablespaces:

SQL> ALTER TABLE `world`.`City` IMPORT TABLESPACE; SQL> ALTER TABLE `world`.`Country` IMPORT TABLESPACE; SQL> ALTER TABLE `world`.`CountryLanguage` IMPORT TABLESPACE;

That is it! We have restored one single schema with a physical MariaDB backup...

Possibly the *.cfg files can be cleaned-up yet:

# rm -f ${DATADIR}/${SCHEMA}/*.cfg
Literature

Taxonomy upgrade extras: Backup Restore schema database physical backup mariabackup table restore schema restore partial restore database restore

Categories: FromDual all (en), FromDual TechFeed (en), MySQL Tech-Feed (en)

FromDual Backup and Recovery Manager for MariaDB and MySQL 2.2.2 has been released

Shinguz - Wed, 2020-10-14 14:22

FromDual has the pleasure to announce the release of the new version 2.2.2 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 FromDual Bugtracker or just send us an email.

Any feedback, statements and testimonials are welcome as well! Please send them to feedback@fromdual.com.

Upgrade from 2.x to 2.2.2 shell> cd ${HOME}/product shell> tar xf /download/brman-2.2.2.tar.gz shell> rm -f brman shell> ln -s brman-2.2.2 brman
Changes in FromDual Backup and Recovery Manager 2.2.2

This release is a new minor release. It contains only bug fixes. We have tried to maintain backward-compatibility with the 1.2, 2.0 and 2.1 release series. But you should test the new release seriously!

You can verify your current FromDual Backup Manager version with the following command:

shell> fromdual_bman --version shell> bman --version shell> rman --version
General

Implode command made compatible to new PHP versions.
Function readMyCnf restructured to not write to STDOUT any more.
Max connection reached problem solved.
myEnv library and constants synced/updated.

FromDual Backup Manager

Missing return code added.
RELOAD privilege is checked in doSchemaBackup and BinlogBackup.
Reset return code to not spoil final return code in doSchemaBackup.
WARN reduced severity level to INFO to not spoil STDERR in doSchemaBackup.
Error caught in case when binary logs were deleted but not available in doCleanup.
doBinlogBackup separated into own file.
Do not print wrong recommendation in case of too many connections for target.
Bman error message improved.
Code clean-up in checkForGeneralTablespaces function.
Bman falsely warns on MySQL 8.0 for general tablespace (new InnoDB system tablespace). This warning is suppressed now.
Schema overview output was ugly with long schema names. Now extended to 24 characters.
Bug caught better for clean-up backups in case the backup was already deleted by somebody else.
Error handling improved for binlog backup on remote machine, code cleaned-up.
Separated schema backup into own file.
Fixed error messages and added remote binary log backup tags for future feature request.
Function checkForGeneralTablespaces cleaned-up.
Lock directory moved to /var/lock and alternative location for some Cloud set-ups.
Physical backup for MariaDB 10.5 fixed.

FromDual Recovery Manager

Password is not shown any more in the log for restore.
Physical restore for MySQL 5.7 with Xtrabackup should work now.
Exception for sync_frm for MySQL 8.0 added.

FromDual brman Catalog

Bugs around catalog entries fixed.

FromDual brman Data masking / data obfuscating

Data masking / data obfuscating POC work has been started to work exactly the same way as mariadb-dump / mysqldump.

Testing

PHP Lint test added.
Tests improved.
New MariaDB 10.5 version added and test for stopped instance added.
Tests for MySQL 8.0 fixed.

Subscriptions for commercial use of FromDual Backup and Recovery Manager you can get from from us.

Taxonomy upgrade extras: Backup Restore Recovery pitr brman release bman rman FromDual Backup and Recovery Manager

Categories: FromDual all (en), FromDual TechFeed (en), MySQL Tech-Feed (en)

Creating synthetic data sets for tuning SQL queries

Shinguz - Fri, 2020-10-02 16:50

When it comes to SQL Query tuning with customers we often get the slow running SQL query and possibly, in good cases, also the table structure. But very often, for various reasons, we do not get the data.

SQL query tuning on an empty table or a table with only little data is not really fun because either the results of the optimizer have nothing to do with reality or the response times do not really show if your change has improved anything. For example if your query response time before the change was 2 ms and after 1 ms this can be either the consequence of your improvement but more probable a hiccup of your system.

So what to do to get valid results from your SQL query optimizer during SQL query tuning?

The best case is you get real data from the customer in size and content.
The second best case is if you get real data from the customer in content. So you can analyze this content and synthetically pump it up.
The worst case is if you get no data at all from your customer. In this case you have to create your own data set in size (this is easy) and in content. And this is a bit more tricky.

So let us have a look at how we get to this synthetic data.

Creating data volume by pumping up the table

We get from the customer a slow query for a data cleansing job on the call detail record (CDR) table. This table is used in telecom solutions like VoIP (Asterix, OpenSIPS), PBX and so on.

SELECT COUNT(*) FROM cdrs WHERE start >= end;

And fortunately we also get the CDR table:

CREATE TABLE `cdrs` ( `uniqueid` varchar(40) NOT NULL, `callid` varchar(40) NOT NULL, `asteriskid` varchar(20) NOT NULL DEFAULT '', `machine` int(11) NOT NULL DEFAULT 0, `status` varchar(15) NOT NULL DEFAULT '', `start` TIMESTAMP(6) NOT NULL, `end` TIMESTAMP(6) NOT NULL, `scustomer` int(11) NOT NULL DEFAULT 0, `stype` varchar(30) NOT NULL DEFAULT '', `snumber` varchar(255) NOT NULL DEFAULT '', `dcustomer` int(11) NOT NULL DEFAULT 0, `dtype` varchar(30) NOT NULL DEFAULT '', `dnumber` varchar(255) NOT NULL DEFAULT '', PRIMARY KEY (`uniqueid`), KEY `server` (`callid`), KEY `start` (`start`), KEY `scustomer` (`scustomer`,`start`), KEY `dcustomer` (`dcustomer`,`start`), KEY `asteriskid` (`asteriskid`) );

So how can we pump up this table to get a decent volume? For pumping up the table we use the concept of the Rice/Wheat and chessboard problem:

We first insert one row and then pump it up by adding rows from itself. This gives us an exponential growth and after a few statement we have enough data (possibly time becomes an issue sooner or later):

INSERT INTO cdrs SELECT UUID(), MD5(RAND()), '', 0, '', FROM_UNIXTIME(ROUND(RAND() * UNIX_TIMESTAMP(), 6)), 0, 0, '', '', 0, '', '' ; Query OK, 1 row affected (0.001 sec) Records: 1 Duplicates: 0 Warnings: 0 INSERT INTO cdrs SELECT UUID(), MD5(RAND()), '', 0, '', FROM_UNIXTIME(ROUND(RAND() * UNIX_TIMESTAMP(), 6)), 0, 0, '', '', 0, '', '' FROM cdrs ; -- Repeat this second query about 20 times to get 1 Mio rows. Query OK, 1 row affected (0.001 sec) Records: 1 Duplicates: 0 Warnings: 0 Query OK, 2 rows affected (0.001 sec) Records: 2 Duplicates: 0 Warnings: 0 Query OK, 4 rows affected (0.000 sec) Records: 4 Duplicates: 0 Warnings: 0 Query OK, 8 rows affected (0.000 sec) Records: 8 Duplicates: 0 Warnings: 0 Query OK, 16 rows affected (0.001 sec) Records: 16 Duplicates: 0 Warnings: 0 Query OK, 32 rows affected (0.002 sec) Records: 32 Duplicates: 0 Warnings: 0 Query OK, 64 rows affected (0.002 sec) Records: 64 Duplicates: 0 Warnings: 0 Query OK, 128 rows affected (0.094 sec) Records: 128 Duplicates: 0 Warnings: 0 Query OK, 256 rows affected (1.406 sec) Records: 256 Duplicates: 0 Warnings: 0 Query OK, 512 rows affected (2.747 sec) Records: 512 Duplicates: 0 Warnings: 0 Query OK, 1024 rows affected (4.888 sec) Records: 1024 Duplicates: 0 Warnings: 0

What happened here???

Query OK, 2048 rows affected (0.178 sec) Records: 2048 Duplicates: 0 Warnings: 0 Query OK, 4096 rows affected (0.259 sec) Records: 4096 Duplicates: 0 Warnings: 0 Query OK, 8192 rows affected (1.879 sec) Records: 8192 Duplicates: 0 Warnings: 0 Query OK, 16384 rows affected (4.149 sec) Records: 16384 Duplicates: 0 Warnings: 0 Query OK, 32768 rows affected (3.256 sec) Records: 32768 Duplicates: 0 Warnings: 0 Query OK, 65536 rows affected (7.209 sec) Records: 65536 Duplicates: 0 Warnings: 0 Query OK, 131072 rows affected (13.555 sec) Records: 131072 Duplicates: 0 Warnings: 0

Buffer Pool seems to be full! More RAM helps more...

Query OK, 262144 rows affected (6 min 17.659 sec) Records: 262144 Duplicates: 0 Warnings: 0

Increased Buffer Pool (online!) 6 times and waited for dirty page flushing.

Query OK, 524288 rows affected (1 min 14.629 sec) Records: 524288 Duplicates: 0 Warnings: 0

It definitely helped! More RAM helps more!!!

How to get more or less useful or realistic data DescriptionFunctionExampleUnique ID:SELECT UUID();09e16608-017f-11eb-9cc7-a8a15920b138Random Float from 0 TO 99:SELECT RAND() * 100;82.15320322863124Random Integer from 10 to 19:SELECT 10 + FLOOR(RAND() * 10);10Random Float for currencies:SELECT ROUND(RAND() * 1000, 2);628.07Random String:SELECT UUID(), MD5(RAND()), CRC32(RAND());232fccee-017f-11eb-9cc7-a8a15920b138 0e468db120211529f5fc2940994024a8 263783538Random Timestamp ≥ 1970-01-01 00:00:00 UTC:SELECT FROM_UNIXTIME(ROUND(RAND() * UNIX_TIMESTAMP(), 6));1992-06-30 11:04:04.784335Random Timestamp ≥ 2020-01-01 and < 2020-12-31:SELECT FROM_UNIXTIME(UNIX_TIMESTAMP('2020-01-01') + (365 * 24 * 3600 * RAND()));2020-08-06 04:48:53.342219Some kind of email address:SELECT CONCAT(CRC32(RAND()), '@', MD5(RAND()), '.com');1619088853@6b20a5dad4522feee5efbfd3ebb17d71.comTime range of 21 days from now:SELECT FROM_UNIXTIME(@begin := UNIX_TIMESTAMP()), FROM_UNIXTIME(@begin + (86400 * 21));2020-10-02 11:14:48 2020-10-23 11:14:48Street name:SELECT CONCAT(CRC32(RAND()), 'street ', (1 + FLOOR(RAND() * 100)));3416042219street 14

and there are for sure many more possibilities...

Now back to the query: With no rows the Query Execution Plan with EXPLAIN looks as follows:

and the query run time is this:

SQL> SELECT COUNT(*) FROM cdrs WHERE start >= end; +----------+ | COUNT(*) | +----------+ | 0 | +----------+ 1 row in set (0.004 sec)

If we pump up the table the query execution plan with EXPLAIN looks like this:

and the query run time is this:

SQL> SELECT COUNT(*) FROM cdrs WHERE start >= end; +----------+ | COUNT(*) | +----------+ | 1048576 | +----------+ 1 row in set (0.230 sec)

So we have a good starting point for testing tuning measures on this SQL query...

But caution: There is still a logical error in the data above... Did you find it?

If and how this query can be speed up is an other story...

Skew data distribution

What we got so far is a completely random data distribution or at least random in a specific range. This is very often far from reality. So we have to influence or manipulate this random data distribution a bit more into the direction that it reflects our reality.

There are 2 extreme cases: We are only searching for one unique value or all values are equal. In reality we are somewhere in between.

If you where using a UUID or hash function to create the data they should be pretty unique. So this extreme case is covered:

UPDATE cdrs SET start = '2020-04-29 12:00:00.000000', end = '2020-04-29 12:13:13.999999' WHERE uniqueid = 'd00c7166-01a4-11eb-9cc7-a8a15920b138';

Or you can specifically UPDATE ONE row to your needs. This other extreme case also can be solved by a simple UPDATE statement:

UPDATE cdrs SET machine = 42;

If you want to set only every nth row to a specific value the modulo operation might help:

SET @nth = 7; UPDATE cdrs SET machine = 42 WHERE id % @nth; UPDATE cdrs SET machine = IF(HEX(SUBSTR(UNIQUEID, 8, 1)) % @nth, 42, machine);

And finaly if you need monotonic increasing numbers this is a possibility to do it:

SET @row_number = 0; UPDATE cdrs SET machine = (@row_number := @row_number + 1);
Taxonomy upgrade extras: performance tuning query Optimizer sql explain optimizing query tuning Performance Tuning

Categories: FromDual all (en), FromDual TechFeed (en), MySQL Tech-Feed (en)

MyISAM locking and who is the evil?

Shinguz - Wed, 2020-09-23 09:58

Yes, I know, MyISAM is deprecated and unofficially discontinued by the vendors. But we still have from time to time customers using MyISAM and even evangelize for MyISAM...

And to be honest in some cases MyISAM has even advantages (beside some huge disadvantages) over other Storage Engines (simple file copy, footprint, single-query latency, ...). But most of our customers are not aware of these advantages and are using MyISAM just because they did it since ever...

One of the biggest problems we see at customers is the MyISAM table lock behaviour. They claim things like the database stalls, crashes or stocks beside other non-qualified expressions. Which is typically not the case but the database just runs out of connections because they reach the max_connections fuse. In fact what happens is that one long running writer connection blocks an important and frequently used (MyISAM) table and other writer and reader connections have to wait (Waiting for table level lock) until the writer finishes its work. If you are lucky the system relaxes again afterwards. If not, the database rejects new connections because in the meanwhile other connections have filled up the allowed number of connections up to max_connections.

Instead of finding and solving the problem, customers typically just increase max_connections until it becomes so cumbersome they cannot live any more with it. And then they show up at FromDual consulting services.

Now, how can we find that this scenario happened? The first thing you should do when this situation happens is to gather immediately the output of the command SHOW FULL PROCESSLIST and store it away for later analysis by your preferred spreadsheet tool or editor (please not a screenshot but just copy plain characters from your CLI!).

If you are a bit more prepared for the situation you can also use:

SELECT thread_id, processlist_id, processlist_user, processlist_host, processlist_db, processlist_time, processlist_state, processlist_info FROM performance_schema.threads WHERE PROCESSLIST_COMMAND != 'Sleep' AND TYPE = 'FOREGROUND' ORDER BY PROCESSLIST_TIME DESC ;

An this is how it looks like if you have a MyISAM table level locking situation:

INSERT INTO ... SELECT * FROM ... +-----------+----------------+------------------+------------------+----------------+------------------+------------------------------+--------------------------------------------------------------------------------------------------------------------------+ | THREAD_ID | PROCESSLIST_ID | PROCESSLIST_USER | PROCESSLIST_HOST | PROCESSLIST_DB | PROCESSLIST_TIME | PROCESSLIST_STATE | PROCESSLIST_INFO | +-----------+----------------+------------------+------------------+----------------+------------------+------------------------------+--------------------------------------------------------------------------------------------------------------------------+ | 39557 | 38968 | root | localhost | test | 12 | Sending data | INSERT INTO test SELECT * FROM vol | | 49102 | 48489 | root | localhost | test | 11 | Waiting for table level lock | INSERT INTO test (id, data, ts) VALUES (NULL, CONCAT('Test data insert from boss on ', @@hostname), CURRENT_TIMESTAMP()) | +-----------+----------------+------------------+------------------+----------------+------------------+------------------------------+--------------------------------------------------------------------------------------------------------------------------+

or

+-----------+----------------+------------------+------------------+----------------+------------------+------------------------------+--------------------------------------------------------------------------------------------------------------------------+ | THREAD_ID | PROCESSLIST_ID | PROCESSLIST_USER | PROCESSLIST_HOST | PROCESSLIST_DB | PROCESSLIST_TIME | PROCESSLIST_STATE | PROCESSLIST_INFO | +-----------+----------------+------------------+------------------+----------------+------------------+------------------------------+--------------------------------------------------------------------------------------------------------------------------+ | 39557 | 38968 | root | localhost | test | 21 | Writing to binlog | INSERT INTO test SELECT * FROM vol | | 49102 | 48489 | root | localhost | test | 20 | Waiting for table level lock | INSERT INTO test (id, data, ts) VALUES (NULL, CONCAT('Test data insert from boss on ', @@hostname), CURRENT_TIMESTAMP()) | +-----------+----------------+------------------+------------------+----------------+------------------+------------------------------+--------------------------------------------------------------------------------------------------------------------------+
UPDATE ... SET ... +-----------+----------------+------------------+------------------+----------------+------------------+------------------------------+--------------------------------------------------------------------------------------------------------------------------+ | THREAD_ID | PROCESSLIST_ID | PROCESSLIST_USER | PROCESSLIST_HOST | PROCESSLIST_DB | PROCESSLIST_TIME | PROCESSLIST_STATE | PROCESSLIST_INFO | +-----------+----------------+------------------+------------------+----------------+------------------+------------------------------+--------------------------------------------------------------------------------------------------------------------------+ | 39557 | 38968 | root | localhost | test | 47 | Updating | UPDATE test SET data = 'Blub' WHERE id > 1000 | | 117892 | 117279 | root | localhost | test | 47 | Waiting for table level lock | INSERT INTO test (id, data, ts) VALUES (NULL, CONCAT('Test data insert from boss on ', @@hostname), CURRENT_TIMESTAMP()) | +-----------+----------------+------------------+------------------+----------------+------------------+------------------------------+--------------------------------------------------------------------------------------------------------------------------+
DELETE FROM ... +-----------+----------------+------------------+------------------+----------------+------------------+------------------------------+--------------------------------------------------------------------------------------------------------------------------+ | THREAD_ID | PROCESSLIST_ID | PROCESSLIST_USER | PROCESSLIST_HOST | PROCESSLIST_DB | PROCESSLIST_TIME | PROCESSLIST_STATE | PROCESSLIST_INFO | +-----------+----------------+------------------+------------------+----------------+------------------+------------------------------+--------------------------------------------------------------------------------------------------------------------------+ | 39557 | 38968 | root | localhost | test | 7 | Updating | DELETE FROM test WHERE id > 10 | | 153272 | 152659 | root | localhost | test | 7 | Waiting for table level lock | INSERT INTO test (id, data, ts) VALUES (NULL, CONCAT('Test data insert from boss on ', @@hostname), CURRENT_TIMESTAMP()) | +-----------+----------------+------------------+------------------+----------------+------------------+------------------------------+--------------------------------------------------------------------------------------------------------------------------+
SELECT ... FROM ... +-----------+----------------+------------------+------------------+----------------+------------------+------------------------------+--------------------------------------------------------------------------------------------------------------------------+ | THREAD_ID | PROCESSLIST_ID | PROCESSLIST_USER | PROCESSLIST_HOST | PROCESSLIST_DB | PROCESSLIST_TIME | PROCESSLIST_STATE | PROCESSLIST_INFO | +-----------+----------------+------------------+------------------+----------------+------------------+------------------------------+--------------------------------------------------------------------------------------------------------------------------+ | 39557 | 38968 | root | localhost | test | 2 | Sending data | SELECT * FROM test | | 160014 | 159401 | root | localhost | test | 2 | Waiting for table level lock | INSERT INTO test (id, data, ts) VALUES (NULL, CONCAT('Test data insert from boss on ', @@hostname), CURRENT_TIMESTAMP()) | +-----------+----------------+------------------+------------------+----------------+------------------+------------------------------+--------------------------------------------------------------------------------------------------------------------------+

This means for searching the locker you have to find all connections which are not in processlist command Sleep and which are not in processlist state Waiting for table level lock sort the remaining by processlist time and the one with the longest processlist time is probably the evildoer.

In some cases where you have a Master/Master set-up (or if you have the problem on the slave) data changes can also be induced by the (other) Master. In this situation it looks as follows:

and then you can check the according PERFORMANCE_SCHEMA views:

SQL> SELECT @uptime := variable_value FROM information_schema.global_status WHERE variable_name = 'Uptime'; SQL> SELECT @start := DATE_SUB(NOW(), INTERVAL @uptime SECOND) AS start;

General statistics:

SQL> SELECT * FROM performance_schema.table_lock_waits_summary_by_table; +-------------+---------------+-----------------------+------------+-----------------+----------------+----------------+-----------------+------------+----------------+----------------+----------------+----------------+-------------+-----------------+-----------------+-----------------+-----------------+-------------------+-----------------------+-----------------------+-----------------------+-----------------------+------------------------------+----------------------------------+----------------------------------+----------------------------------+----------------------------------+--------------------------+------------------------------+------------------------------+------------------------------+------------------------------+----------------------+--------------------------+--------------------------+--------------------------+--------------------------+---------------------+-------------------------+-------------------------+-------------------------+-------------------------+-------------------------+-----------------------------+-----------------------------+-----------------------------+-----------------------------+-------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+---------------------+-------------------------+-------------------------+-------------------------+-------------------------+--------------------------+------------------------------+------------------------------+------------------------------+------------------------------+--------------------+------------------------+------------------------+------------------------+------------------------+----------------------+--------------------------+--------------------------+--------------------------+--------------------------+ | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | COUNT_STAR | SUM_TIMER_WAIT | MIN_TIMER_WAIT | AVG_TIMER_WAIT | MAX_TIMER_WAIT | COUNT_READ | SUM_TIMER_READ | MIN_TIMER_READ | AVG_TIMER_READ | MAX_TIMER_READ | COUNT_WRITE | SUM_TIMER_WRITE | MIN_TIMER_WRITE | AVG_TIMER_WRITE | MAX_TIMER_WRITE | COUNT_READ_NORMAL | SUM_TIMER_READ_NORMAL | MIN_TIMER_READ_NORMAL | AVG_TIMER_READ_NORMAL | MAX_TIMER_READ_NORMAL | COUNT_READ_WITH_SHARED_LOCKS | SUM_TIMER_READ_WITH_SHARED_LOCKS | MIN_TIMER_READ_WITH_SHARED_LOCKS | AVG_TIMER_READ_WITH_SHARED_LOCKS | MAX_TIMER_READ_WITH_SHARED_LOCKS | COUNT_READ_HIGH_PRIORITY | SUM_TIMER_READ_HIGH_PRIORITY | MIN_TIMER_READ_HIGH_PRIORITY | AVG_TIMER_READ_HIGH_PRIORITY | MAX_TIMER_READ_HIGH_PRIORITY | COUNT_READ_NO_INSERT | SUM_TIMER_READ_NO_INSERT | MIN_TIMER_READ_NO_INSERT | AVG_TIMER_READ_NO_INSERT | MAX_TIMER_READ_NO_INSERT | COUNT_READ_EXTERNAL | SUM_TIMER_READ_EXTERNAL | MIN_TIMER_READ_EXTERNAL | AVG_TIMER_READ_EXTERNAL | MAX_TIMER_READ_EXTERNAL | COUNT_WRITE_ALLOW_WRITE | SUM_TIMER_WRITE_ALLOW_WRITE | MIN_TIMER_WRITE_ALLOW_WRITE | AVG_TIMER_WRITE_ALLOW_WRITE | MAX_TIMER_WRITE_ALLOW_WRITE | COUNT_WRITE_CONCURRENT_INSERT | SUM_TIMER_WRITE_CONCURRENT_INSERT | MIN_TIMER_WRITE_CONCURRENT_INSERT | AVG_TIMER_WRITE_CONCURRENT_INSERT | MAX_TIMER_WRITE_CONCURRENT_INSERT | COUNT_WRITE_DELAYED | SUM_TIMER_WRITE_DELAYED | MIN_TIMER_WRITE_DELAYED | AVG_TIMER_WRITE_DELAYED | MAX_TIMER_WRITE_DELAYED | COUNT_WRITE_LOW_PRIORITY | SUM_TIMER_WRITE_LOW_PRIORITY | MIN_TIMER_WRITE_LOW_PRIORITY | AVG_TIMER_WRITE_LOW_PRIORITY | MAX_TIMER_WRITE_LOW_PRIORITY | COUNT_WRITE_NORMAL | SUM_TIMER_WRITE_NORMAL | MIN_TIMER_WRITE_NORMAL | AVG_TIMER_WRITE_NORMAL | MAX_TIMER_WRITE_NORMAL | COUNT_WRITE_EXTERNAL | SUM_TIMER_WRITE_EXTERNAL | MIN_TIMER_WRITE_EXTERNAL | AVG_TIMER_WRITE_EXTERNAL | MAX_TIMER_WRITE_EXTERNAL | +-------------+---------------+-----------------------+------------+-----------------+----------------+----------------+-----------------+------------+----------------+----------------+----------------+----------------+-------------+-----------------+-----------------+-----------------+-----------------+-------------------+-----------------------+-----------------------+-----------------------+-----------------------+------------------------------+----------------------------------+----------------------------------+----------------------------------+----------------------------------+--------------------------+------------------------------+------------------------------+------------------------------+------------------------------+----------------------+--------------------------+--------------------------+--------------------------+--------------------------+---------------------+-------------------------+-------------------------+-------------------------+-------------------------+-------------------------+-----------------------------+-----------------------------+-----------------------------+-----------------------------+-------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+---------------------+-------------------------+-------------------------+-------------------------+-------------------------+--------------------------+------------------------------+------------------------------+------------------------------+------------------------------+--------------------+------------------------+------------------------+------------------------+------------------------+----------------------+--------------------------+--------------------------+--------------------------+--------------------------+ | TABLE | test | test | 994184 | 410880205008924 | 126840 | 413283678 | 101480121005292 | 6 | 7095792 | 300792 | 1182330 | 2797728 | 994178 | 410880197913132 | 126840 | 413285943 | 101480121005292 | 3 | 2583912 | 300792 | 861153 | 1359906 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 3 | 4511880 | 608832 | 1503960 | 2797728 | 0 | 0 | 0 | 0 | 0 | 497080 | 410589058214256 | 126840 | 826001559 | 101480121005292 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 9 | 2890140 | 162174 | 320724 | 953112 | 497089 | 291136808736 | 287202 | 585276 | 69665964 | | TABLE | test | vol | 110 | 113655888 | 94224 | 1032840 | 3911202 | 64 | 30244998 | 94224 | 472479 | 3911202 | 46 | 83410890 | 318912 | 1812906 | 3246198 | 32 | 11410164 | 94224 | 356511 | 558096 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 32 | 18834834 | 96036 | 588447 | 3911202 | 0 | 0 | 0 | 0 | 0 | 23 | 22661778 | 318912 | 985275 | 1338162 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 23 | 60749112 | 1040088 | 2640990 | 3246198 | +-------------+---------------+-----------------------+------------+-----------------+----------------+----------------+-----------------+------------+----------------+----------------+----------------+----------------+-------------+-----------------+-----------------+-----------------+-----------------+-------------------+-----------------------+-----------------------+-----------------------+-----------------------+------------------------------+----------------------------------+----------------------------------+----------------------------------+----------------------------------+--------------------------+------------------------------+------------------------------+------------------------------+------------------------------+----------------------+--------------------------+--------------------------+--------------------------+--------------------------+---------------------+-------------------------+-------------------------+-------------------------+-------------------------+-------------------------+-----------------------------+-----------------------------+-----------------------------+-----------------------------+-------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+---------------------+-------------------------+-------------------------+-------------------------+-------------------------+--------------------------+------------------------------+------------------------------+------------------------------+------------------------------+--------------------+------------------------+------------------------+------------------------+------------------------+----------------------+--------------------------+--------------------------+--------------------------+--------------------------+

Locks currently in use (column EXTERNAL_LOCK?):

Threads currently locking:

But I still have not found a direct view to prove who is the locker of a specific locked thread/connection. So some work is still to do...

Taxonomy upgrade extras: myisam lock locking

Categories: FromDual all (en), FromDual TechFeed (en), MySQL Tech-Feed (en)

MariaDB and MySQL package holding or locking

Shinguz - Fri, 2020-08-07 16:02

Repositories from your favourite Linux distribution and from your favourite database software vendor get regular package updates. If you do periodic system upgrades (for example every 2 weeks as we do) you get the newest packages of a release series.
Unfortunately recently the software vendors started not only to fix bugs in new releases but also to introduce new features. And when you fix bugs or introduce new stuff new bugs might appear.
To avoid being hit unexpectedly by new bugs you do not want to upgrade untested software. To achieve this you want to keep/not upgrade some important packages. For example the MariaDB or MySQL server package.
This package pinning is called package version lock on CentOS and Redhat and package holding on Debian and Ubuntu.

How you do this I have summarized below:

Debian / Ubuntu

shell> dpkg -l | grep mariadb ii libmariadb3:amd64 3.0.3-1build1 amd64 MariaDB Connector/C ii mariadb-client-10.4 1:10.4.11+maria~bionic amd64 MariaDB database client binaries ii mariadb-client-core-10.4 1:10.4.11+maria~bionic amd64 MariaDB database core client binaries ii mariadb-common 1:10.4.11+maria~bionic all MariaDB database common files (e.g. /etc/mysql/conf.d/mariadb.cnf) ii mariadb-server-10.4 1:10.4.11+maria~bionic amd64 MariaDB database server binaries ii mariadb-server-core-10.4 1:10.4.11+maria~bionic amd64 MariaDB database core server files shell> apt-mark hold galera-4 libmariadb3 mariadb-client-10.4 mariadb-client-core-10.4 mariadb-common mariadb-server-10.4 mariadb-server-core-10.4 galera-4 set on hold. libmariadb3 set on hold. mariadb-client-10.4 set on hold. mariadb-client-core-10.4 set on hold. mariadb-common set on hold. mariadb-server-10.4 set on hold. mariadb-server-core-10.4 set on hold. shell> apt-mark showhold galera-4 libmariadb3 mariadb-client-10.4 mariadb-client-core-10.4 mariadb-common mariadb-server-10.4 mariadb-server-core-10.4 shell> dpkg -l | grep -e mariadb -e galera hi galera-4 26.4.2-bionic amd64 Replication framework for transactional applications hi libmariadb3:amd64 3.0.3-1build1 amd64 MariaDB Connector/C hi mariadb-client-10.4 1:10.4.11+maria~bionic amd64 MariaDB database client binaries hi mariadb-client-core-10.4 1:10.4.11+maria~bionic amd64 MariaDB database core client binaries hi mariadb-common 1:10.4.11+maria~bionic all MariaDB database common files (e.g. /etc/mysql/conf.d/mariadb.cnf) hi mariadb-server-10.4 1:10.4.11+maria~bionic amd64 MariaDB database server binaries hi mariadb-server-core-10.4 1:10.4.11+maria~bionic amd64 MariaDB database core server files shell> apt-get upgrade Reading package lists... Done Building dependency tree Reading state information... Done Calculating upgrade... Done The following packages were automatically installed and are no longer required: ... Use 'apt autoremove' to remove them. The following packages have been kept back: galera-4 libmariadb3 mariadb-client-10.4 mariadb-client-core-10.4 mariadb-common mariadb-server-10.4 mariadb-server-core-10.4 netplan.io The following packages will be upgraded: ... 67 to upgrade, 0 to newly install, 0 to remove and 11 not to upgrade. Need to get 107 MB of archives. After this operation, 3,876 kB of additional disk space will be used. Do you want to continue? [Y/n]

With the command apt-mark unhold you can release pinned packages again.

CentOS / Redhat

To restrict a package to a fixed version number with yum you have to first install the package yum-plugin-versionlock [ man yum-versionlock ]:

shell> yum install yum-plugin-versionlock

To add a version lock for the MariaDB packages:

shell> yum versionlock add MariaDB-server MariaDB-client MariaDB-common MariaDB-compat galera-4 Adding versionlock on: 0:MariaDB-server-10.4.10-1.el7.centos Adding versionlock on: 0:MariaDB-client-10.4.10-1.el7.centos Adding versionlock on: 0:MariaDB-common-10.4.10-1.el7.centos Adding versionlock on: 0:MariaDB-compat-10.4.10-1.el7.centos Adding versionlock on: 0:galera-4-26.4.3-1.rhel7.el7.centos versionlock added: 5

To list any available updates that are currently blocked by version lock you can run:

shell> yum versionlock status 0:MariaDB-common-10.4.13-1.el7.centos.* 0:MariaDB-compat-10.4.13-1.el7.centos.* 0:MariaDB-client-10.4.13-1.el7.centos.* 0:MariaDB-server-10.4.13-1.el7.centos.* 0:galera-4-26.4.4-1.rhel7.el7.centos.* versionlock status done

And to list the current version lock entries:

shell> yum versionlock list 0:MariaDB-server-10.4.10-1.el7.centos.* 0:MariaDB-client-10.4.10-1.el7.centos.* 0:MariaDB-common-10.4.10-1.el7.centos.* 0:MariaDB-compat-10.4.10-1.el7.centos.* 0:galera-4-26.4.3-1.rhel7.el7.centos.* versionlock list done

With:

shell> yum versionlock delete MariaDB-server

you can remove any matching version lock entries again.

And to clear the whole list of version lock entries use:

yum versionlock clear

If you will do the next yum update you will not see the MariaDB packages in the list any more:

shell> yum update ... Dependencies Resolved ======================================================================================================================================================== Package Arch Version Repository Size ======================================================================================================================================================== Installing: kernel x86_64 3.10.0-1127.18.2.el7 updates 50 M Updating: bind-export-libs x86_64 32:9.11.4-16.P2.el7_8.6 updates 1.1 M bind-libs-lite x86_64 32:9.11.4-16.P2.el7_8.6 updates 1.1 M bind-license noarch 32:9.11.4-16.P2.el7_8.6 updates 90 k binutils x86_64 2.27-43.base.el7_8.1 updates 5.9 M ca-certificates noarch 2020.2.41-70.0.el7_8 updates 382 k dbus x86_64 1:1.10.24-14.el7_8 updates 245 k dbus-libs x86_64 1:1.10.24-14.el7_8 updates 169 k device-mapper x86_64 7:1.02.164-7.el7_8.2 updates 295 k device-mapper-libs x86_64 7:1.02.164-7.el7_8.2 updates 324 k grub2 x86_64 1:2.02-0.86.el7.centos updates 32 k grub2-common noarch 1:2.02-0.86.el7.centos updates 729 k grub2-pc x86_64 1:2.02-0.86.el7.centos updates 32 k grub2-pc-modules noarch 1:2.02-0.86.el7.centos updates 850 k grub2-tools x86_64 1:2.02-0.86.el7.centos updates 1.8 M grub2-tools-extra x86_64 1:2.02-0.86.el7.centos updates 1.0 M grub2-tools-minimal x86_64 1:2.02-0.86.el7.centos updates 174 k kernel-tools x86_64 3.10.0-1127.18.2.el7 updates 8.1 M kernel-tools-libs x86_64 3.10.0-1127.18.2.el7 updates 8.0 M libgudev1 x86_64 219-73.el7_8.8 updates 107 k microcode_ctl x86_64 2:2.1-61.10.el7_8 updates 2.7 M python-perf x86_64 3.10.0-1127.18.2.el7 updates 8.1 M rsyslog x86_64 8.24.0-52.el7_8.2 updates 621 k selinux-policy noarch 3.13.1-266.el7_8.1 updates 497 k selinux-policy-targeted noarch 3.13.1-266.el7_8.1 updates 7.0 M systemd x86_64 219-73.el7_8.8 updates 5.1 M systemd-libs x86_64 219-73.el7_8.8 updates 416 k systemd-sysv x86_64 219-73.el7_8.8 updates 94 k yum-plugin-fastestmirror noarch 1.1.31-54.el7_8 updates 34 k Removing: kernel x86_64 3.10.0-957.1.3.el7 @updates 63 M Transaction Summary ======================================================================================================================================================== Install 1 Package Upgrade 28 Packages Remove 1 Package Total download size: 105 M
Server restart required: reboot

After you did a full upgrade of all packages typically a server restart is required. Because this causes downtime and service loss we only want to do this if it is really necessary. To find out if a server restart is really required you can run the following commands:

Debian / Ubuntu shell> cat /var/run/reboot-required
CentOS / Redhat shell> yum install yum-utils shell> needs-restarting -r Core libraries or services have been updated: kernel -> 3.10.0-1127.18.2.el7 systemd -> 219-73.el7_8.8 dbus -> 1:1.10.24-14.el7_8 Reboot is required to ensure that your system benefits from these updates.

If only some services must be restarted you can see with:

shell> needs-restarting -s systemd-logind.service NetworkManager.service dbus.service avahi-daemon.service sshd.service polkit.service systemd-udevd.service tuned.service systemd-journald.service firewalld.service
Taxonomy upgrade extras: debian ubuntu centos redhat package upgrade lock locking

Categories: FromDual all (en), FromDual TechFeed (en), MySQL Tech-Feed (en)

MariaDB SQL Error Log Plugin

Shinguz - Thu, 2020-07-30 12:26

When you are for too long in business you think you know already everything and you are getting lazy. This happened to me again a few weeks ago. A customer asked me about the SQL Error Log Plugin. First I though he was talking about the MariaDB Error Log or the General Query Log. But then I have learned that there is something "new" I did not know yet...

MariaDB introduced in 5.5.22 (March 2012) a new plugin called the SQL Error Log Plugin. This Plugin collects all the errors which were sent from the MariaDB daemon to applications/clients and writes it to a log file.

This Client error log file can be analysed later to find and fix bugs in the application if the applications did not catch the errors themselves.

Installation

The Plugin can be easily installed with:

SQL> INSTALL PLUGIN sql_error_log SONAME 'sql_errlog';

and uninstalled again with:

SQL> UNINSTALL PLUGIN sql_error_log; Query OK, 0 rows affected, 1 warning (0.002 sec)

The installation and uninstallation informations are persistent and survive a database instance restart.

If you uninstall the plugin you get a warning:

telling you that the plugin is still running until the next database instance restart.

If the plugin currently is installed you can find with the following command:

SQL> SELECT * FROM information_schema.plugins WHERE PLUGIN_TYPE = 'AUDIT'\G *************************** 1. row *************************** PLUGIN_NAME: SQL_ERROR_LOG PLUGIN_VERSION: 1.0 PLUGIN_STATUS: ACTIVE PLUGIN_TYPE: AUDIT PLUGIN_TYPE_VERSION: 3.2 PLUGIN_LIBRARY: sql_errlog.so PLUGIN_LIBRARY_VERSION: 1.14 PLUGIN_AUTHOR: Alexey Botchkov PLUGIN_DESCRIPTION: Log SQL level errors to a file with rotation PLUGIN_LICENSE: GPL LOAD_OPTION: ON PLUGIN_MATURITY: Stable PLUGIN_AUTH_VERSION: 1.0
Variables

With the command:

you can see the SQL Error Log configuration variables. These variables are NOT shown if the the plugin is not installed yet or uninstalled again.

The SQL Error Log is written by default to $datadir/sql_error.log and cannot be change during runtime:/

SQL> SET GLOBAL sql_error_log_filename = '/home/mysql/database_slow/mariadb-105/log/sql_errors.log'; ERROR 1238 (HY000): Variable 'sql_error_log_filename' is a read only variable
Log Rotation

The other four variables are related to log rotation. It is cool, that this is done by the database and has not to be done by O/S means: See also: MariaDB Log Rotation.

On the first look it did not work like expected. But when reading the documentation again: This is the 'write-only' variable. Assigning TRUE to this variable forces the log rotation. it becomes understandable. If you enter:

SQL> SET GLOBAL sql_error_log_rotate=1; Query OK, 0 rows affected (0.000 sec)

the SQL Error Log is rotated in the background and you can see this in $datadir:

shell> ls -ltr sql_errors.log* -rw-rw---- 1 mysql mysql 53994 Jul 30 10:28 sql_errors.log.4 -rw-rw---- 1 mysql mysql 0 Jul 30 10:28 sql_errors.log.3 -rw-rw---- 1 mysql mysql 1517 Jul 30 10:29 sql_errors.log.2 -rw-rw---- 1 mysql mysql 0 Jul 30 10:29 sql_errors.log.1 -rw-rw---- 1 mysql mysql 6337 Jul 30 10:34 sql_errors.log

You should think in the way of: FLUSH SQL_ERROR LOGS;

The variable itself always stays OFF:

There are currently no status variables available to show how many errors were sent to the application.

Options

There is a configuration option NOT shown in the variable output: sql_error_log. Valid values are OFF, ON, FORCE and FORCE_PLUS_PERMANENT. It looks like that this option can only me used when injected directly into the call of the mysqld (mysqld_safe, systemd unit file, etc.) and thus it is useless for most cases because you do not want to change those things in production (upgrade, etc.).

Enabling/Disabling of the SQL Error Log

You can disable the SQL Error Log with the following command:

SQL> SET GLOBAL sql_error_log_rate = 0;

You enable it again by setting the value greater than 0.

Interpretation of the SQL Error Log

The format of the SQL Error Log looks roughly as follows: timestamp user[user] @ host[host] ERROR nnnn: Error Message : SQL Query

Here some examples:

shell> grep 2020 sql_errors.log 2020-07-30 10:30:05 fpmmm_agent[fpmmm_agent] @ localhost [127.0.0.1] ERROR 1142: SELECT command denied to user 'fpmmm_agent'@'localhost' for table 'global_priv' : WITH password_expiration_info AS -- All users 2020-07-30 10:36:51 root[root] @ localhost [] ERROR 1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'on *.* to fpmmm_agent@'127.0.0.1'' at line 1 : grant binlog_monitor on *.* to fpmmm_agent@'127.0.0.1' 2020-07-30 10:37:05 fpmmm_agent[fpmmm_agent] @ localhost [127.0.0.1] ERROR 1227: Access denied; you need (at least one of) the SUPER, BINLOG MONITOR privilege(s) for this operation : SHOW BINARY LOGS 2020-07-30 10:40:03 [] @ localhost [127.0.0.1] ERROR 1158: Got an error reading communication packets : (null)

This helped us for example to identify easily what privileges were missing for our applications.

Aggregation of the SQL Error Log

A simple way to aggregate the SQL Error Log is the following command:

shell> cat sql_errors.log* | sed 's/users2020/users\n2020/' | grep ^2020 | cut -b21- | sort | uniq -c 21 fpmmm_agent[fpmmm_agent] @ localhost [127.0.0.1] ERROR 1142: SELECT command denied to user 'fpmmm_agent'@'localhost' for table 'events_statements_current' : SELECT th.PROCESSLIST_USER AS user 7 fpmmm_agent[fpmmm_agent] @ localhost [127.0.0.1] ERROR 1142: SELECT command denied to user 'fpmmm_agent'@'localhost' for table 'events_statements_summary_by_user_by_event_name' : SELECT user, SUM(sum_created_tmp_disk_tables) AS created_tmp_disk_tables 22 fpmmm_agent[fpmmm_agent] @ localhost [127.0.0.1] ERROR 1142: SELECT command denied to user 'fpmmm_agent'@'localhost' for table 'global_priv' : WITH password_expiration_info AS 7 fpmmm_agent[fpmmm_agent] @ localhost [127.0.0.1] ERROR 1142: SELECT command denied to user 'fpmmm_agent'@'localhost' for table 'threads' : SELECT processlist_user AS user, COUNT(*) AS connections 7 fpmmm_agent[fpmmm_agent] @ localhost [127.0.0.1] ERROR 1142: SELECT command denied to user 'fpmmm_agent'@'localhost' for table 'threads' : SELECT th.processlist_user AS user, SUM(trx.trx_rows_locked) AS rows_locked 7 fpmmm_agent[fpmmm_agent] @ localhost [127.0.0.1] ERROR 1142: SELECT command denied to user 'fpmmm_agent'@'localhost' for table 'threads' : SELECT trx.trx_started, trx.trx_query 16 fpmmm_agent[fpmmm_agent] @ localhost [127.0.0.1] ERROR 1142: SELECT command denied to user 'fpmmm_agent'@'localhost' for table 'user' : SELECT DISTINCT user AS user FROM mysql.user 8 fpmmm_agent[fpmmm_agent] @ localhost [127.0.0.1] ERROR 1142: SELECT command denied to user 'fpmmm_agent'@'localhost' for table 'user' : SELECT GROUP_CONCAT(CONCAT(user, "@", host) SEPARATOR ", ") AS account FROM mysql.user WHERE user = "" 8 fpmmm_agent[fpmmm_agent] @ localhost [127.0.0.1] ERROR 1142: SELECT command denied to user 'fpmmm_agent'@'localhost' for table 'user' : SELECT GROUP_CONCAT(CONCAT(user, "@", host) SEPARATOR ", ") AS account FROM mysql.user WHERE user != "root" AND create_user_priv = "Y" 8 fpmmm_agent[fpmmm_agent] @ localhost [127.0.0.1] ERROR 1142: SELECT command denied to user 'fpmmm_agent'@'localhost' for table 'user' : SELECT GROUP_CONCAT(CONCAT(user, "@", host) SEPARATOR ", ") AS account FROM mysql.user WHERE user != "root" AND grant_priv = "Y" 8 fpmmm_agent[fpmmm_agent] @ localhost [127.0.0.1] ERROR 1142: SELECT command denied to user 'fpmmm_agent'@'localhost' for table 'user' : SELECT GROUP_CONCAT(CONCAT(user, "@", host) SEPARATOR ", ") AS account FROM mysql.user WHERE user = "root" AND host NOT IN ("localhost", "127.0.0.1", "::1", "chef") 8 fpmmm_agent[fpmmm_agent] @ localhost [127.0.0.1] ERROR 1142: SELECT command denied to user 'fpmmm_agent'@'localhost' for table 'user' : SELECT GROUP_CONCAT(CONCAT(user, "@", host) SEPARATOR ", ") AS account FROM mysql.user WHERE user != "root" AND super_priv = "Y" 20 fpmmm_agent[fpmmm_agent] @ localhost [127.0.0.1] ERROR 1227: Access denied; you need (at least one of) the SUPER, BINLOG MONITOR privilege(s) for this operation : SHOW BINARY LOGS 32 [] @ localhost [127.0.0.1] ERROR 1158: Got an error reading communication packets : (null) 1 [] @ localhost [] ERROR 1160: Got an error writing communication packets : (null) 1 root[root] @ localhost [] ERROR 1049: Unknown database 'bla' : (null) 1 root[root] @ localhost [] ERROR 1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'on *.* to fpmmm_agent@'127.0.0.1'' at line 1 : grant binlog_monitor on *.* to fpmmm_agent@'127.0.0.1' 1 root[root] @ localhost [] ERROR 1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SONAME 'sql_errlog'' at line 1 : UNINSTALL PLUGIN sql_error_log SONAME 'sql_errlog' 1 root[root] @ localhost [] ERROR 1193: Unknown system variable 'sql_error_log_rotat' : set global sql_error_log_rotat=on 1 root[root] @ localhost [] ERROR 1229: Variable 'sql_error_log_rotate' is a GLOBAL variable and should be set with SET GLOBAL : set session sql_error_log_rotate=on 1 root[root] @ localhost [] ERROR 1238: Variable 'sql_error_log_filename' is a read only variable : set global sql_error_log_filename='/home/mysql/database_slow/mariadb-105/log/sql_errors.log'

The sed command was needed because it looks like not every new entry starts at the beginning of a new line (bug?).

How to get warnings

The default sql_mode in MariaDB 10.5 is set to:

SQL> SHOW GLOBAL VARIABLES LIKE 'sql_mode'; +---------------+-------------------------------------------------------------------------------------------+ | Variable_name | Value | +---------------+-------------------------------------------------------------------------------------------+ | sql_mode | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +---------------+-------------------------------------------------------------------------------------------+

by adding TRADITIONAL to the sql_mode:

SQL> SET SESSION sql_mode=concat(@@sql_mode, ',TRADITIONAL');

you can make the SQL Error Log plugin also aware of warnings:

SQL> insert into test.test values (null, 'Some data', '2020-02-31'); Query OK, 1 row affected, 1 warning (0.001 sec) SQL> set session sql_mode=traditional; SQL> insert into test.test values (null, 'Some data', '2020-02-31'); ERROR 1292 (22007): Incorrect datetime value: '2020-02-31' for column `test`.`test`.`ts` at row 1

Just for curiosity, this query was never logged to the SQL Error Log:

SQL> SELECT 5/0; +------+ | 5/0 | +------+ | NULL | +------+ 1 row in set, 1 warning (0.000 sec)

But this seems to be more of a parser issue?

Logging comments

Comments are also logged. But the MariaDB client by default strips comments away. When you run the MariaDB client with the --comment option they are present to the server:

shell> mariadb --user=root --comment test SQL> /* some comment to find the problem */ insert into test.test values (5/0, 'Some data', '2020-02-31'); -- blub ERROR 1365 (22012): Division by 0

And it will be shown in the SQL Error Log:

2020-07-30 11:23:31 root[root] @ localhost [] ERROR 1365: Division by 0 : insert into test.test values (5/0, 'Some data', '2020-02-31') 2020-07-30 11:23:08 root[root] @ localhost [] ERROR 1365: Division by 0 : /* some comment to find the problem */ insert into test.test values (5/0, 'Some data', '2020-02-31') -- blub
Summary

The SQL Error Log is a cool plugin which helps us to identify problems and bugs in the application. This is similar functionality like in MariaDB/MySQL PERFORMANCE_SCHEMA: Finding syntactically wrong/erroneous MySQL queries.

Taxonomy upgrade extras: mariadb sql error logging error log syntax

Categories: FromDual all (en), FromDual TechFeed (en), MySQL Tech-Feed (en)

FromDual Ops Center File Transfer

Shinguz - Tue, 2020-07-28 09:59

With the FromDual Ops Center file transfer tool you can easily upload files from your personal computer to the focmm machine, download files from the focmm machine to your personal computer or transfer files from the focmm machine to any of your database machines or between them. This feature is made for importing, exporting or transferring data from, to or between your different database instances. For example to copy a production schema to a testing database instance.

For backup and restore of a database instance or schema see Operations.

You can reach the file transfer tool under the menu Tools on the left and then File transfer.

Upload

For uploading a file just click to the Browse... button, select a file from your local file system and then click to Upload File.

After a while, when the upload is completed, the file will appear in the Download section of your focmm machine.

Alternatively you can also upload files from your personal computer to the focmm machine via your favourite file transfer tools like scp or sftp.

Download

If you want to download a file from your focmm machine to your personal computer just click on one of the files shown in the download section. A window will pop-up and let you choose the location to store the file to.

Alternatively you can also download files from your focmm machine to your personal computer via your favourite file transfer tools like scp or sftp.

Transfer

To transfer files between your focmm machine and your database machines or between your different database machines you have to choose the machines first, then navigate through your file system tree on each server and then press the transfer link (arrow left to right or arrow right to left) to transfer the file.

Taxonomy upgrade extras: focmm FromDual Ops Center file file transfer

Categories: FromDual all (en), FromDual TechFeed (en), MySQL Tech-Feed (en)

Centralized Crontab with FromDual Ops Center

Shinguz - Thu, 2020-07-23 11:18

One of the tools of FromDual Ops Center for MariaDB and MySQL is the centralized crontab for all of your database machines. Instead of maintaining various different crontabs on different machines you can manage them now in one place within the Ops Center.

Under the Tools menu on the left you find Crontab. Here you get a first overview of crontab jobs available:

The first column shows if the crontab job is active or not.
The second column indicates the O/S user the crontab job should run as.
Then we have the typical crontab scheduling entries: Minute, Hour, Day, Month and Day of Week (DoW).
Now follows the actual command which should be run with a comment describing the command.
The column Last indicates when the job was run last successfully.
And RC shows the last Return Code of the job (0 - success, otherwise error number).
With the trash icon you can delete a crontab job and with the run icon you can start and run a crontab job right now.

With the Add Crontab Entry button on the bottom you can add new crontab entries. To make it persistent you have to save the entry with the Save Crontab button.

You can change as well existing crontab entries directly in-line followed by the Save Crontab button.

Caution: Do not mix up the Ops Center Crontab under the Configuration/Crontab menu with the Centralized Crontab for your database machines under Tools/Crontab menu.

Taxonomy upgrade extras: focmm FromDual Ops Center crontab centralization

Categories: FromDual all (en), FromDual TechFeed (en), MySQL Tech-Feed (en)

Centralized Crontab with FromDual Ops Center

Shinguz - Thu, 2020-07-23 11:18

One of the tools of FromDual Ops Center for MariaDB and MySQL is the centralized crontab for all of your database machines. Instead of maintaining various different crontabs on different machines you can manage them now in one place within the Ops Center.

Under the Tools menu on the left you find Crontab. Here you get a first overview of crontab jobs available:

The first column shows if the crontab job is active or not.
The second column indicates the O/S user the crontab job should run as.
Then we have the typical crontab scheduling entries: Minute, Hour, Day, Month and Day of Week (DoW).
Now follows the actual command which should be run with a comment describing the command.
The column Last indicates when the job was run last.
And RC shows the last Return Code of the job (0 - success, otherwise error number).
With the trash icon you can delete a crontab job and with the run icon you can start and run a crontab job right now.

With the Add Crontab Entry button on the bottom you can add new crontab entries. To make it persistent you have to save the entry with the Save Crontab button.

You can change as well existing crontab entries directly in-line followed by the Save Crontab button.

Caution: Do not mix up the Ops Center Crontab under the Configuration/Crontab menu with the Centralized Crontab for your database machines under Tools/Crontab menu.

Taxonomy upgrade extras: focmm FromDual Ops Center crontab centralization

Categories: FromDual all (en), FromDual TechFeed (en), MySQL Tech-Feed (en)

Increase file limit of a running process

Shinguz - Fri, 2020-06-19 18:53

Asking stupid questions and googling for them is fun some times...

Today I was asking myself if one could rise the file limit for a running MariaDB mysqld process online without restarting the database instance?

And I found an answer on serverfault: Set max file limit on a running process:

PID=$(pidof mysqld) grep -e 'Max open files' -e Limit /proc/${PID}/limits Limit Soft Limit Hard Limit Units Max open files 1024 4096 files prlimit --pid $PID | grep -e NOFILE -e DESC RESOURCE DESCRIPTION SOFT HARD UNITS NOFILE max number of open files 1024 4096 files prlimit --nofile --output RESOURCE,SOFT,HARD --pid ${PID} RESOURCE SOFT HARD NOFILE 1024 4096 sudo prlimit --nofile=2048:8192 --pid ${PID} prlimit --nofile --output RESOURCE,SOFT,HARD --pid ${PID} RESOURCE SOFT HARD NOFILE 2048 8192
Literature

prlimit(1)

Increase file limit of a running process

Shinguz - Fri, 2020-06-19 18:53

Asking stupid questions and googling for them is fun some times...

Today I was asking myself if one could rise the file limit for a running MariaDB mysqld process online without restarting the database instance?

And I found an answer on serverfault: Set max file limit on a running process:

PID=$(pidof mysqld) grep -e 'Max open files' -e Limit /proc/${PID}/limits Limit Soft Limit Hard Limit Units Max open files 1024 4096 files prlimit --pid $PID | grep -e NOFILE -e DESC RESOURCE DESCRIPTION SOFT HARD UNITS NOFILE max number of open files 1024 4096 files prlimit --nofile --output RESOURCE,SOFT,HARD --pid ${PID} RESOURCE SOFT HARD NOFILE 1024 4096 sudo prlimit --nofile=2048:8192 --pid ${PID} prlimit --nofile --output RESOURCE,SOFT,HARD --pid ${PID} RESOURCE SOFT HARD NOFILE 2048 8192
Literature

prlimit(1)

FromDual Performance Monitor for MariaDB 1.2.0 has been released

Shinguz - Fri, 2020-06-12 16:47

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

More detailed information you can find in the fpmmm Installation Guide.

Download

The new FromDual Performance Monitor for MariaDB (fpmmm) can be downloaded from here or you can use our FromDual repositories. 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 Monitor for MariaDB please report it to the FromDual Bug-tracker or just send us an email.

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

Monitoring as a Service (MaaS)

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

Installation of Performance Monitor 1.2.0

A complete guide on how to install FromDual Performance Monitor you can find in the fpmmm Installation Guide.

Upgrade of fpmmm tarball from 1.0.x to 1.2.0

Upgrade with DEB/RPM packages should happen automatically. For tarballs follow this:

shell> cd /opt shell> tar xf /download/fpmmm-1.2.0.tar.gz shell> rm -f fpmmm shell> ln -s fpmmm-1.2.0 fpmmm
Changes in FromDual Performance Monitor for MariaDB 1.2.0

This release contains new features and various bug fixes.

You can verify your current FromDual Performance Monitor for MariaDB version with the following command:

shell> fpmmm --version
General

MariaDB 10.5 problems fixed. Fpmmm supports MariaDB 10.5 now!
Naming convention for Type changed from host to machine and mysqld to instance, including downwards compatibility.
New host screens added.
All Screens removed because they are customer specific, we have host screens now.
Zabbix templates adapted to the more flexible trigger URL.
Renamed all files to make it more agnostic.

Server

Code made more robust for cloud databases.
Free file descriptors removed because it is always zero, trigger added for 80% file descriptors used.
Cache file base bug in getIostat fixed.
Server graph for file descriptors improved
I/O queue ymin set to 0.
Server template optimized.
Iostat graphs added to server template.
Integrated iostat data into fpmmm.
All registered devices and bug on svctm fixed.
More info added when server module is called with --debug option.
Disk status items cleaned-up and filesystem names added for creating new items.
Interface eth1 removed but list of all interfaces added.
NUMA trigger added.
Macros for network interfaces added.

Data

Data module added to measure schema and instance size.
Code made ready for cloud databases.

Galera

Galera cluster size graph y axis set to 0.
3 Galera graphs added, URL added to some triggers, title of one trigger changed.
gmcast segment added.
Locale state name of item fixed, local status removed, cluster and local state added, gmcast segment added to template.
wsrep version fixed to new format.
Item name change for cluster_conf_id.
Dirty code fixed, found on cloud databases.

User

Module for per user data added.
Dirty code fixed, found on cloud databases.
User info for transactions added.
Tmp disk tables and sort merge passes per user information added.

Agent

Output format zabbix, icinga, nagios and centreon should be supported now.
Error messages for connect improved.
Option --debug added, one message was not handled correctly in verbosity level.
Parameters in function goThroughAllSections cleaned-up.
Option -h added, info more clear when wrong options were used.
URLs added to fpmmm template.
fpmmm check and trigger improved.
Made error handling better after test of 1.1.0 on CentOS 7.
fpmmm trigger error message improved.

InnoDB

NUMA information and warning trigger added to InnoDB module.
Trigger for innodb_force_recovery made repeatable.
Alert level for innodb_force_recovery increased, InnoDB non default page size alert added.
InnoDB deadlock detection is alarmed, when disabled.
innodb_metrics only works with SUPER privilege, fixed.
InnoDB Log Buffer much to small trigger added on Innodb_log_waits item.
Items and graphs for InnoDB temporary tables added.

MyISAM

Items and graphs for MyISAM temporary tables added.

Aria

Items and graphs for Aria temporary tables added.

Security

Expired user added for MariaDB including alert.

Slave

URL added and two triggers made repeatable.

Backup

Backup will report EVERY failure and URL is now useful!

For subscriptions of commercial use of fpmmm please get in contact with us.

Taxonomy upgrade extras: performance monitor monitoring fpmmm maas release graph

Categories: FromDual all (en), FromDual TechFeed (en), MySQL Tech-Feed (en)

Languages

Login

You are here

MariaDB Push Replication

VSZ behaviour with MariaDB MEMORY tables

Traffic mirroring with MariaDB MaxScale

MariaDB Galera Cluster Upgrade Path

How to force InnoDB Buffer Pool flushing

Upgrading from MariaDB 10.4 to MariaDB 10.5 Galera Cluster

Partial Restore of a Table into a MariaDB Galera Cluster

MariaDB/MySQL Datenbank-Administrator/in gesucht

Partial Table or Schema restore from mariabackup full backup

FromDual Backup and Recovery Manager for MariaDB and MySQL 2.2.2 has been released

Creating synthetic data sets for tuning SQL queries

MyISAM locking and who is the evil?

MariaDB and MySQL package holding or locking

MariaDB SQL Error Log Plugin

FromDual Ops Center File Transfer

Centralized Crontab with FromDual Ops Center

Centralized Crontab with FromDual Ops Center

Increase file limit of a running process

Increase file limit of a running process

FromDual Performance Monitor for MariaDB 1.2.0 has been released

Pages

About us

Navigation

Trademarks

Languages

Login

Search form

You are here

News

Pages

About us

Navigation

Trademarks