--- pl:dydaktyka:ztb:2010:projekty:partycjonowanie:start [2010/05/31 22:25]
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 indeksy przekraczają rozmiar pamięci RAM\\
 ==== Partycjonowanie w MySQL ====
@@ Linia 141: / Linia 140: @@
 </code>
-Wyrażenie nie musi zwracać typu INTEGER. Użyty algorytm jest taki sam jak w funkcji PASSWORD().
+Wyrażenie nie musi zwracać typu INTEGER. Użyty algorytm jest taki sam jak w funkcji PASSWORD()
 === Przykłady poprawnego podejścia do partycjonowania danych ===
+**Przykład 1: Partycjonowanie po dacie**
+<code sql>
+CREATE TABLE t1 (
+  d date
+)
+PARTITION BY RANGE (YEAR(d))
+(
+  PARTITION p01 VALUES LESS THAN (1999),
+  PARTITION p02 VALUES LESS THAN (2000),
+  PARTITION p03 VALUES LESS THAN (2001),
+  PARTITION p04 VALUES LESS THAN (MAXVALUE)
+);
+</code>
+Partycjonowanie zoptymalizowane jest dla typu DATE, dlatego złym pomysłem jest używanie UNIX_TIMESTAMP do przechowywania dat.
+**Przykład 2:**\\
+Mamy partycjonowaną tabelę:
+<code sql>
+PARTITION BY RANGE(year(from_date))
+</code>
+Wykonujemy zapytanie:
+<code sql>
+SELECT count(*) FROM salaries WHERE YEAR(from_date) = 1998;
+</code>
+Czas wykonania: 2.25 sec
+Drugie zapytanie:
+<code sql>
+SELECT count(*) FROM salaries WHERE from_date BETWEEN '1998-01-01' AND '1998-12-31';
+</code>
+Czas wykonania: 0.46 sec
+Różnica w czasie wynika z tego, że w pierwszym zapytaniu nie wykorzystujemy informacji, że tabela jest partycjonowana po roku. Przez to zapytanie zostanie wykonane na każdej z partycji. W drugim przypadku baza danych wykona zapytanie tylko na partycjach, które należą do danego zakresu dat.
 === Testy wydajnościowe na dużych zbiorach danych ===
-TODO: benchmark myisam?
-== Struktura tabeli z partycjonowaniem ==
+== 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 ==
 <code sql>
-CREATE  TABLE IF NOT EXISTS `logs_partitioned` (
+CREATE  TABLE IF NOT EXISTS `logs_partitioned_myisam` (
   `log_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT ,
   `date` DATETIME 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 = InnoDB PARTITION BY RANGE(TO_DAYS(date))(
 PARTITION p01 VALUES LESS THAN
 (TO_DAYS('2010-01-01')),
@@ Linia 187: / Linia 281: @@
 </code>
-== Struktura tabeli bez partycjonowania, zgodna z praktykami projektowania tabel ==
+== Struktura tabeli bez partycjonowania - silnik MyISAM  ==
 <code sql>
-CREATE TABLE IF NOT EXISTS `logs_nonpartitioned` (
+CREATE TABLE IF NOT EXISTS `logs_nonpartitioned_myisam` (
   `log_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT ,
   `date` DATETIME NOT NULL ,
   `info` TEXT NOT NULL ,
-  PRIMARY KEY (`log_id`) ,
+  PRIMARY KEY (`log_id`, `date`) )
-  INDEX `date` (`date` ASC) )
+ENGINE = MyISAM;
-ENGINE = InnoDB;
+</code>
+== Struktura tabeli z partycjonowaniem - silnik innoDB ==
+<code sql>
+CREATE  TABLE IF NOT EXISTS `logs_partitioned_innodb` (
+  `log_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT ,
+  `date` DATETIME NOT NULL ,
+  `info` TEXT NOT NULL ,
+  PRIMARY KEY (`log_id`, `date`) )
+ENGINE = innoDB PARTITION BY RANGE(TO_DAYS(date))(
+PARTITION p01 VALUES LESS THAN
+(TO_DAYS('2010-01-01')),
+PARTITION p02 VALUES LESS THAN
+(TO_DAYS('2010-02-01')),
+PARTITION p03 VALUES LESS THAN
+(TO_DAYS('2010-03-01')),
+PARTITION p04 VALUES LESS THAN
+(TO_DAYS('2010-04-01')),
+PARTITION p05 VALUES LESS THAN
+(TO_DAYS('2010-05-01')),
+PARTITION p06 VALUES LESS THAN
+(TO_DAYS('2010-06-01')),
+PARTITION p07 VALUES LESS THAN
+(TO_DAYS('2010-07-01')),
+PARTITION p08 VALUES LESS THAN
+(TO_DAYS('2010-08-01')),
+PARTITION p09 VALUES LESS THAN
+(TO_DAYS('2010-09-01')),
+PARTITION p10 VALUES LESS THAN
+(TO_DAYS('2010-10-01')),
+PARTITION p11 VALUES LESS THAN
+(TO_DAYS('2010-11-01')),
+PARTITION p12 VALUES LESS THAN
+(TO_DAYS('2010-12-01')),
+PARTITION p13 VALUES LESS THAN
+(TO_DAYS('2011-01-01')),
+PARTITION p14 VALUES LESS THAN (MAXVALUE)
+);
+</code>
+== Struktura tabeli bez partycjonowania - silnik innoDB ==
+<code sql>
+CREATE TABLE IF NOT EXISTS `logs_nonpartitioned_innodb` (
+  `log_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT ,
+  `date` DATETIME NOT NULL ,
+  `info` TEXT NOT NULL ,
+  PRIMARY KEY (`log_id`, `date`) )
+ENGINE = innoDB;
 </code>
 == Dodawanie danych ==
-** skrypt testujący **
+** Skrypt testujący **
-NIE DZIALA WKLEJANIE KODU!!!
+<code php>
-** wyniki **
+$link = mysql_connect("localhost", "root", "pass");
+if (!$link) {
+    die("Could not connect: " . mysql_error());
+}
+mysql_select_db("partitioning", $link);
+mysql_query("TRUNCATE TABLE `logs_partitioned_myisam`");
+mysql_query("TRUNCATE TABLE `logs_partitioned_innodb`");
+mysql_query("TRUNCATE TABLE `logs_nonpartitioned_innodb`");
+mysql_query("TRUNCATE TABLE `logs_nonpartitioned_myisam`");
+$time1 = 0;
+$time2 = 0;
+$time3 = 0;
+$time4 = 0;
+$n = 1000000;
+for($i=0; $i<$n; $i++){
+	$date = "2010-".rand(1,12)."-".rand(1,28)." ".rand(0,23).":".rand(0,59).":".rand(0,59);
+	$text = md5(uniqid(rand(1,132456), true));
+	$start1 = microtime(true);
+	mysql_query("INSERT INTO `logs_partitioned_myisam` (date, info) VALUES ('".$date."', '".$text."')");
+	$stop1 = microtime(true);
+	$start2 = microtime(true);
+	mysql_query("INSERT INTO `logs_nonpartitioned_myisam` (date, info) VALUES ('".$date."', '".$text."')");
+	$stop2 = microtime(true);
+	$start3 = microtime(true);
+	mysql_query("INSERT INTO `logs_partitioned_innodb` (date, info) VALUES ('".$date."', '".$text."')");
+	$stop3 = microtime(true);
+	$start4 = microtime(true);
+	mysql_query("INSERT INTO `logs_nonpartitioned_innodb` (date, info) VALUES ('".$date."', '".$text."')");
+	$stop4 = microtime(true);
+	$time1 += ($stop1-$start1);
+	$time2 += ($stop2-$start2);
+	$time3 += ($stop3-$start3);
+	$time4 += ($stop4-$start4);
+}
+mysql_close($link);
+</code>
+** Wyniki **
+<code text>
+Total time partitioned MyISAM (s): 662.73534297943
+Total time non-partitioned MyISAM (s): 591.2885260582
+Mean time partitioned MyISAM (ms): 0.66273534297943
+Mean time non-partitioned MyISAM (ms): 0.5912885260582
+Total time partitioned InnoDB (s): 1663.7471723557
+Total time non-partitioned InnoDB (s): 1247.5246930122
+Mean time partitioned InnoDB (ms): 1.6637471723557
+Mean time non-partitioned InnoDB (ms): 1.2475246930122
+</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 ==
-** skrypt testujący **
+** Skrypt testujący **
-NIE DZIALA WKLEJANIE KODU!!!
+<code php>
+$link = mysql_connect('localhost', 'user', 'pass');
+if (!$link) {
+    die('Could not connect: ' . mysql_error());
+}
-=== Partycjonowanie w PostreSQL - lekko nieintuicyjnie... ===
+mysql_select_db("partitioning", $link);
-==== Zaawansowane możliwości partycjonowania ====
+$time1 = 0;
+$time2 = 0;
+$time3 = 0;
+$time4 = 0;
+$n = 1000;
-=== Sub-partitioning ===
+for($i=0; $i<$n; $i++){
+	$m = rand(1,12);
+	$d = rand(1,25);
+	$d2 = $d+3;
+	$start1 = microtime(true);
+	$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)){}
+	$stop1 = microtime(true);
+	$start2 = microtime(true);
+	$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)){}
+	$stop2 = microtime(true);
+	$start3 = microtime(true);
+	$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)){}
+	$stop3 = microtime(true);
+	$start4 = microtime(true);
+	$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)){}
+	$stop4 = microtime(true);
+	$time1 += ($stop1-$start1);
+	$time2 += ($stop2-$start2);
+	$time3 += ($stop3-$start3);
+	$time4 += ($stop4-$start4);
+}
+mysql_close($link);
+</code>
+** Wyniki **
+<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:
+  * 1. Utworzyć główną tabelę, po której wszystkie partycje będą dziedziczyły.
+  * 2. Utworzyć kilka tabel potomnych (partycji) dziedziczących po głównej tabeli. Zazwyczaj potomne tabele nie posiadają żadnych dodatkowych kolumn.
+  * 3. Dodać ograniczenia do partycji aby zdefiniować dopuszczalne wartości kluczy
+<code sql>
+CHECK ( x = 1 )
+CHECK ( county IN ( 'Oxfordshire', 'Buckinghamshire', 'Warwickshire' ))
+CHECK ( outletID >= 100 AND outletID < 200 )
+</code>
+\\
+Należy upewnić się, że nałożone ograniczenia gwarantują unikalność kluczy w poszczególnych partycjach.\\
+Częstym błędem jest:\\
+<code sql>
+CHECK ( outletID BETWEEN 100 AND 200 )
+CHECK ( outletID BETWEEN 200 AND 300 )
+</code>
+W takim przypadku nie wiadomo gdzie należy klucz o wartości 200.\\
+  * 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.
+  * 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
+Np. Załóżmy, że w naszej tabeli gromadzone są pomiary temperatury każdego dnia w różnych regionach świata.\\ \\
+Potrzebna jest następująca tabela:
+<code sql>
+CREATE TABLE measurement (
+    region_id       int not null,
+    logdate         date not null,
+    peaktemp        int,
+);
+</code>
+\\
+Wiemy, że zdecydowana większość zapytań będzie dotyczyła danych z ostatniego tygodnia/miesiąca/kwartału. Aby zredukować ilość niepotrzebne gromadzonych starych danych decydujemy się przechowywać
+jedynie informacje z 3 ostatnich lat. Na początku każdego miesiąca usuwane są najstarsze dane:\\
+W takiej sytuacji partycjonowanie może pomóc nam w sprostaniu różnym wymaganiom dla tabeli ''measurments''. \\
+Zgodnie z krokami wymienionymi powyżej, możemy dokonać partycjonowania w następujący sposób:
+  * 1. Główna tabela ''measurments'' została utworzona.
+  * 2. Tworzymy partycje dla każdego miesiąca:
+<code sql>
+CREATE TABLE measurement_y2006m02 ( ) INHERITS (measurement);
+CREATE TABLE measurement_y2006m03 ( ) INHERITS (measurement);
+...
+CREATE TABLE measurement_y2007m11 ( ) INHERITS (measurement);
+CREATE TABLE measurement_y2007m12 ( ) INHERITS (measurement);
+CREATE TABLE measurement_y2008m01 ( ) INHERITS (measurement);
+</code>
+\\
+Wszystkie partycje są pełnoprawnymi tabelami, ale dziedziczą definicję po tabeli ''measurments''.
+To rozwiązuje problem z usuwanie danych - każdego miesiąca musimy wykonać ''DROP TABLE'' na najstarszej tabeli oraz utworzyć nową tabelę potomną dla danych z nowego miesiąca.
+  * 3. Musimy zapewnić unikalność kluczy we wszystkich partycjach. W związku z tym powyższy skrypt powinien w rzeczywistości wyglądać tak:
+<code sql>
+CREATE TABLE measurement_y2006m02 (
+    CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' )
+) INHERITS (measurement);
+CREATE TABLE measurement_y2006m03 (
+    CHECK ( logdate >= DATE '2006-03-01' AND logdate < DATE '2006-04-01' )
+) INHERITS (measurement);
+...
+CREATE TABLE measurement_y2007m11 (
+    CHECK ( logdate >= DATE '2007-11-01' AND logdate < DATE '2007-12-01' )
+) INHERITS (measurement);
+CREATE TABLE measurement_y2007m12 (
+    CHECK ( logdate >= DATE '2007-12-01' AND logdate < DATE '2008-01-01' )
+) INHERITS (measurement);
+CREATE TABLE measurement_y2008m01 (
+    CHECK ( logdate >= DATE '2008-01-01' AND logdate < DATE '2008-02-01' )
+) INHERITS (measurement);
+</code>
+  * 4. Potrzebujemy indeksów na kolumnach kluczy:
+<code sql>
+CREATE INDEX measurement_y2006m02_logdate ON measurement_y2006m02 (logdate);
+CREATE INDEX measurement_y2006m03_logdate ON measurement_y2006m03 (logdate);
+...
+CREATE INDEX measurement_y2007m11_logdate ON measurement_y2007m11 (logdate);
+CREATE INDEX measurement_y2007m12_logdate ON measurement_y2007m12 (logdate);
+CREATE INDEX measurement_y2008m01_logdate ON measurement_y2008m01 (logdate);
+</code>
+\\
+  * 5. Chcemy, aby w naszej aplikacji działała konstrukcja ''INSERT INTO measurments...'', a wszystkie dane były automatycznie przekierowywane do odpowiednich partycji.
+ Możemy to osiągnąć poprzez podpięcie odpowiedniej funkcji do głównej tabeli.\\
+Jeśli dane będą dodaane tylko do ostatniej partycji możemy użyć bardzo prostej funkcji:\\
+<code sql>
+CREATE OR REPLACE FUNCTION measurement_insert_trigger()
+RETURNS TRIGGER AS $$
+BEGIN
+    INSERT INTO measurement_y2008m01 VALUES (NEW.*);
+    RETURN NULL;
+END;
+$$
+LANGUAGE plpgsql;
+</code>
+Po utworzeniu funkcji tworzymy trigger uruchamiający funkcję:\\
+<code sql>
+CREATE TRIGGER insert_measurement_trigger
+    BEFORE INSERT ON measurement
+    FOR EACH ROW EXECUTE PROCEDURE measurement_insert_trigger();
+</code>
+Musimy uaktualniać funkcję każdego miesiąca, aby dodawała dane gdo odpowiedniej partycji. Definicja triggera nie musi być uaktualniana, jednakże
+możemy chcieć dodawać dane i żądać od serwera automatycznej lokalizacji partycji, do której wiersz powinien zostać wstawiony. Możemy to osiągnąć poprzez użycie bardziej złożonej funkcji triggera:
+<code sql>
+CREATE OR REPLACE FUNCTION measurement_insert_trigger()
+RETURNS TRIGGER AS $$
+BEGIN
+    IF ( NEW.logdate >= DATE '2006-02-01' AND
+         NEW.logdate < DATE '2006-03-01' ) THEN
+        INSERT INTO measurement_y2006m02 VALUES (NEW.*);
+    ELSIF ( NEW.logdate >= DATE '2006-03-01' AND
+            NEW.logdate < DATE '2006-04-01' ) THEN
+        INSERT INTO measurement_y2006m03 VALUES (NEW.*);
+    ...
+    ELSIF ( NEW.logdate >= DATE '2008-01-01' AND
+            NEW.logdate < DATE '2008-02-01' ) THEN
+        INSERT INTO measurement_y2008m01 VALUES (NEW.*);
+    ELSE
+        RAISE EXCEPTION 'Date out of range.  Fix the measurement_insert_trigger() function!';
+    END IF;
+    RETURN NULL;
+END;
+$$
+LANGUAGE plpgsql;
+</code>
+\\
+=== 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,
+że pozwala na wykonywanie tego nieprzyjemnego zadania niemal natychmiast poprzez manipulowanie strukturą partycji, a nie poprzez fizyczne operowanie na dużej ilości danych.\\
+Najprostszym sposobem na usunięcie starych danych jest usunięcie niepotrzebnej partycji:
+<code sql>
+DROP TABLE measurement_y2006m02;
+</code>
+W ten sposób można bardzo szybko usunąc miliony rekordów, ponieważ nie ma potrzeby aby indywidualnie usuwać każdy wiersz.\\
+Innym popularnym sposobem jest usunięcie potomnej tabeli ze zbioru wszystkich partycji ale pozostawienie do niej dostępu:
+<code sql>
+ALTER TABLE measurement_y2006m02 NO INHERIT measurement;
+</code>
+Pozwala to na wykonywanie pewnych operacji na zbiorze danych, zanim zostaną one definitywnie usunięte.
+\\
+W podobnie prosty sposób możemy dodawać nowe partycje.
+<code sql>
+CREATE TABLE measurement_y2008m02 (
+    CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' )
+) INHERITS (measurement);
+</code>
+Alternatywnie, czasem wygodnie jest utworzyć nową tabelę poza zbiorem partycji i włączyć ją do owego zbioru później. To pozwala na załadowanie, sprawdzenie i modyfikację danych, zanim pojawią się w strukturze partycji:
+<code sql>
+CREATE TABLE measurement_y2008m02
+  (LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
+ALTER TABLE measurement_y2008m02 ADD CONSTRAINT y2008m02
+   CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' );
+\copy measurement_y2008m02 from 'measurement_y2008m02'
+-- possibly some other data preparation work
+ALTER TABLE measurement_y2008m02 INHERIT measurement;
+</code>
+\\
+=== Partycjonowanie i Constraint exclusion ===
+//Contsraint exclusion to technika optymalizacji zapytań dla partycjonowanych tabel// Np.\\
+<code sql>
+SET constraint_exclusion = on;
+SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
+</code>
+Bez ''constraint_exclusion'' powyższe zapytanie przeskanowałoby wszystkie partycje wchodzące w skład tabeli ''measurments''. Z włączonym parametrem planner bada ograniczenia nałożone na wszystkie partycje i stara się udowodnić, że
+dana partycja nie musi być skanowana, ponieważ nie zawiera żadnych danych spełniających warunki podane w klauzuli ''WHERE'' zapytania.\\
+Można użyć polecenia ''EXPLAIN'' aby zobaczyć różnicę w planie zapytania z włąćzonym i wyłączonym parametrem ''constraint_exclusion''.\\
+Plan niezoptymalizowanego zapytania wygląda następująco:
+<code>
+SET constraint_exclusion = off;
+EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
+                                          QUERY PLAN
+-----------------------------------------------------------------------------------------------
+ Aggregate  (cost=158.66..158.68 rows=1 width=0)
+   ->  Append  (cost=0.00..151.88 rows=2715 width=0)
+         ->  Seq Scan on measurement  (cost=0.00..30.38 rows=543 width=0)
+               Filter: (logdate >= '2008-01-01'::date)
+         ->  Seq Scan on measurement_y2006m02 measurement  (cost=0.00..30.38 rows=543 width=0)
+               Filter: (logdate >= '2008-01-01'::date)
+         ->  Seq Scan on measurement_y2006m03 measurement  (cost=0.00..30.38 rows=543 width=0)
+               Filter: (logdate >= '2008-01-01'::date)
+...
+         ->  Seq Scan on measurement_y2007m12 measurement  (cost=0.00..30.38 rows=543 width=0)
+               Filter: (logdate >= '2008-01-01'::date)
+         ->  Seq Scan on measurement_y2008m01 measurement  (cost=0.00..30.38 rows=543 width=0)
+               Filter: (logdate >= '2008-01-01'::date)
+</code>
+Aby pobrać wszystkie dane nie trzeba skanować starszych tabel.\\
+Po włączeniu ''constraint_exclusion'' otrzymamy następujący plan:
+<code>
+SET constraint_exclusion = on;
+EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
+                                          QUERY PLAN
+-----------------------------------------------------------------------------------------------
+ Aggregate  (cost=63.47..63.48 rows=1 width=0)
+   ->  Append  (cost=0.00..60.75 rows=1086 width=0)
+         ->  Seq Scan on measurement  (cost=0.00..30.38 rows=543 width=0)
+               Filter: (logdate >= '2008-01-01'::date)
+         ->  Seq Scan on measurement_y2008m01 measurement  (cost=0.00..30.38 rows=543 width=0)
+               Filter: (logdate >= '2008-01-01'::date)
+</code>
+Parametr ''constraint_exclusion'' korzysta wyłącznie z ograniczeń nałożonych przez ''CHECK''.\\
+Zaleca się włączanie parametru ''constraint_exclusion'' tylko w wypadku zapytań operujących na spartycjonowanych tabelach.
+=== Alternatywne metody partycjonowania ===
+Innym sposobem wstawianie danych do partycji jest ustanowienie reguł, np:
+<code sql>
+CREATE RULE measurement_insert_y2006m02 AS
+ON INSERT TO measurement WHERE
+    ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' )
+DO INSTEAD
+    INSERT INTO measurement_y2006m02 VALUES (NEW.*);
+...
+CREATE RULE measurement_insert_y2008m01 AS
+ON INSERT TO measurement WHERE
+    ( logdate >= DATE '2008-01-01' AND logdate < DATE '2008-02-01' )
+DO INSTEAD
+    INSERT INTO measurement_y2008m01 VALUES (NEW.*);
+</code>
+\\
+Należy pamiętać, że ''COPY'' ignoruje reguły. Jeśli chcesz korzystać z ''COPY'' do wstawiania danych musisz sam wskazać odpowiednią partycję.\\
+Inną wadą takiego podejścia jest fakt, iż trudno wymusić błąd jeśli zbiór reguł nie pokrywa całego zbioru danych. Dane zostaną w takiej sytuacji wstawione do głównej tabeli.\\
+Partycjonowanie może być również wykonane przy użyciu ''UNION ALL'', zamiast dziedziczenia.
+<code sql>
+CREATE VIEW measurement AS
+          SELECT * FROM measurement_y2006m02
+UNION ALL SELECT * FROM measurement_y2006m03
+...
+UNION ALL SELECT * FROM measurement_y2007m11
+UNION ALL SELECT * FROM measurement_y2007m12
+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>
+W przypadku pobierania danych, co jest operacją wykonywaną z reguły częściej niż wstawianie, widać zysk wynikający z zastosowania partycjonowania.
 ==== Zalety i wady partycjonowania ====
 === 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 ===
+  * 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

pl/dydaktyka/ztb/2010/projekty/partycjonowanie/start.1275337501.txt.gz · ostatnio zmienione: 2019/06/27 15:56 (edycja zewnętrzna)

Pokaż stronę Poprzednie wersje

Menadżer multimediów Do góry

AIwiki

Menu

Dla Studentów

Old specialized AI courses

SMaDA/SMaIDA/AIDA

Informatyka (EAIiIB)

Studia Dr

Inne materialy dydaktyczne

Archiwum

Dyplomanci

Geist Season of Code

HeKatE

Public

Różnice