Kolumnowa baza danych – kiedy i dla kogo?

blog_wycinka_grafik1

Pracując przy rozwoju hurtowni opartej o tradycyjną bazę wierszową (nie zdradzając tajemnicy, powiem, że był to Oracle), wielokrotnie zetknąłem się z artykułami o bazach kolumnowych. Przeważnie nie przywiązywałem do nich większej wagi, ponieważ zaszufladkowałem bazy kolumnowe jako rozwiązanie dla problemów typu BigData, którego nie warto stosować, jeśli nie masz co najmniej 100TB danych w swojej hurtowni. W końcu jednak dotarło do mnie, że tkwię w błędzie. Bazy kolumnowe w żaden sposób nie są związane z bazami danych nierelacyjnych (noSQL / HADOOP).

Po tym niezbyt odkrywczym odkryciu, że baza kolumnowa jak najbardziej jest bazą relacyjną i wszystkie mechanizmy w skrócie opisywane jako SQL mają do niej zastosowanie, postanowiłem wypróbować jedną z technologii dostępnych na rynku. Ponieważ w testach sprawdziła się większość obietnic zawartych w materiałach marketingowych, postanowiłem podzielić się uwagami o głównych korzyściach, jakie daje to rozwiązanie. Proszę zatem potraktować mój tekst jako subiektywny opis zalet baz kolumnowych i implementacji dostarczanej przez HP pod nazwą HP Vertica.

Główne założenie

Bazy kolumnowe stosuj przede wszystkim dla zastosowań analitycznych, czyli dla funkcji selekcji, agregacji, filtrowania i sumowania danych. W przypadku operacji typu UPDATE wydajność może okazać się gorsza niż tradycyjnej bazy wierszowej. Dlatego główne zastosowania tych baz to Hurtownie Danych, a nie systemy transakcyjne (ERP, CRM…, chyba że mamy na myśli CRM analityczny J).

Główna obietnica mówi o 10-100 razy szybszym tempie przetwarzania danych na porównywalnym sprzęcie (liczba CPU/RAM/Dyski). Jak widać, jest to bardzo obiecująca i wręcz prowokująca zapowiedź, a właśnie taką znalazłem na stronach jednego z dostawców. Skoro Vertica, to wiadomo, że tym dostawcą aktualnie jest HP.

Środowisko testowe

Vertica i Oracle uruchomione są jako maszyny wirtualne na serwerze AIX’owym. Zapytania Oracle są uruchamiane bez opcji paralel, więc wykorzystują zawsze jeden procesor. Oczywiście nie stosujemy żadnego dedykowanego sprzętu zoptymalizowanego dla baz danych, ponieważ chodzi tylko o test oprogramowania.

Dane testowe

Sprawdziłem działanie bazy na bazie modelu danych sprzedaży detalicznej z następującymi kartotekami: faktury, pozycje faktur, klienci, produkty. Oto statystyka:

Orderdetails (pozycje faktur) 117mln wierszy
Orders (faktury) 46mln wierszy
Customers (klienci) 5mln wierszy
Products (produkty) 80

 

Pierwszy najprostszy test

Test 1 – najprostszy count dla zliczenia liczby wierszy tabeli

Vertica Oracle
Select count(*) from orders 0.1s 22s

wynik

220 razy szybciej

 

Wynik okazał się nawet lepszy niż obiecane 10-100 razy szybciej. Ale tutaj jest pewne oszustwo testu, bo HP Vertica zawsze wie, ile ma rekordów w kolumnie, a Oracle nigdy tego nie wie i zawsze musi zrobić „scan” tabeli, więc wynik musi być różny.

Test 2 – grupowanie i wyliczenie średniej

Vertica Oracle

select count(distinct timeid), avg(LineTotal) from orderdetails;
1.7s 2.27min

wynik

86 razy szybciej

 

W tym przypadku obydwa serwery musiały wykonać “scan” tabeli. Różnica polega na tym, że Oracle zrobił to dla wszystkich wierszy i wszystkich kolumn, a HP Vertica dla dwóch kolumn i do tego poddanych kompresji. HP Vertica wykonując zapytanie działa nadal na kompresowanych danych.

Test 3 – wybór kilku wierszy z dużej tabeli wg kryterium dla tabeli połączonej (lista pozycji jednej faktury)

Vertica Oracle

