You are here
dbstat für MariaDB (und MySQL)
Thu, 2024-03-14 11:33 — oli
Wie funktioniert
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_sizesammelt 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
processlistmacht 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_lcksowiemetadata_lockfestgehalten 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_variablesabgedeckt. Wer oder warum die Variable geändert hat, kann leider datenbankseitig nicht eruiert werden. Dazu sind betriebliche Prozesse notwendig. - Das letzte Modul,
global_statusdeckt eigentlich das ab, wassar/sysstattut. Es sammelt die Werte vonSHOW 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:
| Modul | Sammeln | Löschen | Mengengerüst | Bemerkungen |
|---|---|---|---|---|
table_size | 1/d um 02:04 | 12/h, 1000 rows, > 31 d | 1000 tab x 31 d = 31k rows | Sollte bis 288k Tabellen funktionieren. |
processlist | 1/min | 1/min, 1000 rows, > 7 d | 1000 con x 1440 min x 7 d = 10M rows | Sollte bis 1000 Concurrent Connections funktionieren. |
trx_and_lck | 1/min | 1/min, 1000 rows, > 7 d | 100 lck x 1440 min x 7 d = 1M rows | Hängt sehr stark von der Anwendung ab. |
metadata_lock | 1/min | 12/h, 1000 rows, > 30 d | 100 mdl x 1440 x 30 d = 4M rows | Hängt sehr stark von der Anwendung ab. |
global_variables | 1/min | nie | 1000 rows | Im Normalfall sollte diese Tabelle nicht anwachsen. |
global_status | 1/min | 1/min, 1000 rows, > 30 d | 1000 rows x 1440 x 30 d = 40M rows | Kann 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: