MariaDB Galera Cluster Üzerinde Sorgu Optimizasyonu

Yazar:komut.io ekibi
0 Yorum

Galera Cluster ortamında sorgu optimizasyonu, tek düğümlü bir MariaDB kurulumuna kıyasla çok daha fazla incelik barındırır. Çünkü burada sadece sorgunun kendisiyle değil, düğümler arası replikasyon maliyeti, yazma çakışmaları (write conflicts), sertifikasyon gecikmesi ve yük dengeleme kararlarıyla da boğuşursunuz. Bu yazıda gerçek dünya senaryolarından yola çıkarak Galera Cluster üzerinde sorgu performansını nasıl iyileştireceğinizi adım adım ele alacağız.

Galera Cluster’ın Sorgu Performansına Etkisi

Galera, senkron çok yönlü replikasyon (synchronous multi-master replication) kullanır. Bu mimarinin en önemli özelliği, her COMMIT işleminin tüm düğümlere gönderilmesi ve sertifikasyon sürecinden geçmesidir. Bu süreç, özellikle yoğun yazma iş yüklerinde ciddi gecikmelere yol açabilir.

Tipik bir Galera ortamında karşılaştığınız performans sorunları şu kategorilere girer:

  • Yüksek replikasyon gecikmesi: Büyük transaction’ların tüm cluster’a yayılması zaman alır
  • Write-set certification çakışmaları: Aynı satırları eş zamanlı güncelleyen işlemler çakışır
  • Flow control devreye girmesi: Yavaş bir düğüm tüm cluster’ı yavaşlatabilir
  • Hotspot satırlar: Sık güncellenen tek bir satır, tüm cluster’da darboğaz yaratır
  • DDL işlemlerinin bloklaması: ALTER TABLE gibi komutlar cluster genelinde kilitlenmelere neden olabilir

Ortam Analizi: Nerede Duruyoruz?

Optimizasyona başlamadan önce mevcut durumu anlamanız gerekir. Aşağıdaki sorgu seti, Galera cluster’ınızın sağlık durumunu hızlıca ortaya koyar:

mysql -u root -p -e "
SHOW STATUS LIKE 'wsrep_%';
" | grep -E "wsrep_flow_control_paused|wsrep_local_recv_queue|wsrep_cert_deps_distance|wsrep_apply_oool|wsrep_local_send_queue"

Bu komutun çıktısında dikkat etmeniz gereken değerler:

  • wsrep_flow_control_paused: 0’a yakın olmalı, yüksekse cluster darboğazda
  • wsrep_local_recv_queue: Uzun süredir 0’dan büyükse düğüm geride kalıyor
  • wsrep_cert_deps_distance: Paralel uygulama potansiyelini gösterir, yüksek olması iyidir
  • wsrep_apply_oool: Sıra dışı uygulama oranı, yüksekse paralel replikasyon çalışıyor

Yavaş sorguları tespit etmek için slow query log’u etkinleştirin:

# /etc/mysql/conf.d/slow-query.cnf
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1
min_examined_row_limit = 100

Log’u analiz etmek için pt-query-digest kullanın:

pt-query-digest /var/log/mysql/slow.log 
  --since "2024-01-01 00:00:00" 
  --until "2024-01-01 23:59:59" 
  --order-by Query_time:sum 
  --limit 20 
  > /tmp/slow_query_report.txt

Transaction Boyutunu Küçültmek

Galera’da transaction ne kadar büyükse, write-set o kadar büyük olur ve sertifikasyon süreci o kadar uzar. Bir e-ticaret müşterisinde şöyle bir senaryo yaşadım: Geceleri çalışan bir batch job, 500.000 satırlık bir tabloyu tek bir transaction içinde güncelliyordu. Sabah mesaiye geldiğimizde cluster flow control nedeniyle neredeyse durmaktaydı.

Kötü örnek:

mysql -u root -p << 'EOF'
START TRANSACTION;
UPDATE orders 
SET status = 'archived' 
WHERE created_at < DATE_SUB(NOW(), INTERVAL 2 YEAR);
COMMIT;
EOF

İyi örnek, batch güncelleme:

