Performancefrage

Hi, ich habe mal eine allgemeine Frage zu einer Query bzw. zur Performance von pgsql (8.2):
Wir setzen gerade an der Uni eine neue Datenbank auf für recht komplexe Simualtionen mit einiges an Datenmengen.

Dabei treten nun unter anderem auch Queries der folgenden Art auf:

INSERT INTO messpunkt (zs_nr, mw_nr)
SELECT DISTINCT v.zs_nr, v.mw_nr
FROM v_dat_temp v
WHERE v.mp_nr IS NULL



v_dat_temp ist dabei eine eher kleine temporäre Tabelle, aus der wir die Daten einfügen. Tabelle messpunkt ist recht groß, drei Spalten mit etwa 6 Mio Einträgen. Die Tabelle ist indiziert, wir haben auch schon ein bißchen rumprobiert, um die Queries zu optimieren.

Wenn man nun viele Daten einfügt und mitloggt, sieht man, das Queries dieser Art teilweise mehrere Sekunden zur Ausführung brauchen.
Das Testsystem ist ein Athlon Dualcore Desktoprechner mit 4GB Ram und normaler HD ohne Raid. Das ganze läuft “nebenbei” auf Fedora 7, also mit gestartetem KDE und Xemacs, allerdings laufen sonst keine Speicherfressenden Anwendungen im Hintergrund.
Sind solche Zeiten von mehreren Sekunden für derartige Queries normal oder ist das VIEL zu langsam?

Woher sollen wir das wissen?

Wie sieht denn der Queryplan aus in dem Fall?
Wie sehen denn die zuständigen Configeinstellungen aus? Fahrt ihr eine Defaultconfig oder wisst ihr, wie man hier optimieren kann?

Zweiteres.

Was sagt ein EXPLAIN ANALYSE für das SELECT?

Andreas

Sorry, habe gerade keinen Zugriff auf den Rechner, werde morgen mal die Query mit dem zugehörigen explain analyse posten.

So, nun mal ein wenig ausführlicher:

Die Datenbank besteht aus einer Reihe von Tabellen, die jeweils über foreign keys ineinander verschachtelt sind.
…->…->messpunkt->messung->…
Dabei ist jede Tabelle mit einem Eintrag wie “nr” versehen, auf den die darüberliegende Tabelle über einen foreign key referenziert.
Ein Datensatz wird zuerst aus Performancegründen mit copy in eine temporäre Tabelle geschrieben und dann von dort aus auf die einzelnen Tabellen verteilt.

Die beiden kritischen Queries die mir Sorgen machen sind:
INSERT INTO messpunkt (zs_nr, mw_nr)
SELECT DISTINCT v.zs_nr, v.mw_nr
FROM v_dat_temp v
WHERE v.mp_nr IS NULL

und
INSERT INTO messung (minute_tag, messpunkt)
SELECT DISTINCT v.minute_tag, v.mp_nr
FROM v_dat_temp v
WHERE v.ms_nr IS NULL

Die Zeile mit IS NULL ist nötig, da wir schon vor dem einfügen die temporäre Tabelle so filtern, das v.ms_nr nur bei den Einträgen NULL gesetzt wird (über UPDATE), wo die Einträge noch nicht in messung/messpunkt vorhanden sind, da wir keine doppelten Einträge einfügen wollen/können(UNIQUE Bedingungen).

Hier mal eine Bespielausgabe von EXPLAIN ANALYSE der beiden Queries:
Subquery Scan “SELECT” (cost=77.07…87.83 rows=478 width=8) (actual time=3.971…11.007 rows=478 loops=1)
-> Unique (cost=77.07…80.66 rows=478 width=8) (actual time=3.743…6.944 rows=478 loops=1)
-> Sort (cost=77.07…78.27 rows=478 width=8) (actual time=3.739…4.822 rows=478 loops=1)
Sort Key: zs_nr, mw_nr
-> Seq Scan on v_dat_temp2 v (cost=0.00…55.80 rows=478 width=8) (actual time=0.043…2.614 rows=478 loops=1)
Filter: (mp_nr IS NULL)
Trigger for constraint messpunkt_mw_nr_fkey: time=2342.735 calls=478
Trigger for constraint messpunkt_zs_nr_fkey: time=1529.944 calls=478
Total runtime: 28783.047 ms

und

