Abfrage dauert zu lange
- Ersteller long_forum
- Erstellt am
Werbung:
long_forum
Benutzer
- Beiträge
- 17
Ich fange mal ganz von vorn an. Die empfangenden Meldungen von rsyslog werden in eine MySQL DB gespeicht. Ziel ist es mittels PHP diese anzeigen zu lassen. Ich erwarte pro Tag ca. 1 Million Meldungen und möchte diese 30 Tage vorhalten - also ca. 30 Millionen Einträge.
Was habe ich vor:
1.) Ich würde gerne nach den Einträgen in den Spalten FromHost, SysLogTag filtern mittels DISTINCT
2.) Ich würde gerne alle Einträge zählen, welche bestimmte Werte in den Spalten FromHost, SysLogTag, Message, DeviceReportedTime enthalten - Filter können auch leer sein (also alle vorhanden Einträge zählen)
3.) Ausgabe der ersten 1000 Einträge der gefundenen Ergebnisse
Die Frage lautet jetzt, wie muss ich meine Indexe(oder ein anderes Verfahren) anlegen/umsetzen, damit ich die gewünschten Informationen möglichst schnell erhalte???
Code:
DESCRIBE SystemEvents;
+--------------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+------------------+------+-----+---------+----------------+
| ID | int(10) unsigned | NO | PRI | NULL | auto_increment |
| CustomerID | bigint(20) | YES | | NULL | |
| ReceivedAt | datetime | YES | | NULL | |
| DeviceReportedTime | datetime | YES | MUL | NULL | |
| Facility | smallint(6) | YES | | NULL | |
| Priority | smallint(6) | YES | | NULL | |
| FromHost | varchar(60) | YES | MUL | NULL | |
| Message | text | YES | MUL | NULL | |
| NTSeverity | int(11) | YES | | NULL | |
| Importance | int(11) | YES | | NULL | |
| EventSource | varchar(60) | YES | | NULL | |
| EventUser | varchar(60) | YES | | NULL | |
| EventCategory | int(11) | YES | | NULL | |
| EventID | int(11) | YES | | NULL | |
| EventBinaryData | text | YES | | NULL | |
| MaxAvailable | int(11) | YES | | NULL | |
| CurrUsage | int(11) | YES | | NULL | |
| MinUsage | int(11) | YES | | NULL | |
| MaxUsage | int(11) | YES | | NULL | |
| InfoUnitID | int(11) | YES | | NULL | |
| SysLogTag | varchar(60) | YES | MUL | NULL | |
| EventLogType | varchar(60) | YES | | NULL | |
| GenericFileName | varchar(60) | YES | | NULL | |
| SystemID | int(11) | YES | | NULL | |
+--------------------+------------------+------+-----+---------+----------------+Was habe ich vor:
1.) Ich würde gerne nach den Einträgen in den Spalten FromHost, SysLogTag filtern mittels DISTINCT
2.) Ich würde gerne alle Einträge zählen, welche bestimmte Werte in den Spalten FromHost, SysLogTag, Message, DeviceReportedTime enthalten - Filter können auch leer sein (also alle vorhanden Einträge zählen)
3.) Ausgabe der ersten 1000 Einträge der gefundenen Ergebnisse
Die Frage lautet jetzt, wie muss ich meine Indexe(oder ein anderes Verfahren) anlegen/umsetzen, damit ich die gewünschten Informationen möglichst schnell erhalte???
akretschmer
Datenbank-Guru
- Beiträge
- 10.379
mal ein paar unsortierte Gedanken:
- FromHost scheint IP-Adressen zu enthalten. Die speicherst Du als Text. Suboptimal, in PG könntest Du dafür einen eigenen Datentypen verwenden. 127.0.0.1 ist gleich zu 127.000.000.001, allerdings nicht bei Dir. Textsuche ist IMMER langsamer als eine Suche in kurzen, binären Daten mit passendem Index.
- Stringsuche mit Wildcards vorne sind Murks (solange man dafür keine passenden Indexe hat, siehe mein Beispiel). Also vermeiden.
- mit mehreren unterschiedlichen Suchbedingungen, von denen nicht immer alle verfügbar sind, kann man keinen kombinierten Index über alle anlegen (was die schnellste Methode wäre), sondern nur je Kriterien einen Index. PG kann in einer Abfrage auf einer Tabelle mehrere Indexe kombinieren, man spricht von einem "Bitmap Index Scan". MySQL kann das nicht.
- man könnte über Partitioning nachdenken, nach dem Datum. Vorteil beim Löschen nach 30 Tagen: einfach die Partition entsorgen. Das reduziert auch insgesamt die Suchaufwand. Allerdings bei 'nur' 1 Million Rows per Tag schon fast zuviel des Aufwandes.
Ansonsten bin ich raus, viel Spaß mit MySQL.
BerndB
Datenbank-Guru
- Beiträge
- 494
Ich stimme dir da fast 100% zu, jedoch wenn du 30 Tage durchsuchen willst kannst du je Partition nur einen Tag speichern, da du sonst nicht suchen kannst und bei dieser Methode legt MySQL eigene Indexe je Partition an, was beim zusammenführen das ganze auch nicht schneller macht.
Weiterhin gibt es bei MySQL keinen Index über ein ganzes TEXT Feld sondern nur über maximal 767 Bytes.
Die IP könnte man Binär (4Byte) speichern was den Zugriff etwas verbessert, aber das Große Problem mit den LIKES wird damit auch nicht gelöst
akretschmer
Datenbank-Guru
- Beiträge
- 10.379
ja, je Tag eine Partition. Da das Datum zumindest teilweise eine Where-Condition ist, würde das in diesen Fällen helfen. Wenn Suche über mehrere Tage ist, würde PG das in Parallel machen. Aber 1 Million Rows pro Partition ist, naja, untere Grenze normalerweise. Müßte man probieren.
long_forum
Benutzer
- Beiträge
- 17
Bin jetzt auf PostgreSQL gewechselt und habe einen INDEX für meine Tabelle angelegt:
Wie kann ich diesen jetzt sinnvoll als Befehl für meine Suche nutzen bzw. wie muss ich meinen INDEX umstellen, damit er sinnvoll wird?
Code:
CREATE INDEX idx_SystemEvents_FromHost_SysLogTag_DeviceReportedTime_Message ON SystemEvents (FromHost, SysLogTag, DeviceReportedTime, Message);
Code:
\d Systemevents
Table "public.systemevents"
Column | Type | Collation | Nullable | Default
--------------------+-----------------------------+-----------+----------+------------------------------------------
id | integer | | not null | nextval('systemevents_id_seq'::regclass)
customerid | bigint | | |
receivedat | timestamp without time zone | | |
devicereportedtime | timestamp without time zone | | |
facility | smallint | | |
priority | smallint | | |
fromhost | character varying(60) | | |
message | text | | |
ntseverity | integer | | |
importance | integer | | |
eventsource | character varying(60) | | |
eventuser | character varying(60) | | |
eventcategory | integer | | |
eventid | integer | | |
eventbinarydata | text | | |
maxavailable | integer | | |
currusage | integer | | |
minusage | integer | | |
maxusage | integer | | |
infounitid | integer | | |
syslogtag | character varying(60) | | |
eventlogtype | character varying(60) | | |
genericfilename | character varying(60) | | |
systemid | integer | | |
Indexes:
"systemevents_pkey" PRIMARY KEY, btree (id)
"idx_systemevents_fromhost_syslogtag_devicereportedtime_message" btree (fromhost, syslogtag, devicereportedtime, message)Wie kann ich diesen jetzt sinnvoll als Befehl für meine Suche nutzen bzw. wie muss ich meinen INDEX umstellen, damit er sinnvoll wird?
BerndB
Datenbank-Guru
- Beiträge
- 494
Eventuell die bitten beantworten
long_forum
Benutzer
- Beiträge
- 17
mysqld Ver 5.7.29-0ubuntu0.18.04.1 for Linux on x86_64 ((Ubuntu))
Ich möchte nach den Spalten FromHost, DeviceReportedTime, Message, SysLogTag filtern. Gleichzeitig, wobei auch einzelne oder alle Suchbegriffe leer sein können!
Ich möchte nach den Spalten FromHost, DeviceReportedTime, Message, SysLogTag filtern. Gleichzeitig, wobei auch einzelne oder alle Suchbegriffe leer sein können!
Code:
SELECT * FROM systemevents LIMIT 5
id | customerid | receivedat | devicereportedtime | facility | priority | fromhost | message | ntseverity | importance | eventsource | eventuser | eventcategory | eventid | eventbinarydata | maxavailable | currusage | minusage | maxusage | infounitid | syslogtag | eventlogtype | genericfilename | systemid
----+------------+---------------------+---------------------+----------+----------+------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------+------------+------------+-------------+-----------+----------------
1 | | 2020-05-27 21:18:03 | 2020-05-27 21:18:52 | 16 | 6 | SERVER1 | Text1 block 192.168.1.100 | | | | | | | | | | | | 1 | var: | | |
2 | | 2020-05-27 21:18:03 | 2020-05-27 21:18:52 | 16 | 6 | SERVER1 | Text2 pass tcp udp | | | | | | | | | | | | 1 | cron: | | |
3 | | 2020-05-27 21:18:04 | 2020-05-27 21:18:53 | 16 | 6 | 192.168.1.1 | password accepted | | | | | | | | | | | | 1 | cron: | | |
4 | | 2020-05-27 21:18:11 | 2020-05-27 21:19:00 | 9 | 6 | SERVER1 | user accepted | | | | | | | | | | | | 1 | var: | | |
5 | | 2020-05-27 21:18:11 | 2020-05-27 21:19:00 | 9 | 6 | 192.168.1.1 | Text3 pass block | | | | | | | | | | | | 1 | var: | | |
(5 rows)long_forum
Benutzer
- Beiträge
- 17
Verstehe ich nicht..Ich weiß ja nach was ich suchen will (FromHost, Message, SysLogTag, DeviceReportedTime). Ich wollte bloß zeigen wie meine Tabelle aussieht und mein erstellter Index.
Habe ich etwas vergessen?
Ganz pauschal: Nach allem was du suchen oder filtern willst erstellst du einen Index 
Kennst du Online-Buch zu indexbasiertem SQL Tuning ?
Kennst du Online-Buch zu indexbasiertem SQL Tuning ?
Ergänzung:
Single vs Composite Indexes in Relational Databases
Dein Index #23 beinhaltet ja mehrere Spalten.
Fragst du diese immer alle ab? Ansonsten solltest du u.U. mehrere Indexe anlegen.
Message wäre evtl. als "Full Text Index" sinnvoll. Hier landen ja unstrukturierte Daten.
MySQL :: MySQL 8.0 Reference Manual :: 12.9 Full-Text Search Functions
PostgreSQL: Documentation: 12: Chapter 12. Full Text Search
Single vs Composite Indexes in Relational Databases
Dein Index #23 beinhaltet ja mehrere Spalten.
Fragst du diese immer alle ab? Ansonsten solltest du u.U. mehrere Indexe anlegen.
Message wäre evtl. als "Full Text Index" sinnvoll. Hier landen ja unstrukturierte Daten.
MySQL :: MySQL 8.0 Reference Manual :: 12.9 Full-Text Search Functions
PostgreSQL: Documentation: 12: Chapter 12. Full Text Search
Werbung:
akretschmer
Datenbank-Guru
- Beiträge
- 10.379
das wirklich schöne an PG ist, daß man sieht, was es macht...
ein Index über (a,b,c) ist gut, wenn von links aus gesehen die ersten Spalten im Index, also a beginnend, dann auch alle im WHERE sind. Fehlt im Where die Spalte a, kann er nicht mehr genutzt werden (u.U. doch, aber das ist eine andere Geschichte).
Indexe über die einzelnen Spalten, die im Where vorkommen KÖNNTEN, gehen auch, diese werden dann als BitmapIndexScan genutzt. Ist aber 'teurer' (siehe cost=...).
Ansonsten gibt es neben den 'normalen' Btree-Indexen noch einige andere, z.B. GIN, GiST, BRIN, SPGiST etc., die unterschiedliche Eigenschaften haben. Dazu kommen partielle und funktionale Indexe. Das ist alles relativ umfangreich, es gibt aber auch diverse Quellen im Netz, z.B. auch bei uns im Blog.
Code:
test=# create table foo (a int, b int, c int);
CREATE TABLE
test=*# insert into foo select random()*100000, random()*100000, random()*100000 from generate_series(1,100000) s;
INSERT 0 100000
test=*# create index idx_abc on foo(a,b,c);
CREATE INDEX
test=*# analyse foo;
ANALYZE
test=*# explain analyse select * from foo where a=3456 and b = 56789 and c=2345;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Index Only Scan using idx_abc on foo (cost=0.42..8.44 rows=1 width=12) (actual time=0.038..0.038 rows=0 loops=1)
Index Cond: ((a = 3456) AND (b = 56789) AND (c = 2345))
Heap Fetches: 0
Planning Time: 0.650 ms
Execution Time: 0.424 ms
(5 rows)
test=*# explain analyse select * from foo where b = 56789 and c=2345;
QUERY PLAN
---------------------------------------------------------------------------------------------------
Seq Scan on foo (cost=0.00..2041.00 rows=1 width=12) (actual time=17.025..17.025 rows=0 loops=1)
Filter: ((b = 56789) AND (c = 2345))
Rows Removed by Filter: 100000
Planning Time: 0.242 ms
Execution Time: 17.061 ms
(5 rows)
test=*# create index idx_a on foo(a);
CREATE INDEX
test=*# create index idx_b on foo(b);
CREATE INDEX
test=*# create index idx_c on foo(c);
CREATE INDEX
test=*# explain analyse select * from foo where b = 56789 and c=2345;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on foo (cost=4.31..11.97 rows=1 width=12) (actual time=0.025..0.025 rows=0 loops=1)
Recheck Cond: (b = 56789)
Filter: (c = 2345)
-> Bitmap Index Scan on idx_b (cost=0.00..4.31 rows=2 width=0) (actual time=0.023..0.023 rows=0 loops=1)
Index Cond: (b = 56789)
Planning Time: 0.247 ms
Execution Time: 0.042 ms
(7 rows)
test=*#ein Index über (a,b,c) ist gut, wenn von links aus gesehen die ersten Spalten im Index, also a beginnend, dann auch alle im WHERE sind. Fehlt im Where die Spalte a, kann er nicht mehr genutzt werden (u.U. doch, aber das ist eine andere Geschichte).
Indexe über die einzelnen Spalten, die im Where vorkommen KÖNNTEN, gehen auch, diese werden dann als BitmapIndexScan genutzt. Ist aber 'teurer' (siehe cost=...).
Ansonsten gibt es neben den 'normalen' Btree-Indexen noch einige andere, z.B. GIN, GiST, BRIN, SPGiST etc., die unterschiedliche Eigenschaften haben. Dazu kommen partielle und funktionale Indexe. Das ist alles relativ umfangreich, es gibt aber auch diverse Quellen im Netz, z.B. auch bei uns im Blog.
Ähnliche Themen
