Ergebnis 1 bis 8 von 8

Thema: Funktion wird immer langsamer

  1. #1
    kalapsi ist offline Neuer Benutzer
    Registriert seit
    14.05.2009
    Beiträge
    16

    Beitrag Funktion wird immer langsamer

    Hallo,
    ich habe eine Funktion welche laut EXPLAIN intern nur Indexe verwendet und theoretisch sehr schnell ablaufen sollte, aber während einigen Stunden immer langsamer wird: (von 15ms auf 500ms nach ca. 8 Stunden)
    Umgebung: PG 8.3.7, Windows XP

    Die Daten werden ein paar Sekunden bevor die Funktion ausgeführt wird, von einer anderen Funktion in die Haupttabelle eingefügt (Alle 1,5sec ca. 50 Reihen), sollten sich also im Speicher befinden, oder?
    Die eigentliche Funktion ist dann ein relativ unspektakulärer SELECT. (Einzige Besonderheit: Er wird dynamisch zusammengesetzt und dann damit eine TEMP Tabelle gefüllt)

    Das witzige ist, dass die Funktionsaufrufe wieder sehr schnell laufen wenn eine von folgenden Dingen gemacht wird:

    • PG Neustart
    • Client Neustart (????)
    • Einige Daten löschen und VACUUM ANALYZE aufrufen
    Wenn die Funktion mit denselben Parametern (demselben Primary Key) ein zweites Mal aufgerufen wird, läuft sie wieder sehr schnell.

    Im Verdacht hatte ich schon die Windows Speicherverwaltung, da man ja nie genau weiß, was für eine Art von Speicher (RAM oder Platte) man zugewiesen bekommt. Habe dann die Auslagerungsdatei auf 0 gesetzt. Hat aber auch nichts gebracht.
    shared_buffers = 800 (runtersetzen auf 200 bringt auch nix)

    Auf Deutsch: Bin mittlerweile zieeeemlich ratlos. Hat irgendjemand schon einmal einen solchen Effekt gehabt?
    Geändert von kalapsi (08.09.2009 um 11:59 Uhr)

  2. #2
    akretschmer ist offline Super-Moderator
    Registriert seit
    29.10.2006
    Ort
    Nähe Dresden
    Beiträge
    5.337

    Standard

    Zitat Zitat von kalapsi Beitrag anzeigen
    Hallo,
    ich habe eine Funktion welche laut EXPLAIN intern nur Indexe verwendet und theoretisch sehr schnell ablaufen sollte, aber während einigen Stunden immer langsamer wird: (von 15ms auf 500ms nach ca. 8 Stunden)
    Umgebung: PG 8.3.7, Windows XP

    Die Daten werden ein paar Sekunden bevor die Funktion ausgeführt wird, von einer anderen Funktion in die Haupttabelle eingefügt (Alle 1,5sec ca. 50 Reihen), sollten sich also im Speicher befinden, oder?
    Im Speicher ganz sicher. Kommt nur drauf an, was Du darunter verstehst.

    Wo wie Daten aber recht sicher NICHT sind: in den Statistiken, die der Planner verwendet. Setze also mal nach dem Insert ein Analayse ein.

    Ansonsten müßte man ein paar Dinge mehr wissen:
    - passende Indexe
    - konkrete Abfrage
    - ...

    Die Abfrage wird dynamisch erzeugt und mit Execute ausgeführt?

    Andreas
    --
    ads Das hatte dir akretschmer gestern schon gesagt und siehe da sobald du das machst, funktioniert es ;-)
    https://www.xing.com/profile/Andreas_Kretschmer7

  3. #3
    kalapsi ist offline Neuer Benutzer
    Registriert seit
    14.05.2009
    Beiträge
    16

    Standard

    Zitat Zitat von akretschmer Beitrag anzeigen
    Im Speicher ganz sicher. Kommt nur drauf an, was Du darunter verstehst.
    --> Hmmm, beim ersten Anfassen der Daten vermutlich im 1.Level Cache. Daher sollte eigentlich bei Auslagerungsdateigröße = 0 alles im RAM ablaufen.

    Zitat Zitat von akretschmer Beitrag anzeigen
    Wo wie Daten aber recht sicher NICHT sind: in den Statistiken, die der Planner verwendet. Setze also mal nach dem Insert ein Analayse ein.
    Es laufen automatische VACUUM ANALYZE. Ausserdem sind die Art der Daten immer sehr ähnlich.

    Zitat Zitat von akretschmer Beitrag anzeigen
    Ansonsten müßte man ein paar Dinge mehr wissen:
    - passende Indexe
    - konkrete Abfrage
    - ...
    Die Abfrage wird dynamisch erzeugt und mit Execute ausgeführt?
    OK, dann doch etwas mehr Infos:
    Folgender String wird intern zusammengebaut:
    CREATE TEMP TABLE tmp ON COMMIT DROP AS
    SELECT attribid, xdatatype, value, unit, partpassageattrib.xmachineid, typeid, jobid, partid, time, xpartpnr
    FROM partpassageattrib, partpassage, attrib
    WHERE xpartpnr = partpnr
    AND partpassage.xmachineid >= 1000
    AND partpassageattrib.xmachineid BETWEEN 1000 AND 1099 AND attribid = xattribid
    AND attrib.xmachineid = partpassageattrib.xmachineid
    AND partpnr = 41243
    ORDER BY partpnr DESC;
    ALTER TABLE tmp ADD COLUMN attribnr integer DEFAULT 1;
    Dieser String wird nun mit "EXECUTE sqlstring;" ausgeführt.

    Das Ergebnis wird nun sortiert zurückgegeben:
    -- Returns the 'tmp' table, but only if there is any result
    IF EXISTS(SELECT * FROM tmp) THEN
    IF "@order" IS NOT NULL THEN
    var_sql = var_sql || 'SELECT * FROM tmp ORDER BY '
    || "@order" || ';';
    FOR var_row IN EXECUTE var_sql LOOP
    RETURN next var_row;
    END LOOP;
    ELSE
    RETURN query SELECT * FROM tmp;
    END IF;
    END IF;
    Tabelle partpassageattrib (Tabelle ca. 100MB, Index ca. 60MB)
    PRIMARY KEY (xpartpnr, xattribid, xmachineid)

    Tabelle partpassage: (Tabelle: 4MB, Index: 6MB)
    PRIMARY KEY (partpnr, xmachineid)
    zusätzlicher INDEX auf partid

    Tabelle attrib: (Tabelle: 8k)
    PRIMARY KEY (attribid, xmachineid)

  4. #4
    akretschmer ist offline Super-Moderator
    Registriert seit
    29.10.2006
    Ort
    Nähe Dresden
    Beiträge
    5.337

    Standard

    Zitat Zitat von kalapsi Beitrag anzeigen

    OK, dann doch etwas mehr Infos:
    Folgender String wird intern zusammengebaut:[INDENT]CREATE TEMP TABLE tmp ON COMMIT DROP AS
    SELECT attribid, xdatatype, value, unit, partpassageattrib.xmachineid, typeid, jobid, partid, time, xpartpnr
    FROM partpassageattrib, partpassage, attrib
    WHERE xpartpnr = partpnr
    AND partpassage.xmachineid >= 1000
    AND partpassageattrib.xmachineid BETWEEN 1000 AND 1099 AND attribid = xattribid
    AND attrib.xmachineid = partpassageattrib.xmachineid
    AND partpnr = 41243
    ORDER BY partpnr DESC;
    ALTER TABLE tmp ADD COLUMN attribnr integer DEFAULT 1;

    Wird zwar nicht die Ursache sein, ist aber aus Performance-Sicht auch doof: Du erzeugst die Tabelle und fügst dann eine extra Spalte an, mit Default-Wert. Das erfordert ein Neuschreiben der ganzen Tabelle. Erzeuge doch die Spalte gleich mit, einfach im Select-teil noch , 1 as attribnr dazu.

    Mehr fällt mir erst mal grad nicht ein ...


    Andreas
    --
    ads Das hatte dir akretschmer gestern schon gesagt und siehe da sobald du das machst, funktioniert es ;-)
    https://www.xing.com/profile/Andreas_Kretschmer7

  5. #5
    kalapsi ist offline Neuer Benutzer
    Registriert seit
    14.05.2009
    Beiträge
    16

    Standard

    Hi,
    habe dieses Problem leider immer noch. Bisher war es nicht so tragisch, da wie schon geschrieben das Vacuum die Ausführungsdauer wieder beschleunigt hat. Das VACUUM ANALYZE wird stündlich aufgerufen.
    Dies scheint jetzt allerdings mit der Umstellung auf 8.4.3 nicht mehr immer der Fall zu sein. So steigt die Ausführungszeit von 0ms innerhalb von mehreren Stunden langsam auf über 1 Sekunde an.
    Neu getestet: Mit Indizes hat das ganze nichts zu tun, auch mit der Defragmentierung des shared_buffers (habe ich testhalber von 800MB auf 10MB reduziert)

    ... bin komplett ratlos

  6. #6
    akretschmer ist offline Super-Moderator
    Registriert seit
    29.10.2006
    Ort
    Nähe Dresden
    Beiträge
    5.337

    Standard

    Zitat Zitat von kalapsi Beitrag anzeigen
    Hi,
    habe dieses Problem leider immer noch. Bisher war es nicht so tragisch, da wie schon geschrieben das Vacuum die Ausführungsdauer wieder beschleunigt hat. Das VACUUM ANALYZE wird stündlich aufgerufen.
    Dies scheint jetzt allerdings mit der Umstellung auf 8.4.3 nicht mehr immer der Fall zu sein. So steigt die Ausführungszeit von 0ms innerhalb von mehreren Stunden langsam auf über 1 Sekunde an.
    Neu getestet: Mit Indizes hat das ganze nichts zu tun, auch mit der Defragmentierung des shared_buffers (habe ich testhalber von 800MB auf 10MB reduziert)

    ... bin komplett ratlos
    Ähm, ja.

    Du hast eine Tabelle. In einer Funktion erstellst Du eine temp. Tabelle als Abfrage auf diese Tabelle. Dann machst Du ein select * auf diese Tabelle, das ist teuer. Dann machst Du nochmals ein select *, um die Tabelle auszugeben. Wäre es nicht zielführender, das Resultat der ersten Abfrage gleich auszugeben?

    Das schreiben der temp. Tabelle ist teuer, da muß ja auch was WAL geschrieben werden. Das select * ist teuer, weil es eines seq-scan auslöst. Das nochmalige Lesen für die Ausgabe ist dann auch nochmal teuer, das ORDER BY kann keinen Index nutzen, da keiner da. Du wirfst viel Geld auf - ja, auf was eigentlich?


    Andreas
    --
    ads Das hatte dir akretschmer gestern schon gesagt und siehe da sobald du das machst, funktioniert es ;-)
    https://www.xing.com/profile/Andreas_Kretschmer7

  7. #7
    kalapsi ist offline Neuer Benutzer
    Registriert seit
    14.05.2009
    Beiträge
    16

    Standard

    Bingo,
    dein Tip war Gold wert. Ich habe die temporäre Tabelle entfernt (zugegeben ein Relikt von früher und völlig unnötig) und siehe da, die Ausführungszeiten der Abfrage laufen jetzt seit 12 Stunden stabil und schnell.

    Vielen Dank

  8. #8
    akretschmer ist offline Super-Moderator
    Registriert seit
    29.10.2006
    Ort
    Nähe Dresden
    Beiträge
    5.337

    Standard

    Zitat Zitat von kalapsi Beitrag anzeigen
    Bingo,
    dein Tip war Gold wert.
    Geld reicht, wohin darf ich die Rechnung denn schicken?

    Andreas
    --
    ads Das hatte dir akretschmer gestern schon gesagt und siehe da sobald du das machst, funktioniert es ;-)
    https://www.xing.com/profile/Andreas_Kretschmer7

Stichworte

Berechtigungen

  • Neue Themen erstellen: Nein
  • Themen beantworten: Nein
  • Anhänge hochladen: Nein
  • Beiträge bearbeiten: Nein
  •  

Links: Tobias Bauer - IT- und Funkdienstleistungen   -   Bilderrahmen-kaufen.de - Der Onlineshop für Bilderrahmen & Fotorahmen   -   Funkforum