Różnice między wybraną wersją a wersją aktualną.
| Both sides previous revision Poprzednia wersja Nowa wersja | Poprzednia wersja | ||
|
pl:dydaktyka:ztb:2010:projekty:partycjonowanie:start [2010/06/01 22:56] ztb2010 |
pl:dydaktyka:ztb:2010:projekty:partycjonowanie:start [2019/06/27 15:50] (aktualna) |
||
|---|---|---|---|
| Linia 15: | Linia 15: | ||
| * Kiedy tabele są bardzo duże\\ | * Kiedy tabele są bardzo duże\\ | ||
| * Kiedy indeksy przekraczają rozmiar pamięci RAM\\ | * Kiedy indeksy przekraczają rozmiar pamięci RAM\\ | ||
| - | |||
| ==== Partycjonowanie w MySQL ==== | ==== Partycjonowanie w MySQL ==== | ||
| Linia 396: | Linia 395: | ||
| Mean time partitioned MyISAM (ms): 0.66273534297943 | Mean time partitioned MyISAM (ms): 0.66273534297943 | ||
| Mean time non-partitioned MyISAM (ms): 0.5912885260582 | Mean time non-partitioned MyISAM (ms): 0.5912885260582 | ||
| - | Total time partitioned InnoSB (s): 1663.7471723557 | + | Total time partitioned InnoDB (s): 1663.7471723557 |
| - | Total time non-partitioned InnoSB (s): 1247.5246930122 | + | Total time non-partitioned InnoDB (s): 1247.5246930122 |
| Mean time partitioned InnoDB (ms): 1.6637471723557 | Mean time partitioned InnoDB (ms): 1.6637471723557 | ||
| - | Mean time non-partitioned InnoSB (ms): 1.2475246930122 | + | 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 == | ||
| Linia 416: | Linia 419: | ||
| $time3 = 0; | $time3 = 0; | ||
| $time4 = 0; | $time4 = 0; | ||
| - | $n = 100000; | + | $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); | ||
| Linia 448: | Linia 453: | ||
| ** Wyniki ** | ** Wyniki ** | ||
| <code text> | <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> | ||
| - | </code> | + | Tabele spartycjonowane są dużo szybsze **(ponad 10x)** od tabel bez partycjonowania. |
| - | === Partycjonowanie w PostreSQL === | + | \\ |
| - | == Implementacja 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: | ||
| Linia 472: | Linia 488: | ||
| * 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.\\ \\ | ||
| Linia 575: | Linia 591: | ||
| </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.\\ | ||
| Linia 607: | Linia 623: | ||
| </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> | ||
| Linia 657: | Linia 673: | ||
| 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: | ||
| Linia 686: | Linia 702: | ||
| 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 | ||
