MySQL Diski Nasıl Kullanır?

MySQL in nasıl çalıştığını anlamak onun sistem kaynaklarını nasıl kullandığından geçiyor. Bu bağlamda bir makale sersinin ilk bölümü olarak MySQL disk kullanımı hakkında bir makale yayınlamak istedim. İlk aşamada MySQL in veriyi disk üzerinde nerede ve ne şekilde tuttuğunu anlatmakla başlamak MySQL in yapısını anlamaya başlamak için güzel bir başlangıç olacaktır.

MySQL Veri Dosyaları

Makalenin bundan sonraki kısmına MySQL de en çok kullanılan ve ön tanımlı olarak gelen storage engine olan InnoDB yi kullandığınızı varsayarak devam ediyorum.

Önemli Not: Kullandığınız farklı bir storage engine varsa diske veri yazma ve okuma davranışı tamamen değişecektir.

Bu noktada Tablespace kavramı fark veritabanı tipleri için farklı anlamlar ifade ettiği için MySQL açısından tablespace in ne anlama geldiğini anlatarak başlayabiliriz. Tablespace terimini MySQL dünyasında InnoDB nin veriyi ve indexleri fiziksel olarak veri blokları (data block)içerisinde tuttuğu dosya olarak tanımlayabiliriz. Bir sistemde bir ya da birden fazla tablespace olabilir. Veritabanı tabloları ya da indexler ortak tablespaceler içerisinde gruplanmış olabilir.

innodb_file_per_table

Bu config ile MySQL oluşturduğu her bir tablo için ayrı bir tablespace açar. En çok tercih edilen yol budur. Bu ayar son sürümlerle beraber default olarak ON gelmektedir. Parametrenin adından da anlaşılacağı üzere her veritabanı tablosu için ayrı bir veritabanı dosyası oluşturur. Tablonun verisi ve tablo üzerindeki indexlerin verisi bu dosya üzerinde saklanır.

Resimde görüleceği üzere her bir tablo için tablo adı ile aynı isimde .ibd uzantılı bir dosya oluşur. Özellikle sınırlandırılmadığı sürece diskte yer olduğu sürece dosyalar büyüyebilirler.

bir ibd dosyasının boyutu içindeki verinin boyutu ile aynı değildir.

Genelde karıştırılan bir konu ibd dosyalarının diskte kapladıkları alanın tamamen içindeki veri büyüklüğü ile birebir aynı olduğunu sanılmasıdır. Aslında bu ifade bazı durumlarda doğru olmakla beraber şunları sözden kaçırmamak gerekir.

1. Eğer tabloda delete ve update işlemleri var ise ilgili ibd dosyalarında fragmantasyon olabilir. Dolayısı ile veri diskte dosyanın kapladığı yere göre daha az yer kaplıyor olabilir.

2. ibd dosyalarında sadece veri değil index ler de yer almaktadır. Zaten her halükarda üstünde indeks olan tablolarda verinin ibd dosyasının bir kısmını kaplaması beklenir.

Peki kontrol dışı büyümüş ve fragmante olmuş InnoDB tablolarını nasıl küçülteceğiz? Bunun için en iyi yol tabloyu rebuild etmektir. Aşağıdaki örnekte non blocking bir rebuild için bir tabloya rebuild komutu yazdım. Alter ifadesinin sonuna eklediğim iki ekstra opsiyon sayesinde sistem çalışırken tabloda rebuild işlemi yapmak mümkün.

MySQL 5.7 sürümünden beri online DDL operasyonlarını desteklemeye başladı

ALTER TABLE mytable engine=innodb, ALGORITHM=INPLACE, LOCK=NONE

ALTER TABLE mytable engine=innodb, ALGORITHM=INPLACE, LOCK=NONE

ALTER TABLE mytable engine=innodb, ALGORITHM=INPLACE, LOCK=NONE

Bu noktada yine MySQL diski nasıl kullanır sorusuna geri dönüyoruz. Çünkü bu rebuild işlemi sırasında geçici olarak oluşacak tabloya ait ibd dosyasının diski kullanacağının farkında olmamız gerekiyor. Yani büyük bir tabloda rebuild işlemi yapıyorsak diskimizde tablo rebuild olduktan sonraki yeni boyutu kadar geçici yere ihtiyacımız olduğunun farkında olmalıyız.

Bir örnek ile anlatırsak. 300GB a kadar büyümüş bir tablomuz var. Bir takım temizlik ya da offloading işlemleri yaptık. Tablodaki verinin bir kısmını sildik ve tabloyu küçülttük. Ancak tablo yukarda bahsettiğimiz nedenlerden dolayı hala diskte 300GB yer kaplamakta. Bizim hesaplarımıza göre rebuild işlemi yaptığımızda 50GB a kadar düşecek. Bu durumda diskimizde bu işlem için 50 GB geçici olarak boş yerimiz olduğundan emin olmamız gerekiyor.

Son olarak bu örnekte bahsettiğim büyüklükleri nasıl hesapladığımıza dair SQL ile yazıyı bitirelim.

select file_name,
       TOTAL_EXTENTS * EXTENT_SIZE / 1024 / 1024 / 1024 total_space_gb,
       FREE_EXTENTS * EXTENT_SIZE / 1024 / 1024 / 1024 free_space_db
from information_schema.files
order by TOTAL_EXTENTS desc

select file_name,
       TOTAL_EXTENTS * EXTENT_SIZE / 1024 / 1024 / 1024 total_space_gb,
       FREE_EXTENTS * EXTENT_SIZE / 1024 / 1024 / 1024 free_space_db
from information_schema.files
order by TOTAL_EXTENTS desc

select file_name,
       TOTAL_EXTENTS * EXTENT_SIZE / 1024 / 1024 / 1024 total_space_gb,
       FREE_EXTENTS * EXTENT_SIZE / 1024 / 1024 / 1024 free_space_db
from information_schema.files
order by TOTAL_EXTENTS desc

Bu sorgu ile sistemdeki bütün dosyalarının boyutları ve içeriklerinde ne kadar boş alan olduğunu öğrenebilirsiniz.