MariaDB ve MySQL’de Tablo Boyutunu Sorgulamak için information_schema Kullanımı

Yazar:komut.io ekibi
0 Yorum

Veritabanı yönetiminde en sık karşılaşılan sorunlardan biri, zamanla şişen tablolardan hangisinin ne kadar yer kapladığını bilmemektir. Disk alarmı geldiğinde panikleyerek her yere bakmak yerine, MySQL ve MariaDB’nin bize sunduğu information_schema veritabanını kullanarak birkaç saniye içinde net bir resim elde edebiliriz. Bu yazıda, tablo boyutlarını sorgulamanın pratik yollarını, gerçek dünya senaryolarıyla birlikte ele alacağız.

information_schema Nedir?

information_schema, MySQL ve MariaDB sunucusunun meta verilerini tutan sanal bir veritabanıdır. Burada gerçek anlamda fiziksel bir veri dosyası yoktur; sunucu her sorguladığınızda bu verileri anlık olarak üretir. İçinde tablolar, kolonlar, indexler, kullanıcı yetkileri, karakter setleri gibi onlarca bilgi bulunur.

Bizim işimize en çok yarayacak olan kısım TABLES view’ıdır. Bu view içindeki bazı kritik alanlar şunlardır:

  • TABLE_SCHEMA: Tablonun bulunduğu veritabanı adı
  • TABLE_NAME: Tablonun adı
  • ENGINE: Kullanılan depolama motoru (InnoDB, MyISAM vb.)
  • TABLE_ROWS: Tahmini satır sayısı (InnoDB için kesin değil, yaklaşık)
  • DATA_LENGTH: Tablonun veri alanı, byte cinsinden
  • INDEX_LENGTH: Index’lerin kapladığı alan, byte cinsinden
  • DATA_FREE: Tahsis edilmiş ama kullanılmayan alan (fragmentation)
  • CREATE_TIME: Tablonun oluşturulma zamanı
  • UPDATE_TIME: Son güncelleme zamanı

Bu alanları birleştirerek son derece işe yarar sorgular yazabiliriz.

Tek Bir Veritabanındaki Tüm Tabloları Boyutlarına Göre Listelemek

En temel ihtiyaç, belirli bir veritabanındaki tabloların boyutlarını görmektir. Aşağıdaki sorgu, veriyi ve indexleri megabyte cinsinden hesaplayarak büyükten küçüğe sıralar:

mysql -u root -p
SELECT
    TABLE_NAME AS 'Tablo',
    ROUND(DATA_LENGTH / 1024 / 1024, 2) AS 'Veri (MB)',
    ROUND(INDEX_LENGTH / 1024 / 1024, 2) AS 'Index (MB)',
    ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS 'Toplam (MB)'
FROM
    information_schema.TABLES
WHERE
    TABLE_SCHEMA = 'veritabani_adiniz'
ORDER BY
    (DATA_LENGTH + INDEX_LENGTH) DESC;

Bu sorguyu çalıştırdığınızda, veritabanınızdaki her tablonun ayrı ayrı ne kadar yer kapladığını net olarak göreceksiniz. ORDER BY kısmı sayesinde en büyük tablolar listenin başında yer alır, böylece problemi hızla tespit edebilirsiniz.

Gerçek dünya senaryosu: E-ticaret sitenizin veritabanında disk dolmaya başladı. Bu sorguyu çalıştırdığınızda orders_log tablosunun 45 GB kapladığını görüyorsunuz. Oysa bu tablo sadece log amaçlı kullanılıyordu ve 6 aydan eski kayıtlar zaten işe yaramıyordu. Bunu tespit etmek, doğru aksiyonu almanızı sağlar.

Tüm Veritabanlarını Tek Sorguda Görmek

Birden fazla veritabanı yönetiyorsanız, hepsini tek seferde görmek çok daha pratik olur:

SELECT
    TABLE_SCHEMA AS 'Veritabani',
    TABLE_NAME AS 'Tablo',
    ENGINE AS 'Motor',
    TABLE_ROWS AS 'Satir Sayisi',
    ROUND(DATA_LENGTH / 1024 / 1024, 2) AS 'Veri (MB)',
    ROUND(INDEX_LENGTH / 1024 / 1024, 2) AS 'Index (MB)',
    ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS 'Toplam (MB)',
    ROUND(DATA_FREE / 1024 / 1024, 2) AS 'Bos Alan (MB)'
FROM
    information_schema.TABLES
WHERE
    TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
ORDER BY
    (DATA_LENGTH + INDEX_LENGTH) DESC
