4. Wie Daten aus schnell db lesen?

Wie Daten aus schnell db lesen?


  • Nochmal danke für die Antworten.
    Ich mache mal einen Dump mit den Testdaten, mit denen ich teste. Die db enthält Messdaten, die wie oben beschrieben organisiert sind:

    Tabelle measurements:
  Enthält Daten zu einer Messung (Start, Ende, etc). 
  Zu jeder Messung gehören:
    1+ Messdateien (Tabelle measurement_files)

Tabelle measurement_files:
  Enthält Daten zu einer Messdatei( Typ, Größe, etc).
  Zu jeder Messdatei gehören:
    0 - 12 process data Einträge (Tabelle process_data)
    0+     custom_data Einträge (Tabelle custom_data)
    0 - 64 material_data Einträge (Tabelle material_data)
    0+     measurement_data einträge (Tabelle measurement_results)

Tabelle process_data
  Enthält Informationen über die Umstände der Messung (Temperaturen, etc)

Tabelle custom_data
  Enthält beliebige Zusatzinformationen zur Messdatei

Tabelle material_data
  Enthält Informationen über das gemessene Material sowie einige Metainformationen

Tabelle measurement_results
  Enthält die tabellarischen Messergebnisse der Messdatei (Messtabellenname, keine db Tabelle)
  Zu jeder Messdaten-Eintrag gehören:
  1+     Messergebnis

Tabelle measurement_data
  Enthält die konkreten Messergebnisse der Messdaten (Name, Wert, Einheit)

    @Hustbaer:
    Ich mache das aktuell schon mit joins, aber zweistufig. Im ersten Schritt bestimme ich alle Objekte, die tatsächlich angefordert werden. Es handelt sich dabei um Messungen mit einem Zeitstempel, die für einen bestimmten Zeitraum angefordert werden, aber limitiert sind. Zum Beispiel alle Daten vom Juli 2016, aber davon nur die 5.000 jüngsten. Ich muss also schon sortieren lassen, um mit LIMIT die 5.000 jüngsten zu bestimmen. Ich mache das eigentlich schon genau so, wie du das in 1) Dynamic SQL machst. PostgreSQL hat auch kein Problem damit, wenn in der IN Menge 20.000 IDs stehen. In Pseudocode ausgedrückt sieht das so aus:

    1) map<unsigned int,Measurement> obj_result = query_measurements( some_filter_criteria );

2a) vector<CustomData> cd_result = query_custom_data( list_of_object_ids );
    for( auto cd : cd_result ) obj_result[cd.obj_id].add_custom_data( cd );

2b) vector<ProcessData> pd_result = query_process_data( list_of_object_ids );
    for( auto pd : pd_result ) obj_result[pd.obj_id].add_process_data( cd );

2c) vector<MaterialData> ma_result = query_material_data( list_of_object_ids );
    for( auto ma : ma_result ) obj_result[ma.obj_id].add_material_data( ma );

2d) vector<MeasurementResult> mr_result = query_measurement_results( list_of_object_ids );
    for( auto mr : mr_result ) obj_result[mr.obj_id].add_measurement_result( mr );

    Man könnte jetzt das Bestimmen der Detaildaten in 4 eigene Threads auslagern und gucken, ob das damit schneller wird. Bzw. erst prüfen, wieviele Messungen in Schritt 1 rausfallen und erst ab, sagen wir 2.500, Threads für die Detailabfrage benutzen.

    Ich habe da schon relativ lange dran rumoptimiert, da war ich wohl auf dem richtigen Weg. Trotzdem sind manche Kunden unzufrieden, weil sie teilweise 30 Sekunden auf ein Ergebnis oder länger warten müssen. Dass dabei einige Millionen Tabellenzeilen betrachtet werden müssen und dass das einfach dauert verstehen sie oft nicht. Bei extremen Wartezeiten (gab auch schon mehrere Minuten Wartezeit) konnten wir eigentlich immer deren Netzwerk/Hardware dafür verantwortlich machen.

    @tenim:
    Der JOIN ist halt stumpf über alle Tabellen. Ich brauche tatsächlich alle Spalten aus jeder beteiligten Tabelle.

    EXPLAIN ANALYZE SELECT 
   m.*,
   mf.*,
   pd.*,
   cd.*,
   ma.*,
   mr.*,
   md.*
