Metodyka porównywania wydajności operacji SQL Insert w wybranych RDBMS

Poniżej polska wersja artykułu opublikowanego w PIMR,
oryginalna wersja dostępna tutaj

Wprowadzenie
Kluczową kwestią nabierającą coraz większego znaczenia, braną pod uwagę na etapie projektowania, wytwarzania i eksploatowania bazodanowych systemów informatycznych jest problem wydajności i skalowalności aplikacji[1]. W procesie wieloetapowego projektowania danych generalnie nasza uwaga skupiona jest na ich poprawnym odwzorowaniu zgodnie z rozpoznanymi wymaganiami i z przyjętym architektonicznym modelem danych. Zagadnienie wydajności samo w sobie jest zagadnieniem złożonym albowiem możemy go rozpatrywać w kontekście różnych operacji dokonywanych na bazie produkcyjnej lub też w kontekście procesów tworzenia na jej podstawie baz analitycznych[1][7][8]. Z tego obszaru, dość szerokiego, nakreślonego powyżej autorów zainteresował wpływ istnienia klucza podstawowego oaz sposób jego generowania na wydajność zapisu danych w RDBMS. W podejściu badawczym przyjęto perspektywę, interesującą użytkowników gotowego produktu, jaki jest RDBMS, w związku z tym świadomie pominięto zagadnienia związane z mechanizmami fizycznego rozmieszczenia danych na dysku.
Metodyka

Próba odpowiedzi na pytanie na ile obecność klucza podstawowego oraz sposób jego tworzenia wpływa na zapis danych w relacyjnych wymagało opracowania metodyki i wytworzenia niezbędnych narzędzi programistycznych.
Na etapie początkowym przyjęto szereg założeń, których zachowanie powinno eliminować wpływ czynników zakłócających pomiar. Między innymi zdecydowano się, iż aplikacja powinna działać całkowicie po stronie serwera bazodanowego. Tym samym wyeliminowano by wpływ interfejsów programistycznych dostępu do danych typu ADO, ADO.NET itp. na czas zapisu rekordów. Konsekwencją tej decyzji było zwrócenie naszej uwagi tylko na te RDBMS, których wewnętrzny język jest językiem proceduralnym.
Wytworzona procedura, względnie procedury stanowiące podstawowe narzędzie badawcze, powinny tworzyć strukturę tabeli, generować rekordy, a następnie je zapisywać. Równocześnie należałoby rejestrować czas rozpoczęcia i zakończenia zapisu danych z jednoczesnym odnotowywaniem liczby rekordów osadzonych w bazie. Powyższa druga porcja informacji, potrzebna do analiz, gromadzona byłaby w oddzielnej tabeli.
Skoro podstawowym celem autorów było uzyskanie odpowiedzi na pytanie na ile obecność klucza głównego i sposób jego generowania ma wpływ na szybkość zapisu zdecydowano się na utworzenie szeregu niezależnych procedur, osobno dla każdego przewidywanego wariantu. Wspólnym elementem byłby zapis wyników eksperymentu do tej samej tabeli. Przyjęto następujące przypadki, które wiązały się rodzajem informacjami zapisywanych w bazie:
• dane bez klucza podstawowego,
• dane z kluczem głównym, którego wartości generowane są przez RDBMS,
• dane z kluczem głównym, gdzie wartości są generowane na podstawie algorytmu użytkownika.
Procedury to realizujące powinny być zaimplementowane w co najmniej w dwóch RDBMS. Podjęto decyzję, iż będzie to SQL Server 2008, Oracle XE i MySQL. Podstawą wspomnianej decyzji była popularność wykorzystania tych narzędzi informatycznych. I tak dla przykładu na polskim rynku darmowa wersja MSSQL jest wykorzystywana między innymi w znanych programach: Płatnik, MicroSubiekt. Z kolei jego większy brat (płatny) stosowany jest między innymi w jednym z najpopularniejszych programów typu ERP na naszym rynku tj. CDN XL. Baza Mysql skolei dominuje na rynku aplikacji interneotwych, większość skryptów obsługujących fora to tandem Apache, PHP wraz z MySQL. Oprogramowanie bazodanowe dostarczane przez Oracle ma jedną z najlepszych opinii w środowisku IT, dlatego też darmowa wersja (XE) została poddana analogicznym testom jak MSSQL i MySQL.
W prezentowanej metodyce przyjęto jeszcze jedno założenie, z którym w praktyce bazodanowej bardzo często mamy do czynienia, polegające na tym, iż klucz podstawowy jest jedno elementowy, przyjmujący wartości typu int.
Mimo, że zaproponowana metodyka całościowo nie pokrywa się z operacjami przebiegającymi w rzeczywistości, to zdaniem autorów jest dobrym narzędziem do realizacji badań porównawczych. Świadomie zrezygnowano w rozważaniach i badaniach z specjalistycznych narzędzi służących do ładowania danych (typu ETL) wykorzystywanych w tworzeniu hurtowni danych, skupiając się głównie na rozwiązaniach bazodanowych OLTP.

Narzędzie badawcze – procedury

Sygnalizowane powyżej procedury, stanowiące narzędzie badawcze, wytworzono wpierw w języku T-SQL, który jest językiem wbudowanym w SQL Server 2008R2. Pozwala on nie tylko na manipulowanie danymi (składowe języka DML – Data modyfication Language) ale również na definiowanie struktury (składowe języka DDL – Data definition Language). W zaprezentowanej poniżej procedurze, dotyczące przypadku zapisu danych w tabeli bez zdefiniowanego klucza podstawowego, wykorzystano obie wspomniane możliwości języka T-SQL. Podjęto również decyzję o zapisie wyników cząstkowych eksperymentu w dodatkowej tabeli. Do utworzenia agregatów, które tworzą wyniki cząstkowe, odnotowane w tabeli wykorzystano pytanie grupujące wraz z funkcją DateDiff. Grupę tworzą rekordy, które zostały zapisane do bazy w tym samym przedziale czasowym wynoszącym jedna sekunda. Ten sposób grupowania jest możliwy, albowiem jedno z pól tabeli podstawowej (simple_insert_table) zawiera datę i czas systemowy zwróconą przez funkcję GetDate.

CREATE PROCEDURE [dbo].[simple_insert]
@numerow int=1000;
@numtries int= 100;
AS
BEGIN
SET NOCOUNT ON;
declare @testb datetime;
declare @tests datetime;

create table simple_insert_table (
a int, b varchar(10), c datetime );

declare @numtries1 int=@numtries;
declare @numerow1 int=@numerow;

begin transaction;
set @testb = GETDATE();
while @numtries >0
begin
set @numerow = @numerow1;
while @numerow >0
begin
insert into simple_insert_table values (1,’1234567890’,GETDATE());
set @numerow = @numerow -1;
end;
set @numtries = @numtries -1;
end;
set @tests=GETDATE();

insert into test_results (test_name,start_d,stop_d,param1,param2)
values (‘simple insert test’,@testb,@tests,@numerow1,@numtries1);

set @numerow = @@IDENTITY;
insert into test_subresult (test_id, ins_num, time_agr)
select @numerow,count (DATEDIFF(S, ‘19700101’, c)) e, DATEDIFF(S, ‘19700101’, c) from
simple_insert_table
group by DATEDIFF(S, ‘19700101’, c);
insert into test_results (test_name,param1,param2,param3,param4,param5)
select ‘statistic for simple insert: ‘+ cast(@numerow as varchar),exec_per,max_ins,avg_ins,min_ins,stddev_ins from dbo.simple_insert_stat;
drop table simple_insert_table;
commit transaction;
END

Niewielkie zmiany w prezentowanym kodzie pozwoliły na zbudowanie nowych procedur, które stanowiły narzędzie testujące dla dwóch kolejnych przypadków. W pierwszym wariancie klucz był generowany przez algorytm zawarty w procedurze. Zmienna wykorzystywana do przekazywania wartości generowanego klucza do zapytania, stanowiła równocześnie składową warunku, decydującego o liczbie wykonanych pętli. W drugim przypadku do generowania klucza wykorzystano odpowiednie narzędzia serwera bazodanowego. W przypadku SQL Server 2008R2 wykorzystano mechanizm identity, natomiast jego odpowiednikiem na poziomie MySQL był autoincrement. Z kolei w RDBMS Oracle wykorzystano mechanizm sekwencji. Ingerencja w wspomniane narzędzia dotyczyła tylko ustawień początkowych, obejmujących wartość startową oraz skok, które we wszystkich przypadkach były identyczne. Celem zapewnienia możliwości dokonania uogólnień omawiany algorytm został zaimplementowany w języku PL/SQL oraz w proceduralnym rozszerzeniu języka SQL dla bazy MySQL 5.5.8, co pozwoliło na przeprowadzenie testów z wykorzystaniem innych RDBMS.
W trakcie podjętych przez autorów badań pojawiła się, co prawda nowa wersja SQL Server 2011 wyposażona tym razem w dwa mechanizmy, pozwalające na automatyczne tworzenie klucza podstawowego, lecz nie jest to wersja ostateczna, w związku z powyższym nie stała się ona przedmiotem eksperymentów. Tym dodatkowym narzędziem umożliwiającym automatyczne generowanie klucza na poziomie wspomnianego RDBMS jest mechanizm sekwencji [8].
Badania i wyniki

Badania przeprowadzono w wirtualnym środowisku, opartym o system operacyjny Windows XP Home Edition wraz z najnowszymi aktualizacjami. Instalacja miała charakter standardowy podobnie, jak późniejsza instalacja RDBMS (zaaprobowano wszystkie ustawienia kreatora). Po zakończeniu testów danego RDBMS następowała reinstalacja środowiska badawczego. Każdy z testów został także wykonany na maszynie serwerowej podpiętej do macierzy w środowisku Windows Server 2008 ( z wyłączeniem testów bazy Oracle). Procedury dla bazy Oracle zostały przetestowane na serwerze z systemem operacyjnym Ubuntu Server 11 także podpiętym do wydajnej macierzy. Wyniki z środowisk serwerowych były analogiczne do maszyny badawczej (relacje wyników testów dla danego RDBMS).
Powszechnie uważa się iż wyłączenie ograniczeń (np. Klucza podstawowego) podczas wstawiania dużej ilości danych pozwala zwiększyć wydajność zapisu. Opisywany eksperyment pozwala ocenić czy wskazana operacja jest wstanie przynieść realne zyski.
W celu przeprowadzenia testów potrzebne było utworzenie w każdym badanym RDBMS pustej bazy danych, również zgodnie z ustawieniami sugerowanymi przez kreatora. Dało to podstawy do osadzenia w niej procedur testujących z jednoczesnym utworzeniem struktury relacyjnej do gromadzenia wyników badań.
Test przeprowadzono dla każdego z RDBMS dla wyszczególnianych poniżej wariantów, podczas których wprowadzono do bazy 106 rekordów:
• wstawiano dane bez klucza głównego,
• wstawiano dane tworzące klucz główny z poziomu procedury
• wstawiono dane zawierające klucz główny utworzony przy użyciu mechanizmów RDBMS (autoincrement, identity, sequence).

Efekty przeprowadzonych badań zaprezentowano w formie tabelarycznej tab. 1, 2, 3 i 4. Zawierają one wielkości względne, a punktem ich odniesienia są wyniki uzyskane dla pierwszego wariantu badań. Powyższy sposób postępowania zastosowano do rezultatów uzyskiwanych dla każdego z badanych RDBMS. W przypadku MySQL 5.5.8 badania przeprowadzono dla dwóch różnych silników INNODB oraz MyISAM, dostępnych dla tego RDBMS. Natomiast niecelowym, zdaniem autorów było umieszczenie względnych wyników badań uzyskanych dla przypadku zapisu rekordów pozbawionych klucza podstawowego.

Omówienie wyników

Zaprezentowane wyniki badań zapisu danych zawierających i niezawierających klucz podstawowy niewiele się różnią się między sobą w przypadku SQL Server 2008 R2. Dotyczy to zarówno sytuacji, gdy klucz jest tworzony przez RDBMS, jak i przez algorytm zaszyty w procedurze testującej. Opóźnienie zapisu danych wyposażonych w klucz podstawowy, przy przyjęciu prędkości zapisu 5000 wierszy na sekundę oraz liczbie rekordów 106, wynosi 10 sekund. Ważnym odnotowania jest również fakt, iż w trakcie badań nie zaobserwowano spadku prędkości zapisu wierszy wraz ze wzrostem liczby rekordów zawartych w tabeli. Zbliżoną prawidłowość zauważono w przypadku MySQL 5.5.8 wyposażonego w silnik InnoDB z tym, że okazał się on bardziej wrażliwy na sposób generowania klucza podstawowego. Drugą dość zaskakującą zależnością, wynikającą z otrzymanych wyników dla tego RDBMS jest wzrost względnej prędkości zapisu danych, zawierających klucz podstawowy. Dotyczy to zarówno sytuacji gdy klucz główny jest generowany przez system bazodanowy, jak i procedurę. Równoległym faktem, wymagającym wyjaśnienia są zerowe wartości prędkości minimalnej uzyskiwane w trakcie badań MySQL 5.5.8[5]. Skutkowało to znacznym wzrostem odchylenia standardowego oraz tym, że minimalna prędkość względna przyjmowała wartości nieokreślone.
Tego rodzaju prawidłowości nie stwierdzono przy użyciu wcześniejszego silnika MyISAM MySQL 5.5.8 [4]. Odnotowane w tym przypadku tendencje, będę konsekwencjami pomiarów są zgodne z rezultatami uzyskanymi dla Oracle XE, które potwierdzają dotychczasowe przekonanie, że wprowadzenie klucza spowalnia zapis nowych wierszy do tabeli. Należy jednak przypomnieć o istotnych wadach silnika MyISAM, jakim jest brak możliwość tworzenia transakcji i definiowania więzów integralności referencyjnej [6].
Podsumowanie

Współczesne RDBMS bardzo się różnią pod względem implementacji modelu relacyjnego, co utrudniania formułowanie uogólnień i reguł dotyczących wydajności tych systemów informatycznych. W pewnych wypadkach rozwiązania intuicyjne, czy będące efektem dotychczasowych doświadczeń z RDBMS mogą się okazać nieefektywne przy pracy z nowymi systemami bazodanowymi, dlatego autorzy zalecają testowanie proponowanych rozwiązań zwłaszcza przed rozpoczęciem prac rozwojowych tworzonego oprogramowania (zwłaszcza w przypadku tworzenia struktur OLAP). Przeprowadzone badania wraz z dokonaną analizą wyników, z użyciem trzech różnych RDBMS skłoniły autorów do sformułowania następujących uwag i wniosków:
• Wyposażenie danych w klucz podstawowy ogranicza z reguły wydajność zapisu rekordów w każdym badanym RDBMS z wyłączaniem MySQL 5.5 zawierającego silnik InnoDB. Spadek tej wydajności jest zróżnicowany lecz z perspektywy SQL Server 2008R2 jest on mało znaczący.
• Mechanizm generowania klucza podstawowego wbudowany w RDBMS z perspektywy wydajności zapisu nowych wierszy jest generalnie rozwiązaniem lepszym od własnych rozwiązań programistycznych zaszytych w procedurach.
• Wskazanym wydaje się podjęcie dalszych wysiłków poznawczych, zmierzających do wyjaśnienia nietypowego zachowania się MySQL 5.5.8 z silnikiem InnoDB z perspektywy prędkości zapisu danych pozbawianych i wyposażonych w klucz podstawowy.
• Uzyskane całkowite czasy zapisy danych w analizowanych wariantach i w różnych RDBMS i przy zaproponowanej metodyce badań wskazują, że z perspektywy aplikacji OLTP korzyści wydajnościowe, wynikające z niestosowania jednoelementowego klucza podstawowego są mało znaczące.
Bibliografia

[1] Beynon-Davies – Database Systems 2003
[2] Joe Celko – SQL for Smarties, Advanced SQL Programming, 3 Edition 2005
[3] MySQL Reference Manual – http://dev.mysql.com/doc/refman/5.5/en/mysql-nutshell.html 2011
[4] MySQL MyISAM Storage Engine Manual – http://dev.mysql.com/doc/refman/5.0/en/myisam-storage-engine.html
[5] MySQL InnoDB – http://dev.mysql.com/doc/refman/5.5/en/innodb-storage-engine.html
[6] MySQL refman – http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-foreign-keys.html
[7] Paulraj Ponniah – Data Warehousing Fundamentals for IT Professionals 2010
[8] SQL Server perfomance – http://www.sql-server-performance.com/articles/dev/sequence_sql_server_2011_p1.aspx 2011
[9] Chris Todman – Designing a data warehouse: supporting customer relationship management 2003

Leave a Reply

Your email address will not be published. Required fields are marked *

one × five =