You are here

FromDual TechFeed (de)

Sharding mit MariaDB MaxScale

Oli Sennhauser - Mon, 2024-03-18 16:08
Inhaltsverzeichnis
Übersicht

Dieses Feature sollte mehr oder weniger mit MariaDB MaxScale 6 (2.6), 6.1 bis 6.4 und 22.08 (6.5), 23.02 (7.0) und 23.08 (7.1) funktionieren. Wir haben es mit der neusten MaxScale Version 23.08.05 getestet, da wir mit einer älteren Version auf Problem gestossen sind.

shell> maxscale --version MaxScale 23.08.5

Als Datenbank Backend (Shards) haben wir MariaDB 10.11 verwendet.

Weniger als ca 2% aller uns bekannten MariaDB Installationen sind das, was wir technische unter Multi-Mandanten-Systeme (multi-tenant systems) verstehen (jeder Mandant (Kunde) in einer eigenen Datenbank (auch Schema genannt)).

Daher wird dieses MariaDB MaxScale Feature relativ selten genutzt und es besteht die erhöhte Gefahr auf Bugs zu stossen, auf welche vorher noch niemand gestossen ist!

Dieses Feature wird bei MariadDB MaxScale: SchemaRouter genannt und wird immer noch als Beta-Qualität deklariert:

maxctrl> show module schemarouter ┌─────────────┬────────────────────────────────────────────────┐ │ Module │ schemarouter │ ├─────────────┼────────────────────────────────────────────────┤ │ Type │ Router │ ├─────────────┼────────────────────────────────────────────────┤ │ Version │ V1.0.0 │ ├─────────────┼────────────────────────────────────────────────┤ │ Maturity │ Beta │ ├─────────────┼────────────────────────────────────────────────┤ │ Description │ A database sharding router for simple sharding │ ├─────────────┼────────────────────────────────────────────────┤ │ ...

Die Ziel-Topologie soll wie folgt aussehen: Jeder Kunde (Mandant, Tenant) liegt in einer eigenen Datenbank (= Schema). Die Datenbanken sind über mehrere MariaDB Instanzen (Shards) verteilt. Damit die Applikation transparent auf die Datenbank zugreifen kann wird ein Pärchen MaxScale Load Balancer davor geschaltet, welches weiss, wo der Kunde liegt und den Traffic entsprechend an das Shard weiterleitet. Damit die MaxScale Load Balancer hochverfügbar ausgelegt sind, wird noch eine virtuelle IP (VIP) z.B. mittels Keepalived vorgeschaltet. Wem das noch zu einfach ist, der kann jedes einzelne Shard noch als Master/Slave- oder Galera Cluster-Konstrukt auslegen...

Vorbereitung der Shards (MariaDB Datenbank Instanzen)

Als erstes hatten wir bei diesem PoC Problem mit der test Datenbank. Durch das Löschen der test Datenbank auf allen Shards ist das Problem verschwunden. Alternativ dazu kann man mariadb-secure-installation ausführen, was man auf Produktionsystemen sowieso machen sollte, oder man nutzt die MaxScale Konfigurationsparameter: ignore_tables oder ignore_tables_regex um gleiche Tabellen in unterschiedlichen Shards zu erlauben.

Siehe auch: MaxScale Router Parameters.

Testdaten erstellen

Damit wir was zum Spielen haben, haben wir uns Testdaten erstellt:

-- Auf Shard 1: 2 Kunden SQL> CREATE DATABASE customer_0010; SQL> CREATE TABLE customer_0010.address (id INT UNSIGNED, name VARCHAR(255)); SQL> CREATE TABLE customer_0010.sales (id INT UNSIGNED, product VARCHAR(255), sales TINYINT, amount DECIMAL(6, 2), total_amount DECIMAL(6, 2)); SQL> INSERT INTO customer_0010.address VALUES (1, 'Customer 10 GmbH'); SQL> INSERT INTO customer_0010.sales VALUES (1, 'Apples', 5, 1.2, 6), (2, 'Pears', 2, 0.9, 1.8), (3, 'Bread', 1, 2.5, 2.5); SQL> CREATE DATABASE customer_0011; SQL> CREATE TABLE customer_0011.address (id INT UNSIGNED, name VARCHAR(255)); SQL> CREATE TABLE customer_0011.sales (id INT UNSIGNED, product VARCHAR(255), sales TINYINT, amount DECIMAL(6, 2), total_amount DECIMAL(6, 2)); SQL> INSERT INTO customer_0011.address VALUES (1, 'Customer 11 SE'); SQL> INSERT INTO customer_0011.sales VALUES (1, 'Oranges', 2, 1.7, 3.4), (2, 'Salat', 5, 1.2, 6); -- Auf Shard 2: 3 Kunden SQL> CREATE DATABASE customer_0020; SQL> CREATE TABLE customer_0020.address (id INT UNSIGNED, name VARCHAR(255)); SQL> CREATE TABLE customer_0020.sales (id INT UNSIGNED, product VARCHAR(255), sales TINYINT, amount DECIMAL(6, 2), total_amount DECIMAL(6, 2)); SQL> INSERT INTO customer_0020.address VALUES (1, 'Customer 20 AG'); SQL> INSERT INTO customer_0020.sales VALUES (1, 'Oranges', 2, 1.7, 3.4), (2, 'Salat', 5, 1.2, 6); SQL> CREATE DATABASE customer_0021; SQL> CREATE TABLE customer_0021.address (id INT UNSIGNED, name VARCHAR(255)); SQL> CREATE TABLE customer_0021.sales (id INT UNSIGNED, product VARCHAR(255), sales TINYINT, amount DECIMAL(6, 2), total_amount DECIMAL(6, 2)); SQL> INSERT INTO customer_0021.address VALUES (1, 'Customer 21 GmbH'); SQL> INSERT INTO customer_0021.sales VALUES (1, 'Oranges', 2, 1.7, 3.4), (2, 'Salat', 5, 1.2, 6); SQL> CREATE DATABASE customer_0022; SQL> CREATE TABLE customer_0022.address (id INT UNSIGNED, name VARCHAR(255)); SQL> CREATE TABLE customer_0022.sales (id INT UNSIGNED, product VARCHAR(255), sales TINYINT, amount DECIMAL(6, 2), total_amount DECIMAL(6, 2)); SQL> INSERT INTO customer_0022.address VALUES (1, 'Customer 22 Gebr.'); SQL> INSERT INTO customer_0022.sales VALUES (1, 'Oranges', 2, 1.7, 3.4), (2, 'Salat', 5, 1.2, 6); -- Auf Shard 3: 1 Kunde SQL> CREATE DATABASE customer_0030; SQL> CREATE TABLE customer_0030.address (id INT UNSIGNED, name VARCHAR(255)); SQL> CREATE TABLE customer_0030.sales (id INT UNSIGNED, product VARCHAR(255), sales TINYINT, amount DECIMAL(6, 2), total_amount DECIMAL(6, 2)); SQL> INSERT INTO customer_0030.address VALUES (1, 'Customer 30 GmbH'); SQL> INSERT INTO customer_0030.sales VALUES (1, 'Pickles', 2, 2.2, 4.4), (2, 'Salat', 1, 3.1, 3.1), (3, 'Pudding', 5, 2.2, 11.0), (4, 'Asparagus', 12, .3, 3.6);
Rollen und User erstellen

Da in einem geshardeten System, im Unterschied zum Beispiel zu einem Galera Cluster, die einzelnen Datenbank-Instanzen nichts voneinander wissen und nicht miteinander kommunizieren, müssen wir die Rollen und User bzw. Accounts jeweils auf JEDEM Shard einzeln anlegen.

MariaDB MaxScale braucht jeweils für den SchemaRouter Service und den Monitor einen User (auf jedem Shard).

Der Monitor User ist, wie der Name sagt fürs Monitoring zuständig und der SchemaRouter Service User um die User Account Informationen aus den Sharding-Backends einzusammeln und die Queries an das richtige Shard weiterzuleiten.

Da in einem redundanten System typischerweise mit mindestens zwei MaxScale Routern gearbeitet wird und wir dem auseinanderlaufen der Privilegien der Accounts vorbeugen wollten arbeiten wir mit Rollen sowohl für die MaxScale User als auch die applikatorischen User.

MaxScale Monitor User SQL> CREATE ROLE maxscale_monitor_role; SQL> GRANT SELECT ON mysql.user TO 'maxscale_monitor_role'; SQL> GRANT REPLICATION CLIENT ON *.* TO 'maxscale_monitor_role'; SQL> GRANT SLAVE MONITOR ON *.* TO 'maxscale_monitor_role'; SQL> GRANT FILE ON *.* TO 'maxscale_monitor_role'; SQL> GRANT CONNECTION ADMIN ON *.* TO 'maxscale_monitor_role'; SQL> SHOW GRANTS FOR maxscale_monitor_role; +-----------------------------------------------------------------------------------------------+ | Grants for maxscale_monitor_role | +-----------------------------------------------------------------------------------------------+ | GRANT FILE, BINLOG MONITOR, CONNECTION ADMIN, SLAVE MONITOR ON *.* TO `maxscale_monitor_role` | | GRANT SELECT ON `mysql`.`user` TO `maxscale_monitor_role` | +-----------------------------------------------------------------------------------------------+ SQL> CREATE USER maxscale_monitor@'10.139.158.210' IDENTIFIED BY 'secret'; SQL> CREATE USER maxscale_monitor@'10.139.158.211' IDENTIFIED BY 'secret'; SQL> GRANT maxscale_monitor_role TO maxscale_monitor@'10.139.158.210'; SQL> GRANT maxscale_monitor_role TO maxscale_monitor@'10.139.158.211'; SQL> SET DEFAULT ROLE maxscale_monitor_role FOR maxscale_monitor@'10.139.158.210'; SQL> SET DEFAULT ROLE maxscale_monitor_role FOR maxscale_monitor@'10.139.158.211'; SQL> SELECT user, host, is_role, default_role FROM mysql.user WHERE user LIKE 'maxscale_monitor%'; +-----------------------+----------------+---------+-----------------------+ | User | Host | is_role | default_role | +-----------------------+----------------+---------+-----------------------+ | maxscale_monitor_role | | Y | | | maxscale_monitor | 10.139.158.210 | N | maxscale_monitor_role | | maxscale_monitor | 10.139.158.211 | N | maxscale_monitor_role | +-----------------------+----------------+---------+-----------------------+ SQL> SHOW GRANTS FOR maxscale_monitor@'10.139.158.211'; +------------------------------------------------------------------------------------------------------------------------------+ | Grants for maxscale_monitor@10.139.158.211 | +------------------------------------------------------------------------------------------------------------------------------+ | GRANT `maxscale_monitor_role` TO `maxscale_monitor`@`10.139.158.211` | | GRANT USAGE ON *.* TO `maxscale_monitor`@`10.139.158.211` IDENTIFIED BY PASSWORD '*14E65567ABDB5135D0CFD9A70B3032C179A49EE7' | | SET DEFAULT ROLE `maxscale_monitor_role` FOR `maxscale_monitor`@`10.139.158.211` | +------------------------------------------------------------------------------------------------------------------------------+
MaxScale Admin User SQL> CREATE ROLE maxscale_admin_role; SQL> GRANT SHOW DATABASES ON *.* TO 'maxscale_admin_role'; SQL> GRANT SELECT ON mysql.user TO 'maxscale_admin_role'; SQL> GRANT SELECT ON mysql.db TO 'maxscale_admin_role'; SQL> GRANT SELECT ON mysql.tables_priv TO 'maxscale_admin_role'; SQL> GRANT SELECT ON mysql.columns_priv TO 'maxscale_admin_role'; SQL> GRANT SELECT ON mysql.proxies_priv TO 'maxscale_admin_role'; SQL> GRANT SELECT ON mysql.roles_mapping TO 'maxscale_admin_role'; SQL> GRANT SELECT ON mysql.procs_priv TO 'maxscale_admin_role'; SQL> SHOW GRANTS FOR maxscale_admin_role; +------------------------------------------------------------------+ | Grants for maxscale_admin_role | +------------------------------------------------------------------+ | GRANT USAGE ON *.* TO `maxscale_admin_role` | | GRANT SELECT ON `mysql`.`user` TO `maxscale_admin_role` | | GRANT SELECT ON `mysql`.`roles_mapping` TO `maxscale_admin_role` | | GRANT SELECT ON `mysql`.`tables_priv` TO `maxscale_admin_role` | | GRANT SELECT ON `mysql`.`procs_priv` TO `maxscale_admin_role` | | GRANT SELECT ON `mysql`.`db` TO `maxscale_admin_role` | | GRANT SELECT ON `mysql`.`columns_priv` TO `maxscale_admin_role` | | GRANT SELECT ON `mysql`.`proxies_priv` TO `maxscale_admin_role` | +------------------------------------------------------------------+ SQL> CREATE USER maxscale_admin@'10.139.158.210' IDENTIFIED BY 'secret'; SQL> CREATE USER maxscale_admin@'10.139.158.211' IDENTIFIED BY 'secret'; SQL> GRANT maxscale_admin_role TO maxscale_admin@'10.139.158.210'; SQL> GRANT maxscale_admin_role TO maxscale_admin@'10.139.158.211'; SQL> SET DEFAULT ROLE maxscale_admin_role FOR maxscale_admin@'10.139.158.210'; SQL> SET DEFAULT ROLE maxscale_admin_role FOR maxscale_admin@'10.139.158.211'; SQL> SELECT user, host, is_role, default_role FROM mysql.user WHERE user LIKE 'maxscale_admin%'; +---------------------+----------------+---------+---------------------+ | User | Host | is_role | default_role | +---------------------+----------------+---------+---------------------+ | maxscale_admin_role | | Y | | | maxscale_admin | 10.139.158.210 | N | maxscale_admin_role | | maxscale_admin | 10.139.158.211 | N | maxscale_admin_role | +---------------------+----------------+---------+---------------------+ SQL> SHOW GRANTS FOR maxscale_admin@'10.139.158.211'; +----------------------------------------------------------------------------------------------------------------------------+ | Grants for maxscale_admin@10.139.158.211 | +----------------------------------------------------------------------------------------------------------------------------+ | GRANT `maxscale_admin_role` TO `maxscale_admin`@`10.139.158.211` | | GRANT USAGE ON *.* TO `maxscale_admin`@`10.139.158.211` IDENTIFIED BY PASSWORD '*14E65567ABDB5135D0CFD9A70B3032C179A49EE7' | | SET DEFAULT ROLE `maxscale_admin_role` FOR `maxscale_admin`@`10.139.158.211` | +----------------------------------------------------------------------------------------------------------------------------+

Siehe dazu auch: SchemaRouter Configuration

Applikationsrolle und Accounts erstellen

Für die Applikation braucht es ebenfalls einen User, den wir hier wie auf jedem Shard wie folgt erstellen:

SQL> CREATE ROLE app_role; SQL> GRANT SELECT, INSERT, UPDATE, DELETE ON `customer_%`.* TO 'app_role'; SQL> GRANT SHOW DATABASES ON *.* TO 'app_role'; SQL> GRANT CREATE, DROP, ALTER ON *.* TO 'app_role'; -- For creating new tenant databases SQL> SHOW GRANTS FOR app_role; +----------------------------------------------------------------------+ | Grants for app_role | +----------------------------------------------------------------------+ | GRANT SHOW DATABASES ON *.* TO `app_role` | | GRANT SELECT, INSERT, UPDATE, DELETE ON `customer_%`.* TO `app_role` | +----------------------------------------------------------------------+ SQL> CREATE USER app@'10.139.158.%' IDENTIFIED BY 'secret'; SQL> GRANT app_role TO app@'10.139.158.%'; SQL> SET DEFAULT ROLE app_role FOR app@'10.139.158.%'; SQL> SELECT user, host, is_role, default_role FROM mysql.user WHERE user LIKE 'app%'; +----------+--------------+---------+--------------+ | User | Host | is_role | default_role | +----------+--------------+---------+--------------+ | app_role | | Y | | | app | 10.139.158.% | N | app_role | +----------+--------------+---------+--------------+ SQL> SHOW GRANTS FOR app@'10.139.158.%'; +---------------------------------------------------------------------------------------------------------------+ | Grants for app@10.139.158.% | +---------------------------------------------------------------------------------------------------------------+ | GRANT `app_role` TO `app`@`10.139.158.%` | | GRANT USAGE ON *.* TO `app`@`10.139.158.%` IDENTIFIED BY PASSWORD '*14E65567ABDB5135D0CFD9A70B3032C179A49EE7' | | SET DEFAULT ROLE `app_role` FOR `app`@`10.139.158.%` | +---------------------------------------------------------------------------------------------------------------+
Proxy Protokoll

Load Balancer und Proxies haben die Eigenschaft, dass sie die IP Adressen der Clients durch ihre eigenen IP Adressen austauschen. Dies führt einerseits dazu, dass man auf der Datenbank nicht mehr sieht, woher der Client ursprünglich kommt, andererseits kann man die Zugriffsberechtigungen nicht mehr auf User und IP vergeben, da immer gegen die IP der Load Balancer geprüft wird.

Diese beiden Probleme können mittels des Proxy Protokolls gelöst werden.

Hierzu müssen einerseits die Datenbank und andererseits die Load Balancer, in diesem Fall der MaxScale, das Proxy Protokoll aktiviert haben.

Auf der Datenbank-Seite aktiviert man das Proxy Protokoll wie folgt:

# # my.cnf # [mariadbd] proxy_protocol_networks = ::1, 10.139.158.0/24, localhost

und auf MaxScale-Seite mit:

# # /etc/maxscale.cnf # [shard<n>] type = server proxy_protocol = true

Überprüfen kann man die beiden Einstellungen mit:

SQL> SHOW GLOBAL VARIABLES LIKE 'proxy%'; +-------------------------+---------------------------------+ | Variable_name | Value | +-------------------------+---------------------------------+ | proxy_protocol_networks | ::1, 10.139.158.0/24, localhost | +-------------------------+---------------------------------+ shell> maxctrl show server shard1 | grep proxy │ │ "proxy_protocol": true, │

Quellen:


MaxScale SchemaRouter Konfiguration

Als nächstes bereiten wir die MaxScale Konfiguration fürs Sharding vor. Die von MariaDB empfohlene Datei ist /etc/maxscale.cnf. Ob es sinnvoller ist eine eigene Konfigurations-Datei unter /etc/maxscale.cnf.d/ zu erstellen oder gar den ganze MaxScale dynamisch zu konfigurieren (/var/lib/maxscale/maxscale.cnf.d/*.cnf), wird sich langfristig zeigen. Siehe auch Warnungen weiter unten. Die Konfigurationsdatei für dieses Sharding PoC sieht wie folgt aus:

# # /etc/maxscale.cnf # [maxscale] threads = auto admin_gui = false [shard1] type = server address = 10.139.158.1 port = 3363 proxy_protocol = true [shard2] type=server address=10.139.158.1 port=3364 proxy_protocol = true [shard3] type = server address = 10.139.158.1 port = 3365 proxy_protocol = true [Sharding-Monitor] type = monitor module = galeramon servers = shard1,shard2,shard3 user = maxscale_monitor password = secret monitor_interval = 1s [Sharded-Service-Listener] type = listener service = Sharded-Service protocol = MariaDBClient port = 3306 [Sharded-Service] type = service router = schemarouter servers = shard1,shard2,shard3 user = maxscale_admin password = secret auth_all_servers = true

Hinweis: Empfehlung des MaxScale Entwicklers: "One workaround might be to actually use galeramon to monitor the nodes instead of mariadbmon."

Starten und Stoppen des MaxScale Load Balancers

Starten und Stoppen von MaxScale erfolgt wie üblich über SystemD:

shell> systemctl restart maxscale shell> systemctl status maxscale ● maxscale.service - MariaDB MaxScale Database Proxy Loaded: loaded (/lib/systemd/system/maxscale.service; enabled; vendor preset: enabled) Drop-In: /run/systemd/system/service.d └─zzz-lxc-service.conf Active: active (running) since Tue 2024-02-27 09:52:57 UTC; 39s ago Process: 187 ExecStart=/usr/bin/maxscale (code=exited, status=0/SUCCESS) Main PID: 188 (maxscale) Tasks: 10 (limit: 18663) Memory: 4.6M CPU: 150ms CGroup: /system.slice/maxscale.service └─188 /usr/bin/maxscale systemd[1]: Starting MariaDB MaxScale Database Proxy... maxscale[188]: Module 'galeramon' loaded from '/usr/lib/x86_64-linux-gnu/maxscale/libgaleramon.so'. maxscale[188]: Module 'schemarouter' loaded from '/usr/lib/x86_64-linux-gnu/maxscale/libschemarouter.so'. maxscale[188]: Using up to 2.3GiB of memory for query classifier cache systemd[1]: Started MariaDB MaxScale Database Proxy.

Falls es Fehler oder Warnungen gegeben hat, kann man diese im MaxScale Error Log sehen:

shell> grep -v notice /var/log/maxscale/maxscale.log 2024-02-13 16:47:22 MariaDB MaxScale is shut down. ---------------------------------------------------- MariaDB MaxScale /var/log/maxscale/maxscale.log Tue Feb 13 16:47:22 2024 ---------------------------------------------------------------------------- 2024-02-27 09:52:56 warning: Discarding journal file '/var/lib/maxscale/Sharding-Monitor_journal.json'. File is for module 'mariadbmon'. Current module is 'galeramon'. 2024-02-27 09:52:56 warning: [galeramon] Invalid 'wsrep_local_index' on server 'shard1': 18446744073709551615
Applikations-Tests Einfache Applikations-Tests shell> mariadb --user=app --password=secret --host=10.139.158.211 --port=3306 --execute='show databases' +--------------------+ | Database | +--------------------+ | customer_0010 | | customer_0011 | | customer_0020 | | customer_0021 | | customer_0022 | | customer_0030 | | information_schema | | mysql | | performance_schema | | sys | +--------------------+
Neuer Befehl show shards shell> mariadb --user=app --password=secret --host=10.139.158.211 --port=3306 customer_0030 --execute='show shards' | grep customer_00.* | sort | column -t customer_0010.address shard1 customer_0010.sales shard1 customer_0010. shard1 customer_0011.address shard1 customer_0011.sales shard1 customer_0011. shard1 customer_0020.address shard2 customer_0020.sales shard2 customer_0020. shard2 customer_0021.address shard2 customer_0021.sales shard2 customer_0021. shard2 customer_0022.address shard2 customer_0022.sales shard2 customer_0022. shard2 customer_0030.address shard3 customer_0030.sales shard3 customer_0030. shard3

Neue Datenbanken werden nicht sofort angezeigt, sondern erst wenn die gecachten Daten upgedatet wurden (refresh_interval (300s / 5 min)).

Siehe hierzu auch: Custom SQL commands

Allgemeinere Test

Zur Erinnerung:

ShardPortCustomerState #13363customer_001<n>Running #23364customer_002<n>Running #33365customer_003<n>Running #43366customer_004<n>Running
shell> mariadb --user=app --password=secret --host=10.139.158.211 --port=3306 --execute='SELECT @@port' +--------+ | @@port | +--------+ | 3363 | +--------+ shell> mariadb --user=app --password=secret --host=10.139.158.211 --port=3306 --database=customer_0010 --execute='SELECT @@port' +--------+ | @@port | +--------+ | 3363 | +--------+ shell> mariadb --user=app --password=secret --host=10.139.158.211 --port=3306 --database=customer_0020 --execute='SELECT @@port' +--------+ | @@port | +--------+ | 3364 | +--------+ shell> mariadb --user=app --password=secret --host=10.139.158.211 --port=3306 --execute='use customer_0020; SELECT @@port' +--------+ | @@port | +--------+ | 3364 | +--------+ shell> mariadb --user=app --password=secret --host=10.139.158.211 --port=3306 customer_0010 --execute='SELECT @@port' +--------+ | @@port | +--------+ | 3363 | +--------+ shell> mariadb --user=app --password=secret --host=10.139.158.211 --port=3306 customer_0020 --execute='SELECT @@port' +--------+ | @@port | +--------+ | 3364 | +--------+ shell> mariadb --user=app --password=secret --host=10.139.158.211 --port=3306 customer_0030 --execute='SELECT @@port' +--------+ | @@port | +--------+ | 3365 | +--------+
Weniger einfache (Backup-) Test shell> mariadb-dump --user=app --password=secret --host=10.139.158.211 --port=3306 --single-transaction customer_0010 > /tmp/customer_0010.sql shell> echo $? 0 shell> mariadb-dump --user=app --password=secret --host=10.139.158.211 --port=3306 --single-transaction customer_0020 > /tmp/customer_0020.sql shell> echo $? 0 shell> mariadb-dump --user=app --password=secret --host=10.139.158.211 --port=3306 --single-transaction customer_0030 > /tmp/customer_0030.sql shell> echo $? 0 shell> mariadb-dump --user=app --password=secret --host=10.139.158.211 --port=3306 --single-transaction --databases customer_0011 > /tmp/customer_0011.sql shell> echo $? 0 shell> mariadb-dump --user=app --password=secret --host=10.139.158.211 --port=3306 --single-transaction --databases customer_0021 > /tmp/customer_0021.sql shell> echo $? 0 shell> mariadb-dump --user=app --password=secret --host=10.139.158.211 --port=3306 --single-transaction --databases customer_0030 > /tmp/customer_0030.sql shell> echo $? 0 shell> ll /tmp/customer_00*sql -rw-rw-r-- 1 oli oli 2738 Mar 18 12:07 /tmp/customer_0010.sql -rw-rw-r-- 1 oli oli 2904 Mar 18 12:08 /tmp/customer_0011.sql -rw-rw-r-- 1 oli oli 2712 Mar 18 12:08 /tmp/customer_0020.sql -rw-rw-r-- 1 oli oli 2906 Mar 18 12:08 /tmp/customer_0021.sql -rw-rw-r-- 1 oli oli 2964 Mar 18 12:08 /tmp/customer_0030.sql shell> tail -n 1 /tmp/customer_*.sql ==> /tmp/customer_0010.sql <== -- Dump completed on 2024-02-13 14:39:21 ==> /tmp/customer_0011.sql <== -- Dump completed on 2024-02-13 14:39:35 ==> /tmp/customer_0020.sql <== -- Dump completed on 2024-02-13 14:40:15 ==> /tmp/customer_0021.sql <== -- Dump completed on 2024-02-13 14:40:42 ==> /tmp/customer_0030.sql <== -- Dump completed on 2024-02-13 14:40:52 shell> cat /tmp/customer_00*sql | grep -A1 -i insert INSERT INTO `address` VALUES (1,'Customer 10 GmbH'); -- INSERT INTO `sales` VALUES (1,'Apples',5,1.20,6.00), -- INSERT INTO `address` VALUES (1,'Customer 11 SE'); -- INSERT INTO `sales` VALUES (1,'Oranges',2,1.70,3.40), -- INSERT INTO `address` VALUES (1,'Customer 20 AG'); -- INSERT INTO `sales` VALUES (1,'Oranges',2,1.70,3.40), -- INSERT INTO `address` VALUES (1,'Customer 21 GmbH'); -- INSERT INTO `sales` VALUES (1,'Oranges',2,1.70,3.40), -- INSERT INTO `address` VALUES (1,'Customer 30 GmbH'); -- INSERT INTO `sales` VALUES (1,'Pickles',2,2.20,4.40),

In MaxScale 23.08.4 war ein ziemlich übler Bug drin: Ein Rückgabewert von 0 aber keine Daten im Backup!!! Siehe dazu auch die Tickets: MXS-4966: mariadb-dump gets an error dumping schemas und MXS-4947: Tables in information_schema are treated as a normal tables. Symptome des Bugs sehen wie folgt aus:

Error: Couldn't read status information for table address () Error: Couldn't read status information for table sales ()

Daher empfiehlt sich dringend ein Upgrade auf MaxScale 23.08.5.

Komplexere Applikations-Tests

Wir haben noch einen etwas komplexeren Test erstellt (./sharding_test.php), der folgen Queries abarbeitet:

SET NAMES utf8mb4 SHOW DATABASES use customer_<nnnn> START TRANSACTION; SELECT MIN(id) AS first, MAX(id) AS last FROM `sales` INSERT INTO sales (id, product, sales, amount, total_amount) VALUES (%d, '%s', %f, %f, %f) INSERT INTO sales (id, product, sales, amount, total_amount) VALUES (%d, '%s', %f, %f, %f) UPDATE sales SET product = 'Prepare to delete' WHERE id = %d DELETE FROM sales WHERE id = %d COMMIT

Dieser Test lief einwandfrei durch. Die dazu gehörende Kontroll-Abfrage:

SQL> SELECT * FROM customer_0021.sales WHERE id >= (SELECT MAX(id) - 10 FROM customer_0021.sales);

Verschiedene Last-Szenarien können zusätzlich mit db_bench oder dem Acronis perfkit getestet werden. Weitere Informationen dazu siehe hier.

Cross-Shard-Tests

Allensfalls könnte man auf die Idee kommen, Cross-Shard-Queries auszuführen. Dies wird NICHT funktionieren, was ja nicht wirklich erstaunen sollte, da erstens nicht ganz einfach zu implementieren und zweites hier beschrieben:

"Note: As the sharding solution in MaxScale is relatively simple, cross-database queries between two or more shards are not supported."

Quelle: Simple Sharding with Two Servers

und

"USE db1 is routed to the server with db1. If the database is divided to multiple servers, only one server will get the command."

Quelle: SchemaRouter

Hier ein Test mit UNION:

SQL> use customer_0030 Database changed SQL> SELECT * FROM customer_0020.sales UNION SELECT * FROM customer_0030.sales; ERROR 1146 (42S02): Table 'customer_0020.sales' doesn't exist

Und hier noch der Gegenbeweis:

SQL> use customer_0020 Database changed SQL> SELECT * FROM customer_0020.sales UNION SELECT * FROM customer_0030.sales; ERROR 1146 (42S02): Table 'customer_0030.sales' doesn't exist

Und hier noch den Test mit JOIN:

SQL> use customer_0020 SQL> SELECT * FROM customer_0020.sales a JOIN customer_0030.sales b ON a.id = b.id WHERE a.sales > 1 ; ERROR 1146 (42S02): Table 'customer_0030.sales' doesn't exist SQL> use customer_0030 SQL> SELECT * FROM customer_0020.sales a JOIN customer_0030.sales b ON a.id = b.id WHERE a.sales > 1 ; ERROR 1146 (42S02): Table 'customer_0020.sales' doesn't exist
Betrieb eines MaxScale Sharding-Systems

In diesem Kapitel besprechen wir einige Punkte die für den Betrieb eines MariaDB MaxScale Sharding-Systems nützlich sein können.

Do-on-all-shards

Da es immer wieder vorkommen kann, dass O/S oder Datenbank Operationen auf allen Shards ausgeführt werden müssen, wäre es sicher sinnvoll ein Skript zu erstellen, welches reihum, auf allen Shards, den selben Befehl ausführt:

shell> ./do-on-all-shards.sh --sql='SHOW DATABASES'

Ein so geartetes Skript dürfte die Fehlerrate im Betrieb stark reduzieren. Auch Operationen wie das Re-sharding eines Mandanten, wie weiter unten beschrieben, werden sinnvollerweise geskriptet und zentral ausgeführt.

Invalidieren des Database Map Caches

Mit dem Befehlt invalidate kann man den Database Map Cache des MariaDB MaxScale SchemaRouters invalidieren. Dies gibt uns die Möglichkeit, nach dem Hinzufügen oder dem Entfernen von Mandanten den Cache schnell wieder auf einen aktuellen Stand zu bringen.

shell> maxctrl call command schemarouter invalidate Sharded-Service OK

Im unterschied zum Befehl invalidate, bei welchem die Einträge nach dem nächsten refresh_intervall auf den neusten Stand gebracht werden, löscht der Befehl clear die Einträge und ein Remap wird sofort ausgeführt.

Wenn man den Database Map Cache von remote mit einem REST API Call invalidieren möchte, geht das wie folgt:

shell> curl -i -X POST -u api_admin:secret http://10.139.158.211:8989/v1/maxscale/modules/schemarouter/clear?Sharded-Service HTTP/1.1 204 No Content Connection: close Date: Mon, 18 Mar 24 11:49:58 GMT X-Frame-Options: Deny X-XSS-Protection: 1 Referrer-Policy: same-origin Cache-Control: no-cache

Quellen:


Wie ändert man SchemaRouter Variablen dynamisch?

Das refresh_interval spezifiziert die Lebensdauer der Einträge im SchemaRouter Database Map Cache. Der default Wert ist 300 s (5 min). Refresh Interval ist daher, meiner Meinung nach, ein unglücklicher Begriff da es nicht das Intervall zwischen zwei Mappings definiert sondern die Lebzeit der Cache-Einträge (livetime?, timeout?). Sobald der Eintrag gelöscht wurde wir ein neuer Refresh der "Database Map" auf jedem Shard getriggert. Der Befehl sieht aktuell wie folgt aus:

SELECT LOWER(t.table_schema), LOWER(t.table_name) FROM information_schema.tables t UNION ALL SELECT LOWER(s.schema_name), '' FROM information_schema.schemata s

So wie es aussieht reicht ein simpler Connect um den Refresh der Database Map zu triggern.

Der aktuelle Wert für refresh_intervall kann wie folgt abgefragt werden.

shell> maxctrl show service Sharded-Service | grep refresh_interval | awk -F'│' '{ print $3 }' "refresh_interval": "300000ms",

Um den Wert dynamisch zu ändern hilft folgender Befehl:

shell> MAXCTRL_WARNINGS=0 maxctrl alter service Sharded-Service refresh_interval=10s OK

Der Wert sollte nicht zu klein eingestellt werden, da während des Mapping Vorgangs alle anderen Connections angehalten werden.

Quellen:


Hinzufügen und Entfernen eines Mandanten

Das Hinzufügen eines neuen Mandanten zu einem Shard stellt kein grosses Problem dar:

SQL> CREATE DATABASE customer_0029; SQL> use customer_0029 SQL> CREATE TABLE address LIKE customer_template.address; SQL> CREATE TABLE sales LIKE customer_template.sales; shell> maxctrl call command schemarouter invalidate Sharded-Service OK

Das Entfernen eines Mandanten von einem Shard ist hingegen etwas komplizierter und muss in Absprache mit der Applikation erfolgen:

SQL> DROP DATABASE customer_0011; shell> ./sharding_test.php .....ERROR: Table 'customer_0011.sales' doesn't exist...ERROR: Unknown database 'customer_0011'.ERROR: Unknown database 'customer_0011'......ERROR: Unknown database 'customer_0011'... shell> maxctrl call command schemarouter clear Sharded-Service OK

Zumindest ist mir bisher noch keine schlauere Variante eingefallen. Siehe auch Umziehen eines Mandanten weiter unten.

Umziehen eines Mandanten

Die Kombination von Hinzufügen und Entfernen wäre dann das Umziehen eines Mandanten von einem Shard auf ein anderes Shard, auch re-sharding genannt. Hierzu muss ebenfalls wieder mit der Applikation zusammen eine konzertierte Aktion geplant werden.

Falls dies nicht möglich ist, kann zumindest die Zeit, welche die Applikation Fehler erhält reduziert werden... Folgendes Vorgehen kann verwendet werden um einen Mandanten von Shard 2 auf Shard 3 umzuziehen:

SQL> use customer_0020; LOCK TABLES address READ, sales READ; -- Auf Shard 2, Applikation wir allenfalls blockiert! shell> mariadb-dump --user=app --password=secret --host=10.139.158.1 --port=3364 --single-transaction --skip-add-locks --databases customer_0020 | mariadb --user=app --password=secret --host=10.139.158.1 --port=3365 # Kopieren des Mandanten 20 von Shard 2 auf Shard 3 SQL> DROP DATABASE customer_0020; -- Löschen von Mandant 20 geht nicht! ERROR 1192 (HY000): Can't execute the given command because you have active locked tables or an active transaction SQL> UNLOCK TABLES; DROP DATABASE customer_0020; # So geht das Löschen von Mandant 20. shell> maxctrl call command schemarouter clear Sharded-Service # MaxScale Database Map aktualisieren. Schnell machen!!!
Bis zum Refresh der Database Map kann es zu folgenden Fehlern kommen: error : (47621) [schemarouter] (Sharded-Service); 'customer_0020.' found on servers 'shard2','shard3' for user 'app'@'10.139.158.1'. error : (47621) [schemarouter] (Sharded-Service); 'customer_0020.address' found on servers 'shard2','shard3' for user 'app'@'10.139.158.1'. error : (47621) [schemarouter] (Sharded-Service); 'customer_0020.sales' found on servers 'shard2','shard3' for user 'app'@'10.139.158.1'. error : (47621) [schemarouter] (Sharded-Service); Duplicate tables found, closing session.

Und applikationsseitig zu:

ERROR: Error: duplicate tables found on two different shards
Hinzufügen oder entfernen eines Shards

Das Umziehen eines Mandanten von einem Shard auf ein anderes Shard ist das kleine re-sharding. Etwas komplexer wird es, wenn man neue Shards hinzufügen oder alte Shards entfernen möchte. Im Anschluss daran (nach dem Hinzufügen bzw. vor dem Entfernen) würde dann ein grosses re-sharding erfolgen. Zuerst das Erweitern des Clusters um ein Shard:

shell> maxctrl list servers ┌────────┬──────────────┬──────┬─────────────┬─────────┬───────────────┬──────────────────┐ │ Server │ Address │ Port │ Connections │ State │ GTID │ Monitor │ ├────────┼──────────────┼──────┼─────────────┼─────────┼───────────────┼──────────────────┤ │ shard1 │ 10.139.158.1 │ 3363 │ 0 │ Running │ 0-3363-26014 │ Sharding-Monitor │ ├────────┼──────────────┼──────┼─────────────┼─────────┼───────────────┼──────────────────┤ │ shard2 │ 10.139.158.1 │ 3364 │ 0 │ Running │ 0-3364-240612 │ Sharding-Monitor │ ├────────┼──────────────┼──────┼─────────────┼─────────┼───────────────┼──────────────────┤ │ shard3 │ 10.139.158.1 │ 3365 │ 0 │ Running │ 0-3365-289873 │ Sharding-Monitor │ └────────┴──────────────┴──────┴─────────────┴─────────┴───────────────┴──────────────────┘

Das vorbereitete Shard wird MaxScale bekannt gemacht:

shell> maxctrl create server shard4 10.139.158.1 3366 OK shell> maxctrl list servers ┌────────┬──────────────┬──────┬─────────────┬─────────┬──────────────┬──────────────────┐ │ Server │ Address │ Port │ Connections │ State │ GTID │ Monitor │ ├────────┼──────────────┼──────┼─────────────┼─────────┼──────────────┼──────────────────┤ │ shard1 │ 10.139.158.1 │ 3363 │ 1 │ Running │ 0-3363-23676 │ Sharding-Monitor │ ├────────┼──────────────┼──────┼─────────────┼─────────┼──────────────┼──────────────────┤ │ shard2 │ 10.139.158.1 │ 3364 │ 1 │ Running │ 0-3364-52321 │ Sharding-Monitor │ ├────────┼──────────────┼──────┼─────────────┼─────────┼──────────────┼──────────────────┤ │ shard3 │ 10.139.158.1 │ 3365 │ 1 │ Running │ 0-3365-39751 │ Sharding-Monitor │ ├────────┼──────────────┼──────┼─────────────┼─────────┼──────────────┼──────────────────┤ │ shard4 │ 10.139.158.1 │ 3366 │ 0 │ Down │ │ │ └────────┴──────────────┴──────┴─────────────┴─────────┴──────────────┴──────────────────┘

Dann wird des neue Shard mit dem MaxScale Monitor und dem Service verknüpft:

shell> MAXCTRL_WARNINGS=0 maxctrl link monitor Sharding-Monitor shard4 OK shell> MAXCTRL_WARNINGS=0 maxctrl link service Sharded-Service shard4 OK shell> maxctrl list servers ┌────────┬──────────────┬──────┬─────────────┬─────────┬──────────────┬──────────────────┐ │ Server │ Address │ Port │ Connections │ State │ GTID │ Monitor │ ├────────┼──────────────┼──────┼─────────────┼─────────┼──────────────┼──────────────────┤ │ shard1 │ 10.139.158.1 │ 3363 │ 1 │ Running │ 0-3363-24961 │ Sharding-Monitor │ ├────────┼──────────────┼──────┼─────────────┼─────────┼──────────────┼──────────────────┤ │ shard2 │ 10.139.158.1 │ 3364 │ 1 │ Running │ 0-3364-56215 │ Sharding-Monitor │ ├────────┼──────────────┼──────┼─────────────┼─────────┼──────────────┼──────────────────┤ │ shard3 │ 10.139.158.1 │ 3365 │ 1 │ Running │ 0-3365-45177 │ Sharding-Monitor │ ├────────┼──────────────┼──────┼─────────────┼─────────┼──────────────┼──────────────────┤ │ shard4 │ 10.139.158.1 │ 3366 │ 1 │ Running │ 0-3366-32 │ Sharding-Monitor │ └────────┴──────────────┴──────┴─────────────┴─────────┴──────────────┴──────────────────┘

Ob dieser zweite Schritt ebenfalls zwingend notwendig ist, wurde nicht untersucht.

Im MariaDB MaxScale Error Log kann man den ganzen Ablauf mitverfolgen:

warning: Discarding journal file '/var/lib/maxscale/Sharding-Monitor_journal.json'. Servers described in the journal are different from the ones configured on the current monitor. warning: Saving runtime modifications to 'Sharding-Monitor' in '/var/lib/maxscale/maxscale.cnf.d/Sharding-Monitor.cnf'. The modified values will override the values found in the static configuration files. notice : shard4 sent version string '10.11.7-MariaDB-log'. Detected type: MariaDB, version: 10.11.7. notice : Server 'shard4' charset: latin1_swedish_ci notice : Server changed state: shard4[10.139.158.1:3366]: server_up. [Down] -> [Running] warning: Saving runtime modifications to 'Sharded-Service' in '/var/lib/maxscale/maxscale.cnf.d/Sharded-Service.cnf'. The modified values will override the values found in the static configuration files. notice : Added 'shard4' to 'Sharded-Service'

Was wir hier nicht vergessen dürfen, ist das neue Shard ebenfalls mit dem Proxy Protokoll auszustatten:

shell> maxctrl show server shard4 | grep proxy │ │ "proxy_protocol": false, │ MAXCTRL_WARNINGS=0 maxctrl alter server shard4 proxy_protocol=true OK

Und jetzt können neue Mandanten auf dem neuen Shard hinzugefügt oder alte Mandanten auf das neue Shard umgezogen werden... In unserem Set-up wollen wir alle Mandanten vom Shard 1 auf Shard 4 umziehen und zudem einen neuen Mandanten customer_0040 auf Shard 4 erstellen. Die dazu benötigten Einzelschritte sind oben aufgeführt.

Nachdem das Shard 1 leergeräumt wurde, kann es abgebaut werden:

shell> maxctrl list servers ┌────────┬──────────────┬──────┬─────────────┬─────────┬──────────────┬──────────────────┐ │ Server │ Address │ Port │ Connections │ State │ GTID │ Monitor │ ├────────┼──────────────┼──────┼─────────────┼─────────┼──────────────┼──────────────────┤ │ shard1 │ 10.139.158.1 │ 3363 │ 1 │ Running │ 0-3363-25916 │ Sharding-Monitor │ ├────────┼──────────────┼──────┼─────────────┼─────────┼──────────────┼──────────────────┤ │ shard2 │ 10.139.158.1 │ 3364 │ 1 │ Running │ 0-3364-62887 │ Sharding-Monitor │ ├────────┼──────────────┼──────┼─────────────┼─────────┼──────────────┼──────────────────┤ │ shard3 │ 10.139.158.1 │ 3365 │ 1 │ Running │ 0-3365-54035 │ Sharding-Monitor │ ├────────┼──────────────┼──────┼─────────────┼─────────┼──────────────┼──────────────────┤ │ shard4 │ 10.139.158.1 │ 3366 │ 1 │ Running │ 0-3366-2247 │ Sharding-Monitor │ └────────┴──────────────┴──────┴─────────────┴─────────┴──────────────┴──────────────────┘

Mit dem Befehl destroy server wir ein Shard gelöscht. Bevor das aber funktioniert muss ein Shard aus dem Monitor und dem Service entfernt werden:

shell> MAXCTRL_WARNINGS=0 maxctrl unlink service Sharded-Service shard1 OK shell> MAXCTRL_WARNINGS=0 maxctrl unlink monitor Sharding-Monitor shard1 OK shell> maxctrl list servers ┌────────┬──────────────┬──────┬─────────────┬─────────┬──────────────┬──────────────────┐ │ Server │ Address │ Port │ Connections │ State │ GTID │ Monitor │ ├────────┼──────────────┼──────┼─────────────┼─────────┼──────────────┼──────────────────┤ │ shard1 │ 10.139.158.1 │ 3363 │ 0 │ Running │ │ │ ├────────┼──────────────┼──────┼─────────────┼─────────┼──────────────┼──────────────────┤ │ shard2 │ 10.139.158.1 │ 3364 │ 1 │ Running │ 0-3364-64394 │ Sharding-Monitor │ ├────────┼──────────────┼──────┼─────────────┼─────────┼──────────────┼──────────────────┤ │ shard3 │ 10.139.158.1 │ 3365 │ 1 │ Running │ 0-3365-56072 │ Sharding-Monitor │ ├────────┼──────────────┼──────┼─────────────┼─────────┼──────────────┼──────────────────┤ │ shard4 │ 10.139.158.1 │ 3366 │ 1 │ Running │ 0-3366-3267 │ Sharding-Monitor │ └────────┴──────────────┴──────┴─────────────┴─────────┴──────────────┴──────────────────┘

Wenn das Shard aus dem Monitor und dem Service herausgelöst ist, kann es anschliessend gelöscht werden:

shell> maxctrl destroy server shard1 Warning: Object 'shard1' is defined in a static configuration file and cannot be permanently deleted. If MaxScale is restarted, the object will appear again. To hide these warnings, run: export MAXCTRL_WARNINGS=0 OK shell> maxctrl list servers ┌────────┬──────────────┬──────┬─────────────┬─────────┬──────────────┬──────────────────┐ │ Server │ Address │ Port │ Connections │ State │ GTID │ Monitor │ ├────────┼──────────────┼──────┼─────────────┼─────────┼──────────────┼──────────────────┤ │ shard2 │ 10.139.158.1 │ 3364 │ 1 │ Running │ 0-3364-65018 │ Sharding-Monitor │ ├────────┼──────────────┼──────┼─────────────┼─────────┼──────────────┼──────────────────┤ │ shard3 │ 10.139.158.1 │ 3365 │ 1 │ Running │ 0-3365-56886 │ Sharding-Monitor │ ├────────┼──────────────┼──────┼─────────────┼─────────┼──────────────┼──────────────────┤ │ shard4 │ 10.139.158.1 │ 3366 │ 1 │ Running │ 0-3366-3648 │ Sharding-Monitor │ └────────┴──────────────┴──────┴─────────────┴─────────┴──────────────┴──────────────────┘

Und im MaxScale Error Log kann man die Änderungen schön mitverfolgen:

notice : Removed 'shard1' from 'Sharded-Service' warning: Discarding journal file '/var/lib/maxscale/Sharding-Monitor_journal.json'. Servers described in the journal are different from the ones configured on the current monitor. notice : Destroyed server 'shard1' at 10.139.158.1:3363

Quelle:


Anpassen der Konfigurationsdateien

Bei den oben beschriebenen Shard Operationen habe wir einige Warnungen erhalten:

Warning: Object 'shard1' is defined in a static configuration file and cannot be permanently deleted. If MaxScale is restarted, the object will appear again.

und

Warning: Saving runtime modifications to 'Sharding-Monitor' in '/var/lib/maxscale/maxscale.cnf.d/Sharding-Monitor.cnf'. The modified values will override the values found in the static configuration files.

Die entsprechenden Konfigurationsdateien werden von MaxScale automatisch bei dynamischen System-Änderungen erstellt:

shell> ll /var/lib/maxscale/maxscale.cnf.d/ /etc/maxscale.cnf -rw-r--r-- 1 root root 612 Feb 13 14:23 /etc/maxscale.cnf /var/lib/maxscale/maxscale.cnf.d/: total 12 -rw------- 1 maxscale maxscale 187 Feb 13 16:08 Sharding-Monitor.cnf -rw------- 1 maxscale maxscale 150 Feb 13 16:07 Sharded-Service.cnf -rw------- 1 maxscale maxscale 52 Feb 13 15:46 shard4.cnf cat /var/lib/maxscale/maxscale.cnf.d/* [Sharded-Service] debug=true refresh_interval=10000ms auth_all_servers=true log_debug=true password=secret router=schemarouter type=service user=maxscale_admin targets=shard2,shard3,shard4 [Sharding-Monitor] module=galeramon monitor_interval=1000ms password=secret servers=shard2,shard3,shard4 type=monitor user=maxscale_monitor [shard4] address=10.139.158.1 port=3366 type=server

Es müssen also noch entsprechende Nachbesserungen an den Konfigurationsdateien vorgenommen werden. Man müsste sich allgemein überlegen ob man bei einem hochdynamischen System nicht alles dynamisch über Befehle konfigurieren sollte...

Wartungsarbeiten am Shard

Wenn für Wartungsarbeiten ein Shard offline genommen werden soll, hier im Beispiel shard2, kann dies wie folgt bewerkstelligt werden:

shell> maxctrl list servers ┌────────┬──────────────┬──────┬─────────────┬─────────┬──────────────┬──────────────────┐ │ Server │ Address │ Port │ Connections │ State │ GTID │ Monitor │ ├────────┼──────────────┼──────┼─────────────┼─────────┼──────────────┼──────────────────┤ │ shard2 │ 10.139.158.1 │ 3364 │ 1 │ Running │ 0-3364-69817 │ Sharding-Monitor │ ├────────┼──────────────┼──────┼─────────────┼─────────┼──────────────┼──────────────────┤ │ shard3 │ 10.139.158.1 │ 3365 │ 1 │ Running │ 0-3365-63166 │ Sharding-Monitor │ ├────────┼──────────────┼──────┼─────────────┼─────────┼──────────────┼──────────────────┤ │ shard4 │ 10.139.158.1 │ 3366 │ 1 │ Running │ 0-3366-6902 │ Sharding-Monitor │ └────────┴──────────────┴──────┴─────────────┴─────────┴──────────────┴──────────────────┘ shell> maxctrl set server shard2 drain OK shell> maxctrl set server shard2 maintenance OK shell> maxctrl list servers ┌────────┬──────────────┬──────┬─────────────┬──────────────────────┬───────────────┬──────────────────┐ │ Server │ Address │ Port │ Connections │ State │ GTID │ Monitor │ ├────────┼──────────────┼──────┼─────────────┼──────────────────────┼───────────────┼──────────────────┤ │ shard2 │ 10.139.158.1 │ 3364 │ 0 │ Maintenance, Running │ 0-3364-240612 │ Sharding-Monitor │ ├────────┼──────────────┼──────┼─────────────┼──────────────────────┼───────────────┼──────────────────┤ │ shard3 │ 10.139.158.1 │ 3365 │ 0 │ Running │ 0-3365-289873 │ Sharding-Monitor │ ├────────┼──────────────┼──────┼─────────────┼──────────────────────┼───────────────┼──────────────────┤ │ shard4 │ 10.139.158.1 │ 3366 │ 0 │ Running │ 0-3366-119848 │ Sharding-Monitor │ └────────┴──────────────┴──────┴─────────────┴──────────────────────┴───────────────┴──────────────────┘

Zu diesem Zeitpunkt können die Wartungsarbeiten an der Maschine oder der Datenbank vorgenommen werden...

Anschliessend müssen BEIDE Stati wieder gecleared werden, sofern bei gesetzt wurden:

shell> maxctrl clear server shard2 maintenance OK shell> maxctrl clear server shard2 drain OK maxctrl list servers ┌────────┬──────────────┬──────┬─────────────┬─────────┬───────────────┬──────────────────┐ │ Server │ Address │ Port │ Connections │ State │ GTID │ Monitor │ ├────────┼──────────────┼──────┼─────────────┼─────────┼───────────────┼──────────────────┤ │ shard2 │ 10.139.158.1 │ 3364 │ 0 │ Running │ 0-3364-240612 │ Sharding-Monitor │ ├────────┼──────────────┼──────┼─────────────┼─────────┼───────────────┼──────────────────┤ │ shard3 │ 10.139.158.1 │ 3365 │ 0 │ Running │ 0-3365-289873 │ Sharding-Monitor │ ├────────┼──────────────┼──────┼─────────────┼─────────┼───────────────┼──────────────────┤ │ shard4 │ 10.139.158.1 │ 3366 │ 0 │ Running │ 0-3366-119848 │ Sharding-Monitor │ └────────┴──────────────┴──────┴─────────────┴─────────┴───────────────┴──────────────────┘

Der Unterschied zwischen drain und maintenance besteht darin, dass bei drain keinen neuen Verbindungen mehr auf das Shard zugelassen werden aber bei bestehenden Verbindungen wird gewartet, bis sie geschlossen werden. Bei maintenance werden die Verbindungen sofort zwangsweise terminiert.

Beobachtung / Observierung eines MariaDB MaxScale Sharding-Systems

Mit dem MaxScale CLI Client maxtrl kann man den Zustand des MariaDB MaxScale Load Balancers abfragen hierzu gibt es zahlreiche Befehle, hauptsächlich list und show:

shell> maxctrl show module schemarouter | head -n 12 ┌─────────────┬────────────────────────────────────────────────┐ │ Module │ schemarouter │ ├─────────────┼────────────────────────────────────────────────┤ │ Type │ Router │ ├─────────────┼────────────────────────────────────────────────┤ │ Version │ V1.0.0 │ ├─────────────┼────────────────────────────────────────────────┤ │ Maturity │ Beta │ ├─────────────┼────────────────────────────────────────────────┤ │ Description │ A database sharding router for simple sharding │ ├─────────────┼────────────────────────────────────────────────┤ │ Parameters │ ... │ shell> maxctrl list servers ┌────────┬──────────────┬──────┬─────────────┬─────────┬───────────────┬──────────────────┐ │ Server │ Address │ Port │ Connections │ State │ GTID │ Monitor │ ├────────┼──────────────┼──────┼─────────────┼─────────┼───────────────┼──────────────────┤ │ shard2 │ 10.139.158.1 │ 3364 │ 4 │ Running │ 0-3364-290859 │ Sharding-Monitor │ ├────────┼──────────────┼──────┼─────────────┼─────────┼───────────────┼──────────────────┤ │ shard3 │ 10.139.158.1 │ 3365 │ 4 │ Running │ 0-3365-322671 │ Sharding-Monitor │ ├────────┼──────────────┼──────┼─────────────┼─────────┼───────────────┼──────────────────┤ │ shard4 │ 10.139.158.1 │ 3366 │ 4 │ Running │ 0-3366-140018 │ Sharding-Monitor │ └────────┴──────────────┴──────┴─────────────┴─────────┴───────────────┴──────────────────┘

Die Information für die Spalte Connections ist verwirrend, da wir bei diesem Sharding-System in diesem Fall auf jedem Shard nur 1, 1 und 2 Verbindungen offen haben.

Wenn man sich mit SHOW PROCESSLIST die Situation aber auf dem jeweiligen Shard anschaut, sieht man, dass MaxScale auf JEDEM Shard für jede eingehende Verbindung auch eine Verbindung auf jedes Shard aufbaut. Somit ist die Anzeige oben eigentlich technisch korrekt, nur nicht, was man erwarten würde:

SQL> SHOW PROCESSLIST; +--------+------------------+----------------------+---------------+---------+------+----------+-----------------------------------------------------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +--------+------------------+----------------------+---------------+---------+------+----------+-----------------------------------------------------------------+----------+ | 123 | root | localhost | customer_0021 | Query | 0 | starting | show processlist | 0.000 | | 68107 | maxscale_monitor | 10.139.158.211:35418 | NULL | Sleep | 0 | | NULL | 0.000 | | 113372 | app | 10.139.158.1:47548 | NULL | Sleep | 47 | | NULL | 0.000 | | 113538 | app | 10.139.158.1:49058 | NULL | Sleep | 41 | | NULL | 0.000 | | 113662 | app | 10.139.158.1:47072 | NULL | Sleep | 37 | | NULL | 0.000 | | 114789 | app | 10.139.158.1:39574 | customer_0022 | Query | 0 | Updating | UPDATE sales SET product = 'Prepare to delete' WHERE id = 15622 | 0.000 | +--------+------------------+----------------------+---------------+---------+------+----------+-----------------------------------------------------------------+----------+

Das skaliert so nicht bei grossen Systemen mit hunderten oder tausenden von Mandanten! Ev. kommt in diesem Fall das MariaDB Thread Pool Feature zum Einsatz.

Laut dem MaxScale Entwickler ist dies gewünschtes/beabsichtigtes Verhalten...

shell> maxctrl list services ┌─────────────────┬──────────────┬─────────────┬───────────────────┬────────────────────────┐ │ Service │ Router │ Connections │ Total Connections │ Targets │ ├─────────────────┼──────────────┼─────────────┼───────────────────┼────────────────────────┤ │ Sharded-Service │ schemarouter │ 4 │ 82776 │ shard2, shard3, shard4 │ └─────────────────┴──────────────┴─────────────┴───────────────────┴────────────────────────┘ shell> maxctrl list listeners ┌──────────────────────────┬──────┬──────┬─────────┬─────────────────┐ │ Name │ Port │ Host │ State │ Service │ ├──────────────────────────┼──────┼──────┼─────────┼─────────────────┤ │ Sharded-Service-Listener │ 3306 │ :: │ Running │ Sharded-Service │ └──────────────────────────┴──────┴──────┴─────────┴─────────────────┘ shell> maxctrl list monitors ┌──────────────────┬─────────┬────────────────────────┐ │ Monitor │ State │ Servers │ ├──────────────────┼─────────┼────────────────────────┤ │ Sharding-Monitor │ Running │ shard2, shard3, shard4 │ └──────────────────┴─────────┴────────────────────────┘ shell> maxctrl show server shard2 | head -n 20 ┌─────────────────────┬──────────────────────────────────────────────┐ │ Server │ shard2 │ ├─────────────────────┼──────────────────────────────────────────────┤ │ Source │ /etc/maxscale.cnf │ ├─────────────────────┼──────────────────────────────────────────────┤ │ Address │ 10.139.158.1 │ ├─────────────────────┼──────────────────────────────────────────────┤ │ Port │ 3364 │ ├─────────────────────┼──────────────────────────────────────────────┤ │ State │ Running │ ├─────────────────────┼──────────────────────────────────────────────┤ │ Version │ 10.11.7-MariaDB-log │ ├─────────────────────┼──────────────────────────────────────────────┤ │ Uptime │ 178960 │ ├─────────────────────┼──────────────────────────────────────────────┤ │ Last Event │ server_down │ ├─────────────────────┼──────────────────────────────────────────────┤ │ Triggered At │ Sun, 04 Feb 2024 07:37:17 GMT │ ├─────────────────────┼──────────────────────────────────────────────┤ │ Services │ Sharded-Service │ ├─────────────────────┼──────────────────────────────────────────────┤ │ Monitors │ Sharding-Monitor │ ├─────────────────────┼──────────────────────────────────────────────┤ ... ├─────────────────────┼──────────────────────────────────────────────┤ │ Current Connections │ 5 │ ├─────────────────────┼──────────────────────────────────────────────┤ │ Total Connections │ 27 │ ├─────────────────────┼──────────────────────────────────────────────┤ │ Max Connections │ 5 │ shell> maxctrl show service Sharded-Service ┌─────────────────────┬──────────────────────────────────────────────────────┐ │ Service │ Sharded-Service │ ├─────────────────────┼──────────────────────────────────────────────────────┤ │ Source │ /var/lib/maxscale/maxscale.cnf.d/Sharded-Service.cnf │ ├─────────────────────┼──────────────────────────────────────────────────────┤ │ Router │ schemarouter │ ├─────────────────────┼──────────────────────────────────────────────────────┤ │ State │ Started │ ├─────────────────────┼──────────────────────────────────────────────────────┤ │ Started At │ 3/18/2024, 1:52:30 PM │ ├─────────────────────┼──────────────────────────────────────────────────────┤ │ Users Loaded At │ 3/18/2024, 1:52:30 PM │ ├─────────────────────┼──────────────────────────────────────────────────────┤ │ Current Connections │ 4 │ ├─────────────────────┼──────────────────────────────────────────────────────┤ │ Total Connections │ 84590 │ ├─────────────────────┼──────────────────────────────────────────────────────┤ │ Max Connections │ 5 │ ├─────────────────────┼──────────────────────────────────────────────────────┤ │ Cluster │ │ ├─────────────────────┼──────────────────────────────────────────────────────┤ │ Servers │ shard2 │ │ │ shard3 │ │ │ shard4 │ ├─────────────────────┼──────────────────────────────────────────────────────┤ │ Services │ │ ├─────────────────────┼──────────────────────────────────────────────────────┤ │ Filters │ │ ├─────────────────────┼──────────────────────────────────────────────────────┤ │ Parameters │ { │ │ │ "auth_all_servers": true, │ │ │ "connection_keepalive": "300000ms", │ │ │ "debug": true, │ │ │ "disable_sescmd_history": false, │ │ │ "enable_root_user": false, │ │ │ "force_connection_keepalive": false, │ │ │ "idle_session_pool_time": "-1ms", │ │ │ "ignore_tables": [], │ │ │ "ignore_tables_regex": null, │ │ │ "localhost_match_wildcard_host": true, │ │ │ "log_auth_warnings": true, │ │ │ "log_debug": true, │ │ │ "log_info": false, │ │ │ "log_notice": false, │ │ │ "log_warning": false, │ │ │ "max_connections": 0, │ │ │ "max_sescmd_history": 50, │ │ │ "max_staleness": "150000ms", │ │ │ "multiplex_timeout": "60000ms", │ │ │ "net_write_timeout": "0ms", │ │ │ "password": "*****", │ │ │ "prune_sescmd_history": true, │ │ │ "rank": "primary", │ │ │ "refresh_databases": false, │ │ │ "refresh_interval": "10000ms", │ │ │ "retain_last_statements": -1, │ │ │ "router": "schemarouter", │ │ │ "session_trace": false, │ │ │ "strip_db_esc": true, │ │ │ "type": "service", │ │ │ "user": "maxscale_admin", │ │ │ "user_accounts_file": null, │ │ │ "user_accounts_file_usage": "add_when_load_ok", │ │ │ "version_string": null, │ │ │ "wait_timeout": "0ms" │ │ │ } │ ├─────────────────────┼──────────────────────────────────────────────────────┤ │ Router Diagnostics │ { │ │ │ "average_session": 0.028822357131634554, │ │ │ "longest_sescmd_chain": 4, │ │ │ "longest_session": 50, │ │ │ "queries": 761134, │ │ │ "sescmd_percentage": 44.44342257736483, │ │ │ "shard_map_hits": 84356, │ │ │ "shard_map_misses": 5, │ │ │ "shard_map_stale": 229, │ │ │ "shard_map_updates": 216, │ │ │ "shortest_session": 0, │ │ │ "times_sescmd_limit_exceeded": 0 │ │ │ } │ └─────────────────────┴──────────────────────────────────────────────────────┘

Siehe hierzu auch MaxScale SchemaRouter Router diagnostics.

shell> maxctrl show monitor Sharding-Monitor ┌─────────────────────┬──────────────────────────────────────────────────────────┐ │ Monitor │ Sharding-Monitor │ ├─────────────────────┼──────────────────────────────────────────────────────────┤ │ Source │ /etc/maxscale.cnf │ ├─────────────────────┼──────────────────────────────────────────────────────────┤ │ Module │ galeramon │ ├─────────────────────┼──────────────────────────────────────────────────────────┤ │ State │ Running │ ├─────────────────────┼──────────────────────────────────────────────────────────┤ │ Servers │ shard1 │ │ │ shard2 │ │ │ shard3 │ ├─────────────────────┼──────────────────────────────────────────────────────────┤ │ Parameters │ { │ │ │ "available_when_donor": false, │ │ │ "backend_connect_attempts": 1, │ │ │ "backend_connect_timeout": "3000ms", │ │ │ "backend_read_timeout": "3000ms", │ │ │ "backend_write_timeout": "3000ms", │ │ │ "disable_master_failback": false, │ │ │ "disable_master_role_setting": false, │ │ │ "disk_space_check_interval": "0ms", │ │ │ "disk_space_threshold": null, │ │ │ "events": "all,master_down,master_up,...,new_donor", │ │ │ "journal_max_age": "28800000ms", │ │ │ "module": "galeramon", │ │ │ "monitor_interval": "1000ms", │ │ │ "password": "*****", │ │ │ "root_node_as_master": false, │ │ │ "script": null, │ │ │ "script_timeout": "90000ms", │ │ │ "set_donor_nodes": false, │ │ │ "type": "monitor", │ │ │ "use_priority": false, │ │ │ "user": "maxscale_monitor" │ │ │ } │ ├─────────────────────┼──────────────────────────────────────────────────────────┤ │ Monitor Diagnostics │ { │ │ │ "disable_master_failback": false, │ │ │ "disable_master_role_setting": false, │ │ │ "root_node_as_master": false, │ │ │ "server_info": [ │ │ │ { │ │ │ "gtid_binlog_pos": "0-3363-26014", │ │ │ "gtid_current_pos": "0-3363-26014", │ │ │ "master_id": 0, │ │ │ "name": "shard1", │ │ │ "read_only": false, │ │ │ "server_id": 3363 │ │ │ }, │ │ │ { │ │ │ "gtid_binlog_pos": "0-3364-240612", │ │ │ "gtid_current_pos": "0-3364-240612", │ │ │ "master_id": 0, │ │ │ "name": "shard2", │ │ │ "read_only": false, │ │ │ "server_id": 3364 │ │ │ }, │ │ │ { │ │ │ "gtid_binlog_pos": "0-3365-289873", │ │ │ "gtid_current_pos": "0-3365-289873", │ │ │ "master_id": 0, │ │ │ "name": "shard3", │ │ │ "read_only": false, │ │ │ "server_id": 3365 │ │ │ } │ │ │ ], │ │ │ "set_donor_nodes": false, │ │ │ "use_priority": false │ │ │ } │ └─────────────────────┴──────────────────────────────────────────────────────────┘ shell> maxctrl list sessions; ┌───────┬──────┬──────────────┬───────────────────────┬───────┬─────────────────┬────────┬──────────────┐ │ Id │ User │ Host │ Connected │ Idle │ Service │ Memory │ I/O-Activity │ ├───────┼──────┼──────────────┼───────────────────────┼───────┼─────────────────┼────────┼──────────────┤ │ 87240 │ app │ 10.139.158.1 │ 3/18/2024, 2:33:54 PM │ 0 │ Sharded-Service │ 68644 │ 33 │ ├───────┼──────┼──────────────┼───────────────────────┼───────┼─────────────────┼────────┼──────────────┤ │ 72654 │ app │ 10.139.158.1 │ 3/18/2024, 2:25:27 PM │ 506.3 │ Sharded-Service │ 199328 │ 0 │ ├───────┼──────┼──────────────┼───────────────────────┼───────┼─────────────────┼────────┼──────────────┤ │ 72364 │ app │ 10.139.158.1 │ 3/18/2024, 2:25:18 PM │ 516 │ Sharded-Service │ 199328 │ 0 │ ├───────┼──────┼──────────────┼───────────────────────┼───────┼─────────────────┼────────┼──────────────┤ │ 72530 │ app │ 10.139.158.1 │ 3/18/2024, 2:25:23 PM │ 510.5 │ Sharded-Service │ 199328 │ 0 │ └───────┴──────┴──────────────┴───────────────────────┴───────┴─────────────────┴────────┴──────────────┘ shell> maxctrl show session 26 ┌───────────────────────┬───────────────────────────────────────┐ │ Id │ 26 │ ├───────────────────────┼───────────────────────────────────────┤ │ Service │ Sharded-Service │ ├───────────────────────┼───────────────────────────────────────┤ │ State │ Session started │ ├───────────────────────┼───────────────────────────────────────┤ │ User │ app │ ├───────────────────────┼───────────────────────────────────────┤ │ Host │ 10.139.158.1 │ ├───────────────────────┼───────────────────────────────────────┤ │ Port │ 42854 │ ├───────────────────────┼───────────────────────────────────────┤ │ Database │ │ ├───────────────────────┼───────────────────────────────────────┤ │ Connected │ 2/4/2024, 9:31:12 AM │ ├───────────────────────┼───────────────────────────────────────┤ │ Idle │ 610.4 │ ├───────────────────────┼───────────────────────────────────────┤ │ Parameters │ { │ │ │ "log_error": false, │ │ │ "log_info": false, │ │ │ "log_notice": false, │ │ │ "log_warning": false │ │ │ } │ ├───────────────────────┼───────────────────────────────────────┤ │ Client TLS Cipher │ │ ├───────────────────────┼───────────────────────────────────────┤ │ Connection attributes │ { │ │ │ "_client_name": "libmariadb", │ │ │ "_client_version": "3.3.8", │ │ │ "_os": "Linux", │ │ │ "_pid": "251037", │ │ │ "_platform": "x86_64", │ │ │ "_server_host": "10.139.158.211", │ │ │ "program_name": "mysql" │ │ │ } │ ├───────────────────────┼───────────────────────────────────────┤ │ Connections │ shard1 │ │ │ shard2 │ │ │ shard3 │ ├───────────────────────┼───────────────────────────────────────┤ │ Connection IDs │ 666 │ │ │ 139 │ │ │ 138 │ ├───────────────────────┼───────────────────────────────────────┤ │ Queries │ │ ├───────────────────────┼───────────────────────────────────────┤ │ Log │ │ ├───────────────────────┼───────────────────────────────────────┤ │ Memory │ { │ │ │ "connection_buffers": { │ │ │ "backends": { │ │ │ "shard1": { │ │ │ "misc": 678, │ │ │ "readq": 65536, │ │ │ "total": 66214, │ │ │ "writeq": 0 │ │ │ }, │ │ │ "shard2": { │ │ │ "misc": 662, │ │ │ "readq": 0, │ │ │ "total": 662, │ │ │ "writeq": 0 │ │ │ }, │ │ │ "shard3": { │ │ │ "misc": 678, │ │ │ "readq": 65536, │ │ │ "total": 66214, │ │ │ "writeq": 0 │ │ │ } │ │ │ }, │ │ │ "client": { │ │ │ "misc": 654, │ │ │ "readq": 65536, │ │ │ "total": 66190, │ │ │ "writeq": 0 │ │ │ }, │ │ │ "total": 199280 │ │ │ }, │ │ │ "exec_metadata": 0, │ │ │ "last_queries": 0, │ │ │ "sescmd_history": 48, │ │ │ "total": 199328, │ │ │ "variables": 0 │ │ │ } │ ├───────────────────────┼───────────────────────────────────────┤ │ I/O Activity │ 0 │ └───────────────────────┴───────────────────────────────────────┘ shell> maxctrl show listener Sharded-Service-Listener ┌────────────┬───────────────────────────────────────────┐ │ Name │ Sharded-Service-Listener │ ├────────────┼───────────────────────────────────────────┤ │ Source │ /etc/maxscale.cnf │ ├────────────┼───────────────────────────────────────────┤ │ Service │ Sharded-Service │ ├────────────┼───────────────────────────────────────────┤ │ Parameters │ { │ │ │ "MariaDBProtocol": { │ │ │ "allow_replication": true │ │ │ }, │ │ │ "address": "::", │ │ │ "authenticator": null, │ │ │ "authenticator_options": null, │ │ │ "connection_init_sql_file": null, │ │ │ "connection_metadata": [ │ │ │ "character_set_client=auto", │ │ │ "character_set_connection=auto", │ │ │ "character_set_results=auto", │ │ │ "max_allowed_packet=auto", │ │ │ "system_time_zone=auto", │ │ │ "time_zone=auto", │ │ │ "tx_isolation=auto" │ │ │ ], │ │ │ "port": 3306, │ │ │ "protocol": "MariaDBProtocol", │ │ │ "proxy_protocol_networks": null, │ │ │ "service": "Sharded-Service", │ │ │ "socket": null, │ │ │ "sql_mode": "default", │ │ │ "ssl": false, │ │ │ "ssl_ca": null, │ │ │ "ssl_cert": null, │ │ │ "ssl_cert_verify_depth": 9, │ │ │ "ssl_cipher": null, │ │ │ "ssl_crl": null, │ │ │ "ssl_key": null, │ │ │ "ssl_verify_peer_certificate": false, │ │ │ "ssl_verify_peer_host": false, │ │ │ "ssl_version": "MAX", │ │ │ "type": "listener", │ │ │ "user_mapping_file": null │ │ │ } │ └────────────┴───────────────────────────────────────────┘ shell> maxctrl show module schemarouter ┌─────────────┬─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ │ Module │ schemarouter │ ├─────────────┼─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤ │ Type │ Router │ ├─────────────┼─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤ │ Version │ V1.0.0 │ ├─────────────┼─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤ │ Maturity │ Beta │ ├─────────────┼─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤ │ Description │ A database sharding router for simple sharding │ ├─────────────┼─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤ │ Parameters │ [ │ │ │ { │ │ │ "default_value": false, │ │ │ "description": "Enable debug mode", │ │ │ "mandatory": false, │ │ │ "modifiable": true, │ │ │ "name": "debug", │ │ │ "type": "bool" │ │ │ }, │ │ │ { │ │ │ "default_value": [], │ │ │ "description": "List of tables to ignore when checking for duplicates", │ │ │ "mandatory": false, │ │ │ "modifiable": true, │ │ │ "name": "ignore_tables", │ │ │ "type": "stringlist" │ │ │ }, │ │ │ { │ │ │ "description": "Regex of tables to ignore when checking for duplicates", │ │ │ "mandatory": false, │ │ │ "modifiable": true, │ │ │ "name": "ignore_tables_regex", │ │ │ "type": "regex" │ │ │ }, │ │ │ { │ │ │ "default_value": "150000ms", │ │ │ "description": "Maximum allowed staleness of a database map entry before clients block and wait for an update", │ │ │ "mandatory": false, │ │ │ "modifiable": true, │ │ │ "name": "max_staleness", │ │ │ "type": "duration", │ │ │ "unit": "ms" │ │ │ }, │ │ │ { │ │ │ "default_value": false, │ │ │ "description": "Refresh database mapping information", │ │ │ "mandatory": false, │ │ │ "modifiable": true, │ │ │ "name": "refresh_databases", │ │ │ "type": "bool" │ │ │ }, │ │ │ { │ │ │ "default_value": "300000ms", │ │ │ "description": "How often to refresh the database mapping information", │ │ │ "mandatory": false, │ │ │ "modifiable": true, │ │ │ "name": "refresh_interval", │ │ │ "type": "duration", │ │ │ "unit": "ms" │ │ │ }, │ │ │ { │ │ │ "default_value": false, │ │ │ "description": "Retrieve users from all backend servers instead of only one", │ │ │ "mandatory": false, │ │ │ "modifiable": true, │ │ │ "name": "auth_all_servers", │ │ │ "type": "bool" │ │ │ }, │ │ │ { │ │ │ "default_value": "300000ms", │ │ │ "description": "How ofted idle connections are pinged", │ │ │ "mandatory": false, │ │ │ "modifiable": true, │ │ │ "name": "connection_keepalive", │ │ │ "type": "duration", │ │ │ "unit": "ms" │ │ │ }, │ │ │ { │ │ │ "deprecated": true, │ │ │ "description": "Alias for 'wait_timeout'", │ │ │ "mandatory": false, │ │ │ "modifiable": true, │ │ │ "name": "connection_timeout", │ │ │ "type": "duration" │ │ │ }, │ │ │ { │ │ │ "default_value": false, │ │ │ "description": "Disable session command history", │ │ │ "mandatory": false, │ │ │ "modifiable": true, │ │ │ "name": "disable_sescmd_history", │ │ │ "type": "bool" │ │ │ }, │ │ │ { │ │ │ "default_value": false, │ │ │ "description": "Allow the root user to connect to this service", │ │ │ "mandatory": false, │ │ │ "modifiable": true, │ │ │ "name": "enable_root_user", │ │ │ "type": "bool" │ │ │ }, │ │ │ { │ │ │ "default_value": false, │ │ │ "description": "Ping connections unconditionally", │ │ │ "mandatory": false, │ │ │ "modifiable": true, │ │ │ "name": "force_connection_keepalive", │ │ │ "type": "bool" │ │ │ }, │ │ │ { │ │ │ "default_value": "-1ms", │ │ │ "description": "Put connections into pool after session has been idle for this long", │ │ │ "mandatory": false, │ │ │ "modifiable": true, │ │ │ "name": "idle_session_pool_time", │ │ │ "type": "duration", │ │ │ "unit": "ms" │ │ │ }, │ │ │ { │ │ │ "default_value": true, │ │ │ "description": "Match localhost to wildcard host", │ │ │ "mandatory": false, │ │ │ "modifiable": true, │ │ │ "name": "localhost_match_wildcard_host", │ │ │ "type": "bool" │ │ │ }, │ │ │ { │ │ │ "default_value": true, │ │ │ "description": "Log a warning when client authentication fails", │ │ │ "mandatory": false, │ │ │ "modifiable": true, │ │ │ "name": "log_auth_warnings", │ │ │ "type": "bool" │ │ │ }, │ │ │ { │ │ │ "default_value": false, │ │ │ "description": "Log debug messages for this service (debug builds only)", │ │ │ "mandatory": false, │ │ │ "modifiable": true, │ │ │ "name": "log_debug", │ │ │ "type": "bool" │ │ │ }, │ │ │ { │ │ │ "default_value": false, │ │ │ "description": "Log info messages for this service", │ │ │ "mandatory": false, │ │ │ "modifiable": true, │ │ │ "name": "log_info", │ │ │ "type": "bool" │ │ │ }, │ │ │ { │ │ │ "default_value": false, │ │ │ "description": "Log notice messages for this service", │ │ │ "mandatory": false, │ │ │ "modifiable": true, │ │ │ "name": "log_notice", │ │ │ "type": "bool" │ │ │ }, │ │ │ { │ │ │ "default_value": false, │ │ │ "description": "Log warning messages for this service", │ │ │ "mandatory": false, │ │ │ "modifiable": true, │ │ │ "name": "log_warning", │ │ │ "type": "bool" │ │ │ }, │ │ │ { │ │ │ "default_value": 0, │ │ │ "description": "Maximum number of connections", │ │ │ "mandatory": false, │ │ │ "modifiable": true, │ │ │ "name": "max_connections", │ │ │ "type": "count" │ │ │ }, │ │ │ { │ │ │ "default_value": 50, │ │ │ "description": "Session command history size", │ │ │ "mandatory": false, │ │ │ "modifiable": true, │ │ │ "name": "max_sescmd_history", │ │ │ "type": "count" │ │ │ }, │ │ │ { │ │ │ "default_value": "60000ms", │ │ │ "description": "How long a session can wait for a connection to become available", │ │ │ "mandatory": false, │ │ │ "modifiable": true, │ │ │ "name": "multiplex_timeout", │ │ │ "type": "duration", │ │ │ "unit": "ms" │ │ │ }, │ │ │ { │ │ │ "default_value": "0ms", │ │ │ "description": "Network write timeout", │ │ │ "mandatory": false, │ │ │ "modifiable": true, │ │ │ "name": "net_write_timeout", │ │ │ "type": "duration", │ │ │ "unit": "ms" │ │ │ }, │ │ │ { │ │ │ "description": "Password for the user used to retrieve database users", │ │ │ "mandatory": true, │ │ │ "modifiable": true, │ │ │ "name": "password", │ │ │ "type": "password" │ │ │ }, │ │ │ { │ │ │ "default_value": true, │ │ │ "description": "Prune old session command history if the limit is exceeded", │ │ │ "mandatory": false, │ │ │ "modifiable": true, │ │ │ "name": "prune_sescmd_history", │ │ │ "type": "bool" │ │ │ }, │ │ │ { │ │ │ "default_value": "primary", │ │ │ "description": "Service rank", │ │ │ "enum_values": [ │ │ │ "primary", │ │ │ "secondary" │ │ │ ], │ │ │ "mandatory": false, │ │ │ "modifiable": true, │ │ │ "name": "rank", │ │ │ "type": "enum" │ │ │ }, │ │ │ { │ │ │ "default_value": -1, │ │ │ "description": "Number of statements kept in memory", │ │ │ "mandatory": false, │ │ │ "modifiable": true, │ │ │ "name": "retain_last_statements", │ │ │ "type": "int" │ │ │ }, │ │ │ { │ │ │ "default_value": false, │ │ │ "description": "Enable session tracing for this service", │ │ │ "mandatory": false, │ │ │ "modifiable": true, │ │ │ "name": "session_trace", │ │ │ "type": "bool" │ │ │ }, │ │ │ { │ │ │ "default_value": false, │ │ │ "deprecated": true, │ │ │ "description": "Track session state using server responses", │ │ │ "mandatory": false, │ │ │ "modifiable": true, │ │ │ "name": "session_track_trx_state", │ │ │ "type": "bool" │ │ │ }, │ │ │ { │ │ │ "default_value": true, │ │ │ "deprecated": true, │ │ │ "description": "Strip escape characters from database names", │ │ │ "mandatory": false, │ │ │ "modifiable": true, │ │ │ "name": "strip_db_esc", │ │ │ "type": "bool" │ │ │ }, │ │ │ { │ │ │ "description": "Username used to retrieve database users", │ │ │ "mandatory": true, │ │ │ "modifiable": true, │ │ │ "name": "user", │ │ │ "type": "string" │ │ │ }, │ │ │ { │ │ │ "description": "Load additional users from a file", │ │ │ "mandatory": false, │ │ │ "modifiable": false, │ │ │ "name": "user_accounts_file", │ │ │ "type": "path" │ │ │ }, │ │ │ { │ │ │ "default_value": "add_when_load_ok", │ │ │ "description": "When and how the user accounts file is used", │ │ │ "enum_values": [ │ │ │ "add_when_load_ok", │ │ │ "file_only_always" │ │ │ ], │ │ │ "mandatory": false, │ │ │ "modifiable": false, │ │ │ "name": "user_accounts_file_usage", │ │ │ "type": "enum" │ │ │ }, │ │ │ { │ │ │ "description": "Custom version string to use", │ │ │ "mandatory": false, │ │ │ "modifiable": true, │ │ │ "name": "version_string", │ │ │ "type": "string" │ │ │ }, │ │ │ { │ │ │ "default_value": "0ms", │ │ │ "description": "Connection idle timeout", │ │ │ "mandatory": false, │ │ │ "modifiable": true, │ │ │ "name": "wait_timeout", │ │ │ "type": "duration", │ │ │ "unit": "ms" │ │ │ } │ │ │ ] │ ├─────────────┼─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤ │ Commands │ [ │ │ │ { │ │ │ "attributes": { │ │ │ "arg_max": 1, │ │ │ "arg_min": 1, │ │ │ "description": "Clear schemarouter shard map cache", │ │ │ "method": "POST", │ │ │ "parameters": [ │ │ │ { │ │ │ "description": "The schemarouter service", │ │ │ "required": true, │ │ │ "type": "SERVICE" │ │ │ } │ │ │ ] │ │ │ }, │ │ │ "id": "clear", │ │ │ "links": { │ │ │ "self": "http://127.0.0.1:8989/v1/modules/schemarouter/clear/" │ │ │ }, │ │ │ "type": "module_command" │ │ │ }, │ │ │ { │ │ │ "attributes": { │ │ │ "arg_max": 1, │ │ │ "arg_min": 1, │ │ │ "description": "Invalidate schemarouter shard map cache", │ │ │ "method": "POST", │ │ │ "parameters": [ │ │ │ { │ │ │ "description": "The schemarouter service", │ │ │ "required": true, │ │ │ "type": "SERVICE" │ │ │ } │ │ │ ] │ │ │ }, │ │ │ "id": "invalidate", │ │ │ "links": { │ │ │ "self": "http://127.0.0.1:8989/v1/modules/schemarouter/invalidate/" │ │ │ }, │ │ │ "type": "module_command" │ │ │ } │ │ │ ] │ └─────────────┴─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ shell> maxctrl show commands schemarouter ┌────────────┬────────────┬──────────────────────────┐ │ Command │ Parameters │ Descriptions │ ├────────────┼────────────┼──────────────────────────┤ │ clear │ SERVICE │ The schemarouter service │ ├────────────┼────────────┼──────────────────────────┤ │ invalidate │ SERVICE │ The schemarouter service │ └────────────┴────────────┴──────────────────────────┘ shell> maxctrl show dbusers Sharded-Service ┌───────────────────────┬────────────────┬───────────────────────┬───────┬───────┬────────┬───────┬──────┐ │ User │ Host │ Plugin │ TLS │ Super │ Global │ Proxy │ Role │ ├───────────────────────┼────────────────┼───────────────────────┼───────┼───────┼────────┼───────┼──────┤ │ PUBLIC │ │ │ false │ false │ false │ false │ │ ├───────────────────────┼────────────────┼───────────────────────┼───────┼───────┼────────┼───────┼──────┤ │ app │ 10.139.158.% │ mysql_native_password │ false │ false │ false │ false │ │ ├───────────────────────┼────────────────┼───────────────────────┼───────┼───────┼────────┼───────┼──────┤ │ app_role │ │ │ false │ false │ false │ false │ │ ├───────────────────────┼────────────────┼───────────────────────┼───────┼───────┼────────┼───────┼──────┤ │ mariadb.sys │ localhost │ mysql_native_password │ false │ false │ false │ false │ │ ├───────────────────────┼────────────────┼───────────────────────┼───────┼───────┼────────┼───────┼──────┤ │ maxscale_admin │ 10.139.158.210 │ mysql_native_password │ false │ false │ false │ false │ │ ├───────────────────────┼────────────────┼───────────────────────┼───────┼───────┼────────┼───────┼──────┤ │ maxscale_admin │ 10.139.158.211 │ mysql_native_password │ false │ false │ false │ false │ │ ├───────────────────────┼────────────────┼───────────────────────┼───────┼───────┼────────┼───────┼──────┤ │ maxscale_admin_role │ │ │ false │ false │ false │ false │ │ ├───────────────────────┼────────────────┼───────────────────────┼───────┼───────┼────────┼───────┼──────┤ │ maxscale_monitor │ 10.139.158.210 │ mysql_native_password │ false │ false │ false │ false │ │ ├───────────────────────┼────────────────┼───────────────────────┼───────┼───────┼────────┼───────┼──────┤ │ maxscale_monitor │ 10.139.158.211 │ mysql_native_password │ false │ false │ false │ false │ │ ├───────────────────────┼────────────────┼───────────────────────┼───────┼───────┼────────┼───────┼──────┤ │ maxscale_monitor_role │ │ │ false │ false │ false │ false │ │ ├───────────────────────┼────────────────┼───────────────────────┼───────┼───────┼────────┼───────┼──────┤ │ mysql │ localhost │ mysql_native_password │ false │ true │ true │ false │ │ ├───────────────────────┼────────────────┼───────────────────────┼───────┼───────┼────────┼───────┼──────┤ │ root │ localhost │ mysql_native_password │ false │ true │ true │ false │ │ └───────────────────────┴────────────────┴───────────────────────┴───────┴───────┴────────┴───────┴──────┘ shell> maxctrl show commands mariadbmon ┌───────────────────────────┬─────────────────────────────┬───────────────────────────────────────────────────────────────────────────────┐ │ Command │ Parameters │ Descriptions │ ├───────────────────────────┼─────────────────────────────┼───────────────────────────────────────────────────────────────────────────────┤ │ switchover │ MONITOR, [SERVER], [SERVER] │ Monitor name, New primary (optional), Current primary (optional) │ ├───────────────────────────┼─────────────────────────────┼───────────────────────────────────────────────────────────────────────────────┤ │ switchover-force │ MONITOR, [SERVER], [SERVER] │ Monitor name, New primary (optional), Current primary (optional) │ ├───────────────────────────┼─────────────────────────────┼───────────────────────────────────────────────────────────────────────────────┤ │ async-switchover │ MONITOR, [SERVER], [SERVER] │ Monitor name, New primary (optional), Current primary (optional) │ ├───────────────────────────┼─────────────────────────────┼───────────────────────────────────────────────────────────────────────────────┤ │ failover │ MONITOR │ Monitor name │ ├───────────────────────────┼─────────────────────────────┼───────────────────────────────────────────────────────────────────────────────┤ │ async-failover │ MONITOR │ Monitor name │ ├───────────────────────────┼─────────────────────────────┼───────────────────────────────────────────────────────────────────────────────┤ │ rejoin │ MONITOR, SERVER │ Monitor name, Joining server │ ├───────────────────────────┼─────────────────────────────┼───────────────────────────────────────────────────────────────────────────────┤ │ async-rejoin │ MONITOR, SERVER │ Monitor name, Joining server │ ├───────────────────────────┼─────────────────────────────┼───────────────────────────────────────────────────────────────────────────────┤ │ reset-replication │ MONITOR, [SERVER] │ Monitor name, Primary server (optional) │ ├───────────────────────────┼─────────────────────────────┼───────────────────────────────────────────────────────────────────────────────┤ │ async-reset-replication │ MONITOR, [SERVER] │ Monitor name, Primary server (optional) │ ├───────────────────────────┼─────────────────────────────┼───────────────────────────────────────────────────────────────────────────────┤ │ release-locks │ MONITOR │ Monitor name │ ├───────────────────────────┼─────────────────────────────┼───────────────────────────────────────────────────────────────────────────────┤ │ async-release-locks │ MONITOR │ Monitor name │ ├───────────────────────────┼─────────────────────────────┼───────────────────────────────────────────────────────────────────────────────┤ │ fetch-cmd-result │ MONITOR │ Monitor name │ ├───────────────────────────┼─────────────────────────────┼───────────────────────────────────────────────────────────────────────────────┤ │ cancel-cmd │ MONITOR │ Monitor name │ ├───────────────────────────┼─────────────────────────────┼───────────────────────────────────────────────────────────────────────────────┤ │ async-cs-add-node │ MONITOR, STRING, STRING │ Monitor name, Hostname/IP of node to add to ColumnStore cluster, Timeout │ ├───────────────────────────┼─────────────────────────────┼───────────────────────────────────────────────────────────────────────────────┤ │ async-cs-remove-node │ MONITOR, STRING, STRING │ Monitor name, Hostname/IP of node to remove from ColumnStore cluster, Timeout │ ├───────────────────────────┼─────────────────────────────┼───────────────────────────────────────────────────────────────────────────────┤ │ cs-get-status │ MONITOR │ Monitor name │ ├───────────────────────────┼─────────────────────────────┼───────────────────────────────────────────────────────────────────────────────┤ │ async-cs-get-status │ MONITOR │ Monitor name │ ├───────────────────────────┼─────────────────────────────┼───────────────────────────────────────────────────────────────────────────────┤ │ async-cs-start-cluster │ MONITOR, STRING │ Monitor name, Timeout │ ├───────────────────────────┼─────────────────────────────┼───────────────────────────────────────────────────────────────────────────────┤ │ async-cs-stop-cluster │ MONITOR, STRING │ Monitor name, Timeout │ ├───────────────────────────┼─────────────────────────────┼───────────────────────────────────────────────────────────────────────────────┤ │ async-cs-set-readonly │ MONITOR, STRING │ Monitor name, Timeout │ ├───────────────────────────┼─────────────────────────────┼───────────────────────────────────────────────────────────────────────────────┤ │ async-cs-set-readwrite │ MONITOR, STRING │ Monitor name, Timeout │ ├───────────────────────────┼─────────────────────────────┼───────────────────────────────────────────────────────────────────────────────┤ │ async-rebuild-server │ MONITOR, SERVER, [SERVER] │ Monitor name, Target server, Source server (optional) │ ├───────────────────────────┼─────────────────────────────┼───────────────────────────────────────────────────────────────────────────────┤ │ async-create-backup │ MONITOR, SERVER, STRING │ Monitor name, Source server, Backup name │ ├───────────────────────────┼─────────────────────────────┼───────────────────────────────────────────────────────────────────────────────┤ │ async-restore-from-backup │ MONITOR, SERVER, STRING │ Monitor name, Target server, Backup name │ └───────────────────────────┴─────────────────────────────┴───────────────────────────────────────────────────────────────────────────────┘
Literatur / Quellen
Taxonomy upgrade extras: shardingmaxscaleschemarouterload balancermulti-tenant

dbstat für MariaDB (und MySQL)

Oli Sennhauser - Thu, 2024-03-14 11:33
Inhaltsverzeichnis

Eine Idee, die ich schon lange ins Auge gefasst und jetzt endlich, dank eines Kunden, in Angriff genommen habe, ist dbstat für MariaDB/MySQL. Die Idee ist angelehnt an sar/sysstat von Sebastien Godard:

sar - Collect, report, or save system activity information.

und Oracle Statspack:

Statspack is a performance tuning tool ... to quickly gather detailed analysis of the performance of that database instance.

Funktionalität

Zwar haben wir seit längerem das Performance Schema, aber dieses deckt einige Punkte nicht ab, die wir in der Praxis als Problem sehen und von Kunden gewünscht werden:

  • Das Modul table_size sammelt Daten über das Wachstum von Tabellen. Somit können Aussagen über das Wachstum einzelner Tabellen, Datenbanken, die zukünftigen MariaDB Kataloge oder die ganze Instanz gemacht werden. Dies ist interessant für Nutzer welche Multi-Mandanten-Systeme (multi-tenant) im Einsatz oder sonst wie mit unkontrolliertem Wachstum zu kämpfen haben.
  • Das Modul processlist macht in regelmässigen Abständen einen Snapshot der Prozessliste und speichert diese ab. Diese Informationen sind nützlich bei post-mortem Analysen wenn der Nutzer zu langsam war, seine Prozessliste wegzuspeichern oder um zu verstehen, wie sich ein Problem aufgebaut hat.
  • Oft baut sich das Problem durch langlaufende Transaktion, Row Locks oder Metadata Locks auf. Diese werden durch die Module trx_and_lck sowie metadata_lock festgehalten und abgespeichert. Somit können wir Probleme sehen, die wir vorher gar nicht gespürt haben oder wird sehen nach dem Unglück, was zum Problem geführt hat (analog zu einem Fahrtenschreiber im Fahrzeug).
  • Eine weitere Fragestellung, die wir in der Praxis manchmal antreffen, ist: Wann wurde welche Datenbankvariable geändert und wie sah sie vorher aus. Dies wird durch das Modul global_variables abgedeckt. Wer oder warum die Variable geändert hat, kann leider datenbankseitig nicht eruiert werden. Dazu sind betriebliche Prozesse notwendig.
  • Das letzte Modul, global_status deckt eigentlich das ab, was sar/sysstat tut. Es sammelt die Werte von SHOW GLOBAL STATUS; ein und speichert sie ab für spätere Analysezwecke oder um damit einfach Graphen erstellen zu können.

Wie funktioniert dbstat

dbstat nutzt als Scheduler den Datenbank Event Scheduler. Dieser muss bei MariaDB zuerst eingeschaltet werden (event_scheduler = ON). Bei MySQL ist er bereits per default eingeschaltet. Der Event Scheduler hat den Vorteil, dass wir die Jobs feingranularer aktivieren können, zum Beispiel 10 s, was mit der Crontab nicht möglich wäre.

Der Event Scheduler führt dann SQL/PSM Code aus um einerseits die Daten zu sammeln und andererseits die Daten auch wieder zu löschen, damit die dbstat Datenbank nicht ins unermessliche wächst.

Aktuell sind folgende Jobs vorgesehen:

ModulSammelnLöschenMengengerüstBemerkungen table_size1/d um 02:0412/h, 1000 rows, > 31 d1000 tab x 31 d = 31k rowsSollte bis 288k Tabellen funktionieren. processlist1/min1/min, 1000 rows, > 7 d1000 con x 1440 min x 7 d = 10M rowsSollte bis 1000 Concurrent Connections funktionieren. trx_and_lck1/min1/min, 1000 rows, > 7 d100 lck x 1440 min x 7 d = 1M rowsHängt sehr stark von der Anwendung ab. metadata_lock1/min12/h, 1000 rows, > 30 d100 mdl x 1440 x 30 d = 4M rowsHängt sehr stark von der Anwendung ab. global_variables1/minnie1000 rowsIm Normalfall sollte diese Tabelle nicht anwachsen. global_status1/min1/min, 1000 rows, > 30 d1000 rows x 1440 x 30 d = 40M rowsKann gross werden?
Installation

dbstat kann von Github heruntergeladen werden und steht unter der GPLv2.

Die Installation ist einfach: Als erstes die SQL Datei create_user_and_db.sql ausführen. Dann in der Datenbank dbstat die entsprechenden create_*.sql Dateien für die jeweiligen Module ausführen. Es gibt zur Zeit keine direkten Abhängigkeiten zwischen den Modulen. Wenn ein anderer User oder eine andere Datenbank als dbstat verwendet werden soll, muss man sich selber drum kümmern.

Abfrage

Einige mögliche Abfragen auf die Daten wurden bereits vorbereitet. Sie sind zu finden in den Dateien query_*.sql. Hier ein paar Beispiele:

table_size SELECT `table_schema`, `table_name`, `ts`, `table_rows`, `data_length`, `index_length` FROM `table_size` WHERE `table_catalog` = 'def' AND `table_schema` = 'dbstat' AND `table_name` = 'table_size' ORDER BY `ts` ASC ; +--------------+------------+---------------------+------------+-------------+--------------+ | table_schema | table_name | ts | table_rows | data_length | index_length | +--------------+------------+---------------------+------------+-------------+--------------+ | dbstat | table_size | 2024-03-09 20:01:00 | 0 | 16384 | 16384 | | dbstat | table_size | 2024-03-10 17:26:33 | 310 | 65536 | 16384 | | dbstat | table_size | 2024-03-11 08:28:12 | 622 | 114688 | 49152 | | dbstat | table_size | 2024-03-12 08:02:38 | 934 | 114688 | 49152 | | dbstat | table_size | 2024-03-13 08:08:55 | 1247 | 278528 | 81920 | +--------------+------------+---------------------+------------+-------------+--------------+
processlist SELECT connection_id, ts, time, state, SUBSTR(REGEXP_REPLACE(REPLACE(query, "\n", ' '), '\ +', ' '), 1, 64) AS query FROM processlist WHERE command != 'Sleep' AND connection_id = @connection_id ORDER BY ts ASC LIMIT 5 ; +---------------+---------------------+---------+---------------------------------+---------------------------------------------+ | connection_id | ts | time | state | query | +---------------+---------------------+---------+---------------------------------+---------------------------------------------+ | 14956 | 2024-03-09 20:21:12 | 13.042 | Waiting for table metadata lock | update test set data = 'bla' where id = 100 | | 14956 | 2024-03-09 20:22:12 | 73.045 | Waiting for table metadata lock | update test set data = 'bla' where id = 100 | | 14956 | 2024-03-09 20:23:12 | 133.044 | Waiting for table metadata lock | update test set data = 'bla' where id = 100 | | 14956 | 2024-03-09 20:24:12 | 193.044 | Waiting for table metadata lock | update test set data = 'bla' where id = 100 | | 14956 | 2024-03-09 20:25:12 | 253.041 | Waiting for table metadata lock | update test set data = 'bla' where id = 100 | +---------------+---------------------+---------+---------------------------------+---------------------------------------------+
trx_and_lck SELECT * FROM trx_and_lck\G *************************** 1. row *************************** machine_name: connection_id: 14815 trx_id: 269766 ts: 2024-03-09 20:05:57 user: root host: localhost db: test command: Query time: 41.000 running_since: 2024-03-09 20:05:16 state: Statistics info: select * from test where id = 6 for update trx_state: LOCK WAIT trx_started: 2024-03-09 20:05:15 trx_requested_lock_id: 269766:821:5:7 trx_tables_in_use: 1 trx_tables_locked: 1 trx_lock_structs: 2 trx_rows_locked: 1 trx_rows_modified: 0 lock_mode: X lock_type: RECORD lock_table_schema: test lock_table_name: test lock_index: PRIMARY lock_space: 821 lock_page: 5 lock_rec: 7 lock_data: 6 *************************** 2. row *************************** machine_name: connection_id: 14817 trx_id: 269760 ts: 2024-03-09 20:05:57 user: root host: localhost db: test command: Sleep time: 60.000 running_since: 2024-03-09 20:04:57 state: info: trx_state: RUNNING trx_started: 2024-03-09 20:04:56 trx_requested_lock_id: NULL trx_tables_in_use: 0 trx_tables_locked: 1 trx_lock_structs: 2 trx_rows_locked: 1 trx_rows_modified: 1 lock_mode: X lock_type: RECORD lock_table_schema: test lock_table_name: test lock_index: PRIMARY lock_space: 821 lock_page: 5 lock_rec: 7 lock_data: 6
metadata_lock SELECT lock_mode, ts, user, host, lock_type, table_schema, table_name, time, started, state, query FROM metadata_lock WHERE connection_id = 14347 ORDER BY started DESC LIMIT 5 ; +-------------------------+---------------------+------+-----------+----------------------+--------------+------------+-------+---------------------+----------------+------------------------------------------------------+ | lock_mode | ts | user | host | lock_type | table_schema | table_name | time | started | state | query | +-------------------------+---------------------+------+-----------+----------------------+--------------+------------+-------+---------------------+----------------+------------------------------------------------------+ | MDL_SHARED_WRITE | 2024-03-13 10:27:33 | root | localhost | Table metadata lock | test | test | 1.000 | 2024-03-13 10:27:32 | Updating | UPDATE test set data3 = MD5(id) | | MDL_BACKUP_TRANS_DML | 2024-03-13 10:27:33 | root | localhost | Backup lock | | | 1.000 | 2024-03-13 10:27:32 | Updating | UPDATE test set data3 = MD5(id) | | MDL_BACKUP_ALTER_COPY | 2024-03-13 10:22:33 | root | localhost | Backup lock | | | 0.000 | 2024-03-13 10:22:33 | altering table | ALTER TABLE test DROP INDEX ts, ADD INDEX (ts, data) | | MDL_SHARED_UPGRADABLE | 2024-03-13 10:22:33 | root | localhost | Table metadata lock | test | test | 0.000 | 2024-03-13 10:22:33 | altering table | ALTER TABLE test DROP INDEX ts, ADD INDEX (ts, data) | | MDL_INTENTION_EXCLUSIVE | 2024-03-13 10:22:33 | root | localhost | Schema metadata lock | test | | 0.000 | 2024-03-13 10:22:33 | altering table | ALTER TABLE test DROP INDEX ts, ADD INDEX (ts, data) | +-------------------------+---------------------+------+-----------+----------------------+--------------+------------+-------+---------------------+----------------+------------------------------------------------------+
global_variables SELECT variable_name, COUNT(*) AS cnt FROM global_variables GROUP BY variable_name HAVING COUNT(*) > 1 ; +-------------------------+-----+ | variable_name | cnt | +-------------------------+-----+ | innodb_buffer_pool_size | 7 | +-------------------------+-----+ SELECT variable_name, ts, variable_value FROM global_variables WHERE variable_name = 'innodb_buffer_pool_size' ; +-------------------------+---------------------+----------------+ | variable_name | ts | variable_value | +-------------------------+---------------------+----------------+ | innodb_buffer_pool_size | 2024-03-09 21:36:28 | 134217728 | | innodb_buffer_pool_size | 2024-03-09 21:40:25 | 268435456 | | innodb_buffer_pool_size | 2024-03-09 21:41:25 | 268435456 | | innodb_buffer_pool_size | 2024-03-09 21:42:25 | 268435456 | | innodb_buffer_pool_size | 2024-03-09 21:43:25 | 268435456 | | innodb_buffer_pool_size | 2024-03-09 21:44:25 | 268435456 | | innodb_buffer_pool_size | 2024-03-09 21:48:14 | 134217728 | +-------------------------+---------------------+----------------+
global_status SELECT s1.ts , s1.variable_value AS 'table_open_cache_misses' , s2.variable_value AS 'table_open_cache_hits' FROM global_status AS s1 JOIN global_status AS s2 ON s1.ts = s2.ts WHERE s1.variable_name = 'table_open_cache_misses' AND s2.variable_name = 'table_open_cache_hits' AND s1.ts BETWEEN '2024-03-13 11:55:00' AND '2024-03-13 12:05:00' ORDER BY ts ASC ; +---------------------+-------------------------+-----------------------+ | ts | table_open_cache_misses | table_open_cache_hits | +---------------------+-------------------------+-----------------------+ | 2024-03-13 11:55:47 | 1001 | 60711 | | 2024-03-13 11:56:47 | 1008 | 61418 | | 2024-03-13 11:57:47 | 1015 | 62125 | | 2024-03-13 11:58:47 | 1022 | 62829 | | 2024-03-13 11:59:47 | 1029 | 63533 | | 2024-03-13 12:00:47 | 1036 | 64237 | | 2024-03-13 12:01:47 | 1043 | 64944 | | 2024-03-13 12:02:47 | 1050 | 65651 | | 2024-03-13 12:03:47 | 1057 | 66355 | | 2024-03-13 12:04:47 | 1064 | 67059 | +---------------------+-------------------------+-----------------------+
Testen

Zur Zeit haben wir dbstat auf unseren Test- und Produktionssystemen ausgerollt um es zu testen und zu sehen ob unsere Annahmen bezüglich Stabilität und Berechnungen des Mengengerüsts zutreffen. Zudem stellen wir beim selber nutzen am besten fest, wenn es noch was fehlt oder die Handhabung unpraktisch ist (Eat your own dog food).

Quellen
Taxonomy upgrade extras: performancemonitoringperformance monitoringmetadata locklocklockingperformance_schema

Wir bauen uns ein Data Warehouse aus dem General Query Log

Oli Sennhauser - Tue, 2024-01-30 16:17

Das Design eines Data Warehouses unterscheidet sich vom relationalen Design. Data Warehouses designt man oft nach dem Konzept des Star Schemas.

Üblicherweise zäumt man beim Bau eines Data Warehouses das Pferd von hinten auf:

  • Welche Fragen soll mein Data Warehouse beantworten können?
  • Wie muss ich mein Modell designen damit sich meine Fragen einfach beantworten lassen?
  • Woher kriege ich die Daten um das Modell zu befüllen?
  • Wie befülle ich mein Model mit den Daten?

Zu Übungszwecken sind wir hier einer Fragestellung nachgegangen, welche ab und zu bei unserem Support auftaucht: Das System fängt plötzlich und unerwartet an sich ungewöhnlich zu verhalten, niemand hat was gemacht und niemand weiss warum. Beispiel bei einem Kunden letzte Woche: Um 15 Uhr fängt das System an instabil zu werden, wird anschliessend hart neu gestartet und stabilisiert sich dann ab 16 Uhr wieder...

Das einfachste wäre es, in einem solchen Fall, schnell mit dem Befehl SHOW PROCESSLIST auf die Datenbank zu schauen und dann wird oft sofort klar, wo das Problem liegt. Aber oft vergessen das die Kunden oder sie sind nicht schnell genug. Bei diesem Kunden war das General Query Log bereits eingeschaltet, das wäre also ein prima Fall für unser General Query Log Data Warehouse!

Welche Fragen soll mein Data Warehouse beantworten können?

Die generische Fragestellung für dieses Problem müsste in etwa lauten: "Wer oder was hat mein System dazu veranlasst, sich ungewöhnlich zu verhalten."

Technisch ausgedrückt würde die Frage in etwa lauten:

  • Wer: Welcher User oder Account war zur fraglichen Zeit mit wie vielen Connections auf der Datenbank drauf? Was war daran ungewöhnlich?
  • Was: Welche Abfragen liefen zur fraglichen Zeit in welchem Schema auf dem System? Welche dieser Abfragen waren ungewöhnlich?

Wie soll mein Modell aussehen?

Aus der Fragestellung können wir bereits einige Fakten und Dimensionen ableiten:

  • User oder Account (User + Host)
  • Zeit
  • Connections
  • Schema
  • Abfragen

Und daraus ergeben sich auch bereit 4 Dimensionen und die Fact-Tabelle:

Datenquelle

Woher die Daten kommen ist in diesem Fall relativ einfach zu beantworten: Der Kunde stellt seine General Query Logs zur Verfügung oder zu Testzwecken kann man auch die General Query Logs unserer eigenen Systeme verwenden.

Wie wird das Modell befüllt?

Technisch geht das unter dem Begriff ETL-Prozess (Extract-Transform-Load). In unserem Fall haben wir einen General Query Log Parser gebaut, der das General Query Log einliest, die Daten entsprechend aufbereitet und im Modell abspeichert.

Überprüfung des Modells

Und dann kommen wir auch schon zur Überprüfung des Modells. Wir haben dazu Testdaten eines unserer Systeme verwendet:

  • Welcher User war zur fraglichen Zeit auf dem System drauf?
  • Welcher User hatte zur fraglichen Zeit wie viel Connections offen?

SELECT td.time, cd.user, COUNT(*) AS count FROM connection_dim cd JOIN query_fact AS qf ON qf.connection_id = cd.connection_id JOIN time_dim AS td ON td.time_id = qf.time_id JOIN date_dim AS dd ON dd.date_id = qf.date_id WHERE td.time BETWEEN '17:00' AND '18:30' AND dd.date = '2019-08-02' GROUP BY td.time, cd.user ORDER BY td.time ASC, cd.user ; +----------+---------------+-------+ | time | user | count | +----------+---------------+-------+ | 17:58:00 | UNKNOWN USER | 1 | | 17:59:00 | brman | 58 | | 17:59:00 | brman_catalog | 18 | | 17:59:00 | root | 5 | | 18:00:00 | brman | 296 | | 18:00:00 | brman_catalog | 7 | | 18:00:00 | root | 3 | | 18:01:00 | brman_catalog | 18 | | 18:01:00 | root | 3 | | 18:06:00 | brman | 266 | | 18:06:00 | brman_catalog | 6 | | 18:07:00 | brman | 88 | | 18:07:00 | brman_catalog | 7 | | 18:10:00 | brman | 211 | | 18:10:00 | brman_catalog | 18 | | 18:10:00 | root | 4 | | 18:11:00 | brman | 141 | | 18:11:00 | root | 3 | | 18:13:00 | brman | 4 | | 18:14:00 | brman | 348 | | 18:17:00 | brman | 354 | | 18:17:00 | brman_catalog | 12 | | 18:17:00 | root | 1 | +----------+---------------+-------+
  • Welcher Account war zur fraglichen Zeit auf dem System drauf?
  • Welcher Account hatte zur fraglichen Zeit wie viel Connections offen?

SELECT td.time, cd.user, cd.hostname, COUNT(*) AS count FROM connection_dim cd JOIN query_fact AS qf ON qf.connection_id = cd.connection_id JOIN time_dim AS td ON td.time_id = qf.time_id JOIN date_dim AS dd ON dd.date_id = qf.date_id WHERE td.time BETWEEN '17:00' AND '18:30' AND dd.date = '2019-08-02' GROUP BY td.time, cd.user, cd.hostname ORDER BY td.time ASC, cd.user ; +----------+---------------+--------------+-------+ | time | user | hostname | count | +----------+---------------+--------------+-------+ | 17:58:00 | UNKNOWN USER | UNKNOWN HOST | 1 | | 17:59:00 | brman | localhost | 58 | | 17:59:00 | brman_catalog | localhost | 18 | | 17:59:00 | root | localhost | 5 | | 18:00:00 | brman | localhost | 296 | | 18:00:00 | brman_catalog | localhost | 7 | | 18:00:00 | root | localhost | 3 | | 18:01:00 | brman_catalog | localhost | 18 | | 18:01:00 | root | localhost | 3 | | 18:06:00 | brman | localhost | 266 | | 18:06:00 | brman_catalog | localhost | 6 | | 18:07:00 | brman | localhost | 88 | | 18:07:00 | brman_catalog | localhost | 7 | | 18:10:00 | brman | localhost | 211 | | 18:10:00 | brman_catalog | localhost | 18 | | 18:10:00 | root | localhost | 4 | | 18:11:00 | brman | localhost | 141 | | 18:11:00 | root | localhost | 3 | | 18:13:00 | brman | localhost | 4 | | 18:14:00 | brman | localhost | 348 | | 18:17:00 | brman | localhost | 354 | | 18:17:00 | brman_catalog | localhost | 12 | | 18:17:00 | root | localhost | 1 | +----------+---------------+--------------+-------+
  • Was war daran ungewöhnlich?

SELECT cd.user, td.time, COUNT(*) AS count FROM connection_dim cd JOIN query_fact AS qf ON qf.connection_id = cd.connection_id JOIN time_dim AS td ON td.time_id = qf.time_id JOIN date_dim AS dd ON dd.date_id = qf.date_id WHERE td.time BETWEEN '17:00' AND '18:30' AND dd.date = '2019-08-02' GROUP BY td.time, cd.user ORDER BY cd.user ASC, td.time ASC ; +---------------+----------+-------+ | user | time | count | +---------------+----------+-------+ | brman | 17:59:00 | 58 | | brman | 18:00:00 | 296 | | brman | 18:06:00 | 266 | | brman | 18:07:00 | 88 | | brman | 18:10:00 | 211 | | brman | 18:11:00 | 141 | | brman | 18:13:00 | 4 | | brman | 18:14:00 | 348 | | brman | 18:17:00 | 354 | | brman_catalog | 17:59:00 | 18 | | brman_catalog | 18:00:00 | 7 | | brman_catalog | 18:01:00 | 18 | | brman_catalog | 18:06:00 | 6 | | brman_catalog | 18:07:00 | 7 | | brman_catalog | 18:10:00 | 18 | | brman_catalog | 18:17:00 | 12 | | root | 17:59:00 | 5 | | root | 18:00:00 | 3 | | root | 18:01:00 | 3 | | root | 18:10:00 | 4 | | root | 18:11:00 | 3 | | root | 18:17:00 | 1 | | UNKNOWN USER | 17:58:00 | 1 | +---------------+----------+-------+

Man könnte hier z.B. ableiten, dass der User brman relativ viele Verbindung in der fraglichen Zeit offen hatte. Ob das ungewöhnlich ist, dazu haben wir zu wenige Daten bzw. dazu ist der Zeitraum zu klein.

  • Welche Abfragen liefen zur fraglichen Zeit in welchem Schema auf dem System?
  • Welche dieser Abfragen waren ungewöhnlich?

SELECT sd.schema_name, td.time, SUBSTR(std.statement_text, 1, 128) AS query FROM query_fact AS qf JOIN time_dim AS td ON td.time_id = qf.time_id JOIN schema_dim AS sd ON sd.schema_id = qf.schema_id JOIN statement_dim AS std ON std.statement_id = qf.statement_id WHERE td.time BETWEEN '17:00' AND '18:30' AND sd.schema_name = 'brman_catalog' AND std.command = 'Query' ORDER BY td.time, qf.statement_id LIMIT 10 ; +---------------+----------+----------------------------------------------------------------------------------------------------------------------------------+ | schema_name | time | query | +---------------+----------+----------------------------------------------------------------------------------------------------------------------------------+ | brman_catalog | 17:59:00 | SET NAMES `utf8` | | brman_catalog | 17:59:00 | SELECT COUNT ( * ) AS `cnt` FROM `information_schema` . `tables` WHERE `table_schema` = ? AND TABLE_NAME = ? | | brman_catalog | 17:59:00 | SELECT COUNT ( * ) AS `cnt` FROM `information_schema` . `tables` WHERE `table_schema` = ? AND TABLE_NAME = ? | | brman_catalog | 17:59:00 | CREATE TABLE `metadata` ( `id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT , `key` VARCHARACTER (?) NOT NULL , `value` VARCHARACTER | | brman_catalog | 17:59:00 | INSERT INTO `metadata` ( `key` , `value` ) VALUES (...) | | brman_catalog | 17:59:00 | INSERT INTO `metadata` ( `key` , `value` ) VALUES (...) | | brman_catalog | 17:59:00 | CREATE TABLE `backups` ( `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT , `instance_name` VARCHARACTER (?) NOT NULL , `start_ts` | | brman_catalog | 17:59:00 | CREATE TABLE `backup_details` ( `backup_id` INTEGER UNSIGNED NOT NULL , `hostname` VARCHARACTER (?) NULL , `binlog_file` VARCHAR | | brman_catalog | 17:59:00 | CREATE TABLE `files` ( `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT , `schema_name` VARCHARACTER (?) NULL , `original_name` VAR | | brman_catalog | 17:59:00 | CREATE TABLE `binary_logs` ( `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT , `filename` VARCHARACTER (?) NOT NULL , `begin_ts` I | +---------------+----------+----------------------------------------------------------------------------------------------------------------------------------+
Verbesserungsvorschläge

Anhand dieser ersten Iteration des Modells sieht man auch schon, welche Fragen das Modell noch nicht beantworten kann oder wo das Modell zu ungenau ist. Dies kann dann in einer zweiten Rund nachgebessert werden....

Beispiele hierzu sind:

  • Die Granulariät der Dimension time ist mit Minutengenauigkeit möglicherweise zu grob. Sekundengenauigkeit wäre sinnvoller?
  • Die Frage, wie lange eine Connection offen war lässt sich nich so einfach beantworten. Ev. wäre hier eine weiter Fact Tabelle angebracht? SELECT cd.connection_number, cd.user, cd.hostname, tdf.time AS time_from, tdt.time AS time_to, (UNIX_TIMESTAMP(tdt.time) - UNIX_TIMESTAMP(tdf.time)) AS duration FROM connection_dim AS cd JOIN query_fact AS qf1 ON cd.connection_id = qf1.connection_id JOIN time_dim AS tdf ON tdf.time_id = qf1.time_id JOIN statement_dim AS sdf ON sdf.statement_id = qf1.statement_id JOIN query_fact AS qf2 ON cd.connection_id = qf2.connection_id JOIN time_dim AS tdt ON tdt.time_id = qf2.time_id JOIN statement_dim AS sdt ON sdt.statement_id = qf2.statement_id WHERE tdf.time BETWEEN '17:00' AND '18:30' AND sdf.command = 'Connect' AND sdt.command = 'Quit' AND (UNIX_TIMESTAMP(tdt.time) - UNIX_TIMESTAMP(tdf.time)) > 0 ORDER BY tdf.time ; +-------------------+-------+-----------+-----------+----------+----------+ | connection_number | user | hostname | time_from | time_to | duration | +-------------------+-------+-----------+-----------+----------+----------+ | 211 | brman | localhost | 17:59:00 | 18:00:00 | 60 | | 215 | root | localhost | 18:00:00 | 18:17:00 | 1020 | | 219 | brman | localhost | 18:06:00 | 18:07:00 | 60 | | 225 | brman | localhost | 18:10:00 | 18:11:00 | 60 | | 226 | brman | localhost | 18:13:00 | 18:14:00 | 60 | +-------------------+-------+-----------+-----------+----------+----------+
  • Spannend wäre natürlich jetzt noch, wenn man eine KI auf das Problem ansetzt. Wie traniert man sie richtig und findet sie das Problem, wenn sie trainiert wurde?

Soweit die kleine Spielerei zum Bau eines Data Warehouses...

Taxonomy upgrade extras: data warehousegeneral query log

InnoDB Deadlock bei SELECT? Nicht möglich! Oder doch?

Oli Sennhauser - Sun, 2023-11-19 16:18
Einleitung

Kurz vorab zwei Punkte:

  1. Ein Deadlock ist eine Zustand, in welchem 2 unterschiedliche Transaktionen nicht mehr in der Lage sind weiter zu arbeiten, weil jede Transaktion jeweils einen Lock hält, welche die andere Transaktion gerade bräuchte. Weil jetzt beide Transaktionen jeweils darauf warten, bis die andere Transaktion ihren Lock wieder frei gibt, wird keine von beiden Transaktionen ihre jeweiligen Locks wieder frei geben. Und das würde bis zum Sankt-Nimmerleins-Tag andauern. Um das zu vermeiden schreitet die MariaDB Instanz ein und killt kurzerhand diejenige Transaktion, die weniger Arbeit geleistet hat. Die Applikation kriegt darauf hin eine Deadlock Fehlermeldung vom Typ:
    ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
  2. Im MariaDB Ökosystem gilt allgemein das Mantra, dass ein SELECT keine Locks verursacht (Ausnahme: FOR UPDATE oder LOCK IN SHARE MODE) und somit auch nicht Teil eines Deadlocks sein kann.

  3. Das Problem

    Ein langjähriger Kunde kommt zum FromDual remote-DBA Team mit der Bitte, eine Deadlock-Situation zu erklären:

    Hallo FromDual Team,
    ich brauche mal wieder euer Fachwissen zum Thema Deadlocks.
    Wann würde es Euch passen?


    Die Situation ist folgende: Transaktion 1 besteht aus einem simplen INSERT. Transaktion 2 besteht aus einem SELECT. Das dürfte eigentlich KEINEN Deadlock verursachen!

    Zuerst prüfen wir folgende Punkte ab:

  • Sind alle Tabellen, die durch diese Abfragen betroffen sind, sauber indexiert? Jawohl sind sie. Die Queries laufen alle perfekt!
  • Ist die SELECT Abfragen eventuell Teil einer grösseren Transaktion (NICHT Auto-Commit Transaktion) und daher nicht die eigentlich Ursache für den Deadlock? Nein, ist sie nicht. Es handelt sich um Auto-Commit Transaktionen.

Was nun? Was man zur Erläuterung noch sagen muss: Das SELECT wird mit einer sehr hohen Kadenz, also so ca. alle 5 ms abgesetzt!

Dass der INSERT Locks erzeugt ist klar. Wird ja auch angezeigt. Aber warum erzeugt der SELECT Befehl Locks? Diese werden ebenfalls angezeigt!

Also versuchen wir das Problem in Einzelschritte runter zu brechen.

Der Lösungsweg

Das Query sieht wie folgt aus:

SQL> SET @id = (SELECT id FROM test WHERE id = 3);

Wenn wir dieses Query in eine explizite Transaktion packen, können wir die Locks sogar sehen:

SQL> START TRANSACTION; SQL> SET @id = (SELECT id FROM test WHERE id = 3);

und in einer zweiten Session:

SQL> SELECT * FROM information_schema.INNODB_TRX\G *************************** 1. row *************************** trx_id: 0 trx_state: RUNNING trx_started: 2023-11-19 15:27:09 trx_requested_lock_id: NULL trx_wait_started: NULL trx_weight: 2 trx_mysql_thread_id: 3765 trx_query: NULL trx_operation_state: trx_tables_in_use: 0 trx_tables_locked: 1 trx_lock_structs: 2 trx_lock_memory_bytes: 1128 trx_rows_locked: 1 trx_rows_modified: 0 trx_concurrency_tickets: 0 trx_isolation_level: REPEATABLE READ trx_unique_checks: 1 trx_foreign_key_checks: 1 trx_last_foreign_key_error: NULL trx_is_read_only: 0 trx_autocommit_non_locking: 0

Leider sehen wir nicht welche Art von Lock (IS) es ist, da die View INNODB_LOCKS leer ist.

Die Lösung

Wenn wir den selben Versuch mit "normalen" SELECTs machen:

SQL> START TRANSACTION; SELECT id FROM test WHERE id = 3;

oder

SQL> START TRANSACTION; SELECT id INTO @id FROM test WHERE id = 3;

sehen wir KEINE Locks:

SQL> SELECT * FROM information_schema.INNODB_TRX\G *************************** 1. row *************************** trx_id: 0 trx_state: RUNNING trx_started: 2023-11-19 15:31:35 trx_requested_lock_id: NULL trx_wait_started: NULL trx_weight: 0 trx_mysql_thread_id: 3765 trx_query: NULL trx_operation_state: trx_tables_in_use: 0 trx_tables_locked: 0 trx_lock_structs: 0 trx_lock_memory_bytes: 1128 trx_rows_locked: 0 trx_rows_modified: 0 trx_concurrency_tickets: 0 trx_isolation_level: REPEATABLE READ trx_unique_checks: 1 trx_foreign_key_checks: 1 trx_last_foreign_key_error: NULL trx_is_read_only: 0 trx_autocommit_non_locking: 0

Es scheint also so zu sein, dass das Konstrukt SET @id = (...) diesen IS Lock verursacht. Der Kunde schreibt seine Applikation um und kurz darauf erhalten wir folgende Meldung:

Hallo FromDual Team,
Euer Tipp war goldrichtig.
Seit Freitag Mittag keine Deadlocks mehr.
Danke und schönes Wochenende.


Weitere geklärte Fragen

MySQL 8.0 verhält sich gleich? Ja, genau gleich.

Webinar: Upgrade Ihres MySQL 5.7 Galera Clusters auf MySQL 8.0 ohne Ausfallzeiten

Oli Sennhauser - Thu, 2023-11-16 11:34

Sie haben sicher schon davon gehört, dass MySQL 5.7 im Oktober 2023 das End of Life (EOL) erreicht hat. In diesem Webinar zeigen wir Ihnen, dass die Migration von MySQL 5.7 Galera Cluster nicht schwierig ist. MySQL 8.0 ist seit 5 Jahren allgemein verfügbar, und das Galera Cluster für MySQL 8.0 hat sich seit über 3 Jahren im Markt bewährt. Es ist also wirklich an der Zeit, sich auf die Migration vorzubereiten.

Im ersten Webinar dieser Reihe werden wir uns mit den neuen Funktionen von Galera Cluster mit MySQL 8 befassen:

  • Die neuen Funktionen von Galera Cluster für MySQL 8, von denen Sie profitieren können, einschliesslich der in der Galera Cluster Enterprise Edition (EE) verfügbaren Funktionen
  • Wie man eine Migration von MySQL 5.7 auf MySQL 8.0 plant
  • Dinge, die vor der Migration getestet werden sollten
  • Häufige Fallstricke bei einer solchen Migration
  • Wie Sie sicherstellen, dass Ihr Galera Cluster während der Migration ohne Ausfallzeiten weiterläuft

Das Webinar findet am Freitag, 17. November 2023, 9:00 - 10:00 MEZ statt.

Für das Webinar können Sie sich hier registrieren.

Präsentatoren:
Oli Sennhauser, Fromdual
Sakari Keskitalo, Codership, the developers of Galera Cluster

Über Galera Cluster Support Subskriptionen und Migrations-Support informieren wir sie gerne persönlich. Bitte wenden Sie sich an uns, wir helfen Ihnen gerne weiter.

Bitte senden Sie Ihre Fragen, Anmerkungen und Ihr Feedback an: contact@fromdual.com

Mit freundlichen Grüssen,
Ihr FromDual Team

MariaDB und MySQL Schulungsprogramm 2023

Oli Sennhauser - Tue, 2023-01-17 16:24

Auch im Jahr 2023 haben Sie wieder die Möglichkeit, sich bei unseren 3 Schulungspartnern in Essen, Köln und Berlin MariaDB und MySQL seitig fit zu machen.

Folgende Termine können wir Ihnen für das Jahr 2023 schon jetzt anbieten:

  • 30. Januar bis 3. Februar 2023: MariaDB/MySQL für Fortgeschrittene, GFU Schulungszentrum, Köln
  • 27. Februar bis 3. März 2023: MariaDB/MySQL für Fortgeschrittene, Heinlein Akademie, Berlin
  • 20. bis 24. März 2023: MariaDB/MySQL für Fortgeschrittene, Linuxhotel, Essen
  • 27. bis 29. März 2023: Galera Cluster für MariaDB/MySQL, GFU Schulungszentrum, Köln
  • 24. bis 28. April 2023: MariaDB/MySQL für Fortgeschrittene, GFU Schulungszentrum, Köln
  • 3. bis 5. Mai 2023: Galera Cluster für MariaDB/MySQL, Linuxhotel, Essen
  • 15. bis 17. Mai 2023: Galera Cluster für MariaDB/MySQL, Heinlein Akademie, Berlin
  • 5. bis 7. Juni 2023: Galera Cluster für MariaDB/MySQL, GFU Schulungszentrum, Köln
  • 24. bis 28. Juli 2023: MariaDB/MySQL für Fortgeschrittene, GFU Schulungszentrum, Köln
  • 11. bis 15. September 2023: MariaDB/MySQL für Fortgeschrittene, Heinlein Akademie, Berlin
  • 25. bis 27. September 2023: Galera Cluster für MariaDB/MySQL, GFU Schulungszentrum, Köln
  • 23. bis 27. Oktober 2023: MariaDB/MySQL für Fortgeschrittene, GFU Schulungszentrum, Köln
  • 6. bis 8. November 2023: Galera Cluster für MariaDB/MySQL, Linuxhotel, Essen
  • 13. bis 15. November 2023: Galera Cluster für MariaDB/MySQL, Heinlein Akademie, Berlin
  • 27. November bis 1. Dezember 2023: MariaDB/MySQL für Fortgeschrittene, Linuxhotel, Essen
  • 4. bis 6. Dezember 2023: Galera Cluster für MariaDB/MySQL, GFU Schulungszentrum, Köln

Diese Schulungen können online gebucht werden.

Weitere Termine können während des Jahres noch hinzukommen.

Bei Fragen, oder wenn Sie Spezialwünsche haben, stehen wir Ihnen gerne mit Rat und Tat zur Seite! Zögern Sie nicht, uns zu kontaktieren, wir helfen Ihnen gerne per eMail weiter.

Bei allen weiteren Galera, MariaDB und MySQL Problemen oder Fragen unterstützen wir Sie natürlich ebenfalls gerne!

Mit freundlichen Grüssen,
Ihr FromDual Team

Quellen: Bild von StartupStockPhotos auf Pixabay

Taxonomy upgrade extras: schulungtrainingseminar2023mysqlmariadbgaleramysql schulungmariadb schulunggalera schulung

Zusätzliche Galera Cluster Schulung im Dezember 2022

Oli Sennhauser - Mon, 2022-10-10 16:40

Aufgrund der grossen Nachfrage bieten wir vom 19. bis 22. Dezember 2022 ein zusätzliches Galera Cluster Seminar an. Dies in Zusammenarbeit mit unserem Schulungspartner, der GfU Cyrus AG in Köln.

Dieses Seminar wird remote durchgeführt. Sie können an diesem Seminar also bequem von zuhause aus teilnehmen...

Eine Seminarübersicht finden Sie hier.

Sie können das Seminar einfach online buchen.

Falls Sie an diesen Terminen verhindert sind, haben wir bereits einige Termine für das Jahr 2023 für Sie in Planung.


Ihr FromDual Schulungsteam

Taxonomy upgrade extras: galeraschulunggfu20232022seminar

FromDual Seminarprogramm 2022 für MariaDB und MySQL ist online

Oli Sennhauser - Fri, 2021-10-15 10:27

Das FromDual Seminarprogramm für 2022 steht. Mit unseren Schulungspartnern Linuxhotel in Essen, GfU Cyrus in Köln sowie der Heinlein Akademie in Berlin bieten wir auch 2022 wieder zahlreiche MariaDB und MySQL Schulungen an:


Im Seminar für Fortgeschrittene nehmen wir uns Themen wie Backup/Restore, Master/Slave Replikation, Galera Cluster sowie Datenbank-Konfigurations-Tuning und SQL Query Tuning vor und üben die einzelnen Punkte praktisch.

Im Galera Cluster Seminar nehmen wir alle Aspekte des Aufbaus und des Betriebs eines Galera Clusters durch und praktizieren das Ganz anschliessend ausführlich.

Den Umständen entsprechend werden die Seminare entweder vor Ort, remote oder hybrid durchgeführt.

Bei allfälligen Fragen bitten wir Sie, mit uns oder unseren Schulungspartnern Kontakt aufzunehmen und diese zu klären.

Taxonomy upgrade extras: trainingschulungseminarmysqlmariadbgalera2022replikation

MariaDB und MySQL Schulungsprogramm 2021

Oli Sennhauser - Wed, 2021-02-03 09:40

Das FromDual MariaDB und MySQL Schulungsprogramm 2021 steht jetzt zur Verfügung.

Die Schulungen werden remote oder auch vor Ort bei unseren drei Schulungspartnern in Essen, Köln und Berlin angeboten. Oder ein Seminar, ganz persönlich, remote oder vor Ort, bei Ihnen in der Firma.

Wer neu in der Thematik MariaDB oder MySQL ist, für den ist das Seminar MariaDB/MySQL für Einsteiger vorgesehen. Falls Sie sich mit MariaDB oder MySQL schon auskennen empfehlen wir Ihnen das Seminar MariaDB/MySQL für Fortgeschrittene. Wenn Sie Entwickler und nicht Administrator sind, dann ist das Seminar MariaDB/MySQL für Entwickler genau das richtige für Sie. Und sollten Sie mit dem Gedanken spielen, sich einen Galera Cluster anzulachen, dann zeigen wir Ihnen im Seminar Galera Cluster für MariaDB/MySQL wie man das richtig macht und wo Sie dabei aufpassen sollten.

Die geplanten Schulungstermine für das Jahr 2021 finden Sie, immer auf dem neusten Stand, auf unserer Website: MariaDB und MySQL Schulungstermine.

Taxonomy upgrade extras: schulungtrainingmysql schulungmariadb schulunggalera cluster schulung

MariaDB/MySQL Datenbank-Administrator/in gesucht

Oli Sennhauser - Thu, 2020-11-19 15:48

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

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

Auszug aus der Original-Stellenausschreibung:

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

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

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

Taxonomy upgrade extras: mariadbmysqljob descriptionjob

Programm des Vorarlberger LinuxDay 2020 wurde veröffentlicht

Oli Sennhauser - Fri, 2020-09-18 10:00

Das diesjährige Programm des Vorarlberger LinuxDay 2020 wurde veröffentlicht!


Die Vorträge decken Themen querbeet aus dem Opensource Umfeld ab: Linux Mint, Smart Home, Bootloader, Regolith Linux, LibreOffice, MariaDB/MySQL, Forensik, PostgreSQL, Patch-Management, Softwareverteilung, Backup und Videokonferenzdienste.

Im Unterschied zu anderen Jahren findet dieses Jahr die Konferenz am 10. Oktober 2020 rein virtuell statt. Die Teilnahme lohnt sich!

Für MariaDB/MySQL Enthusiasten können wir den Vortrag MariaDB/MySQL Stolperfallen und wie komme ich da wieder raus empfehlen.

Taxonomy upgrade extras: 2020konferenzlinux

Programm der DOAG 2020 Konferenz veröffentlicht

Oli Sennhauser - Fri, 2020-09-11 10:56

Das diesjährige Programm der DOAG 2020 Konferenz + Ausstellung ist veröffentlicht!



Das Programm ist abwechslungsreich und interessant, wenn auch etwas kleiner als andere Jahre. Die Teilnahme lohnt sich!

Aus MariaDB/MySQL Sicht ist vor allem der Mittwoch, im Raum Kiew (55 Plätze), mit den üblichen Verdächtigen, interessant:

09:00 - 09:45 Sessionkeynote: The new MySQL Database and MySQL Analytics Service - Extreme Performance, Cloud Scale, Significant Cost Savings Nipun Agarwal 10:00 - 10:45 MariaDB/MySQL Stolperfallen und wie komme ich da wieder raus Oliver Sennhauser 11:00 - 11:45 Wie ein Ei dem anderen... MySQL ReplicaSets Matthias Jung 12:00 - 12:45 MySQL 8: Ein Statusbericht 3 Jahre nach dem ersten Release Carsten Thalheimer

Die Konferenz findet dieses Jahr von Dienstag, dem 17. November 2020 bis Donnerstag, dem 19. November 2020 in Nürnberg und in Teilen auch als online Veranstaltung statt. Mehr dazu auf der Website der Konferenz.

Taxonomy upgrade extras: doag2020konferenz

FromDual Schulungen trotz Corona - einfach online und remote

Oli Sennhauser - Mon, 2020-03-23 15:43

Aufgrund der aktuellen Corona-Pandemie sind sämtliche FromDual vor Ort Schulungen, Schulungen bei unseren Schulungspartnern sowie FromDual Beratungseinsätze bis auf weiteres sistiert.

Damit Sie diesen Frühling aber nicht ganz ohne Weiterbildung auskommen müssen, sind wir bereits seit letzter Woche daran, alternative Möglichkeiten mittels online Schulungen zu testen. Erste Testschulungen wurden bereits Ende letzter Woche und anfangs dieser Woche durchgeführt.

Möglicherweise bietet sich Ihnen in den nächsten Wochen die Möglichkeit, aus dem Homeoffice die eine oder andere unserer online-Schulungen zu besuchen. Voraussichtlich betroffen sind die folgenden Schulungstermine:

  • 2. bis 4. April: Galera Cluster für MariaDB/MySQL im Linuxhotel in Essen
  • 23. bis 24. April: Galera Cluster für MariaDB/MySQL in der Heinlein Academy in Berlin
  • 4. bis 8. Mai: MariaDB/MySQL für Fortgeschrittene bei der GfU Cyrus in Köln
  • 11. bis 15. Mai: MariaDB/MySQL für Fortgeschrittene in der Heinlein Academy in Berlin

Ob es auch noch spätere Schulungstermine betrifft werden wir sehen...

Remote geht es besser - FromDual remote-DBA Dienstleistungen

Die Corona-Pandemie verursacht möglicherweise auch ein anderes oder neues Lastmuster auf Ihrer Datenbank.

  • Buchungen werden storniert (Reiseportale),
  • Rabatt-Aktionen gestartet (Outdoor-Aktivitäten),
  • es wird vermehrt online eingekauft (Webshops),
  • es wird vermehrt kommuniziert (VoIP, Video-Conferencing, Screen Sharing) oder auch
  • die Plattformen des Gesundheitswesen werden heftiger als sonst in Anspruch genommen.
  • etc.

Falls diese Situation bei Ihnen zu betrieblichen Problemen oder Performance-Engpässen führt, helfen wir Ihnen auch gerne mit einem remote-DBA Einsatz anstelle eines vor Ort Beratungseinsatzes weiter. Wir haben diese Technologien bereits seit Jahren im Einsatz und zeigen Ihnen auch gerne remote, wie Sie Ihre Probleme in den Griff kriegen.

Taxonomy upgrade extras: schulungremote-dbaremoteberatungconsultingtraining

FromDual Schulungen trotz Corona - einfach online und remote

Oli Sennhauser - Mon, 2020-03-23 15:43

Aufgrund der aktuellen Corona-Pandemie sind sämtliche FromDual vor Ort Schulungen, Schulungen bei unseren Schulungspartnern sowie FromDual Beratungseinsätze bis auf weiteres sistiert.

Damit Sie diesen Frühling aber nicht ganz ohne Weiterbildung auskommen müssen, sind wir bereits seit letzter Woche daran, alternative Möglichkeiten mittels online Schulungen zu testen. Erste Testschulungen wurden bereits Ende letzter Woche und anfangs dieser Woche durchgeführt.

Möglicherweise bietet sich Ihnen in den nächsten Wochen die Möglichkeit, aus dem Homeoffice die eine oder andere unserer online-Schulungen zu besuchen. Voraussichtlich betroffen sind die folgenden Schulungstermine:

  • 2. bis 4. April: Galera Cluster für MariaDB/MySQL im Linuxhotel in Essen
  • 23. bis 24. April: Galera Cluster für MariaDB/MySQL in der Heinlein Academy in Berlin
  • 4. bis 8. Mai: MariaDB/MySQL für Fortgeschrittene bei der GfU Cyrus in Köln
  • 11. bis 15. Mai: MariaDB/MySQL für Fortgeschrittene in der Heinlein Academy in Berlin

Ob es auch noch spätere Schulungstermine betrifft werden wir sehen...

Remote geht es besser - FromDual remote-DBA Dienstleistungen

Die Corona-Pandemie verursacht möglicherweise auch ein anderes oder neues Lastmuster auf Ihrer Datenbank.

  • Buchungen werden storniert (Reiseportale),
  • Rabatt-Aktionen gestartet (Outdoor-Aktivitäten),
  • es wird vermehrt online eingekauft (Webshops),
  • es wird vermehrt kommuniziert (VoIP, Video-Conferencing, Screen Sharing) oder auch
  • die Plattformen des Gesundheitswesen werden heftiger als sonst in Anspruch genommen.
  • etc.

Falls diese Situation bei Ihnen zu betrieblichen Problemen oder Performance-Engpässen führt, helfen wir Ihnen auch gerne mit einem remote-DBA Einsatz anstelle eines vor Ort Beratungseinsatzes weiter. Wir haben diese Technologien bereits seit Jahren im Einsatz und zeigen Ihnen auch gerne remote, wie Sie Ihre Probleme in den Griff kriegen.

Taxonomy upgrade extras: schulungremote-dbaremoteberatungconsultingtraining

FromDual Schulungen trotz Corona - einfach online und remote

Oli Sennhauser - Mon, 2020-03-23 15:43

Aufgrund der aktuellen Corona-Pandemie sind sämtliche FromDual vor Ort Schulungen, Schulungen bei unseren Schulungspartnern sowie FromDual Beratungseinsätze bis auf weiteres sistiert.

Damit Sie diesen Frühling aber nicht ganz ohne Weiterbildung auskommen müssen, sind wir bereits seit letzter Woche daran, alternative Möglichkeiten mittels online Schulungen zu testen. Erste Testschulungen wurden bereits Ende letzter Woche und anfangs dieser Woche durchgeführt.

Möglicherweise bietet sich Ihnen in den nächsten Wochen die Möglichkeit, aus dem Homeoffice die eine oder andere unserer online-Schulungen zu besuchen. Voraussichtlich betroffen sind die folgenden Schulungstermine:

  • 2. bis 4. April: Galera Cluster für MariaDB/MySQL im Linuxhotel in Essen
  • 23. bis 24. April: Galera Cluster für MariaDB/MySQL in der Heinlein Academy in Berlin
  • 4. bis 8. Mai: MariaDB/MySQL für Fortgeschrittene bei der GfU Cyrus in Köln
  • 11. bis 15. Mai: MariaDB/MySQL für Fortgeschrittene in der Heinlein Academy in Berlin

Ob es auch noch spätere Schulungstermine betrifft werden wir sehen...

Remote geht es besser - FromDual remote-DBA Dienstleistungen

Die Corona-Pandemie verursacht möglicherweise auch ein anderes oder neues Lastmuster auf Ihrer Datenbank.

  • Buchungen werden storniert (Reiseportale),
  • Rabatt-Aktionen gestartet (Outdoor-Aktivitäten),
  • es wird vermehrt online eingekauft (Webshops),
  • es wird vermehrt kommuniziert (VoIP, Video-Conferencing, Screen Sharing) oder auch
  • die Plattformen des Gesundheitswesen werden heftiger als sonst in Anspruch genommen.
  • etc.

Falls diese Situation bei Ihnen zu betrieblichen Problemen oder Performance-Engpässen führt, helfen wir Ihnen auch gerne mit einem remote-DBA Einsatz anstelle eines vor Ort Beratungseinsatzes weiter. Wir haben diese Technologien bereits seit Jahren im Einsatz und zeigen Ihnen auch gerne remote, wie Sie Ihre Probleme in den Griff kriegen.

Taxonomy upgrade extras: schulungremote-dbaremoteberatungconsultingtraining

Eher Finger weg: innodb_deadlock_detect

Oli Sennhauser - Fri, 2020-03-06 15:27

Kürzlich haben wir bei einem unserer Kunden, der gelegentlich massive Datenbankprobleme hat, bei der Durchsicht der MySQL Konfigurationsdatei (my.cnf) festgestellt, dass er die InnoDB Deadlock-Erkennung (innodb_deadlock_detect) deaktiviert hatte.

Da wir davon bisher immer abgeraten haben, ich aber noch nie konkret über dieses Problem gestolpert bin, bin ich der Sache noch etwas nachgegangen und habe zur Variable innodb_deadlock_detect recherchiert.

Die MySQL Dokumentation sagt dazu folgendes [1]:

Disabling Deadlock Detection
On high concurrency systems, deadlock detection can cause a slowdown when numerous threads wait for the same lock. At times, it may be more efficient to disable deadlock detection and rely on the innodb_lock_wait_timeout setting for transaction rollback when a deadlock occurs. Deadlock detection can be disabled using the innodb_deadlock_detect configuration option.

Und zum Parameter innodb_deadlock_detect selbst [2]:

This option is used to disable deadlock detection. On high concurrency systems, deadlock detection can cause a slowdown when numerous threads wait for the same lock. At times, it may be more efficient to disable deadlock detection and rely on the innodb_lock_wait_timeout setting for transaction rollback when a deadlock occurs.

Das Problem ist, dass jedes mal, wenn MySQL einen (Row) Lock oder Table Lock macht, überprüft wird, ob dieser Lock einen Deadlock verursacht. Was entsprechend teuer ist. Diese Feature wurde übrigens von Facebook entwickelt [3].

Die entsprechenden Funktionen sind in [4] zu finden:

class DeadlockChecker, method check_and_resolve (DeadlockChecker::check_and_resolve) Every InnoDB (row) Lock (for mode LOCK_S or LOCK_X) and type ORed with LOCK_GAP or LOCK_REC_NOT_GAP, ORed with LOCK_INSERT_INTENTION Enqueue a waiting request for a lock which cannot be granted immediately. lock_rec_enqueue_waiting()

und

Every (InnoDB) Table Lock Enqueues a waiting request for a table lock which cannot be granted immediately. Checks for deadlocks. lock_table_enqueue_waiting()

Das heisst jetzt, wenn die Variable innodb_deadlock_detect eingeschaltet ist (= default) wird bei jedem Lock (Row oder Table) überprüft, ob dadurch ein Deadlock entsteht. Wenn die Variable ausgeschaltet ist, wird diese Überprüfung NICHT ausgeführt (was schneller ist) und die Transaktion bleibt im (Dead-)Lock hängen bis der Lock frei gegeben wird oder die Zeit innodb_lock_wait_timeout (default 50 Sekunden) überschritten ist. Dann schlägt der InnoDB Lock Wait Timeout (Detektor?) zu.

SQL> SHOW GLOBAL VARIABLES LIKE 'innodb_lock_wait%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | innodb_lock_wait_timeout | 50 | +--------------------------+-------+

Das bedeutet, das Deaktivieren der InnoDB Deadlock Detektion ist interessant, wenn Sie sehr viele (wie Facebook!?!) kurze/kleine Transaktionen haben, bei welchen wenig bis keine Konflikte auftreten.
Im Weiteren wird empfohlen, gleichzeitig die innodb_lock_wait_timeout auf einen sehr geringen Wert (wenige Sekunden) einzustellen.

Da die meisten unserer Kunden aber nicht in die Kragenweite Facebook passen und tendenziell eher nicht viele gleichzeitige kurze/kleine Transaktionen haben sondern wenig lange Transaktionen (mit wahrscheinlich vielen Locks und daher einer grossen Deadlock-Wahrscheinlichkeit), kann ich mir durchaus vorstellen, dass das deaktivieren diese Parameters für das Verschlucken (Locks stauen auf) des Kundensystems verantwortlich ist, was anschliessen dazu führt, dass max_connections erreicht wird und schliesslich gar nichts mehr geht.

Daher würde ich dringend empfehlen, die InnoDB Deadlock Detektierung aktiviert zu lassen. Ausser man weiss genau, was man tut (nach ca. 2 Wochen testen und messen).

Literatur Taxonomy upgrade extras: innodbdeadlocklockperformancelockingblock

Eher Finger weg: innodb_deadlock_detect

Oli Sennhauser - Fri, 2020-03-06 15:27

Kürzlich haben wir bei einem unserer Kunden, der gelegentlich massive Datenbankprobleme hat, bei der Durchsicht der MySQL Konfigurationsdatei (my.cnf) festgestellt, dass er die InnoDB Deadlock-Erkennung (innodb_deadlock_detect) deaktiviert hatte.

Da wir davon bisher immer abgeraten haben, ich aber noch nie konkret über dieses Problem gestolpert bin, bin ich der Sache noch etwas nachgegangen und habe zur Variable innodb_deadlock_detect recherchiert.

Die MySQL Dokumentation sagt dazu folgendes [1]:

Disabling Deadlock Detection
On high concurrency systems, deadlock detection can cause a slowdown when numerous threads wait for the same lock. At times, it may be more efficient to disable deadlock detection and rely on the innodb_lock_wait_timeout setting for transaction rollback when a deadlock occurs. Deadlock detection can be disabled using the innodb_deadlock_detect configuration option.

Und zum Parameter innodb_deadlock_detect selbst [2]:

This option is used to disable deadlock detection. On high concurrency systems, deadlock detection can cause a slowdown when numerous threads wait for the same lock. At times, it may be more efficient to disable deadlock detection and rely on the innodb_lock_wait_timeout setting for transaction rollback when a deadlock occurs.

Das Problem ist, dass jedes mal, wenn MySQL einen (Row) Lock oder Table Lock macht, überprüft wird, ob dieser Lock einen Deadlock verursacht. Was entsprechend teuer ist. Diese Feature wurde übrigens von Facebook entwickelt [3].

Die entsprechenden Funktionen sind in [4] zu finden:

class DeadlockChecker, method check_and_resolve (DeadlockChecker::check_and_resolve) Every InnoDB (row) Lock (for mode LOCK_S or LOCK_X) and type ORed with LOCK_GAP or LOCK_REC_NOT_GAP, ORed with LOCK_INSERT_INTENTION Enqueue a waiting request for a lock which cannot be granted immediately. lock_rec_enqueue_waiting()

und

Every (InnoDB) Table Lock Enqueues a waiting request for a table lock which cannot be granted immediately. Checks for deadlocks. lock_table_enqueue_waiting()

Das heisst jetzt, wenn die Variable innodb_deadlock_detect eingeschaltet ist (= default) wird bei jedem Lock (Row oder Table) überprüft, ob dadurch ein Deadlock entsteht. Wenn die Variable ausgeschaltet ist, wird diese Überprüfung NICHT ausgeführt (was schneller ist) und die Transaktion bleibt im (Dead-)Lock hängen bis der Lock frei gegeben wird oder die Zeit innodb_lock_wait_timeout (default 50 Sekunden) überschritten ist. Dann schlägt der InnoDB Lock Wait Timeout (Detektor?) zu.

SQL> SHOW GLOBAL VARIABLES LIKE 'innodb_lock_wait%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | innodb_lock_wait_timeout | 50 | +--------------------------+-------+

Das bedeutet, das Deaktivieren der InnoDB Deadlock Detektion ist interessant, wenn Sie sehr viele (wie Facebook!?!) kurze/kleine Transaktionen haben, bei welchen wenig bis keine Konflikte auftreten.
Im Weiteren wird empfohlen, gleichzeitig die innodb_lock_wait_timeout auf einen sehr geringen Wert (wenige Sekunden) einzustellen.

Da die meisten unserer Kunden aber nicht in die Kragenweite Facebook passen und tendenziell eher nicht viele gleichzeitige kurze/kleine Transaktionen haben sondern wenig lange Transaktionen (mit wahrscheinlich vielen Locks und daher einer grossen Deadlock-Wahrscheinlichkeit), kann ich mir durchaus vorstellen, dass das deaktivieren diese Parameters für das Verschlucken (Locks stauen auf) des Kundensystems verantwortlich ist, was anschliessen dazu führt, dass max_connections erreicht wird und schliesslich gar nichts mehr geht.

Daher würde ich dringend empfehlen, die InnoDB Deadlock Detektierung aktiviert zu lassen. Ausser man weiss genau, was man tut (nach ca. 2 Wochen testen und messen).

Literatur Taxonomy upgrade extras: innodbdeadlocklockperformancelockingblock

Eher Finger weg: innodb_deadlock_detect

Oli Sennhauser - Fri, 2020-03-06 15:27

Kürzlich haben wir bei einem unserer Kunden, der gelegentlich massive Datenbankprobleme hat, bei der Durchsicht der MySQL Konfigurationsdatei (my.cnf) festgestellt, dass er die InnoDB Deadlock-Erkennung (innodb_deadlock_detect) deaktiviert hatte.

Da wir davon bisher immer abgeraten haben, ich aber noch nie konkret über dieses Problem gestolpert bin, bin ich der Sache noch etwas nachgegangen und habe zur Variable innodb_deadlock_detect recherchiert.

Die MySQL Dokumentation sagt dazu folgendes [1]:

Disabling Deadlock Detection
On high concurrency systems, deadlock detection can cause a slowdown when numerous threads wait for the same lock. At times, it may be more efficient to disable deadlock detection and rely on the innodb_lock_wait_timeout setting for transaction rollback when a deadlock occurs. Deadlock detection can be disabled using the innodb_deadlock_detect configuration option.

Und zum Parameter innodb_deadlock_detect selbst [2]:

This option is used to disable deadlock detection. On high concurrency systems, deadlock detection can cause a slowdown when numerous threads wait for the same lock. At times, it may be more efficient to disable deadlock detection and rely on the innodb_lock_wait_timeout setting for transaction rollback when a deadlock occurs.

Das Problem ist, dass jedes mal, wenn MySQL einen (Row) Lock oder Table Lock macht, überprüft wird, ob dieser Lock einen Deadlock verursacht. Was entsprechend teuer ist. Diese Feature wurde übrigens von Facebook entwickelt [3].

Die entsprechenden Funktionen sind in [4] zu finden:

class DeadlockChecker, method check_and_resolve (DeadlockChecker::check_and_resolve) Every InnoDB (row) Lock (for mode LOCK_S or LOCK_X) and type ORed with LOCK_GAP or LOCK_REC_NOT_GAP, ORed with LOCK_INSERT_INTENTION Enqueue a waiting request for a lock which cannot be granted immediately. lock_rec_enqueue_waiting()

und

Every (InnoDB) Table Lock Enqueues a waiting request for a table lock which cannot be granted immediately. Checks for deadlocks. lock_table_enqueue_waiting()

Das heisst jetzt, wenn die Variable innodb_deadlock_detect eingeschaltet ist (= default) wird bei jedem Lock (Row oder Table) überprüft, ob dadurch ein Deadlock entsteht. Wenn die Variable ausgeschaltet ist, wird diese Überprüfung NICHT ausgeführt (was schneller ist) und die Transaktion bleibt im (Dead-)Lock hängen bis der Lock frei gegeben wird oder die Zeit innodb_lock_wait_timeout (default 50 Sekunden) überschritten ist. Dann schlägt der InnoDB Lock Wait Timeout (Detektor?) zu.

SQL> SHOW GLOBAL VARIABLES LIKE 'innodb_lock_wait%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | innodb_lock_wait_timeout | 50 | +--------------------------+-------+

Das bedeutet, das Deaktivieren der InnoDB Deadlock Detektion ist interessant, wenn Sie sehr viele (wie Facebook!?!) kurze/kleine Transaktionen haben, bei welchen wenig bis keine Konflikte auftreten.
Im Weiteren wird empfohlen, gleichzeitig die innodb_lock_wait_timeout auf einen sehr geringen Wert (wenige Sekunden) einzustellen.

Da die meisten unserer Kunden aber nicht in die Kragenweite Facebook passen und tendenziell eher nicht viele gleichzeitige kurze/kleine Transaktionen haben sondern wenig lange Transaktionen (mit wahrscheinlich vielen Locks und daher einer grossen Deadlock-Wahrscheinlichkeit), kann ich mir durchaus vorstellen, dass das deaktivieren diese Parameters für das Verschlucken (Locks stauen auf) des Kundensystems verantwortlich ist, was anschliessen dazu führt, dass max_connections erreicht wird und schliesslich gar nichts mehr geht.

Daher würde ich dringend empfehlen, die InnoDB Deadlock Detektierung aktiviert zu lassen. Ausser man weiss genau, was man tut (nach ca. 2 Wochen testen und messen).

Literatur Taxonomy upgrade extras: innodbdeadlocklockperformancelockingblock

FromDual ist 10 Jahre alt

Oli Sennhauser - Mon, 2020-03-02 10:01

Am 1. März 2020 wurde die FromDual GmbH 10 Jahre alt! Wir möchten allen Kunden, Partnern und Interessenten herzlich für die Unterstützung und gute Zusammenarbeit in den vergangenen 10 Jahren danken. Es würde uns freuen, Euch auch in den kommenden 10 Jahren kompetent beraten und betreuen zu dürfen.

Euer FromDual Team

Bild von kalhh auf Pixabay

Taxonomy upgrade extras: fromdual

FromDual ist 10 Jahre alt

Oli Sennhauser - Mon, 2020-03-02 10:01

Am 1. März 2020 wurde die FromDual GmbH 10 Jahre alt! Wir möchten allen Kunden, Partnern und Interessenten herzlich für die Unterstützung und gute Zusammenarbeit in den vergangenen 10 Jahren danken. Es würde uns freuen, Euch auch in den kommenden 10 Jahren kompetent beraten und betreuen zu dürfen.

Euer FromDual Team

Bild von kalhh auf Pixabay

Taxonomy upgrade extras: fromdual

Pages

Subscribe to FromDual aggregator - FromDual TechFeed (de)