FROM 
   measurements m,
   measurement_files mf,
   process_data pd,
   custom_data cd,
   material_data ma,
   measurement_results mr,
   measurement_data md
WHERE
   m.measurement_id IN 
   (
      SELECT measurement_id 
      FROM measurements
      WHERE start_time > '2016-07-01 00:00:00' AND start_time < '2016-08-01 00:00:00'
      ORDER BY start_time DESC
      LIMIT 5000
   ) AND         
   m.measurement_id = mf.measurement_id AND
   mf.file_id = pd.file_id AND
   mf.file_id = cd.file_id AND
   mf.file_id = ma.file_id AND
   mf.file_id = mr.file_id AND
   mr.measurement_result_id = md.measurement_result_id

    So, der Dump ist durch. Gezippt sind das immer noch 6GB (roh 30GB), wollt ihr das immer noch zum Spielen haben? :D. Hab keine Ahnung, wo ich sowas hochladen könnte.

    Edit:
    Formatierung verbessert

    0

  • Nachtrag 1): EXPLAIN ANALYZE der Komplettabfrage.

    Nachtrag 2): Abfrage durchlaufen lassen, Abbruch nach 4:30 Minuten: Out of memory for query result.

    0
  • ?

    Warum klopft ihr solche Datenmengen in eine SQL-Datenbank?

    Warum nicht Upscaledb, LevelDB oder sonstige Key/Value-Pair-DB (https://influxdata.com/blog/benchmarking-leveldb-vs-rocksdb-vs-hyperleveldb-vs-lmdb-performance-for-influxdb/) - die für sowas entwickelt wurden
    und nur das Management in der SQL-Datenbank machen
    - fühlt sich an wie Bilder und Videos als Blobs in der DB

    0

  • Gast3 schrieb:

    Warum klopft ihr solche Datenmengen in eine SQL-Datenbank?

    Warum nicht Upscaledb, LevelDB oder sonstige Key/Value-Pair-DB (https://influxdata.com/blog/benchmarking-leveldb-vs-rocksdb-vs-hyperleveldb-vs-lmdb-performance-for-influxdb/) - die für sowas entwickelt wurden
    und nur das Management in der SQL-Datenbank machen
    - fühlt sich an wie Bilder und Videos als Blobs in der DB

    Das ist ne gute Frage... fehlende Erfahrung spielt da sicher eine Rolle.

    0

  • Hm.
    Clustered Indexe bzw. Tabellen partitionieren könnten hier noch helfen.

    0

  • Der measurement_id primary key ist bereits geclustert. Einige Versuche mit Partitionen haben unterschiedliche Ergebnisse gebracht, mal schneller, mal langsamer. Wir haben uns dann wegen des Mehraufwands dagegen entschieden.
    Wenn´s nicht schneller geht, dann geht´s halt nicht schneller. DBMS können auch nicht zaubern. Hab jetzt die Meinung von Leuten gehört, die bei DB mehr Erfahrungen haben als ich, das ist dann auch ok. Wenn mehr Performance nur durch ein anderes DBMS gewonnen werden kann dann können wir uns überlegen, ob wir migrieren wollen.

    0

  • Sieht so aus als würde er das "measurement_id IN (...)" viel zu spät machen. Kann man Postgres da Hints geben welchen Teil es als erstes machen soll?

    0

  • DocShoe schrieb:

    Der measurement_id primary key ist bereits geclustert. Einige Versuche mit Partitionen haben unterschiedliche Ergebnisse gebracht, mal schneller, mal langsamer. Wir haben uns dann wegen des Mehraufwands dagegen entschieden.
    Wenn´s nicht schneller geht, dann geht´s halt nicht schneller. DBMS können auch nicht zaubern.

    Aaaalso.
    Clusterung auf measurement_id ist für diese Abfrage sicher nicht optimal.
    Wenn dann müsste auf start_time bzw. (start_time, xxx_id) geclustert werden. Und zwar alle beteiligten Tabellen.
    Und dann für alle Tabellen die Einschränkung auf das gewünschte start_time Intervall dazugeben.

    Wenn das die einzige Abfrage auf die DB ist die eine nennenswerte Anzahl von Zeilen liest, dann könnte man das auch durchaus so machen. Falls nicht muss man sich natürlich angucken was das dann für Auswirkungen auf die Ausführungzeit der restlichen Abfragen hat.

    Natürlich könnte man auch das Clustering so lassen wie es ist, und zusätzlich für jede Tabelle einen convering Index auf start_time anlegen. Die Abfrage müsste dann ebenfalls halbwegs schnell gehen. Nur wird dadurch INSERT/UPDATE/DELETE deutlich langsamer - vermutlich wird es dann mehr als doppelt so lange dauern.

    Ansonsten, was du z.B. noch probieren kannst ist Temp-Tables zu verwenden, um damit die Ausführungsreihenfolge zu erzwingen.
    z.B.:

    SELECT *
INTO #m
FROM measurements
WHERE start_time > '2016-07-01 00:00:00' AND start_time < '2016-08-01 00:00:00'
ORDER BY start_time DESC
LIMIT 5000

SELECT * -- hier wird * nicht wirklich gehen, du wirst alle Spalten aufführen müssen
         -- (in * gäbe es ja sonst measurement_id doppelt => uneindeutiger Spaltenname => Fehler)
INTO #mf
FROM #m m
INNER JOIN measurement_files mf ON mf.measurement_id = m.measurement_id

SELECT
   mf.*,
   pd.*,
   cd.*,
   ma.*,
   mr.*,
   md.*
FROM
   #mf mf
   INNER JOIN process_data pd ON pd.file_id = mf.file_id
   INNER JOIN custom_data cd ON cd.cd.file_id = mf.file_id
   INNER JOIN material_data ma ON ma.file_id = mf.file_id
   INNER JOIN measurement_results mr ON mr.file_id = mf.file_id
   INNER JOIN measurement_data md ON md.measurement_result_id = mr.measurement_result_id

DROP TABLE #m
DROP TABLE #mf

    Wäre interessant was der Postgres daraus macht.
    Ein potentielles Problem bleibt dabei natürlich noch bestehen, nämlich der letzte Join von measurement_results nach measurement_data .
    Hier könnte man versuchen nochmal das selbe zu machen wie bei measurement_files, also den Join auf measurement_results damit er schon im Temp-Table materialisiert ist, wenn der Query-Optimizer anfängt das letzte SELECT zu optimieren.

    Aber andere Frage: Wie viele Datensätze fallen denn da am Ende raus?
    Bei wirklich grossen Mengen hilft nämlich i.A. einfach nur Clustern bzw. Partitionieren.
    Wenn die Lokalität der Rows im File gut ist und sie in der richtigen Reihenfolge angefordert werden kann auch ein Nested Loops Join akzeptable Resultate liefern. Merge und Hash sind halt bei grossen Tabellen Killer, weil sie - wenns keine weiteren Einschränkungen gibt - nen kompletten Table-Scan machen müssen.

    0

  • Wobei...
    Wenn ich mir nochmal angucke was du da schreibst...

    DocShoe schrieb:

    `

    obj

    +-- pd (6-10 Elemente)

    +-- ma (8-16 Elemente)

    +-- cd (2+ Elemente)

    +-- md (0-5 Elemente)

      +-- mr (1-16 Elemente)

    `

    ... dann denke ich mir: Da wird wohl ein JOIN über alles die falsche Wahl sein.
    Weil sich ja die Zeilenanzahl immer multipliziert.
    Du joinst ja immer auf file_id...
    Wenn du im 1. Join für eine file_id 6 Zeilen bekommst, dann joinst du diese file_id im 2. Join schon 6-fach. Da kommen dann nochmal JE 8 Zeilen dazu, bist du auf 48-fach. Usw.

    Weisst du natürlich alles, ich schreib's mehr für mich als für dich.

    Schlussfolgerung: Einzelne SELECTs auf die verschiedenen Tabellen machen da sicher mehr Sinn als ein grosser JOIN über alles.

    Also vielleicht doch statt IN mal mit Temp-Table versuchen. Und der Tip mit Clusterung auf start_time bzw. Covering-Index auf start_time ist natürlich nach wie vor gut (hoffe ich :)). Nur nicht vergessen die WHERE start_time BETWEEN ... Bedingung dann bei jedem SELECT mit dazuzugeben.

    0
  • ?

    DocShoe schrieb:

    Nachtrag 1): EXPLAIN ANALYZE der Komplettabfrage.

    Nachtrag 2): Abfrage durchlaufen lassen, Abbruch nach 4:30 Minuten: Out of memory for query result.

    Du liest aber schon über cursor in dein Programm?

    0

  • So, kurzes Feedback. Ich optimiere noch fleißig und muss dabei feststellen, dass der Query Planer von Postgres nicht immer die beste Strategie wählt. Die schnellste Lösung für PostgreSQL 9.1.13 ist im Moment, alle Abfrage separat in temporäre Tabellen zu schreiben und anschließend auszulesen.

    SELECT m.* FROM measurements m INTO TEMP TABLE tmp_measurements WHERE...
