資料庫壓測好工具-SQLQueryStress

前文

隨著業務量增長,資料庫的複雜程度也會成正比增長

這裡跟大家分享一個好用壓測資料庫工具SqlQueryStress

在Dev可以模擬高併發時產生的問題,下面會分享我之前Prod遇到問題並解決問題過程

詳細資訊可以看SQLServer-Merge-condition-problem

SQLQueryStress介紹

在執行效能調教和測試高併發產生問題時,我們會關注幾個特別資訊

  1. CPU執行時間
  2. logical read數值
  3. Total執行時間

在此工具都有相對應的資訊提供給我們觀看

SQLQueryStress可以讓我們輸入要重複執行次數跟使用多少個Thread來執行.

Total Exceptions可以協助查看目前語法執行上有多少錯誤產生(這個功能在高併發驗證問題很有幫助)

Sample Data

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
CREATE TABLE [dbo].[PriceLimitation](
[CategoryID] [int] NOT NULL,
[ProdcutGroupID] [smallint] NOT NULL,
[UserID] [int] NOT NULL,
[StakeAmount] [numeric](18, 4) NOT NULL,
[ProductID] [smallint] NOT NULL,
CONSTRAINT [PK_PriceLimitation] PRIMARY KEY CLUSTERED
(
[UserID] ASC,
[CategoryID] ASC,
[ProductID] ASC,
[ProdcutGroupID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TYPE [dbo].[uftt_PriceLimit] AS TABLE(
[CategoryID] [int] NOT NULL,
[ProdcutGroupID] [smallint] NOT NULL,
[UserID] [int] NOT NULL,
[StakeAmount] [numeric](18, 4) NOT NULL,
[ProductID] [smallint] NOT NULL,
PRIMARY KEY CLUSTERED
(
[UserID] ASC,
[CategoryID] ASC,
[ProductID] ASC,
[ProdcutGroupID] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
CREATE OR ALTER PROC [dbo].[CalculateStake]
@CategoryID int,
@ProductID smallint ,
@ProdcutGroupID smallint,
@PriceLimit [uftt_PriceLimit] readonly
AS
BEGIN
SET NOCOUNT ON;

MERGE INTO [dbo].[PriceLimitation] t1
USING @PriceLimit t2
ON t1.UserID = t2.UserID
AND t1.ProdcutGroupID= @ProdcutGroupID
AND t1.CategoryID=@CategoryID
AND t1.ProductID = @ProductID
WHEN MATCHED THEN
UPDATE SET t1.StakeAmount = t1.StakeAmount + t2.StakeAmount
WHEN NOT MATCHED THEN
INSERT VALUES(@CategoryID, @ProdcutGroupID, t2.UserID ,t2.StakeAmount, @ProductID);
END

執行語法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
DECLARE @CategoryID int,
@ProductID smallint ,
@ProdcutGroupID smallint,
@PriceLimit [uftt_PriceLimit]

declare @from int
SELECT @from = ROUND(RAND(CAST(NEWID() as varbinary)) * 500,0)
SELECT @CategoryID = CAST(ROUND(RAND(CAST(NEWID() as varbinary)) * 123,0) as int) % 4 +1
SELECT @ProductID = CAST(ROUND(RAND(CAST(NEWID() as varbinary)) * 123,0) as int) % 5 +1
SELECT @ProdcutGroupID = CAST(ROUND(RAND(CAST(NEWID() as varbinary)) * 731,0) as int) % 20 +1

;with cte as(
select @from as num
union all
select num + 1 as num from cte
where num < @from+500
)
insert into @PriceLimit ([UserID],[StakeAmount],CategoryID,[ProductID],[ProdcutGroupID])
select num,100,@CategoryID,@ProductID,@ProdcutGroupID
from cte
option(MAXRECURSION 0);

exec [dbo].[CalculateStake] @CategoryID,@ProductID,@ProdcutGroupID,@PriceLimit

使用SqlQueryStress重現問題

基本script建立好後,我們可以利用SqlQueryStress來進行壓力測試.

  • Number of Iterations 設定成 100
  • Number of Threads 設定成 100

使用100 Thread,重複跑100次.

之後就可以重現Prod出現的Merge問題了,有了這個Baseline我們就可以開始進行優化改善了.

小結

SqlQueryStress這個工具可以很快速幫助我們模擬許多高併發問題,但在使用這工具時偶爾會遇到UI卡住或無法停止問題,這時候就需要強制停止應用程式.

此文作者:Daniel Shih(石頭)
此文地址https://isdaniel.github.io/SQLQueryStress-Intro/
版權聲明:本博客所有文章除特別聲明外,均採用 CC BY-NC-SA 3.0 TW 許可協議。轉載請註明出處!


如果本文對您幫助很大,可街口支付斗內鼓勵石頭^^