Temmuz 2019'un ilk pazartesisi, sabah 07:52. Operasyon müdürünün mesajı kısaydı: "Aylık kilometre raporu 20 dakikadır dönüyor, müşteri toplantıda bekliyor." Konum tablomuza baktım: 1,9 milyar satır, 410 GB veri, üstüne 130 GB indeks. Tek tablo. Tek InnoDB tablosu. Ve o tabloya her saniye 90-100 yeni satır yazılıyordu — günde kabaca 8 milyon konum kaydı. Rapor sorgusu EXPLAIN'de 240 milyon satır tarayacağını söylüyordu ve söylediğini de yapıyordu.
O tabloyu ben büyütmüştüm. "İleride partisyonlarız" diye not düşerek, iki yıl boyunca.
Büyük Tablonun Sessiz Çürümesi
İşin sinsi tarafı, tablonun bir gün "bozulması" değil, aylar içinde yavaş yavaş çürümesiydi. İlk yıl her şey harikaydı çünkü aktif veri (son birkaç hafta) buffer pool'a sığıyordu. Sorunlar veri buffer pool'un birkaç katına çıkınca başladı: indeksin sıcak olmayan bölgeleri diske düştü, rastgele okuma başına 8-10 ms ödemeye başladık. Gece çalışan özet işleri sabaha sarkmaya, DELETE ile eski veri silme denemelerimiz ise felakete dönüşmeye başladı — 90 günden eski kayıtları silen iş, 6 saat çalışıp undo log'u şişiriyor, replikasyonu 40 dakika geriye düşürüyordu. Silme işini iptal ettik; veri büyümeye devam etti. Kısır döngünün ders kitabı hali.
Çözümün adını herkes biliyordu: partisyon. Bilmediğimiz şey, MySQL'de partisyonun beraberinde getirdiği kurallar zinciriydi.
Primary Key ile İlk Yüzleşme
İlk deneme dosyasını hâlâ saklıyorum. Tabloyu gün bazında RANGE partisyonlara bölecektik; ALTER cümlesini yazdık, test sunucusunda çalıştırdık ve MySQL suratımıza şu hatayı çarptı: "A PRIMARY KEY must include all columns in the table's partitioning function." Bizim primary key (device_id, gps_time) idi ama partisyon fonksiyonunda kullanmak istediğimiz sütun server_time'dı — cihaz saatine güvenmediğimiz için sunucuya varış zamanını ayrı tutuyorduk. MySQL'in kuralı net: partisyon anahtarı, tablodaki her benzersiz indeksin parçası olmak zorunda. Yoksa benzersizliği garanti etmek için tüm partisyonlara bakması gerekir ki bu partisyonun bütün amacını öldürür.
İki gün tartıştık. Sonunda primary key'i (device_id, server_time, gps_time) yaptık ve bir gerçeği kabullendik: cihaz bazında benzersizlik garantimiz artık uygulama katmanının sorumluluğuydu. Bu tavizin bedelini bir kez ödedik — bir firmware hatası aynı kaydı iki kez gönderdiğinde tablo ikisini de kabul etti — ama tekrarlı kayıt ayıklamayı işleme katmanına ekleyince mesele kapandı.
Pruning'in Nazlı Doğası
Partisyonlu tablonun bütün sihri partition pruning'de: sorgunuz zaman aralığı belirtiyorsa MySQL yalnızca ilgili partisyonlara iniyor. 1,9 milyar satırlık tabloda son 3 günü sorgularken sadece 3 günlük partisyonlara bakmak — vaat buydu. İlk testte vaat tutmadı. EXPLAIN PARTITIONS çıktısında sorgunun bütün partisyonları taradığını gördük.
Sebep, benim yazdığım eski bir yardımcı fonksiyondu: sorgular WHERE DATE(server_time) BETWEEN ... şeklinde kuruluyordu. Sütunu fonksiyona sokduğunuz anda pruning ölüyor; MySQL DATE(server_time)'ın hangi partisyona düştüğünü çıkaramıyor. Aynı şey indeks kullanımını da öldürüyordu aslında, yani bu hatayı yıllardır taşıyorduk da maliyetini partisyonsuz tabloda ayırt edemiyorduk. Tüm sorgu üretim katmanını sütunu çıplak bırakacak şekilde (server_time >= '2019-07-01' AND server_time < '2019-07-02') yeniden yazdık. Bir de zamanı TO_DAYS ile partisyonladığımız için BETWEEN sınırlarının gün ortasına denk geldiği durumlarda fazladan bir partisyonun taranmasını normal karşılamayı öğrendik; pruning kaba tanecikli çalışır, cerrahi değil.
1,9 Milyar Satırı Yerinden Oynatmak
Asıl mesele mevcut devi partisyonlu yapıya taşımaktı. ALTER TABLE ... PARTITION BY doğrudan çalışır ama tabloyu kopyalar; bizim boyutta bu, replikasyon dahil 30 saatten fazla kilitli kopyalama demekti. Kabul edilemezdi. Planımız şu oldu: aynı şemada partisyonlu boş bir gölge tablo kur, yazma katmanını çift yazacak şekilde güncelle (yeni kayıt hem eskiye hem yeniye), sonra tarihsel veriyi günlük dilimler halinde arkadan kopyala. Kopyalama betiği gece 01:00-06:00 arasında çalıştı, günde ortalama 45 günlük dilim taşıdı, replikasyon gecikmesi 5 saniyeyi aşarsa kendini duraklatıyordu. 41 gece sonra iki tablo eşitlendi; bir bakım penceresinde RENAME TABLE ile yer değiştirdiler. Toplam kesinti: 11 saniye.
Bu arada bir yol ayrımını da not edeyim: gün bazlı RANGE yerine device_id üzerinden HASH partisyonu ya da ikisinin birleşimi olan subpartition da masadaydı. Kâğıt üstünde cazip — sorguların çoğu tek cihaza iniyor sonuçta. Vazgeçtik, çünkü bizim asıl kanayan yaramız zaman eksenindeydi: eski veriyi ucuza silmek ve zaman aralıklı raporları hızlandırmak. HASH partisyonda DROP PARTITION ile "90 günden eskiyi at" diyemezsiniz; her partisyonda her yaştan veri olur. Subpartition ise yönetim karmaşıklığını ikiye katlıyordu ve 5.7'de bazı kombinasyonlar zaten kısıtlıydı. Partisyon anahtarını sorgu desenine değil, veri yaşam döngüsüne göre seçin; sorgu desenini indeksler zaten karşılıyor.
Partisyon düzenimiz gün bazında RANGE (TO_DAYS(server_time)) oldu, her gece bir cron 14 gün sonrasının partisyonunu önceden açıyor. (İlk hafta bunu unutup "gelecek tarihli kayıt partisyon bulamadı" hatası yiyen de biziz — MAXVALUE partisyonunu güvenlik ağı olarak sonradan ekledik.) Veri saklama politikası ise artık tek satır: ALTER TABLE ... DROP PARTITION. 6 saatlik DELETE işkencesinin yerini milisaniyeler süren bir metadata işlemi aldı. İlk DROP PARTITION'ı çalıştırıp komutun anında döndüğünü gördüğümüzde ofiste gerçekten alkış koptu.
Rakamlar ve Kalan Yaralar
Sayılarla bilanço: o meşhur aylık kilometre raporu 19 dakikadan 42 saniyeye indi (pruning + sorgu düzeltmesi birlikte). Son 24 saatlik araç izi sorgusu ortalama 1,8 saniyeden 140 milisaniyeye düştü. Gece özet işleri 05:40'ta değil 02:15'te bitmeye başladı. Disk kullanımı, 90 gün dışındaki partisyonları düzenli düşürmeye başlayınca 540 GB'tan 210 GB'a geriledi ve buffer pool isabet oranımız yüzde 87'den yüzde 99,2'ye çıktı — asıl hız artışının gizli kahramanı muhtemelen bu.
Göçten sonra ekibe tek bir alışkanlık dayattım: konum tablosuna dokunan her yeni sorgu, code review'da EXPLAIN PARTITIONS çıktısıyla birlikte gelir. Taranan partisyon sayısı beklenenden fazlaysa sorgu geri döner, tartışması sonra. Bu kural sayesinde iki kez, daha yayına çıkmadan, pruning'i öldüren ORM kaynaklı fonksiyon sarmalaması yakaladık. Partisyonlu tablo, disiplinsiz sorguya karşı sizi korumaz; disiplini süreç kurarsınız.
Her şey güllük gülistanlık da değil. Partisyon sayısı arttıkça tablo açılışı ve bazı metadata işlemleri yavaşlıyor; 400 civarı günlük partisyonda tutup eskiyi aylık partisyonlara katlamayı düşündük, sonra 90 günlük saklama politikası netleşince gerek kalmadı. Ve partisyon anahtarını içermeyen sorgular — mesela cihazdan bağımsız, plakaya göre tüm zamanlar araması — hâlâ bütün partisyonları geziyor. Partisyon, kötü sorguyu iyi yapmaz; iyi sorgunun önündeki taşları kaldırır. Bu cümleyi duvara asın.
Sonradan öğrendiğimiz bir incelik daha: partisyonlu tabloda ORDER BY server_time DESC LIMIT 1 gibi "son kaydı ver" sorguları, dikkat etmezseniz her partisyonun indeksine ayrı ayrı dokunup sonuçları birleştiriyor. Cihazın son konumu gibi saniyede yüzlerce kez çalışan bir sorgu için bu israftı; çözümü sorguyu partisyonlu tabloya hiç sokmamak oldu — her cihazın güncel durumu zaten ayrı, küçücük bir "son durum" tablosunda tutuluyordu, oradan okuduk.
Zaman serisi verisiyle boğuşan, tablosu milyar satıra koşan biriyseniz tavsiyem net: partisyonu tablo 100 milyon satırken planlayın, 2 milyarken değil. Kendi şemanız için hangi anahtarla, hangi taneciklikte bölmeniz gerektiğini tartışmak isterseniz buradan ulaşın; bizim 41 gecelik göçün notları hâlâ taze.