Feed Aggregator

We got some feedback from Paul Campbell about Handler_read_first:

Thought you might like to know that you can get do this without a full index scan (in 5.7 at least) with a query for SELECT MIN(pkey), only the first key is read in this instance. SELECT MAX(pkey) will do the same for Handler_read_last.

e.g.

mysql> EXPLAIN select min(a) from t2;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Select tables optimized away | …

FromDual has the pleasure to announce the release of the new version 2.2.3 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 described in FromDual Backup and Recovery Manager (brman) installation guide.

In the inconceivable case that you find a bug in the FromDual Backup and Recovery Manager please report it to the FromDual Bugtracker or just send us an [email](mailto:contact@fromdual.com?Subject=Bug report for brman).

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

Upgrade from 2.x to 2.2.3

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

Changes in FromDual Backup and Recovery Manager 2.2.3

This release is a new minor release. It contains only bug fixes. We have tried to …

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 …

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 …

An inside view from the MariaDB database instance can be received with:

SQL> SHOW GLOBAL STATUS LIKE 'memory_used';
+---------------+------------+
| Variable_name | Value      |
+---------------+------------+
| Memory_used   | 3235456112 |
+---------------+------------+

Here the memory is listed as released when the TRUCATE TABLE is done.

I just got a note from Markus Makela with the following suggestion:

You could try and see if the mirror router from the experimental module package would work as a substitute for this.

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 …

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 …

Hi Oli,

I think that it could be worth mentioning that in MariaDB 10.5, the described problem should no longer exist, thanks to https://jira.mariadb.org/browse/MDEV-19747 removing the code. Also, in older MariaDB versions, https://jira.mariadb.org/browse/MDEV-23720 disables the feature by default.

I must confess that omitting the redo logging was my suggestion. Unfortunately, no proper benchmarking was conducted before it was implemented in MySQL 5.7. With a reasonable innodb_log_file_size, performance should be acceptable, as noted in the above two MDEVs.

Best regards,

Marko
--
Marko Mäkelä, Lead Developer InnoDB
MariaDB Corporation

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 …

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 …

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 <br>
   --databases="${SCHEMA}" <br>
   --tables="${TABLE}" <br>
   --datadir=${DATADIR} <br>
   --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; …

For me it was for a long time not clear if a mariadb-backup 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 mariadb-backup 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 …

Table of Contents

• Global Options
• Service Options
• Server Options
• Routing Modules
• Listener Options
• Monitor Options
• Filter Options

Global Options

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

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

Upgrade from 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 …

How could the following SQL queries be improved performance wise and otherwise and can you also explain why your change is more optimal?

Please consider, when testing, that your results are not confused by the Query Cache or by reading data from your I/O system which are an order of magnitude faster (Query Cache) or slower (I/O) than the in-memory behaviour.

Table of Contents

• Covering function
• LIKE search
• Covering function twice
• Compare 2 indexed columns
• Function and 2 indexed columns
• Time series problem
• Wrong built index or query
• OR optimization
• Nested subquery DELETE
• Wrong casting
• Non normalized join column
• Optimizer is not considering ICP
• OR optimization with Materialized View

SQL Query Tuning - Covering function

Difficulty: Very easy.

Query:

SELECT *
  FROM bills
 WHERE UNIX_TIMESTAMP(due_date) < 1601560170
;

Table and data:

CREATE TABLE bills (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
, amount DECIMAL (10,2) NOT NULL
, due_date DATETIME NOT NULL
, customer_id SMALLINT UNSIGNED NOT NULL …

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 …

And this is what the Linux kernel documentation says about it: https://www.kernel.org/doc/Documentation/networking/bonding.txt

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 …

802.3ad bond interface have show high RX dropped packets

I found a god explanation for dropped packages on bonded interfaces: [ 1 ]

This is related to the bonding mode and _not_ a bug. The bonding module will drop duplicate frames received on inactive ports, which is normal behaviour. Overall the packets should be getting into the machine without problems since they are received on the active slave. To confirm this do the following

1) Check dropped packets from all interfaces. So if eth0/eth1 are connected to bond0, we may see dropped packets for bond0 and eth0, but not for eth1. This depends on which interface is the active interface. This can be checked using the following:

cat /sys/class/net/bond0/bonding/active_slave

So if the active_slave isn't dropping packets, and the inactive slave is dropping packets this is normal in 'active-backup' mode (or any mode where there is an inactive slave).

2) If we want both interfaces to not drop packets we can use 'all_slaves_active' bonding module parameter. …