Nasz system zapisuje wiele danych (rodzaj systemu Big Data). Wydajność zapisu jest wystarczająca dla naszych potrzeb, ale wydajność odczytu jest naprawdę zbyt wolna.
Struktura klucza podstawowego (ograniczenie) jest podobna dla wszystkich naszych tabel:
timestamp(Timestamp) ; index(smallint) ; key(integer).Tabela może zawierać miliony wierszy, a nawet miliardy wierszy, a żądanie odczytu zwykle dotyczy określonego okresu (znacznik czasu / indeks) i znacznika. Często zdarza się, że zapytanie zwraca około 200 000 wierszy. Obecnie możemy odczytać około 15 000 linii na sekundę, ale musimy być 10 razy szybsi. Czy to możliwe, a jeśli tak, to w jaki sposób?
Uwaga: PostgreSQL jest dostarczany z naszym oprogramowaniem, więc sprzęt różni się w zależności od klienta.
Jest to maszyna wirtualna używana do testowania. Host maszyny wirtualnej to Windows Server 2008 R2 x64 z 24,0 GB pamięci RAM.
Specyfikacja serwera (VMWare maszyny wirtualnej)
Server 2008 R2 x64
2.00 GB of memory
Intel Xeon W3520 @ 2.67GHz (2 cores)
postgresql.conf optymalizacje
shared_buffers = 512MB (default: 32MB)
effective_cache_size = 1024MB (default: 128MB)
checkpoint_segment = 32 (default: 3)
checkpoint_completion_target = 0.9 (default: 0.5)
default_statistics_target = 1000 (default: 100)
work_mem = 100MB (default: 1MB)
maintainance_work_mem = 256MB (default: 16MB)
Definicja tabeli
CREATE TABLE "AnalogTransition"
(
"KeyTag" integer NOT NULL,
"Timestamp" timestamp with time zone NOT NULL,
"TimestampQuality" smallint,
"TimestampIndex" smallint NOT NULL,
"Value" numeric,
"Quality" boolean,
"QualityFlags" smallint,
"UpdateTimestamp" timestamp without time zone, -- (UTC)
CONSTRAINT "PK_AnalogTransition" PRIMARY KEY ("Timestamp" , "TimestampIndex" , "KeyTag" ),
CONSTRAINT "FK_AnalogTransition_Tag" FOREIGN KEY ("KeyTag")
REFERENCES "Tag" ("Key") MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
OIDS=FALSE,
autovacuum_enabled=true
);
Pytanie
Wykonanie zapytania zajmuje około 30 sekund w pgAdmin3, ale chcielibyśmy uzyskać ten sam wynik poniżej 5 sekund, jeśli to możliwe.
SELECT
"AnalogTransition"."KeyTag",
"AnalogTransition"."Timestamp" AT TIME ZONE 'UTC',
"AnalogTransition"."TimestampQuality",
"AnalogTransition"."TimestampIndex",
"AnalogTransition"."Value",
"AnalogTransition"."Quality",
"AnalogTransition"."QualityFlags",
"AnalogTransition"."UpdateTimestamp"
FROM "AnalogTransition"
WHERE "AnalogTransition"."Timestamp" >= '2013-05-16 00:00:00.000' AND "AnalogTransition"."Timestamp" <= '2013-05-17 00:00:00.00' AND ("AnalogTransition"."KeyTag" = 56 OR "AnalogTransition"."KeyTag" = 57 OR "AnalogTransition"."KeyTag" = 58 OR "AnalogTransition"."KeyTag" = 59 OR "AnalogTransition"."KeyTag" = 60)
ORDER BY "AnalogTransition"."Timestamp" DESC, "AnalogTransition"."TimestampIndex" DESC
LIMIT 500000;
Wyjaśnij 1
"Limit (cost=0.00..125668.31 rows=500000 width=33) (actual time=2.193..3241.319 rows=500000 loops=1)"
" Buffers: shared hit=190147"
" -> Index Scan Backward using "PK_AnalogTransition" on "AnalogTransition" (cost=0.00..389244.53 rows=1548698 width=33) (actual time=2.187..1893.283 rows=500000 loops=1)"
" Index Cond: (("Timestamp" >= '2013-05-16 01:00:00-04'::timestamp with time zone) AND ("Timestamp" <= '2013-05-16 15:00:00-04'::timestamp with time zone))"
" Filter: (("KeyTag" = 56) OR ("KeyTag" = 57) OR ("KeyTag" = 58) OR ("KeyTag" = 59) OR ("KeyTag" = 60))"
" Buffers: shared hit=190147"
"Total runtime: 3863.028 ms"
Wyjaśnij 2
W moim ostatnim teście wybranie moich danych zajęło 7 minut! Patrz poniżej:
"Limit (cost=0.00..313554.08 rows=250001 width=35) (actual time=0.040..410721.033 rows=250001 loops=1)"
" -> Index Scan using "PK_AnalogTransition" on "AnalogTransition" (cost=0.00..971400.46 rows=774511 width=35) (actual time=0.037..410088.960 rows=250001 loops=1)"
" Index Cond: (("Timestamp" >= '2013-05-22 20:00:00-04'::timestamp with time zone) AND ("Timestamp" <= '2013-05-24 20:00:00-04'::timestamp with time zone) AND ("KeyTag" = 16))"
"Total runtime: 411044.175 ms"