FromDual TechFeed (de)

Partieller physischer Datenbank-Restore für MariaDB und MySQL

Oli Sennhauser - Mon, 2024-07-01 16:52

Um was geht es?

Bei der Beschreibung von Backup- und /Restore-Szenarien wird in der Regel immer von einem vollständigen Backup (full backup) und einem vollständigen Restore (full restore) der Datenbankinstanz (mariadbd/mysqld) ausgegangen. Das bedeutet, dass die gesamte Datenbankinstanz inklusive aller Datenbanken (Schemata) gesichert und wiederhergestellt wird.

In der Praxis sieht die Situation jedoch oft anders aus: Es soll nicht eine ganze Datenbankinstanz wiederhergestellt werden, sondern nur einzelne Datenbanken oder gar einzelne Tabellen, weil nur diese kaputt gegangen sind.

Dies kann in vielen Fällen recht einfach mit den Tools mariadb-dump/mariadb oder mysqldump/mysql (logisches Backup) bewerkstelligt werden. Wenn die Datenbank oder die Tabelle jedoch sehr gross ist, wird die Wiederherstellung nicht in angemessener Zeit (einige Minuten bis wenige Stunden) abgeschlossen sein.

Genau in diesem Fall kommt der sogenannte partielle physische Restore ins Spiel. Partiell steht für eine oder mehrere Tabellen (oder eine ganze Datenbank), physisch für: Es werden nicht einzelne SQL-Anweisungen ausgeführt, sondern die Datenfiles werden physisch zurückgespielt. In diesem Szenario können, eine entsprechende Infrastruktur vorausgesetzt, sehr schnell sehr grosse Datenbestände zurückgespielt werden. Faustregel: Auf fetter Hardware: 1 Tbyte pro Stunde. Auf diese Weise können Datenbank-Restores sehr schnell durchgeführt werden.

MariaDB und MySQL bieten diese Funktionalität bereits von Haus aus an. Für einzelne Tabellen ist der Mechanismus einigermassen praktikabel (siehe Restore partial Backup). Für ganze Datenbanken mit möglicherweise Dutzenden oder Hunderten von Tabellen ist der Bord-Mechanismus jedoch sehr umständlich und fehleranfällig.

Anwendungsfall

Genau hier kommt die neue Funktionalität des FromDual Backup and Recovery Managers (brman) v2.3.0 ins Spiel: Er vereinfacht den partiellen physischen Datenbank-Restore erheblich.

Ein zweites Szenario, in welchem diese neue Funktionalität ebenfalls genutzt werden kann, ist der Umzug einer grossen Datenbank von einer Datenbankinstanz in eine andere Datenbankinstanz (z. B. von Dev nach Prod).

Vorbereitungen für den partiellen physischen Datenbank-Restore

Um eine Datenbank wiederherstellen zu können, muss natürlich zunächst ein sauberes Backup vorliegen. Dieses kann entweder mit dem FromDual Backup Manager (bman) erstellt werden:

PORT=3306 BACKUPNAME=bck_full_2024-07-01 BACKUPDIR=/tmp/bck ./brman/bin/bman --target=brman:secret@127.0.0.1:${PORT} --type=full --mode=physical --policy=daily --backupdir=${BACKUPDIR} --backup-name=${BACKUPNAME} --no-compress

oder man kann das Backup auch einfach mit den MariaDB- (mariadb-backup) oder MySQL-Bordmitteln (xtrabackup) erstellen:

PORT=3306 BACKUPNAME=bck_full_2024-07-01 BACKUPDIR=/tmp/bck POLICY=daily mariadb-backup --user=brman --password=secret --host=127.0.0.1 --port=${PORT} --backup --target-dir=${BACKUPDIR}/${POLICY}/${BACKUPNAME} mariadb-backup --user=brman --password=secret --host=127.0.0.1 --port=${PORT} --prepare --target-dir=${BACKUPDIR}/${POLICY}/${BACKUPNAME}
Partieller physischer Datenbank-Restore

Um einen partiellen physischen Datenbank-Restore durchzuführen, muss die Datenbank, im Gegensatz zum vollständigen physischen Restore, laufen.

Der partielle physische Datenbank-Restore ist dann einfach:

