Ponieważ jestem młodym programistą i nie bardzo dobrze posługuję się bazami danych (PostgreSQL 9.3), napotkałem pewne problemy z projektem, w którym naprawdę potrzebuję pomocy.
Mój projekt dotyczy gromadzenia danych z urządzeń (do 1000 lub więcej urządzeń), gdzie każde urządzenie wysyła jeden blok danych co sekundę, co daje około 3 milionów wierszy na godzinę.
Obecnie mam jedną dużą tabelę, w której przechowuję przychodzące dane z każdego urządzenia:
CREATE TABLE data_block(
id bigserial
timestamp timestamp
mac bigint
)Ponieważ istnieje kilka rodzajów danych, które blok danych może (lub nie może) zawierać, istnieją inne tabele, które odwołują się do data_blocktabeli.
CREATE TABLE dataA(
data_block_id bigserial
data
CONSTRAINT fkey FOREIGN KEY (data_block_id) REFERENCES data_block(id);
);
CREATE TABLE dataB(...);
CREATE TABLE dataC(...);
CREATE INDEX index_dataA_block_id ON dataA (data_block_id DESC);
...Możliwe jest, że w jednym bloku danych znajduje się 3 x dane A, 1 x dane B, ale brak danych C.
Dane będą przechowywane przez kilka tygodni, więc w tej tabeli mam około 5 miliardów wierszy. W tej chwili mam około 600 milionów wierszy w tabeli, a moje zapytania zajmują naprawdę dużo czasu. Postanowiłem więc utworzyć indeks ponad, timestampa macponieważ moje instrukcje select zawsze sprawdzają w czasie, a często także w czasie + mac.
CREATE INDEX index_ts_mac ON data_block (timestamp DESC, mac);... ale moje zapytania wciąż trwają wieki. Na przykład przesłuchałem dane dla jednego dnia i jednego komputera Mac:
SELECT * FROM data_block
WHERE timestamp>'2014-09-15'
AND timestamp<'2014-09-17'
AND mac=123456789Index Scan using index_ts_mac on data_block (cost=0.57..957307.24 rows=315409 width=32) (actual time=39.849..334534.972 rows=285857 loops=1)
Index Cond: ((timestamp > '2014-09-14 00:00:00'::timestamp without time zone) AND (timestamp < '2014-09-16 00:00:00'::timestamp without time zone) AND (mac = 123456789))
Total runtime: 334642.078 msZrobiłem pełną próżnię przed uruchomieniem zapytania. Czy istnieje elegancki sposób rozwiązania takiego problemu z dużymi tabelami w celu wykonania zapytania <10 sekund?
Czytałem o partycjonowaniu, ale to nie będzie działać z moimi danymi A, dataB, dataC odwołania do data_block_id, prawda? Jeśli to jakoś zadziała, czy powinienem tworzyć partycje w czasie czy na Macu?
Zmieniłem indeks na inny kierunek. Najpierw MAC, potem znacznik czasu i zyskuje dużo wydajności.
CREATE INDEX index_mac_ts ON data_block (mac, timestamp DESC);Nadal jednak zapytania zajmują> 30 sekund. Zwłaszcza, gdy robię LEFT JOINz tabelami danych. Oto EXPLAIN ANALYZEzapytanie z nowym indeksem:
EXPLAIN ANALYZE SELECT * FROM data_block WHERE mac = 123456789 AND timestamp < '2014-10-05 00:00:00' AND timestamp > '2014-10-04 00:00:00'Bitmap Heap Scan on data_block (cost=1514.57..89137.07 rows=58667 width=28) (actual time=2420.842..32353.678 rows=51342 loops=1)
Recheck Cond: ((mac = 123456789) AND (timestamp < '2014-10-05 00:00:00'::timestamp without time zone) AND (timestamp > '2014-10-04 00:00:00'::timestamp without time zone))
-> Bitmap Index Scan on index_mac_ts (cost=0.00..1499.90 rows=58667 width=0) (actual time=2399.291..2399.291 rows=51342 loops=1)
Index Cond: ((mac = 123456789) AND (timestamp < '2014-10-05 00:00:00'::timestamp without time zone) AND (timestamp > '2014-10-04 00:00:00'::timestamp without time zone))
Total runtime: 32360.620 ms Niestety mój sprzęt jest ściśle ograniczony. Korzystam z procesora Intel i3-2100 @ 3,10 GHz, 4 GB pamięci RAM. Moje obecne ustawienia są następujące:
default_statistics_target = 100
maintenance_work_mem = 512MB
constraint_exclusion = on
checkpoint_completion_target = 0.9
effective_cache_size = 4GB
work_mem = 512MB
wal_buffers = 16MB
checkpoint_segments = 32
shared_buffers = 2GB
max_connections = 20
random_page_cost = 2