beste Performance Erfahrungen?

Nach seinen Aussagen waren es ‘früher’ 10 Sekunden. Da wäre mal nett zu wissen, was explain analyse von ‘früher’ dazu meint…

Vielleicht einfach nur massig tote Tupel, aber man sollte das mal untersuchen, damit die neue DB nicht irgend wann wieder so lange braucht. Einfach Hardware auf ein Problem zu werfen geht nicht ewig lange gut.

Andreas

\dt sind fast 3000 rows…

kurzform:

List of relations
Schema | Name | Type | Owner
--------±-------------------±------±---------
nagios | downtime | table | postgres
nagios | filesysdata | table | nagios
nagios | filesysdata_200501 | table | nagios

nagios | filesysdata_200912 | table | nagios
nagios | fs_daily | table | nagios
nagios | fs_daily_001 | table | nagios

nagios | fs_daily_1300 | table | nagios
nagios | fs_hourly | table | nagios
nagios | fs_hourly_001 | table | nagios

nagios | fs_hourly_1300 | table| nagios
nagios | fs_weekly | table | nagios
nagios | fs_weekly_001 | table | nagios
nagios | fs_weekly_1300 | table | nagios

die explains sind ähnlich lang
kurzform:

QUERY PLAN

Limit (cost=58050.23…58050.23 rows=1 width=16)
→ Sort (cost=58050.23…58105.66 rows=22172 width=16)
Sort Key: nagios.filesysdata.fstime
→ Result (cost=22.35…57939.37 rows=22172 width=16)
→ Append (cost=22.35…57939.37 rows=22172 width=16)
→ Bitmap Heap Scan on filesysdata (cost=22.35…3486.70 rows=1287 width=16)
Recheck Cond: (fsid = 1)
→ Bitmap Index Scan on filesysdata_fsid_idx (cost=0.00…22.03 rows=1287 width=0)
Index Cond: (fsid = 1)
→ Bitmap Heap Scan on filesysdata_200501 filesysdata (cost=4.31…14.47 rows=8 width=16)
Recheck Cond: (fsid = 1)
→ Bitmap Index Scan on filesysdata_200501_fsid_idx (cost=0.00…4.31 rows=8 width=0)
Index Cond: (fsid = 1)
→ Bitmap Heap Scan on filesysdata_200502 filesysdata (cost=4.31…14.47 rows=8 width=16)
Recheck Cond: (fsid = 1)
→ Bitmap Index Scan on filesysdata_200502_fsid_idx (cost=0.00…4.31 rows=8 width=0)
Index Cond: (fsid = 1)
→ Bitmap Heap Scan on filesysdata_200503 filesysdata (cost=4.31…14.47 rows=8 width=16)
Recheck Cond: (fsid = 1)
→ Bitmap Index Scan on filesysdata_200503_fsid_idx (cost=0.00…4.31 rows=8 width=0)
Index Cond: (fsid = 1)




→ Bitmap Heap Scan on filesysdata_200906 filesysdata (cost=12.96…1608.37 rows=600 width=16)
Recheck Cond: (fsid = 1)
→ Bitmap Index Scan on filesysdata_200906_fsid_idx (cost=0.00…12.81 rows=600 width=0)
Index Cond: (fsid = 1)
→ Bitmap Heap Scan on filesysdata_200907 filesysdata (cost=4.31…14.47 rows=8 width=16)
Recheck Cond: (fsid = 1)
→ Bitmap Index Scan on filesysdata_200907_fsid_idx (cost=0.00…4.31 rows=8 width=0)
Index Cond: (fsid = 1)
→ Bitmap Heap Scan on filesysdata_200908 filesysdata (cost=4.31…14.47 rows=8 width=16)
Recheck Cond: (fsid = 1)
→ Bitmap Index Scan on filesysdata_200908_fsid_idx (cost=0.00…4.31 rows=8 width=0)
Index Cond: (fsid = 1)
→ Bitmap Heap Scan on filesysdata_200909 filesysdata (cost=4.31…14.47 rows=8 width=16)
Recheck Cond: (fsid = 1)
→ Bitmap Index Scan on filesysdata_200909_fsid_idx (cost=0.00…4.31 rows=8 width=0)
Index Cond: (fsid = 1)
→ Bitmap Heap Scan on filesysdata_200910 filesysdata (cost=4.31…14.47 rows=8 width=16)
Recheck Cond: (fsid = 1)
→ Bitmap Index Scan on filesysdata_200910_fsid_idx (cost=0.00…4.31 rows=8 width=0)
Index Cond: (fsid = 1)
→ Bitmap Heap Scan on filesysdata_200911 filesysdata (cost=4.31…14.47 rows=8 width=16)
Recheck Cond: (fsid = 1)
→ Bitmap Index Scan on filesysdata_200911_fsid_idx (cost=0.00…4.31 rows=8 width=0)
Index Cond: (fsid = 1)
→ Bitmap Heap Scan on filesysdata_200912 filesysdata (cost=4.31…14.47 rows=8 width=16)
Recheck Cond: (fsid = 1)
→ Bitmap Index Scan on filesysdata_200912_fsid_idx (cost=0.00…4.31 rows=8 width=0)
Index Cond: (fsid = 1)
(249 rows)



QUERY PLAN

Sort (cost=194.05…194.43 rows=150 width=16)
Sort Key: nagios.fs_hourly.datum
→ Result (cost=0.00…188.63 rows=150 width=16)
→ Append (cost=0.00…188.63 rows=150 width=16)
→ Index Scan using fs_hourly_fsid_idx on fs_hourly (cost=0.00…8.28 rows=1 width=16)
Index Cond: (fsid = 1)
Filter: ((datum <= ‘2009-06-30’::date) AND (datum >= ‘2009-06-23’::date))
→ Bitmap Heap Scan on fs_hourly_001 fs_hourly (cost=5.78…180.35 rows=149 width=16)
Recheck Cond: ((datum <= ‘2009-06-30’::date) AND (datum >= ‘2009-06-23’::date))
Filter: (fsid = 1)
→ Bitmap Index Scan on fs_hourly_001_datum_idx (cost=0.00…5.74 rows=149 width=0)
Index Cond: ((datum <= ‘2009-06-30’::date) AND (datum >= ‘2009-06-23’::date))
(12 rows)

Also ich würde daraus ersehen, dass Indizes und Partitionierungen genutzt werden

den stand von früher kann ich mir demnächst erst anschauen wenn nen altes backup für mich eingespielt wird.
das problem früher war eine abfrage auf eine Tabelle mit 25 mio datensätzen, keine Partitionierung, schlechte Indizes und ein gnadenlos überforderter Server.

Ich werfe da mal 3 Stichworte in den Raum:

  • explain ANALYSE
  • constraint exclusion
  • Code-Tags

Andreas

Habe ich bereits genutzt und danach macht er index scans… also nutzt er definitiv die Indizes

Bitmap Heap Scan on filesysdata_200901 filesysdata  (cost=97.52..5562.35 rows=5831 width=20) (actual time=2.694..76.238 rows=6002 lo
ops=1)
                           Recheck Cond: (fsid = 5)
                           ->  [B]Bitmap Index Scan on filesysdata_200901_fsid_idx[/B]  (cost=0.00..96.06 rows=5831 width=0) (actual time=1.758..1.758 rows=6002 loo
ps=1)

und er durchsucht auch die Partitionen, jedoch immer alle.


Checks habe ich bei jeder Partition drinne, bis eben war es nur in der Config noch auskommentiert.

Fals du damit meinst, dass ich oben Quote anstatt Code genommen habe. Habe mich einfach verklickt :smiley:
Solltest du was anderes meinen, dann keine Ahnung was.

Der Unterschied ist, daß er bei Analyse die Abfrage auch ausführt. Dadurch kann er auch anzeigen, ob z.B. seine Schätzwerte in der Praxis was taugen.

und er durchsucht auch die Partitionen, jedoch immer alle.


Checks habe ich bei jeder Partition drinne, bis eben war es nur in der Config noch auskommentiert.

Ebend. Ist ja nicht wirklich nötig, alle Partitionen zu durchsuchen, oder?


Andreas

Und wie bringe ich ihn dazu nur die korekte Partition zu durchsuchen?
Sind meine Checks vielleicht falsch aufegbaut oder muss noch etwas anderes in der Config aktiviert werden?

\d filesysdata_200901

monitoring=> \d filesysdata_200901
          Table "nagios.filesysdata_200901"
 Column |            Type             |   Modifiers
--------+-----------------------------+---------------
 fsid   | integer                     |
 fsfree | integer                     |
 fsused | integer                     |
 fstime | timestamp without time zone | default now()
Indexes:
    "filesysdata_200901_fsid_idx" btree (fsid)
    "filesysdata_200901_fstime" btree (fstime)
Check constraints:
    "filesysdata_200901_fstime_check" CHECK (date(fstime) >= '2009-01-01'::date)
    "filesysdata_200901_fstime_check1" CHECK (date(fstime) <= '2009-01-31'::date)
    "filesysdata_fsfree_check" CHECK (fsfree >= 0)
    "filesysdata_fsused_check" CHECK (fsused >= 0)
Inherits: filesysdata

Dazu dient ja das constraint_exclusion, da müssen natürlich die Abfragen mit ihren WHERE-Bedingungen zu den Check-Constraints der Partitionierung passen.

Ist jetzt schwer, via Ferndiagnose das zu analysieren, ich kenne ja auch das select nicht oder finde es nicht im Thread…

Andreas

Also genau stimmen die Where und Check Constraints nicht überein, denn meine Abfragen holen z.B. nur Daten der letzten 60 min aus der aktuellen Patition (bzw. sollten es nur aus der aktuellen holen, er durchsucht ja leider immer alle).

Select aus meinem Cron der alle 60min läuft

Select fsfree, fsused, fstime from filesysdata where fsid = 1 and 
                                date(fstime) <= date('2009/07/01 08:00:00') and
                                date(fstime) >= date('2009/07/01 07:00:00') 
                                ORDER BY fstime DESC"

Mit den Daten werden dann paar Berechnungen gemacht und in andere Tabellen gelegt.
Könnte es dann daran liegen? Und würde es Sinn machen meine Monats Partitionen auf Stunden Partitionen zu “verkleinern”? Kann mir nicht vorstellen, dass der Nutzen daraus so groß sein könnte das sich der Aufwand lohnt.

Sollte einklich gehen, ich sehe nix falsches. Vielleicht noch zu früh am Tag? Vielleicht findest ads ja was.

Ach ja, Du hast nun constraint_exclusion eingeschaltet UND den Server neu gestartet bzw. Reload (sollte reichen)?

Andreas

habe gerade festgestellt, dass bei anderen Tabellen die Partitionen genutzt werden

monitoring=> explain analyze select * from fs_hourly where datum >= date('2008/05/01') and datum <= date('2008/05/02') and fsid = 250;
                                                                  QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
 Result  (cost=0.00..79.81 rows=25 width=48) (actual time=0.136..0.273 rows=24 loops=1)
   ->  Append  (cost=0.00..79.81 rows=25 width=48) (actual time=0.133..0.214 rows=24 loops=1)
         ->  Index Scan using fs_hourly_fsid_idx on fs_hourly  (cost=0.00..8.28 rows=1 width=48) (actual time=0.092..0.092 rows=0 loops=1)
               Index Cond: (fsid = 250)
               Filter: ((datum >= '2008-05-01'::date) AND (datum <= '2008-05-02'::date))
         ->  Bitmap Heap Scan on fs_hourly_250 fs_hourly  (cost=4.50..71.54 rows=24 width=48) (actual time=0.037..0.067 rows=24 loops=1)
               Recheck Cond: ((datum >= '2008-05-01'::date) AND (datum <= '2008-05-02'::date))
               Filter: (fsid = 250)
               ->  Bitmap Index Scan on fs_hourly_250_datum_idx  (cost=0.00..4.49 rows=24 width=0) (actual time=0.022..0.022 rows=24 loops=1)
                     Index Cond: ((datum >= '2008-05-01'::date) AND (datum <= '2008-05-02'::date))
 Total runtime: 0.877 ms
(11 rows)


monitoring=> \d fs_hourly_250
            Table "nagios.fs_hourly_250"
  Column   |            Type             | Modifiers
-----------+-----------------------------+-----------
 fsid      | integer                     |
 datum     | timestamp without time zone |
 mean_used | double precision            |
 sum_used  | bigint                      |
 mean_free | double precision            |
 sum_free  | bigint                      |
 n         | integer                     |
Indexes:
    "fs_hourly_250_datum_idx" btree (datum)
    "fs_hourly_250_fsid_idx" btree (fsid)
Check constraints:
    "fs_hourly_250_fsid_check" CHECK (fsid = 250)
    "fs_hourly_n_check" CHECK (n > 0)
Inherits: fs_hourly

das heißt dann wohl, dass meine Partitionen auf zu der Tabelle filesysdata schlecht gewählt sind mit den Zeitbegrenzungen. Vielleicht hätte ich diese auch nach den ID’s erstellen sollen


Edit

Ach ja, Du hast nun constraint_exclusion eingeschaltet UND den Server neu gestartet bzw. Reload (sollte reichen)?

habe es in der Config auf on gestellt und einmal “pg_ctl restart” gemacht, wenn du das meinst.

dein check constraint :

 .... [color="DeepSkyBlue"]date[/color]([B][color="RoyalBlue"]fstime[/color][/B]) <= date('2009/07/01 08:00:00'

deine query:

select ... from fs_hourly where [color="#4169e1"][B]datum[/B][/color] >= date('2008/05/01') and datum <= date('2008/05/02') and fsid = 250;

Was fällt Dir auf?!

Eine Partitionierung wählt man nicht irgendwie - wenn Deine Daten sich zeitlich gut abgrenzen lassen - also du z.B. mit “alten” Daten anders umgehen willst als mit “neuen” - dann machen Partitions nach datum/zeit durchaus sinn.

Blieb also nur die Frage, warum die Partitionierung nicht so funktioniert, wie erwartet - siehe oben.

  1. solltest du schon das selbe feld deines check-constraint selektieren
  2. sollte der ausdruck des constraint möglichst mit dem ausdruck in der select-bedingung uebereinstimmen … wenn dein constraint also date(fstime) >= … heisst, dann solltest du auch nach date(fstime) … eingrenzen. So kann postgres feststellen, dass der constraint ueberhaupt zu verwenden ist.

wenn es dann immernoch nicht funzt:
3. schauen ob du nicht andere indizes hast, die den selben ausdruck wie den des check-constraint enthalten - ich gehe aber mal davon aus, dass die partition-constraints bevorzugt werden - wie es sein sollte (da bin ich mir aber nicht sicher)

Wenn du dir die Statements nochmal genau ansiehst, dann fällt dir auf, dass es grad um zwei verschiedene Tabellen geht.

einmal die filesysdata in der die Spalte fstime heißt und die Partitionen nicht funktionieren. Partitionen eingeteilt nach Zeit.

und die andere Tabelle ist fs_hourly, dort heißt die Spalte datum (beim erstellen nicht aufgepasst und daher anders genannt) und die Partitionen funktionieren. Partitionen eingeteilt nach ID’s

Bei den Abfragen zu den Tabellen werden schon die richtigen Spalten genutzt, sonst würde die Abfrage ja nicht laufen.

    "filesysdata_200901_fstime_check" CHECK (date(fstime) >= '2009-01-01'::date)
    "filesysdata_200901_fstime_check1" CHECK (date(fstime) <= '2009-01-31'::date)



Select fsfree, fsused, fstime from filesysdata where fsid = 1 and 
                                date(fstime) <= date('2009/07/01 08:00:00') and
                                date(fstime) >= date('2009/07/01 07:00:00') 
                                ORDER BY fstime DESC"

möglicher weise meint PG, dass fsid in allen partitionen zu selektieren billiger ist, als die partitions zu beleuchten … was ja durchaus auch richtig sein kann …

was sagt denn das explain dazu wenn du es ohne die fsid = 1 absetzt

ein weiterer schwachpunkt KÖNNTE sein, dass die rechte seite des ausdrucks unterschiedlich ist (in select und constraint) … wobei ich das eher unwahrscheinlich (aber nicht fuer unmoeglich) halte



monitoring=> explain Select fsfree, fsused, fstime from filesysdata where
monitoring->                                 date(fstime) <= date('2009/06/27 08:00:00') and
monitoring->                                 date(fstime) >= date('2009/06/27 07:00:00')
monitoring->                                 ORDER BY fstime DESC;
                                                 QUERY PLAN
-------------------------------------------------------------------------------------------------------------
 Sort  (cost=55108.38..55134.66 rows=10510 width=16)
   Sort Key: nagios.filesysdata.fstime
   ->  Result  (cost=0.00..54406.34 rows=10510 width=16)
         ->  Append  (cost=0.00..54406.34 rows=10510 width=16)
               ->  Seq Scan on filesysdata  (cost=0.00..37704.68 rows=7284 width=16)
                     Filter: ((date(fstime) <= '2009-06-27'::date) AND (date(fstime) >= '2009-06-27'::date))
               ->  Seq Scan on filesysdata_200906 filesysdata  (cost=0.00..16701.66 rows=3226 width=16)
                     Filter: ((date(fstime) <= '2009-06-27'::date) AND (date(fstime) >= '2009-06-27'::date))
(8 rows)



meinst du damit das größer/kleiner geneau entgegensetzt sind bei Select und Constraint? also von der Reihenfolge her andersrum

Nein - dass sich PG womöglich durch <= ‘2009-06-27’::date (ein cast) und <= date(‘2009/06/27 07:00:00’) (eine funktion) verwirren laesst … der typ ist bei beiden sicher date - aber man weiss ja nie …

also ich meine - es koennte hilfreich sein nach

date(feld) <= date(‘…’) zu selectieren, wenn das constraint auch diese form hat date(feld) <= date(‘…’)

habe die checks aber auch mittels Date() erstellt.

check (Date (fstime) >= Date('$k/$strMon/01')),       
check (Date (fstime) <= Date('$k/$strMon/$days')

diesen cast macht der automatisch dann.



Jedoch habe ich was anderes interessantes entdeckt.
Bei den Abfragen mein crons

Select fsfree, fsused, fstime from filesysdata where fsid = 500 and
                                date(fstime) <= date('2009/06/27 08:00:00') and
                                date(fstime) >= date('2009/06/27 07:00:00')
                                ORDER BY fstime DESC;

werden die Partitionen genutzt, egal ob im where noch ein fsid=1 drin steht oder nicht.

jedoch nicht bei der Abfrage

SELECT fsused, fsfree from filesysdata 
                              where fsid = 550 order by fstime desc limit 1;

nicht. Verstehen tue ich das nicht :smiley:

dann wird der constraint / partitions also prinzipiell so genutzt wie man es erwartet - scheint dann doch eine “kostenfrage” zu sein ?!?

jedoch nicht bei der Abfrage

SELECT fsused, fsfree from filesysdata
where fsid = 550 order by fstime desc limit 1;

>
> nicht. Verstehen tue ich das nicht > :smiley: >

[edit]gelöscht

aeh - da ist wieder ein where fsid = ... drin ... natuerlich muss er da in alle partitionen schauen .. denn erst wird alles mit dieser condition gewählt und erst danach sortiert .. trotzdem kann es ncht schaden nach date(fstime) zu sortieren ..

ok dann pack ich da doch einfach mal noch eine zeiteingrenzung mit rein… er soll ja nur den aktuellsten wert raus fischen.


Vielen vielen Dank für die Ganze Hilfen.

der witz ist ja, dass die partition nach datum nur was bringen, wenn du auch in deinen queries entsprechend die partition “wählst” indem du eine bedingung verwendest, die den partition-constraint berücksichtigen …

so ist das ja acuh gewollt … wenn du die bedingung NICHT angibst, dann ist davon auszugehen, dass alle daten gemeint werden …

dein order by mag dabei grenzwertig sein - da weiss ich gerade nicht wie schlau Postgres an der stelle ist - in deinem beispiel spielt aber auch das keine rolle, weil VORHER ja erstmal die fsid selektiert werden soll, die ja in allen Partitionen auftreten kann.