LIMIT 20;

Burada WHERE koşuluna dikkat edin. MySQL’in sistem veritabanlarını (information_schema, mysql, performance_schema, sys) dışarıda bırakıyoruz, çünkü onlar zaten her zaman küçük kalır ve bizi yanıltabilir. LIMIT 20 ile de en büyük 20 tabloyu görüyoruz, fazlasına genelde gerek olmaz.

Veritabanı Bazında Toplam Boyut

Bazen tek tek tablo değil, veritabanı bazında toplam bir resim görmek isteyebilirsiniz. Bu özellikle birden fazla müşteri veritabanı yönetirken işe yarar:

SELECT
    TABLE_SCHEMA AS 'Veritabani',
    COUNT(TABLE_NAME) AS 'Tablo Sayisi',
    ROUND(SUM(DATA_LENGTH) / 1024 / 1024, 2) AS 'Toplam Veri (MB)',
    ROUND(SUM(INDEX_LENGTH) / 1024 / 1024, 2) AS 'Toplam Index (MB)',
    ROUND(SUM(DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS 'Genel Toplam (MB)',
    ROUND(SUM(DATA_FREE) / 1024 / 1024, 2) AS 'Fragmente Alan (MB)'
FROM
    information_schema.TABLES
WHERE
    TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
GROUP BY
    TABLE_SCHEMA
ORDER BY
    SUM(DATA_LENGTH + INDEX_LENGTH) DESC;

Bu sorgu, her veritabanı için tablo sayısı, toplam veri boyutu, index boyutu ve fragmente alan bilgisini özet olarak sunar. Fragmente alan özellikle önemlidir: eğer bir tabloda çok fazla DATA_FREE değeri görüyorsanız, o tablo OPTIMIZE TABLE komutuyla küçültülebilir.

Gigabyte Cinsinden Sorgulama ve Büyük Sistemler için Pratik Yaklaşım

Büyük veritabanlarında megabyte yerine gigabyte görmek daha mantıklıdır. Şu şekilde düzenleyebilirsiniz:

SELECT
    TABLE_SCHEMA AS 'Veritabani',
    TABLE_NAME AS 'Tablo',
    ENGINE AS 'Motor',
    FORMAT(TABLE_ROWS, 0) AS 'Satir Sayisi',
    ROUND(DATA_LENGTH / 1024 / 1024 / 1024, 3) AS 'Veri (GB)',
    ROUND(INDEX_LENGTH / 1024 / 1024 / 1024, 3) AS 'Index (GB)',
    ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024 / 1024, 3) AS 'Toplam (GB)'
FROM
    information_schema.TABLES
WHERE
    TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
    AND (DATA_LENGTH + INDEX_LENGTH) > 1073741824
ORDER BY
    (DATA_LENGTH + INDEX_LENGTH) DESC;

Buradaki AND (DATA_LENGTH + INDEX_LENGTH) > 1073741824 koşulu, 1 GB’tan büyük tabloları filtreler (1073741824 byte = 1 GB). Yani gereksiz küçük tablolarla uğraşmaksızın direkt sorunlu olabilecek büyük tablolara odaklanırsınız.

Gerçek dünya senaryosu: Bir hosting firmasında 200’den fazla müşteri veritabanı yönetiyorsunuz. Disk kullanımı %85’e geldi ve nerede sıkıştığını bilmiyorsunuz. Bu sorgu ile 5 saniyede hangi müşterinin veritabanının patladığını görebilirsiniz.

Belirli Bir Tabloyu Detaylı İncelemek

Sorunlu tabloyu tespit ettikten sonra, o tablo hakkında daha fazla bilgi almak isteyebilirsiniz:

SELECT
    TABLE_NAME AS 'Tablo',
    ENGINE AS 'Depolama Motoru',
    ROW_FORMAT AS 'Satir Formati',
    TABLE_ROWS AS 'Tahmini Satir',
    AVG_ROW_LENGTH AS 'Ort. Satir Boyu (byte)',
    ROUND(DATA_LENGTH / 1024 / 1024, 2) AS 'Veri (MB)',
    ROUND(INDEX_LENGTH / 1024 / 1024, 2) AS 'Index (MB)',
    ROUND(DATA_FREE / 1024 / 1024, 2) AS 'Bos Alan (MB)',
    CREATE_TIME AS 'Olusturulma',
    UPDATE_TIME AS 'Son Guncelleme',
    TABLE_COLLATION AS 'Karakter Seti'
FROM
    information_schema.TABLES
WHERE
    TABLE_SCHEMA = 'veritabani_adiniz'
    AND TABLE_NAME = 'tablo_adiniz';

Bu sorgu tek bir tablonun tüm meta verilerini gösterir. AVG_ROW_LENGTH değeri, satır başına ortalama byte kullanımını gösterir ve bu sayede gelecekteki büyüme tahminleri yapabilirsiniz. Örneğin, ortalama satır boyu 500 byte olan bir tabloya günde 100.000 yeni satır geliyorsa, aylık yaklaşık 1.5 GB büyüyeceğini hesaplayabilirsiniz.

Fragmentation Analizi ve OPTIMIZE TABLE

Özellikle çok silme/güncelleme yapılan tablolarda fragmentation ciddi bir sorun haline gelir. Aşağıdaki sorgu, fragmentasyon oranı yüksek olan tabloları tespit eder:

SELECT
    TABLE_SCHEMA AS 'Veritabani',
    TABLE_NAME AS 'Tablo',
    ENGINE AS 'Motor',
    ROUND(DATA_LENGTH / 1024 / 1024, 2) AS 'Veri (MB)',
    ROUND(DATA_FREE / 1024 / 1024, 2) AS 'Bos Alan (MB)',
    ROUND(DATA_FREE / (DATA_LENGTH + DATA_FREE) * 100, 2) AS 'Fragmanasyon %'
FROM
    information_schema.TABLES
WHERE
    TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
    AND DATA_FREE > 0
    AND DATA_LENGTH > 0
ORDER BY
    (DATA_FREE / (DATA_LENGTH + DATA_FREE)) DESC
LIMIT 15;

Dikkat: InnoDB tablolarda DATA_FREE değeri, tablespace içindeki boş alanı gösterir ve bu değer her zaman fragmentasyonu doğrudan yansıtmaz. MyISAM tablolarda bu değer çok daha anlamlıdır. Yüksek fragmentasyon tespit ettiğinizde şu komutla düzeltebilirsiniz:

mysqlcheck -u root -p --optimize --all-databases

Ya da tek bir tablo için doğrudan SQL ile:

OPTIMIZE TABLE veritabani_adiniz.tablo_adiniz;

Uyarı: OPTIMIZE TABLE komutu, özellikle büyük tablolarda uzun süre table lock yapabilir. Bunu mutlaka bakım penceresi sırasında ya da düşük trafikli saatlerde çalıştırın.

Shell Script ile Otomatik İzleme

Sysadmin olarak bu sorguları her gün elle çalıştırmak yerine, bir shell script’e döküp crontab ile otomatik hale getirebilirsiniz:

#!/bin/bash
# db_size_monitor.sh
# Her gün çalışır, büyük tabloları raporlar

DB_USER="monitor_user"
DB_PASS="guclu_sifre"
LOG_DIR="/var/log/mysql-reports"
DATE=$(date +%Y-%m-%d)
THRESHOLD_MB=500  # 500 MB'tan büyük tabloları raporla

mkdir -p "$LOG_DIR"

mysql -u "$DB_USER" -p"$DB_PASS" --skip-column-names -e "
SELECT
    CONCAT(TABLE_SCHEMA, '.', TABLE_NAME) AS tablo,
    ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS boyut_mb,
    TABLE_ROWS AS satir_sayisi
FROM
    information_schema.TABLES
WHERE
    TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
    AND (DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024 > $THRESHOLD_MB
ORDER BY
    (DATA_LENGTH + INDEX_LENGTH) DESC;
" > "$LOG_DIR/buyuk_tablolar_$DATE.txt"

echo "Rapor olusturuldu: $LOG_DIR/buyuk_tablolar_$DATE.txt"

# Opsiyonel: Mail ile gonder
# mail -s "MySQL Buyuk Tablo Raporu - $DATE" [email protected] < "$LOG_DIR/buyuk_tablolar_$DATE.txt"

Bu scripti çalıştırılabilir yapıp crontab’a ekleyin:

chmod +x /usr/local/bin/db_size_monitor.sh

# Her sabah 08:00'de çalıştır
echo "0 8 * * * root /usr/local/bin/db_size_monitor.sh" >> /etc/crontab

Önemli Notlar ve Yaygın Hatalar

InnoDB ve TABLE_ROWS Güvenilirliği

InnoDB’de TABLE_ROWS değeri kesin değildir. Bu değer, InnoDB’nin istatistik toplarken hesapladığı tahmindir ve gerçek satır sayısından %40-50 sapabilir. Eğer kesin satır sayısına ihtiyaç duyuyorsanız:

SELECT COUNT(*) FROM veritabani_adiniz.tablo_adiniz;

komutunu kullanmanız gerekir. Ancak bu, büyük tablolarda ciddi zaman alabilir. Genellikle boyut tahmini için TABLE_ROWS yeterlidir ama raporlama veya faturalandırma gibi hassas işlemler için COUNT(*) kullanın.

information_schema Sorgularının Performansı

information_schema.TABLES sorguları, özellikle çok sayıda veritabanı ve tablo olan sistemlerde yavaş çalışabilir. Bunun nedeni, MySQL’in bu verileri toplamak için tüm tablolara bakmasıdır. MariaDB 10.x sürümlerinde bu durum biraz iyileştirilmiş olsa da yine de büyük sistemlerde dikkatli olun.

Eğer sunucunuzda 1000’den fazla tablo varsa ve sorgular 10-20 saniye sürüyorsa, bu normal bir durumdur. Bu sorguları production trafiği yüksekken çalıştırmaktan kaçının.

Doğru Yetki Yapılandırması

Monitoring için ayrı bir kullanıcı oluşturun ve sadece gerekli yetkiyi verin:

CREATE USER 'monitor_user'@'localhost' IDENTIFIED BY 'guclu_sifre_buraya';
GRANT SELECT ON information_schema.* TO 'monitor_user'@'localhost';
FLUSH PRIVILEGES;

Bu kullanıcı, yalnızca information_schema okuyabilir ve başka hiçbir işlem yapamaz. Güvenlik açısından en doğru yaklaşım budur.

Disk Kullanımıyla Karşılaştırma

information_schema sorguları bazen gerçek disk kullanımıyla tam örtüşmeyebilir. Bunun birkaç nedeni vardır:

  • InnoDB tablolarda ibdata1 veya ayrı .ibd dosyaları, pre-allocated alan içerebilir.
  • Binary log, undo log ve temporary tablolar hesaba katılmaz.
  • Bazı depolama motorları veriyi sıkıştırabilir.

Gerçek disk kullanımını doğrulamak için:

du -sh /var/lib/mysql/
du -sh /var/lib/mysql/veritabani_adiniz/
ls -lh /var/lib/mysql/veritabani_adiniz/*.ibd | sort -k5 -rh | head -20

Bu iki yöntemi birlikte kullanmak, sağlıklı bir resim elde etmenizi sağlar.

Pratik Özet: Hangi Senaryoda Ne Kullanmalısınız

  • Disk alarmı geldi, hızlıca nerede sorun var? Tüm veritabanlarını büyükten küçüğe sıralayan sorguyu çalıştırın.
  • Belirli bir veritabanının içindeki sorunlu tablo nerede? Tek veritabanı sorgusunu kullanın.
  • Tablom neden beklenmedik büyüdü? Detaylı tablo inceleme sorgusunu çalıştırın, UPDATE_TIME ve TABLE_ROWS değerlerini inceleyin.
  • OPTIMIZE TABLE çalıştırmalı mıyım? Fragmentation sorgusunu çalıştırın, %30’un üzerindeyse optimize etmeyi düşünün.
  • Düzenli izleme istiyorum. Shell scripti ile crontab’a ekleyin ve raporları otomatik alın.

Sonuç

information_schema.TABLES view’ı, MySQL ve MariaDB yöneticilerinin elinde olan en güçlü araçlardan biridir. Disk doldu alarmı geldiğinde SSH’a bağlanıp birkaç satır SQL yazmak, sorunu dakikalar içinde tespit etmenizi sağlar. Bu yazıda anlattığımız sorgular ve shell script, günlük sysadmin hayatında doğrudan kullanılabilir düzeydedir.

Özellikle şunu vurgulamak isterim: bu sorguları proaktif olarak, yani sorun çıkmadan önce çalıştırmak çok daha değerlidir. Haftalık ya da günlük raporları otomatik hale getirirseniz, bir tablonun anormal büyüdüğünü önceden fark eder ve önlem alabilirsiniz. Reaktif sysadmin olmak yerine proaktif olmak, hem sizin için hem de yönettiğiniz sistemler için çok daha sağlıklı bir yaklaşımdır.

Son olarak, bu sorguları root kullanıcısıyla değil, sadece information_schema okuma yetkisi olan bir monitoring kullanıcısıyla çalıştırma alışkanlığı edinin. Küçük bir güvenlik önlemi, uzun vadede büyük sorunların önüne geçer.

Bir yanıt yazın

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