Czy wiele indeksów w tabeli MYSQL powoduje powolne AKTUALIZACJE i WSTAWKI?

Dec 10 2020

Wydajność mojej witryny (stos LAMP) znacznie spadła w ciągu ostatnich kilku dni, pomimo braku aktualizacji kodu. Wygląda na to, że tylko wstawianie, aktualizowanie i usuwanie określonej tabeli MySQL powoduje problem. Każda strona, która aktualizuje, wstawia lub usuwa wpis w „tabeli” zadań, zajmuje około 10 sekund. (EG UPDATE jobs SET title = 'sdfldsfjlk' WHERE job_id = 134324)

SELECT zapytania wydają się być wykonywane jak poprzednio, chociaż wydają się być wolniejsze, jeśli aktualizacja odbywa się w tym samym czasie.

Tabela zawiera około 180 000 wpisów. Zauważyłem w widoku PHPMyAdmin, że oprócz „normalnego” indeksu w polu podstawowym, w polu „entry_date” (patrz obrazek) znajduje się indeks. Czy to może być problem w tym przypadku? Nie mam pojęcia, dlaczego zostałby utworzony indeks na tym polu.

Jeśli nie, co jeszcze może być źródłem problemu? Sprawdziłem miejsce na dysku, które wydaje się OK. (Dostępne 7 GB) zgodnie z df.

SHOW CREATE TABLE job\G

Create Table: CREATE TABLE `job` (
    `job_id` int(11) NOT NULL AUTO_INCREMENT, 
    `user_id` int(11) NOT NULL DEFAULT '0', 
    `entry_date` date NOT NULL DEFAULT '0000-00-00', 
    `timescale` varchar(20) COLLATE latin1_german2_ci NOT NULL DEFAULT '0000-00-00', 
    `title` varchar(60) COLLATE latin1_german2_ci NOT NULL, 
    `description` text COLLATE latin1_german2_ci NOT NULL, 
    `start_date` varchar(60) COLLATE latin1_german2_ci NOT NULL, 
    `address_town` varchar(40) COLLATE latin1_german2_ci NOT NULL DEFAULT '', 
    `address_county` varchar(40) COLLATE latin1_german2_ci NOT NULL DEFAULT '', 
    `postcode1` varchar(4) COLLATE latin1_german2_ci NOT NULL DEFAULT '', 
    `postcode2` char(3) COLLATE latin1_german2_ci NOT NULL DEFAULT '', 
    `status` tinyint(4) NOT NULL DEFAULT '0', 
    `cat_id` int(4) NOT NULL DEFAULT '0', 
    `price` decimal(4,2) NOT NULL DEFAULT '1.00', 
    `emailcount` smallint(5) NOT NULL DEFAULT '-1', 
    `emailcount2` int(11) NOT NULL DEFAULT '-1', 
    `recemailcount` int(11) NOT NULL DEFAULT '-1', 
    `archive` tinyint(4) NOT NULL DEFAULT '0', 
    `post_url` varchar(100) COLLATE latin1_german2_ci NOT NULL, 
    PRIMARY KEY (`job_id`), 
    KEY `entrydatejob_id` (`entry_date`,`job_id`)
) ENGINE=MyISAM AUTO_INCREMENT=235844
           DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci

AKTUALIZACJA - Po pierwsze dziękuję wszystkim współpracownikom. Bardzo to doceniam. Tak więc w ciągu ostatnich kilku dni problem ustał, co jeszcze bardziej utrudniło znalezienie przyczyny. Ale teraz wydaje się, że znowu powrócił. Zacznę od podania typu maszyny hostowanej w Google Cloud: g1-small (1 vCPU, 1,7 GB pamięci). Będę kontynuował aktualizację o dalsze informacje, o które proszą komentujący.

Odpowiedzi

6 ypercubeᵀᴹ Dec 10 2020 at 19:22

Aby odpowiedzieć na konkretne pytanie:

Nie , dodatkowy indeks na (entry_date)lub dwóch nie zaszkodziłby wydajności aktualizacji innej titlekolumny.

Dodatkowo:

Nie , wersja MySQL 5.6 nie jest zbyt stara, nawet jeśli brakuje niektórych nowoczesnych funkcji (takich jak funkcje okien). Powinieneś mieć przyzwoitą wydajność i przyzwoity sprzęt.

Możemy tylko spekulować na temat omawianego problemu, ale co może być nie tak lub wyjaśnić:

stary, nieefektywny sprzęt: sprawdź specyfikację dysku, zmierz jego wydajność.
pofragmentowany indeks / tabela. Sprawdź dokumentację MySQL i stare pytania / odpowiedzi tutaj, jak defragmentować tabele MyISAM (OPTYMALIZACJA TABELI).

Nie mniej ważny:

Twój stół korzysta z silnika MyISAM , co jest starą wiadomością. InnODB zastąpił go wiele lat temu jako domyślny silnik MySQL. Nie ma aktywnego rozwoju tego silnika. Brakuje kilku funkcji w porównaniu do InnoDB (transakcje, ograniczenia klucza obcego itp.) I wydajności w większości obciążeń roboczych . Zdecydowanie sugeruję zmianę tabeli (po przetestowaniu oczywiście, że twoje aplikacje i procedury nie psują się) na korzystanie z InnoDB.
Co ważne, aby twoje pytanie, InnoDB może wykonywać SELECTi UPDATEw tym samym czasie (zazwyczaj). MyISAM całkowicie blokuje stół; aktualizacja lub wybór muszą zakończyć się przed rozpoczęciem wybierania lub aktualizacji.
Przełączając się z MyISAM na InnoDB, pamiętaj o dostosowaniu key_buffer_sizei innodb_buffer_pool_size.

3 J.D. Dec 10 2020 at 19:20

Indeksy nieznacznie wpływają na wydajność operacji INSERT i DELETE, ale generalnie jest to pomijalne, zwłaszcza jeśli indeksy są dobrze zaplanowane i nie przesadzisz, umieszczając 30 indeksów w tej samej tabeli, każdy z 30 różnymi kombinacjami pól. (Generalnie trzymam się wskazówki 5 na 5 - maksymalnie 5 indeksów, maksymalnie 5 pól na indeks. Oczywiście jest to tylko wskazówka, a nie twarda reguła).

Biorąc to pod uwagę, indeksy są faktycznie używane do zwiększania wydajności zapytań UPDATE i SELECT, ponieważ indeks jest używany do lokalizowania wierszy do UPDATE lub SELECT.

Drastyczna zmiana, którą widzisz, jest wątpliwa, aby była powiązana z indeksem entry_date, który odkryłeś (czy wiesz, czy został on niedawno dodany, czy też istniał już przed zmianą wydajności?).

Dwie rzeczy, na które powinieneś zwrócić uwagę, to:

Jeśli poprzednie indeksy dla twoich zapytań są nadal używane, szczególnie dla zapytań UPDATE i SELECT (i jeśli są one wyszukiwane lub operacja skanowania ma teraz miejsce). Może się to zmienić z powodu zmian w statystykach danych tabeli w czasie. Możesz użyć instrukcji ANALYZE , aby sprawdzić statystyki tabeli.
Inną rzeczą, na którą możesz zwrócić uwagę, jest fragmentacja indeksu , która jest naturalnym zjawiskiem w czasie. Dzieje się tak, gdy do tabeli jest dodawanych więcej danych. (Generalnie nie powinno to stanowić problemu w przypadku małego stolika takiego jak Twój, ale i tak warto to sprawdzić.)

Będę nadal aktualizować swoją odpowiedź, dodając więcej rzeczy do rozważenia, gdy o nich pomyślę. Uruchomienie EXPLAIN na swoich zapytaniach może również pomóc ci znaleźć wskazówkę w sprawie. Jest próbą oddzielenia instrukcji UPDATE i INSERT, które działają wolno, i spróbuj zrozumieć, czy ma na nie ta sama główna przyczyna (ponieważ znowu działają one odwrotnie niż indeksy, INSERT są pomijalnie wolniejsze, ale UPDATE zwykle korzystają z prawidłowego indeksu i powinno być szybsze.)