2018年8月30日 星期四

交易紀錄檔 VLF的數量對效能影響


SQLSERVER中,不論DB在哪種復原模式下,所有的異動都會寫入交易紀錄檔。差別在於紀錄的方式以及清除的方式不同。因此,如果交易紀錄檔的效能不好,其影響是全面性的。

SQLSERVER會將交易紀錄檔(transaction log file / ldf)劃分成多個「虚擬紀錄檔(Virtual Log File - VLF)來管理。
SQL2014以前的版本(不含2014),當建立、手動或自動成長LDF時,系統會將其劃分成數個VLF,如下表所示:

增量的大小
VLF
< 64MB
4
>=64MB and < 1GB
8
>=1GB
16

SQL2014,則有不同的演算法:
如果成長的量小於現行log file size1/8,那麼就只增加一個VLF
如果大於現行log file size1/8,那麼則依照舊有的規則。

我們可以使用dbcc loginfo來查看VLF的資訊,這也是目前唯一的方法。
以下圖為例,該資料庫的交易紀錄檔(*.ldf),共被劃分為10VLF來管理。


所以,假設你建立資料庫時,LDF檔初始大小設為10MB,那麼資料庫建立後,LDF檔會被切割為4VLF。如果有設定自動成長,每次成長10MB,那麼每成長一次,就會再多出4VLF
在一個繁忙的系統中,交易紀錄檔的初始大小如果不夠大,在系統運作的過程中,則會經常觸發自動成長。每次成長,系統就會將該空間依前述的規則切成數個VLF,因此VLF的數量也會增加。
自動成長所分配的空間,加入到原本的ldf檔的尾部。在邏輯上它會是連續的。但從實體上看,則整個ldf檔會是由許多不連續的空間所組成。因此,如果你的VLF的數量越多,表示你的交易紀錄檔(ldf)破碎的越嚴重。通常這被稱為 Transaction Log Fragmentation - 交易紀錄碎片,或者 VLF Fragmentation

我們知道,一般資料檔的碎片,會影響效能,所以我們通常會定期的檢查並重整。

~~~對於交易紀錄檔的碎片呢?

或許大家也跟我初次聽到時一樣~~~「啥….交易紀錄也有碎片!!!」

交易紀錄碎片的定義是比較粗糙的,一般是使用交易紀錄檔中VLF的數量來評估。也就是VLF的數量越多,表示交易紀錄的碎片越嚴重。
至於VLF的數量超過多少個才應該進行一些處理,這並沒有一個確切的標準可供遵循。一般的建議是VLF不要超過1000個。

我們可以使用下列指令,來取得所有DBVLF的計數:

DECLARE @query varchar(1000),
  @dbname varchar(1000),
  @count int
SET NOCOUNT ON
DECLARE csr CURSOR FAST_FORWARD READ_ONLY
FOR
SELECT name
FROM sys.databases

CREATE TABLE ##loginfo
(
  dbname varchar(100),
  num_of_rows int)
OPEN csr
FETCH NEXT FROM csr INTO @dbname
WHILE (@@fetch_status <> -1)
BEGIN

CREATE TABLE #log_info
(
  RecoveryUnitId tinyint,
  fileid tinyint,
  file_size bigint,
  start_offset bigint,
  FSeqNo int,
[status] tinyint,
  parity tinyint,
  create_lsn numeric(25,0)
)

SET @query = 'DBCC loginfo (' + '''' + @dbname + ''') '
INSERT INTO #log_info
EXEC (@query)
SET @count = @@rowcount
DROP TABLE #log_info
INSERT ##loginfo
VALUES(@dbname, @count)
FETCH NEXT FROM csr INTO @dbname

END

CLOSE csr
DEALLOCATE csr

SELECT dbname,
  num_of_rows
FROM ##loginfo
--WHERE num_of_rows >= 50
ORDER BY dbname

DROP TABLE ##loginfo

接下來,我們實際測試一下,交易紀錄的碎片真的對效能有影響嗎?

首先我建立兩個DB
VLF_LARGE模擬有許多的VLF,它的LOG的初始值10MB,然後用alter database的指令不斷的加LOG空間,以模擬自動成長,目地是增加VLF的數量。
VLF_SMALL模擬有較少的VLF,它的LOG的初始值直接給4GB

 
VLF_SMALL
VLF_LARGE
資料檔大小
4GB
4GB
資料檔路徑
H:\SQLData
H:\SQLData
LOG檔大小
4GB
4GB
LOG檔路徑
G:\SQLLog
G:\SQLLog
復原模式
Full
Full
VLF數量
16
4,008

接著我在兩個DB各建立一個空的TABLE,然後測試從另一個DBTABLE(300萬筆),將資料一次性新增進來。並且再測試updatedelete

經過四輪的測試,結果如下表
DML
VLF_SMALL
VLF_LARGE
1
2
3
4
平均
1
2
3
4
平均
INSERT
24
18
18
17
19.25
16
24
23
25
22
UPDATE
5
4
7
5
5.25
9
8
10
14
10.25
DELETE
7
5
11
7
7.5
17
10
21
15
15.75



