You are here
dbstat für MariaDB (und MySQL)
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
sowiemetadata_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, wassar/sysstat
tut. 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).