Partycjonowanie polega na fizycznym podzieleniu tabel w bazie danych na osobne pliki. Mechanizm ten jest zupełnie transparentny dla użytkownika. Oznacza to, że na tabeli można wykonywać dokładnie te same operacje co na zwykłej tabli, uwzględniając transakcje.

Dzięki partycjonowaniu można znacznie przyspieszyć operacje na bardzo dużych tabelach.

Kiedy używać partycjonowania?

Kiedy chcemy przyspieszyć pojedyncze operacje INSERT i SELECT
Kiedy chcemy przyspieszyć operacje SELECT operujące na zakresach (np. od - do)
Kiedy chcemy fizycznie podzielić tabelę między różne partycje lub dyski twarde
Kiedy chcemy wydajnie przechowywać dane historyczne
Kiedy tabele są bardzo duże
Kiedy indeksy przekraczają rozmiar pamięci RAM

Partycjonowanie w MySQL

Mechanizm partycjonowania dostępny jest w MySQL od wersji 5.1

Zagadnienia techniczne

Partycje można tworzyć poleceniem

CREATE TABLE

lub

ALTER TABLE

Składnia:

CREATE TABLE <tableName> (<columns>)
ENGINE=<engineName>
PARTITION BY <type> ( <partitionExpression> );

<type> może być: RANGE, LIST, HASH lub KEY

Klucze

Wszystkie kolumny użyte w wyrażeniu PARTITION muszą być częściami wszystkich kuczy PRIMARY i UNIQUE.

MyISAM

Każda partycja przechowywana jest w pliku .MYD i .MYI
Można ustawić ścieżki DATA DIRECTORY i INDEX DIRECTORY

InnoDB

Bez ustawionego parametru innodb_file_per_table, tabele są przechowywane w domyślnej przestrzeni
W przeciwnym wypadku, dla każdej tabeli, tworzony jest plik .ibd

Metody partycjonowania

Dostępne są 4 metody partycjonowania: RANGE, LIST, HASH i KEY.

RANGE, LIST i HASH mogą używać tylko funkcji, które zwracają typ INTEGER. Dozwolone funkcje:

ABS()
MINUTE()
CEILING()
MOD()
DAY()
MONTH()
DAYOFMONTH()
QUARTER()
DAYOFWEEK()
SECOND()
DAYOFYEAR()
TIME_TO_SEC()
DATEDIFF()
TO_DAYS()
EXTRACT()
WEEKDAY()
FLOOR()
YEAR()
HOUR()
YEARWEEK()
MICROSECOND()

Przykład RANGE:

CREATE TABLE employee (
  employee_id INTEGER AUTO_INCREMENT,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  store_id TINYINT,
  PRIMARY KEY (employee_id )
) ENGINE=MyISAM
PARTITION BY RANGE (employee_id) (
PARTITION p0 VALUES LESS THAN (10000),
PARTITION p1 VALUES LESS THAN (20000),
PARTITION p2 VALUES LESS THAN (30000),
PARTITION p3 VALUES LESS THAN (40000),
PARTITION p4 VALUES LESS THAN MAXVALUE)

Parametr MAXVALUE jest opcjonalny.

Zakresy wartości muszą być podane w kolejności rosnącej.

Przykład LIST:

CREATE TABLE employee (
  employee_id INT,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  store_id TINYINT
) ENGINE=MyISAM
PARTITION BY LIST (store_id) (
PARTITION pNorth VALUES IN (2,8,12),
PARTITION pEast VALUES IN (1,4,7),
PARTITION pWest VALUES IN (3,5,6,10),
PARTITION pSouth VALUES IN (9,11)
)

Przykład HASH:

CREATE TABLE employee (
  employee_id INT AUTO_INCREMENT,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  store_id TINYINT,
PRIMARY KEY (employee_id)
) ENGINE=MyISAM
PARTITION BY HASH (employee_id)
PARTITIONS 4;

Wyrażenie w funkcji HASH musi zwracać typ INTEGER.

Przykład KEY:

CREATE TABLE employee (
  employee_id INT,
  first_name VARCHAR(50),
  last_lname VARCHAR(50),
  store_id TINYINT
) ENGINE=MyISAM
PARTITION BY KEY (last_name)
PARTITIONS 4;

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

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)
);

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ę:

PARTITION BY RANGE(YEAR(from_date))

Wykonujemy zapytanie:

SELECT COUNT(*) FROM salaries WHERE YEAR(from_date) = 1998;

Czas wykonania: 2.25 sec

Drugie zapytanie:

SELECT COUNT(*) FROM salaries WHERE from_date BETWEEN '1998-01-01' AND '1998-12-31';

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

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

cpu family      : 6
model           : 8
model name      : Pentium III (Coppermine)
stepping        : 3
cpu MHz         : 731.297
cache size      : 256 KB

Pamięć

MemTotal:       255372 kB

System operacyjny

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))

PHP

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]

MySQL

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

Struktura tabeli z partycjonowaniem - silnik MyISAM

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`) )
ENGINE = MyISAM 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)
);

Struktura tabeli bez partycjonowania - silnik MyISAM

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`, `date`) )
ENGINE = MyISAM;

Struktura tabeli z partycjonowaniem - silnik innoDB

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)
);

Struktura tabeli bez partycjonowania - silnik innoDB

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;

Dodawanie danych

Skrypt testujący

$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);

Wyniki

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

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

$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);

Wyniki

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

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

CHECK ( x = 1 )
CHECK ( county IN ( 'Oxfordshire', 'Buckinghamshire', 'Warwickshire' ))
CHECK ( outletID >= 100 AND outletID < 200 )

Należy upewnić się, że nałożone ograniczenia gwarantują unikalność kluczy w poszczególnych partycjach.
Częstym błędem jest:

CHECK ( outletID BETWEEN 100 AND 200 )
CHECK ( outletID BETWEEN 200 AND 300 )

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 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:

CREATE TABLE measurement (
    region_id       INT NOT NULL,
    logdate         DATE NOT NULL,
    peaktemp        INT,
);

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:

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);

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:

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);

4. Potrzebujemy indeksów na kolumnach kluczy:

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);

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:

CREATE OR REPLACE FUNCTION measurement_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO measurement_y2008m01 VALUES (NEW.*);
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

Po utworzeniu funkcji tworzymy trigger uruchamiający funkcję:

CREATE TRIGGER insert_measurement_trigger
    BEFORE INSERT ON measurement
    FOR EACH ROW EXECUTE PROCEDURE measurement_insert_trigger();

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:

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;

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:

DROP TABLE measurement_y2006m02;

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:

ALTER TABLE measurement_y2006m02 NO INHERIT measurement;

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.

CREATE TABLE measurement_y2008m02 (
    CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' )
) INHERITS (measurement);

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:

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;

Partycjonowanie i Constraint exclusion

Contsraint exclusion to technika optymalizacji zapytań dla partycjonowanych tabel Np.

SET constraint_exclusion = ON;
SELECT COUNT(*) FROM measurement WHERE logdate >= DATE '2008-01-01';

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:

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)

Aby pobrać wszystkie dane nie trzeba skanować starszych tabel.
Po włączeniu constraint_exclusion otrzymamy następujący plan:

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)

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:

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.*);

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.

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;

Testy wydajnościowe

Sprzęt

Procesor:	Intel Core 2 Duo e6750@3200MHz
RAM:		4GB DDR II

System operacyjny:

Linux Gentoo 2.6.33 x86_64

Postgres:

Postgresql-8.4.4

Struktura niepartycjonowanej tabeli:

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);

Struktura partycjonowanej tabeli:

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);

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);

Funkcje i triggery opisane powyżej:

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;

CREATE TRIGGER insert_measurement_trigger
    BEFORE INSERT ON logs_partitioned
    FOR EACH ROW EXECUTE PROCEDURE measurement_insert_trigger();

Procedura testowa
Do celów testowych napisana została prosta aplikacja w Javie.
Najpierw do bazy wprowadzono dużą ilość danych (1000000 rekordów).
Wyniki:

Non-partitioned		859.245s
Partitioned		921.565

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

Non-partitioned		77.442s
Partitioned		8.636s

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.txt · ostatnio zmienione: 2019/06/27 15:50 (edycja zewnętrzna)

Pokaż źródło strony 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

Spis treści

Partycjonowanie dużych zbiorów danych

Czym jest partycjonowanie?

Kiedy używać partycjonowania?

Partycjonowanie w MySQL

Zagadnienia techniczne

Klucze

MyISAM

InnoDB

Metody partycjonowania

Przykłady poprawnego podejścia do partycjonowania danych

Testy wydajnościowe na dużych zbiorach danych

Założenia

Sprzęt

Struktura tabeli z partycjonowaniem - silnik MyISAM

Struktura tabeli bez partycjonowania - silnik MyISAM

Struktura tabeli z partycjonowaniem - silnik innoDB

Struktura tabeli bez partycjonowania - silnik innoDB

Dodawanie danych

pobieranie danych

Partycjonowanie w PostreSQL

Implementacja partycjonowania

Zarządzanie partycjami

Partycjonowanie i Constraint exclusion

Alternatywne metody partycjonowania

Testy wydajnościowe

Zalety i wady partycjonowania

Zalety

Wady

Bibliografia