You are here

Oli Sennhauser

Subscribe to Oli Sennhauser feed
FromDual RSS feed about MySQL, Galera Cluster, MariaDB and Percona Server
Updated: 1 min 26 sec ago

dbstat nach einem Monat produktiver Nutzung

Thu, 2024-04-25 12:39
Inhaltsverzeichnis
Rückblick

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

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

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

Einen Monat später

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

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

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

SQL> SELECT db, name, definer, CONCAT(interval_value, ' ', interval_field) AS 'interval' , last_executed, ends, status FROM mysql.event ORDER BY db, name ASC ; +--------+-------------------------+------------------+----------+---------------------+------+---------+ | db | name | definer | interval | last_executed | ends | status | +--------+-------------------------+------------------+----------+---------------------+------+---------+ | dbstat | gather_global_status | dbstat@localhost | 1 MINUTE | 2024-04-24 07:44:14 | NULL | ENABLED | | dbstat | gather_global_variables | dbstat@localhost | 1 MINUTE | 2024-04-24 07:44:32 | NULL | ENABLED | | dbstat | gather_metadata_lock | dbstat@localhost | 1 MINUTE | 2024-04-24 07:44:47 | NULL | ENABLED | | dbstat | gather_processlist | dbstat@localhost | 1 MINUTE | 2024-04-24 07:44:28 | NULL | ENABLED | | dbstat | gather_table_size | dbstat@localhost | 1 DAY | 2024-04-24 00:04:00 | NULL | ENABLED | | dbstat | gather_trx_and_lck | dbstat@localhost | 1 MINUTE | 2024-04-24 07:44:35 | NULL | ENABLED | | dbstat | purge_global_status | dbstat@localhost | 1 MINUTE | 2024-04-24 07:44:08 | NULL | ENABLED | | dbstat | purge_metadata_lock | dbstat@localhost | 5 MINUTE | 2024-04-24 07:44:37 | NULL | ENABLED | | dbstat | purge_processlist | dbstat@localhost | 1 MINUTE | 2024-04-24 07:43:58 | NULL | ENABLED | | dbstat | purge_table_size | dbstat@localhost | 5 MINUTE | 2024-04-24 07:40:04 | NULL | ENABLED | | dbstat | purge_trx_and_lck | dbstat@localhost | 1 MINUTE | 2024-04-24 07:44:45 | NULL | ENABLED | +--------+-------------------------+------------------+----------+---------------------+------+---------+
Grösse der Tabellen

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

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

Nimmt man zum Vergleich den Plattenplatz im O/S:

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

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

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

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

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

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

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

Der einzige wirkliche Treiber für die Datenmenge auf diesem System ist die Tabelle global_status. Dies ist auch zu erwarten (siehe Mengengerüst von dbstat).

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

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

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

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

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

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

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

Liste der Prozesse

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

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

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

Globale Variablen

Interessant sind auch die Informationen in der Tabelle global_variables:

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

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

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

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

Metadata Lock und InnoDB Transaction Lock

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

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

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

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

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

Globaler Status

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

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

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

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

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

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

Taxonomy upgrade extras: performancemonitoringperformance monitoringmetadata locklockingperformance_schema

MariaDBs parallele Replikation zum Aufholen

Mon, 2024-04-08 11:01

Aufgrund eines applikatorischen Fehlers ist unsere Replikation während 5 Tagen stehen geblieben (über Ostern). Nachdem das Problem gelöst war, sollte die Replikation aufholen, was sich als sehr zäh herausstellte. All die üblichen Tricks (innodb_flush_log_at_trx_commit, sync_binlog, etc.) wurden bereits ausgereizt. Also haben wir uns an der parallelen Replikation des MariaDB Servers versucht.

Per default ist die parallel Replikation deaktiviert:

SQL> SHOW GLOBAL VARIABLES LIKE '%parallel%'; +-------------------------------+------------+ | Variable_name | Value | +-------------------------------+------------+ | slave_domain_parallel_threads | 0 | | slave_parallel_max_queued | 131072 | | slave_parallel_mode | optimistic | | slave_parallel_threads | 0 | | slave_parallel_workers | 0 | +-------------------------------+------------+

