Powody sporadycznie powolnych zapytań?

Używamy MySQL 5.1 na Windows Server 2008 R2.

Ostatnio przeprowadziliśmy diagnostykę naszej bazy danych i znaleźliśmy niepokojące artefakty, których nie możemy wyjaśnić . Dodaliśmy trochę kodu do rejestrowania, gdy mieliśmy zapytania, które zajmowały dużo czasu (> 2000 ms). Wyniki były zaskakujące (i być może wyjaśnienie naszych impasów).

Czasami zapytania, które zwykle zajmują bardzo mało czasu (<10 ms), zajmują od 4 do 13 sekund. Dla jasności, są to zapytania, które są uruchamiane stale (kilka razy na sekundę) i nie cierpią z powodu tych skoków czasu zapytań.

Przeszukaliśmy nasze indeksy w poszukiwaniu oczywistych błędów i nie mieliśmy dużo szczęścia.

Aktualizacja

Tabela osób:

| people | CREATE TABLE `people` (
`people_id` bigint(20) NOT NULL AUTO_INCREMENT,
`company_id` bigint(20) NOT NULL,
`name` varchar(255) DEFAULT NULL,
`password` varchar(255) DEFAULT NULL,
`temp_password` varchar(10) DEFAULT NULL,
`reset_password_hash` varchar(255) DEFAULT NULL,
`email` varchar(255) DEFAULT NULL,
`phone` varchar(32) DEFAULT NULL,
`mobile` varchar(32) DEFAULT NULL,
`iphone_device_id` varchar(160) DEFAULT NULL,
`iphone_device_time` datetime DEFAULT NULL,
`last_checkin` datetime DEFAULT NULL,
`location_lat` double DEFAULT NULL,
`location_long` double DEFAULT NULL,
`gps_strength` smallint(6) DEFAULT NULL,
`picture_blob_id` bigint(20) DEFAULT NULL,
`authority` int(11) NOT NULL DEFAULT '0',
`active` tinyint(1) NOT NULL DEFAULT '1',
`date_created` datetime NOT NULL,
`last_login` datetime NOT NULL,
`panic_mode` tinyint(1) NOT NULL DEFAULT '0',
`battery_level` double DEFAULT NULL,
`battery_state` varchar(32) DEFAULT NULL,
PRIMARY KEY (`people_id`),
KEY `email` (`email`),
KEY `company_id` (`company_id`),
KEY `iphone_device_id` (`iphone_device_id`),
KEY `picture_blob_id` (`picture_blob_id`),
CONSTRAINT `people_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `companies` (`company_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `people_ibfk_2` FOREIGN KEY (`picture_blob_id`) REFERENCES `blobs` (`blob_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4658 DEFAULT CHARSET=utf8 |

Indeksy:

+--------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table  | Non_unique | Key_name         | Seq_in_index | Column_name      | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+
| people |          0 | PRIMARY          |            1 | people_id        | A         |        3502 |     NULL | NULL   |      | BTREE      |         |
| people |          1 | email            |            1 | email            | A         |        3502 |     NULL | NULL   | YES  | BTREE      |         |
| people |          1 | company_id       |            1 | company_id       | A         |        3502 |     NULL | NULL   |      | BTREE      |         |
| people |          1 | iphone_device_id |            1 | iphone_device_id | A         |        3502 |     NULL | NULL   | YES  | BTREE      |         |
| people |          1 | picture_blob_id  |            1 | picture_blob_id  | A         |        3502 |     NULL | NULL   | YES  | BTREE      |         |
+--------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+

mamy ~ 5000 wierszy w tabeli na serwerze, który sprawia nam problemy.

Zapytania UPDATE w poprzednich dwóch pytaniach ( Pytanie 1 , Pytanie 2 ) uderzają w tabelę „ludzie” według KLUCZA PODSTAWOWEGO z blokowaniem na poziomie wiersza. Oto, co powiedziałem w pytaniu 1 z 6 czerwca 2011 10:03

Wszystkie transakcje dotyczą klucza PODSTAWOWEGO. Ponieważ PODSTAWOWY jest indeksem klastrowym w InnoDB, klucz PODSTAWOWY i sam wiersz są razem. Zatem przemierzanie rzędu i KLUCZ PODSTAWOWY są jednym i tym samym. Dlatego każda blokada indeksu na KLUCZU PODSTAWOWYM jest również blokadą na poziomie wiersza.

Nie rozważono jeszcze czegoś innego, co może przypisywać powolność indeksom: Zastosowanie indeksów NIEJEDNOCZESNYCH w InnoDB. Każde indeksowane wyszukiwanie w InnoDB przy użyciu nieunikalnych indeksów ma również identyfikator wiersza każdego wiersza dołączony do klucza nieunikalnego. RowID zasadniczo eminuje z indeksu klastrowanego . Aktualizowanie nieunikalnych indeksów MUSI ZAWSZE oddziaływać na indeks klastrowany NAWET JEŚLI TABELA NIE MA PODSTAWOWEGO KLUCZA.

Inną rzeczą do przemyślenia jest proces zarządzania węzłami BTREE w indeksie. Czasami wymaga podziału strony węzłów. Wszystkie wpisy w węźle BTREE indeksów nieunikalnych zawierają pola nieunikalne PLUS identyfikator rowID w indeksie klastrowym. Aby właściwie złagodzić podział takich stron BTREE bez zakłócania integralności danych, wiersz powiązany z rowID musi wewnętrznie zostać zablokowany na poziomie wiersza.

Jeśli tabela „ludzie” ma wiele nieunikalnych indeksów, przygotuj się na dużą liczbę stron indeksu w obszarze tabel, a także od czasu do czasu zakrywających cię małymi blokadami wierszy.

Jest jeszcze jeden czynnik, który nie jest tak oczywisty: Kluczowa populacja

Czasami, gdy indeks jest zapełniany, kluczowe wartości tworzące indeksy mogą z czasem zostać zniekształcone i spowodować, że MySQL Query Optimizer przełączy się z wyszukiwania kluczowego, na skanowanie indeksów, a na koniec na pełne skanowanie tabeli. Nie możesz tego kontrolować, chyba że przeprojektujesz tabelę za pomocą nowych indeksów, aby skompensować krzywe klawisze. Podaj strukturę tabeli dla tabeli „ludzie”, liczbę tabel „osób” oraz dane wyjściowe dotyczące indeksów show dla tabeli „osoby” .

Nawet jeśli zapytania używają tylko KLUCZA PIERWOTNEGO, przekrzywienie kluczy w nieindywidualnych indeksach nadal wymaga równoważenia BTREE i podziału strony. Takie zarządzanie BTREE spowoduje znaczące spowolnienie z powodu sporadycznych blokad na poziomie wierszy, których nie zamierzałeś.

AKTUALIZACJA 14.06.2011 22:19

Pytania od pytania 1

UPDATE people SET company_id = 1610, name = '<name>', password = '<hash>',
temp_password = NULL, reset_password_hash = NULL, email = '<redacted>@yahoo.com',
phone = NULL, mobile = '<phone>', iphone_device_id = 'android:<id>-<id>',
iphone_device_time = '2011-06-06 05:35:09', last_checkin = '2011-06-06 05:24:42',
location_lat = <lat>, location_long = -<lng>, gps_strength = 3296,
picture_blob_id = 1190,
authority = 1, active = 1, date_created = '2011-04-13 20:21:20',
last_login = '2011-06-06 05:35:09', panic_mode = 0,
battery_level = NULL, battery_state = NULL WHERE people_id = 3125

UPDATE people SET company_id = 1610, name = '<name>', password = '<hash>',
temp_password = NULL, reset_password_hash = NULL, email = '<redacted>@yahoo.com',
phone = NULL, mobile = '<phone>', iphone_device_id = 'android:<id>-<id>-<id>-<id>',
iphone_device_time = '2011-06-06 05:24:42', last_checkin = '2011-06-06 05:35:07',
location_lat = <lat>, location_long = -<lng>, gps_strength = 3296,
picture_blob_id = 1190,
authority = 1, active = 1, date_created = '2011-04-13 20:21:20',
last_login = '2011-06-06 05:35:09', panic_mode = 0,
battery_level = NULL, battery_state = NULL WHERE people_id = 3125

Wyobraź sekwencję zdarzeń

1. Znajdź wiersz według klucza podstawowego
2. Zablokuj wiersz i indeks klastrowany
3. Utwórz dane MVCC dla wszystkich aktualizowanych kolumn
4. Indeksowane są cztery kolumny (adres e-mail, id_firmy, id_urządzenia iphone, id_blobu_obrazu)
5. Każdy indeks wymaga zarządzania BTREE
6. W obrębie tej samej przestrzeni transakcji kroki 1-5 próbują zostać powtórzone w tym samym wierszu, aktualizując te same kolumny (wyślij to samo w obu zapytaniach, nazwa_obsługi taka sama w obu zapytaniach, picture_blob_id taka sama w obu zapytaniach, iphone_device_id różne)

Pytania od pytania 2

UPDATE people SET iphone_device_id=NULL
WHERE iphone_device_id='iphone:<device_id_blah>' AND people_id<>666;

UPDATE people SET company_id = 444, name = 'Dad', password = '<pass>',
temp_password = NULL, reset_password_hash = NULL, email = '<redacted>@gmail.com',
phone = NULL, mobile = NULL, iphone_device_id = 'iphone:<device_id_blah>',
iphone_device_time = '2011-06-06 19:12:29', last_checkin = '2011-06-07 02:49:47',
location_lat = <lat>, location_long = <lng>, gps_strength = 66,
picture_blob_id = 1661,
authority = 1, active = 1, date_created = '2011-03-20 19:18:34',
last_login = '2011-06-07 11:15:01', panic_mode = 0, battery_level = 0.55,
battery_state = 'unplugged' WHERE people_id = 666;

Te dwa zapytania są jeszcze bardziej mylące, ponieważ pierwsze zapytanie aktualizuje wszystko oprócz people_id 666. Setki wierszy są boleśnie blokowane tylko pierwszym zapytaniem. Drugim zapytaniem jest aktualizacja people_id 666 uruchamiający 5 sekwencji zdarzeń. Pierwsze zapytanie uruchamia te same 5 sekwencji zdarzeń w każdym zaangażowanym wierszu, z wyjątkiem people_id 666, ale indeks dla iphone_device_id jest na kursie interecept z dwoma różnymi zapytaniami. Ktoś musi zablokować strony BTREE według kolejności zgłoszeń.

W obliczu tych dwóch par zapytań na kursie kolizyjnym zablokowanie tych samych stron BTREE w ramach jednego indeksu może być kłopotliwe dla InnoDB lub dowolnego RDBMS zgodnego z ACID. Dlatego spowolnienie indeksów jest przeznaczeniem tych par zapytań, chyba że możesz zagwarantować, że zapytania będą działały z AUTOCOMMIT = 1 lub zezwalając na nieczytelne odczyty (chociaż takie kolizje sprawiają, że ODCZYTYWANIE ZOBOWIĄZANIA i ODCZYTYWANIE NIEZGODNE są koszmarem dla MVCC).

AKTUALIZACJA 15.06.2011 10:29

@RedBlueThing: W zapytaniach z pytania 2 pierwsze zapytanie jest zapytaniem o zakres, więc uzyskuje się wiele blokad wierszy. Zauważ również, że oba zapytania próbują zablokować tę samą przestrzeń o numerze 0 strona nr 4611 n bity 152 są blokowane w kluczu podstawowym, czyli indeksie klastrowym.

Aby mieć pewność, że aplikacja działa przynajmniej w oparciu o oczekiwaną serię zdarzeń, możesz wypróbować dwie różne opcje:

Opcja 1) Konwertuj tę tabelę na MyISAM (przynajmniej na serwerze programistycznym). Każda AKTUALIZACJA, WSTAW i USUŃ nałoży blokadę pełnego stołu na zasadzie „kto pierwszy, ten lepszy”.

Opcja 2) Spróbuj użyć poziomu izolacji SERIALIZABLE . Spowoduje to zablokowanie wszystkich zamierzonych wierszy w trybie UDOSTĘPNIANIA.

Sekwencja zdarzeń, której się spodziewasz, ulegnie awarii lub zakończy się powodzeniem przy użyciu tych dwóch alternatywnych opcji. Jeśli obie te opcje zawiodą, musisz przejrzeć swoją aplikację i ustalić kolejność wykonywania zapytań. Po ustaleniu tego priorytetu możesz po prostu cofnąć te opcje (w przypadku opcji 1 wróć do InnoDB, w przypadku opcji 2 wróć do domyślnego poziomu izolacji [przestań używać SERIALIZABLE]).

POKAŻ ZMIENNE JAK 'innodb%'; - W szczególności, jeśli dane i indeksy po prostu nie osiągnęły wielkości puli buforów, możesz uderzać o dysk znacznie mocniej niż wcześniej. I / O jest wielkim zabójcą wydajności.

Większość pól jest dwa razy większa niż potrzeba. BIGINT (8 bajtów) to zdecydowanie przesada w przypadku większości identyfikatorów. 5000 wierszy potrzebuje tylko SMALLINT UNSIGNED (limit 65 KB, tylko 2 bajty). Lub użyj MEDIUMINT dla marginesu bezpieczeństwa.

DOUBLE daje 16 cyfr znaczących przy koszcie 8 bajtów. Czy poziom naładowania baterii ma więcej niż 2 znaczące cyfry precyzji? FLOAT zajmuje 4 bajty.

Chodzi mi o to, że „mniejszy -> bardziej buforowalny -> szybszy”.

Pokaż nam powolne zapytania; przynajmniej niektóre z tych, które nagle stały się wolniejsze. Możemy tylko zgadywać bez nich. Włącz slowlog i ustaw long_query_time = 1; te pomogą znaleźć najwolniejsze zapytania.

Czy rozumiesz zalety indeksów „złożonych”?