SQL 壓力測試實戰篇

05-28

SQL 壓力測試實戰篇

來自專欄有關SQL

針對資料庫的測試，市面上已經有很多工具了，Google 上搜一下 sql testing tool ，他為你選出的工具，琳琅滿目，看花雙眼。

比如：40+ Best Database Testing Tools - Popular Data Testing Solutions 這篇文章列舉了總共 43 個測試工具，可以用來完成 SQL 的測試，包括生成測試數據，功能性測試，邏輯性測試，當然還有壓力測試。

在這裡羅列幾個工具，以便有應用場景的時候，可以拿起來直接用。

現在的應用系統，一般都會有好幾層，比如 UI, Access Layer, Business Layer, 資料庫。每一層都有自己獨立的測試工具，下面的工具可能會同時支持其中1,2層對應的測試。

Test Data Generator:

1. data factory

商業用資料庫數據生成工廠。主要特點有：

1.生成數據

2.壓力測試

2. MockupData

3. DTM Data Generator

SQL-Based Tools:

1. SQL Server Database tools

2. SQL Test: uses tSQLt framework to make test on views, stored procedures and functions。使用的是 tSQLt 測試框架，用來測試試圖，存儲過程以及函數

3. tSQLt:dedicated to sql server

4. oracle sql developer:

5. NoSQLUnit:

6.NoSQLMap:

7. SeLite:combination of selenium and SQLite, known as Selenium extension

8. SQLMap: open source tool for SQLite, MySQL, SQL Server, DB2, PostgreSQL

DB based Level Testing tools:

1. HammerDB: open source tool for database load testing, used as benchmarking tool for sql server, mysql, db2, oracle。這款工具的優點在於，他是 open source即開源的工具，意味著你可以完全看到他的測試方案。看不到測試方案的工具，其實對自己理解測試報告，是有障礙的，比如並發是怎麼協調的，測試用的邏輯腳本是不是能體現出測試要求。

UI Enhanced Tools: 自帶絢麗UI的一體化工具

1. Toad。這款工具就不用多說了，和 Oracle 打交道並且是大廠項目的話，Toad 基本是必用工具。

2.DBVisualizer: toad 旗下的可視化開發工具，最有用的一點是可以快速建立ER圖

3. Database benchmark: open source tool for performing stress testing on a database that contains a large volume of data. Graphic visualization and reporting options are advanced features of this tool. 開源工具，可視化配置測試用例，性能測試報告。

針對 MySQL, Oracle, MariaDB :

1. Navicat: for mySQL, also manage data in sql server , oracle, mysql, SQLite

2. LoadRunner for Oracle:

在 Brent Ozar 的博客上找到一個非常棒的工具： SQLQueryStress, 並且是open source 開源的。可以從github 上下載並使用。

https://www.brentozar.com/archive/2015/05/how-to-fake-load-tests-with-sqlquerystress

說了那麼多，這才是本文的主角。

如果你是手機的 Geek, 我想你一定會喜歡 Andriod.

iPhone 當然也有玩頭，只不過 App Store 上線一應用，審批實在麻煩。

這款 SQLQueryStress 就像是 Andriod 上的應用一樣，隨你拆開來玩，一睹內部玄妙的機關。Github 是個重武器庫，當今的軟體替代品差不多都能找到。大家看到的題圖，都是用了 GitHub 上的小爬蟲，從 Instagram 上扣下來的。

首先交代測試環境：

1. 測試工具： SQLQueryStress

下載路徑：https://github.com/ErikEJ/SqlQueryStress

2. 測試用 SQL Server 伺服器：

個人的vmware 虛擬機，配置較低

Windows Server 2012

2GB RAM

Intel Core i3CPU @3.07 GHZ

20GB SSD

3. 測試方案：

3.1 用戶響應時間：讀

3.2 伺服器 CPU 利用率

今天暫時針對 CPU 做測試。

1. 10 個並發的情況下, 平均響應時間在 0.4973 秒。可以說妥妥的。但細心的朋友肯定注意到，其實這裡只是單純的運行同一個 SQL，想想是不是哪裡不夠嚴謹，哪裡可以提高，哪裡是瓶頸？

2. 200個並發下，平均響應時間已經超過 1s, 接近 2s 了

當這 200個並發，一直在運行著查詢的時候，即使是同一個查詢，響應時間也已經不可接受了。

我們在本次實驗中，應用的是同一段腳本，同一段腳本運行完了之後，執行計劃會被緩存起來，相應的數據，也會在資料庫的緩存中保留下來，這樣的環境下，連續的發出查詢請求，其實對伺服器的CPU考核已經不是很嚴格了，不需要硬解析，不需要從資料庫磁碟拿出數據，但並發的響應時間依然不合格。

假如我們用一個總調度，根據線程ID來隨機抽取一個只讀存儲過程進行查詢，這樣的查詢對CPU的考驗才叫嚴格與真實。

剛才我們用10個並發的時候，系統沒有明顯的壓力，響應時間夠快。

這次我們就做10個存儲過程，當10個並發同時調用這10個存儲過程的時候，檢查響應時間，是不是依然合格！

1 利用 AdventureWorks 資料庫，編寫10個簡單的存儲過程

2 編寫一個總調度存儲過程，根據各自的線程ID，即 session_id，來隨機調取其中一個存儲過程

10 個簡單的存儲過程可以這麼快速生成：

DECLARE @sql_body NVARCHAR(max);

DECLARE my_cur CURSOR

FOR

SELECT TOP 10 create procedure + schema_name(schema_id) + .get + NAME + as begin + select count(*) as cnt from + schema_name(schema_id) + . + NAME + end + CHAR(10) + go

FROM sys.objects

WHERE type_desc = user_table

OPEN my_cur

FETCH NEXT

FROM my_cur

INTO @sql_body

PRINT @sql_body

WHILE @@fetch_status = 0

BEGIN

FETCH NEXT

FROM my_cur

INTO @sql_body

PRINT @sql_body;

END

CLOSE my_cur

DEALLOCATE my_cur

總調度存儲過程：

CREATE PROCEDURE dbo.usp_randRunQuery @threads INT = 10

BEGIN

DECLARE @sql_body NVARCHAR(max);

CREATE TABLE #temp_procs (

procedurename NVARCHAR(max)

,id INT identity(1, 1)

)

INSERT INTO #temp_procs

SELECT TOP 10 exec + schema_name(schema_id) + . + NAME

FROM sys.objects

WHERE type_desc = SQL_STORED_PROCEDURE

ORDER BY create_date DESC

SELECT @sql_body = procedurename

FROM #temp_procs

WHERE id = @@spid % @threads

EXEC sp_executesql @stmt = @sql_body;

DROP TABLE #temp_procs

END

當 10 個線程，調用不同的存儲過程時，響應速度依舊可以。有峰值，但不過百

在執行過程中，我發現並不是每一次的 iteration 都是固定的線程，有可能每一次執行，分配的處理線程會改變，因此需要多循環幾次，來保證每一個存儲過程都被調用，並且一段時間內有足夠多的並發在運行

我的猜想是，到了一定的時間之後，相應時間會趨穩。

所以做了一個記錄每一個查詢的執行時間的功能，如下：

1 加一張Log表：

create table execution_log(execid bigint identity(1,1), spid int, procname nvarchar(2000),start_dt datetime, end_dt datetime)

2. 修改我們的調度過程

CREATE PROCEDURE dbo.usp_randRunQuery @threads INT = 10

BEGIN

DECLARE @execid BIGINT

DECLARE @sql_body NVARCHAR(2000);

CREATE TABLE #temp_procs (

procedurename NVARCHAR(2000)

,id INT identity(1, 1)

)

INSERT INTO #temp_procs

SELECT TOP 10 exec + schema_name(schema_id) + . + NAME

FROM sys.objects

WHERE type_desc = SQL_STORED_PROCEDURE

ORDER BY create_date DESC

SET IDENTITY_INSERT #temp_procs OFF

INSERT INTO #temp_procs (

,procedurename

)

SELECT 0

,procedurename

FROM #temp_procs

WHERE id = 6

SET IDENTITY_INSERT #temp_procs ON

SELECT @sql_body = procedurename

FROM #temp_procs

WHERE id = @@spid % @threads % 10

INSERT INTO execution_log (

spid

,procname

,start_dt

)

VALUES (

@@spid

,@sql_body

,getutcdate()

)

SELECT @execid = SCOPE_IDENTITY()

EXEC sp_executesql @stmt = @sql_body;

UPDATE execution_log

SET end_dt = getutcdate()

WHERE execid = @execid;

DROP TABLE #temp_procs

END

果不出所料，再多的請求，相應時間也穩定下來了