Subquery Scan “SELECT” (cost=99.72…119.07 rows=860 width=6) (actual time=5.228…14.988 rows=860 loops=1)
-> Unique (cost=99.72…106.17 rows=860 width=6) (actual time=5.002…10.137 rows=860 loops=1)
-> Sort (cost=99.72…101.87 rows=860 width=6) (actual time=4.997…6.780 rows=860 loops=1)
Sort Key: minute_tag, mp_nr
-> Seq Scan on v_dat_temp2 v (cost=0.00…57.80 rows=860 width=6) (actual time=0.028…2.747 rows=860 loops=1)
Filter: (ms_nr IS NULL)
Trigger for constraint messung_messpunkt_fkey: time=329.798 calls=860
Total runtime: 463.276 ms

wobei der letztere eher auf der schnelleren Seite war, auch dort treten gerne mal runtimes von mehreren Sekunden auf.

Hier noch mal eine der UPDATE queries, so ganz zufrieden bin ich dort mit der Performance auch nicht.
UPDATE v_dat_temp2 AS v SET ms_nr = p.nr
FROM messung p
WHERE p.minute_tag = v.minute_tag AND p.messpunkt = v.mp_nr AND p.minute_tag IS NOT NULL
und
Nested Loop (cost=0.00…20287.74 rows=155 width=56) (actual time=560.524…2574.593 rows=1020 loops=1)
-> Seq Scan on v_dat_temp2 v (cost=0.00…57.80 rows=1880 width=52) (actual time=0.046…6.744 rows=1880 loops=1)
-> Index Scan using messung_minute_tag_key on messung p (cost=0.00…10.75 rows=1 width=10) (actual time=1.358…1.360 rows=1 loops=1880)
Index Cond: ((p.minute_tag = v.minute_tag) AND (p.messpunkt = v.mp_nr))
Total runtime: 3284.059 ms

Es interessiert mich vor allem, ob da von den Queries her irgendwelche groben Schnitzer drin sind, die das ganze ausbremsen. Wir haben schon einiges probiert mit Umstellen der Queries, Indices erstellen usw., ohne allerdings wirklich merkliche Geschwindigkeitsgewinne. Die postgres.conf ist sicherlich nicht optimal eingerichtet, aber wir wollen erstmal ausschließen, das schon in den Queries etwas grob falsch läuft.

wie groß ist die Tabelle insgesamt? Das IS NULL erzwingt einen Seq-Scan, der kostet.

Trigger for constraint messpunkt_mw_nr_fkey: time=2342.735 calls=478
Trigger for constraint messpunkt_zs_nr_fkey: time=1529.944 calls=478
Total runtime: 28783.047 ms

Was sind das für Trigger?

Andreas

Die temporäre Tabelle ist nicht allzu groß, liegt jeweils im Bereich von einigen tausend Zeilen, das Ganze ist auch fix, wächst also nicht mit der Datenbankgrösse.

Spalten
Was sind das für Trigger?

Andreas

Da bin ich auch ein wenig überfragt wo die genau herkommen, hier mal ein \d+ von messpunkt:

Column | Type | Modifiers | Description
--------±--------±-------------------------------------------------------±------------
nr | integer | not null default nextval(‘messpunkt_nr_seq’::regclass) |
zs_nr | integer | |
mw_nr | integer | |
Indexes:
“messpunkt_pkey” PRIMARY KEY, btree (nr)
“messpunkt_zs_nr_key” UNIQUE, btree (zs_nr, mw_nr)
“messpunkt_mw_nr_idx” btree (mw_nr)
“messpunkt_nr_idx” btree (nr)
“messpunkt_zs_nr_idx” btree (zs_nr)
Foreign-key constraints:
“messpunkt_mw_nr_fkey” FOREIGN KEY (mw_nr) REFERENCES de_mw(nr)
“messpunkt_zs_nr_fkey” FOREIGN KEY (zs_nr) REFERENCES de_zs(zs)
Has OIDs: no

“messpunkt_nr_idx” btree (nr) ist sinnfrei, weil durch den PK bereits vorhanden. Der “messpunkt_zs_nr_key” ist IMHO ebenfalls sinnfrei, weil 2 separate Indexe bestehen, welche auch via Bitmap-Scan verwendet werden können.

Andreas

Beim ersten hast du auf jeden Fall Recht, der ist sinnfrei.

Beim zweiten bin ich mir da nicht so sicher, das ist ja auch kein “richtiger” Index sondern stammt von der Uniquebedingung auf (mw_nr, zs_nr). Übersehe ich da irgendwas?

Das hatte ich übersehen.