SELECT f.* FROM measurement_files f INTO TEMP TABLE tmp_files WHERE f.measurement_id = f.measurement_id
...

    Damit wird das alles schon deutlich schneller. Was mich nur etwas wundert ist, dass das Erzeugen und anschließendes SELECT auf zwei temp. Tabellen 2-3x so schnell ist wie die Abfrage über die "echten" Tabellen. Hängt vermutlich iwie mit Plattenzugriffen oder Caching zusammen.

    -- "echte" Abfrage dauert ca. ~1:30 mit 1.7M Treffern
SELECT 
  m.val1,
  m.val2,
  r.val1,
  r.val2
FROM
  measurement_data m,
  measurement_results r
WHERE
  m.result_id = r.result_id AND
  EXISTS( SELECT 1 FROM tmp_files WHERE m.file_id = tmp_files.file_id );

-- Erzeugen temp. Tabellen und anschließende Abfrage dauert ~0:35 mit 1.7M Treffern
SELECT 
  m.*
INTO TEMP TABLE 
   tmp_data 
FROM
   measurement_data m
WHERE EXISTS( SELECT 1 FROM tmp_files WHERE m.file_id = tmp_files.file_id );

SELECT
   r.*
INTO TEMP TABLE 
   tmp_result
FROM
   measurement_results r