PORT=3306 DATADIR=/var/lib/mysql BACKUPNAME=bck_full_2024-07-01 BACKUPDIR=/tmp/bck ./brman/bin/rman --target=brman:secret@127.0.0.1:${PORT} --type=schema --mode=physical --policy=daily --schema=test --log=/tmp/rman.log --backupdir=${BACKUPDIR} --datadir=${DATADIR} --backup-name=${BACKUPNAME} ... Start restore at 2024-07-01 16:29:48 Backup with tool mariabackup version 10.11.8 (from path /home/mysql/product/mariadb-10.11/bin/mariabackup). Parent: We are the parent. Our child is: 63712. Waiting for database daemon... Child: We are the child: Starting database daemon... Child: Change ownership of database files (/tmp/bck/daily/bck_full_2024-07-01) to mysql Child: /home/mysql/product/mariadb-10.11/bin/mariadbd --no-defaults --user=mysql --basedir=/home/mysql/product/mariadb-10.11 --datadir=/tmp/bck/daily/bck_full_2024-07-01 --log-error=/tmp/my.err --port=3360 --socket=/tmp/my.sock --lower-case-table-names=0 Parent: Tables not InnoDB or sequences: 0 Parent: Tables with partitions: 0 Parent: Tables with full-text index: 0 Parent: InnoDB table `test` found to restore Parent: Dump database test Parent: /home/mysql/product/mariadb-10.11/bin/mariadb-dump --user=brman --host=127.0.0.1 --port=3360 --routines --events --triggers --no-data --skip-lock-tables --add-drop-database --databases test Parent: Shutdown backup database. Restore empty database test Prepare and export tables: /home/mysql/product/mariadb-10.11/bin/mariabackup --user=brman --host=127.0.0.1 --port=3321 --prepare --export --databases=test --target-dir=/tmp/bck/daily/bck_full_2024-07-01 SET SESSION foreign_key_checks = 0 SET SESSION sql_log_bin = off Restore table test ALTER TABLE `test`.`test` DISCARD TABLESPACE cp /tmp/bck/daily/bck_full_2024-07-01/test/test.cfg /home/mysql/database/mariadb-1011/data/test/test.cfg cp /tmp/bck/daily/bck_full_2024-07-01/test/test.ibd /home/mysql/database/mariadb-1011/data/test/test.ibd chown mysql: /home/mysql/database/mariadb-1011/data/test/test.cfg /home/mysql/database/mariadb-1011/data/test/test.ibd ALTER TABLE `test`.`test` IMPORT TABLESPACE rm /home/mysql/database/mariadb-1011/data/test/test.cfg rm /tmp/bck/daily/bck_full_2024-07-01/test/test.cfg ---------------------------------------- WARNING: You should restart the database now! Otherwise possible future backups may fail. See MDEV-34418 (https://jira.mariadb.org/browse/MDEV-34418). ---------------------------------------- Restore time was: 0d 0h 0' 2" End restore at 2024-07-01 16:29:50 (rc=0)

Bei MariaDB empfiehlt es sich, die Datenbank anschliessend neu zu starten, bis der Bug MDEV-34418: mariadb-backup fails on database which was partially restored with mariadb-backup behoben ist. Für MySQL kann dieser Schritt entfallen.

Einschränkungen

Zur Zeit sind noch folgende Einschränkungen beim partiellen physischen Restore von Datenbanken mit rman zu beachten:

Es können nur ganze Datenbanken zurückgesichert werden. Das zurücksichern von einzelne Tabellen ist derzeit noch nicht implementiert. Verwenden Sie hierfür die Basis-Bordwerkzeuge.
Die Wiederherstellung partitionierter Tabellen ist derzeit noch nicht implementiert. Verwenden Sie dazu die Basis-Bordwerkzeuge für partitionierte Tabellen.
Ein anschliessendes Point-in-Time-Recovery der Datenbank ist noch nicht implementiert und muss manuell durchgeführt werden.
Ein partielles physisches Datenbank-Restore für einen gesamten Galera-Cluster ist derzeit noch nicht implementiert und muss manuell durchgeführt werden. In diesem Fall wird ein Restore auf einen Galera-Knoten und eine anschliessende Synchronisation der anderen Knoten mittels SST empfohlen.
Beim physischen partiellen Datenbank-Restore wird eine Pseudo-Instanz auf den Backup-Dateien gestartet. Diese Pseudoinstanz benötigt einen freien Port 3360.
Die Backup-Dateien müssen bereits in einem konsistenten Zustand vorliegen (--prepare).
Beim partiellen physischen Datenbank-Restore wird ein logisches Backup der Datenbank ohne die Daten auf der Pseudo-Instanz erstellt. Diese Sicherung wird auf die zu reparierende Instanz zurückgespielt. Das bedeutet, dass alle Objekte (Views, Trigger, Functions, Procedures, Events, etc.), die NACH dem vollständigen physischen Backup erstellt wurden, vor dem partiellen physischen Datenbank-Restore gelöscht werden und anschliessend nicht mehr vorhanden sind.
Die ursprüngliche Datenbankinstanz, von der das Backup erstellt wurde, und die Instanz, auf der der Restore durchgeführt wird, müssen die gleiche Einstellung für lower_case_table_names haben.
Sowohl das Backup als auch die Datenbankinstanz und das rman-Werkzeug müssen sich auf derselben Maschine befinden.
Das Backup muss zur Zeit noch in unkomprimierter Form vorliegen.

Literatur

Taxonomy upgrade extras: partial restore Restore database schema

Categories: FromDual all (de), FromDual TechFeed (de)

Verkleinern des InnoDB-System-Tablespaces

Oli Sennhauser - Wed, 2024-06-12 15:27

Ein Feature, das mich im neuen MariaDB 11.4 LTS Release wirklich begeistert hat, ist das Verkleinern bzw. Schrumpfen des System-Tablespaces (ibdata1). Auf dieses Feature habe ich seit ca. 2006 sehnsüchtig gewartet und nun ist es mit MariaDB 11.4 endlich gekommen.
Eigentlich gibt es dieses Feature schon seit dem MariaDB 11.2 IR (Juni 2023).

Leider ist die Ankündigung dieses Features etwas zu kurz gekommen. In den MariaDB Release Notes heisst es lapidar:

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

Aus den MariaDB 11.2.0 Release Notes.

Die Gründe, warum dieses Datei ins Unermessliche wachsen kann, sind eigentlich schon lange bekannt und die Massnahmen dagegen sind auch klar (siehe Literatur). Nur sehen wir immer wieder MariaDB-Anwender draussen im Feld, die das Problem nicht oder zu spät auf dem Schirm hatten und nun mit einer viel zu grossen ibdata1-Datei da stehen...

Wie kann das Problem provoziert werden?

Das Problem kann provoziert werden, indem man eine Tabelle im System-Tablespace anlegt:

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;

und diese dann mit Daten befüllt:

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

Während die Tabelle gefüllt wird, kann man auf dem Dateisystem beobachten, wie die Datei ibdata1 anschwillt:

$ 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

Wenn die Datei ibdata1 gross genug ist, kann man die Tabelle vom System-Tablespace in einen dedizierten Tablespace verschieben:

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

Und man sieht, wie die neue Datei aufgebaut wird:

$ 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

Wir haben jetzt also einmal die Daten aber doppelte so viel Platz verbraucht.

Und wie kann man den System-Tablespace wieder verkleinern?

Diese Information ist leider etwas versteck und muss aus der Dokumentation und den MariaDB Jira Issues (siehe Literatur) zusammengesucht werden:

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

Beim Herunterfahren sieht man die entsprechenden Einträge im 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

Und wenn man sich die Datei ibdata1 danach auf Platte anschaut, ist sie wieder so klein wie zu Beginn des Experiments:

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

Siehe auch:

UNDO logs in InnoDB system tablespace ibdata1
Shrinking InnoDB system tablespace file ibdata1 PoC
Who else is using my memory - File System Cache analysis
InnoDB system tablespace cannot be shrunk (MDEV-14795)
ibdata1 shrinking (MDEV-31462)
InnoDB System Tablespaces - Decreasing the Size

Taxonomy upgrade extras: innodb tablespace ibdata1

Categories: FromDual all (de), FromDual TechFeed (de)

Verkleinern des InnoDB-System-Tablespaces

Oli Sennhauser - Wed, 2024-06-12 15:27

Ein Feature, das mich im neuen MariaDB 11.4 LTS Release wirklich begeistert hat, ist das Verkleinern bzw. Schrumpfen des System-Tablespaces (ibdata1). Auf dieses Feature habe ich seit ca. 2006 sehnsüchtig gewartet und nun ist es mit MariaDB 11.4 endlich gekommen.
Eigentlich gibt es dieses Feature schon seit dem MariaDB 11.2 IR (Juni 2023).

Leider ist die Ankündigung dieses Features etwas zu kurz gekommen. In den MariaDB Release Notes heisst es lapidar:

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

Aus den MariaDB 11.2.0 Release Notes.

Die Gründe, warum dieses Datei ins Unermessliche wachsen kann, sind eigentlich schon lange bekannt und die Massnahmen dagegen sind auch klar (siehe Literatur). Nur sehen wir immer wieder MariaDB-Anwender draussen im Feld, die das Problem nicht oder zu spät auf dem Schirm hatten und nun mit einer viel zu grossen ibdata1-Datei da stehen...

Wie kann das Problem provoziert werden?

Das Problem kann provoziert werden, indem man eine Tabelle im System-Tablespace anlegt:

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;

und diese dann mit Daten befüllt:

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

Während die Tabelle gefüllt wird, kann man auf dem Dateisystem beobachten, wie die Datei ibdata1 anschwillt:

$ 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

Wenn die Datei ibdata1 gross genug ist, kann man die Tabelle vom System-Tablespace in einen dedizierten Tablespace verschieben:

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

Und man sieht, wie die neue Datei aufgebaut wird:

$ 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

Wir haben jetzt also einmal die Daten aber doppelte so viel Platz verbraucht.

Und wie kann man den System-Tablespace wieder verkleinern?

Diese Information ist leider etwas versteck und muss aus der Dokumentation und den MariaDB Jira Issues (siehe Literatur) zusammengesucht werden:

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

Beim Herunterfahren sieht man die entsprechenden Einträge im 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

Und wenn man sich die Datei ibdata1 danach auf Platte anschaut, ist sie wieder so klein wie zu Beginn des Experiments:

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

Siehe auch:

UNDO logs in InnoDB system tablespace ibdata1
Shrinking InnoDB system tablespace file ibdata1 PoC
Who else is using my memory - File System Cache analysis
InnoDB system tablespace cannot be shrunk (MDEV-14795)
ibdata1 shrinking (MDEV-31462)
InnoDB System Tablespaces - Decreasing the Size

Taxonomy upgrade extras: innodb tablespace ibdata1

Categories: FromDual all (de), FromDual TechFeed (de)

dbstat für MariaDB nach einem Monat produktiver Nutzung

Oli Sennhauser - Thu, 2024-04-25 12:39

Inhaltsverzeichnis

Rückblick
Einen Monat später
Grösse der Tabellen
Prozessliste
Globale Variablen
Metadata Lock und InnoDB Transaction Lock
Globaler Status

Rückblick

Nachdem wir vor gut 5 Wochen dbstat für MariaDB (und MySQL) vorgestellt haben, haben wir es natürlich auch auf unseren Systemen ausgerollt um das Verhalten im täglichen Einsatz zu testen (eat your own dog food).

Das ging soweit ganz gut, bis wir auf unserem MariaDB aktiv/passiv Master/Master Replikationscluster auf die Idee kamen, dbstat auch auf dem passiven dbstat Node zu aktivieren (eine ähnliche Situation hätte man auch bei einem Galera Cluster). Dabei stellten wir fest, dass das Design von dbstat noch Potential hatte. Nachdem dieses Problem behoben war (v0.0.2 und v0.0.3) und auch das Problem gelöst war, wie man Events auf Master UND Slave aktivieren kann (MDEV-33782: Event is always disabled on slave), schien auf den ersten Blick alles in Ordnung. Leider haben wir bei der Korrektur nicht bedacht, dass auch die Daten hätten angepasst werden müssen. Dies hatte zur Folge, dass unsere Replikation über die Osterfeiertage zum Stillstand kam, was dann beim Aufholen zu einem weiteren Problem führte (MDEV-33923: MariaDB parallel replication causes Foreign Key errors).

Nachdem auch dieser kleine Zwischenfall behoben war lief dbstat auf unserem Master/Master Replikationscluster seitdem einwandfrei... Das Produkt dbstat ist Open Source (GPLv2) und kann von GitHub heruntergeladen werden.

Einen Monat später

Datenbanken sollten NICHT mit der Zeit wachsen sondern nur mit der Anzahl {Kunden, Produkte, etc.}, sobald das gewünschte Gleichgewicht (steady state) erreicht ist. In unserer dbstat-Installation haben wir diesen Wert auf 30 Tage gesetzt. Es wird also langsam an der Zeit, dass sich die Grösse von dbstat stabilisiert und nicht weiter wächst...

Ausserdem wäre es spannend zu verstehen, welchen praktischen Nutzen dbstat hat. Deshalb haben wir uns jetzt an die Arbeit gemacht und versuchen, die Ergebnisse von dbstat auszuwerten.

Hier zunächst noch einmal ein Überblick über die 11 laufenden Events:

Zunächst ist das Wachstum von dbstat selbst interessant. Aber natürlich kann diese Auswertung auch für jede andere Datenbank, Tabelle oder Catalog (kommt in MariaDB 11.7?) durchgeführt werden:

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 | +--------------+------------+---------+----------+---------+----------+--------+

Nimmt man zum Vergleich den Plattenplatz im O/S:

# 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

sieht man, dass die Werte aus der Datenbank in etwa stimmen (5% Fehler)...

Wichtig: Die Datenbank dbstat erreicht nach ca. einem Monat eine Grösse von ca. 9 Gbyte auf einem nicht besonders grossen Datenbanksystem.

Man sieht auch, dass sich die Grösse der Datenbank gerade erst stabilisiert.

Wenn man genauer wissen will, welche Tabellen für welchen Teil des Datenvolumens verantwortlich sind, kann man auch in die Daten hineinzoomen bzw. hineindrillen (drill down):

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 | +------------------+------------+---------+----------+---------+----------+------+--------+

Anmerkung: Bitte entschuldigen Sie die Nichverwendung der Window-Funktion!

Der einzige wirkliche Treiber für die Datenmenge auf diesem System ist die Tabelle global_status. Dies ist auch zu erwarten (siehe Mengengerüst von 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 +------------+-------------+---------+----------+---------+----------+

Anmerkung: Sorry, ich sollte mich wirklich mit den Window-Funktionen vertraut machen...

Wenn wir die Daten etwas genauer analysieren, sehen wir, dass sich die Anzahl der Rows in den letzten 4 Tagen langsam stabilisiert hat (Achtung: table_rows wird berechnet (aus der Anzahl der Blöcke und der durchschnittlichen Zeilenlänge?) und ist kein exakter Wert), aber die "Datenmenge" hat bis gestern weiter zugenommen, was wahrscheinlich auf das "Zerfleddern" der Tabellen und Indizes zurückzuführen ist...

Der Primärschlüssel der Tabelle global_status wurde gewählt, um die Lokalisierung der Daten zu optimieren:

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

Die Lage sollte sich in den nächsten Tagen beruhigen. In 2 bis 4 Wochen müssen wir die Lage erneut prüfen.

Zusammenfassung: Ich würde sagen, dass dieses Feature die Anforderungen erfüllt und hilft, das Datenwachstum zu verstehen.

Liste der Prozesse

Da wir keine ernsthaften Lastprobleme in unseren Datenbanken haben, ist diese Funktion in unserem Fall nicht so interessant. Wir können zum Beispiel sehen, was eine (persistente) Verbindung gemacht hat:

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 | +---------------+---------------------+---------+-------+----------------+----------------------------------------------------------------------+

Wichtig ist auch, dass wir in dieser Auswertung nur die Einträge sehen, wenn der Thread ETWAS gemacht hat (State Sleep haben wir ausgeblendet). Interessant ist auch, dass wir diese (persistente) Verbindung nicht vor dem 17. April sehen, aber ich habe im Moment KEINE Erklärung dafür aus operativer Sicht (Restart etc.). Wahrscheinlich muss die Anwendung (Zabbix) das erklären.

Globale Variablen

Interessant sind auch die Informationen in der Tabelle global_variables:

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 | +---------------------------+---------------------+----------------+

Hier sieht man sehr genau, wann und was an der Datenbank gemacht wurde:

Am 9. März wurde dbstat zum ersten Mal installiert.
Dann am 27. März (vor Ostern) scheint es dann Probleme mit der Replikation gegeben zu haben (hier wurde die neue Version von dbstat installiert, die das gleichzeitige Sammeln auf Master und Slave erlaubt. Dies führte zu Replikationsfehlern, die teilweise behoben wurden).
Am 2. April (nach Ostern) haben wir dann versucht, den Rückstand mit der parallelen Replikation aufzuholen. Man sieht auch, dass AUTO_INCREMENT_OFFSET und AUTO_INCREMENT_INCREMENT geändert wurden. Hier haben wir einen Fehler in der Datenbankkonfiguration korrigiert...
Ausserdem ist zu sehen, dass die Zeitzone von CET auf CEST geändert hat (Sommerzeit!) Warum erst am 2. April ist mir nicht ganz klar. (Vielleicht weil das über die Replikation gekommen ist?)
Dann haben wir am 16. und 17. April haben wir versucht einen "Bug" in der parallelen Replikation zu reproduzieren. Anscheinend haben wir den Wert nicht zurückgesetzt. Denn erst nach dem Neustart am 24. April (übliches zweiwöchentliches Wartungsfenster) wurde der Wert wieder zurückgesetzt.
Am 24. April sieht man auch, dass die Datenbank jetzt die Rolle des aktiven Masters übernommen hat (read_only = off). Es hat also ein Gracefull-Switchover stattgefunden...

Fazit: Ein sehr nützliches Feature um zu sehen, wann was geändert wurde. Obwohl ich alle diese Operationen genau verfolgt habe, bin ich doch erstaunt über die Aussagekraft dieses Features. Ich würde mir wünschen, dass es in allen Datenbanken installiert wird...

Metadata Lock und InnoDB Transaction Lock

Aufgrund des geringen Traffics auf unseren Datenbanken sehen wir hier leider nicht allzu viel Spannendes.

Hier sind die Metadata Locks, die wir in den den letzten 24 Stunden auf dem Master "erwischt" haben:

+---------------+---------------------+--------+-----------------+--------------+---------------+-----------------+----------------------------------------------------------------------+ | 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 | +---------------+---------------------+--------+-----------------+--------------+---------------+-----------------+----------------------------------------------------------------------+

InnoDB-Locks haben wir in den letzten 24 Stunden repektive 7 Tage keine gefunden.

Es wäre interessant, ein System zu sehen, auf dem mehr passiert...

Globaler Status

Wenn ein normales Datenbankmonitoring wie z.B. der FromDual Performance Monitor für MariaDB und MySQL (fpmmm) mit Zabbix verwendet wird, ist dieses Feature nicht unbedingt notwendig. Die meisten unserer Kunden haben jedoch kein brauchbares Monitoring im Einsatz. Daher wäre dieses Feature sehr nützlich für Post-Morten-Analysen...

Zum Beispiel InnoDB-Lock-Waits, minuten-granular über die letzten 30 Tage (analog zu sar aus sysstat):

Hier sieht man, dass die Datenbank am 10. April zwischen 08:37 und 08:41 neu gestartet wurde. Das könnte man zwar auch anders herausfinden, ist aber leider oft aus verschiedenen Gründen nicht möglich (Error Log wegrotiert, etc.).

Interessant ist auch der Trendbruch um den 2. April. Zu diesem Zeitpunkt haben wir mit der parallelen Replikation rumexperimentiert. Es sollte kein Failover gewesen sein (siehe GLOBAL VARIABLES, weiter oben).

Obwohl die parallele Replikation später wieder deaktiviert wurde, gab es mehr Locks. Eine ähnliche Situation um den 16./17. April auch hier haben wir mit der parallelen Replikation herumgespielt, was sich auf das Locking-Verhalten ausgewirkt zu haben scheint.

Auch mit diesem Feature gibt es viele Möglichkeiten die Datenbank zu untersuchen. Leider ist unsere Datenbank relativ langweilig: Hauptsächlich monotoner Traffic (der durch das Monitoring reichlich vorhanden ist) und aussergwöhnlicher Traffic in sehr geringem Umfang.

Anmerkung: Dieser Text wurde mit der Unterstützung von DeepL optimiert.

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

Categories: FromDual all (de), FromDual TechFeed (de)

dbstat nach einem Monat produktiver Nutzung

Oli Sennhauser - Thu, 2024-04-25 12:39

Inhaltsverzeichnis

Rückblick
Einen Monat später
Grösse der Tabellen
Prozessliste
Globale Variablen
Metadata Lock und InnoDB Transaction Lock
Globaler Status