Information ausblenden
Willkommen im Forum für alle Datenbanken! Registriere Dich kostenlos und diskutiere über DBs wie Mysql, MariaDB, Oracle, Sql-Server, Postgres, Access uvm

Performance-Problem

Dieses Thema im Forum "MySQL und MariaDB" wurde erstellt von Coolia, 18 Juli 2017.

  1. Coolia

    Coolia Benutzer

    Hallo,

    ich wende mich an euch, da ich mit einer Tabelle scheinbar Performance-Probleme habe und auf der Suche nach hilfreichen Tipps bin.

    Die Tabelle hat 3 Spalten:
    spalte1: smallint unsigned; KEY
    spalte2: bigint unsigned
    spalte3: tinyblob

    Die Datentypen der Spalten sind zwingend vorgegeben.

    in Spalte 1 und 2 kommen die Werte jeweils mehrfach vor. Ein Primary Key auf beide Spalten hat keinen merklichen Vorteil gebracht.

    Die Tabelle enthaelt zu Testzwecken z. Zt. "nur" ca. 41 Millionen Datensätze, was ein winziger Bruchteil dessen ist, was geplant ist ( 100x - 1000x).

    Ein optimize wurde schon durchgeführt.

    Die Lesezeiten sind auf MariaDB 10.1.21 sowie auf einer älteren MySQL-Version nahezu identisch trotz unterschiedlich leistungsfähiger Hardware.

    Die Abfrage lautet:
    SELECT spalte2, spalte3 FROM tabelle WHERE spalte1=wert;
    und liefert in der Testumgebung jeweils ca. 10.000 Datensätze zurück.

    Gibt es noch Möglichkeiten, die Abfragezeiten zu beschleunigen?

    Danke im Voraus!
     
  2. akretschmer

    akretschmer Datenbank-Guru

    Scheinbar? Geht es etwas genauer?

    "in Spalte 1 und 2 kommen die Werte jeweils mehrfach vor. Ein Primary Key auf beide Spalten hat keinen merklichen Vorteil gebracht." Ähm, wenn da Wertepaare doppelt vorkommen, dann kannst Du darauf keinen PK setzen. Können Werte doppelt vorkommen?

    Was sagt EXPLAIN?

    Btw.: bei den Datenmengen hast möglicherweise nicht die optimale Wahl mit der DB gemacht...
     
  3. akretschmer

    akretschmer Datenbank-Guru

    PS.:

    eine wie folgt erstelle Tabelle:

    Code:
    test=# create table coolia as select (random()*10000)::int, (random()*100000000)::bigint, repeat(md5(s::text),10) from generate_series(1,41000000) s;
    SELECT 41000000
    test=*# \d coolia 
      Tabelle »public.coolia«
     Spalte |  Typ  | Attribute
    --------+---------+-----------
     int4  | integer |
     int8  | bigint  |
     repeat | text  |
    
    test=*#
    
    braucht 13 Millisekunden für solch eine Abfrage (liefert 4073 Rows)

    Code:
    test=*# explain analyse select * from coolia where int4 = 4712;
      QUERY PLAN   
    ------------------------------------------------------------------------------------------------------------------------------
     Bitmap Heap Scan on coolia  (cost=4749.31..601851.30 rows=205000 width=44) (actual time=2.886..12.832 rows=4073 loops=1)
      Recheck Cond: (int4 = 4712)
      Heap Blocks: exact=4070
      ->  Bitmap Index Scan on idx_coolia  (cost=0.00..4698.06 rows=205000 width=0) (actual time=1.431..1.431 rows=4073 loops=1)
      Index Cond: (int4 = 4712)
     Planning time: 0.102 ms
     Execution time: 13.664 ms
    (7 Zeilen)
    
    test=*#
    
     
  4. Coolia

    Coolia Benutzer

    Stimmt, das mit dem PK war dämlich (war bei deutlich kleineren Mengen; später wurde aufgestockt).
    spalte1 und 2 kommen Werte mehrfach vor.

    explain sagt:
    select_type: simple
    type: ref
    possible_keys: spalte1
    key: spalte1
    key_len: 2
    ref: const
    rows: 10074
    Extra:
     
  5. akretschmer

    akretschmer Datenbank-Guru

    mit nur Index auf der ersten Spalte:

    Code:
    test=# explain analyse select * from coolia where int4 = 4712;
      QUERY PLAN   
    -------------------------------------------------------------------------------------------------------------------------------
     Bitmap Heap Scan on coolia  (cost=3841.32..600943.30 rows=205000 width=44) (actual time=1.256..10.234 rows=4073 loops=1)
      Recheck Cond: (int4 = 4712)
      Heap Blocks: exact=4070
      ->  Bitmap Index Scan on idx_coolia2  (cost=0.00..3790.07 rows=205000 width=0) (actual time=0.733..0.733 rows=4073 loops=1)
      Index Cond: (int4 = 4712)
     Planning time: 0.223 ms
     Execution time: 10.669 ms
    (7 Zeilen)
    
    test=*#
    
    Das MySQL-Explain ist natürlich direkt für die Tonne...
     
  6. Coolia

    Coolia Benutzer

    ok, also das Problem ist nicht die Query-Zeit (aktueller Test: 0,0153 Sekunden)
    sondern das fetchen der Daten.

    Scheint also nur noch bessere Hardware zu helfen...
     
  7. ukulele

    ukulele Datenbank-Guru

    Vielleicht ist es einfach die Datenmenge aus Spalte2 und Spalte3 die vermutlich über das Netzwerk gesendet wird.
     
  8. Coolia

    Coolia Benutzer

    Momentan wird auf einer lokalen Installation getestet. Das Einlesen / fetchen der Daten soll auch auf lokalen Installationen erfolgen. Das Netzwerk kommt erst deutlich später zum Zuge :)
     
  9. ukulele

    ukulele Datenbank-Guru

    Dennoch gehen die Daten zwischen DB und Client vermutlich dennoch über den Netzwerkstack, so in der Tiefe stecke ich da aber auch nicht drin.

    Du kannst ja mal im SELECT-Teil nur Spalte2 abfragen, das geht vermutlich deutlich schneller.
     
  10. Coolia

    Coolia Benutzer

    Ja, das könnte ich machen. Nur dann bekomme ich irgendwelche Daten, und das nutzt mir nicht wirklich was.
     
  11. ukulele

    ukulele Datenbank-Guru

    Ich meinte auch um die Laufzeit besser einzugrenzen. Für die DB ist es etwa genauso aufwendig nur die Spalte2 zu X Zeilen zu "suchen", wie Spalte2 und Spalte3 auszuliefern. Spalte3 wird nur mehr Traffic verursachen.
     
  12. Coolia

    Coolia Benutzer

    Momentan setze ich gerade alles auf eine SSD auf, vielleicht hilfts ja, mal abwarten.

    Mir ist nicht klar, was mir die Ergebnisse bringen würden. Laden muss ich dann ja doch so, wie im Eingangspost geschrieben, da nur die Kombination von spalte2 und spalte3 einen gültigen Datensatz ergibt.
    Zum Testen werde ich dann auch noch auf die Spalten 2 und 3 einen Key setzen; sieht mir zwar momentan nicht sinnvoll aus, aber sonst kann ich wohl nichts anderes mehr verbessern abfragemässig.

    Falls ein geändertes Layout bessere Auslieferungszeiten bringt, würde ich auch das machen. Nur auch dazu fällt mir momentan nichts besseres ein.
     
  13. akretschmer

    akretschmer Datenbank-Guru

    Warum?

    PG verwendet für Blobs und so'n Kram TOAST-Tables. Sprich: die Daten finden sich in einer extra Tabelle. Das ist alles transparent. Hat aber den Vorteil, daß das Scannen der Haupttabelle schneller geht, weil diese deutlich kleiner ist. Dein Problem sind aber offenbar eh nicht die Suchzeiten, sondern die Auslieferungszeiten. Daher würde ich nicht zuviel Erwartungen in die SSD setzen.
     
    BerndB gefällt das.
  14. Coolia

    Coolia Benutzer

    Weil mir echt nichts weiter einfällt und ich einfach alles probiere. Wenn es doch nichts bringt, kann ich ja wieder zurückrudern. Bin ja dabei, die bestmögliche Lösung zu finden, da kann ich einiges probieren.

    Zu den Spalten: die Werte in spalte 1 und spalte 2 können zwar mehrfach vorkommen, aber spalte1 UND spalte2 sind unique. Also werde ich das als Key auch noch mal versuchen.
    edit: Ich denke, das hatte ich schon als Primary, bin aber nicht sicher; bin soviel am rumdoktorn.

    Aber wie du schon sagtest: Das eigentlich Problem dürften nicht die Suchzeiten, sondern die Auslieferungszeiten sein.
     
    Zuletzt bearbeitet: 18 Juli 2017
  15. akretschmer

    akretschmer Datenbank-Guru

    Wenn das so ist und wenn das auch ein Constraint ist, dann ist das ein PK.
     
Die Seite wird geladen...

Diese Seite empfehlen

  1. Diese Seite verwendet Cookies. Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden