Strona korzysta z plików cookies w celu realizacji usług i zgodnie z Polityką Plików Cookies.



06.12.2018

Niższe moce

UPS Eaton 9SX
03.12.2018

Monitory dla MŚP

AOC E1
29.11.2018

Wykrycie szkodliwego...

Sophos Intercept X Advanced
27.11.2018

Automatyzacja zabezpieczeń

Red Hat Ansible Automation
23.11.2018

Nieograniczona skalowalność

SUSE Enterprise Storage 5.5
20.11.2018

Dwa procesory Threadripper

AMD Ryzen Threadripper 2970WX i 2920X
16.11.2018

Dla biznesu i edukacji

Optoma 330USTN
13.11.2018

Superszybki dysk SSD

Patriot Evolver
09.11.2018

Ograniczenie kosztów

Canon imageRUNNER ADVANCE 525/615/715

Indeksy kolumnowe – wskazówki i zastosowania

Data publikacji: 26-09-2018 Autor: Marcin Szeliga
Porównanie wydajności...

W pierwszej części artykułu przedstawione zostały budowa i działanie indeksów kolumnowych na przykładzie zaimplementowanej w serwerze SQL Server technologii xVelocity. W tej części przyjrzymy się praktycznym zastosowaniom indeksów kolumnowych, w tym sposobom osiągnięcia ich maksymalnej wydajności.

 

Typowym wąskim gardłem wydajności serwerów baz danych są dyski – indeksy kolumnowe rozwiązują ten problem. Po pierwsze, skompresowane dane zajmują wielokrotnie (około 10-krotnie) mniej miejsca na dysku, co pozwala ograniczyć liczbę niezbędnych do ich wczytania do pamięci operacji I/O.

Po drugie, struktura indeksów kolumnowych umożliwia odczytywanie tylko potrzebnych do wykonania zapytania kolumn i segmentów, w dodatku za pomocą szesnastokrotnie większych operacji odczytu. SQL Server odczytuje dane w 8-kilobajtowych blokach nazywanych stronami. Ponieważ pojedyncza, duża operacja odczytu jest znacznie szybsza od wielu małych, SQL Server w miarę możliwości przeprowadza odczyty z wyprzedzeniem, czyli jednorazowo wczytuje do pamięci do 64 sąsiednich stron. Pozwala to na jednorazowe wczytanie 512 KB danych. Tymczasem do odczytu danych z indeksów kolumnowych wykorzystywane są operacje I/O o wielkości do 8 MB.

Po trzecie, skompresowane dane zajmują mniej miejsca w pamięci operacyjnej, co pozwala serwerowi na buforowanie ich większej ilości i przekłada się na zmniejszenie liczby odczytów z dysku.
 
Po ograniczeniu wpływu dysków na wydajność kolejnym wąskim gardłem okazuje się procesor. Tutaj rozwiązaniem okazał się tryb blokowego przetwarzania danych. Przekonajmy się, jak duży ma on wpływ na wydajność zapytań.

Zaczniemy od wykonania przykładowych zapytań na tabeli lineitem (sposób utworzenia używanych do pomiaru wydajności tabel został przedstawiony w pierwszej części artykułu) skompresowanej w trybie PAGE. Na początek włączamy statystyki odczytu czasu oraz czyścimy bufor danych:

DBCC DROPCLEANBUFFERS
SET STATISTICS TIME, IO ON

Następnie wykonujemy dwa poniższe zapytania: pierwsze symuluje proste zapytanie analityczne odwołujące się wyłącznie do tabeli faktów, drugie zawiera podzapytanie odwołujące się do kilku powiązanych z nią tabel wymiarów.

SELECT

l_returnflag,
l_linestatus,
SUM(l_quantity) as sum_qty,
SUM(l_extendedprice) as sum_base_price,
SUM(l_extendedprice * (1 – l_discount)) as sum_disc_price,
SUM(l_extendedprice * (1 – l_discount) * (1 + l_tax)) as sum_charge,
AVG(l_quantity) as avg_qty,
AVG(l_extendedprice) as avg_price,
AVG(l_discount) as avg_disc,
COUNT(*) as count_order

FROM lineitem
WHERE l_shipdate <= ‘1998-12-01’
GROUP BY l_returnflag, l_linestatus
ORDER BY l_returnflag, l_linestatus;

Wykonanie prostego zapytania wymagało odczytania z wyprzedzeniem 52 214 stron (417 712 KB danych) i zajęło 2,6 sekundy. Wykonanie złożonego zapytania wymagało dodatkowo odczytania znacznie mniejszych tabel wymiarów i również zajęło 2,5 sekundy, przy czym czas zajęcia procesorów wyniósł 15,5 sekundy (zapytanie wykonywane było wielowątkowo).

Po założeniu dla tabeli lineitem zgrupowanego indeksu kolumnowego i włączeniu kompresji archiwalnej wykonanie tych samych zapytań:

 

  • ƒƒw przypadku prostego zapytania zajęło 0,5 sekundy i wymagało wykonania 4600 operacji odczytu; ƒƒ
  • w przypadku złożonego zapytania zajęło 0,8 sekundy, a czas zajęcia procesorów spadł do 1,6 sekundy.


Jeżeli jednak dla drugiego zapytania wyłączymy tryb blokowego przetwarzania danych, dopisując na jego końcu dyrektywę OPTION (querytraceon 9453), przekonamy się, że czas wykonania tego samego zapytania w trybie blokowym był kilkukrotnie krótszy, jego koszt był ponad 3-krotnie niższy, a czas zajęcia procesora ponad 10-krotnie mniejszy (rysunek).

> PAMIĘĆ RAM

Indeksy kolumnowe do optymalnego działania wymagają wystarczająco dużo pamięci operacyjnej. Jeżeli będzie jej brakowało podczas tworzenia indeksu, dane zostaną podzielone na większą niż optymalna liczbę grup wierszy, a jeśli zabraknie pamięci podczas odczytywania danych z indeksu, do ich przetworzenia użyta zostanie systemowa baza tempdb.

 

Większe grupy wierszy poprawiają wydajność indeksów kolumnowych. Im są one większe, tym większe utworzone na ich podstawie segmenty – ponieważ dane kompresowane są w segmentach, większe segmenty oznaczają większy współczynnik kompresji danych. Z tego powodu, jeżeli serwer SQL nie będzie dysponował wystarczającą ilością pamięci podczas tworzenia czy przebudowywania indeksów kolumnowych, automatycznie zmniejszy rozmiar grup wierszy.


[...]

Pracownik naukowy Wyższej Szkoły Bankowej w Poznaniu Wydział Zamiejscowy w Chorzowie; jest autorem książek poświęconych analizie danych i posiada tytuł Microsoft Most Valuable Professional.

Pełna treść artykułu jest dostępna w papierowym wydaniu pisma.

.

Transmisje online zapewnia: StreamOnline

All rights reserved © 2013 Presscom / Miesięcznik "IT Professional"