Feed Aggregator

Currently we are in the testing phase of the next release of our Ops Center (focmm). This testing is quite complex because it includes testing the interaction of various different components like a MariaDB Galera Cluster, a Galera Load Balancer, a virtual IP, etc.

Recently I was in the Linuxhotel for a Galera Cluster Training and there one of the other trainers was romanticising about Linux Containers and Incus. So I had a short look, if Incus could be useful to simplify our unit testing. Some old knowledge from a previous Docker PoC was quite helpful to start with…

Prepare an Incus container for Galera Load Balancer

This did NOT work as expected because the Debian image was lacking IPv4 addresses and I did not find on the quick how to change that. So I used the Ubuntu 22.04 image.

$ incus remote list
$ incus image list images: ubuntu/22.04 amd64

$ INSTANCE='qa-glb'

$ incus launch images:ubuntu/jammy ${INSTANCE}
$ incus list *glb* …

I used to maintain Flexviews, but I ported it to use a native MySQL syntax. Now CREATE INCREMENTAL MATERIALIZED VIEW … is a reality. Inner join and all aggregation functions are supported. Check out http://www.leapdb.com

The newest release of the Performance Monitor for MariaDB and MySQL v2.0.0 (fpmmm) allows you to monitor your MariaDB and MySQL databases via SNMP (Simple Network Management Protocol).

This feature enables you to report your Database Metrics into Enterprise Monitoring solutions from Microsoft, IBM or into Oracle Cloud Control.

In this article we will show you the few simple steps it needs to send the data from your fpmmm Agent to your SNMP Manager.

Check if SNMP Manager can be reached

To make sure fpmmm can send data to the SNMP Manager at all you can test the connection with the following command to send SNMP Notifications to the SNMP Manager:

# COMMUNITY='public'
# MANAGER='192.168.56.102'
# PORT='162'
# TRAP_OID='1.3.6.1.4.1.57800.1.1.2'
# OID='1.3.6.1.4.1.57800.1.1.1'
# TYPE='c'
# VALUE=$(mariadb --user=root --execute="SELECT variable_value FROM information_schema.global_status WHERE variable_name LIKE 'threads_running'<br>G" | grep variable_value | cut -d' ' -f2)

# snmptrap -v 2c -c …

FromDual has the pleasure to announce the release of the new version 2.0.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](mailto:contact@fromdual.com?Subject=Bug report for fpmmm).

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

For this how-to we were working with a MariaDB 10.6.5 database server and the newest MariaDB MaxScale 6.3.0 (aka 2.6) load balancer/proxy.

As a starting point we had a working MariaDB Master/Master replication with one active Master (M_a) and one passive Master (M_p). Whereas the passive Master was set to read_only. As a replication user we used the user replication.

Creating database accounts for the MaxScale Load Balancer

The MaxScale load balancer connects itself with the application users to the database. To do this it needs to retrieve the available users from the database first. To get these users the MaxScale account needs some privileges:

CREATE USER 'maxscale_admin'@'%' IDENTIFIED BY 'secret';

GRANT SELECT ON mysql.user TO 'maxscale_admin'@'%';
GRANT SELECT ON mysql.db TO 'maxscale_admin'@'%';
GRANT SELECT ON mysql.tables_priv TO 'maxscale_admin'@'%';
GRANT SELECT ON mysql.columns_priv TO 'maxscale_admin'@'%';
GRANT SELECT ON mysql.procs_priv TO 'maxscale_admin'@'%';
GRANT SELECT ON mysql.proxies_priv …

Because the original MariaDB documentation is a little bit sparse about this topic here are some notes about how to do a streaming MariaDB backup:

# mariadb-backup --user=root --backup --parallel=4 --stream=mbstream | ssh root@target_machine "/usr/bin/mbstream -x -C /var/lib/mysql/"

# mariadb-backup --user=root --prepare --target-dir=/var/lib/mysql

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

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

Changes in FromDual Backup and Recovery Manager 2.2.5

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

Are containers so popular because application developers do not know how to write multi-threaded applications well? With containers you can circumvent this problem a bit. You ramp up man single threaded applications in containers and thus get a multi-threaded behaviour.
Databases ARE already multi-threaded. So no need to containerize them.

As already mentioned earlier, we roughly have a hand full of customers which are playing with the though of sharding solutions. They typically have many different customers (clients, tenants) and the number of customers becomes so huge (thousands to millions) that one machine cannot cope with the load any more.

So splitting the load by customers to different machines makes sense. This is quite easy when customers are separated per schema. In the good old times of Open Source our customers have implemented those solutions themselves. But nowadays it looks like do-it-yourself is not sexy any more. It seems like this core competence of a business advantage must be outsourced. So some vendors have already made some solutions available to solve this need: Sharding Solutions.

My question here is: Can a generic sharding solution build exactly what your business needs? Are you still capable to optimize your business process in the way you need it when you buy a 3^rd party solution?

And: If you use another product, you …

In the last months we got more and more requests for supporting MariaDB/MySQL/Galera Cluster in (mostly Docker) containers.

Because of its additional layer and added complexity I do not like containers much. Containers are more complicated during troubleshooting and debugging problems.

Other people have already written more than enough about the advantages of containers. What is more difficult to find are the disadvantages of technologies. Thus I focus on those:

Wrong technology?

Container solutions were designed to deal with stateless applications that have ephemeral data. Containers spin up a quick microservice and then destroy it. This includes all the components of that container (including its cache and data). The transient nature of containers is because all of the components and services of that container are considered to be part of the container (essentially it is all or nothing). Serving the container a data volume owned by the underlying O/S by punching a hole through the container can be very …

Once in a year or so we get a request of a customer about MariaDB/MySQL sharding solutions. So here we have a list of sharding solutions we are currently aware of:

• MySQL Fabric: A Brief Introduction to MySQL Fabric This product is discontinued by MySQL in the meanwhile!
• Spider Storage Engine for MariaDB: Spider Storage Engine Overview
• MariaDB MaxScale: Simple Sharding with Two Servers and Schema Router
• ProxySQL: Sharding in ProxySQL
• PingCap TiDB (Titan DB): Release Candidate der verteilten Datenbank TiDB veröffentlicht and PingCap
• Vitess: What is Vitess

What does it need for Schema sharding

• Metadata database: Where you record which customer schema is located in which shard (with a locking mechanism).
• Your application connector or a “sharding facility” must do the (re-)direction.
• A re-sharding mechanism for “load balancing”.
• A good Monitoring per schema and per shard so you know which schema causes more load and which shard is soon overloaded.

See also our blog post: Sharding …

This week I came across an old known issue reported in May 2010: Master/Slave Replication with binlog_format = ROW and tables without a Primary Key is a bad idea! Especially if these tables are huge.

Why this is a bad idea is described in the bug report #53375:

if one runs DML on a table that has no indexes, a full table scan is done. with RBR, the slave might need to scan the full table for each row changed.

The consequence of this behaviour is that the Slave starts lagging. It was further mentioned:

Worst part is that PROCESSLIST, etc provide absolutely NO obvious indication what is going on, for something that may take 12 hours, 3 days or even more…

Symptoms of this problem are described as follows:

Observe 78,278 row locks but only 10,045 undo log entries, so many more rows being scanned than changed. Also observer 16 row deletes per second but 600,754 row reads per second, same mismatch between counts suggesting unindexed accesses are happening.

You may also see “invalidating query …

We get ever and ever again customer requests concerning Deadlocks. First of all, Deadlocks are usually an application problem, not a database problem! The database itself manifests the application problem with the following message which is sent to the application as an error:

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

If your application receives this error message you know where in your application you have a problem. But a deadlock is always a problem between 2 different connections. So how you can find the other part of the problem?

The full Deadlock situation is shown with the following command:

SQL> SHOW ENGINE InnoDB STATUS<br>G
...
------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-12-23 18:55:18 0x7f51045e3700
*** (1) TRANSACTION:
TRANSACTION 847, ACTIVE 10 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 46, OS thread …

Is this not just a simple an Anonymous PL/SQL Block?

I believe that the infinite loop example is just standard SQL. The only problem is that, in this context, BEGIN means START TRANSACTION. But you can use BEGIN NOT ATOMIC instead. A nice feature contributed by Antony Curtis.

Also in 2022 there will be a FOSDEM (Free and Open source Software Developers’ European Meeting) on 5 and 6 February 2022. This time again online from Brussels (Belgium).

MariaDB has again its own Devroom and the Call for Papers (CfP) is now open for your submissions. We are looking for interesting topics about your daily business, technical presentations, war stories, point of views of management, etc. The deadline for the CfP is before 21 December 2021.

For further information about how to submit a presentation please look here: https://mariadb.org/cfp-for-the-mariadb-devroom-fosdem-2022-now-open/

If you need any help or if you have any question please let us know…

The MariaDB Connection ID exists since long ago. So why bother about the Connection ID? Because it is interesting and you can do some interesting things with the Connection ID like tracking statements in your connections and find where they come from your application code.

The MariaDB Connection ID is a strictly monotonic increasing number starting with 1 at server restart:

shell> mariadb --user=root --execute='SELECT CONNECTION_ID()<br>G' | grep CONNECTION_ID
CONNECTION_ID(): 2372
shell> mariadb --user=root --execute='SELECT CONNECTION_ID()<br>G' | grep CONNECTION_ID
CONNECTION_ID(): 2373
shell> mariadb --user=root --execute='SELECT CONNECTION_ID()<br>G' | grep CONNECTION_ID
CONNECTION_ID(): 2374

The MariaDB documentation states
[1
]:

Returns the connection ID (thread ID) for the connection. Every thread (including events) has an ID that is unique among the set of currently connected clients.

The MariaDB documentation is only partly correct because the thread ID is something …

XXX means Thread ID (= Connection ID) and YYY wsrep transaction sequence number (wsrep_last_committed?):

snprintf(filename, len+1, "%s/GRA_%lld_%lld.log",
         wsrep_data_home_dir, (long long) thd->thread_id,
         (long long) wsrep_thd_trx_seqno(thd));

From 25 to 29 October 2021 (calendar week 43) we will have another MariaDB / MySQL advanced training in the Linuxhotel in Essen (Germany). The training is in German and will take place on-site (3G!). There are still some places free!

More details about the training you can find here.

In all FromDual tools the rc is unique. So you can easily find where in the code the error happened. In your case it is in lib/myEnv.inc in the function stopInstance.

In the code we check if PID file is empty. And it is empty if it cannot be found:

• in my.cnf specified in myenv.conf as variable pid_file or pid-file
• under $datadir/mysqld.pid
• under $datadir/<hostname>.pid

It would be interesting for us if you could tell us where your PID file is located and where it is configured.