mysql -u root -p << 'EOF'
DELIMITER //
CREATE PROCEDURE batch_archive_orders()
BEGIN
  DECLARE affected_rows INT DEFAULT 1;
  DECLARE batch_size INT DEFAULT 1000;
  
  WHILE affected_rows > 0 DO
    START TRANSACTION;
    
    UPDATE orders 
    SET status = 'archived' 
    WHERE created_at < DATE_SUB(NOW(), INTERVAL 2 YEAR)
      AND status != 'archived'
    LIMIT 1000;
    
    SET affected_rows = ROW_COUNT();
    COMMIT;
    
    -- Cluster'ın nefes almasına izin ver
    DO SLEEP(0.1);
  END WHILE;
END //
DELIMITER ;

CALL batch_archive_orders();
EOF

Bu yaklaşımla aynı işi yapıyorsunuz ancak cluster’ı boğmuyorsunuz. DO SLEEP(0.1) kısmı küçük görünse de yoğun iş yüklerinde kritik öneme sahiptir.

İndeks Stratejisi ve Galera Özellikleri

Galera ortamında indeks optimizasyonu, standart MariaDB’den farklı değil gibi görünse de bazı önemli nüanslar var. Özellikle PRIMARY KEY seçimi doğrudan replikasyon performansını etkiler.

Galera, row-based replikasyon kullandığından her satırın PRIMARY KEY üzerinden tanımlanması gerekir. PRIMARY KEY olmayan tablolar Galera’da ciddi performans sorunlarına yol açar:

# PRIMARY KEY'i olmayan tabloları tespit et
mysql -u root -p information_schema << 'EOF'
SELECT 
  t.TABLE_SCHEMA,
  t.TABLE_NAME,
  t.TABLE_ROWS,
  t.ENGINE
FROM 
  information_schema.TABLES t
  LEFT JOIN information_schema.TABLE_CONSTRAINTS tc 
    ON t.TABLE_SCHEMA = tc.TABLE_SCHEMA 
    AND t.TABLE_NAME = tc.TABLE_NAME 
    AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
WHERE 
  t.TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
  AND t.TABLE_TYPE = 'BASE TABLE'
  AND tc.CONSTRAINT_NAME IS NULL;
EOF

Bu sorgudan çıkan tablolar varsa acil öncelikli düzeltilmesi gerekir. PRIMARY KEY eklemek için:

mysql -u root -p mydb << 'EOF'
-- Mevcut yapıyı bozmadan UUID tabanlı PK ekle
ALTER TABLE log_entries 
  ADD COLUMN id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
  ADD PRIMARY KEY (id);
EOF

Bileşik indeks kullanımı için gerçek bir örnek verelim. Bir lojistik firmasında şu sorgu dakikalar sürmekteydi:

mysql -u root -p logistics_db << 'EOF'
-- Problematik sorgu
EXPLAIN SELECT 
  s.shipment_code,
  s.origin_city,
  s.destination_city,
  s.created_at,
  c.company_name,
  c.contact_email
FROM shipments s
JOIN customers c ON s.customer_id = c.id
WHERE 
  s.status = 'in_transit'
  AND s.created_at BETWEEN '2024-01-01' AND '2024-03-31'
  AND s.destination_city = 'Istanbul'
ORDER BY s.created_at DESC
LIMIT 50;
EOF

EXPLAIN çıktısı type: ALL ve rows: 2500000 gösteriyordu. Çözüm:

mysql -u root -p logistics_db << 'EOF'
-- Bileşik indeks oluştur (sıralama önemli!)
CREATE INDEX idx_shipments_status_city_date 
  ON shipments (status, destination_city, created_at);

-- customers tablosu için covering index
CREATE INDEX idx_customers_id_company 
  ON customers (id, company_name, contact_email);

-- Tekrar EXPLAIN çalıştır
EXPLAIN SELECT 
  s.shipment_code,
  s.origin_city,
  s.destination_city,
  s.created_at,
  c.company_name,
  c.contact_email
