Archivierungstechniken

[akretschmer]

Bsp.: Wenn wir jetzt die Partition 2018 und Partition 2017-1950 haben. Ein Tag vor einem Meeting greifen 200 Leute auf die Partition 2018 zu. Das System wird langsam, die Produktion die ebenfalls ihre Daten von der Partition 2018 bezieht muss warten und die Produktion steht still.

Das ist alles schwammig. Probleme analysiert man mit EXPLAIN (bzw. EXPLAIN ANALYSE in PostgreSQL). Das liefert Fakten, mit denen man dann operieren kann.

 

[Dukel]

Das selbe Problem hast du mit zwei Datenbanken genau so. Wenn deine Datenbank generell zu langsam ist musst du da ansetzen.

Evtl. brauchst du nicht zwei Datenbanken / Partitionen sondern ein Duplikat der Datenbank für die Produktion.
Z.B. Active Secondaries-Readable Secondary Replicas-Always On Availability

 

[Leobahrt]

@akretschmer: Schwammig? Du verstehst das theoretische Problem glaube ich noch nicht so richtig. Was soll ich da mit einer Explain-Analyse kommen? ... Viele Anfragen -> System wird langsam, Produktion bekommt ihre Daten zu langsam (darf nicht sein). Auch wenn wir Partitionieren, ändert es nichts daran, dass trotzdem viele Anfragen kommen und das System langsam wird -> Produktion bekommt ihre Daten zu langsam (darf nicht sein). Aber trotzdem ein dickes Dankeschön auch an dich, dass du hier mitmachst und versucht mri zu helfen. Es scheint ein ganz einfaches Problem zu sein, jedoch trügt der Schein

Bei der Nutzung von zwei Datenbanken (Db1: Produktivdaten, Db2; Produktivdaten + historische Daten) hätte man die Möglichkeit die Anfragen zu lenken. Die Hauptsache ist, dass die Produktion nicht still steht. Wenn andere "Benutzergruppen" auf Anfragen "warten" müssen, ist es in meinem Fall nicht schlimm#

Danke @Dukel werde mir das anschauen

 

[akretschmer]

Schwammig? Du verstehst das theoretische Problem glaube ich noch nicht so richtig. Was soll ich da mit einer Explain-Analyse kommen? ...

Ja, schwammig. Weil durch Partitioning Abfragen nur auf die Partitionen gelenkt werden, die real betroffen sind. Und EXPLAIN ANALYSE, um zu sehen, ob das funktioniert.

test=# create table demo(id serial, d date, val numeric) partition by range (d);
CREATE TABLE
test=*# create table demo_2016 partition of demo for values from ('2016-01-01') to ('2017-01-01');
CREATE TABLE
test=*# create table demo_2017 partition of demo for values from ('2017-01-01') to ('2018-01-01');
CREATE TABLE
test=*# create table demo_2018 partition of demo for values from ('2018-01-01') to ('2019-01-01');
CREATE TABLE
test=*# insert into demo (d, val) select '2016-01-01'::date + random() * 1000 * '1day'::interval, random() * 1000 from generate_series(1,10000000) s;
INSERT 0 10000000
test=*# analyse demo;
ANALYZE
test=*# explain analyse select * from demo where d = '2018-10-18'::date;
                                                             QUERY PLAN                                                             
------------------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..33158.03 rows=9965 width=19) (actual time=132.371..139.374 rows=0 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Append  (cost=0.00..31161.53 rows=4152 width=19) (actual time=127.399..127.399 rows=0 loops=3)
         ->  Parallel Seq Scan on demo_2018  (cost=0.00..31140.77 rows=4152 width=19) (actual time=127.397..127.397 rows=0 loops=3)
               Filter: (d = '2018-10-18'::date)
               Rows Removed by Filter: 896561
 Planning Time: 1.092 ms
 Execution Time: 139.406 ms
(9 rows)

test=*#

wenn wir jetzt noch einen passenden Index setzen, wird es richtig gut:

test=*# create index idx_demo_d on demo(d);
CREATE INDEX
test=*# explain analyse select * from demo where d = '2018-10-18'::date;
                                                            QUERY PLAN                                                             
-----------------------------------------------------------------------------------------------------------------------------------
 Append  (cost=189.66..15697.17 rows=9965 width=19) (actual time=0.048..0.048 rows=0 loops=1)
   ->  Bitmap Heap Scan on demo_2018  (cost=189.66..15647.34 rows=9965 width=19) (actual time=0.047..0.047 rows=0 loops=1)
         Recheck Cond: (d = '2018-10-18'::date)
         ->  Bitmap Index Scan on demo_2018_d_idx  (cost=0.00..187.17 rows=9965 width=0) (actual time=0.043..0.043 rows=0 loops=1)
               Index Cond: (d = '2018-10-18'::date)
 Planning Time: 0.548 ms
 Execution Time: 0.085 ms
(7 rows)

test=*#

Du siehst: 0,085 Millisekunden, um 10 Millionen Rows zu durchsuchen. Und laut Plan wirken die Partitionen. Das sind jetzt Fakten.

 

[Walter]

. Wenn nun viele Anfragen kommen, wird das System langsam und die Produktion bekommt ihre Daten nicht schnell genug, was nicht sein darf.

Ich kann nur @akretschmer zustimmen. Bevor ihr nicht genau analysiert habt, warum diese Verlangsamung entsteht, stochert ihr im Nebel - im schlimmsten Fall treibt ihr Aufwand und zerlegt alles in zwei Datenbanken, und es ist dann noch immer langsam.

 

[Dukel]

Viele Anfragen -> System wird langsam, Produktion bekommt ihre Daten zu langsam (darf nicht sein).

Die Frage ist WARUM das System langsam wird! Was sind bei dir viele Anfragen? 100? 1.000? 10.000?

 

[Leobahrt]

Okay ich verstehe.
Also das System verlangsamt sich weil die Anfragen an die Datenbank komplex sind. Es ist nicht unüblich, dass eine Query ein 50 Zeiler ist (mehrere joins, Aggregatfunktion ...).

 

[akretschmer]

Es ist nicht unüblich, dass eine Query ein 50 Zeiler ist (mehrere joins, Aggregatfunktion ...).

Bist Du Dir sicher, daß die Abfragen auch effektiv ausgeführt werden? Ich hab schon SQL-Abfragen gesehen, die einige MB groß waren, mit mehr als 'nur' 50 Zeilen und ein paar joins ...

 

[Leobahrt]

Ja, da bin ich mir sicher. Jedoch geht es an dieser Stelle nicht darum ob die Queries effektiv ausgeführt werden, sondern darum, dass das Produktivsystem, wenn viele Anfragen kommen, verlangsamt wird und die Produktion ihre Daten nicht schnell genug bekommt. Die einzige Möglichkeit die mir bis dato eingefallen ist war es die Datenbank in mehrere aufzuteilen und die Anfragen durch Benutzerprofile zu lenken oder anhand der Auslastung der Datenbank umzuleiten (bis jetzt nur ein theoretischer Ansatz).

... Evtl. brauchst du nicht zwei Datenbanken / Partitionen sondern ein Duplikat der Datenbank für die Produktion.
Z.B. Active Secondaries-Readable Secondary Replicas-Always On Availability

Sry, das ich jetzt erst drauf eingehe. Das nutzen eines Duplikats für die Produktion (primary ) und das nutzen der secondary für alle anderen Zugriffe würde das Probleme lösen. Jedoch hätte man in diesem Fall eine komplette Redundanz. Da muss es doch eine elegantere Lösung geben?

 

[akretschmer]

Jedoch geht es an dieser Stelle nicht darum ob die Queries effektiv ausgeführt werden, sondern darum, dass das Produktivsystem, wenn viele Anfragen kommen, verlangsamt wird und die Produktion ihre Daten nicht schnell genug bekommt.

Dann analysiere, warum das passiert. Noch einmal werde ich das aber nicht sagen, da Du es ja eisern ignorierst.

Das mit dem Duplikat: schon mal was von HA gehört? Dazu verwendet man 1 oder mehr Standby-Server, in einer Replikation. Diese kann man durchaus für Read-Only-Abfragen verwenden. Machen faktisch alle unserer Kunden, z.T. auch in Multi-Master (BDR), aber das ist wieder ein anderes Thema ...

 

[Dukel]

Ja, da bin ich mir sicher. Jedoch geht es an dieser Stelle nicht darum ob die Queries effektiv ausgeführt werden, sondern darum, dass das Produktivsystem, wenn viele Anfragen kommen, verlangsamt wird und die Produktion ihre Daten nicht schnell genug bekommt. Die einzige Möglichkeit die mir bis dato eingefallen ist war es die Datenbank in mehrere aufzuteilen und die Anfragen durch Benutzerprofile zu lenken oder anhand der Auslastung der Datenbank umzuleiten (bis jetzt nur ein theoretischer Ansatz).

Es ist nicht normal, dass bei vielen Anfragen das System langsam wird. Hier sind vermutlich die Queries schuld oder das Sizing passt nicht. Setze einen Dienstleister drauf an, der dir sagen kann WARUM das System langsam ist und behebe DIESES Problem!

Ich bin hier raus.