Yıllarca sipariş geçmişi olan mağazalarda veritabanı bakımı

Geri yüklediğiniz bir yedekle başlayın

Aşağıdaki her şey satır siliyor ya da tablo yeniden inşa ediyor. Önce yedek alın — sonra da çalıştığını kanıtlayın, çünkü test edilmemiş bir yedek yalnızca dosya adı olan bir tahmindir.

mysqldump --single-transaction --quick --routines --triggers \
  -u ocuser -p opencart | gzip > opencart-$(date +%F).sql.gz

# kanıtlayın: ayrı bir veritabanına geri yükleyip karşılaştırın
zcat opencart-$(date +%F).sql.gz | mysql -u ocuser -p opencart_restore_test

--single-transaction, InnoDB tablolarının tutarlı bir anlık görüntüsünü mağazayı kilitlemeden verir; yani müşteriler alışveriş ederken çalıştırabilirsiniz. Dump işlem desteklemeyen tablolar için uyarı veriyorsa bu zaten başlı başına bir bulgudur — aşağıdaki tablo motoru bölümüne bakın. Dosyayı da geldiği sunucudan başka bir yerde tutun; bir disk arızalandığında mağazayı ve yanı başındaki yedeği birlikte götürür.

Önemli yedek parametresi

--single-transaction

Güvenli silme partisi

5.000 satır

Yavaş sorgu eşiği

long_query_time = 1

Gerçekten büyük olanı bulun

Bu yazı dahil, hiçbir tablo adı listesinden başlamayın. Her mağaza farklı şişer; hangi raporların açık bırakıldığına ve hangi eklentilerin kendi tablosuna log yazdığına bağlıdır. Öğleden sonranızı nereye harcayacağınızı tek sorgu söyler:

SELECT table_name,
       ROUND((data_length + index_length) / 1024 / 1024) AS mb,
       table_rows,
       engine
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY (data_length + index_length) DESC
LIMIT 15;

table_rows InnoDB’de bir tahmindir; burada sorun değil, çünkü aradığınız şey diğerlerinden bir büyüklük mertebesi büyük olan tablolar. Yıllardır çalışan bir mağazada o listenin tepesinde neredeyse hiçbir zaman ürünler ya da siparişler olmaz.

Kimse bakmazken büyüyen tablolar

OpenCart, zamanlanmış hiçbir işin temizlemediği birkaç log yazar. Tek başlarına zararsız, beş yıllık bot ve trafik sonrasında devasa oluyorlar:

• oc_customer_online — Ayarlar → Seçenek altındaki “Çevrimiçi Müşteriler” raporu açıkken sayfa görüntülemelerinde ziyaretçi başına satır yazar.
• oc_customer_activity — müşteri hareket günlüğü; yanındaki ayarla kontrol edilir.
• oc_customer_search — mağazada aratılan her kelime; arama raporunu besler.
• oc_session — oturum motoru db iken oturum satırları; süresi dolanları kimse silmez.
• oc_cart — terk edilmiş sepetler, yıllar öncesinin misafir sepetleri dahil.
• oc_api_session — admin sipariş düzenleme ve entegrasyonların açtığı API oturumları.

Çevrimiçi müşteriler ya da hareket raporlarını okumuyorsanız, önce admin’den kapatın. Hâlâ dolan bir tabloyu temizlemek, her ay tekrarlayacağınız bir angaryadır. Sonra kilitleri tutup redo log’u şişirecek tek bir dev ifade yerine partiler hâlinde silin:

-- silmeden önce mutlaka bakın
SELECT COUNT(*) FROM oc_customer_online WHERE date_added < NOW() - INTERVAL 7 DAY;

-- sonra etkilenen satır sıfır olana kadar tekrarlayın
DELETE FROM oc_customer_online WHERE date_added < NOW() - INTERVAL 7 DAY LIMIT 5000;
DELETE FROM oc_session        WHERE expire < NOW() LIMIT 5000;
DELETE FROM oc_cart           WHERE customer_id = 0
                                AND date_added < NOW() - INTERVAL 60 DAY LIMIT 5000;

İlk tur bittikten sonra aynı ifadeleri daha dar bir aralıkla gece çalışan bir cron işine bağlayın. Elle yapılan temizlik, ilk yoğun haftada atlanan temizliktir; bir yıl sonra yine buradasınız demektir.

Sipariş geçmişi: neyi silmeyin

İnsanların en çok budamak istediği tablolar sipariş tabloları ve en son dokunulması gerekenler onlar. oc_order, oc_order_product, oc_order_option, oc_order_total ve oc_order_history sizin muhasebe kaydınız; çoğu ülkede yıllarca saklamak yasal bir zorunluluk.

Anlamaya değer tek bir istisna var. order_status_id = 0 olan satırlar, onay adımına ulaşıp tamamlanmamış ödemelerdir. Yoğun bir mağazada sayıları gerçek siparişlerin birkaç katı olabilir. Yine de bedava silinmezler: ödeme sağlayıcısından gelen geç bir bildirim bunlardan birini saatler sonra tamamlayabilir ve destek ekibinin bir kart provizyonunu açıklamak için kayda ihtiyacı olabilir. Bunları yalnızca geniş bir zaman aralığının dışında, partiler hâlinde ve sağlayıcıda bekleyen bir şey olmadığını kontrol ettikten sonra siliyoruz.

Sorun gerçekten sipariş tablolarıysa cevap silmek değil arşivlemektir: saklama süresini geçmiş satırları, vitrinin hiç sorgulamadığı bir arşiv veritabanına taşıyın.

OpenCart’ın göndermediği indexler

“Büyük” işte burada “yavaş”a dönüşüyor. OpenCart’ın varsayılan şeması birkaç bin ürüne göre ölçülmüştür; bunun ötesinde kategori ve arama sorguları taramaya başlar. Bir şey eklemeden önce plana bakın:

EXPLAIN SELECT p.product_id
FROM oc_product p
JOIN oc_product_to_category p2c ON p2c.product_id = p.product_id
JOIN oc_product_description pd ON pd.product_id = p.product_id
WHERE p2c.category_id = 20 AND pd.language_id = 1 AND p.status = 1
ORDER BY pd.name
LIMIT 20;

SHOW INDEX FROM oc_product_to_category;

Büyük bir tabloda type sütununun ALL olması ya da isme göre sıralamada “Using filesort” görünmesi işaret sayılır. Üç index çoğu durumu kapatır — ilk ikisi vitrin için, üçüncüsü kullanılamaz hâle gelmiş bir admin sipariş listesi için:

ALTER TABLE oc_product_to_category ADD INDEX idx_cat_prod (category_id, product_id);
ALTER TABLE oc_product_description ADD INDEX idx_lang_name (language_id, name(64));
ALTER TABLE oc_order               ADD INDEX idx_status_added (order_status_id, date_added);

Buradaki name(64) bir önek indexidir: sütunun tamamı yerine ilk 64 karakteri indeksler, böylece index sıralamayı karşılamaya devam ederken kullanışlı kalacak kadar küçük olur. Bunları teker teker ekleyin ve her birinden sonra EXPLAIN’i tekrar çalıştırın. Index bedava değildir: her biri her ekleme ve güncellemede yazılır, yani on iki indexli bir tablo başka bir biçimde yavaştır. Hazır oradayken SEO URL tablosuna da bakın; her istekte okunduğu için indexsiz bir keyword araması tüm siteden vergi alır.

OPTIMIZE, ANALYZE ve tablo motoru

Milyonlarca satır silmek diskteki dosyayı küçültmez; InnoDB boşalan alanı yeniden kullanmak üzere tutar. Geri istiyorsanız OPTIMIZE TABLE tabloyu yeniden inşa eder — ve bunu size söyler, çünkü InnoDB optimize’ı doğrudan uygulamaz, yeniden oluştur ve analiz et adımına düşer. Bu bir yeniden inşadır; yeri cron değil, bakım penceresidir.

ANALYZE TABLE oc_product, oc_product_description, oc_order;   -- ucuz, istatistikleri tazeler
OPTIMIZE TABLE oc_customer_online;                            -- yeniden inşa, alan geri alır

-- InnoDB olmayan tablolar; genelde OpenCart 1.5 / 2.x kalıntısı
SELECT table_name, engine FROM information_schema.tables
WHERE table_schema = DATABASE() AND engine <> 'InnoDB';

Rutin olarak çalıştırılması gereken ANALYZE. Büyük bir silmeden sonra optimizer’ın istatistikleri artık var olmayan bir tabloyu anlatır ve geçen hafta sorunsuz olan sorgularda kötü planlar seçmeye başlar. Son sorgunun bulduğu MyISAM tablolar da dönüştürülmeli: InnoDB havuzunu görmezden gelir ve her yazmada tam tablo kilidi alırlar.

Sonra bırakın veritabanı size söylesin

Bariz iş bittikten sonra tahmin etmeyi bırakın. Yavaş sorgu logunu long_query_time = 1 ile açın ve normal bir hafta boyunca — mümkünse bir kampanya günü de dahil — açık bırakın.

mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

-- ya da MySQL 5.7+ üzerinde sys şemasıyla
SELECT db, exec_count, avg_latency, query
FROM sys.statement_analysis
ORDER BY total_latency DESC
LIMIT 10;

En yavaş tek sorguya değil, toplam süreye göre sıralayın. Her sayfa görüntülemesinde çalışan 80 ms’lik bir sorgu, birinin pazartesileri açtığı iki saniyelik bir rapordan mağazaya çok daha pahalıya mal olur — ve bu genelde OpenCart çekirdeği değil, bir eklentidir.

Otomatikleştirmeye değer bir rutin

1. 1Her gece: log tablolarını partiler hâlinde budayın, yedeğin alındığını ve sıfır bayt olmadığını doğrulayın.
2. 2Her hafta: büyük tablolarda ANALYZE çalıştırın, yavaş logda yeni bir şey var mı diye bakın.
3. 3Her ay: tablo boyutu sorgusunu yeniden çalıştırıp geçen ayla karşılaştırın.
4. 4Üç ayda bir: bir yedeği ayrı bir veritabanına geri yükleyin ve mağazayı gerçekten onunla açın.
5. 5Her eklenti kurulumundan sonra: boyut sorgusuna tekrar bakın; yeni tablolar sessizce belirir.

Bunların hiçbiri zor değil. Sadece çoğu mağazada sahibi olmayan bir iş — dördüncü yılda veritabanının olması gerekenin üç katı olmasının sebebi tam olarak bu.

Ne zaman bize yazın

Canlı mağazada DELETE çalıştırmak istemiyorsanız, bunu sınırları belli bir iş olarak yapıyoruz: ölçüyoruz, temizliği öneriyoruz, önce geri yüklenmiş bir kopyada çalıştırıyoruz, sonra geri dönüş planıyla uyguluyoruz. Saatlik 10 $ + KDV lansman ücretiyle faturalanır, ilk yanıtı iki saat içinde veriyoruz, Pazartesi–Cumartesi 09:00–22:00 (GMT+3).