Ergebnis 1 bis 10 von 10

Thema: Tunen einer Abfrage

  1. #1
    JSpatz ist offline Benutzer
    Registriert seit
    19.05.2006
    Beiträge
    55

    Standard Tunen einer Abfrage

    HI zusammen,

    bei meinem aktuellem Projekt habe ich ein dringendes Problem. Für einen bestimmten Zweck benötigen wir folgende Abfrage:

    Code:
     SELECT      
       LJ_ART, LJ_MENGE, PE_NR, SER_ID
     FROM    
       sysadm.lohnjournal  
     WHERE    
       pr_nr = '4B-159'
       and     
       lj_lfdnr <= 66823245
     ORDER BY  
       LJ_LFDNR desc
    Diese Abfrage bezieht sich auf eine Tabelle mit ca. 3.5 Mio Datensätzen und lj_lfdnr ist der Primärschlüssel und auf pr_nr liegt auch ein Index.
    (der Server läuft unter Debian und PostgreSQL 8.3.1)
    Die Abfrage liefert ca. 2500 Datensätze, wobei eigentlich nur die ersten 50 benötigt werden.
    Jetzt das Problem: Wenn ich die Abfrage das erste Mal ausführe dauert es ca. 10 Sekunden bis das Ergebnis da, alle weiteren Male dann nur noch 500 - 600 ms.
    D.h. bedeutet für mich dass da irgendwo etwas in einem Cache gespeichert wird und dann wesentlich schneller zur Verfügung steht.
    Nur nützt mir das nicht viel, weil sich der Wert von pr_nr, der abgefragt wird immer wieder ändert, und jedesmal wenn ich hier einen neuen Wert einsetze dauert es das erste Mal wieder 10 Sekunden bis die Daten kommen.

    Und das Problem ist die erste Ausführungzeit, die ist einfach zu lange für mein Empfinden, selbst wenn es um 3.5 Mio Datensätze geht sollte das doch eigentlich schneller gehen.

    Der Ausführungsplan sieht für mich eigentlich auch OK aus,
    Code:
    Sort  (cost=1624.37..1625.39 rows=409 width=69)
      Sort Key: lj_lfdnr
      ->  Bitmap Heap Scan on lohnjournal  (cost=19.84..1606.63 rows=409 width=69)
            Recheck Cond: ((pr_nr)::text = '4A-159'::text)
            Filter: (lj_lfdnr <= 66823246)
            ->  Bitmap Index Scan on xlj_prnr  (cost=0.00..19.73 rows=412 width=0)
                  Index Cond: ((pr_nr)::text = '4A-157'::text)
    Ich frage mich nur weshalb die recheck Condition so teuer ist, obwohl er ja durch den ersten Index scan die Datenmenge doch schon erheblich reduziert hat ?

    Da wir nur die ersten 50 Datensätze benötigen habe ich es auch schon mit Limit probiert, hat leider keine Auswirkung und auch ein Umdrehen der Sortierung hat keinen Einfluss.

    Ich bin für jede Anregung dankbar.

    Gruss
    JSpatz

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

    Standard

    Zitat Zitat von JSpatz Beitrag anzeigen
    HI zusammen,

    bei meinem aktuellem Projekt habe ich ein dringendes Problem. Für einen bestimmten Zweck benötigen wir folgende Abfrage:

    Code:
     SELECT      
       LJ_ART, LJ_MENGE, PE_NR, SER_ID
     FROM    
       sysadm.lohnjournal  
     WHERE    
       pr_nr = '4B-159'
       and     
       lj_lfdnr <= 66823245
     ORDER BY  
       LJ_LFDNR desc
    Diese Abfrage bezieht sich auf eine Tabelle mit ca. 3.5 Mio Datensätzen und lj_lfdnr ist der Primärschlüssel und auf pr_nr liegt auch ein Index.
    (der Server läuft unter Debian und PostgreSQL 8.3.1)
    Die Abfrage liefert ca. 2500 Datensätze, wobei eigentlich nur die ersten 50 benötigt werden.
    Jetzt das Problem: Wenn ich die Abfrage das erste Mal ausführe dauert es ca. 10 Sekunden bis das Ergebnis da, alle weiteren Male dann nur noch 500 - 600 ms.
    D.h. bedeutet für mich dass da irgendwo etwas in einem Cache gespeichert wird und dann wesentlich schneller zur Verfügung steht.
    Nur nützt mir das nicht viel, weil sich der Wert von pr_nr, der abgefragt wird immer wieder ändert, und jedesmal wenn ich hier einen neuen Wert einsetze dauert es das erste Mal wieder 10 Sekunden bis die Daten kommen.

    Und das Problem ist die erste Ausführungzeit, die ist einfach zu lange für mein Empfinden, selbst wenn es um 3.5 Mio Datensätze geht sollte das doch eigentlich schneller gehen.

    Der Ausführungsplan sieht für mich eigentlich auch OK aus,
    Code:
    Sort  (cost=1624.37..1625.39 rows=409 width=69)
      Sort Key: lj_lfdnr
      ->  Bitmap Heap Scan on lohnjournal  (cost=19.84..1606.63 rows=409 width=69)
            Recheck Cond: ((pr_nr)::text = '4A-159'::text)
            Filter: (lj_lfdnr <= 66823246)
            ->  Bitmap Index Scan on xlj_prnr  (cost=0.00..19.73 rows=412 width=0)
                  Index Cond: ((pr_nr)::text = '4A-157'::text)
    Ich frage mich nur weshalb die recheck Condition so teuer ist, obwohl er ja durch den ersten Index scan die Datenmenge doch schon erheblich reduziert hat ?

    Da wir nur die ersten 50 Datensätze benötigen habe ich es auch schon mit Limit probiert, hat leider keine Auswirkung und auch ein Umdrehen der Sortierung hat keinen Einfluss.

    Ich bin für jede Anregung dankbar.

    Gruss
    JSpatz
    Versuch es mal mit einem kombinierten Index über pr_nr und lj_lfdnr und zeige uns EXPLAIN ANALYSE.

    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
    ads
    ads ist offline Super-Moderator
    Registriert seit
    08.02.2006
    Ort
    MD
    Beiträge
    4.020

    Standard

    Wieviel Speicher hat die Maschine, auf was ist shared_buffers gesetzt?
    Xandrian: Kurz gesagt: Du hast ein falsches Konzept und willst nicht auf ads hören

    Jetzt NEU: PostgreSQL - Datenbankpraxis für Anwender, Administratoren und Entwickler

    PostgreSQL Service & Support

  4. #4
    JSpatz ist offline Benutzer
    Registriert seit
    19.05.2006
    Beiträge
    55

    Standard

    Hallo,

    Index habe ich angelegt

    CREATE INDEX xlj_lfdnr_Test
    ON lohnjournal
    USING btree
    (pr_nr, lj_lfdnr);

    Und hier das Explain Analyse der Abfrage

    Sort (cost=648.92..649.33 rows=164 width=33) (actual time=7.941..8.817 rows=378 loops=1)
    Sort Key: lj_lfdnr
    Sort Method: quicksort Memory: 43kB
    -> Bitmap Heap Scan on lohnjournal (cost=6.01..642.89 rows=164 width=33) (actual time=0.882..6.589 rows=378 loops=1)
    Recheck Cond: (((pr_nr)::text = '4B-159'::text) AND (lj_lfdnr <= 66823245))
    -> Bitmap Index Scan on xlj_lfdnr_test (cost=0.00..5.97 rows=164 width=0) (actual time=0.205..0.805 rows=378 loops=1)
    Index Cond: (((pr_nr)::text = '4B-159'::text) AND (lj_lfdnr <= 66823245))
    Total runtime: 9.061 ms

    Der Index wird nicht benutzt, auch nach einem Analyze der Tabelle wird der Index nicht benutzt

    JSpatz

  5. #5
    JSpatz ist offline Benutzer
    Registriert seit
    19.05.2006
    Beiträge
    55

    Standard

    Der Server hat 4 GB und
    Shared Buffers = 32MB

    JSpatz

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

    Standard

    Zitat Zitat von JSpatz Beitrag anzeigen
    Der Server hat 4 GB und
    Shared Buffers = 32MB

    JSpatz
    LOL.

    Setze das mal auf ca. 20-25% vom RAM, außerdem effective_cache_size auf vielleicht 3 GByte.

    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
    ads
    ads ist offline Super-Moderator
    Registriert seit
    08.02.2006
    Ort
    MD
    Beiträge
    4.020

    Standard

    Du bist doch eigentlich mittlerweile lange genug im Forum um zu wissen, dass man eine Datenbank erst mal konfigurieren muss. Oder?

    Ich verdonnere mal zu einer Datenbankschulung, dann habe ich wenigstens noch etwas von dem Spaß ;-)
    Xandrian: Kurz gesagt: Du hast ein falsches Konzept und willst nicht auf ads hören

    Jetzt NEU: PostgreSQL - Datenbankpraxis für Anwender, Administratoren und Entwickler

    PostgreSQL Service & Support

  8. #8
    JSpatz ist offline Benutzer
    Registriert seit
    19.05.2006
    Beiträge
    55

    Standard

    HI,

    Asche auf mein Haupt, wenn man natürlich die falsche Datei ausliest, kann nur Mist rauskommen:

    Also hier die richtigen Werte des Servers:

    4 GB RAM und Shared Buffers ist 256 MB

    Werde ich mal testweise auf 512 MB erhöhen (Ich nehme absichtlich nicht gleich 1 GB, weil die Linux Buffers auch schon 1.6 GB groß sind)

    Gruß

    JSPatz

  9. #9
    ads
    ads ist offline Super-Moderator
    Registriert seit
    08.02.2006
    Ort
    MD
    Beiträge
    4.020

    Standard

    Die Buffers sind alles, was das Betriebssystem nicht anderweitig verwendet. Ist kein Platz frei, ist das nahezu 0.
    Ist viel Platz frei, ist da viel in Verwendung.
    Xandrian: Kurz gesagt: Du hast ein falsches Konzept und willst nicht auf ads hören

    Jetzt NEU: PostgreSQL - Datenbankpraxis für Anwender, Administratoren und Entwickler

    PostgreSQL Service & Support

  10. #10
    JSpatz ist offline Benutzer
    Registriert seit
    19.05.2006
    Beiträge
    55

    Standard Neue Werte

    HI

    ich habe Shared Buffers auf 512 MB gesetzt und damit läuft die Abfrage schin wesentlilch besser. Außerdem haben wir eine kompletten Vacuum Full und Redindex gemacht, damit die toten Tupel aus der Tabellen verschwinden.

    Damit sinkt die Ausführungszeit auf ca. 1 Sek. was akzeptabel ist. Ich werde mit den veränderten Werten noch etwas weiter experimentieren.

    Danke soweit für die Hinweise

    JSpatz

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