Abfrage dauert zu lange

Poste mal 2 - 3 Zeilen aus der Tabelle, damit ich sehen kann was da so drin steht und was du willst.

Weiterhin schreib noch welche MySQL Version du einsetzt.
 
Werbung:
Poste mal 2-3 Zeilen deiner Tabelle, damit ich sehe was da so drin steht und was du willst.

Und die genaue MySQL Version
 
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.

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???
 
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???

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.
 
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.
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
 
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.
 
Die generelle Frage ist, ob rsyslog mit einer DB das richtige ist oder man z.B. auf andere Systeme wie Splunk, Elasticsearch (ELK Stack), ... setzt.
 
Bin jetzt auf PostgreSQL gewechselt und habe einen INDEX für meine Tabelle angelegt:

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?
 
Man hat erst Abfragen und beschleunigt diese mit einem Index und nicht andersherum.
Du musst doch wissen, was du suchst.
 
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!


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)
 
Man hat erst Abfragen und beschleunigt diese mit einem Index und nicht andersherum.
Du musst doch wissen, was du suchst.


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?
 
Werbung:
das wirklich schöne an PG ist, daß man sieht, was es macht...

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.
 
Zurück
Oben