MySQL Tech-Feed (en)

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

Shinguz - Tue, 2024-06-25 15:12

FromDual has the pleasure to announce the release of the new version 2.3.0 of its popular Backup and Recovery Manager for MariaDB and MySQL (brman).

The new FromDual Backup and Recovery Manager can be downloaded from here. The FromDual Repositories were updated. How to install and use the Backup and Recovery Manager is 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.

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

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

This release is a new major release. The most important new feature is the partial physical restore. We have tried to maintain backward-compatibility with the 1.2, 2.0, 2.1 and 2.2 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

Packaging: Release el9 added and debian format increase from 9 to 10 because of debian12.
Documentation prepared for Asciidoctor.
Distribtion dependent things fixed.
Build script improved.
Typos fixed.
All print replaced by standard PHP echo commands.
Redhat replace by Red Hat and CentOS by Rocky Linux.
Copyright year updated to 2024.
Library myEnv updated (getDistribution, lsb_release removed).

FromDual Backup Manager (bman)

Bug in sftp transfer for archiving files fixed.
Also create subdirectories only once if necessary in physical backup.
Archive location directory is only created once in physical backup. This avoids errors and makes the backup slightly faster.
In function createArchiveDestinationDirectory php-ssh2 calls where replaced by the more stable scp/sftp calls.
Bug in archive destination directory creation fixed and example improved.
Library LXClib added for testing.
Number of lines in dump parsing increased because of changes in MySQL 8.0.
Binlog backup debug output made nicer.
Retention time increased from 800 days to 1200 days (more than 3 years), tests added for this, newer DB versions added to tests.
Advices apt-get and yum replace by apt and dnf.
Error was not returned correctly when bman aborts because of MyISAM tables. Fixed.
Syntax error fixed.
flock on fpmmm file added.
BINLOG MONITOR privilege check added for MariaDB 10.11.
Code clean-up.
Example enhanced.
Output made more clear for trouble shooting (doCleanup).

FromDual Recovery Manager (rman)

Various bugs fixed found during automated testing.
Some minor fixes about timing and repeatable testing, warnings and messages improved.
mariadb_backup_binlog_info case (MariaDB 11.4) added for physical full backup.
Code refactored and case when backup is compressed caught.
Function doPhysicalRestore renamed to doFullPhysicalRestore.
Split restore functions in their own files.
sql_log_bin is used for schema restore on partial physical restore.
New example for partial physical restore added.
New method for partial physical restore implemented.
Partial physical schema restore added.

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

Shrinking the InnoDB system tablespace

Shinguz - Mon, 2024-06-17 14:45

One feature that really excited me in the new MariaDB 11.4 LTS release is the shrinking of the system tablespace (ibdata1). I have been eagerly waiting for this feature since around 2006 and now it has finally arrived with MariaDB 11.4.
Actually, this feature has been available since MariaDB 11.2 IR (June 2023).

Unfortunately, the announcement of this feature came a little too short. In the MariaDB release notes it says succinctly:

The InnoDB system tablespace is now shrunk by reclaiming unused space at startup (MDEV-14795)

From the MariaDB 11.2.0 Release Notes.

The reasons why this file can grow immeasurably have actually been known for a long time and the measures against it are also clear (see literature). But again and again we see MariaDB users out in the field who didn't realise the problem or realised it too late and are now left with an ibdata1 file that is far too large...

How can the problem be provoked?

The problem can be provoked by creating a table in the system tablespace:

