Jakiś czas temu przyszło mi się zmierzyć z problemem optymalizacji zapytań na bardzo dużej ilości danych w tabeli. Tabela, o której pisze, ma wielkość około 40GB i 80 milionów rekordów. Oto jej schemat:
CREATE TABLE `service_numbers` (
`template_id` bigint(20) NOT NULL,
`template_info_id` bigint(20) NOT NULL,
`mobile_number` varchar(255) DEFAULT NULL,
`appeared_at` timestamp NULL DEFAULT NULL,
`verified_at` timestamp NULL DEFAULT NULL,
`alias_name` varchar(255) NOT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
`fake_id` int(11) NOT NULL AUTO_INCREMENT,
`external_operator_id` int(11) DEFAULT NULL,
`areacode` varchar(3) DEFAULT NULL,
PRIMARY KEY (`fake_id`),
KEY `idx_mn_ti_tii` (`mobile_number`(191),`template_id`,`template_info_id`),
KEY `sn_by_template_then_appeared` (`template_id`,`template_info_id`,`appeared_at`),
KEY `sn_by_template_then_verified` (`template_id`,`template_info_id`,`verified_at`),
KEY `sn_by_tmeplate_alias_appeared` (`template_id`,`alias_name`(191),`template_info_id`,`appeared_at`),
KEY `ssn_tid_arec_tiid_vat_idx` (`template_id`,`areacode`,`template_info_id`,`verified_at`),
KEY `ssn_tid_oid_tiid_vat_idx` (`template_id`,`external_operator_id`,`template_info_id`,`verified_at`),
KEY `idx_tmeplate_alias_verified` (`template_id`,`alias_name`(64),`template_info_id`,`verified_at`)
) ENGINE=InnoDB AUTO_INCREMENT=184473055 DEFAULT CHARSET=utf8mb4;
Zapytanie, które wymaga optymalizacji ma następującą konstrukcję:
select count(*) from service_numbers where template_id = ID1 and template_info_id = ID2 and verified_at < 'DATE';
Może przyjmować różnego rodzaju dodatkowe warunki, dlatego dla każdego rodzaju zapytania dodano indexy. Zawsze jednak głównym elementem jest tutaj kolumna template_id. Aby pomóc przyspieszyć wykonywanie tych zapytań, dobrze jest usunąć historyczne dane, aby zmniejszyć ich ilość. Jednak ze względu na specyfikę wykorzystania tych danych, nie jest to proste. Ale to w tym momencie pomijam. Tabela została uszczuplona na tyle na ile się da, ale nadal jest ona ogromna. Ale co jeśli wykorzystamy partycjonowanie?
Co to jest partycjonowanie?
Partycjonowanie jest fizycznym dzieleniem tabeli na części, do których jednak nie masz bezpośredniego dostępu (nie ma możliwości wykonania SELECT'a tylko na wskazanej partycji, ale możesz np. wskazać, którą partycję wyczyścić). Każda partycja jest zarządzana przez engine bazodanowy, ma własne indexy, które zostały zdefiniowane na tabeli.
Ważne informacje o partycjonowaniu w MySQLu
Najpierw sprawdźmy, czy nasz serwer wspiera partycjonowanie - oto jeden ze sposobów:
mysql (root@partition_test)> SHOW VARIABLES LIKE '%partition%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| have_partitioning | YES |
+-------------------+-------+
1 row in set (0.00 sec)
Jak traktowane są operacji bazodanowych:
- SELECT - Otwierana jest warstwa partycjonowania i lokowane są wszystkie partycje. Optymalizator decyduje, które z partycji są ignorowane, bo warunki selektu ich nie dotyczą. Wtedy warstwa partycjonowania wywołuje API do silnika bazodanowego, który zarządza partycjami.
mysql (root@partition_test)> explain partitions select * from test1;
+----+-------------+-------+-------------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | test1 | p0,p1,p2,p3 | ALL | NULL | NULL | NULL | NULL | 4 | |
+----+-------------+-------+-------------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)
mysql (root@partition_test)> explain partitions select * from test1 where tid = 1;
+----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+-------------+
| 1 | SIMPLE | test1 | p0 | ref | idx_tid_tnid_c | idx_tid_tnid_c | 5 | const | 1 | Using where |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+-------------+
1 row in set (0.07 sec)
- INSERT - Otwierana jest warstwa partycjonowania i lokowane są wszystkie partycje, a następnie określa, która partycja ma otrzymać nowy wiersz. Później przekazuje ten wiersz do tej partycji.
- DELETE - Otwierana jest warstwa partycjonowania i lokowane są wszystkie partycje. Określa gdzie znajduje się wiersz i wysyła żądanie jego usunięcia do tej partycji.
- UPDATE - Otwierana jest warstwa partycjonowania i lokowane są wszystkie partycje aby określić gdzie znajduje się szukany wiersz. Jest on pobierany i modyfikowany. Określane jest gdzie po modyfikacji ma zostać zlokalizowany 'nowy' wiersz, następnie jest tam wysyłany. Do partycji źródłowej wiersza jest wysyłane żądanie usunięcia.
Oczywiście operacje te mogą być wspierane przez mechanizm nazywany
pruning. Czyli w momencie gdy wykonywana jest operacja, tylko kilku lub jednej partycji, to na reszcie z nich jest ignorowana lock.
W jakich przypadkach najlepiej używać partycjonowania?
- tabela jest tak duża, że nie mieści się już pamięci lub gdy najważniejsze dane są samym końcu tabelki, a sama tabela posiada bardzo dużo starych danych
- utrzymanie tabel partycjonowanych jest o wiele łatwiejsze np. przy usuwaniu danych historycznych i odzyskiwaniu wolnej przestrzeni dyskowej
- może być użyte przy zapobieganiu niektórych specyficznych obciążeniach, takich jak muteksy na indexach InnoDB
- możliwy jest backup i ponowne załadowanie pojedynczej partycji dla bardzo dużej ilości danych
- partycje mogą być rozłożone fizycznie na różnych dyskach
Ograniczenia:
- maksymalna ilość partycji to 1,024 dla pojedynczej tabeli
- dla MySQL v5.1 wyrażenie potrzebne do partycjonowania danych musi zwrócić wartość typu integer. Od wersji 5.5 możemy partycjonować także poprzez kolumny, które nie są tego typu
- każdy klucz główny i index unikalny musi uwzględniać wszystkie kolumny w wyrażeniu partycjonującym
- nie ma możliwości użycia kluczy obcych
- lista funkcji, których można używać do wyrażenia partycjonującego jest dostępna pod tą stroną http://dev.mysql.com/doc/refman/5.5/en/partitioning-limitations-functions.html
Typy partycjonowania:
- RANGE - dla każdej partycji zdefiniowane jest wyrażenie, które zwraca wartość typu integer:
mysql (root@partition_test)> create table test_range (`id` int(11) NOT NULL, `ms` varchar(255) DEFAULT NULL, `tid` int(11) DEFAULT NULL, `tnid` int(11) DEFAULT NULL, `c` varchar(255) DEFAULT NULL) PARTITION BY RANGE (tnid) (PARTITION p0 VALUES LESS THAN (2), PARTITION p1 VALUES LESS THAN (3), PARTITION p2 VALUES LESS THAN (9), PARTITION p3 VALUES LESS THAN (10), PARTITION p4 VALUES LESS THAN MAXVALUE);
Query OK, 0 rows affected (0.96 sec)
mysql (root@partition_test)> show create table test_range \G
*************************** 1. row ***************************
Table: test_range
Create Table: CREATE TABLE `test_range` (
`id` int(11) NOT NULL,
`ms` varchar(255) DEFAULT NULL,
`tid` int(11) DEFAULT NULL,
`tnid` int(11) DEFAULT NULL,
`c` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (tnid)
(PARTITION p0 VALUES LESS THAN (2) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (3) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (9) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (10) ENGINE = InnoDB,
PARTITION p4 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.00 sec)
W powyższym przykładzie, w partycji p0 będę umieszczone wszystkie wiersze gdzie wartość w kolumnie tnid jest mniejsze od 2, a w partycji p1 wszystkie wiersze gdzie kolumna tnid przyjmuje wartości równe 2 i mniejsze od 3.
- LIST - jest bardzo podobne do powyższego sposobu partycjonowania - każda partycja musi być zdefiniowana. Różnica wynika z tego, że wyrażenie definiujące partycję jest listą wartości typu integer. Powyższą tabelę możemy zdefiniować następująco:
mysql (root@partition_test)> create table test_list (`id` int(11) NOT NULL, `ms` varchar(255) DEFAULT NULL, `tid` int(11) DEFAULT NULL, `tnid` int(11) DEFAULT NULL, `c` varchar(255) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 PARTITION BY LIST (tnid) (PARTITION p0 VALUES IN (0,1), PARTITION p1 VALUES IN (2), PARTITION p2 VALUES IN (3,4,5,6,7,8), PARTITION p3 VALUES IN (9), PARTITION p4 VALUES IN (10,11,12,13));
Query OK, 0 rows affected (0.56 sec)
mysql (root@partition_test)> show create table test_list \G
*************************** 1. row ***************************
Table: test_list
Create Table: CREATE TABLE `test_list` (
`id` int(11) NOT NULL,
`ms` varchar(255) DEFAULT NULL,
`tid` int(11) DEFAULT NULL,
`tnid` int(11) DEFAULT NULL,
`c` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY LIST (tnid)
(PARTITION p0 VALUES IN (0,1) ENGINE = InnoDB,
PARTITION p1 VALUES IN (2) ENGINE = InnoDB,
PARTITION p2 VALUES IN (3,4,5,6,7,8) ENGINE = InnoDB,
PARTITION p3 VALUES IN (9) ENGINE = InnoDB,
PARTITION p4 VALUES IN (10,11,12,13) ENGINE = InnoDB) */
1 row in set (0.00 sec)
Znając strukturę danych jesteśmy świadomi jakie dane będą przechowywane w kolumnie tnid (Wartości typu integer od 0 do 13 - nie ma możliwości aby wstawić wiersz, dla którego kolumn tnid będzie przyjmować inne wartość, a także i NULL. Czytaj dalej.).
- COLUMNS - jest nowym typem partycjonowania od wersji 5.5. Może być stosowana nie tylko dla kolumn typu integer. Wyróżniamy dwa sposoby podziału: RANGE lub LIST aby zwrócić wartości przedziałów lub listy. Dodatkowo w porównaniu do swoich podstawowych typów partycjonowania (COLUMN RANGE -> RANGE i COLUMN LIST -> LIST) możliwe jest zdefiniowanie podziału tabeli przez wiele kolumn. Link do dokumentacji: http://dev.mysql.com/doc/refman/5.5/en/partitioning-columns.html
- HASH - używane głównie do równomiernego rozkładania wierszy w partycji między z góry określoną liczbą ścieżek. Wyrażenie definiująca partycję musi zwracać wartość typu integer. Przykład definicji tak partycjonowanej tabeli:
CREATE TABLE t1 (c1 INT, c2 VARCHAR(255), c3 TIMESTAMP)
PARTITION BY HASH( MONTH(c3))
PARTITIONS 4;
Tabela t1 będzie miała 4 partycje gdzie dane będą rozlokowane przez następujący wzór: MOD(MONTH(c3),4)
- KEY - bardzo podobna metoda do HASH, ale tutaj to MySQL narzuca wyrażenie partycjonujące przy pomocy funkcji hashującej.
- Subpartitiong (composite partitiong czyli złożone partycjonowanie) - jest dalszym podziałem partycji na jeszcze mniejsze partycje. Zainteresowanych odsyłam do dokumentacji: http://dev.mysql.com/doc/refman/5.5/en/partitioning-subpartitions.html
- Wartość NULL nie są wartościami typu integer (http://db-diary.blogspot.com/2013/12/wartosc-null-w-bazach-danych.html). Każdy tym partycjonowania może inaczej traktować wartości NULL i niektóre z nich muszą być specjalnie przygotowane na tą ewentualność wartości danych.
Dla partycjonowania typu RANGE wiersz jest ustawiany w pierwszej (najniżej) partycji. W ten sam sposób zostanie potraktowany wiersz dla partycji zdefiniowanej przy pomocy wyrażenia z funkcją SQL. Dla typu LIST partycja musi mieć zdefiniowaną wartość NULL aby można było wstawić do niej wiersz (UWAGA: ta sama zasada odnosi się także do innych 'poprawny' wartości). Dla powyższej tabeli test_list spróbujemy wstawić wiersz, którego wartość nie istnieje w liście wartości partycjonujących:
mysql (root@partition_test)> insert into test_list (id, ms, tid, tnid, c) values (1, '11111', 22, 14, 'sfsfdfa');
ERROR 1526 (HY000): Table has no partition for value 14
mysql (root@partition_test)> insert into test_list (id, ms, tid, tnid, c) values (1, '11111', 22, NULL, 'sfsfdfa');
ERROR 1526 (HY000): Table has no partition for value NULL
Teraz jak już wiemy mniej więcej na czym partycjonowanie polega, porównajmy czasy wykonania kilku zapytań dla przedstawionej na samym początku tabeli
service_numbers (baza danych:
test) i nowej tabeli
service_numbers (baza danych:
test2), która została przeze mnie partycjonowania:
mysql (root@test2)> show create table service_numbers \G
*************************** 1. row ***************************
Table: service_numbers
Create Table: CREATE TABLE `service_numbers` (
`template_id` bigint(20) NOT NULL,
`template_info_id` bigint(20) NOT NULL,
`mobile_number` varchar(255) DEFAULT NULL,
`appeared_at` timestamp NULL DEFAULT NULL,
`verified_at` timestamp NULL DEFAULT NULL,
`alias_name` varchar(255) NOT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
`fake_id` int(11) NOT NULL AUTO_INCREMENT,
`external_operator_id` int(11) DEFAULT NULL,
`areacode` varchar(3) DEFAULT NULL,
KEY `idx_mn_ti_tii` (`mobile_number`(191),`template_id`,`template_info_id`),
KEY `sn_by_template_then_appeared` (`template_id`,`template_info_id`,`appeared_at`),
KEY `sn_by_template_then_verified` (`template_id`,`template_info_id`,`verified_at`),
KEY `sn_by_tmeplate_alias_appeared` (`template_id`,`alias_name`(191),`template_info_id`,`appeared_at`),
KEY `ssn_tid_arec_tiid_vat_idx` (`template_id`,`areacode`,`template_info_id`,`verified_at`),
KEY `ssn_tid_oid_tiid_vat_idx` (`template_id`,`external_operator_id`,`template_info_id`,`verified_at`),
KEY `idx_tmeplate_alias_verified` (`template_id`,`alias_name`(64),`template_info_id`,`verified_at`),
KEY `idx_fake_id` (`fake_id`)
) ENGINE=InnoDB AUTO_INCREMENT=184435432 DEFAULT CHARSET=utf8mb4
/*!50100 PARTITION BY KEY (template_id)
PARTITIONS 100 */
1 row in set (0.00 sec)
Mała uwaga do zmienionej struktury tabeli: Pamiętajmy o ograniczeniach przy zmianie schematu tabeli, gdy mamy indexy unikalne lub klucz główny. W moim przypadku musiałam usunąć klucz główny i zamienić go na index, a kolumnę, na której działał zmodyfikować. Te operację wymagają czasu i przestrzeni dyskowej, dlatego lepiej dokonywać zmiany schematu tabeli na pustej tabeli (przed rozpoczęciem oczywiście exportujemy dane do pliku):
truncate table service_numbers;
alter table service_numbers MODIFY fake_id int(11) NOT NULL;
alter table service_numbers drop primary key;
alter table service_numbers add index idx_fake_id (fake_id);
alter table service_numbers MODIFY fake_id int(11) NOT NULL auto_increment;
alter table service_numbers PARTITION BY KEY(template_id) PARTITIONS 100;
Testujemy nasze partycjonowanie
Przykładowe zapytanie na nie zmienionym schemacie tabeli:
mysql (root@test)> select count(*) from service_numbers where template_id = 6401 and template_info_id = 10 and verified_at < '2013-05-01';
+----------+
| count(*) |
+----------+
| 608136 |
+----------+
1 row in set (11.32 sec)
mysql (root@test)> explain partitions select count(*) from service_numbers where template_id = 6401 and template_info_id = 10 and verified_at < '2013-05-01' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: service_numbers
partitions: NULL
type: ref
possible_keys: sn_by_template_then_appeared,sn_by_template_then_verified,sn_by_tmeplate_alias_appeared,ssn_tid_arec_tiid_vat_idx,ssn_tid_oid_tiid_vat_idx,idx_tmeplate_alias_verified
key: ssn_tid_oid_tiid_vat_idx
key_len: 8
ref: const
rows: 1227206
Extra: Using where; Using index
1 row in set (0.00 sec)
Nie wiem czy zwróciliście uwagę, ale optymalizator wybrał 'gorszy' index do wykonania tego zapytania. Poprawmy jego statystyki i wykonajmy explain ponownie:
mysql (root@test)> analyze table service_numbers;
+----------------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+----------------------+---------+----------+----------+
| test.service_numbers | analyze | status | OK |
+----------------------+---------+----------+----------+
1 row in set (1.39 sec)
mysql (root@test)> explain partitions select count(*) from service_numbers where template_id = 6401 and template_info_id = 10 and verified_at < '2013-05-01' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: service_numbers
partitions: NULL
type: range
possible_keys: sn_by_template_then_appeared,sn_by_template_then_verified,sn_by_tmeplate_alias_appeared,ssn_tid_arec_tiid_vat_idx,ssn_tid_oid_tiid_vat_idx,idx_tmeplate_alias_verified
key: sn_by_template_then_verified
key_len: 21
ref: NULL
rows: 922120
Extra: Using where; Using index
1 row in set (0.02 sec)
Tym razem jednak było znacznie lepiej. I czas wykonania zapytania jest lepszy:
mysql (root@test)> select count(*) from service_numbers where template_id = 6401 and template_info_id = 10 and verified_at < '2013-05-01';
+----------+
| count(*) |
+----------+
| 608136 |
+----------+
1 row in set (3.96 sec)
To samo zapytanie wykonajmy na tabeli ze zmienionym schematem:
mysql (root@test2)> select count(*) from service_numbers where template_id = 6401 and template_info_id = 10 and verified_at < '2013-05-01';
+----------+
| count(*) |
+----------+
| 608136 |
+----------+
1 row in set (2.26 sec)
mysql (root@test2)> explain partitions select count(*) from service_numbers where template_id = 6401 and template_info_id = 10 and verified_at < '2013-05-01' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: service_numbers
partitions: p3
type: range
possible_keys: sn_by_template_then_appeared,sn_by_template_then_verified,sn_by_tmeplate_alias_appeared,ssn_tid_arec_tiid_vat_idx,ssn_tid_oid_tiid_vat_idx,idx_tmeplate_alias_verified
key: sn_by_template_then_verified
key_len: 21
ref: NULL
rows: 454688
Extra: Using where; Using index
1 row in set (0.00 sec)
Ilość wierszy, które optymalizator musi odczytać jest o wiele mniejsza, więc i zapytanie trwa o wiele krócej.
To chyba jednak mało wiarogodny test na lepsze działanie. Dlatego przygotowałam plik z ponad 600 zapytaniami, które na serwerze produkcyjnym złapały się do slow logów. Mój serwer testowy miał włączone slow logi, które teraz chce wam przedstawić (Wykresy zostały wygenerowane przy pomocy narzędzia:
mysql-slow-query-log-visualizer, jeśli ktoś jest zainteresowany).
Czas trwania wykonywania wszystkich zapytań dla schematu niepartycjonowanego:
a dla schematu partycjonowanego:
Podsumowanie tego testu:
| Niepartycjonowany schemat tabeli | Partycjonowany schemat tabeli |
| Ilość zapytań 'złapanych' w slow logach: | 632 | 429 |
| Czas działania testu: | + 5 godz | + 1 godz |
| Czas najdłużej wykonanego zapytania: | 399 sekundy | 31 sekundy |
| Czas wykonania zapytania, które musiało 'przeczyta' największą ilość wierszy (2662877): | 85 sekundy | 25 sekundy |
| Dodatkowe informacje: | Optymalizator bierze pod uwagę gorsze indexy | - |
No to tyle. Dla mnie okazało się to wybawieniem i w końcu rozwiązaniem problemu optymalizacji działania zapytań na tak dużej tabeli.