FROM shipments s
JOIN customers c ON s.customer_id = c.id
WHERE 
  s.status = 'in_transit'
  AND s.created_at BETWEEN '2024-01-01' AND '2024-03-31'
  AND s.destination_city = 'Istanbul'
ORDER BY s.created_at DESC
LIMIT 50;
EOF

Bu değişiklikle sorgu süresi 2.8 dakikadan 0.04 saniyeye düştü.

DDL İşlemlerini Optimize Etmek

Galera’da DDL işlemleri özellikle tehlikelidir çünkü wsrep_OSU_method parametresine göre farklı davranır. Varsayılan TOI (Total Order Isolation) modu tüm cluster’ı kilitler.

# Mevcut DDL metodunu kontrol et
mysql -u root -p -e "SHOW VARIABLES LIKE 'wsrep_OSU_method';"

# RSU (Rolling Schema Upgrade) moduna geç
mysql -u root -p -e "SET GLOBAL wsrep_OSU_method='RSU';"

RSU modu ile büyük tablo üzerinde ALTER:

mysql -u root -p mydb << 'EOF'
-- Önce bu düğümü cluster'dan mantıksal olarak ayır
SET wsrep_OSU_method=RSU;

-- pt-online-schema-change ile büyük tablolarda güvenli ALTER
-- (Bu komutu MySQL shell dışında çalıştırın)
EOF

# pt-osc ile gerçek ALTER işlemi
pt-online-schema-change 
  --host=localhost 
  --user=root 
  --password=your_password 
  --database=mydb 
  --table=large_table 
  --alter="ADD COLUMN metadata JSON DEFAULT NULL, ADD INDEX idx_metadata ((CAST(metadata->>'$.category' AS CHAR(50))))" 
  --execute 
  --no-drop-old-table 
  --progress time,30 
  --print

TOI moduna geri dönmeyi unutmayın:

mysql -u root -p -e "SET GLOBAL wsrep_OSU_method='TOI';"

Paralel Replikasyon Ayarları

Galera, slave tarafında paralel replikasyon uygulayabilir. Bu özellik doğru yapılandırıldığında özellikle yoğun okuma/yazma karma iş yüklerinde performansı önemli ölçüde artırır:

# /etc/mysql/conf.d/galera-parallel.cnf
[mysqld]
# Slave thread sayısını CPU çekirdeğine göre ayarla
wsrep_slave_threads = 8

# Paralel uygulama için gerekli
innodb_autoinc_lock_mode = 2

# Replikasyon gecikmesini azaltmak için
wsrep_max_ws_size = 2G
wsrep_max_ws_rows = 131072

# InnoDB buffer pool (toplam RAM'in %70-80'i)
innodb_buffer_pool_size = 12G
innodb_buffer_pool_instances = 8

# Log dosyası boyutu
innodb_log_file_size = 1G
innodb_log_buffer_size = 256M

# Flush politikası - Galera için optimize
innodb_flush_log_at_trx_commit = 0
innodb_flush_method = O_DIRECT

wsrep_slave_threads değerini belirlemek için şu sorguyu kullanın:

mysql -u root -p -e "
SELECT 
  VARIABLE_NAME,
  VARIABLE_VALUE 
FROM information_schema.GLOBAL_STATUS 
WHERE VARIABLE_NAME IN (
  'wsrep_cert_deps_distance',
  'wsrep_apply_window',
  'wsrep_commit_window'
);"

wsrep_cert_deps_distance değeriniz 8 ise wsrep_slave_threads değerini 8 veya biraz üzerinde tutmak idealdir.

Hotspot Sorununu Çözmek

Hotspot, birden fazla düğümün aynı satırları sık sık güncellediği durumdur ve Galera’da certification failure’lara yol açar. Bir SaaS ürününde counter tablosunda şu problemi yaşadım:

# Certification failure sayısını izle
mysql -u root -p -e "
SHOW STATUS LIKE 'wsrep_local_cert_failures';
SHOW STATUS LIKE 'wsrep_local_bf_aborts';
"

Hotspot çözümü olarak counter tablosu yerine sharding yaklaşımı:

mysql -u root -p saas_db << 'EOF'
-- Kötü tasarım: tek satır counter
CREATE TABLE page_views (
  page_id INT NOT NULL PRIMARY KEY,
  view_count BIGINT DEFAULT 0
);

-- İyi tasarım: sharded counter
CREATE TABLE page_views_sharded (
  page_id INT NOT NULL,
  shard_id TINYINT NOT NULL,
  view_count BIGINT DEFAULT 0,
  PRIMARY KEY (page_id, shard_id)
);

-- Insert yerine shard'a yaz
INSERT INTO page_views_sharded (page_id, shard_id, view_count)
VALUES (1001, FLOOR(RAND() * 16), 1)
ON DUPLICATE KEY UPDATE view_count = view_count + 1;

-- Okuma: tüm shard'ları topla
SELECT page_id, SUM(view_count) as total_views
FROM page_views_sharded
WHERE page_id = 1001
GROUP BY page_id;
EOF

Bu yaklaşımla 16 farklı satıra dağıtılan yazma işlemleri, certification failure oranını %95 oranında düşürür.

Okuma ve Yazma Ayrımı

Galera cluster’da her düğüm hem okuma hem yazma kabul edebilir, ancak ProxySQL veya MaxScale kullanarak okuma/yazma ayrımı yapmak, yazma yükünü tek düğümde yoğunlaştırır ve certification çakışmalarını azaltır.

ProxySQL yapılandırması için temel kurallar:

# ProxySQL admin arayüzüne bağlan
mysql -u admin -padmin -h 127.0.0.1 -P 6032 << 'EOF'
-- Yazma grubunu tanımla (hostgroup 10)
INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight)
VALUES (10, '192.168.1.101', 3306, 1000);

-- Okuma grubunu tanımla (hostgroup 20)
INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight)
VALUES (20, '192.168.1.101', 3306, 500),
       (20, '192.168.1.102', 3306, 500),
       (20, '192.168.1.103', 3306, 500);

-- Sadece SELECT sorgularını okuma grubuna yönlendir
INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply)
VALUES (1, 1, '^SELECT.*FOR UPDATE', 10, 1),
       (2, 1, '^SELECT', 20, 1);

-- Galera için health check sorgusu
UPDATE global_variables 
SET variable_value = 'SELECT variable_value FROM information_schema.global_status WHERE variable_name = ''wsrep_local_state_comment'' AND variable_value = ''Synced'''
WHERE variable_name = 'mysql-monitor_galera_healthcheck_query';

LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
SAVE MYSQL QUERY RULES TO DISK;
EOF

Query Cache ve Performans Schema Kullanımı

Galera ortamında query cache kullanımı tartışmalıdır çünkü her yazma işlemi cache’i geçersiz kılar. Bunun yerine Performance Schema ile darboğazları tespit edin:

mysql -u root -p << 'EOF'
-- En yavaş 10 sorguyu bul
SELECT 
  DIGEST_TEXT,
  COUNT_STAR as execution_count,
  ROUND(AVG_TIMER_WAIT / 1000000000, 3) as avg_seconds,
  ROUND(SUM_TIMER_WAIT / 1000000000, 3) as total_seconds,
  SUM_ROWS_EXAMINED,
  SUM_ROWS_SENT,
  ROUND(SUM_ROWS_EXAMINED / SUM_ROWS_SENT, 2) as examination_ratio
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = 'mydb'
  AND COUNT_STAR > 100
ORDER BY avg_seconds DESC
LIMIT 10;
EOF
  • examination_ratio: Bu değer yüksekse (örneğin 1000’den büyük) indeks eksikliği var demektir
  • avg_seconds: Ortalama çalışma süresi
  • execution_count: Sıklık, optimize edilecek sorgunun önceliğini belirler

Galera Spesifik Monitoring

Performans sorunlarını gerçek zamanlı izlemek için aşağıdaki scripti cron’a ekleyin:

#!/bin/bash
# /usr/local/bin/galera_monitor.sh

MYSQL_CMD="mysql -u monitor_user -pmonitor_pass -N -B"
ALERT_EMAIL="[email protected]"
THRESHOLD_FLOW_CONTROL=0.1
THRESHOLD_QUEUE=10

# Flow control kontrolü
FLOW_PAUSED=$($MYSQL_CMD -e "SHOW STATUS LIKE 'wsrep_flow_control_paused';" | awk '{print $2}')

if (( $(echo "$FLOW_PAUSED > $THRESHOLD_FLOW_CONTROL" | bc -l) )); then
  echo "UYARI: Galera flow control aktif! Deger: $FLOW_PAUSED" | 
    mail -s "[GALERA ALERT] Flow Control Yuksek - $(hostname)" $ALERT_EMAIL
fi

# Receive queue kontrolü
RECV_QUEUE=$($MYSQL_CMD -e "SHOW STATUS LIKE 'wsrep_local_recv_queue_avg';" | awk '{print $2}')

if (( $(echo "$RECV_QUEUE > $THRESHOLD_QUEUE" | bc -l) )); then
  echo "UYARI: Galera receive queue yüksek! Deger: $RECV_QUEUE" | 
    mail -s "[GALERA ALERT] Receive Queue Yuksek - $(hostname)" $ALERT_EMAIL
fi

# Cert failures son 5 dakika
CERT_FAIL=$($MYSQL_CMD -e "SHOW STATUS LIKE 'wsrep_local_cert_failures';" | awk '{print $2}')
echo "$(date): flow_control=$FLOW_PAUSED recv_queue=$RECV_QUEUE cert_failures=$CERT_FAIL" >> /var/log/galera_monitor.log
chmod +x /usr/local/bin/galera_monitor.sh
echo "*/5 * * * * root /usr/local/bin/galera_monitor.sh" > /etc/cron.d/galera-monitor

InnoDB Deadlock Analizi

Galera ortamında certification failure ile InnoDB deadlock’ları karıştırmak yaygın bir hatadır. Bunları ayırt etmek için:

mysql -u root -p << 'EOF'
-- Son deadlock bilgisi
SHOW ENGINE INNODB STATUSG

-- Certification failure mı deadlock mu?
SELECT 
  g.VARIABLE_NAME,
  g.VARIABLE_VALUE
FROM information_schema.GLOBAL_STATUS g
WHERE g.VARIABLE_NAME IN (
  'wsrep_local_cert_failures',    -- Galera cert hatası
  'wsrep_local_bf_aborts',        -- Galera BF (brute force) abort
  'Innodb_deadlocks'               -- InnoDB deadlock
);
EOF
  • wsrep_local_cert_failures: Galera sertifikasyon hatası, uygulama transaction’larını yeniden denemelidir
  • wsrep_local_bf_aborts: Galera’nın BF abort uyguladığı durumlar, uygulama tarafında retry mekanizması şarttır
  • Innodb_deadlocks: Klasik InnoDB deadlock

Her iki hata türü için uygulama katmanında retry mekanizması implement edilmelidir.

Sonuç

Galera Cluster üzerinde sorgu optimizasyonu tek seferlik bir iş değil, sürekli bir döngüdür. Şu adımları düzenli olarak tekrarlayın:

  • Slow query log ve Performance Schema ile yavaş sorguları tespit edin
  • Transaction boyutlarını batch işlemlerle küçültün
  • PRIMARY KEY’i olmayan tabloları hemen düzeltin
  • DDL işlemleri için pt-osc veya RSU modunu tercih edin
  • wsrep_slave_threads değerini wsrep_cert_deps_distance metriğine göre ayarlayın
  • Hotspot tablolarını sharding yöntemiyle dağıtın
  • ProxySQL ile okuma/yazma ayrımı yapın
  • Flow control ve certification failure metriklerini sürekli izleyin

Galera’nın güçlü yönleri, bu optimizasyonlar yapıldığında gerçekten parlıyor. Üç düğümlü bir cluster, doğru yapılandırıldığında hem yüksek erişilebilirlik hem de tek sunucuyu geçen okuma performansı sunabilir. Ama bunun için cluster’ın kendine özgü davranışlarını anlamak ve uygulamaları buna göre yazmak şart.

Bir yanıt yazın

E-posta adresiniz yayınlanmayacak. Gerekli alanlar * ile işaretlenmişlerdir