Auswahl der besten Tabelleneinstellung für TimescaleDB

Aufgabenbeschreibung

Eine gängige Aufgabe für Marktplätze besteht darin, das Volumen einiger Produkte in einem Zeitraum von 1 Tag/7 Tagen/30 Tagen in nahezu Echtzeit zu berechnen. TimescaleDB oder jede andere Zeitreihendatenbank ist für diese Art von Aufgabe perfekt geeignet. Lassen Sie uns TimescaleDB testen, da es in meinem aktuellen Projekt bereits PostgreSQL verwendet wird. Ich werde ein sehr vereinfachtes Modell aus meinem aktuellen Projekt (NFT-Marktplatz) verwenden. Wir haben Tausende von Sammlungen für eine Vielzahl von Blockchains und erfassen jede Listungs-, Gebots- oder Verkaufs-Transaktion in der Tabelle. Die Gesamtzahl der Transaktionen ist enorm: Milliarden von Zeilen. Die Idee, wie wir die Berechnung beschleunigen können, besteht darin, unsere Tabelle in zeitliche Buckets aufzuteilen und die Zahl mit ihnen zu berechnen.

Vorbereitung

Dies ist ein Schema der Tabelle und einiger Indizes:

create table public.sale_changes
(
    time          timestampz not null,
    id            varchar(1024),
    collection_id varchar(512),
    blockchain    varchar(512),
    floor         numeric(78, 10),
    sales         bigint,
    volume        numeric(78, 10)
);
select create_hypertable('sale_changes', 'time', chunk_time_interval => interval '1 day');
create unique index ON sale_changes(blockchain, id, time);
create index ON sale_changes(collection_id, time);
create * from add_dimension('sale_changes', by_hash('blockchain', 16));

Wir haben einige zusätzliche Indizes erstellt:

• CREATE UNIQUE INDEX ON sale_changes(blockchain, id, time); -- dies ist für die Datenkonsistenz erforderlich
• CREATE INDEX ON sale_changes(collection_id, time); -- dies dient der schnelleren Aggregation nach Sammlung

Außerdem haben wir eine zusätzliche Dimension für eine bessere Performance von blockchain-spezifischen Abfragen hinzugefügt. 16 ist die Anzahl der Buckets, die derzeit für Tests ausreichend ist.

Ich habe ein Backup mit Daten für das aktuelle Jahr erstellt und in meine lokale TimescaleDB geladen:

• POLYGON: 4.917.155 Zeilen
• ETHEREUM: 3.032.708 Zeilen
• APTOS: 129.760 Zeilen
• CELO: 13.692 Zeilen
• ZKSYNC: 8.832 Zeilen

Insgesamt haben wir lokal 8.102.147 (~8m).

Lassen Sie uns zwei Abfragen für Leistungstests verwenden:

-- alle Abfragen
select csc.*,
       cs.floor,
       cs.owners from (select collection_id,
       SUM(volume),
       SUM(sales)  AS total_sales,
       CASE
           WHEN first(floor, time) = 0 THEN 0
           ELSE ROUND(((last(floor, time) - first(floor, time)) / first(floor, time)) * 100, 2)
           END         AS floor_change
from sale_changes
where time >= NOW() - INTERVAL '$period'
group by collection_id
order by floor_change DESC
limit ?

-- Spezifische Kettenabfrage:
select csc.*,
       cs.floor,
       cs.owners
from (select collection_id,
             SUM(volume),
             SUM(sales) AS total_sales,
             CASE
                 WHEN first(floor, time) = 0 THEN 0
          ELSE ROUND(((last (floor, time) - first (floor, time)) / first (floor, time)) * 100, 2)
          END AS floor_change
                           from $table
WHERE blockchain = ? and time >= NOW() - INTERVAL '$period'
group by collection_id
order by floor_change DESC
limit ?

Testen

Wir werden die Performance testen, indem wir jede Abfrage 10 Mal ausführen und Metriken zur Ausführungszeit sammeln. Meine lokale Umgebung:

• MacBook M1 mit 32 GB RAM
• TimescaleDB 2.17.2-pg17 in Docker
• erhöhter Arbeitsspeicher für pg: shared_buffers=8GB, work_mem=8GB

Erster Versuch

Dies ist das Ergebnis der ersten Abfrage:

Zeitraum	Limit	Min Zeit (ms)	Max Zeit (ms)	Durchschnittszeit (ms)	Medianzeit (ms)	Std Dev (ms)
1 Stunde	100	71	105	77,3	74,5	9,7
1 Tag	100	73	81	76,0	75,0	2,4
7 Tage	100	161	192	168,1	166,0	8,5
30 Tage	100	515	630	536,3	526,5	32,2

Dies ist das Ergebnis der zweiten Abfrage mit der kleinen Blockchain CELO:

Zeitraum	Limit	Min Zeit (ms)	Max Zeit (ms)	Durchschnittszeit (ms)	Medianzeit (ms)	Std Dev (ms)
1 Stunde	100	13	101	23,7	15,0	25,8
1 Tag	100	13	99	23,4	14,5	25,2
7 Tage	100	14	99	24,5	15,5	24,9
30 Tage	100	16	104	26,1	17,5	26,0

Dies ist das Ergebnis der zweiten Abfrage mit der großen Blockchain ETHEREUM:

Zeitraum	Limit	Min Zeit (ms)	Max Zeit (ms)	Durchschnittszeit (ms)	Medianzeit (ms)	Std Dev (ms)
1 Stunde	100	27	114	37,8	29,5	25,5
1 Tag	100	27	112	37,0	28,0	25,0
7 Tage	100	66	172	79,6	68,5	30,9
30 Tage	100	206	382	251,6	220,0	61,9

Wir sehen, dass Abfragen für ein 30-Sekunden-Intervall nicht so schnell sind wie andere, außer bei der CELO-Blockchain. Dies ist absolut zu erwarten, da die Datenbank mehr Zeilen verarbeiten muss. Lassen Sie uns eine andere Konfiguration versuchen und sehen, ob wir die Performance verbessern können.

Zweiter Versuch

Schalten wir die Komprimierung ein. Wir werden collection_id als Komprimierungssegment verwenden, da es sinnvoll ist, diese Zahlen zusammenzuhalten.

ALTER TABLE sale_changes SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'collection_id'
    );
SELECT compress_chunk(c) from show_chunks('sale_changes') c;

Dies ist das Ergebnis der ersten Abfrage:

Zeitraum	Limit	Min Zeit (ms)	Max Zeit (ms)	Durchschnittszeit (ms)	Medianzeit (ms)	Std Dev (ms)
1 Stunde	100	249	1091	355,9	261,5	247,0
1 Tag	100	237	286	255,4	251,0	15,4
7 Tage	100	247	278	259,4	260,5	8,9
30 Tage	100	349	412	370,2	358,5	21,0

Dies ist das Ergebnis der zweiten Abfrage mit der kleinen Blockchain CELO:

Zeitraum	Limit	Min Zeit (ms)	Max Zeit (ms)	Durchschnittszeit (ms)	Medianzeit (ms)	Std Dev (ms)
1 Stunde	100	19	131	33,1	21,0	32,8
1 Tag	100	18	128	32,9	21,5	32,1
7 Tage	100	21	129	33,3	22,0	31,9
30 Tage	100	25	151	42,1	25,0	36,9

Dies ist das Ergebnis der zweiten Abfrage mit der großen Blockchain ETHEREUM:

Zeitraum	Limit	Min Zeit (ms)	Max Zeit (ms)	Durchschnittszeit (ms)	Medianzeit (ms)	Std Dev (ms)
1 Stunde	100	90	203	115,7	99,0	34,6
1 Tag	100	94	208	110,5	99,5	32,8
7 Tage	100	95	219	111,1	98,5	36,3
30 Tage	100	149	272	170,7	159,5	34,2

Komprimierte Tabelle gab uns schlechtere Ergebnisse... Selbst für Abfragen für kleine Intervalle, doch für 30 Tage im Fall aller Blockchains verbesserte sich das Ergebnis. Ich nehme an, dass das Halten von Zahlen zusammen nach collection_id hier half.

Dritter Versuch

Lassen Sie uns versuchen, das Fragment (Chunk) für unsere Hypertabelle zu vergrößern, da wir viel RAM haben. Laut der offiziellen Empfehlung ist es gut, wenn das Fragment 25% des Speichers einnehmen kann. Derzeit habe ich 24 GB RAM für Docker zugeteilt.

select create_hypertable('sale_changes', 'time', chunk_time_interval => interval '7 day');
CREATE UNIQUE INDEX ON sale_changes(blockchain, id, time);
CREATE INDEX ON sale_changes(collection_id, time);
SELECT * FROM add_dimension('sale_changes', by_hash('blockchain', 16));

Dies ist das Ergebnis der ersten Abfrage:

Zeitraum	Limit	Min Zeit (ms)	Max Zeit (ms)	Durchschnittszeit (ms)	Medianzeit (ms)	Std Dev (ms)
1 Stunde	100	10	61	17,1	11,0	14,8
1 Tag	100	10	18	11,6	11,0	2,2
7 Tage	100	87	119	91,5	88,0	9,3
30 Tage	100	367	514	390,4	373,5	42,9

Dies ist das Ergebnis der zweiten Abfrage mit der kleinen Blockchain CELO:

Zeitraum	Limit	Min Zeit (ms)	Max Zeit (ms)	Durchschnittszeit (ms)	Medianzeit (ms)	Std Dev (ms)
1 Stunde	100	2	14	5,0	4,5	3,2
1 Tag	100	2	12	5,0	4,0	2,7
7 Tage	100	3	15	6,2	5,0	3,4
30 Tage	100	6	15	7,6	7,0	2,6

Dies ist das Ergebnis der zweiten Abfrage mit der großen Blockchain ETHEREUM:

Zeitraum	Limit	Min Zeit (ms)	Max Zeit (ms)	Durchschnittszeit (ms)	Medianzeit (ms)	Std Dev (ms)
1 Stunde	100	3	14	5,9	5,0	2,8
1 Tag	100	5	13	6,0	5,0	2,4
7 Tage	100	32	47	37,6	34,5	5,4
30 Tage	100	129	140	133,3	132,0	3,2

Jetzt haben wir viel bessere Ergebnisse! Mir gefällt nur die Abfrage für alle Ketten über einen Zeitraum von 30 Tagen nicht. Die CELO/ETHEREUM-Abfragen sind wirklich schnell.

Vierter Versuch

Lassen Sie uns versuchen, die Komprimierung für einen 7-Tage-Bucket zu verwenden.

Dies ist das Ergebnis der ersten Abfrage:

Zeitraum	Limit	Min Zeit (ms)	Max Zeit (ms)	Durchschnittszeit (ms)	Medianzeit (ms)	Std Dev (ms)
1 Stunde	100	47	153	60,3	49,5	31,0
1 Tag	100	50	62	54,8	54,0	4,4
7 Tage	100	62	73	67,1	67,0	3,6
30 Tage	100	139	182	150,4	145,0	12,5

Dies ist das Ergebnis der zweiten Abfrage mit der kleinen Blockchain CELO:

Zeitraum	Limit	Min Zeit (ms)	Max Zeit (ms)	Durchschnittszeit (ms)	Medianzeit (ms)	Std Dev (ms)
1 Stunde	100	18	39	22,6	20,0	6,1
1 Tag	100	20	36	25,1	23,0	5,7
7 Tage	100	6	21	8,0	7,0	4,4
30 Tage	100	7	21	9,4	8,0	4,0

Dies ist das Ergebnis der zweiten Abfrage mit der großen Blockchain ETHEREUM:

Zeitraum	Limit	Min Zeit (ms)	Max Zeit (ms)	Durchschnittszeit (ms)	Medianzeit (ms)	Std Dev (ms)
1 Stunde	100	21	34	23,8	23,0	3,5
1 Tag	100	23	59	29,0	24,5	10,5
7 Tage	100	31	58	38,2	36,5	7,4
30 Tage	100	59	74	64,5	63,5	4,2

Schließlich sind unsere Abfragen blitzschnell. Die langsamste ist immer noch die 30-Tage-Abfrage für alle Ketten, aber die Medianzeit beträgt <0,2s, also würde ich sagen, sie ist schnell für die Aggregation unter Millionen von Zeilen.

Diagramme

Fazit

Werfen wir einen Blick auf die Tabellengrößen mit der Abfrage:

select 
    hypertable_name, 
    pg_size_pretty(hypertable_size(format('%I.%I', hypertable_schema, hypertable_name)::regclass))
from timescaledb_information.hypertables;

• 1-Tage-Bucket-Tabelle: 2,4 GB
• 1-Tage-Bucket-Tabelle mit Komprimierung: 2,6 GB
• 7-Tage-Bucket-Tabelle: 2,2 GB
• 7-Tage-Bucket-Tabelle mit Komprimierung: 0,3 GB

Es ist fast ein 10-facher Unterschied zwischen der ersten und der letzten Tabelle, was erklärt, warum die 7-Tage-Bucket-Tabelle mit Komprimierung so schnell ist. Die Konfiguration mit einem 7-Tage-Fragment und Komprimierung ist ein klarer Sieger.