Durch setzen der Server Variablen slave_parallel_threads wird die parallele Replikation aktiviert:

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

Dies muss aber bei gestoppter Replikation erfolgen:

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

Anschliessend hat die Replikation etwas schneller aufgeholt. Da wir aber ungeduldig waren, haben wir versucht, das ganze noch schneller hin zu kriegen. Mit dem Befehl:

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

haben wir die folgende Meldung gefunden. Man würde sie auch über den Befehl SHOW PROCESSLIST sehen:

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

Gemäss Doku kann es in diesem Fall helfen, die Variable slave_parallel_max_queued etwas zu vergrössern (Achtung: Oom!).

SQL> STOP SLAVE; SQL> SET GLOBAL slave_parallel_max_queued = 1*1024*1024; SQL> SHOW GLOBAL VARIABLES LIKE '%parallel%'; +-------------------------------+------------+ | Variable_name | Value | +-------------------------------+------------+ | slave_domain_parallel_threads | 0 | | slave_parallel_max_queued | 1048576 | | slave_parallel_mode | optimistic | | slave_parallel_threads | 8 | | slave_parallel_workers | 8 | +-------------------------------+------------+ SQL> START SLAVE;

Wir haben mit den Werten slave_parallel_threads im Bereich von 4 bis 32 (bei 8 vCores) und bei slave_parallel_max_queued im Bereich von 128 kbyte bis 32 Mbyte rumgespielt.
Achtung: Nicht übertreiben: 32 Threads x 32 Mbyte = 1 Gbyte RAM (Oom)!

Um herauszufinden, welche Werte das Optimum sind, müsste man ausführlicher testen und messen. Jedenfalls hat die Replikation nach circa einer Stunde die 5 Tage Rückstand aufgeholt, gegen Schluss etwas mehr als zu Beginn, was hoffentlich durch unsere Konfigurationsanpassungen verursacht wurde.

Je nach dem, was gerade an DML Statements läuft sieht man, dass alle Threads genutzt werden können oder die einen Threads auf andere Threads warten müssen:

SQL> SHOW PROCESSLIST; +--------+-----------------+--------------+--------+---------------------------------------------------------------+------------+ | Id | User | Command | Time | State | Info | +--------+-----------------+--------------+--------+---------------------------------------------------------------+------------+ | 2 | event_scheduler | Daemon | 506179 | Waiting for next activation | NULL | | 191154 | root | Query | 0 | starting | show pr... | | 208669 | replication | Binlog Dump | 297 | Master has sent all binlog to slave; waiting for more updates | NULL | | 212495 | system user | Slave_IO | 20 | Waiting for master to send event | NULL | | 212497 | system user | Slave_worker | 0 | Waiting for prior transaction to commit | NULL | | 212498 | system user | Slave_worker | 0 | Waiting for prior transaction to commit | NULL | | 212499 | system user | Slave_worker | 0 | Waiting for prior transaction to commit | NULL | | 212500 | system user | Slave_worker | 0 | Waiting for prior transaction to commit | NULL | | 212501 | system user | Slave_worker | 0 | Write_rows_log_event::write_row(-1) on table `history_uint` | insert ... | | 212502 | system user | Slave_worker | 0 | Write_rows_log_event::write_row(-1) on table `history_uint` | insert ... | | 212503 | system user | Slave_worker | 0 | Write_rows_log_event::write_row(-1) on table `history_str` | insert ... | | 212504 | system user | Slave_worker | 0 | Waiting for prior transaction to commit | NULL | | 212505 | system user | Slave_worker | 0 | Waiting for prior transaction to commit | NULL | | 212506 | system user | Slave_worker | 0 | Waiting for prior transaction to commit | NULL | | 212507 | system user | Slave_worker | 0 | Waiting for prior transaction to commit | NULL | | 212510 | system user | Slave_worker | 0 | Waiting for prior transaction to commit | NULL | | 212509 | system user | Slave_worker | 0 | Waiting for prior transaction to commit | NULL | | 212508 | system user | Slave_worker | 0 | Waiting for prior transaction to commit | NULL | | 212511 | system user | Slave_worker | 0 | Waiting for prior transaction to commit | NULL | | 212512 | system user | Slave_worker | 0 | Waiting for prior transaction to commit | NULL | | 212496 | system user | Slave_SQL | 16 | Waiting for room in worker thread event queue | NULL | +--------+-----------------+--------------+--------+---------------------------------------------------------------+------------+ SQL> SHOW PROCESSLIST; +--------+-----------------+--------------+--------+---------------------------------------------------------------+------------+ | Id | User | Command | Time | State | Info | +--------+-----------------+--------------+--------+---------------------------------------------------------------+------------+ | 2 | event_scheduler | Daemon | 506197 | Waiting for next activation | NULL | | 191154 | root | Query | 0 | starting | show pr... | | 208669 | replication | Binlog Dump | 315 | Master has sent all binlog to slave; waiting for more updates | NULL | | 212495 | system user | Slave_IO | 37 | Waiting for master to send event | NULL | | 212497 | system user | Slave_worker | 0 | Delete_rows_log_event::ha_delete_row(-1) on table `history` | delete ... | | 212498 | system user | Slave_worker | 0 | Delete_rows_log_event::find_row(-1) on table `history` | delete ... | | 212499 | system user | Slave_worker | 0 | Delete_rows_log_event::ha_delete_row(-1) on table `history` | delete ... | | 212500 | system user | Slave_worker | 0 | Delete_rows_log_event::find_row(-1) on table `history` | delete ... | | 212501 | system user | Slave_worker | 0 | Delete_rows_log_event::find_row(-1) on table `history` | delete ... | | 212502 | system user | Slave_worker | 0 | Delete_rows_log_event::find_row(-1) on table `history` | delete ... | | 212503 | system user | Slave_worker | 0 | Delete_rows_log_event::find_row(-1) on table `history` | delete ... | | 212504 | system user | Slave_worker | 0 | Delete_rows_log_event::ha_delete_row(-1) on table `history` | delete ... | | 212505 | system user | Slave_worker | 0 | Delete_rows_log_event::find_row(-1) on table `history` | delete ... | | 212506 | system user | Slave_worker | 0 | Delete_rows_log_event::ha_delete_row(-1) on table `history` | delete ... | | 212507 | system user | Slave_worker | 0 | Delete_rows_log_event::find_row(-1) on table `history` | delete ... | | 212510 | system user | Slave_worker | 0 | Delete_rows_log_event::find_row(-1) on table `history` | delete ... | | 212509 | system user | Slave_worker | 0 | Delete_rows_log_event::find_row(-1) on table `history` | delete ... | | 212508 | system user | Slave_worker | 0 | Delete_rows_log_event::find_row(-1) on table `history` | delete ... | | 212511 | system user | Slave_worker | 0 | Delete_rows_log_event::find_row(-1) on table `history` | delete ... | | 212512 | system user | Slave_worker | 0 | Delete_rows_log_event::find_row(-1) on table `history` | delete ... | | 212496 | system user | Slave_SQL | 11 | Waiting for room in worker thread event queue | NULL | +--------+-----------------+--------------+--------+---------------------------------------------------------------+------------+

Unser Monitoring hat uns auch schön angezeigt, dass die CPU Load hoch ging, das I/O-System mehr zu tun kriegte und mehr Rows modifiziert wurden...

Was ebenfalls auffiel ist, dass mit parallel Replication plötzlich Foreing Key Fehler auftraten, ein Phänomen, dass wir so vorher noch nicht beobachtet hatten:

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

Mit dem Befehl SHOW ENGINE INNODB STATUS\G kann man diese entsprechend inspizieren oder im Monitoring anschauen:

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

MariaDB Server aus den Quellen bauen

Thu, 2024-04-04 14:53

Kürzlich musste ich ein neues MariaDB Feature testen, welches auf unseren Wunsch entwickelt wurde (MDEV-33782). Um dieses Feature zu testen musste ich aber den MariaDB Server selber aus den Quellen bauen, was ich schon seit längerem nicht mehr gemacht habe. Also eine neue Herausforderung, insbesondere mit CMake...

I habe hierzu die MariaDB Dokumentation Get, Build and Test Latest MariaDB the Lazy Way befolgt um den Server zu bauen.

Auf Ubuntu 22.04 hat es bei mir, aus mir nicht bekannten Gründen, nicht funktioniert. Also habe ich mir einen Ubuntu 23.04 (Lunar Lobster) LXC Container geclont und darin den MariaDB Server gebaut.

Damit das ganze funktioniert musste aber vorher noch im Container /etc/apt/sources.list durch die Paketquellen ergänzt werden:

deb-src http://de.archive.ubuntu.com/ubuntu lunar main restricted universe multiverse deb-src http://de.archive.ubuntu.com/ubuntu lunar-updates main restricted universe multiverse deb-src http://de.archive.ubuntu.com/ubuntu lunar-security main restricted universe multiverse deb-src http://de.archive.ubuntu.com/ubuntu lunar-backports main restricted universe multiverse

Dann wurde nach Vorgabe weiter verfahren:

shell> apt install build-essential bison shell> apt build-dep mariadb-server

Der Entsprechende Branch wurde geclont:

shell> # git clone https://github.com/andremralves/server.git mariadb-MDEV-33782 shell> # git branch --all shell> git clone --branch MDEV-33782 --single-branch https://github.com/andremralves/server.git mariadb-MDEV-33782 shell> cd mariadb-MDEV-33782 shell> # git checkout 11.5

und dann der Server gebaut. Dies hat auf meiner alten Maschine etwa 20 Minuten gedauert. CMake ist noch auf einen Fehler gelaufen, welcher mit dem Nachinstallieren des entsprechenden Pakets gelöst wurde (MDEV-33815):

shell> apt install libgnutls28-dev shell> cmake . -DBUILD_CONFIG=mysql_release && make -j8

Die Tests wurden ausgeführt:

shell> cd mysql-test shell> ./mtr rpl.rpl_create_drop_event

Und dann ein Binary-Tarball gebaut.

shell> make package Run CPack packaging tool... CPack: Create package using TGZ CPack: Install projects CPack: - Run preinstall target for: MariaDB CPack: - Install project: MariaDB [] CPack: Create package CPack: - package: /root/mariadb-MDEV-33782/mariadb-11.5.0-linux-x86_64.tar.gz generated.
Taxonomy upgrade extras: mariadbbuildcompilingsourcestarball

MaxScale Konfigurations-Synchronisation

Tue, 2024-04-02 17:43
Inhaltsverzeichnis
Übersicht

Ein Feature, welches ich beim Stöbern kürzlich entdecke habe, ist die MaxScale Konfigurations-Synchronisation Funktionalität.

Es geht hier nicht primär um einen MariaDB Replikations-Cluster oder einen MariaDB Galera Cluster sondern um einen Cluster bestehend aus zwei oder mehreren MaxScale Knoten. Bzw. etwas genauer gesagt, den Austausch der Konfiguration unter diesen MaxScale Knoten.

Pon Suresh Pandian hat bereits 2022 einen Blog-Artikel über dieses Feature geschrieben, der noch etwas ausführlicher ist, als dieser Beitrag hier.

Vorbereitungen

Es wurde eine LXD Container-Umgebung vorbereitet, bestehend aus 3 Datenbank-Containern (deb12-n1 (10.139.158.33), deb12-n2 (10.139.158.178), deb12-n3(10.139.158.39)) und 2 MaxScale Containern (deb12-mxs1 (10.139.158.66), deb12-mxs2(10.139.158.174)). Die Datenbankversion ist eine MariaDB 10.11.6 aus dem Debian-Repository und MaxScale wurde in der Version 22.08.5 von der MariaDB plc Website heruntergeladen.

Die Datenbank-Konfiguration sieht für alle 3 Knoten analog aus:

# # /etc/mysql/mariadb.conf.d/99-fromdual.cnf # [server] server_id = 1 log_bin = deb12-n1-binlog binlog_format = row bind_address = * proxy_protocol_networks = ::1, 10.139.158.0/24, localhost gtid_strict_mode = on log_slave_updates = on skip_name_resolve = on

Die MaxScale Knoten wurden wie im Artikel Sharding mit MariaDB MaxScale beschrieben gebaut.

Der maxscale_admin User hat genau die selben Rechte wie dort beschrieben, der maxscale_monitor User hat folgende Rechte erhalten:

RELOAD, SUPER, REPLICATION SLAVE, READ_ONLY ADMIN

Siehe auch hier: Required Grants.

Die MaxScale Start-Konfiguration sieht wie folgt aus:

# # /etc/maxscale.cnf # [maxscale] threads = auto admin_gui = false [deb12-n1] type = server address = 10.139.158.33 port = 3306 proxy_protocol = true [deb12-n2] type = server address = 10.139.158.178 port = 3306 proxy_protocol = true [Replication-Monitor] type = monitor module = mariadbmon servers = deb12-n1,deb12-n2 user = maxscale_monitor password = secret monitor_interval = 500ms auto_failover = true auto_rejoin = true enforce_read_only_slaves = true replication_user = replication replication_password = secret cooperative_monitoring_locks = majority_of_running [WriteListener] type = listener service = WriteService port = 3306 [WriteService] type = service router = readwritesplit servers = deb12-n1,deb12-n2 user = maxscale_admin password = secret transaction_replay = true transaction_replay_timeout = 30s

Wichtig: Die Konfiguration sollte auf allen MaxScale Knoten gleich aussehen!

Und dann noch ein paar Checks um sicher zu sein, dass alles stimmt:

shell> maxctrl list listeners ┌───────────────┬──────┬──────┬─────────┬──────────────┐ │ Name │ Port │ Host │ State │ Service │ ├───────────────┼──────┼──────┼─────────┼──────────────┤ │ WriteListener │ 3306 │ :: │ Running │ WriteService │ └───────────────┴──────┴──────┴─────────┴──────────────┘ shell> maxctrl list services ┌──────────────┬────────────────┬─────────────┬───────────────────┬────────────────────┐ │ Service │ Router │ Connections │ Total Connections │ Targets │ ├──────────────┼────────────────┼─────────────┼───────────────────┼────────────────────┤ │ WriteService │ readwritesplit │ 0 │ 0 │ deb12-n1, deb12-n2 │ └──────────────┴────────────────┴─────────────┴───────────────────┴────────────────────┘ shell> maxctrl list servers ┌──────────┬────────────────┬──────┬─────────────┬─────────────────┬────────┬─────────────────────┐ │ Server │ Address │ Port │ Connections │ State │ GTID │ Monitor │ ├──────────┼────────────────┼──────┼─────────────┼─────────────────┼────────┼─────────────────────┤ │ deb12-n1 │ 10.139.158.33 │ 3306 │ 0 │ Master, Running │ 0-1-19 │ Replication-Monitor │ ├──────────┼────────────────┼──────┼─────────────┼─────────────────┼────────┼─────────────────────┤ │ deb12-n2 │ 10.139.158.178 │ 3306 │ 0 │ Slave, Running │ 0-1-19 │ Replication-Monitor │ └──────────┴────────────────┴──────┴─────────────┴─────────────────┴────────┴─────────────────────┘ SQL> SELECT @@hostname, test.* FROM test.test; +------------+----+-----------+---------------------+ | @@hostname | id | data | ts | +------------+----+-----------+---------------------+ | deb12-n2 | 1 | Some data | 2024-03-26 09:40:21 | +------------+----+-----------+---------------------+ SQL> SELECT @@hostname, test.* FROM test.test FOR UPDATE; +------------+----+-----------+---------------------+ | @@hostname | id | data | ts | +------------+----+-----------+---------------------+ | deb12-n1 | 1 | Some data | 2024-03-26 09:40:21 | +------------+----+-----------+---------------------+

Und noch ein weiterer Test ob MaxScale den Failover auch wirklich richtig ausführt:

shell> systemctl stop mariadb 2024-03-26 16:27:05 error : Monitor was unable to connect to server deb12-n2[10.139.158.178:3306] : 'Can't connect to server on '10.139.158.178' (115)' 2024-03-26 16:27:05 notice : Server changed state: deb12-n2[10.139.158.178:3306]: master_down. [Master, Running] -> [Down] 2024-03-26 16:27:05 warning: [mariadbmon] Primary has failed. If primary does not return in 4 monitor tick(s), failover begins. 2024-03-26 16:27:07 notice : [mariadbmon] Selecting a server to promote and replace 'deb12-n2'. Candidates are: 'deb12-n1'. 2024-03-26 16:27:07 notice : [mariadbmon] Selected 'deb12-n1'. 2024-03-26 16:27:07 notice : [mariadbmon] Performing automatic failover to replace failed primary 'deb12-n2'. 2024-03-26 16:27:07 notice : [mariadbmon] Failover 'deb12-n2' -> 'deb12-n1' performed. 2024-03-26 16:27:07 notice : Server changed state: deb12-n1[10.139.158.33:3306]: new_master. [Slave, Running] -> [Master, Running] shell> systemctl start mariadb 2024-03-26 16:28:03 notice : Server changed state: deb12-n2[10.139.158.178:3306]: server_up. [Down] -> [Running] 2024-03-26 16:28:03 notice : [mariadbmon] Directing standalone server 'deb12-n2' to replicate from 'deb12-n1'. 2024-03-26 16:28:03 notice : [mariadbmon] Replica connection from deb12-n2 to [10.139.158.33]:3306 created and started. 2024-03-26 16:28:03 notice : [mariadbmon] 1 server(s) redirected or rejoined the cluster. 2024-03-26 16:28:03 notice : Server changed state: deb12-n2[10.139.158.178:3306]: new_slave. [Running] -> [Slave, Running]

Welcher MaxScale Knoten gerade für das Monitoring und Failover zuständig ist (cooperatve_monitoring) kann wir folgt eruiert werden:

