Komenda DELETE nie wypełnia tabeli 30 000 000 wierszy

Odziedziczyłem bazę danych i chcę ją wyczyścić i przyspieszyć. Mam tabelę zawierającą 30 000 000 wierszy, z których wiele to niepotrzebne dane wstawione z powodu błędu w imieniu naszego programisty. Przed dodaniem nowych, bardziej zoptymalizowanych indeksów przekonwertowałem tabelę z MyISAM na InnoDB i chcę usunąć wiele wierszy zawierających niepotrzebne dane.

Baza danych to MySQL 5.0 i mam dostęp root do serwera. Najpierw uruchomiłem te polecenia za pomocą Administratora, a następnie phpMyAdmin, oba z tymi samymi wynikami.

Polecenie, które uruchamiam, to:

DELETE
FROM `tablename`
WHERE `columnname` LIKE '-%'

Zasadniczo usuń wszystko z tej kolumny, która zaczyna się od myślnika -.

Działa przez około 3-5 minut, a potem, gdy przeglądam listę procesów, nie ma go.

Następnie biegnę

SELECT *
FROM `tablename`
WHERE `columnname` LIKE '-%'

i zwraca miliony wierszy.

Dlaczego moja instrukcja usuwania nie jest kompletna?

PS, zdaję sobie sprawę z tego, jak nieaktualny jest MySQL 5.0. Pracuję nad przeniesieniem bazy danych do MySQL 5.6 w InnoDB (może MariaDB 10 w XtraDB), ale do tego czasu staram się odpowiedzieć na to pytanie w wersji DB w obecnej postaci.

-

Edycja usunięta, zobacz moją odpowiedź.

Proszę spojrzeć na architekturę InnoDB (zdjęcie z CTO Percona Vadim Tkachenko)

Usuwane wiersze są zapisywane w dziennikach cofania. Plik ibdata1 powinien teraz rosnąć na czas usuwania. Według mysqlperformanceblog.comReasons for run-away main Innodb Tablespace :

• Wiele zmian transakcyjnych
• Bardzo długie transakcje
• Opóźniony wątek oczyszczania

W twoim przypadku powód nr 1 zajmowałby jeden segment wycofania wraz z częścią cofania, ponieważ usuwasz wiersze. Te wiersze muszą znajdować się w ibdata1, aż do zakończenia usuwania. To miejsce jest logicznie odrzucane, ale przestrzeń dyskowa nie zmniejsza się.

Musisz teraz zabić to usunięcie. Gdy zabijesz zapytanie usuwania, przywróci ono usunięte wiersze.

Robisz to zamiast tego:

CREATE TABLE tablename_new LIKE tablename;
INSERT INTO tablename_new SELECT * FROM tablename WHERE `columnname` NOT LIKE '-%';
RENAME TABLE
    tablename TO tablename_old,
    tablename_new TO tablename
;
DROP TABLE tablename_old;

Najpierw mogłeś to zrobić przeciwko wersji tabeli MyISAM. Następnie przekonwertuj go na InnoDB.

Myślę, że mogliśmy nadmiernie skomplikować odpowiedź, która była wymagana w moim przypadku . Nie mam wątpliwości, że zarówno Roland, jak i Rick James mają rację, tworząc tymczasową tabelę, wstrzykując tylko wiersze, które przechodzą przez filtr, NOT LIKE '-%'ale rozwiązanie było dla mnie „łatwiejsze”, ponieważ wystąpił ważny błąd, którego nie znałem do tej pory i dla przepraszam.

Uruchomiłem zapytanie w mysqlinteraktywnym pytaniu i zauważyłem komunikat o błędzie,

mysql> DELETE FROM `slugs` WHERE `slug` LIKE '-%';
ERROR 1206 (HY000): The total number of locks exceeds the lock table size

Przez Googleing błąd, znalazłem rozwiązanie polegające na zwiększeniu innodb_buffer_pool_sizepoprzez /etc/my.cnfplik i zrestartowaniu demona mysql. Dla mojego serwera został ustawiony na domyślny 8Mi zwiększyłem go do 1G(serwer ma 32 GB i jest to jedyna tabela, która jest obecnie InnoDB).

mysql> DELETE FROM `slugs` WHERE `slug` LIKE '-%';
Query OK, 23517226 rows affected (27 min 33.23 sec)

Potem mogłem uruchomić polecenie i usunąć 23 miliony rekordów w ~ 27 minut.

Dla tych, którzy innodb_buffer_pool_sizepowinni wiedzieć, co należy ustawić, zwróć uwagę na ilość pamięci RAM, a następnie spójrz na ten wątek, który podaje sugerowane oszacowanie w GB.

Sugestię Rolanda można przyspieszyć, wykonując obie rzeczy jednocześnie:

CREATE TABLE tablename_new LIKE tablename;
ALTER TABLE tablename_new ENGINE = InnoDB;
INSERT INTO tablename_new 
    SELECT * FROM tablename WHERE `columnname` NOT LIKE '-%' ORDER BY primary_key;
RENAME TABLE
    tablename TO tablename_old,
    tablename_new TO tablename
;
DROP TABLE tablename_old;

Ale tutaj jest blog, który wyjaśnia, jak robić duże DELETE w kawałkach, zamiast pozornie trwać wiecznie: http://mysql.rjweb.org/doc.php/deletebig Istotą jest przejście przez tabelę za pomocą PK, wykonując 1K wiersze na raz. (Oczywiście jest więcej szczegółów, o których należy pamiętać.)

I ten blog dotyczy potencjalnych gotów w konwersji do InnoDB: http://mysql.rjweb.org/doc.php/myisam2innodb

Moim pierwszym instynktem byłoby zrobienie wielu mniejszych operacji usuwania przez ograniczenie liczby wyników zapytania i wielokrotne uruchomienie zapytania:

DELETE
FROM `tablename`
WHERE `columnname` LIKE '-%' LIMIT 1000000

Najłatwiejszym rozwiązaniem jest po prostu tego nie robić - wykonaj mniejsze usuwanie, które można łatwiej przetworzyć.

W takim przypadku zaleciłbym spróbowanie sekwencyjnego usuwania formularza:

DELETE
FROM `tablename`
WHERE `columnname` LIKE '-a%'

Może mógłbyś zrobić coś takiego:

• Dodaj nowe pole o nazwie deleted.
• Wykonaj aktualizację jak UPDATE tablename SET deleted=1 WHERE `columnname` LIKE '-a%'.
• Ustaw, cronaby usunąć to w nocy.