Brauchst Du dann die zwei einzelnen?

Andreas

Die zwei einzelnen haben wir eingefügt, um folgenden UPDATE zu beschleunigen:

UPDATE v_dat_temp AS v SET mp_nr = p.nr
FROM messpunkt p
WHERE p.zs_nr = v.zs_nr AND p.mw_nr = v.mw_nr

da hier sonst wohl ein Seq Scan über messpunkt nötig wäre, oder?

Hier noch der explain analyse zum Update :

Nested Loop (cost=0.00…17460.19 rows=44 width=56) (actual time=7.243…2490.297 rows=1402 loops=1)
-> Seq Scan on v_dat_temp v (cost=0.00…55.80 rows=1880 width=52) (actual time=0.058…8.594 rows=1880 loops=1)
-> Index Scan using messpunkt_zs_nr_key on messpunkt p (cost=0.00…9.24 rows=1 width=12) (actual time=1.300…1.312 rows=1 loops=1880)
Index Cond: ((p.zs_nr = v.zs_nr) AND (p.mw_nr = v.mw_nr))
Total runtime: 2558.516 ms

Vielleicht kann man das noch irgendwie besser machen?

Dann sollte auch ein where (p.zs_nr,p.mw_nr)=(v.zs_nr,v.mw_nr) gehen, welches den kombinierten Index nutzen kann.

Nur bin ich grad a bissl verwirrt, mal wird v_dat_temp geUPDATEt mit Daten aus messpunkt und dann wieder insert in messpunkt mit Daten aus v_dat_temp …


Andreas

Jo, danke, werden wir auf jeden fall mal ausprobieren.

Nur bin ich grad a bissl verwirrt, mal wird v_dat_temp geUPDATEt mit Daten aus messpunkt und dann wieder insert in messpunkt mit Daten aus v_dat_temp …

Andreas

Die Daten sind teilweise schon in messpunkt vorhanden und wir wollen nur die neuen Einträge inserten. Deshalb der Abgleich zwischen v_dat_temp und messpunkt in dem update, anschliessend fügen wir dann halt nur die Zeilen eiin, in denen die entsprechende Spalte mp_nr NULL ist.

So, ich habe jetzt mal die Änderungen eingebaut, also die überfüssigen Indices entfernt und die Updateanfragen modifiziert. Die Performance scheint sich zum Teil gebessert zu haben, insgesamt dauern die Queries aber immer noch Recht lange.

Hier nochmal die Queries mit den dazugehörigen ‘explain analyse’:

UPDATE v_dat_temq AS v SET mp_nr = p.nr
FROM messpunkt p
WHERE (p.zs_nr,p.mw_nr) = (v.zs_nr,v.mw_nr)

Nested Loop (cost=0.00…21181.50 rows=74 width=56) (actual time=5.720…2713.492 rows=1742 loops=1)
-> Seq Scan on v_dat_temq v (cost=0.00…63.32 rows=2132 width=52) (actual time=5.523…13.429 rows=2132 loops=1)
-> Index Scan using messpunkt_zs_nr_key on messpunkt p (cost=0.00…9.89 rows=1 width=12) (actual time=1.257…1.259 rows=1 loops=2132)
Index Cond: ((p.zs_nr = v.zs_nr) AND (p.mw_nr = v.mw_nr))
Total runtime: 2767.737 ms

INSERT INTO messpunkt (zs_nr, mw_nr)
SELECT DISTINCT v.zs_nr, v.mw_nr
FROM v_dat_temq v
WHERE v.mp_nr IS NULL

Subquery Scan “SELECT” (cost=96.10…104.88 rows=390 width=8) (actual time=3.334…7.972 rows=390 loops=1)
-> Unique (cost=96.10…99.03 rows=390 width=8) (actual time=3.254…5.602 rows=390 loops=1)
-> Sort (cost=96.10…97.08 rows=390 width=8) (actual time=3.249…4.048 rows=390 loops=1)
Sort Key: zs_nr, mw_nr
-> Seq Scan on v_dat_temq v (cost=0.00…79.32 rows=390 width=8) (actual time=0.388…2.374 rows=390 loops=1)
Filter: (mp_nr IS NULL)
Trigger for constraint messpunkt_mw_nr_fkey: time=1906.616 calls=390
Trigger for constraint messpunkt_zs_nr_fkey: time=12.470 calls=390
Total runtime: 2305.529 ms


INSERT INTO messung (minute_tag, messpunkt)
SELECT DISTINCT v.minute_tag, v.mp_nr
FROM v_dat_temq v
WHERE v.ms_nr IS NULL

Subquery Scan “SELECT” (cost=132.54…152.81 rows=901 width=6) (actual time=2497.051…2507.155 rows=901 loops=1)
-> Unique (cost=132.54…139.30 rows=901 width=6) (actual time=2496.821…2502.091 rows=901 loops=1)
-> Sort (cost=132.54…134.79 rows=901 width=6) (actual time=2496.812…2498.588 rows=901 loops=1)
Sort Key: minute_tag, mp_nr
-> Seq Scan on v_dat_temq v (cost=0.00…88.32 rows=901 width=6) (actual time=0.029…2494.242 rows=901 loops=1)
Filter: (ms_nr IS NULL)
Trigger for constraint messung_messpunkt_fkey: time=2598.108 calls=901
Total runtime: 5124.530 ms


Ich habe mal nicht alle Update-Queries mitgepostet, weil die alle gleich aufgebaut sind. Richtig Sorgen machen mir hier eher die inserts, bei hoher Auslastung und ungünstigen Datensätzen können die auch schonmal über 10 Sekunden dauern. Sind da noch offensichtliche Bremsen drin, die ich übersehen habe oder ist das System hier einfach an seinen Grenzen/SQL schlecht konfiguriert?

Hier nochmal eine Beschreibung des grundsätzlichen Problems:

Man hat zwei Tabellen, eine sehr große (A) und eine kleine (B). Man will nun aus Tabelle B alle Einträge nach A übernehmen, die noch nicht in A vorhanden sind.

Folgender Aufbau der Tabellen:
A

a b c

B
a b c

wobei bei Tabelle B Spalte c leer ist, in Tabelle A wird c über CREATE SEQUENCE vergeben.

Nun würde ich folgendermassen vorgehen:

update B set c = A.c
from A
where (B.a, B.b) = (A.a, A.b)



insert into A (a,b)
select distinct (B.a, B.b)
from B
where c is null

Ich suche hier den performantesten Weg, eine solche Operation auszuführen, mit left join habe ich auch schon rumprobiert, das schien aber noch deutlich langsamer als obiger Weg zu sein. Richtig zufrieden bin ich hier mit der Performance aber nicht. Ist an dem Aufbau irgendwas grob falsch, was das ganze extrem ausbremst?

Wenn ich das richtig sehen, triffst Du doch damit in B nur Datensätze, die schon in A sind, oder?

insert into A (a,b)
select distinct (B.a, B.b)
from B
where c is null

Damit triffst Du in B Datensätze, wo C NULL ist, die also im ersten Update nicht getroffen wurden.
Irknwie versteh ich den Sinn nicht ganz.

Nach Deiner beschreibung täte doch eher sinngemäß passen:

insert into A select a,b,nextval(‘the_sequence’) from B where c is null


Andreas

Sorry, war vielleicht etwas undurchsichtig von mir beschrieben das Ganze.

Eigentlich tut es hier doch genau das was wir wollten. Im ersten Update wurden die “doppelten”, die sowohl in A als auch B drin stehen, markiert, indem man dort in B die Spalte c setzt. Alle, wo B.c NULL ist, sind dann dementsprechend nicht in A und werden dort eingefügt.

Nach Deiner beschreibung täte doch eher sinngemäß passen:

insert into A select a,b,nextval(‘the_sequence’) from B where c is null

Ist das nicht genau die Query, die ich auch verwende, minus die Klammern um a,b und das distinct(die Sequenz ist bei uns in der Tabellendefinition enthalten)?
Und ohne das Update bringt die Query ja nichts, da dann B.c immer null wäre.

Wir haben jetzt mal die Inserts durch Copy ersetzt, also erst die Selectanfrage des Inserts ausgeführt und die daraus erhaltenen Daten dann aus dem Client per Copy in die Datenbank eingefügt (Wir verwenden eine Java/Scheme Umgebung, wo wir die Daten in Listen zwischenspeichern können).

Leider keine echte Geschwindigkeitsänderung, für große Datensätze dauern die Copies teilweise auch über 10 Sekunden.
Die Tabellen, in die eingefügt wird, liegen im Bereich zwischen 5 und 25 Millionen Datensätze bei einer Breite von 2 bis 4 Spalten, eingefügt werden zwischen 500 und 3000 Datensätze.
Keine Ahnung ob unsere Zeiten dafür sehr mies oder im Rahmen sind :frowning: