Performance-Problem

C

Coolia

Benutzer
Beiträge
12
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!
 
Werbung:
Coolia schrieb:
Tabelle scheinbar Performance-Probleme
Zum Vergrößern anklicken....

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...
 
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=*#
 
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:
 
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...
 
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...
 
Vielleicht ist es einfach die Datenmenge aus Spalte2 und Spalte3 die vermutlich über das Netzwerk gesendet wird.
 
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 :)
 
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.
 
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.
 
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.
 
Coolia schrieb:
auf die Spalten 2 und 3 einen Key setzen
Zum Vergrößern anklicken....
Warum?

Falls ein geändertes Layout bessere Auslieferungszeiten bringt, würde ich auch das machen.
Zum Vergrößern anklicken....

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.
 
akretschmer schrieb:
Warum?
Zum Vergrößern anklicken....

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