SQL> SET global innodb_file_per_table = off; SQL> CREATE TABLE `test` ( `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=InnoDB;

and then filling it with data:

SQL> INSERT INTO test SELECT NULL, 'Some data to provoke huge data growth in system tablespace', NOW() ; SQL> INSERT INTO test SELECT NULL, 'Some data to provoke huge data growth in system tablespace', NOW() FROM test LIMIT 1000000 ; ...

While the table is being filled, you can observe how the file ibdata1 swells on the file system:

$ while [ 1 ] ; do ll -h ibdata1 ; sleep 5 ; done -rw-rw---- 1 mysql mysql 12M Jun 2 13:57 ibdata1 -rw-rw---- 1 mysql mysql 76M Jun 12 13:57 ibdata1 -rw-rw---- 1 mysql mysql 76M Jun 12 13:57 ibdata1 -rw-rw---- 1 mysql mysql 140M Jun 12 13:58 ibdata1 -rw-rw---- 1 mysql mysql 204M Jun 12 13:58 ibdata1 -rw-rw---- 1 mysql mysql 268M Jun 12 13:58 ibdata1 -rw-rw---- 1 mysql mysql 332M Jun 12 13:59 ibdata1 -rw-rw---- 1 mysql mysql 396M Jun 12 13:59 ibdata1 -rw-rw---- 1 mysql mysql 460M Jun 12 13:59 ibdata1 -rw-rw---- 1 mysql mysql 524M Jun 12 13:59 ibdata1 -rw-rw---- 1 mysql mysql 588M Jun 12 13:59 ibdata1 -rw-rw---- 1 mysql mysql 652M Jun 12 13:59 ibdata1 -rw-rw---- 1 mysql mysql 716M Jun 12 13:59 ibdata1 -rw-rw---- 1 mysql mysql 780M Jun 12 14:00 ibdata1 -rw-rw---- 1 mysql mysql 844M Jun 12 14:00 ibdata1 -rw-rw---- 1 mysql mysql 908M Jun 12 14:00 ibdata1 -rw-rw---- 1 mysql mysql 972M Jun 12 14:00 ibdata1 -rw-rw---- 1 mysql mysql 1.1G Jun 12 14:00 ibdata1 -rw-rw---- 1 mysql mysql 1.2G Jun 12 14:00 ibdata1 -rw-rw---- 1 mysql mysql 1.3G Jun 12 14:00 ibdata1 -rw-rw---- 1 mysql mysql 1.4G Jun 12 14:00 ibdata1

If the ibdata1 file is large enough, you can move the table from the system tablespace to a dedicated tablespace:

SQL> SET global innodb_file_per_table = off; SQL> ALTER TABLE test.test FORCE; Query OK, 0 rows affected (33.764 sec)

And you can see how the new file is created:

$ ll -h ibdata1 test/* -rw-rw---- 1 mysql mysql 1.4G Jun 12 14:01 ibdata1 -rw-rw---- 1 mysql mysql 1.1K Jun 12 14:01 test/#sql-alter-dca30-12.frm -rw-rw---- 1 mysql mysql 696M Jun 12 14:01 test/#sql-alter-dca30-12.ibd -rw-rw---- 1 mysql mysql 1.1K Jun 12 13:56 test/test.frm -rw-rw---- 1 mysql mysql 1.4G Jun 12 14:01 ibdata1 -rw-rw---- 1 mysql mysql 1.1K Jun 12 14:01 test/test.frm -rw-rw---- 1 mysql mysql 1.4G Jun 12 14:02 test/test.ibd

So now we have once the data but twice as much space used.

And how can you make the system tablespace smaller again?

Unfortunately, this information is somewhat hidden and has to be gathered from the documentation and the MariaDB Jira issues (see literature):

SQL> SET GLOBAL innodb_fast_shutdown=0; SQL> SHUTDOWN;

When shutting down, you can see the corresponding entries in the MariaDB error log:

[Note] bin/mariadbd (initiated by: root[root] @ localhost []): Normal shutdown [Note] InnoDB: FTS optimize thread exiting. [Note] InnoDB: Truncating system tablespace from 90880 to 768 pages [Note] InnoDB: System tablespace truncated successfully [Note] InnoDB: Starting shutdown... [Note] InnoDB: Dumping buffer pool(s) to /home/mysql/database/mariadb-114/data/ib_buffer_pool [Note] InnoDB: Restricted to 2016 pages due to innodb_buf_pool_dump_pct=25 [Note] InnoDB: Buffer pool(s) dump completed at 240612 14:11:11 [Note] InnoDB: Removed temporary tablespace data file: "./ibtmp1" [Note] InnoDB: Shutdown completed; log sequence number 4011132308; transaction id 139 [Note] bin/mariadbd: Shutdown complete

And if you look at the file ibdata1 on disc afterwards, it is as small as it was at the beginning of the experiment:

$ ll ibdata1* -h -rw-rw---- 1 mysql mysql 12M Jun 12 14:11 ibdata1
Literature

Shrinking the InnoDB system tablespace

Shinguz - Mon, 2024-06-17 14:45

One feature that really excited me in the new MariaDB 11.4 LTS release is the shrinking of the system tablespace (ibdata1). I have been eagerly waiting for this feature since around 2006 and now it has finally arrived with MariaDB 11.4.
Actually, this feature has been available since MariaDB 11.2 IR (June 2023).

Unfortunately, the announcement of this feature came a little too short. In the MariaDB release notes it says succinctly:

The InnoDB system tablespace is now shrunk by reclaiming unused space at startup (MDEV-14795)

From the MariaDB 11.2.0 Release Notes.

The reasons why this file can grow immeasurably have actually been known for a long time and the measures against it are also clear (see literature). But again and again we see MariaDB users out in the field who didn't realise the problem or realised it too late and are now left with an ibdata1 file that is far too large...

How can the problem be provoked?

The problem can be provoked by creating a table in the system tablespace:

SQL> SET global innodb_file_per_table = off; SQL> CREATE TABLE `test` ( `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=InnoDB;

and then filling it with data:

SQL> INSERT INTO test SELECT NULL, 'Some data to provoke huge data growth in system tablespace', NOW() ; SQL> INSERT INTO test SELECT NULL, 'Some data to provoke huge data growth in system tablespace', NOW() FROM test LIMIT 1000000 ; ...

While the table is being filled, you can observe how the file ibdata1 swells on the file system:

$ while [ 1 ] ; do ll -h ibdata1 ; sleep 5 ; done -rw-rw---- 1 mysql mysql 12M Jun 2 13:57 ibdata1 -rw-rw---- 1 mysql mysql 76M Jun 12 13:57 ibdata1 -rw-rw---- 1 mysql mysql 76M Jun 12 13:57 ibdata1 -rw-rw---- 1 mysql mysql 140M Jun 12 13:58 ibdata1 -rw-rw---- 1 mysql mysql 204M Jun 12 13:58 ibdata1 -rw-rw---- 1 mysql mysql 268M Jun 12 13:58 ibdata1 -rw-rw---- 1 mysql mysql 332M Jun 12 13:59 ibdata1 -rw-rw---- 1 mysql mysql 396M Jun 12 13:59 ibdata1 -rw-rw---- 1 mysql mysql 460M Jun 12 13:59 ibdata1 -rw-rw---- 1 mysql mysql 524M Jun 12 13:59 ibdata1 -rw-rw---- 1 mysql mysql 588M Jun 12 13:59 ibdata1 -rw-rw---- 1 mysql mysql 652M Jun 12 13:59 ibdata1 -rw-rw---- 1 mysql mysql 716M Jun 12 13:59 ibdata1 -rw-rw---- 1 mysql mysql 780M Jun 12 14:00 ibdata1 -rw-rw---- 1 mysql mysql 844M Jun 12 14:00 ibdata1 -rw-rw---- 1 mysql mysql 908M Jun 12 14:00 ibdata1 -rw-rw---- 1 mysql mysql 972M Jun 12 14:00 ibdata1 -rw-rw---- 1 mysql mysql 1.1G Jun 12 14:00 ibdata1 -rw-rw---- 1 mysql mysql 1.2G Jun 12 14:00 ibdata1 -rw-rw---- 1 mysql mysql 1.3G Jun 12 14:00 ibdata1 -rw-rw---- 1 mysql mysql 1.4G Jun 12 14:00 ibdata1

If the ibdata1 file is large enough, you can move the table from the system tablespace to a dedicated tablespace:

SQL> SET global innodb_file_per_table = off; SQL> ALTER TABLE test.test FORCE; Query OK, 0 rows affected (33.764 sec)

And you can see how the new file is created:

$ ll -h ibdata1 test/* -rw-rw---- 1 mysql mysql 1.4G Jun 12 14:01 ibdata1 -rw-rw---- 1 mysql mysql 1.1K Jun 12 14:01 test/#sql-alter-dca30-12.frm -rw-rw---- 1 mysql mysql 696M Jun 12 14:01 test/#sql-alter-dca30-12.ibd -rw-rw---- 1 mysql mysql 1.1K Jun 12 13:56 test/test.frm -rw-rw---- 1 mysql mysql 1.4G Jun 12 14:01 ibdata1 -rw-rw---- 1 mysql mysql 1.1K Jun 12 14:01 test/test.frm -rw-rw---- 1 mysql mysql 1.4G Jun 12 14:02 test/test.ibd

So now we have once the data but twice as much space used.

And how can you make the system tablespace smaller again?

Unfortunately, this information is somewhat hidden and has to be gathered from the documentation and the MariaDB Jira issues (see literature):

SQL> SET GLOBAL innodb_fast_shutdown=0; SQL> SHUTDOWN;

When shutting down, you can see the corresponding entries in the MariaDB error log:

[Note] bin/mariadbd (initiated by: root[root] @ localhost []): Normal shutdown [Note] InnoDB: FTS optimize thread exiting. [Note] InnoDB: Truncating system tablespace from 90880 to 768 pages [Note] InnoDB: System tablespace truncated successfully [Note] InnoDB: Starting shutdown... [Note] InnoDB: Dumping buffer pool(s) to /home/mysql/database/mariadb-114/data/ib_buffer_pool [Note] InnoDB: Restricted to 2016 pages due to innodb_buf_pool_dump_pct=25 [Note] InnoDB: Buffer pool(s) dump completed at 240612 14:11:11 [Note] InnoDB: Removed temporary tablespace data file: "./ibtmp1" [Note] InnoDB: Shutdown completed; log sequence number 4011132308; transaction id 139 [Note] bin/mariadbd: Shutdown complete

And if you look at the file ibdata1 on disc afterwards, it is as small as it was at the beginning of the experiment:

$ ll ibdata1* -h -rw-rw---- 1 mysql mysql 12M Jun 12 14:11 ibdata1
Literature

FromDual Ops Center 1.2.2 for MariaDB and MySQL databases has been released

Shinguz - Mon, 2024-04-29 17:41

FromDual is pleased to announce the release of the new version 1.2.2 of the popular FromDual Ops Center focmm, a Graphical User Interface (GUI) for MariaDB, MySQL and compatible databases.

The FromDual Ops Center for MariaDB and MySQL (focmm) helps DBAs and system administrators to better manage their MariaDB and MySQL databases and Galera Cluster farms. Ops Center makes life easier for DBAs and Admins!

The main purpose of the Ops Center is to help you with your daily MariaDB and MySQL operations. More information about FromDual Ops Center can be found in the General Information section.

Download now

The new FromDual Ops Center for MariaDB and MySQL (focmm) can be downloaded from our download page. How to install and use focmm is documented in the Ops Center User Guide.

In the unlikely event that you find a bug in the FromDual Ops Center for MariaDB and MySQL please report it to the FromDual bug tracker or just send us an email.

Any feedback, comments and testimonials are also welcome! Please send them to feedback@fromdual.com.

Installing Ops Center 1.2.2

Information on how to install FromDual Ops Center can be found in the Ops Center User Guide.

Upgrading from 0.9.x to 1.2.2

The upgrade from 0.9.x to 1.2.2 should be automatic. Please make a backup of your Ops Center database instance before upgrading! See also Upgrading.

Important: In some cases the directory focmm/tmp/start_jobs.lock is missing. In this case jobs will not start. Please check the log file under focmm/log/start_jobs.log and create the folder accordingly. There should also be a file called pid in this folder. Create this file if it does not exist.

Downgrading from 1.2.2

Downgrading from 1.2.2 to 1.2.1 should work.

Changes in Ops Center 1.2.2 Machine

Machine information is automatically gathered after first successful check now.
Machine function calls optimized.
Ssh key check added for machine.
Some tests around machine library added.
Function createPublicKey + page refactored.
Copy of ssh key and ssh command implemented.
Skip machines which are down during gathering resource costs.
Error message in pricing when adding a new machine was fixed.

Instance

Instance operations are now faster when instance is running but not reachable.
Node check improved in cluster and instance, works now also correctly on multi instance environments.
PID file is gathered now.
Stop instance on instance operations improved if a Galera cluster node.
Instance is now checked immediately after start or restart and checks are set to failed after instance stop. So the instance state is more appropriate and in time now.
Link added to read_only variable comment.
Instance is shown orange when ping check was not run yet. This is fixed now.
Error log gathering improved in instance operations.
If mysql_upgrade_info file is missing error is suppressed now.
Gather instance information is done now right after the first successful check.
Check database_ping was removed from general instance tests to avoid noise in the database error log.
Check instance_ping_check removed from instanceTest to avoid error log messages on a regular base, wondering what kind of impact this has...
innodb_log_writer_threads rule added to instance configuration.
Target connect function error logging improved.
Continue button is handled correctly now when stopping, starting or restarting an instance.
Instance operations location of error log is shown now.
Refactored instance operations code for moving Galera buttons out.
Function refreshInstance refactored because we need this information for Galera Cluster.
Unknown array_keys error message fixed in instance.
Foreign MariaDB/MySQL repositories should now be considered as well.
Optimized instance handling.
Instance error log handling can now handle systemd/journalctl.
Instance hardening when creating instance improved.
Create instance improved.
Create instance bug fixed.
Instance create on Rocky 9, fixed.
Create instance for Rocky 8 improved.
Check for restart also removed, so also restart works now.
Check removed to allow a running but not reachable instance to be restarted, restart and start button added.
Non existing configuration was not handled correctly, fixed.
Edit instance placeholder added and default instance name changed from mysqld to mariadbd.
Create instance output made nicer in case of failure.

Cluster

Cluster overview signal lights are also working now for Galera.
Cluster operations returns faster now if machine is not reachable.
Cluster show checks non important information removed.
Cluster checks in menu are now shown correctly.
grastate.dat async check added.
Cluster check made async.
Instance state added to cluster operations and check view.
M/S cluster tests made more robust against strange situations.
Some bugs in M/S cluster fixed.
focmm user passwords should sync in galera.
Bootstrap should distribute focmm user on all nodes.
Bootstrap on Debian fixed again.
Error message for missing grastate.dat file is suppressed now.
Deploy configuration button is now on a new line.
2 little bugs in M/S cluster operations removed.
Instance operations improved with Galera clusters.
Soft bootstrap implemented.
Cluster node bootstrap, start stop fixed.
Function bootstrap implemented.
Async rolling cluster restart added.
Problem with rolling restart fixed.
Cluster check and operations is read if not OK. Failed, same behaviour as before!
Rolling cluster restart and other buttons added to cluster operations.
Variable wsrep_node_name is set to instance name per default.
Galera cluster config deployment made smoother and fixed bug.
Sort order of cluster nodes and title of table clarified.
Page start/stop notices added to cluster.php.
Indention problem in function getClusterChecks fixed.

Load Balancer

Terms load-balancer and loadbalancer replaced by load balancer.
User radmin added for ProxySQL monitoring.
Socket default for HAproxy changed to the new standards.

Virtual IP (VIP)/Floating IP

Status grey image does not exist in vip_show_checks, fixed.

Tools

Crontab: Missing library in check_instances.php added.
Crontab: html tags removed in mail.
Crontab: Error messages and error handling in crontab and start jobs improved, lock file directory is created automatically now.

Configuration

No changes.

Database-as-a-Service (DBaaS)

No changes.

Building and Packaging

Bulid moved to MyEnv project.
Missing Rocky Linux branch added.
Package redhat-lsb-core does not exist on Rocky 9 any more, spec file adapted.
Package build test fixed.

Themes / UI

jquery updated from 3.6.3 to 3.7.1

General

Modification for license key automation added.
Package installation ssh-keygen is only called if key does not exist. So upgrade should work without error/warning.
Some tests fixed, bug in createPublicKey fixed.
Function listRemoteDirectory does not write needlessly error messages any more.
HTML tag placeholder added for add function, add object and save object made more error prone.
Variable tx_isolation replaced by transaction_isolation which was deprecated in MariaDB 11.2 and MySQL 5.7.
Code clean-up.
Some menu items opened a new tab which was not intended. Fixed.
Field naming made consistent.
Error handling and error messages improved.
Copyright year updated from 2023 to 2024.
Message downgraded from ERROR to DEBUG.
Function runRemoteCommand will return an error code again when going_back_to functionality implemented.
Function renderDropDown newline was removed.
Function renderSubmitButton accepts a title now.
Function runRemoteCommand can suppress error logging now.
Command apt-get replaced by apt.
Distribution information refactored.
Function whoami cleaned-up.
PHP 8.0 function calls removed again because of Debian 10/11 support.
User interaction made unique among all 5 different objects.
Redundant DEBUG information removed.
Configuration file was removed again when not needed any more (clean-up).
Command yum replaced by dnf, also apt-get partly.
Command which replaced by type -p to make it Rocky 9 compliant.
Some more lsb_release stuff removed.
myEnv.inc library updated, lsb_release removed.
Dead code message added.

Repository

No changes.

Documentation

Version changed from 1.2.0 to 1.2.1 and TLS conflict on ubuntu 22.04 fixed.
Build automatized.
Documentation for M/S updated.
All text taken over from CMS.
Load balancer documentation added.

Taxonomy upgrade extras: Operations release FromDual Ops Center ops center dbaas focmm

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

FromDual Ops Center 1.2.2 for MariaDB and MySQL databases has been released

Shinguz - Mon, 2024-04-29 17:41

FromDual is pleased to announce the release of the new version 1.2.2 of the popular FromDual Ops Center focmm, a Graphical User Interface (GUI) for MariaDB, MySQL and compatible databases.

The FromDual Ops Center for MariaDB and MySQL (focmm) helps DBAs and system administrators to better manage their MariaDB and MySQL databases and Galera Cluster farms. Ops Center makes life easier for DBAs and Admins!

The main purpose of the Ops Center is to help you with your daily MariaDB and MySQL operations. More information about FromDual Ops Center can be found in the General Information section.

Download now

The new FromDual Ops Center for MariaDB and MySQL (focmm) can be downloaded from our download page. How to install and use focmm is documented in the Ops Center User Guide.

In the unlikely event that you find a bug in the FromDual Ops Center for MariaDB and MySQL please report it to the FromDual bug tracker or just send us an email.

Any feedback, comments and testimonials are also welcome! Please send them to feedback@fromdual.com.

Installing Ops Center 1.2.2

Information on how to install FromDual Ops Center can be found in the Ops Center User Guide.

Upgrading from 0.9.x to 1.2.2

The upgrade from 0.9.x to 1.2.2 should be automatic. Please make a backup of your Ops Center database instance before upgrading! See also Upgrading.

Important: In some cases the directory focmm/tmp/start_jobs.lock is missing. In this case jobs will not start. Please check the log file under focmm/log/start_jobs.log and create the folder accordingly. There should also be a file called pid in this folder. Create this file if it does not exist.

Downgrading from 1.2.2

Downgrading from 1.2.2 to 1.2.1 should work.

Changes in Ops Center 1.2.2 Machine

Machine information is automatically gathered after first successful check now.
Machine function calls optimized.
Ssh key check added for machine.
Some tests around machine library added.
Function createPublicKey + page refactored.
Copy of ssh key and ssh command implemented.
Skip machines which are down during gathering resource costs.
Error message in pricing when adding a new machine was fixed.

Instance

Instance operations are now faster when instance is running but not reachable.
Node check improved in cluster and instance, works now also correctly on multi instance environments.
PID file is gathered now.
Stop instance on instance operations improved if a Galera cluster node.
Instance is now checked immediately after start or restart and checks are set to failed after instance stop. So the instance state is more appropriate and in time now.
Link added to read_only variable comment.
Instance is shown orange when ping check was not run yet. This is fixed now.
Error log gathering improved in instance operations.
If mysql_upgrade_info file is missing error is suppressed now.
Gather instance information is done now right after the first successful check.
Check database_ping was removed from general instance tests to avoid noise in the database error log.
Check instance_ping_check removed from instanceTest to avoid error log messages on a regular base, wondering what kind of impact this has...
innodb_log_writer_threads rule added to instance configuration.
Target connect function error logging improved.
Continue button is handled correctly now when stopping, starting or restarting an instance.
Instance operations location of error log is shown now.
Refactored instance operations code for moving Galera buttons out.
Function refreshInstance refactored because we need this information for Galera Cluster.
Unknown array_keys error message fixed in instance.
Foreign MariaDB/MySQL repositories should now be considered as well.
Optimized instance handling.
Instance error log handling can now handle systemd/journalctl.
Instance hardening when creating instance improved.
Create instance improved.
Create instance bug fixed.
Instance create on Rocky 9, fixed.
Create instance for Rocky 8 improved.
Check for restart also removed, so also restart works now.
Check removed to allow a running but not reachable instance to be restarted, restart and start button added.
Non existing configuration was not handled correctly, fixed.
Edit instance placeholder added and default instance name changed from mysqld to mariadbd.
Create instance output made nicer in case of failure.

Cluster

Cluster overview signal lights are also working now for Galera.
Cluster operations returns faster now if machine is not reachable.
Cluster show checks non important information removed.
Cluster checks in menu are now shown correctly.
grastate.dat async check added.
Cluster check made async.
Instance state added to cluster operations and check view.
M/S cluster tests made more robust against strange situations.
Some bugs in M/S cluster fixed.
focmm user passwords should sync in galera.
Bootstrap should distribute focmm user on all nodes.
Bootstrap on Debian fixed again.
Error message for missing grastate.dat file is suppressed now.
Deploy configuration button is now on a new line.
2 little bugs in M/S cluster operations removed.
Instance operations improved with Galera clusters.
Soft bootstrap implemented.
Cluster node bootstrap, start stop fixed.
Function bootstrap implemented.
Async rolling cluster restart added.
Problem with rolling restart fixed.
Cluster check and operations is read if not OK. Failed, same behaviour as before!
Rolling cluster restart and other buttons added to cluster operations.
Variable wsrep_node_name is set to instance name per default.
Galera cluster config deployment made smoother and fixed bug.
Sort order of cluster nodes and title of table clarified.
Page start/stop notices added to cluster.php.
Indention problem in function getClusterChecks fixed.

Load Balancer

Terms load-balancer and loadbalancer replaced by load balancer.
User radmin added for ProxySQL monitoring.
Socket default for HAproxy changed to the new standards.

Virtual IP (VIP)/Floating IP

Status grey image does not exist in vip_show_checks, fixed.

Tools

Crontab: Missing library in check_instances.php added.
Crontab: html tags removed in mail.
Crontab: Error messages and error handling in crontab and start jobs improved, lock file directory is created automatically now.

Configuration

No changes.

Database-as-a-Service (DBaaS)

No changes.

Building and Packaging

Bulid moved to MyEnv project.
Missing Rocky Linux branch added.
Package redhat-lsb-core does not exist on Rocky 9 any more, spec file adapted.
Package build test fixed.

Themes / UI

jquery updated from 3.6.3 to 3.7.1

General

Modification for license key automation added.
Package installation ssh-keygen is only called if key does not exist. So upgrade should work without error/warning.
Some tests fixed, bug in createPublicKey fixed.
Function listRemoteDirectory does not write needlessly error messages any more.
HTML tag placeholder added for add function, add object and save object made more error prone.
Variable tx_isolation replaced by transaction_isolation which was deprecated in MariaDB 11.2 and MySQL 5.7.
Code clean-up.
Some menu items opened a new tab which was not intended. Fixed.
Field naming made consistent.
Error handling and error messages improved.
Copyright year updated from 2023 to 2024.
Message downgraded from ERROR to DEBUG.
Function runRemoteCommand will return an error code again when going_back_to functionality implemented.
Function renderDropDown newline was removed.
Function renderSubmitButton accepts a title now.
Function runRemoteCommand can suppress error logging now.
Command apt-get replaced by apt.
Distribution information refactored.
Function whoami cleaned-up.
PHP 8.0 function calls removed again because of Debian 10/11 support.
User interaction made unique among all 5 different objects.
Redundant DEBUG information removed.
Configuration file was removed again when not needed any more (clean-up).
Command yum replaced by dnf, also apt-get partly.
Command which replaced by type -p to make it Rocky 9 compliant.
Some more lsb_release stuff removed.
myEnv.inc library updated, lsb_release removed.
Dead code message added.

Repository

No changes.

Documentation

Version changed from 1.2.0 to 1.2.1 and TLS conflict on ubuntu 22.04 fixed.
Build automatized.
Documentation for M/S updated.
All text taken over from CMS.
Load balancer documentation added.

Taxonomy upgrade extras: Operations release FromDual Ops Center ops center dbaas focmm

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

dbstat for MariaDB after one month of productive use

Shinguz - Fri, 2024-04-26 14:13

Table of contents

Review
One month later
Size of the tables
Process list
Global variables
Metadata Lock and InnoDB Transaction Lock
Global status

Review

After we introduced dbstat for MariaDB (and MySQL) a good 5 weeks ago, we naturally also rolled it out on our systems to test the behaviour in daily use (eat your own dog food).

This went quite well until we came up with the idea of activating dbstat on the passive dbstat node on our MariaDB active/passive master/master replication cluster (a similar situation would also occur with a Galera cluster). We realised that the design of dbstat still had potential. After this problem was fixed (v0.0.2 and v0.0.3) and the problem of how to activate events on master AND slave was solved (MDEV-33782: Event is always disabled on slave), everything seemed fine at first glance. Unfortunately, we did not realise that the data also had to be adjusted. As a result, our replication came to a complete stop over the Easter holidays, which then led to another problem when catching up (MDEV-33923: MariaDB parallel replication causes Foreign Key errors).

After this minor incident was also resolved, dbstat has been running flawlessly on our MariaDB master/master replication cluster ever since... The product dbstat is open source (GPLv2) and can be downloaded from GitHub.

One month later

Databases should NOT grow over time but only over the number of {customers, products, etc.} once the desired equilibrium (steady state) is reached. In our dbstat installation, we have set this equilibrium state to 30 days. So it is now time that the size of dbstat stabilises and the database stops growing...

It would also be interesting to understand what practical use dbstat has. That is why we have now set to work and are trying to analyse the results of dbstat.

Here is an overview of the 11 current running database events:

Firstly, the growth of dbstat itself is interesting. But of course this evaluation can also be carried out for any other database, table or catalogue (coming in MariaDB 11.7?):

SQL> SET SESSION sql_mode='STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,only_full_group_by'; SQL> SET @machine_name = @@hostname; SQL> SELECT `table_schema`, SUBSTR(`ts`, 1, 10) AS date , ROUND(SUM(`data_length`)/1024/1024, 1) AS data_mb , ROUND(SUM(`index_length`)/1024/1024, 1) AS index_mb , ROUND(SUM(`data_free`)/1024/1024, 1) AS free_mb , ROUND((SUM(`data_length`) + SUM(`index_length`) + SUM(`data_free`))/1024/1024, 1) AS total_mb , ROUND(SUM(`table_rows`)/1000/1000, 1) AS rows_m FROM `table_size` WHERE `machine_name` = @machine_name AND `table_catalog` = 'def' AND `table_schema` = 'dbstat' GROUP BY `table_catalog`, `table_schema`, `date` ORDER BY `table_catalog`, `table_schema`, `date` ASC ; +--------------+------------+---------+----------+---------+----------+--------+ | table_schema | date | data_mb | index_mb | free_mb | total_mb | rows_m | +--------------+------------+---------+----------+---------+----------+--------+ | dbstat | 2024-03-26 | 762.8 | 1128.6 | 18.0 | 1909.4 | 10.9 | | dbstat | 2024-03-27 | 835.8 | 1241.6 | 17.0 | 2094.4 | 11.1 | | dbstat | 2024-03-28 | 837.8 | 1241.6 | 14.0 | 2093.4 | 11.8 | | dbstat | 2024-03-29 | 960.7 | 1443.6 | 18.0 | 2422.4 | 14.2 | | dbstat | 2024-03-30 | 960.7 | 1443.6 | 17.0 | 2421.4 | 15.0 | | dbstat | 2024-03-31 | 1057.7 | 1604.6 | 20.0 | 2682.4 | 16.9 | | dbstat | 2024-04-01 | 1057.7 | 1602.6 | 21.0 | 2681.4 | 17.6 | | dbstat | 2024-04-02 | 1172.7 | 1797.6 | 22.0 | 2992.3 | 17.8 | | dbstat | 2024-04-03 | 1442.8 | 2333.7 | 12.0 | 3788.5 | 22.8 | | dbstat | 2024-04-04 | 1649.8 | 2723.7 | 13.0 | 4386.5 | 24.4 | | dbstat | 2024-04-05 | 1649.8 | 2722.7 | 14.0 | 4386.5 | 26.0 | | dbstat | 2024-04-06 | 1821.8 | 3034.8 | 13.0 | 4869.6 | 24.6 | | dbstat | 2024-04-07 | 1821.8 | 3034.8 | 14.0 | 4870.6 | 26.2 | | dbstat | 2024-04-08 | 1989.9 | 3344.8 | 12.0 | 5346.6 | 29.9 | | dbstat | 2024-04-09 | 1990.9 | 3343.8 | 14.0 | 5348.6 | 31.5 | | dbstat | 2024-04-10 | 2193.9 | 3712.8 | 13.0 | 5919.7 | 31.6 | | dbstat | 2024-04-11 | 2193.9 | 3712.8 | 15.0 | 5921.7 | 31.1 | | dbstat | 2024-04-12 | 2405.8 | 4119.1 | 12.0 | 6537.0 | 34.9 | | dbstat | 2024-04-13 | 2405.8 | 4119.1 | 14.0 | 6538.9 | 35.7 | | dbstat | 2024-04-14 | 2480.8 | 4278.9 | 15.0 | 6774.8 | 36.2 | | dbstat | 2024-04-15 | 2560.8 | 4443.7 | 12.0 | 7016.5 | 37.5 | | dbstat | 2024-04-16 | 2560.8 | 4443.7 | 12.0 | 7016.5 | 38.2 | | dbstat | 2024-04-17 | 2640.8 | 4610.6 | 18.0 | 7269.4 | 38.5 | | dbstat | 2024-04-18 | 2640.9 | 4611.6 | 14.0 | 7266.5 | 39.7 | | dbstat | 2024-04-19 | 2743.9 | 4826.5 | 14.0 | 7584.3 | 36.9 | | dbstat | 2024-04-20 | 2826.9 | 4995.5 | 14.0 | 7836.4 | 38.3 | | dbstat | 2024-04-21 | 2830.9 | 4997.4 | 18.0 | 7846.3 | 39.2 | | dbstat | 2024-04-22 | 2919.9 | 5177.4 | 14.0 | 8111.3 | 43.2 | | dbstat | 2024-04-23 | 2923.0 | 5177.3 | 16.0 | 8116.3 | 44.1 | | dbstat | 2024-04-24 | 3020.0 | 5376.3 | 16.0 | 8412.3 | 41.0 | | dbstat | 2024-04-25 | 3024.0 | 5377.3 | 17.0 | 8418.3 | 40.9 | +--------------+------------+---------+----------+---------+----------+--------+

If you take the disc space in the O/S for comparison:

# du -shc *.ibd 8.6G global_status.ibd 308K global_variables.ibd 692K metadata_lock.ibd 97M processlist.ibd 18M table_size.ibd 212K trx_and_lck.ibd 8.7G total

you can see that the values from the database are approximately correct (5% error)...

Important: The database dbstat reaches a size of approx. 9 Gbyte after approx. one month on a not particularly large database system.

You can also see that the size of the database is only just stabilising:

If you want to know more precisely which tables are responsible for which part of the data volume, you can also zoom in or drill down into the data:

SQL> SELECT `table_name`, SUBSTR(`ts`, 1, 10) AS date , ROUND(`data_length`/1024/1024, 1) AS data_mb , ROUND(`index_length`/1024/1024, 1) AS index_mb , ROUND(`data_free`/1024/1024, 1) AS free_mb , ROUND((`data_length` + `index_length` + `data_free`)/1024/1024, 1) AS total_mb , ROUND((`data_length` + `index_length` + `data_free`)/1024/1024/8418.26*100, 1) AS pct , ROUND(`table_rows`/1000/1000, 1) AS rows_m FROM `table_size` WHERE `machine_name` = @machine_name AND `table_catalog` = 'def' AND `table_schema` = 'dbstat' AND SUBSTR(`ts`, 1, 10) = CURRENT_DATE() ORDER BY rows_m DESC ; +------------------+------------+---------+----------+---------+----------+------+--------+ | table_name | date | data_mb | index_mb | free_mb | total_mb | pct | rows_m | +------------------+------------+---------+----------+---------+----------+------+--------+ | global_status | 2024-04-25 | 2949.9 | 5356.9 | 5.0 | 8311.8 | 98.7 | 40.4 | | processlist | 2024-04-25 | 68.2 | 17.1 | 7.0 | 92.2 | 1.1 | 0.4 | | global_variables | 2024-04-25 | 0.1 | 0.1 | 0.0 | 0.2 | 0.0 | 0.0 | | metadata_lock | 2024-04-25 | 0.4 | 0.2 | 0.0 | 0.6 | 0.0 | 0.0 | | table_size | 2024-04-25 | 5.4 | 3.1 | 5.0 | 13.5 | 0.2 | 0.0 | | trx_and_lck | 2024-04-25 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | +------------------+------------+---------+----------+---------+----------+------+--------+

Note: Please excuse the non-use of the window function!

The only real driver for the data volume of this database is the global_status table. This is to be expected (see: see quantity structure of dbstat).

SQL> SELECT SUBSTR(ts, 1, 10) AS date, table_rows/1000/1000 AS k_rows , ROUND(data_length/1024/1024, 1) AS data_mb, ROUND(index_length/1024/1024, 1) AS index_mb, ROUND(data_free/1024/1024, 1) AS free_mb , ROUND((data_length + index_length + data_free)/1024/1024, 1) AS total_mb FROM table_size WHERE `machine_name` = @machine_name AND `table_catalog` = 'def' AND `table_schema` = 'dbstat' AND table_name = 'global_status' AND ts > DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY) ; +------------+-------------+---------+----------+---------+----------+ | date | k_rows | data_mb | index_mb | free_mb | total_mb | +------------+-------------+---------+----------+---------+----------+ | 2024-04-15 | 37.13876300 | 2512.9 | 4433.0 | 4.0 | 6949.9 | | 2024-04-16 | 37.94217200 | 2512.9 | 4433.0 | 4.0 | 6949.9 | + 0M | 2024-04-17 | 38.19867500 | 2592.9 | 4600.0 | 7.0 | 7199.9 | + 250M | 2024-04-18 | 39.39108500 | 2592.9 | 4600.0 | 5.0 | 7197.9 | - 2M | 2024-04-19 | 36.52539600 | 2691.9 | 4813.0 | 5.0 | 7509.8 | + 312M | 2024-04-20 | 37.99073500 | 2770.9 | 4980.9 | 6.0 | 7757.8 | + 248M | 2024-04-21 | 38.79420200 | 2770.9 | 4980.9 | 7.0 | 7758.8 | + 1M | 2024-04-22 | 42.82606200 | 2855.9 | 5158.9 | 6.0 | 8020.8 | + 263M | 2024-04-23 | 43.62953000 | 2855.9 | 5158.9 | 7.0 | 8021.8 | + 1M | 2024-04-24 | 40.54342200 | 2949.9 | 5356.9 | 7.0 | 8313.8 | + 292M | 2024-04-25 | 40.43067700 | 2949.9 | 5356.9 | 5.0 | 8311.8 | - 2M +------------+-------------+---------+----------+---------+----------+

Note: Sorry, I should really familiarise myself with the window functions...

If we analyse the data a bit more closely, we see that the number of rows has slowly stabilised over the last 4 days (note: table_rows is calculated (from the number of blocks and the average row length?) and is not an exact value), but the "amount of data" has continued to increase until yesterday, which is probably due to the fragmentation of the tables and indexes...

The primary key of the global_status table was chosen to optimise the localisation of the data:

PRIMARY KEY (`machine_name`,`variable_name`,`ts`),

The situation should calm down in the next few days. In 2 to 4 weeks we will have to check the situation again.

Summary: I would say that this feature fulfils the requirements and helps to understand the data growth.

List of processes

Since we do not have any serious load issues in our databases, this feature is not that interesting in our case. For example, we can see what a (persistent) connection has done:

SQL> SELECT connection_id, ts, command, time, state, SUBSTR(REGEXP_REPLACE(REPLACE(query, "\n", ' '), '\ +', ' '), 1, 64) FROM processlist WHERE machine_name = @machine_name AND command != 'Sleep' AND connection_id = @connection_id AND state NOT IN ( 'Waiting for next activation' , 'Master has sent all binlog to slave; waiting for more updates' , 'Waiting for master to send event' , 'Slave has read all relay log; waiting for more updates' ) ORDER BY ts ASC ; +---------------+---------------------+---------+-------+----------------+----------------------------------------------------------------------+ | connection_id | ts | command | time | state | SUBSTR(REGEXP_REPLACE(REPLACE(query, "\n", ' '), '\ +', ' '), 1, 64) | +---------------+---------------------+---------+-------+----------------+----------------------------------------------------------------------+ | 18 | 2024-04-17 12:30:28 | Query | 0.029 | Sending data | select pp.item_preprocid,pp.itemid,pp.type,pp.params,pp.step,h.h | | 18 | 2024-04-17 14:58:28 | Query | 0.009 | Writing to net | select itemtagid,itemid,tag,value from item_tag | | 18 | 2024-04-18 06:24:28 | Query | 0.003 | Sending data | select pp.item_preprocid,pp.itemid,pp.type,pp.params,pp.step,h.h | | 18 | 2024-04-18 11:34:28 | Query | 0.030 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-18 16:39:28 | Query | 0.006 | Sending data | select itemid,functionid,name,parameter,triggerid from functions | | 18 | 2024-04-18 19:12:28 | Query | 0.014 | Sending data | select triggerid,description,expression,error,priority,type,valu | | 18 | 2024-04-18 21:49:28 | Query | 0.004 | Writing to net | select i.itemid,i.hostid,i.templateid from items i inner join ho | | 18 | 2024-04-19 00:21:28 | Query | 0.032 | Sending data | select pp.item_preprocid,pp.itemid,pp.type,pp.params,pp.step,h.h | | 18 | 2024-04-19 02:59:28 | Query | 0.017 | Writing to net | select triggerid,description,expression,error,priority,type,valu | | 18 | 2024-04-19 05:39:28 | Query | 0.052 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-19 08:19:28 | Query | 0.000 | Statistics | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-19 13:26:28 | Query | 0.075 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-19 15:57:28 | Query | 0.027 | Writing to net | select itemtagid,itemid,tag,value from item_tag | | 18 | 2024-04-19 18:33:28 | Query | 0.010 | Sending data | select itemtagid,itemid,tag,value from item_tag | | 18 | 2024-04-19 21:10:28 | Query | 0.008 | Sending data | select pp.item_preprocid,pp.itemid,pp.type,pp.params,pp.step,h.h | | 18 | 2024-04-19 23:50:28 | Query | 0.067 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-20 02:28:28 | Query | 0.008 | Sending data | select triggerid,description,expression,error,priority,type,valu | | 18 | 2024-04-20 05:08:28 | Query | 0.052 | Writing to net | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-20 07:44:28 | Query | 0.123 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-20 10:21:28 | Query | 0.144 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-20 12:55:28 | Query | 0.004 | Sending data | select i.itemid,i.hostid,i.templateid from items i where i.flags | | 18 | 2024-04-20 15:35:28 | Query | 0.092 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-20 18:12:28 | Query | 0.041 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-20 20:47:28 | Query | 0.113 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-20 23:25:28 | Query | 0.101 | Writing to net | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-21 02:03:28 | Query | 0.120 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-21 04:42:28 | Query | 0.099 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-21 07:18:28 | Query | 0.015 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-21 12:32:28 | Query | 0.018 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-21 15:06:28 | Query | 0.091 | Writing to net | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-21 20:16:28 | Query | 0.012 | Sending data | select itemtagid,itemid,tag,value from item_tag | | 18 | 2024-04-22 06:44:28 | Query | 0.161 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-22 09:21:28 | Query | 0.000 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-22 11:54:28 | Query | 0.020 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-22 14:23:28 | Query | 0.067 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-22 16:59:28 | Query | 0.128 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-22 22:05:28 | Query | 0.078 | Writing to net | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-23 00:38:28 | Query | 0.084 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-23 03:15:28 | Query | 0.098 | Writing to net | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-23 05:52:28 | Query | 0.000 | starting | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-23 08:27:28 | Query | 0.011 | Sending data | select pp.item_preprocid,pp.itemid,pp.type,pp.params,pp.step,h.h | | 18 | 2024-04-23 10:58:28 | Query | 0.000 | Sending data | select i.itemid,i.hostid,i.templateid from items i inner join ho | | 18 | 2024-04-23 13:31:28 | Query | 0.110 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-23 16:01:28 | Query | 0.023 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-23 18:35:28 | Query | 0.095 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-23 21:10:28 | Query | 0.017 | Writing to net | select itemtagid,itemid,tag,value from item_tag | | 18 | 2024-04-23 23:44:28 | Query | 0.014 | Sending data | select triggerid,description,expression,error,priority,type,valu | | 18 | 2024-04-24 02:21:28 | Query | 0.024 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-24 07:33:28 | Query | 0.046 | Writing to net | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | +---------------+---------------------+---------+-------+----------------+----------------------------------------------------------------------+

It is also important that we only see the entries in this report when the thread has done SOMETHING (we have hidden state Sleep). It is also interesting that we do not see this (persistent) connection before 17 April, but at the moment I have NO explanation for this from an operational point of view (restart etc.). Probably the application (Zabbix) has to explain this.

Global variables

The information in the global_variables table is also interesting:

SQL> SELECT variable_name, ts, variable_value FROM global_variables WHERE machine_name = @machine_name AND variable_name IN ( SELECT variable_name FROM global_variables WHERE machine_name = @machine_name GROUP BY variable_name HAVING COUNT(*) > 1 ) ORDER BY ts, variable_name ; +---------------------------+---------------------+----------------+ | variable_name | ts | variable_value | +---------------------------+---------------------+----------------+ | auto_increment_increment | 2024-03-09 22:10:42 | 1 | | auto_increment_offset | 2024-03-09 22:10:42 | 1 | | read_only | 2024-03-09 22:10:42 | OFF | | slave_parallel_max_queued | 2024-03-09 22:10:42 | 131072 | | slave_parallel_threads | 2024-03-09 22:10:42 | 0 | | slave_parallel_workers | 2024-03-09 22:10:42 | 0 | | slave_skip_errors | 2024-03-09 22:10:42 | OFF | | system_time_zone | 2024-03-09 22:10:42 | CET | | read_only | 2024-03-27 09:42:50 | ON | | slave_skip_errors | 2024-03-27 12:33:13 | 1032 | | slave_skip_errors | 2024-03-27 12:35:13 | OFF | | slave_skip_errors | 2024-03-27 12:42:13 | 1032 | | slave_skip_errors | 2024-03-27 12:50:13 | OFF | | slave_parallel_threads | 2024-04-02 10:17:32 | 8 | | slave_parallel_workers | 2024-04-02 10:17:32 | 8 | | slave_parallel_max_queued | 2024-04-02 10:22:32 | 1048576 | | slave_parallel_max_queued | 2024-04-02 10:23:32 | 4194304 | | slave_parallel_max_queued | 2024-04-02 10:25:32 | 16777216 | | slave_parallel_threads | 2024-04-02 10:25:32 | 16 | | slave_parallel_workers | 2024-04-02 10:25:32 | 16 | | slave_parallel_threads | 2024-04-02 10:28:32 | 32 | | slave_parallel_workers | 2024-04-02 10:28:32 | 32 | | auto_increment_increment | 2024-04-02 10:39:32 | 2 | | auto_increment_offset | 2024-04-02 10:39:32 | 2 | | slave_parallel_max_queued | 2024-04-02 10:57:32 | 131072 | | slave_parallel_threads | 2024-04-02 10:57:32 | 0 | | slave_parallel_workers | 2024-04-02 10:57:32 | 0 | | system_time_zone | 2024-04-02 10:57:32 | CEST | | slave_parallel_max_queued | 2024-04-16 14:06:32 | 16777216 | | slave_parallel_threads | 2024-04-16 14:06:32 | 8 | | slave_parallel_workers | 2024-04-16 14:06:32 | 8 | | slave_parallel_max_queued | 2024-04-16 14:26:32 | 131072 | | slave_parallel_threads | 2024-04-16 14:26:32 | 0 | | slave_parallel_workers | 2024-04-16 14:26:32 | 0 | | slave_parallel_max_queued | 2024-04-17 09:03:32 | 16777216 | | slave_parallel_threads | 2024-04-17 09:03:32 | 16 | | slave_parallel_workers | 2024-04-17 09:03:32 | 16 | | slave_parallel_max_queued | 2024-04-24 08:26:32 | 131072 | | slave_parallel_threads | 2024-04-24 08:26:32 | 0 | | slave_parallel_workers | 2024-04-24 08:26:32 | 0 | | read_only | 2024-04-24 08:42:32 | OFF | +---------------------------+---------------------+----------------+

Here you can see very precisely when and what was done to the database:

On 9 March, dbstat was installed for the first time.
Then on 27 March (before Easter) there seem to have been problems with the replication (here the new version of dbstat was installed, which allows simultaneous collection on master and slave. This led to replication errors, which were partially rectified).
On 2 April (after Easter) we then tried to catch up with parallel replication. You can also see that AUTO_INCREMENT_OFFSET and AUTO_INCREMENT_INCREMENT have been changed. Here we have corrected an error in the database configuration...
You can also see that the time zone has changed from CET to CEST (summer time!) Why only on 2 April is not entirely clear to me. (Maybe because it came via replication?)
Then on 16 and 17 April we tried to reproduce a "bug" in the parallel replication. Apparently we did not reset the value. Because only after the restart on 24 April (usual fortnightly maintenance window) was the value reset again.
On 24 April, you can also see that the database has now assumed the role of the active master (read_only = off). A gracefull switchover has therefore taken place...

Conclusion: A very useful feature to see when something has been changed. Although I have followed all these operations closely, I am still amazed at the informative value of this feature. I would like to see it installed in all databases...

Metadata Lock and InnoDB Transaction Lock

Unfortunately, due to the low traffic on our databases, we do not see too much exciting stuff here.

Here are the metadata locks that we have "caught" on the master in the last 24 hours:

+---------------+---------------------+--------+-----------------+--------------+---------------+-----------------+----------------------------------------------------------------------+ | connection_id | ts | user | host | table_schema | table_name | state | SUBSTR(REGEXP_REPLACE(REPLACE(query, "\n", ' '), '\ +', ' '), 1, 64) | +---------------+---------------------+--------+-----------------+--------------+---------------+-----------------+----------------------------------------------------------------------+ | 18 | 2024-04-23 14:16:47 | zabbix | localhost:51252 | zabbix | triggers | Writing to net | select triggerid,description,expression,error,priority,type,valu | | 1325025 | 2024-04-23 16:01:47 | zabbix | localhost:50150 | | | init for update | delete from history_text where itemid=85477 and clock<1678167661 | | 1325025 | 2024-04-23 16:01:47 | zabbix | localhost:50150 | zabbix | history_text | init for update | delete from history_text where itemid=85477 and clock<1678167661 | | 1365229 | 2024-04-24 02:13:47 | root | localhost:38096 | dbstat | global_status | Writing to net | SELECT /*!40001 SQL_NO_CACHE */ `machine_name`, `variable_name`, | | 18 | 2024-04-24 03:10:47 | zabbix | localhost:51252 | zabbix | item_tag | Writing to net | select itemtagid,itemid,tag,value from item_tag | | 1368524 | 2024-04-24 04:41:47 | zabbix | localhost:38112 | | | | NULL | | 1368524 | 2024-04-24 04:41:47 | zabbix | localhost:38112 | zabbix | history_uint | | NULL | | 18 | 2024-04-24 05:46:47 | zabbix | localhost:51252 | zabbix | item_tag | Sending data | select itemtagid,itemid,tag,value from item_tag | +---------------+---------------------+--------+-----------------+--------------+---------------+-----------------+----------------------------------------------------------------------+

We have not found any InnoDB locks in the last 24 hours for 7 days in a row.

It would be interesting to see a system where more is happening...

Global status

If a normal database monitoring such as the FromDual Performance Monitor for MariaDB and MySQL (fpmmm) with Zabbix is used, this feature is not absolutely necessary. However, most of our customers do not have any useful monitoring in use. Therefore, this feature would be very useful for post-mortem analyses...

For example InnoDB Row Lock Waits, minute-granular over the last 30 days (analogue to sar from sysstat):

Here you can see that the database was restarted on 10 April between 08:37 and 08:41. You could also find this out in another way, but unfortunately this is often not possible for various reasons (error log rotated away, etc.).

The trend break around 2 April is also interesting. At this time we were experimenting with parallel replication. It should not have been a failover (see GLOBAL VARIABLES, above).

Although parallel replication was later deactivated again, there were more locks. A similar situation around the 16th/17th of April, here too we played around with parallel replication, which seems to have had an effect on the locking behaviour.

Even with this feature, there are many ways to analyse the database. Unfortunately, our database is relatively boring: Mainly monotonous traffic (which is plentiful due to the monitoring) and very little exceptional traffic.

Remark: This text has been translated with the assistance of DeepL.

Taxonomy upgrade extras: performance monitoring performance monitoring metadata lock locking performance_schema

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

dbstat for MariaDB after one month of productive use

Shinguz - Fri, 2024-04-26 14:13

Table of contents

Review
One month later
Size of the tables
Process list
Global variables
Metadata Lock and InnoDB Transaction Lock
Global status

Review

After we introduced dbstat for MariaDB (and MySQL) a good 5 weeks ago, we naturally also rolled it out on our systems to test the behaviour in daily use (eat your own dog food).

This went quite well until we came up with the idea of activating dbstat on the passive dbstat node on our MariaDB active/passive master/master replication cluster (a similar situation would also occur with a Galera cluster). We realised that the design of dbstat still had potential. After this problem was fixed (v0.0.2 and v0.0.3) and the problem of how to activate events on master AND slave was solved (MDEV-33782: Event is always disabled on slave), everything seemed fine at first glance. Unfortunately, we did not realise that the data also had to be adjusted. As a result, our replication came to a complete stop over the Easter holidays, which then led to another problem when catching up (MDEV-33923: MariaDB parallel replication causes Foreign Key errors).

After this minor incident was also resolved, dbstat has been running flawlessly on our MariaDB master/master replication cluster ever since... The product dbstat is open source (GPLv2) and can be downloaded from GitHub.

One month later

Databases should NOT grow over time but only over the number of {customers, products, etc.} once the desired equilibrium (steady state) is reached. In our dbstat installation, we have set this equilibrium state to 30 days. So it is now time that the size of dbstat stabilises and the database stops growing...

It would also be interesting to understand what practical use dbstat has. That is why we have now set to work and are trying to analyse the results of dbstat.

Here is an overview of the 11 current running database events:

Firstly, the growth of dbstat itself is interesting. But of course this evaluation can also be carried out for any other database, table or catalogue (coming in MariaDB 11.7?):

SQL> SET SESSION sql_mode='STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,only_full_group_by'; SQL> SET @machine_name = @@hostname; SQL> SELECT `table_schema`, SUBSTR(`ts`, 1, 10) AS date , ROUND(SUM(`data_length`)/1024/1024, 1) AS data_mb , ROUND(SUM(`index_length`)/1024/1024, 1) AS index_mb , ROUND(SUM(`data_free`)/1024/1024, 1) AS free_mb , ROUND((SUM(`data_length`) + SUM(`index_length`) + SUM(`data_free`))/1024/1024, 1) AS total_mb , ROUND(SUM(`table_rows`)/1000/1000, 1) AS rows_m FROM `table_size` WHERE `machine_name` = @machine_name AND `table_catalog` = 'def' AND `table_schema` = 'dbstat' GROUP BY `table_catalog`, `table_schema`, `date` ORDER BY `table_catalog`, `table_schema`, `date` ASC ; +--------------+------------+---------+----------+---------+----------+--------+ | table_schema | date | data_mb | index_mb | free_mb | total_mb | rows_m | +--------------+------------+---------+----------+---------+----------+--------+ | dbstat | 2024-03-26 | 762.8 | 1128.6 | 18.0 | 1909.4 | 10.9 | | dbstat | 2024-03-27 | 835.8 | 1241.6 | 17.0 | 2094.4 | 11.1 | | dbstat | 2024-03-28 | 837.8 | 1241.6 | 14.0 | 2093.4 | 11.8 | | dbstat | 2024-03-29 | 960.7 | 1443.6 | 18.0 | 2422.4 | 14.2 | | dbstat | 2024-03-30 | 960.7 | 1443.6 | 17.0 | 2421.4 | 15.0 | | dbstat | 2024-03-31 | 1057.7 | 1604.6 | 20.0 | 2682.4 | 16.9 | | dbstat | 2024-04-01 | 1057.7 | 1602.6 | 21.0 | 2681.4 | 17.6 | | dbstat | 2024-04-02 | 1172.7 | 1797.6 | 22.0 | 2992.3 | 17.8 | | dbstat | 2024-04-03 | 1442.8 | 2333.7 | 12.0 | 3788.5 | 22.8 | | dbstat | 2024-04-04 | 1649.8 | 2723.7 | 13.0 | 4386.5 | 24.4 | | dbstat | 2024-04-05 | 1649.8 | 2722.7 | 14.0 | 4386.5 | 26.0 | | dbstat | 2024-04-06 | 1821.8 | 3034.8 | 13.0 | 4869.6 | 24.6 | | dbstat | 2024-04-07 | 1821.8 | 3034.8 | 14.0 | 4870.6 | 26.2 | | dbstat | 2024-04-08 | 1989.9 | 3344.8 | 12.0 | 5346.6 | 29.9 | | dbstat | 2024-04-09 | 1990.9 | 3343.8 | 14.0 | 5348.6 | 31.5 | | dbstat | 2024-04-10 | 2193.9 | 3712.8 | 13.0 | 5919.7 | 31.6 | | dbstat | 2024-04-11 | 2193.9 | 3712.8 | 15.0 | 5921.7 | 31.1 | | dbstat | 2024-04-12 | 2405.8 | 4119.1 | 12.0 | 6537.0 | 34.9 | | dbstat | 2024-04-13 | 2405.8 | 4119.1 | 14.0 | 6538.9 | 35.7 | | dbstat | 2024-04-14 | 2480.8 | 4278.9 | 15.0 | 6774.8 | 36.2 | | dbstat | 2024-04-15 | 2560.8 | 4443.7 | 12.0 | 7016.5 | 37.5 | | dbstat | 2024-04-16 | 2560.8 | 4443.7 | 12.0 | 7016.5 | 38.2 | | dbstat | 2024-04-17 | 2640.8 | 4610.6 | 18.0 | 7269.4 | 38.5 | | dbstat | 2024-04-18 | 2640.9 | 4611.6 | 14.0 | 7266.5 | 39.7 | | dbstat | 2024-04-19 | 2743.9 | 4826.5 | 14.0 | 7584.3 | 36.9 | | dbstat | 2024-04-20 | 2826.9 | 4995.5 | 14.0 | 7836.4 | 38.3 | | dbstat | 2024-04-21 | 2830.9 | 4997.4 | 18.0 | 7846.3 | 39.2 | | dbstat | 2024-04-22 | 2919.9 | 5177.4 | 14.0 | 8111.3 | 43.2 | | dbstat | 2024-04-23 | 2923.0 | 5177.3 | 16.0 | 8116.3 | 44.1 | | dbstat | 2024-04-24 | 3020.0 | 5376.3 | 16.0 | 8412.3 | 41.0 | | dbstat | 2024-04-25 | 3024.0 | 5377.3 | 17.0 | 8418.3 | 40.9 | +--------------+------------+---------+----------+---------+----------+--------+

If you take the disc space in the O/S for comparison:

# du -shc *.ibd 8.6G global_status.ibd 308K global_variables.ibd 692K metadata_lock.ibd 97M processlist.ibd 18M table_size.ibd 212K trx_and_lck.ibd 8.7G total

you can see that the values from the database are approximately correct (5% error)...

Important: The database dbstat reaches a size of approx. 9 Gbyte after approx. one month on a not particularly large database system.

You can also see that the size of the database is only just stabilising:

If you want to know more precisely which tables are responsible for which part of the data volume, you can also zoom in or drill down into the data:

SQL> SELECT `table_name`, SUBSTR(`ts`, 1, 10) AS date , ROUND(`data_length`/1024/1024, 1) AS data_mb , ROUND(`index_length`/1024/1024, 1) AS index_mb , ROUND(`data_free`/1024/1024, 1) AS free_mb , ROUND((`data_length` + `index_length` + `data_free`)/1024/1024, 1) AS total_mb , ROUND((`data_length` + `index_length` + `data_free`)/1024/1024/8418.26*100, 1) AS pct , ROUND(`table_rows`/1000/1000, 1) AS rows_m FROM `table_size` WHERE `machine_name` = @machine_name AND `table_catalog` = 'def' AND `table_schema` = 'dbstat' AND SUBSTR(`ts`, 1, 10) = CURRENT_DATE() ORDER BY rows_m DESC ; +------------------+------------+---------+----------+---------+----------+------+--------+ | table_name | date | data_mb | index_mb | free_mb | total_mb | pct | rows_m | +------------------+------------+---------+----------+---------+----------+------+--------+ | global_status | 2024-04-25 | 2949.9 | 5356.9 | 5.0 | 8311.8 | 98.7 | 40.4 | | processlist | 2024-04-25 | 68.2 | 17.1 | 7.0 | 92.2 | 1.1 | 0.4 | | global_variables | 2024-04-25 | 0.1 | 0.1 | 0.0 | 0.2 | 0.0 | 0.0 | | metadata_lock | 2024-04-25 | 0.4 | 0.2 | 0.0 | 0.6 | 0.0 | 0.0 | | table_size | 2024-04-25 | 5.4 | 3.1 | 5.0 | 13.5 | 0.2 | 0.0 | | trx_and_lck | 2024-04-25 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | +------------------+------------+---------+----------+---------+----------+------+--------+

Note: Please excuse the non-use of the window function!

The only real driver for the data volume of this database is the global_status table. This is to be expected (see: see quantity structure of dbstat).

SQL> SELECT SUBSTR(ts, 1, 10) AS date, table_rows/1000/1000 AS k_rows , ROUND(data_length/1024/1024, 1) AS data_mb, ROUND(index_length/1024/1024, 1) AS index_mb, ROUND(data_free/1024/1024, 1) AS free_mb , ROUND((data_length + index_length + data_free)/1024/1024, 1) AS total_mb FROM table_size WHERE `machine_name` = @machine_name AND `table_catalog` = 'def' AND `table_schema` = 'dbstat' AND table_name = 'global_status' AND ts > DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY) ; +------------+-------------+---------+----------+---------+----------+ | date | k_rows | data_mb | index_mb | free_mb | total_mb | +------------+-------------+---------+----------+---------+----------+ | 2024-04-15 | 37.13876300 | 2512.9 | 4433.0 | 4.0 | 6949.9 | | 2024-04-16 | 37.94217200 | 2512.9 | 4433.0 | 4.0 | 6949.9 | + 0M | 2024-04-17 | 38.19867500 | 2592.9 | 4600.0 | 7.0 | 7199.9 | + 250M | 2024-04-18 | 39.39108500 | 2592.9 | 4600.0 | 5.0 | 7197.9 | - 2M | 2024-04-19 | 36.52539600 | 2691.9 | 4813.0 | 5.0 | 7509.8 | + 312M | 2024-04-20 | 37.99073500 | 2770.9 | 4980.9 | 6.0 | 7757.8 | + 248M | 2024-04-21 | 38.79420200 | 2770.9 | 4980.9 | 7.0 | 7758.8 | + 1M | 2024-04-22 | 42.82606200 | 2855.9 | 5158.9 | 6.0 | 8020.8 | + 263M | 2024-04-23 | 43.62953000 | 2855.9 | 5158.9 | 7.0 | 8021.8 | + 1M | 2024-04-24 | 40.54342200 | 2949.9 | 5356.9 | 7.0 | 8313.8 | + 292M | 2024-04-25 | 40.43067700 | 2949.9 | 5356.9 | 5.0 | 8311.8 | - 2M +------------+-------------+---------+----------+---------+----------+

Note: Sorry, I should really familiarise myself with the window functions...

If we analyse the data a bit more closely, we see that the number of rows has slowly stabilised over the last 4 days (note: table_rows is calculated (from the number of blocks and the average row length?) and is not an exact value), but the "amount of data" has continued to increase until yesterday, which is probably due to the fragmentation of the tables and indexes...

The primary key of the global_status table was chosen to optimise the localisation of the data:

PRIMARY KEY (`machine_name`,`variable_name`,`ts`),

The situation should calm down in the next few days. In 2 to 4 weeks we will have to check the situation again.

Summary: I would say that this feature fulfils the requirements and helps to understand the data growth.

List of processes

Since we do not have any serious load issues in our databases, this feature is not that interesting in our case. For example, we can see what a (persistent) connection has done:

SQL> SELECT connection_id, ts, command, time, state, SUBSTR(REGEXP_REPLACE(REPLACE(query, "\n", ' '), '\ +', ' '), 1, 64) FROM processlist WHERE machine_name = @machine_name AND command != 'Sleep' AND connection_id = @connection_id AND state NOT IN ( 'Waiting for next activation' , 'Master has sent all binlog to slave; waiting for more updates' , 'Waiting for master to send event' , 'Slave has read all relay log; waiting for more updates' ) ORDER BY ts ASC ; +---------------+---------------------+---------+-------+----------------+----------------------------------------------------------------------+ | connection_id | ts | command | time | state | SUBSTR(REGEXP_REPLACE(REPLACE(query, "\n", ' '), '\ +', ' '), 1, 64) | +---------------+---------------------+---------+-------+----------------+----------------------------------------------------------------------+ | 18 | 2024-04-17 12:30:28 | Query | 0.029 | Sending data | select pp.item_preprocid,pp.itemid,pp.type,pp.params,pp.step,h.h | | 18 | 2024-04-17 14:58:28 | Query | 0.009 | Writing to net | select itemtagid,itemid,tag,value from item_tag | | 18 | 2024-04-18 06:24:28 | Query | 0.003 | Sending data | select pp.item_preprocid,pp.itemid,pp.type,pp.params,pp.step,h.h | | 18 | 2024-04-18 11:34:28 | Query | 0.030 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-18 16:39:28 | Query | 0.006 | Sending data | select itemid,functionid,name,parameter,triggerid from functions | | 18 | 2024-04-18 19:12:28 | Query | 0.014 | Sending data | select triggerid,description,expression,error,priority,type,valu | | 18 | 2024-04-18 21:49:28 | Query | 0.004 | Writing to net | select i.itemid,i.hostid,i.templateid from items i inner join ho | | 18 | 2024-04-19 00:21:28 | Query | 0.032 | Sending data | select pp.item_preprocid,pp.itemid,pp.type,pp.params,pp.step,h.h | | 18 | 2024-04-19 02:59:28 | Query | 0.017 | Writing to net | select triggerid,description,expression,error,priority,type,valu | | 18 | 2024-04-19 05:39:28 | Query | 0.052 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-19 08:19:28 | Query | 0.000 | Statistics | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-19 13:26:28 | Query | 0.075 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-19 15:57:28 | Query | 0.027 | Writing to net | select itemtagid,itemid,tag,value from item_tag | | 18 | 2024-04-19 18:33:28 | Query | 0.010 | Sending data | select itemtagid,itemid,tag,value from item_tag | | 18 | 2024-04-19 21:10:28 | Query | 0.008 | Sending data | select pp.item_preprocid,pp.itemid,pp.type,pp.params,pp.step,h.h | | 18 | 2024-04-19 23:50:28 | Query | 0.067 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-20 02:28:28 | Query | 0.008 | Sending data | select triggerid,description,expression,error,priority,type,valu | | 18 | 2024-04-20 05:08:28 | Query | 0.052 | Writing to net | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-20 07:44:28 | Query | 0.123 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-20 10:21:28 | Query | 0.144 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-20 12:55:28 | Query | 0.004 | Sending data | select i.itemid,i.hostid,i.templateid from items i where i.flags | | 18 | 2024-04-20 15:35:28 | Query | 0.092 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-20 18:12:28 | Query | 0.041 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-20 20:47:28 | Query | 0.113 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-20 23:25:28 | Query | 0.101 | Writing to net | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-21 02:03:28 | Query | 0.120 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-21 04:42:28 | Query | 0.099 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-21 07:18:28 | Query | 0.015 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-21 12:32:28 | Query | 0.018 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-21 15:06:28 | Query | 0.091 | Writing to net | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-21 20:16:28 | Query | 0.012 | Sending data | select itemtagid,itemid,tag,value from item_tag | | 18 | 2024-04-22 06:44:28 | Query | 0.161 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-22 09:21:28 | Query | 0.000 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-22 11:54:28 | Query | 0.020 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-22 14:23:28 | Query | 0.067 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-22 16:59:28 | Query | 0.128 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-22 22:05:28 | Query | 0.078 | Writing to net | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-23 00:38:28 | Query | 0.084 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-23 03:15:28 | Query | 0.098 | Writing to net | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-23 05:52:28 | Query | 0.000 | starting | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-23 08:27:28 | Query | 0.011 | Sending data | select pp.item_preprocid,pp.itemid,pp.type,pp.params,pp.step,h.h | | 18 | 2024-04-23 10:58:28 | Query | 0.000 | Sending data | select i.itemid,i.hostid,i.templateid from items i inner join ho | | 18 | 2024-04-23 13:31:28 | Query | 0.110 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-23 16:01:28 | Query | 0.023 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-23 18:35:28 | Query | 0.095 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-23 21:10:28 | Query | 0.017 | Writing to net | select itemtagid,itemid,tag,value from item_tag | | 18 | 2024-04-23 23:44:28 | Query | 0.014 | Sending data | select triggerid,description,expression,error,priority,type,valu | | 18 | 2024-04-24 02:21:28 | Query | 0.024 | Sending data | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | | 18 | 2024-04-24 07:33:28 | Query | 0.046 | Writing to net | select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.s | +---------------+---------------------+---------+-------+----------------+----------------------------------------------------------------------+

It is also important that we only see the entries in this report when the thread has done SOMETHING (we have hidden state Sleep). It is also interesting that we do not see this (persistent) connection before 17 April, but at the moment I have NO explanation for this from an operational point of view (restart etc.). Probably the application (Zabbix) has to explain this.

Global variables

The information in the global_variables table is also interesting:

SQL> SELECT variable_name, ts, variable_value FROM global_variables WHERE machine_name = @machine_name AND variable_name IN ( SELECT variable_name FROM global_variables WHERE machine_name = @machine_name GROUP BY variable_name HAVING COUNT(*) > 1 ) ORDER BY ts, variable_name ; +---------------------------+---------------------+----------------+ | variable_name | ts | variable_value | +---------------------------+---------------------+----------------+ | auto_increment_increment | 2024-03-09 22:10:42 | 1 | | auto_increment_offset | 2024-03-09 22:10:42 | 1 | | read_only | 2024-03-09 22:10:42 | OFF | | slave_parallel_max_queued | 2024-03-09 22:10:42 | 131072 | | slave_parallel_threads | 2024-03-09 22:10:42 | 0 | | slave_parallel_workers | 2024-03-09 22:10:42 | 0 | | slave_skip_errors | 2024-03-09 22:10:42 | OFF | | system_time_zone | 2024-03-09 22:10:42 | CET | | read_only | 2024-03-27 09:42:50 | ON | | slave_skip_errors | 2024-03-27 12:33:13 | 1032 | | slave_skip_errors | 2024-03-27 12:35:13 | OFF | | slave_skip_errors | 2024-03-27 12:42:13 | 1032 | | slave_skip_errors | 2024-03-27 12:50:13 | OFF | | slave_parallel_threads | 2024-04-02 10:17:32 | 8 | | slave_parallel_workers | 2024-04-02 10:17:32 | 8 | | slave_parallel_max_queued | 2024-04-02 10:22:32 | 1048576 | | slave_parallel_max_queued | 2024-04-02 10:23:32 | 4194304 | | slave_parallel_max_queued | 2024-04-02 10:25:32 | 16777216 | | slave_parallel_threads | 2024-04-02 10:25:32 | 16 | | slave_parallel_workers | 2024-04-02 10:25:32 | 16 | | slave_parallel_threads | 2024-04-02 10:28:32 | 32 | | slave_parallel_workers | 2024-04-02 10:28:32 | 32 | | auto_increment_increment | 2024-04-02 10:39:32 | 2 | | auto_increment_offset | 2024-04-02 10:39:32 | 2 | | slave_parallel_max_queued | 2024-04-02 10:57:32 | 131072 | | slave_parallel_threads | 2024-04-02 10:57:32 | 0 | | slave_parallel_workers | 2024-04-02 10:57:32 | 0 | | system_time_zone | 2024-04-02 10:57:32 | CEST | | slave_parallel_max_queued | 2024-04-16 14:06:32 | 16777216 | | slave_parallel_threads | 2024-04-16 14:06:32 | 8 | | slave_parallel_workers | 2024-04-16 14:06:32 | 8 | | slave_parallel_max_queued | 2024-04-16 14:26:32 | 131072 | | slave_parallel_threads | 2024-04-16 14:26:32 | 0 | | slave_parallel_workers | 2024-04-16 14:26:32 | 0 | | slave_parallel_max_queued | 2024-04-17 09:03:32 | 16777216 | | slave_parallel_threads | 2024-04-17 09:03:32 | 16 | | slave_parallel_workers | 2024-04-17 09:03:32 | 16 | | slave_parallel_max_queued | 2024-04-24 08:26:32 | 131072 | | slave_parallel_threads | 2024-04-24 08:26:32 | 0 | | slave_parallel_workers | 2024-04-24 08:26:32 | 0 | | read_only | 2024-04-24 08:42:32 | OFF | +---------------------------+---------------------+----------------+

Here you can see very precisely when and what was done to the database:

On 9 March, dbstat was installed for the first time.
Then on 27 March (before Easter) there seem to have been problems with the replication (here the new version of dbstat was installed, which allows simultaneous collection on master and slave. This led to replication errors, which were partially rectified).
On 2 April (after Easter) we then tried to catch up with parallel replication. You can also see that AUTO_INCREMENT_OFFSET and AUTO_INCREMENT_INCREMENT have been changed. Here we have corrected an error in the database configuration...
You can also see that the time zone has changed from CET to CEST (summer time!) Why only on 2 April is not entirely clear to me. (Maybe because it came via replication?)
Then on 16 and 17 April we tried to reproduce a "bug" in the parallel replication. Apparently we did not reset the value. Because only after the restart on 24 April (usual fortnightly maintenance window) was the value reset again.
On 24 April, you can also see that the database has now assumed the role of the active master (read_only = off). A gracefull switchover has therefore taken place...

Conclusion: A very useful feature to see when something has been changed. Although I have followed all these operations closely, I am still amazed at the informative value of this feature. I would like to see it installed in all databases...

Metadata Lock and InnoDB Transaction Lock

Unfortunately, due to the low traffic on our databases, we do not see too much exciting stuff here.

Here are the metadata locks that we have "caught" on the master in the last 24 hours:

+---------------+---------------------+--------+-----------------+--------------+---------------+-----------------+----------------------------------------------------------------------+ | connection_id | ts | user | host | table_schema | table_name | state | SUBSTR(REGEXP_REPLACE(REPLACE(query, "\n", ' '), '\ +', ' '), 1, 64) | +---------------+---------------------+--------+-----------------+--------------+---------------+-----------------+----------------------------------------------------------------------+ | 18 | 2024-04-23 14:16:47 | zabbix | localhost:51252 | zabbix | triggers | Writing to net | select triggerid,description,expression,error,priority,type,valu | | 1325025 | 2024-04-23 16:01:47 | zabbix | localhost:50150 | | | init for update | delete from history_text where itemid=85477 and clock<1678167661 | | 1325025 | 2024-04-23 16:01:47 | zabbix | localhost:50150 | zabbix | history_text | init for update | delete from history_text where itemid=85477 and clock<1678167661 | | 1365229 | 2024-04-24 02:13:47 | root | localhost:38096 | dbstat | global_status | Writing to net | SELECT /*!40001 SQL_NO_CACHE */ `machine_name`, `variable_name`, | | 18 | 2024-04-24 03:10:47 | zabbix | localhost:51252 | zabbix | item_tag | Writing to net | select itemtagid,itemid,tag,value from item_tag | | 1368524 | 2024-04-24 04:41:47 | zabbix | localhost:38112 | | | | NULL | | 1368524 | 2024-04-24 04:41:47 | zabbix | localhost:38112 | zabbix | history_uint | | NULL | | 18 | 2024-04-24 05:46:47 | zabbix | localhost:51252 | zabbix | item_tag | Sending data | select itemtagid,itemid,tag,value from item_tag | +---------------+---------------------+--------+-----------------+--------------+---------------+-----------------+----------------------------------------------------------------------+

We have not found any InnoDB locks in the last 24 hours for 7 days in a row.

It would be interesting to see a system where more is happening...

Global status

If a normal database monitoring such as the FromDual Performance Monitor for MariaDB and MySQL (fpmmm) with Zabbix is used, this feature is not absolutely necessary. However, most of our customers do not have any useful monitoring in use. Therefore, this feature would be very useful for post-mortem analyses...

For example InnoDB Row Lock Waits, minute-granular over the last 30 days (analogue to sar from sysstat):

Here you can see that the database was restarted on 10 April between 08:37 and 08:41. You could also find this out in another way, but unfortunately this is often not possible for various reasons (error log rotated away, etc.).

The trend break around 2 April is also interesting. At this time we were experimenting with parallel replication. It should not have been a failover (see GLOBAL VARIABLES, above).

Although parallel replication was later deactivated again, there were more locks. A similar situation around the 16th/17th of April, here too we played around with parallel replication, which seems to have had an effect on the locking behaviour.

Even with this feature, there are many ways to analyse the database. Unfortunately, our database is relatively boring: Mainly monotonous traffic (which is plentiful due to the monitoring) and very little exceptional traffic.

Remark: This text has been translated with the assistance of DeepL.

Taxonomy upgrade extras: performance monitoring performance monitoring metadata lock locking performance_schema

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

