--- pl:dydaktyka:ztb:2010:projekty:partycjonowanie:start [2010/06/01 21:59]
ztb2010
+++ pl:dydaktyka:ztb:2010:projekty:partycjonowanie:start [2019/06/27 15:50] (aktualna)
@@ Linia 14: / Linia 14: @@
   * Kiedy chcemy wydajnie przechowywać dane historyczne\\
   * 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
@@ Linia 184: / Linia 185: @@
 === 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 ==
@@ Linia 191: / Linia 249: @@
   `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))(
 PARTITION p01 VALUES LESS THAN
@@ Linia 231: / Linia 288: @@
   `date` DATETIME NOT NULL ,
   `info` TEXT NOT NULL ,
-  PRIMARY KEY (`log_id`, `date`) ,
+  PRIMARY KEY (`log_id`, `date`) )
-  INDEX `date` (`date` ASC) )
 ENGINE = MyISAM;
 </code>
@@ Linia 242: / Linia 298: @@
   `date` DATETIME 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))(
 PARTITION p01 VALUES LESS THAN
@@ Linia 282: / Linia 337: @@
   `date` DATETIME NOT NULL ,
   `info` TEXT NOT NULL ,
-  PRIMARY KEY (`log_id`, `date`) ,
+  PRIMARY KEY (`log_id`, `date`) )
-  INDEX `date` (`date` ASC) )
 ENGINE = innoDB;
 </code>
@@ Linia 289: / Linia 343: @@
 == Dodawanie danych ==
 ** Skrypt testujący **
 <code php>
+$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 **
 <code php>
-NIE DZIALA WKLEJANIE KODU!!!
+$link = mysql_connect('localhost', 'user', 'pass');
+if (!$link) {
+    die('Could not connect: ' . mysql_error());
+}
+mysql_select_db("partitioning", $link);
+$time1 = 0;
+$time2 = 0;
+$time3 = 0;
+$time4 = 0;
+$n = 1000;
+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>
-=== Partycjonowanie w PostreSQL  ===
+Tabele spartycjonowane są dużo szybsze **(ponad 10x)** od tabel bez partycjonowania.
-== 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:
@@ Linia 322: / 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ę)
   * 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
 Np. Załóżmy, że w naszej tabeli gromadzone są pomiary temperatury każdego dnia w różnych regionach świata.\\ \\
@@ Linia 425: / Linia 591: @@
 </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,
 ż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 457: / Linia 623: @@
 </code>
 \\
-== Partycjonowanie i Constraint exclusion ==
+=== Partycjonowanie i Constraint exclusion ===
 //Contsraint exclusion to technika optymalizacji zapytań dla partycjonowanych tabel// Np.\\
 <code sql>
@@ Linia 507: / Linia 673: @@
 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:
@@ Linia 537: / Linia 703: @@
 UNION ALL SELECT * FROM measurement_y2008m01;
 </code>
+=== Testy wydajnościowe ===
-==== Zaawansowane możliwości partycjonowania ====
+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>
-=== Sub-partitioning ===
+Struktura niepartycjonowanej tabeli:
-==== Zalety i wady partycjonowania ====
+<code sql>
+CREATE TABLE logs_not_partitioned
+(
+  LOG_ID        BIGINT             NOT NULL,
+  DATE 	 DATE 	NOT NULL,
+  INFO 	 TEXT	NOT NULL
+);
-=== Zalety ===
+ALTER TABLE logs_not_partitioned ADD CONSTRAINT PK_LOG_T PRIMARY KEY (LOG_ID);
+</code>
-=== Wady ===
+Struktura partycjonowanej tabeli:
-<code php>
-$link = mysql_connect("localhost", "root", "pass");
-if (!$link) {
-    die("Could not connect: " . mysql_error());
-}
-mysql_select_db("partitioning", $link);
+<code sql>
+CREATE TABLE logs_partitioned
+(
+  LOG_ID        BIGINT             NOT NULL,
+  DATE 	 DATE 	NOT NULL,
+  INFO 	 TEXT	NOT NULL
+);
-mysql_query("TRUNCATE TABLE `logs_partitioned_myisam`");
+ALTER TABLE logs_partitioned ADD CONSTRAINT PK_LOGP_T PRIMARY KEY (LOG_ID);
-mysql_query("TRUNCATE TABLE `logs_partitioned_innodb`");
+</code>
-mysql_query("TRUNCATE TABLE `logs_nonpartitioned_innodb`");
-mysql_query("TRUNCATE TABLE `logs_nonpartitioned_myisam`");
-$time1 = 0;
-$time2 = 0;
-$time3 = 0;
-$time4 = 0;
-$n = 1000000;
+<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>
-for($i=0; $i<$n; $i++){
+Funkcje i triggery opisane powyżej:
-        $date = "2010-".rand(1,12)."-".rand(1,28)." ".rand(0,23).":".rand(0,59).":".rand(0,59);
+<code sql>
-        $text = md5(uniqid(rand(1,132456), true));
+CREATE OR REPLACE FUNCTION measurement_insert_trigger()
-        $start1 = microtime(true);
+RETURNS TRIGGER AS $$
-        mysql_query("INSERT INTO `logs_partitioned_myisam` (date, info) VALUES ("".$date."", "".$text."")");
+BEGIN
-        $stop1 = microtime(true);
+    IF (NEW.date = DATE '2010-06-03') THEN
-        $start2 = microtime(true);
+        INSERT INTO measurement_y2010m06d03 VALUES (NEW.*);
-        mysql_query("INSERT INTO `logs_nonpartitioned_myisam` (date, info) VALUES ("".$date."", "".$text."")");
+    ELSIF (NEW.date = DATE '2010-06-04') THEN
-        $stop2 = microtime(true);
+        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>
-        $start3 = microtime(true);
+<code sql>
-        mysql_query("INSERT INTO `logs_partitioned_innodb` (date, info) VALUES ("".$date."", "".$text."")");
+CREATE TRIGGER insert_measurement_trigger
-        $stop3 = microtime(true);
+    BEFORE INSERT ON logs_partitioned
-        $start4 = microtime(true);
+    FOR EACH ROW EXECUTE PROCEDURE measurement_insert_trigger();
-        mysql_query("INSERT INTO `logs_nonpartitioned_innodb` (date, info) VALUES ("".$date."", "".$text."")");
+</code>
-        $stop4 = microtime(true);
+**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. \\ \\
-        $time1 += ($stop1-$start1);
+Za pomocą tej samej aplikacji pobrane zostały wprowadzone wcześniej dane:\\
-        $time2 += ($stop2-$start2);
+Wyniki
-        $time3 += ($stop3-$start3);
+<code>
-        $time4 += ($stop4-$start4);
+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
-mysql_close($link);
-</code>

pl/dydaktyka/ztb/2010/projekty/partycjonowanie/start.1275422376.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