ClickHouse rindu dzēšana: vieglais DELETE, mutācijas vai DROP PARTITION

Kad jādzēš rindas ClickHouse, SQL izskatās pazīstams, bet dzinējs zem tā nav. MergeTree tabulas glabā datus nemainīgās daļās, tāpēc "dzēšana" vai nu paslēpj rindas un gaida, kamēr sapludināšana tās iztīrīs, vai pārraksta veselas daļas uz diska. Mazā tabulā atšķirību gandrīz nemana. Tabulā ar miljardiem rindu izvēle nosaka, vai dzēšana beidzas sekundēs, ilgst dienu vai iestrēgst un bloķē katru nākamo izmaiņu.

Trīs dzēšanas veidi, no lētākā līdz dārgākajam

1. Dzēst veselas particijas (vai izpildīt TRUNCATE tabulai), ja dzēšamie dati sakrīt ar particiju atslēgu. Tas izņem daļas kā failus, neko nepārrakstot.
2. Vieglais DELETE FROM mērķtiecīgai dzēšanai. Tas atzīmē rindas kā dzēstas un ļauj vēlākām sapludināšanām tās izņemt.
3. ALTER TABLE ... DELETE, klasiska mutācija, kas pārraksta katru daļu ar atbilstošām rindām.

Pati DELETE dokumentācija norāda uz pirmo variantu: biežai lielapjoma dzēšanai tabulā ar piemērotu particiju atslēgu ALTER TABLE ... DROP PARTITION ir ātrāks.

Vieglais DELETE: kas notiek patiesībā

-- vieglā dzēšana: atzīmē rindas, fiziski tās izņem vēlākas sapludināšanas
DELETE FROM events WHERE user_id = 42;

-- ierobežojiet ar vienu particiju (nereplicētam MergeTree jānorāda tieši)
DELETE FROM events IN PARTITION '202611' WHERE user_id = 42;

Vieglais DELETE joprojām ir mutācija, bet lēta. ClickHouse skartajām daļām pievieno slēptu kolonnu _row_exists un izpilda dzēšanu kā šīs kolonnas atjaunināšanu uz 0. SELECT vaicājumi tiek pārrakstīti tā, lai izlaistu rindas, kur tā ir false. Platajās (wide) daļās tiek rakstīta tikai šī viena kolonna; kompaktās daļas tiek pārrakstītas pilnībā.

Kas jāzina, pirms uz to paļauties:

• Dati kādu laiku paliek uz diska. Rindas fiziski izņem tikai vēlākas sapludināšanas, tāpēc vieta neatbrīvojas uzreiz. Ja vajag paredzamu tīrīšanu, dokumentācija iesaka tabulas iestatījumu min_age_to_force_merge_seconds vai klasisko ALTER TABLE ... DELETE.
• Pēc noklusējuma komanda gaida, līdz rindas ir atzīmētas. lightweight_deletes_sync noklusējums ir 2, kas pašreizējā pirmkodā nozīmē gaidīt visas replikas; 0 atgriežas uzreiz, un rindas var palikt redzamas, līdz fona mutācija beidzas.
• Projekcijas to bloķē. Pēc noklusējuma DELETE neizdodas tabulām ar projekcijām; MergeTree iestatījums lightweight_mutation_projection_mode (noklusējums throw) var to mainīt uz drop vai rebuild.
• Lasīšana maksā cenu. Dokumentācija brīdina, ka liela apjoma dzēšana šādā veidā var pasliktināt SELECT veiktspēju, jo vaicājumi turpina filtrēt maskētās rindas, līdz sapludināšana tās izņem.
• Particiju atlase. ReplicatedMergeTree gadījumā nosacījumi uz particiju atslēgu WHERE daļā tiek automātiski izmantoti, lai skartu tikai atbilstošās particijas (optimize_mutations_with_partition_pruning, pēc noklusējuma ieslēgts). Nereplicētam MergeTree IN PARTITION jāpievieno pašam.

ALTER TABLE DELETE: pārrakstīšana maskā

-- klasiskā mutācija: pārraksta katru skarto daļu
ALTER TABLE events DELETE WHERE event_date < '2024-01-01';

-- veciem datiem parasti daudz lētāk: dzēsiet veselas particijas
ALTER TABLE events DROP PARTITION '202312';

Mutāciju dokumentācija skaidri saka, kā tas darbojas: mutācijas MergeTree tabulās tiek izpildītas, pārrakstot veselas datu daļas. Daļa ar vienu atbilstošu rindu no piecdesmit miljoniem tiek uzrakstīta no jauna pilnībā. Dažas sekas:

• Nav atomitātes un atcelšanas. Daļas tiek aizstātas ar mutētām, tiklīdz katra ir gatava, tāpēc vaicājums mutācijas laikā redz sajaukumu. Kad mutācija iesniegta, to nevar atritināt.
• Tā pārdzīvo restartus. Iesniegta mutācija turpinās pēc servera restarta, tāpēc "restartēt un cerēt" to neatceļ.
• Ievietošana netiek bloķēta. Rindas, kas ievietotas pirms mutācijas iesniegšanas, tiek skartas; vēlāk ievietotās netiek.
• Stingra secība. Mutācijas katrai daļai tiek piemērotas izveides secībā. Lēna mutācija aiztur katru nākamo mutāciju tajā pašā tabulā, arī vēlākas vieglās dzēšanas.
• Pēc noklusējuma asinhroni. mutations_sync noklusējums ir 0: ALTER atgriežas uzreiz, un darbs notiek fonā. 1 gaida pašreizējā serverī, 2 visās replikās, 3 tikai aktīvajās replikās.

Mutāciju uzraudzība un apturēšana

Katrai mutācijai ir rinda tabulā system.mutations:

SELECT database, table, mutation_id, command, create_time,
       parts_to_do, is_done, latest_failed_part, latest_fail_reason
FROM system.mutations
WHERE is_done = 0
ORDER BY create_time;

-- atceliet iestrēgušu mutāciju (jau pārrakstītās daļas paliek pārrakstītas)
KILL MUTATION WHERE database = 'default' AND table = 'events'
  AND mutation_id = 'mutation_3.txt';

parts_to_do rāda, cik daļu vēl gaida. latest_fail_reason ir pirmā vieta, kur skatīties, ja mutācija nevirzās: bieži iemesli ir izteiksme, kas dažām rindām neizdodas, vai diska vietas vai atmiņas trūkums pārrakstīšanai. Ņemiet vērā, ka replicētām tabulām parts_to_do = 0 ne vienmēr nozīmē, ka mutācija ir pabeigta, jo ilga ievietošana vēl var radīt daļu, kas jāmutē.

KILL MUTATION atceļ iestrēgušu mutāciju, bet jau veiktās izmaiņas netiek atritinātas. Pārrakstītās daļas paliek pārrakstītas; pārējās saglabā vecās rindas. Pēc dzēšanas atcelšanas tabula ir daļēji izdzēstā stāvoklī, un jāizlemj, vai iesniegt laboto komandu atkārtoti.

Pabeigtās mutācijas sarakstā tiek glabātas līdz finished_mutations_to_keep (MergeTree iestatījumos pēc noklusējuma 100). Replicētām tabulām mutāciju rinda atrodas Keeper, tāpēc, ja Keeper nav pieejams, apstājas arī mutācijas; šo atkarību skaidrojām rakstā table is in readonly mode.

Lielas tabulas: kas mainās

• Diska vieta. Pārrakstīšana uzraksta katras skartās daļas jaunu versiju, pirms vecā tiek izņemta. Ja jūsu WHERE nesakrīt ar particiju atslēgu, var tikt skartas visas daļas, un jūs faktiski pārrakstāt visu tabulu. Salīdziniet brīvo vietu ar skarto particiju izmēru, nevis ar dzēšamo rindu izmēru.
• Fona pūls. Mutācijas konkurē ar sapludināšanu par tiem pašiem fona pavedieniem, CPU un disku. Liela pārrakstīšana palēnina sapludināšanu, un tabulā ar intensīvu ievietošanu tas var novest pie Too many parts.
• Mutāciju ierobežojumi. MergeTree iestatījumi mākslīgi palēnina mutācijas, kad tabulai ir 500 nepabeigtu mutāciju (number_of_mutations_to_delay), un pie 1000 noraida jaunas ar "Too many mutations" (number_of_mutations_to_throw). GDPR uzdevums, kas sūta vienu DELETE uz katru lietotāju, to var sasniegt ātri. Apvienojiet ID vienā komandā ar IN.
• Replikas. Mutācijas tiek koordinētas caur Keeper, un ar mutations_sync = 0 jūsu klients nezina, kad pēdējā replika ir beigusi. Datiem, kuriem jābūt dzēstiem, gaidiet ar 2 vai pārbaudiet system.mutations katrā replikā.
• Vieglais nav bezmaksas lielā mērogā. Atzīmējot lielu daļu lielas tabulas kā dzēstu, vaicājumiem paliek daudz maskētu rindu, ko izlaist, un vieta atgriežas tikai pēc sapludināšanas. Vecu datu dzēšanai particijas vai TTL gandrīz vienmēr ir labāki.

Plānojiet dzēšanu jau sākumā

Lētākā dzēšana ir tā, ko ieplānojāt, veidojot tabulu. Particionējiet pa mēnešiem vai dienām, ja vecos datus dzēš pēc vecuma, un tad dzēsiet particijas vai izmantojiet TTL klauzulu, lai dati beigtos automātiski. Ja regulāri jādzēš atsevišķi lietotāji vai ieraksti, apvienojiet šīs dzēšanas un rēķinieties ar maskētām rindām, līdz sapludināšana panāk. Atjauninājumiem, nevis dzēšanai, jaunākās ClickHouse versijās ir arī vieglais UPDATE ar ielāpu daļām (patch parts). Dokumentācija saka, ka tas paredzēts nelielām izmaiņām, līdz aptuveni 10% tabulas, un ka tas palielina slodzi vaicājumiem, kuriem jāpiemēro ielāpi.

Kontrolsaraksts pirms rindu dzēšanas ClickHouse

1. Vai dzēšanu var izteikt kā particiju dzēšanu vai TTL? Izmantojiet to.
2. Pārbaudiet system.mutations, vai tabulai nav nepabeigtu vai neveiksmīgu mutāciju.
3. Vieglajam DELETE pārbaudiet projekcijas un nereplicētām tabulām pievienojiet IN PARTITION.
4. ALTER TABLE ... DELETE gadījumā novērtējiet skarto daļu izmēru un pārliecinieties par brīvo diska vietu.
5. Apvienojiet daudzas mazas dzēšanas vienā komandā.
6. Izlemiet, kā gaidīt: mutations_sync vai lightweight_deletes_sync, vai uzraugiet system.mutations katrā replikā.
7. Sagatavojiet plānu KILL MUTATION gadījumam, atceroties, ka tas neatceļ jau pabeigtās daļas.