select od.LineTotal, od.UnitPrice, od.Description, od.ProductId, o.SalesPerson
from orderdetails od
inner join orders o on od.orderId=o.orderID and od.timeid=o.timeid
where od.orderId=171610 and od.timeid=21
0.2s 2.2s

wynik

11 razy szybciej

 

Tym razem wynik otarł się o granicę obiecanej wydajności minimum 10 razy szybciej. Dane w bazie Oracle nie wykorzystywały indeksów, więc przy lepszej optymalizacji bazy Oracle prawdopodobnie dostalibyśmy wynik na pewno lepszy, a może i zbliżony do wyniku z bazy HP Vertica. Jednak baza HP Vertica też nie jest optymalizowana w tym przypadku. Jedna z podstawowych metod optymalizacji bazy HP Vertica to dodanie tzw. projekcji. Projekcje są to duplikaty kolumn z innymi regułami sortowania lub kompresji niż oryginalna tabela. Dobry opis znaczenia projekcji można znaleźć tutaj .

 

Test 4 wybór ten sam co w teście 3, ale bez warunków WHERE

Vertica Oracle

select od.LineTotal, od.UnitPrice, od.Description, od.ProductId, o.SalesPerson
from orderdetails od
inner join orders o on od.orderId=o.orderID and od.timeid=o.timeid
limit 100 (where rownum<=100)
3,3s 1.24s

wynik

2,2 razy WOLNIEJ!!!

 

Czyżby obietnica marketingowa była nie do końca prawdziwa? Ten test celowo pokazuję po teście numer 3, ponieważ widać tu prostą zależność i mechanizm działania bazy kolumnowej, który różni się istotnie od podejścia baz wierszowych. W przypadku HP Vertica baza najpierw ładuje kolumny do pamięci, a potem wykonuje operacje join i kalkulacyjne. Jeśli nie podamy warunków „where”, ładuje się komplet danych, co jest jawnym marnotrawstwem, jeśli chcemy zobaczyć tylko pierwsze dziesięć wierszy. Baza wierszowa zacznie po prostu czytać kolejne wiersze, wykonywać połączenie i pokaże wynik bez operacji „scan”. Im większa tabela źródłowa, tym większa będzie przewaga podejścia bazy wierszowej w takim przypadku.

JEDNAK kiedy zadamy sobie pytanie o to, jaki sens mają zapytania zwracające przypadkowe pierwsze rekordy z dużego zbioru wynikowego, okaże się, że są one potrzebne tylko w przypadkach testowych realizowanych ad-hoc, a nie rzeczywistych analizach danych. Do przypadków testowych wystarczy zastosować warunki WHERE i już będziemy mieli wyniki takie jak w teście numer 3 lub lepsze. Wyniki bazy kolumnowej będą tym lepsze w porównaniu do wierszowej, im większy zbiór danych jest poddany analizie oraz im mniej wykorzystujemy kolumn. Tabela może mieć kolumn 200, a w zapytaniu korzystamy z pięciu i to one decydują o czasie realizacji zapytania. Baza kolumnowa wszystko zaczyna od KOLUMNY. Nawet jeśli przyjrzymy się, jak w słownikach systemowych zapisywane są rozmiary obiektów, to widać, że są one zapisywane na poziomie kolumny. Każda z nich ma swój rozmiar, liczbę rekordów, statystykę itd.

Test 5 podzapytania

Vertica Oracle

select sum(od.LineTotal) as s_total, sum(od.LineTotal)/max(st.total) as udzial_p, o.SalesPerson
from orderdetails od
inner join orders o on od.orderId=o.orderID and od.timeid=o.timeid
inner join (select sum(LineTotal) as total
from orderdetails where timeid in (10,11,12, 13)) st on 1=1
where o.timeid in (10,11,12,13)
group by o.SalesPerson
8.6s 2.09m

wynik

15 razy szybciej

 

W przypadku powyższego testu muszę przyznać się do drobnej manipulacji. Otóż zacząłem od sumy dla dwóch miesięcy i baza kolumnowa okazała się być tylko osiem razy szybsza niż wierszowa. Zwiększyłem więc zakres danych do czterech miesięcy i już wtedy otrzymaliśmy wynik z przedziału 10-100. Potwierdza to poprzednie spostrzeżenie, że im większy wolumen danych, tym większa będzie przewaga baz kolumnowych w przypadku zapytań analitycznych, agregujących i filtrujących dane.

