Bu başlıkta SQL Server’ın hayati mekanizmalardan biri olan log mekanizmasını ve bu mekanizma ile doğrudan ilişkili olan transaction’ları konuşacağız. Bu mekanizma derin bilgi ve deneyim gerektirmektedir. Bu makaleyi okuduktan sonra daha da bilgi almak için doğru kaynaklara başvurmanızı tavsiye ederim. Yazımıza dönersek eğer, SQL Server’da, veri tabanı kullanıcıları, uygulama geliştiricileri veya DBA’lar tarafından transaction işlemleri yapılabilmektedir. Bir transaction açıldığında özellikle DML (Update, delete, insert) işlemleri commit ve rollback durumuna girebilir. Commit durumunda transaction işlemi veri tabanına yansırken, rollback durumunda veri tabanına yansımayacak işlem geri alınacaktır.
Bu durumu anlayabilmek için önce bir transaction’un nasıl davrandığını bilmemiz gerekmektedir. SQL Server’da 3 tür transaction operasyonu vardır.
- Auto-Commit Transaction
- Implicit Transaction
- Explicit Transaction
Bu 3 operasyonlardan;
Auto-Commit Transaction, default olandır. Örneğin Primary Key ve (1,1 ) artan Id alanı için aynı Id değerini manuel olarak Insert ettiğimizde, bize tabloya duplicate hatası vermesi. SQL Engine’in bu işlemi rollback yapmasıdır. SQL Server tarafından yönetilen bu mekanizmanın adına Write-Ahead Logging (WAL) diyoruz. Ve bu mekanizma log kayıtlarının diske yazıldığı mekanizmadır. Bu mekanizma sayesinde ACID yapısının Durability özelliği sağlanır. Herhangi bir crash durumunda diskte olan kayıtlardan kurtarma sağlanabilir.
Implicit Transaction, çalıştıracağımız query üzerinden de SET IMPLICIT_TRANSACTION ON diyerek, istediğimiz transaction’u veya Management GUI’den Tools > Options > Query Execution > ANSI ekranı üzerinden SET IMPLICIT_TRANSACTIONS seçeneği seçilerek çalıştırılacak olan tüm query’ler üzerinden aktif edilebilir. Akitf edilen bu özellik, transaction çalıştırıldıktan sonra veri tabanına yansıması için commit veya yansımaması için rollback komutu kullanılır. Eğer commit veya rollback yapılmaz ise SQL Server lock mekanizmasını çalıştıracak, aynı tablo üzerinde yapacağınız işlemlerde lock meydana gelecektir. Bu duruma ait bilgiye sp_WhoIsActive çıktısında Lock kolonu altında xml formatında ulaşabilirsiniz.
Explicit Transaction, işleminde başlama ve bitiş noktalarını belirtiriz. Örneğin bir update işlemi yapacaksak eğer, Begin transaction dedikten sonra update işlemini yaparız. Sonrasında eğer commit edersek yapılan işlem veri tabanına yansır. Rollback komutunu çalıştırdığımızda yapılan işlem geri alınacaktır.
SQL Server işlem günlüğünü (Log Records) küçük bir memory içerine cache’ler bu bölgeye Log Buffer denir ve tüm yapılan bu transactionlar öncelikle Log Buffer içerisine yazılır. Log Buffer içinde de her biri 12860 KB büyüklüğünde log blokları vardır. Log blokları 60 KB’a kadar yer kaplar ve tamamen dolduktan sonra transaction log dosyası (ldf) içine işlenir. Bu işlemlerin transaction log dosyası (ldf) içerisine yazılması içinde bazı koşullar vardır. Bu koşullar;
- Transaction commit olduğunda
- Log Buffer size 60 KB değerine ulaştığında
- sp_flush_log store procedure execute edildiğinde
- CHECKPOINT prosesi tamamlandığında
Log Buffer’da, Log Record üretimi
Log Buffer’da Commit, Checkpoint ve Rollback Operasyonları
Yukarıdaki bahsi geçen 4 operasyonun dışında bir transaction rollback olduğunda ya diskimizde yer kalmamıştır ya session kill edilmiştir ya da server crash olmuştur.
Özellikle checkpoint prosesi log mekanizmasın çalışmasında önemli yer edinmektedir. Checkpoint prosesinin 2 sebebi vardır. Diske yazma operasyonlarını gruplandırarak I/O performansını geliştirmek ve Crash Recovery için gerekli olan süreyi azaltmak. Checkpoint ile ilgili bilinmesi gereken en önemli konulardan bir diğeri de, data commit olsun yada olmasın tüm dirty page’ler data file’lar içerisine yazılır. Ve checkpoint prosesi asenkron çalıştığı için işlem günlüğündeki tutulan kayıtlar ile data file’lar içinde tutulan kayıtlar arasında her zaman bir gecikme vardır.
Buffer Pool içerisinde olan dirty page’ler data file içerisine yazılır. Chekpoint prosesi ile Log Buffer içinde yer alan ve log bloklarından oluşan Log Records içindeki bilgilerde transaction log dosyası içine yazılır. Rollback ve commit işlemi bu esnada gerçekleşir. Rollback olması durumunda o LSN numarasına ait data, data file içinden kaldırılır.
SQL Server Log Buffer içerisindeki log bloklarında yer alan log bilgilerini flush ederken 2 bekleme tipi ile karşılaşır. Birinci flush edilen log bilgilerinin bekleme tipi WRITELOG bekleme tipidir. Diğer bekleme tipi ise LOGBUFFER bekleme tipidir. LOGBUFFER bekleme tipi WRITELOG bekleme tipi ile birlikte görülebilir. Sebebi ise Log Buffer içinde bulunan log records blokları dolduğunda yeni bir log records oluşması esnasında oluşan bekleme tipi LOGBUFFER dır.
Bu 2 bekleme tipi esnasında diske commit olma esnasında latency’ler gecikebilir. Bunu kontrol edebilmek için aşağıdaki scripti kullanabilirsiniz.
SELECT [ReadLatency] = CASE WHEN [num_of_reads] = 0 THEN 0 ELSE ([io_stall_read_ms] / [num_of_reads]) END, [WriteLatency] = CASE WHEN [num_of_writes] = 0 THEN 0 ELSE ([io_stall_write_ms] / [num_of_writes]) END, [Latency] = CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0) THEN 0 ELSE ([io_stall] / ([num_of_reads] + [num_of_writes])) END, [AvgBPerRead] = CASE WHEN [num_of_reads] = 0 THEN 0 ELSE ([num_of_bytes_read] / [num_of_reads]) END, [AvgBPerWrite] = CASE WHEN [num_of_writes] = 0 THEN 0 ELSE ([num_of_bytes_written] / [num_of_writes]) END, [AvgBPerTransfer] = CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0) THEN 0 ELSE (([num_of_bytes_read] + [num_of_bytes_written]) / ([num_of_reads] + [num_of_writes])) END, LEFT ([mf].[physical_name], 2) AS [Drive], DB_NAME ([vfs].[database_id]) AS [DB], [mf].[physical_name] FROM sys.dm_io_virtual_file_stats (NULL,NULL) AS [vfs] JOIN sys.master_files AS [mf] ON [vfs].[database_id] = [mf].[database_id] AND [vfs].[file_id] = [mf].[file_id] -- WHERE [vfs].[file_id] = 2 -- log files -- ORDER BY [Latency] DESC -- ORDER BY [ReadLatency] DESC ORDER BY [WriteLatency] DESC; GO
SQL Server’da loglama mekanizması birçok operasyon içerebilir. Bu mekanizma, bir crash meydana geldiğinde commit olmuş her data veri tabanı içerisinde var olmuş olacak. Uncommit olanlar ise rollback olacaktır. Havada kalmış olan (in-flight) transaction’lar da rollback olacaktır. Crash olduktan sonra commit olmuş datalarda istediğimiz zamana (point in time) tutarlı olarak (consistency) dönebiliriz. SQL Server’da replikasyon, mirroring ve cdc log kayıtlarını okuyarak hizmet eden özelliklerdendir.
Log kayıtları Recovery mekanizmasının temelini oluşturur. Recovery Mekanizması log kayıtlarının replay edilmesi veya revert edilmesi işlemidir. Replay edilmesi yani roll forward (REDO) dediğimiz crash öncesi commit olmuş dataların veri tabanına yansımasıdır. Revert edilme işlemi ise roll back (UNDO) dediğimiz crash öncesi commit olmamış dataların veri tabanına yansımamasıdır.
En basit şekilde recovery’nin tanımı bir transaction’nın iptal edilmesidir. İptal edilen transaction roll back olur ve veri tabanı üzerinde herhangi bir etkiye sahip olmaz. Fakat SQL Server’da crash olduğunda undone yapısı karmaşık bir yapıya sahiptir. Bu işlem yoğunluğu içinde tutarlığı sağlayacak olan işlem günlüğü (Log Records) dür. Crash olduğunda transactionlar roll forward (REDO) edilerek commit edilir ve in-flight (havada) kalan transaction’lar roll back yaparak commit edilmez. Bu süreçte bize consistent (tutarlılık) sağlar.
Peki bu recovery esnasında nereye hangi zamana nasıl döneceğini SQL Server nereden biliyor. Bunu SQL Server İşlem günlüğü (Log Records) ile transaction işlemlerini sıraya koyarak kendi page yapısı içine yazar. Yazılan bu bilgiler ardışık olarak birbirini takip eder. SQL Server bu yapıyı LSN ile tanımlamıştır. LSN (Log Sequence Number) sürekli artan, eşsiz olarak tanımlanmış 3 parçalı bir sayıdır. Ve her işlem kendinden önceki ile bağlantılıdır. Bu LSN bilgisi 8192 byte’lık page içerisinde bulunan 96 byte’lık Header diye adlandırılan bölüm içerisinde tutulmaktadır. Kurtarma işleminde bu kısım içinde LSN kayıtları okunarak işlemler gerçekleştirilir. Checkpoint operasyonları, dirty page’leri diske commit etmesinden dolayı crash operasyonları ile doğrudan ilişkilidir. Ne kadar kısa tutulursa bu dirty page’ler REDO da o kadar kısa süre beklerler.
Bir veri tabanının en önemli parçası olan İşlem Günlüğü (Log Records), veri tabanındaki tüm işlemlerinin garanti edildiği tek yerdir. Eğer işlem günlüğü bozulmuş veya hasar görmüşse veri tabanı recovery sağlanamaz. Ve bu da veri tabanının suspect moda düşmesine neden olur. Bu durumda da yedeklerden geri dönüş sağlamak ya da emergency mode ile kurtarma senaryolarına ele almak gerekir.
İşlem günlüğü, her veri tabanının düzgün bir şekilde çalışması için tutulan özel bir dosyadır. Kurtarma sırasında bu dosya okunur. Bu veri tabanı üzerinde oluşacak işlemler için rollback veya commit, bunlar için kendinde yer ayırır. Örneğin, 25 MB’lık bir insert işlemi için işlem günlüğü 50 MB’lık yer ayırır. Bu yer ayırmalar log blokları içinde gerçekleşir.
Yeni bir veri tabanı oluşturulduğunda işlem günlüğü boştur. İşlemler yapılmaya başladıkça işlem günlüğü sıralı olarak kayıtlarını yapmaya başlar. Birden fazla işlem günlüğü oluşturmanın performansa etkisi olmayacaktır.
İşlem günlükleri transaction log içerisine eş zamanlı işlemlerde serpiştirilebilir. Bu işlemler LSN ile ilişkilidir. Bu yüzden bu işlemleri bir işlem günlüğü içinde gruplandırmaya gerek yoktur. LSN ‘ler burada bir zaman damgası görevi görürler.
İşlem günlüğünden flush edilerek Transaction Log dosyaları içine yazılan datalar commit olmuştur. Transaction log doyslarıda kendi içinde küçük chunk’lara (VLF) bölünürler. Buradaki amaç işlem günlüğünün daha kolay bir şekilde yönetilmesidir. Bir VLF dolduğunda işlem günlüğündeki diğer VLF’e yazmaya devam edilir ve bu adım otomatik olarak ilerler. Buradaki değer bizim veri tabanı için vermiş olduğumuz autogrowth değerleri ile ilişkilidir. Günün sonunda doldurduktan sonra yer kalmayacağını düşünebilirsiniz fakat buradaki mekanizma dataların yazılmasından farklı şekilde ilerler.
Transaction Log Dosyasının Fiziksel Mimarisi – 1
Transaction log dosyaları (ldf) dairesel bir yapıdadır. Ve bu ldf içerisinde bulunan işlem günlüğü (Log Records) içindeki işlem bilgilerinin başladıktan truncate (temizlenme) anına kadarki bilgileri tutar. İşlem kaydı, işlem gününün sonuna ulaştığında yeniden başa döner ve daha önce orada olanın üzerine yazmaya başlar.
VLF içerisindeki log bilgilerinin etkin olup olmadığını kontrol edebilmek için işlem günlüğünü (Log Records) kontrol edebilirsiniz. Eğer tümü etkin değilse VLF truncated olarak işaretlenir. Bu işlem günlüğü tamamlandığında VLF’in üzerine yazılabileceği anlamına gelir. Bir VLF truncate edildiğinde hiçbir şekilde üzerine yazılmaz veya sıfırlanmaz. Sadece truncated edilmiş olarak işaretlenir ve daha sonra yeniden kullanılır. Yani VLF’ler active durumdan inactive duruma geçer. Bu işlem log truncate etme işlemidir, shrink etme işlemi ile karıştırmamak gerekir.
Truncated, işlem günlüğünün (Log Records) fiziksel boyutunu hiçbir zaman değiştirmez; yalnızca işlem günlüğünün hangi bölümlerinde etkin olup olmadığını değiştirir. VLF bilgilerine ulaşmak için DBCC LOGINFO scriptini kullanabilirsiniz. Status’u 0 olanlar inactive, 2 olanlar ise active VLF’leri gösterir.
Truncate Sonrası Transaction Log Dosyası
Active VLF’ler, işlem günlüğünün tüm active loglarını içeren logical log’u oluşturur. Veri tabanı kendisi, recoverye başladığında log içindeki active kayıtların içindeki min LSN sayısını bulur ve kurtarmaya buradan başlar. Fakar kurtarma esnasında nerede duracağını bilmez. Bu nedenle işlem günlüğünün sıfırlanmış bölümüne ya da LSN kaydı bir ardışık dizinde uymayana kadar devam eder. VLF’ler kesilip yenileri active duruma geldikçe, logical log, fiziksel transaction log içinde hareket eder ve sonunda aşağıdaki görseldeki gibi hareket eder.
Transaction Log Dosyasının Dairesel Doğası
DBCC LOGINFO Çıktısı
İşlem günlüğünü truncate edilmesinin önündeki en büyük engel üzün süredir devam eden aktif bir transactiondır ve bu işlem günlüğün de sonlanana kadar yer tutacaktır. Log backup alıncaya kadar bu işlem işlem günlüğünde durmaya devam edecektir. Bir işlem günlüğü en fazla 2 TB büyür ve daha fazla büyüyemediğinden dolayı 9002 hatası (AVAILABILITY_REPLICA) hatası görürsünüz. Bunun kontrolünü aşağıdaki script ile bulabilirsiniz.
select name,log_reuse_wait_desc from sys.databases d
Active transaction devam ederken işlem günlüğü hiçbir şekilde silinmemeli, bilinmeyen komutlar ile rebuild işlemi yapılmamalı, LOG BACKUP alırken NO_LOG veya TRUNCATE_ONLY seçeneklerini kullanarak truncate edilmemeli. Bu seçenekler işlem tutarsızlığına sebep olur ve veri tabanı recovery esnasında sorunlara sebep olabilir.
İşlem günlüğünün davranışı veri tabanın Recovery Model’ine bağlıdır. SQL Server 3 tür model recovery model’e sahiptir. FULL, SIMPLE ve BULK-LOGGED. Bu modellerden FULL recovery modeli her işlemin günlüğe (Log Records) kaydedildiği anlamına gelir. FULL recovery modelinde Full backup alındığında işlem günlüğü log backup alınana kadar truncate edilmez. Eğer point in time bir recovery düşünmüyorsanız bu modeli kullanmanıza gerek yoktur. Fakat veri tabanınınızı Mirroring, Always On mimarisinde kullanmak isterseniz FULL Recovery model seçeneğinden başka bir seçeneği seçme şansınız yoktur.
BULK-LOGGED Recovery modeli ise FULL Recovery modeli ile aynı şekilde işlem günlüğünü truncate etme semantiğine sahiptir. Fakat her işlemin günlüğe kaydedilmesine izin vermez ve minimum düzeyde günlüğe kaydeder. Böylece işlem günlüğündeki büyümeyi azaltır.
SIMPLE Recovery modeli, BULK-LOGGED Recovery modeli ile aynı işlem günlüğü modeline sahiptir. Ancak işlem günlüğü truncate etme semantiği tamamen farklıdır. SIMPLE Recovery modelinde log backup almak mümkün değildir. Bu durumda her checkpoint atıldığında günlük kaydının (Log) truncate edileceği anlamına gelir.
0 Yorum