Partycjonowanie dużych zbiorów danych
Czym jest partycjonowanie?
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
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.
-
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:
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.
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);
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);
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