[singollo]

mam taką tabelkę error_log, na silniku InnoDB, z 5 indeksami (unikalne: ErrorLogID int primary i ErrorCode varchar(100) + 3 inne, każdy na pojedynczej kolumnie) Mam w niej 12 milionów rekordów, i w efekcie ta tabelka to jakieś 50% dziennego backupu. Chciałbym wyrzucić z niej wszystko poza wpisami z ostatnich 30 dni.
No i tu jest problem, bo to strasznie długo trwa. Kasowanie ok 1000 najstarszych rekordów trwa prawie minutę.
Macie jakiś pomysł na przyspieszenie tego procesu? Czy uzyskam coś usuwając indeksy, a później tworząc po ograniczeniu ilości rekordów?
#mysql #bazydanych #sql

[G.....n]

@singollo: Po pierwsze, rzuć wynik explain.
Po drugie - spróbuj dorzucić indeks na datę.
Po trzecie - daj więcej RAMu w ustawieniach bazy.

[singollo]

@Ginden: explain robię oczywiście na select

mysql> explain select * from error_log where ErrorTime < '2014-01-01' limit 1000\G
*************************** 1. row ***************************
 id: 1
 select_type: SIMPLE
 table: error_log
 type: range
possible_keys: index2
 key: index2
 key_len: 8
 ref: NULL
 rows: 3049470
 Extra: Using where
1 row in set (0.00 sec)

Po drugie, mam taki indeks.
Po trzecie... dla vhosta z bazą mam przydzielone 2GB, i nie mam skąd wziąć więcej.

[ksiak]

@singollo: jak nie masz tam żadnych kluczy obcych porobionych to proponuje:

create table error_log_new like error_log;

insert into error_log_new (ErrorLogID, ErrorCode, ErrorTime, ....) select ErrorLogID, ErrorCode, ErrorTime, ... from error_log WHERE ErrorTime >= '2014-01-01';

drop table error_log;

alter table error_log_new rename to error_log;
powinno byc szybciej ;)
Przy okazji, która wersja MySQL?

[G.....n]

@singollo: SELECT robi się szybko, DELETE nie - są jakieś triggery, klucze obce?

@ksiak: Esencja #mysql - DELETE jest zbyt wolny? Zróbmy kopię tabeli i podmieńmy.

[ksiak]

@Ginden: a myślisz, że w innych systemach jest inaczej? Zwykle danych z bazy się nie usuwa od razu a tylko oznacza do usunięcia. Następnie są odpowiednie mechanizmy które robią to w innym terminie - gdy system nie jest obciążony. Tak są skonstruowane bazy danych i zbyt wiele na to nie poradzisz.

[ksiak]

@singollo: możesz jeszcze usuwać w partiach po hmm 10k rekordów (zależnie jak baza zareaguje) jeżeli nie chcesz robić opcji recreate && drop.

[plushy]

@singollo: Wybierz rekordy z ostatnich 30 dni do innej tabeli, wyrzuć starą, zmień nazwę nowej. Ewentualnie zamiast dropować to użyj truncate.

[singollo]

@Ginden: nic z tych rzeczy, ale pojedyncze rekordy mogą być dość duże (4 pola TEXT)
@ksiak: chyba zrobię to "your way". Ma tylko taką wadę, że choć rozwiązuje problem, to nie zbliża mnie do rozwiązania.

[G.....n]

@plushy: A co jak dojdą nowe wiersze pomiędzy zmianami?

[ksiak]

@singollo: dlatego proponuje opcję drugą, czyli kasowanie po +/-10k rekordów (musisz sprawdzić ile będzie ok). Jak już to zrobisz, to proponuje dodać zadanie do Event Schedulera, żeby Ci co noc usuwał starsze niż 30 dni, wtedy będziesz miał w miarę ok ;)

[ksiak]

@Ginden: to jest tabela error logów, znając życie nie wymaga się od niej by dane siedzące w środku były ciągłe w 100% ale oczywiście ten przypadek może być inny.

[singollo]

@ksiak: @Ginden: na taką okazję to zrobię sobie 60 sekundowy downtime gdzieś w środku nocy. Klient to jakoś przeżyje ;)

[plushy]

@Ginden: To będą miały pecha, stosując MySQL zamiast innych rozwiązań trzeba się z czymś takim liczyć.

[ksiak]

@plushy: które inne rozwiązania nie mają problemu z usunięciem 3mln rekordów z tabeli? Aaa i weź pod uwagę, że to jest vhost.

[000loki]

@singollo: a to ma być regularny proces który ma być odpalany raz na miesiąc czy tylko jednorazowo?

[singollo]

@000loki: Tu i teraz interesuje mnie jednorazowe przycięcie tabeli. To są dane z ostatnich 4 lat, mam je w backupach, serwisowi "live" są zupełnie zbędne. Za rok mogę powtórzyć operację ;)

[000loki]

@singollo: no to przerzut do innej tabeli danych tych co potrzebujesz. Drop tamtej i zmiana nazwy. Jak chcesz za rok mieć mniejszy problem to możesz zrobić partycje co miesiąc (pod warunkiem, że nic innego w tym nie przeszkadza). Jesli boisz się ze coś tam wpadnie w trakcie tej operacji to tabele zwyczajnie zalockuj na czas operacji. Procesy które ewentualnie będą chciały coś tam robić będą musiały poczekać.

[singollo]

@000loki: partycje mi tu nic nie zmienią, bo ja chcę się tych danych fizycznie pozbyć. Przez tą tabelę backupy rozrosły się do zupełnie bezsensownych rozmiarów (> 1.5GB po kompresji) co generuje kolejne problemy (typu "gdzie to kurde trzymać" ;))

[000loki]

@singollo: no to jak nic nie dadzą. Robisz w takim przypadku drop partycji które sięgają dalej niż miesiąc wstecz. Jest to proces na parę sekund. Nie martwisz się ze coś się wydarzy w trakcie. Drop partycji i nara. Możesz nawet zrobić proces który regularnie raz na miesiąc będzie dropowal ileś tam partycji z wcześniejszą data.

[singollo]

@000loki: no w zasadzie masz rację. Ale myślę, że jak już przytnę tabelę za pierwszym razem, to dalsza kontrola objętości będzie znacznie prostsza