shell> maxctrl show monitor Replication-Monitor | grep -e 'Diagnostics' -e '"primary"' -e 'lock_held' | uniq │ Monitor Diagnostics │ { │ │ │ "primary": true, │ │ │ "lock_held": true, │

Es sollte sichergestellt sein, das bis hier hin alles sauber funktioniert. Ansonsten haben die weiteren Schritte keinen wirklichen Sinn.

MaxScale Konfigurations-Synchronisation aktivieren

Für die Konfigurations-Synchronisation braucht es einen eigenen Datenbank-User mit folgenden Rechten:

SQL> CREATE USER 'maxscale_confsync'@'%' IDENTIFIED BY 'secret'; SQL> GRANT SELECT, INSERT, UPDATE, CREATE ON `mysql`.`maxscale_config` TO maxscale_confsync@'%';

Anschliessend muss MaxScale entsprechend konfiguriert werden (auf beiden MaxScale Knoten), damit die Konfigurations-Synchronisation aktiviert wird. Diese Konfiguration erfolgt im globalen MaxScale Abschnitt:

# # /etc/maxscale.cnf # [maxscale] config_sync_cluster = Replication-Monitor config_sync_user = maxscale_confsync config_sync_password = secret

Dann erfolgt der Neustart der MaxScale Knoten:

shell> systemctl restart maxscale

Die MaxScale Konfigurations-Synchronisation kann auch dynamisch aktiviert und deaktiviert werden:

shell> maxctrl show maxscale | grep config_sync │ │ "config_sync_cluster": null, │ │ │ "config_sync_db": "mysql", │ │ │ "config_sync_interval": "5000ms", │ │ │ "config_sync_password": null, │ │ │ "config_sync_timeout": "10000ms", │ │ │ "config_sync_user": null, │

Hier ist wichtig, die richtige Reihenfolge der 3 Befehle einzuhalten, da es sonst einen Fehler gibt:

shell> MAXCTRL_WARNINGS=0 maxctrl alter maxscale config_sync_user='maxscale_confsync' shell> MAXCTRL_WARNINGS=0 maxctrl alter maxscale config_sync_password='secret' shell> MAXCTRL_WARNINGS=0 maxctrl alter maxscale config_sync_cluster='Replication-Monitor'
MaxScale Parameter ändern

Als ersten Test haben wir die MaxScale Monitor Variable monitor_interval ins Auge gefasst, die in diesem Fall auf beiden MaxScale-Knoten sogar unterschiedlich ist:

shell> maxctrl show monitor Replication-Monitor | grep monitor_interval │ │ "monitor_interval": "750ms", shell> maxctrl show monitor Replication-Monitor | grep monitor_interval │ │ "monitor_interval": "1000ms",

Mit dem alter monitor Befehl kann die Variable jetzt auf einem MaxScale Knoten gesetzt werden:

shell> MAXCTRL_WARNINGS=0 maxctrl alter monitor Replication-Monitor monitor_interval=500ms OK

was einerseits im MaxScale Error Log ersichtlich ist:

2024-03-26 14:09:16 notice : (ConfigManager); Updating to configuration version 1

anderseits sollte der Wert innerhalb von 5 Sekunden (config_sync_interval) auf den zweiten MaxScale Knoten propagiert werden, was mit dem obigen Befehl überprüft werden kann.

Neuer Slave hinzufügen und MaxScale bekannt machen

Ein neuer Slave (deb12-n3) wird zuerst erstellt und dem MariaDB Replikations-Cluster von Hand hinzugefügt. Anschliessen wird der Slave einem MaxScale Knoten bekannt gemacht:

shell> maxctrl create server deb12-n3 10.139.158.39 shell> MAXCTRL_WARNINGS=0 maxctrl link monitor Replication-Monitor deb12-n3 OK shell> MAXCTRL_WARNINGS=0 maxctrl link service WriteService deb12-n3 OK shell> maxctrl list servers ┌──────────┬────────────────┬──────┬─────────────┬─────────────────┬────────────┬─────────────────────┐ │ Server │ Address │ Port │ Connections │ State │ GTID │ Monitor │ ├──────────┼────────────────┼──────┼─────────────┼─────────────────┼────────────┼─────────────────────┤ │ deb12-n1 │ 10.139.158.33 │ 3306 │ 3 │ Slave, Running │ 0-2-479618 │ Replication-Monitor │ ├──────────┼────────────────┼──────┼─────────────┼─────────────────┼────────────┼─────────────────────┤ │ deb12-n2 │ 10.139.158.178 │ 3306 │ 3 │ Master, Running │ 0-2-479618 │ Replication-Monitor │ ├──────────┼────────────────┼──────┼─────────────┼─────────────────┼────────────┼─────────────────────┤ │ deb12-n3 │ 10.139.158.39 │ 3306 │ 1 │ Slave, Running │ 0-2-479618 │ Replication-Monitor │ └──────────┴────────────────┴──────┴─────────────┴─────────────────┴────────────┴─────────────────────┘
Alter Slave entfernen und MaxScale bekannt machen

Bevor ein Slave gelöscht werden kann, sollte er bei einem MaxScale Knoten aus dem Replikations-Cluster entfernt werden:

shell> maxctrl destroy server deb12-n1 --force OK shell> maxctrl list servers ┌──────────┬────────────────┬──────┬─────────────┬─────────────────┬────────────┬─────────────────────┐ │ Server │ Address │ Port │ Connections │ State │ GTID │ Monitor │ ├──────────┼────────────────┼──────┼─────────────┼─────────────────┼────────────┼─────────────────────┤ │ deb12-n2 │ 10.139.158.178 │ 3306 │ 3 │ Master, Running │ 0-2-493034 │ Replication-Monitor │ ├──────────┼────────────────┼──────┼─────────────┼─────────────────┼────────────┼─────────────────────┤ │ deb12-n3 │ 10.139.158.39 │ 3306 │ 1 │ Slave, Running │ 0-2-493032 │ Replication-Monitor │ └──────────┴────────────────┴──────┴─────────────┴─────────────────┴────────────┴─────────────────────┘

Anschliessend kann der Slave abgebaut werden.

Wie wird die Konfiguration synchronisiert?

Die Konfiguration der beiden MaxScale Knoten wird über die Datenbank synchronisiert, was ich persönlich als unglückliche Design-Entscheidung ansehe, da eine Konfigurationsänderung potentiell hackelt, wenn der Master kaputt geht oder Netzwerkprobleme zwischen den Datenbanknoten auftreten...

Die Konfiguration wird in der Tabelle mysql.maxscale_config abgespeichert, welche wie folgt aussieht:

CREATE TABLE `maxscale_config` ( `cluster` varchar(256) NOT NULL, `version` bigint(20) NOT NULL, `config` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`config`)), `origin` varchar(254) NOT NULL, `nodes` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`nodes`)), PRIMARY KEY (`cluster`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

Diese Tabelle hat in etwa folgenden Inhalt:

SQL> SELECT cluster, version, CONCAT(SUBSTR(config, 1, 32), ' ... ', SUBSTR(config, -32)) AS config , origin, nodes FROM mysql.maxscale_config; +---------------------+---------+-----------------------------------------------------------------------+------------+------------------------------------------+ | cluster | version | config | origin | nodes | +---------------------+---------+-----------------------------------------------------------------------+------------+------------------------------------------+ | Replication-Monitor | 2 | {"config":[{"id":"deb12-n1","typ ... ter_name":"Replication-Monitor"} | deb12-mxs1 | {"deb12-mxs1": "OK", "deb12-mxs2": "OK"} | +---------------------+---------+-----------------------------------------------------------------------+------------+------------------------------------------+

Eine lokale Kopie liegt zur Sicherheit auf jedem Knoten vor:

shell> cut -b-32 /var/lib/maxscale/maxscale-config.json {"config":[{"id":"deb12-n2","typ
Was passiert im Konfliktfall?

Siehe hierzu auch: Error Handling in Configuration Synchronization

Wenn zeitgleich (innerhalb von config_sync_interval?) auf zwei unterschiedlichen MaxScale Knoten die Konfiguration geändert wird erhalten wir folgende Fehlermeldung:

Error: Server at http://127.0.0.1:8989 responded with 400 Bad Request to `PATCH monitors/Replication-Monitor` { "errors": [ { "detail": "Cannot start configuration change: Configuration conflict detected: version stored in the cluster (3) is not the same as the local version (2), MaxScale is out of sync." } ] }

Folgender Befehl hilft allenfalls bei grösseren Störungen das Problem zu erkennen:

shell> maxctrl show maxscale | grep -A9 'Config Sync' │ Config Sync │ { │ │ │ "checksum": "0052fe6f775168bf00778abbe37775f6f642adc7", │ │ │ "nodes": { │ │ │ "deb12-mxs1": "OK", │ │ │ "deb12-mxs2": "OK" │ │ │ }, │ │ │ "origin": "deb12-mxs2", │ │ │ "status": "OK", │ │ │ "version": 3 │ │ │ } │
Tests

Alle Tests wurden auch unter Last ausgeführt. Folgende Tests liefen parallel:

  • insert_test.php
  • insert_test.sh
  • mixed_test.php
  • while [ true ] ; do mariadb -s --user=app --host=10.139.158.174 --port=3306 --password=secret --execute='SELECT @@hostname, COUNT(*) FROM test.test GROUP BY @@hostname' ; sleep 0.5 ; done
  • while [ true ] ; do mariadb -s --user=app --host=10.139.158.174 --port=3306 --password=secret --execute='SELECT @@hostname, COUNT(*) FROM test.test GROUP BY @@hostname FOR UPDATE' ; sleep 0.5 ; done

Alle Tests sind bei allen Manipulationen einwandfrei und ohne Probleme durchgelaufen.

MaxScale Konfigurations-Synchronisation wieder deaktivieren

Auf beiden MaxScale Knoten ist folgender Befehl auszuführen und damit ist die Konfigurations-Synchronisation wieder beendet:

shell> MAXCTRL_WARNINGS=0 maxctrl alter maxscale config_sync_cluster=''
Literatur/Quellen
Taxonomy upgrade extras: maxscaleconfigurationclusterload balancer

Sharding mit MariaDB MaxScale

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)

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

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?

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

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

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

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

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

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

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

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

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

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

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

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

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

Pages