You are here

Home » Blogs » oli's blog

Wir bauen uns ein Data Warehouse aus dem General Query Log

Tue, 2024-01-30 16:17 — oli

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 warehouse
general query log