Test 6 podstawowe funkcje analityczne

Vertica Oracle

select * from
(
select sum(od.LineTotal), o.SalesPerson, od.ProductId, o.CustomerID,
sum(sum(od.LineTotal)) over (partition by o.SalesPerson) as s_total,
sum(sum(od.LineTotal)) over (partition by od.ProductId) as p_total,
sum(sum(od.LineTotal)) over (partition by o.CustomerId) as c_total
from orderdetails od
inner join orders o on od.orderId=o.orderID and od.timeid=o.timeid
where od.timeid in (10,11,12)
group by o.SalesPerson, od.ProductId, o.CustomerId
) s
where p_total>0 and CustomerId = 2006050;
 
19s 4.59min

wynik

15 razy szybciej

 

Ponownie otrzymaliśmy wynik zgodnie z obietnicą. Wspomnę tylko, że tym razem tabele bazy Oracle miały dodane indeksy, które wspomagały operacje join i filtrowania, a baza kolumnowa HP Vertica nie ma indeksów z definicji. Pewnym przybliżeniem do indeksów bazy wierszowej są w HP Vertica projekcje, o których pisałem wcześniej. Jednak na potrzeby tego artykułu baza została wypełniona bez optymalizacji tego rodzaju.

 

Test 7 Przykład optymalizacji

Vertica Oracle

select * from
(
select sum(od.LineTotal), o.SalesPerson, od.ProductId, o.CustomerID,
sum(sum(od.LineTotal)) over (partition by o.SalesPerson) as s_total,
sum(sum(od.LineTotal)) over (partition by od.ProductId) as p_total,
sum(sum(od.LineTotal)) over (partition by o.CustomerId) as c_total
from orderdetails od
inner join orders o on od.orderId=o.orderID and od.timeid=o.timeid
where od.timeid in (10,11,12)
and CustomerId = 2006050
group by o.SalesPerson, od.ProductId, o.CustomerId
) s
where p_total>0
0.4s 2.37min

wynik

392 razy szybciej

 

Jedna prosta zmiana zapytania skróciła wynik bazy wierszowej prawie o połowę. W przypadku bazy kolumnowej mamy przyspieszenie wielokrotne (ponad 40 razy szybciej). Wyjaśnienie znowu tkwi w specyfice działania bazy kolumnowej. W przypadku testu nr 6 baza musiała odczytać wszystkie dane wymaganych kolumn, potem wykonać funkcje analityczne i na końcu zastosować filtr. Obydwie bazy działały zatem na podobnym zbiorze danych.

W przypadku testu nr 7 Vertica zdobyła dużą przewagę dzięki znacznie lepszej wydajności filtrowania i operacji join w pamięci. Wszystkie operacje JOIN bazy HP Vertica są wykonywane w pamięci, więc jednym krokiem z załadowane zostały potrzebne obszary danych (nieduże bo skompresowane) i następnie procesor+pamięć zapewniły szybki wynik.
Ogólna zasada jest następująca: załaduj do pamięci obszary tabel (lub skorzystaj z gotowych, jeśli już są), a następnie wykonaj wszystkie operacje zadanie przez optymalizator zapytania (sortowania, łączenia, grupowania) już na zbiorach w pamięci.
7 jest liczbą szczęśliwą, dlatego na tym zakończę sprawdzanie, czy Vertica potrafi pracować 10-100 razy szybciej od bazy wierszowej. Na siedem testów tylko w jednym przypadku Vertica nie spełniła oczekiwań i zadziałała zdecydowanie wolniej. Jednak był to według mnie przypadek, który nie zalicza się do działań z zakresu analizy danych. Uważam, że system się obronił i tym samym potwierdził zasadność implementacji baz kolumnowych dla zastosowań ANALITYCZNYCH.

Mam nadzieję, że na przykładzie opisanych testów udało mi się wyjaśnić, dlaczego baza kolumnowa będzie i powinna działać szybciej niż baza wierszowa w konkretnych zastosowaniach. Chodzi oczywiście o funkcje selekcji, filtrowania, grupowania i agregacji danych. Jak duży jest to obszar, wiedzą wszyscy, którzy korzystają z zasobów hurtowni danych oraz narzędzi Business Intelligence. Właśnie dla nich jest to bardzo ciekawa nowa propozycja technologiczna.

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *