Both sides previous revision
Poprzednia wersja
Nowa wersja
|
Poprzednia wersja
|
pl:dydaktyka:ztb:2010:projekty:partycjonowanie:start [2010/06/01 22:18] ztb2010 |
pl:dydaktyka:ztb:2010:projekty:partycjonowanie:start [2019/06/27 15:50] (aktualna) |
* Kiedy chcemy wydajnie przechowywać dane historyczne\\ | * Kiedy chcemy wydajnie przechowywać dane historyczne\\ |
* Kiedy tabele są bardzo duże\\ | * Kiedy tabele są bardzo duże\\ |
* Kiedy indeksy przekraczają rozmiar pamięci RAM\\==== Partycjonowanie w MySQL ==== | * Kiedy indeksy przekraczają rozmiar pamięci RAM\\ |
| ==== Partycjonowanie w MySQL ==== |
| |
Mechanizm partycjonowania dostępny jest w MySQL od wersji 5.1 | Mechanizm partycjonowania dostępny jest w MySQL od wersji 5.1 |
| |
=== Testy wydajnościowe na dużych zbiorach danych === | === Testy wydajnościowe na dużych zbiorach danych === |
| |
| == Założenia == |
| * Stworzyć warunki pracy prostej aplikacji, do tego posłuży specjalny skrypt PHP uruchamiany z CLI. |
| * W czasie jednej iteracji wykonać tą samą operację z tymi samymi danymi dla wszystkich testowanych tabel. |
| * Zaburzenia w pracy serwera powinny zostać ewentualnie wychwycone przez wszystkie testy, co sprawia, że testowanie będzie bardziej wiarygodne. |
| * Testujemy, które rozwiązanie jest lepsze, niekoniecznie jak szybko działa. |
| |
| == Sprzęt == |
| ** Procesor ** |
| <code text> |
| cpu family : 6 |
| model : 8 |
| model name : Pentium III (Coppermine) |
| stepping : 3 |
| cpu MHz : 731.297 |
| cache size : 256 KB |
| </code> |
| |
| ** Pamięć ** |
| <code text> |
| MemTotal: 255372 kB |
| </code> |
| |
| ** System operacyjny ** |
| <code text> |
| Linux version 2.6.26-2-686 (Debian 2.6.26-21lenny4) (dannf@debian.org) (gcc version 4.1.3 20080704 (prerelease) (Debian 4.1.2-25)) |
| </code> |
| |
| ** PHP ** |
| <code text> |
| PHP 5.3.2 (cli) (built: Mar 31 2010 16:22:53) |
| Copyright (c) 1997-2010 The PHP Group |
| Zend Engine v2.3.0, Copyright (c) 1998-2010 Zend Technologies |
| with Zend Extension Manager v5.1, Copyright (c) 2003-2010, by Zend Technologies |
| - with Zend Download Server v1.5.0, Copyright (c) 1998-2010 Zend Technologies Ltd., by Zend Technologies [loaded] [licensed] [disabled] |
| - with Zend Data Cache v4.0, Copyright (c) 2004-2010, by Zend Technologies [loaded] [licensed] [disabled] |
| - with Zend Job Queue v4.0, Copyright (c) 2004-2010, by Zend Technologies [loaded] [not licensed] [enabled] |
| - with Zend Session Clustering v4.0, Copyright (c) 2004-2010, by Zend Technologies [loaded] [licensed] [enabled] |
| - with Zend Utils v1.0, Copyright (c) 2004-2010, by Zend Technologies [loaded] [licensed] [enabled] |
| - with Zend Optimizer+ v4.1, Copyright (c) 1999-2010, by Zend Technologies [loaded] [licensed] [disabled] |
| - with Zend Code Tracing v1.0, Copyright (c) 2009-2010, by Zend Technologies [loaded] [not licensed] [disabled] |
| - with Zend Monitor v4.1, Copyright (c) 1999-2010, by Zend Technologies [loaded] [licensed] [disabled] |
| - with Zend Debugger v5.3, Copyright (c) 1999-2010, by Zend Technologies [loaded] [licensed] [enabled] |
| - with Zend Page Cache v4.0, Copyright (c) 2004-2010, by Zend Technologies [loaded] [licensed] [disabled] |
| </code> |
| |
| ** MySQL ** |
| <code text> |
| mysql Ver 14.14 Distrib 5.1.45, for debian-linux-gnu (i486) using readline 5.2 |
| |
| query_cache_limit = 1M |
| query_cache_size = 16M |
| key_buffer = 16M |
| max_allowed_packet = 16M |
| thread_stack = 192K |
| thread_cache_size = 8 |
| </code> |
| |
== Struktura tabeli z partycjonowaniem - silnik MyISAM == | == Struktura tabeli z partycjonowaniem - silnik MyISAM == |
`date` DATETIME NOT NULL , | `date` DATETIME NOT NULL , |
`info` TEXT NOT NULL , | `info` TEXT NOT NULL , |
PRIMARY KEY (`log_id`, `date`) , | PRIMARY KEY (`log_id`, `date`) ) |
INDEX `date` (`date` ASC) ) | |
ENGINE = MyISAM PARTITION BY RANGE(TO_DAYS(date))( | ENGINE = MyISAM PARTITION BY RANGE(TO_DAYS(date))( |
PARTITION p01 VALUES LESS THAN | PARTITION p01 VALUES LESS THAN |
`date` DATETIME NOT NULL , | `date` DATETIME NOT NULL , |
`info` TEXT NOT NULL , | `info` TEXT NOT NULL , |
PRIMARY KEY (`log_id`, `date`) , | PRIMARY KEY (`log_id`, `date`) ) |
INDEX `date` (`date` ASC) ) | |
ENGINE = MyISAM; | ENGINE = MyISAM; |
</code> | </code> |
`date` DATETIME NOT NULL , | `date` DATETIME NOT NULL , |
`info` TEXT NOT NULL , | `info` TEXT NOT NULL , |
PRIMARY KEY (`log_id`, `date`) , | PRIMARY KEY (`log_id`, `date`) ) |
INDEX `date` (`date` ASC) ) | |
ENGINE = innoDB PARTITION BY RANGE(TO_DAYS(date))( | ENGINE = innoDB PARTITION BY RANGE(TO_DAYS(date))( |
PARTITION p01 VALUES LESS THAN | PARTITION p01 VALUES LESS THAN |
`date` DATETIME NOT NULL , | `date` DATETIME NOT NULL , |
`info` TEXT NOT NULL , | `info` TEXT NOT NULL , |
PRIMARY KEY (`log_id`, `date`) , | PRIMARY KEY (`log_id`, `date`) ) |
INDEX `date` (`date` ASC) ) | |
ENGINE = innoDB; | ENGINE = innoDB; |
</code> | </code> |
** Wyniki ** | ** Wyniki ** |
<code text> | <code text> |
Total time partitioned MyISAM (s): 1502.8280341625 | Total time partitioned MyISAM (s): 662.73534297943 |
Total time non-partitioned MyISAM (s): 2083.5244688988 | Total time non-partitioned MyISAM (s): 591.2885260582 |
Mean time partitioned MyISAM (ms): 1.5028280341625 | Mean time partitioned MyISAM (ms): 0.66273534297943 |
Mean time non-partitioned MyISAM (ms): 2.0835244688988 | Mean time non-partitioned MyISAM (ms): 0.5912885260582 |
Total time partitioned InnoDB (s): 8349.2716002464 | Total time partitioned InnoDB (s): 1663.7471723557 |
Total time non-partitioned InnoDB (s): 4752.3431515694 | Total time non-partitioned InnoDB (s): 1247.5246930122 |
Mean time partitioned InnoDB (ms): 8.3492716002464 | Mean time partitioned InnoDB (ms): 1.6637471723557 |
Mean time non-partitioned InnoDB (ms): 4.7523431515694 | Mean time non-partitioned InnoDB (ms): 1.2475246930122 |
</code> | </code> |
| |
| Na naszej maszynie odczuwalna jest lekka różnica między dodawaniem danych do tabel spartycjonowanych i niespartycjonowanych. Związane jest to z dodatkowymi operacjami jakie musi wykonać baza danych dla polecenia INSERT. |
| \\ |
| Zgodnie z oczekiwaniami, mechanizm składowania MyISAM jest szybszy od transakcyjnego InnoDB. |
| |
== pobieranie danych == | == pobieranie danych == |
$time3 = 0; | $time3 = 0; |
$time4 = 0; | $time4 = 0; |
$n = 1000000; | $n = 1000; |
| |
for($i=0; $i<$n; $i++){ | for($i=0; $i<$n; $i++){ |
$date = "2010-".rand(1,12)."-".rand(1,28)." ".rand(0,23).":".rand(0,59).":".rand(0,59); | $m = rand(1,12); |
| $d = rand(1,25); |
| $d2 = $d+3; |
$start1 = microtime(true); | $start1 = microtime(true); |
$r = mysql_query("SELECT SQL_NO_CACHE * FROM `logs_partitioned_myisam` WHERE `date` = '".$date."'"); | $r = mysql_query("SELECT SQL_NO_CACHE * FROM `logs_partitioned_myisam` WHERE `date` BETWEEN '2010-".$m."-".$d."' AND '2010-".$m."-".$d2."'"); |
while($row = mysql_fetch_row($r)){} | while($row = mysql_fetch_row($r)){} |
$stop1 = microtime(true); | $stop1 = microtime(true); |
$start2 = microtime(true); | $start2 = microtime(true); |
$r = mysql_query("SELECT SQL_NO_CACHE * FROM `logs_nonpartitioned_myisam` WHERE `date` = '".$date."'"); | $r = mysql_query("SELECT SQL_NO_CACHE * FROM `logs_nonpartitioned_myisam` WHERE `date` BETWEEN '2010-".$m."-".$d."' AND '2010-".$m."-".$d2."'"); |
while($row = mysql_fetch_row($r)){} | while($row = mysql_fetch_row($r)){} |
$stop2 = microtime(true); | $stop2 = microtime(true); |
$start3 = microtime(true); | $start3 = microtime(true); |
$r = mysql_query("SELECT SQL_NO_CACHE * FROM `logs_partitioned_innodb` WHERE `date` = '".$date."'"); | $r = mysql_query("SELECT SQL_NO_CACHE * FROM `logs_partitioned_innodb` WHERE `date` BETWEEN '2010-".$m."-".$d."' AND '2010-".$m."-".$d2."'"); |
while($row = mysql_fetch_row($r)){} | while($row = mysql_fetch_row($r)){} |
$stop3 = microtime(true); | $stop3 = microtime(true); |
$start4 = microtime(true); | $start4 = microtime(true); |
$r = mysql_query("SELECT SQL_NO_CACHE * FROM `logs_nonpartitioned_innodb` WHERE `date` = '".$date."'"); | $r = mysql_query("SELECT SQL_NO_CACHE * FROM `logs_nonpartitioned_innodb` WHERE `date` BETWEEN '2010-".$m."-".$d."' AND '2010-".$m."-".$d2."'"); |
while($row = mysql_fetch_row($r)){} | while($row = mysql_fetch_row($r)){} |
$stop4 = microtime(true); | $stop4 = microtime(true); |
| |
$time1 += ($stop1-$start1); | $time1 += ($stop1-$start1); |
$time2 += ($stop2-$start2); | $time2 += ($stop2-$start2); |
</code> | </code> |
| |
=== Partycjonowanie w PostreSQL === | ** Wyniki ** |
== Implementacja partycjonowania == | <code text> |
| Total time partitioned MyISAM (s): 38.746290206909 |
| Total time non-partitioned MyISAM (s): 321.26728057861 |
| Mean time partitioned MyISAM (ms): 38.746290206909 |
| Mean time non-partitioned MyISAM (ms): 321.26728057861 |
| Total time partitioned InnoDB (s): 148.56066942215 |
| Total time non-partitioned InnoDB (s): 1536.3432192802 |
| Mean time partitioned InnoDB (ms): 148.56066942215 |
| Mean time non-partitioned InnoDB (ms): 1536.3432192802 |
| </code> |
| |
| Tabele spartycjonowane są dużo szybsze **(ponad 10x)** od tabel bez partycjonowania. |
| \\ |
| Przy pobieraniu danych, prosty mechanizm składowania MyISAM jest dużo szybszy od transakcyjnego InnoDB. |
| ==== Partycjonowanie w PostreSQL ==== |
| === Implementacja partycjonowania === |
Aby dokonać partycjonowania tabeli należy wykonać następujące czynności: | Aby dokonać partycjonowania tabeli należy wykonać następujące czynności: |
| |
* 4. Dla każdej partycji utworzyć indeks na kolumnie klucza (nie jest to bezwzględnie konieczne, ale w większości przypadków przydaje się) | * 4. Dla każdej partycji utworzyć indeks na kolumnie klucza (nie jest to bezwzględnie konieczne, ale w większości przypadków przydaje się) |
* 5. Opcjonalnie można zdefiniować trigger przekierowujący dane z głównej tabeli do odpowiednich partycji. | * 5. Opcjonalnie można zdefiniować trigger przekierowujący dane z głównej tabeli do odpowiednich partycji. |
* 6. Upewnić się, że parametr [[http://http://www.postgresql.org/docs/current/static/runtime-config-query.html#GUC-CONSTRAINT-EXCLUSION|constraint_exclusion]] nie został wyłączony w ''postgresql.conf''. | * 6. Upewnić się, że parametr [[http://www.postgresql.org/docs/current/static/runtime-config-query.html#GUC-CONSTRAINT-EXCLUSION|constraint_exclusion]] nie został wyłączony w ''postgresql.conf''. |
W przeciwnym wypadku zapytania nie będą odpowiednio zoptymalizowane | W przeciwnym wypadku zapytania nie będą odpowiednio zoptymalizowane |
Np. Załóżmy, że w naszej tabeli gromadzone są pomiary temperatury każdego dnia w różnych regionach świata.\\ \\ | Np. Załóżmy, że w naszej tabeli gromadzone są pomiary temperatury każdego dnia w różnych regionach świata.\\ \\ |
</code> | </code> |
\\ | \\ |
== Zarządzanie partycjami == | === Zarządzanie partycjami === |
Zazwyczaj zbiór partycji utworzonych podczas definiowania tabeli z czasem ulega zmianom. Często usuwa się stare partycje i dodaje nowe. Jedną z największych zalet partycjonowania jest to, | Zazwyczaj zbiór partycji utworzonych podczas definiowania tabeli z czasem ulega zmianom. Często usuwa się stare partycje i dodaje nowe. Jedną z największych zalet partycjonowania jest to, |
że pozwala na wykonywanie tego nieprzyjemnego zadania niemal natychmiast poprzez manipulowanie strukturą partycji, a nie poprzez fizyczne operowanie na dużej ilości danych.\\ | że pozwala na wykonywanie tego nieprzyjemnego zadania niemal natychmiast poprzez manipulowanie strukturą partycji, a nie poprzez fizyczne operowanie na dużej ilości danych.\\ |
</code> | </code> |
\\ | \\ |
== Partycjonowanie i Constraint exclusion == | === Partycjonowanie i Constraint exclusion === |
//Contsraint exclusion to technika optymalizacji zapytań dla partycjonowanych tabel// Np.\\ | //Contsraint exclusion to technika optymalizacji zapytań dla partycjonowanych tabel// Np.\\ |
<code sql> | <code sql> |
Zaleca się włączanie parametru ''constraint_exclusion'' tylko w wypadku zapytań operujących na spartycjonowanych tabelach. | Zaleca się włączanie parametru ''constraint_exclusion'' tylko w wypadku zapytań operujących na spartycjonowanych tabelach. |
| |
== Alternatywne metody partycjonowania == | === Alternatywne metody partycjonowania === |
| |
Innym sposobem wstawianie danych do partycji jest ustanowienie reguł, np: | Innym sposobem wstawianie danych do partycji jest ustanowienie reguł, np: |
UNION ALL SELECT * FROM measurement_y2007m12 | UNION ALL SELECT * FROM measurement_y2007m12 |
UNION ALL SELECT * FROM measurement_y2008m01; | UNION ALL SELECT * FROM measurement_y2008m01; |
| </code> |
| === Testy wydajnościowe === |
| |
| Sprzęt \\ |
| <code> |
| Procesor: Intel Core 2 Duo e6750@3200MHz |
| RAM: 4GB DDR II |
| </code> |
| System operacyjny: |
| <code> |
| Linux Gentoo 2.6.33 x86_64 |
| </code> |
| Postgres: |
| <code> |
| Postgresql-8.4.4 |
| </code> |
| |
| Struktura niepartycjonowanej tabeli: |
| <code sql> |
| CREATE TABLE logs_not_partitioned |
| ( |
| LOG_ID BIGINT NOT NULL, |
| DATE DATE NOT NULL, |
| INFO TEXT NOT NULL |
| ); |
| |
| ALTER TABLE logs_not_partitioned ADD CONSTRAINT PK_LOG_T PRIMARY KEY (LOG_ID); |
| </code> |
| |
| Struktura partycjonowanej tabeli: |
| |
| <code sql> |
| CREATE TABLE logs_partitioned |
| ( |
| LOG_ID BIGINT NOT NULL, |
| DATE DATE NOT NULL, |
| INFO TEXT NOT NULL |
| ); |
| |
| ALTER TABLE logs_partitioned ADD CONSTRAINT PK_LOGP_T PRIMARY KEY (LOG_ID); |
| </code> |
| |
| <code sql> |
| CREATE TABLE measurement_y2010m06d03 ( |
| CHECK ( date = DATE '2010-06-03') |
| ) INHERITS (logs_partitioned); |
| CREATE TABLE measurement_y2010m06d04 ( |
| CHECK ( date = DATE '2010-06-04') |
| ) INHERITS (logs_partitioned); |
| CREATE TABLE measurement_y2010m06d05 ( |
| CHECK ( date = DATE '2010-06-05') |
| ) INHERITS (logs_partitioned); |
| CREATE TABLE measurement_y2010m06d06 ( |
| CHECK ( date = DATE '2010-06-06') |
| ) INHERITS (logs_partitioned); |
| CREATE TABLE measurement_y2010m06d07 ( |
| CHECK ( date = DATE '2010-06-07') |
| ) INHERITS (logs_partitioned); |
| CREATE TABLE measurement_y2010m06d08 ( |
| CHECK ( date = DATE '2010-06-08') |
| ) INHERITS (logs_partitioned); |
| CREATE TABLE measurement_y2010m06d09 ( |
| CHECK ( date = DATE '2010-06-09') |
| ) INHERITS (logs_partitioned); |
| CREATE TABLE measurement_y2010m06d10 ( |
| CHECK ( date = DATE '2010-06-10') |
| ) INHERITS (logs_partitioned); |
| CREATE TABLE measurement_y2010m06d11 ( |
| CHECK ( date = DATE '2010-06-11') |
| ) INHERITS (logs_partitioned); |
| CREATE TABLE measurement_y2010m06d12 ( |
| CHECK ( date = DATE '2010-06-12') |
| ) INHERITS (logs_partitioned); |
| CREATE TABLE measurement_y2010m06d13 ( |
| CHECK ( date = DATE '2010-06-13') |
| ) INHERITS (logs_partitioned); |
| </code> |
| |
| Funkcje i triggery opisane powyżej: |
| <code sql> |
| CREATE OR REPLACE FUNCTION measurement_insert_trigger() |
| RETURNS TRIGGER AS $$ |
| BEGIN |
| IF (NEW.date = DATE '2010-06-03') THEN |
| INSERT INTO measurement_y2010m06d03 VALUES (NEW.*); |
| ELSIF (NEW.date = DATE '2010-06-04') THEN |
| INSERT INTO measurement_y2010m06d04 VALUES (NEW.*); |
| ELSIF (NEW.date = DATE '2010-06-05') THEN |
| INSERT INTO measurement_y2010m06d05 VALUES (NEW.*); |
| ELSIF (NEW.date = DATE '2010-06-06') THEN |
| INSERT INTO measurement_y2010m06d06 VALUES (NEW.*); |
| ELSIF (NEW.date = DATE '2010-06-07') THEN |
| INSERT INTO measurement_y2010m06d07 VALUES (NEW.*); |
| ELSIF (NEW.date = DATE '2010-06-08') THEN |
| INSERT INTO measurement_y2010m06d08 VALUES (NEW.*); |
| ELSIF (NEW.date = DATE '2010-06-09') THEN |
| INSERT INTO measurement_y2010m06d09 VALUES (NEW.*); |
| ELSIF (NEW.date = DATE '2010-06-10') THEN |
| INSERT INTO measurement_y2010m06d10 VALUES (NEW.*); |
| ELSIF (NEW.date = DATE '2010-06-11') THEN |
| INSERT INTO measurement_y2010m06d11 VALUES (NEW.*); |
| ELSIF (NEW.date = DATE '2010-06-12') THEN |
| INSERT INTO measurement_y2010m06d12 VALUES (NEW.*); |
| ELSIF (NEW.date = DATE '2010-06-13') THEN |
| INSERT INTO measurement_y2010m06d13 VALUES (NEW.*); |
| ELSE |
| RAISE EXCEPTION 'Date out of range. Fix the measurement_insert_trigger() function!'; |
| END IF; |
| RETURN NULL; |
| END; |
| $$ |
| LANGUAGE plpgsql; |
| </code> |
| |
| <code sql> |
| CREATE TRIGGER insert_measurement_trigger |
| BEFORE INSERT ON logs_partitioned |
| FOR EACH ROW EXECUTE PROCEDURE measurement_insert_trigger(); |
| </code> |
| |
| **Procedura testowa**\\ |
| Do celów testowych napisana została prosta aplikacja w Javie. \\ |
| Najpierw do bazy wprowadzono dużą ilość danych (1000000 rekordów).\\ |
| Wyniki: |
| <code> |
| Non-partitioned 859.245s |
| Partitioned 921.565 |
| </code> |
| Wprowadzanie danych do partycji trwa nieco dłużej, ponieważ sprawdzane są warunki ograniczające w każdej z tabeli potomnych. \\ \\ |
| |
| Za pomocą tej samej aplikacji pobrane zostały wprowadzone wcześniej dane:\\ |
| Wyniki |
| <code> |
| Non-partitioned 77.442s |
| Partitioned 8.636s |
</code> | </code> |
| |
==== Zaawansowane możliwości partycjonowania ==== | W przypadku pobierania danych, co jest operacją wykonywaną z reguły częściej niż wstawianie, widać zysk wynikający z zastosowania partycjonowania. |
| |
=== Sub-partitioning === | |
==== Zalety i wady partycjonowania ==== | ==== Zalety i wady partycjonowania ==== |
| |
=== Zalety === | === Zalety === |
| * Oszczędność pamięci przy bardzo dużych zbiorach danych |
| * Usprawnienia wydajnościowe przy dodawaniu oraz pobieraniu danych z bardzo dużych zbiorów danych (Huge Table) |
| * Możliwe rozróżnienie funkcjonalne przy replikacji danych (Serwer Master - szybkie inserty poprzez partycjonowanie HASH; Serwer Slave - szybkie pobieranie danych poprzez partycjonowanie RANGE) |
| * Możliwość utrzymywania różnych partycji tej samej tabeli na różnych typach storage (dysk, ramdisk, ntfs) - skalowalność |
| * Indeksy tabeli również są partycjonowane |
| |
=== Wady === | === Wady === |
| * Konieczność przygotowania podziału partycji przy tworzeniu tabeli / aktualizacji struktury tabeli |
| * Partycjonowanie można wykonać tylko po kolumnach zawartych w kluczu podstawowym |
| * Partycjonowanie można wykonać tylko po kolumnach zawierajacych liczby lub po wyrażaniu zwracającym liczbę |
| ===== Bibliografia ===== |
| * Manual MySQL: http://dev.mysql.com/doc/refman/5.1/en/partitioning.html |
| * Manual PostreSQL: http://www.postgresql.org/docs/8.1/interactive/ddl-partitioning.html |
| * MySQL partitions tutorial przygotowany przez developerów MySQL: http://www.slideshare.net/datacharmer/mysql-partitions-tutorial |
| |