從結果來看,VLF Fragmentation確實對這種大資料量的操作效能有所影響,我這個案例是測試300萬筆,是一個較大IO的操作。

由於交易紀錄是循序性的寫入,本文所測試的這種大資料量的操作,可能一次的操作所產生的交易紀錄量,就會跨許多不同的VLF才能儲存,因此若VLF碎片過高,會影響寫入的效能。

如果是小資料量的操作(如OLTP),由於交易紀錄量小(一次交易量不超過1VLF),則不受VLF碎片的影響。

上述的測試,其DB後端storage是傳統的旋轉式硬碟。

有趣的是,後來我改用本機的SSD硬碟進行上述測試,則不論VLF_LARGEVLF_SMALL,兩者對於DML的效能幾乎完全一致,也就是說如果DB的儲存媒體是SSD,則DML的效能不受VLF Fragementation的影響。這跟索引碎片在SSD的結果類似,碎片之所以在傳統旋轉式硬碟上會影響效能,主要是磁頭的物理移動頻繁所致,而SSD由於其存取的方式不同,所以沒有這個問題。




VLF數量對recovery效能的影響
VLF數量的多寡,對於recovery的影響則是巨大的,如果你的資料庫在啟動時需要很久才會online,且狀態處於recovery pending,那麼這篇文章會對你有所幫助。

我們做一個簡單的測試,有興趣的朋友也可以跟著試試看。

CREATE DATABASE [VLF_SMALL]
 ON  PRIMARY
( NAME = N'VLF_SMALL', FILENAME = N'H:\SQLData\VLF_SMALL.mdf' , SIZE = 2GB , FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'VLF_SMALL_log', FILENAME = N'G:\SQLLog\VLF_SMALL_log.ldf' , SIZE = 2GB , FILEGROWTH = 10%)
GO
CREATE DATABASE [VLF_LARGE]
 ON  PRIMARY
( NAME = N'VLF_LARGE', FILENAME = N'H:\SQLData\VLF_LARGE.mdf' , SIZE = 2GB , FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'VLF_LARGE_log', FILENAME = N'G:\SQLLog\VLF_LARGE_log.ldf' , SIZE = 1MB , FILEGROWTH = 10%)
GO

use master
go
declare @x int
declare @ssql varchar(1024)
set @x=10
while (@x<=2048)
   begin
      set @x=@x+1
      set @ssql='alter database VLF_LARGE modify file ( name = ''VLF_LARGE_log'', size = '+convert(varchar,@x)+'MB )'
      exec (@ssql)
   end
go

dbcc loginfo('VLF_LARGE')   --查看VLF數量有8160
go
dbcc loginfo('VLF_SMALL')   --查看VLF數量有16
go

資料庫相關指標如下
 
VLF_SMALL
VLF_LARGE
資料檔大小
2GB
2GB
資料檔路徑
H:\SQLData
H:\SQLData
LOG檔大小
2GB
2GB
LOG檔路徑
G:\SQLLog
G:\SQLLog
復原模式
Full
Full
VLF數量
16
8,160


這兩個 DB都是空的,剛剛建立,其中沒有任何的user tables。接著我們將它offline後再online,並計算online所需的時間,指令如下:
declare @x datetime
alter database VLF_SMALL set offline

set @x=getdate()
alter database VLF_SMALL set online

select datediff(ss,@x,getdate())





由上圖可見,VLF_SAMLLrecovery僅需1秒鐘。而VLF_LARGErecovery49秒,兩者recovery的速度差異非常的大。

SQL SERVER在執行recovery時,一次掃瞄一個VLF,當VLF數目多,會有較頻繁的VLF切換,故導致recovery所需時間的增加。
當你的SQL SERVER啟動時DB StartUp需要太長的時間,請檢查DBVLF數量是否太多。

VLF的數量及大小多少才適合,有幾個重要的參考指標:
一、每個VLF size不超過500MB
二、在SQL2012VLF數量超過1000個時,當SQL startrestoreattach時會有警告。
三、VLF的數量上限不要超過1000個
四、VLF數量的甜蜜點(sweet spot)為100個。

當超過1萬個VLF時,如果DBrecovery的情況時,則系統會發出警告,但警告的內容會告訴你說DB超過1仟個VLF時可能導致long startup and backup效能問題。但其實只要有recovery的動作,如offline/onlinerestore都會跳訊息。訊息如下:
Database dbName has more than n virtual log files which is excessive. Too many virtual log files can cause long startup and backup times. Consider shrinking the log and using a different growth increment to reduce the number of virtual log

VLF的數量及大小,也是影響SQL SERVER效能的一個重要環節,就像是我們日常重整索引的碎片,VLF碎片也需要定期的關注。

下一篇文章,我想接著談談VLF碎片的解決方法以及交易紀錄檔的壓縮(Shrink)

沒有留言:

張貼留言