MariaDB's parallel replication to catch up

Shinguz - Tue, 2024-04-09 10:53

Due to an application error, our replication stopped for 5 days (over Easter). After the problem was solved, the replication was supposed to catch up, which turned out to be very slow. All the usual tricks (innodb_flush_log_at_trx_commit, sync_binlog, etc.) had already been exhausted. So we tried our hand at parallel replication of the MariaDB server.

Parallel replication is deactivated by default:

Parallel replication is activated by setting the server variables slave_parallel_threads:

SQL> SET GLOBAL slave_parallel_threads = 8; ERROR 1198 (HY000): This operation cannot be performed as you have a running slave ''; run STOP SLAVE '' first

However, this must be done when replication is stopped:

SQL> STOP SLAVE; SQL> SET GLOBAL slave_parallel_threads = 8; SQL> START SLAVE;

Replication then caught up a little faster. However, as we were impatient, we tried to make it even faster. With the command:

SQL> SHOW SLAVE STATUS\G ... Slave_SQL_Running_State: Waiting for room in worker thread event queue ...

we found the following message. You would also see it using the SHOW PROCESSLIST command:

SQL> SHOW PROCESSLIST; +--------+-------------+- ... -+-----------+------+-----------------------------------------------+- ... -+ | Id | User | ... | Command | Time | State | ... | +--------+-------------+- ... -+-----------+------+-----------------------------------------------+- ... -+ ... ... ... | 212496 | system user | ... | Slave_SQL | 16 | Waiting for room in worker thread event queue | ... | +--------+-------------+- ... -+-----------+------+-----------------------------------------------+- ... -+

According to the documentation, it can help in this case to increase the size of the slave_parallel_max_queued variable slightly (attention: Oom!).

We have played around with the values slave_parallel_threads in the range from 4 to 32 (with 8 vCores) and with slave_parallel_max_queued in the range from 128 kbyte to 32 Mbyte.
Caution: Do not exaggerate: 32 threads x 32 Mbyte = 1 Gbyte RAM (Oom)!

To find out which values are the optimum, you would have to test and measure more extensively. In any case, the replication made up the 5-day backlog after about an hour, towards the end a little more than at the beginning, which was hopefully caused by our configuration adjustments.

Depending on what DML statements are currently running, you can see that all threads can be used or that some threads have to wait for other threads:

Our monitoring also showed us that the CPU load went up, the I/O system got more to do and more rows were modified...

What was also noticeable is that with parallel replication, Foreign Key errors suddenly occurred, a phenomenon that we had not observed before:

FromDual.maas2.prod2 - Warning: InnoDB Foreign Key error detected Trigger: InnoDB Foreign Key error detected Trigger status: PROBLEM Trigger severity: Warning Trigger URL: https://fromdual.com/innodb-foreign-key-error-detected Item values: 1 1. InnoDB new Foreign Key error (FromDual.maas2.prod2:FromDual.MySQL.innodb.ForeignKey_new): 1

With the command SHOW ENGINE INNODB STATUS\G you can inspect these accordingly or view them in the monitoring:

------------------------ LATEST FOREIGN KEY ERROR ------------------------ 2024-04-02 10:36:39 0x7f36088ff640 Transaction: TRANSACTION 7199599266, ACTIVE 0 sec inserting mysql tables in use 1, locked 1 6 lock struct(s), heap size 1128, 3 row lock(s), undo log entries 1 MariaDB thread id 228555, OS thread handle 139870048613952, query id 28453893 Write_rows_log_event::write_row(-1) on table `alerts` insert into alerts (alertid,actionid,eventid,userid,clock,mediatypeid,sendto,subject,message,status,error,esc_step,alerttype,acknowledgeid,parameters) values (203687,4,471733,3,1712044003,1,'xxx@fromdual.com','Zabbix server - High: Too many processes on Zabbix server','Trigger: Too many processes on Zabbix server Trigger status: PROBLEM Trigger severity: High Trigger URL: Item values: 309 1. Number of processes (Zabbix server:proc.num[]): 309',3,'',1,0,null,'{}') Foreign key constraint fails for table `zabbix`.`alerts`: , CONSTRAINT `c_alerts_2` FOREIGN KEY (`eventid`) REFERENCES `events` (`eventid`) ON DELETE CASCADE in parent table, in index alerts_3 tuple: DATA TUPLE: 2 fields; ... But in parent table `zabbix`.`events`, in index PRIMARY, the closest match we can find is record: PHYSICAL RECORD: n_fields 12; compact format; info bits 0 ...
Literature/Sources

Parallel Replication

Taxonomy upgrade extras: replication mariadb parallel multi-threaded