WHERE EXISTS( SELECT 1 FROM tmp_data WHERE r.result_id = tmp_data.result_id );

SELECT 
  m.val1,
  m.val2,
  r.val1,
  r.val2
FROM
  tmp_data d,
  tmp_result r
WHERE
  m.result_id = r.result_id;
    0

  • Der Grund ist das WHERE beim Erzeugen der Temp-Tables.
    Dadurch muss der JOIN dann nicht mehr die kompletten Tabellen joinen, sondern nur mehr das was nach dem WHERE übrig bleibt.

    Bei der "normalen" Abfrage scheint Postgres das falschrum zu machen, also erstmal alles zu JOINen und dann erst zu filtern.

    0

  • So, neues Update:
    Dank eurer Hilfe habe ich die Performance enorm steigern können, die Laufzeit einer Abfrage ist von 240 Sekunden auf 35 Sekunden gefallen. 7 Sekunden davon entfallen auf eine 3rd Party Komponente, die nach einer Abfrage wohl noch Aufräumarbeiten durchführen muss und dazu ewig braucht. Hab den Hersteller mal angeschrieben und ein Ticket aufgemacht, mal sehen, was dabei rauskommt.
    Parallelisieren lässt sich das Ganze nicht, da die temporären Tabellen sessionbezogen sind und nur für die Erzeuger-Session sichtbar sind. Die db Zugriffskomponenten lassen den Zugriff nur in dem Thread zu, in dem sie erzeugt worden sind, also müsste ich für jeden Thread eine neue db Session erzeugen, die dann aber die temporären Tabellen nicht sehen. Und persistente Tabellen zu erzeugen zieht wieder andere Problem nach sich, die 3 Sekunden Laufzeitgewinn IMHO nicht rechtfertigen.

    Edit:
    Die o.g. Abfrage stellt das Maximum dessen dar, was praxistauglich ist. IdR laufen die Abfragen wesentlich kürzer und sind nach ca. 12-15 Sekunden zurück.

    0

  • Cool!
    Würde mich interessieren wie die aktuelle Lösung jetzt aussieht.

    0

  • Die Daten werden nach wie vor zweistufig gelesen.

    Stufe 1)
    Bestimmen der Messungen-Treffermenge.
    Die alte Lösung brauchte noch ein DISTINCT in der Abfrage, was irre zeit gefressen hat. Die neue Variante kommt ohne aus und hat dadurch schon im Worst Case 80 Sekunden gewonnen). Sie trägt alle Treffer in eine temporäre Tabelle ein.

    DROP TABLE IF EXISTS tmp_measurements;
DROP TABLE IF EXISTS tmp_files;
DROP TABLE IF EXISTS tmp_tables;
DROP TABLE IF EXISTS tmp_data;
DROP TABLE IF EXISTS tmp_custom;
DROP TABLE IF EXISTS tmp_material;
DROP TABLE IF EXISTS tmp_process;

CREATE TEMPORARY TABLE tmp_measurements (LIKE measurements) WITH (FILLFACTOR = 100);
CREATE TEMPORARY TABLE tmp_files        (LIKE measurement_files) WITH (FILLFACTOR = 100);
CREATE TEMPORARY TABLE tmp_tables
(
   measurement_id        INTEGER,
   file_id               INTEGER,
   measurement_result_id INTEGER,
   measurement_name      TEXT
) WITH (FILLFACTOR = 100);

CREATE TEMPORARY TABLE tmp_data
(
   measurement_id        INTEGER,
   file_id               INTEGER,
   measurement_result_id INTEGER,
   measurement_name      TEXT,
   measurement_data_id   INTEGER,
   item_name             TEXT,
   value                 REAL,
   unit                  TEXT
) WITH (FILLFACTOR = 100);

CREATE TEMPORARY TABLE tmp_custom
(
   measurement_id      INTEGER,
   file_id             INTEGER,
   custom_data_id      INTEGER,
   user_id             INTEGER,
   timestamp           TIMESTAMP(0) WITHOUT TIME ZONE,
   key                 TEXT,
   value               TEXT
) WITH (FILLFACTOR = 100);

CREATE TEMPORARY TABLE tmp_material
(
   measurement_id      INTEGER,
   file_id             INTEGER,
   material_data_id    INTEGER,
   material_id         INTEGER,
   material            TEXT
) WITH (FILLFACTOR = 100);

CREATE TEMPORARY TABLE tmp_process
(
   measurement_id      INTEGER,
   file_id             INTEGER,
   process_data_id     INTEGER,
   item_id             INTEGER,
   item_value          REAL
) WITH (FILLFACTOR = 100);

INSERT INTO tmp_measurements
SELECT m.* FROM measurements m
WHERE
(-- BEGIN SYSTEM FILTERS
   ( -- BEGIN SYSTEM FILTER GROUP Vordefinierter Filter (Zeitraum)
      EXISTS( SELECT 1 FROM measurements WHERE
         measurements.measurement_id = m.measurement_id AND
         measurements.start_time > '2016-08-22 00:00:00' AND
         measurements.start_time < '2016-08-23 00:00:00'
      )
   ) -- END SYSTEM FILTER GROUP Vordefinierter Filter (Zeitraum)
   AND
   ( -- BEGIN SYSTEM FILTER GROUP Vordefinierter Filter (Mount Point)
      EXISTS( SELECT 1 FROM measurement_files WHERE
         measurement_files.measurement_id = m.measurement_id AND
         measurement_files.mount_point_id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
      )
   ) -- END SYSTEM FILTER GROUP Vordefinierter Filter (Mount Point)
)-- END SYSTEM FILTERS
ORDER BY
   m.start_time DESC
LIMIT 2001;

SELECT
   tm.measurement_id,
   tm.measurement_name,
   tm.start_time,
   tm.stop_time,
   tm.comment,
   tm.measurement_guid
FROM
   tmp_measurements tm

    Stufe 2)
    Bestimmung der Messdateien und aller Detaildaten. In Schritt 1) wurden bereits alle notwendigen temporären Tabellen erzeugt, in Schritt 2) werden sie befüllt. Die temp. Tabellen enthalten für jede Zeile zusätzlich die ID der Zeile aus der "Parent" Tabelle, damit die finalen Datentabellen keine Daten mehr aus anderen Tabellen benötigen. Was mich dabei nur wirklich wundert ist, dass das Erzeugen, Füllen und Auslesen der temp. Tabellen schneller geht als das direkte Auslesen der Daten per WHERE Klausel aus zwei Tabellen, denn es wird prinzipiell das Gleiche gemacht.

    INSERT INTO tmp_files
SELECT mf.* FROM measurement_files mf
WHERE
(
   EXISTS( SELECT 1 FROM tmp_measurements
				WHERE
              mf.measurement_id = tmp_measurements.measurement_id
   )
);

INSERT INTO tmp_tables
SELECT
  tf.measurement_id,
  tf.file_id,
  mr.measurement_result_id,
  mr.measurement_name
FROM
   tmp_files tf,
   measurement_results mr
WHERE
   tf.file_id = mr.file_id;

INSERT INTO tmp_data
SELECT
   tt.measurement_id,
   tt.file_id,
   tt.measurement_result_id,
   tt.measurement_name,
   md.measurement_data_id,
   md.item_name,
   md.value,
   md.unit
FROM
   tmp_tables tt,
   measurement_data md
WHERE
   tt.measurement_result_id = md.measurement_result_id;

INSERT INTO tmp_custom
SELECT
   tf.measurement_id,
   tf.file_id,
   cd.custom_data_id,
   cd.user_id,
   cd.timestamp,
   cd.key,
   cd.value
FROM
   tmp_files tf,
   custom_data cd
WHERE
   tf.file_id = cd.file_id;

INSERT INTO tmp_process
SELECT
   tf.measurement_id,
   tf.file_id,
   pd.process_data_id,
   pd.item_id,
   pd.item_value
FROM
   tmp_files tf,
   process_data pd
WHERE
   tf.file_id = pd.file_id;

INSERT INTO tmp_material
SELECT
   tf.measurement_id,
   tf.file_id,
   ma.material_data_id,
   ma.material_id,
   ma.material
FROM
   tmp_files tf,
   material_data ma
WHERE
   tf.file_id = ma.file_id;

SELECT
   tf.file_id,
   tf.measurement_id,
   tf.mount_point_id,
   tf.file_type,
   tf.file_name,
   tf.comment,
   tf.file_status,
   tf.file_version,
   tf.archive_name,
   tf.file_size,
   tf.file_guid,
   tf.file_flags
FROM
   tmp_files tf
    0

  • Danke.

    DocShoe schrieb:

    Was mich dabei nur wirklich wundert ist, dass das Erzeugen, Füllen und Auslesen der temp. Tabellen schneller geht als das direkte Auslesen der Daten per WHERE Klausel aus zwei Tabellen, denn es wird prinzipiell das Gleiche gemacht.

    Nur im Prinzip, oder ist das SELECT wirklich identisch?
    Bei identischen SELECTs würde mich das nämlich etwas wundern.

    0
Anmelden zum Antworten