MySQL從5.1.4版開始帶有一個壓力測試工具mysqlslap,通過模拟多個并發用戶端通路mysql來執行測試,使用起來非常的簡單。通過mysqlslap -help可以獲得可用的選項,這裡列一些主要的參數,更詳細的說明參考官方手冊。
man mysqlslap 也可以看到有下面一條示例:
- mysqlslap --delimiter=";" --create="CREATE TABLE a (b int);INSERT INTO a VALUES (23)" --query="SELECT * FROM a" --concurrency=50 --iterations=200
- -a, --auto-generate-sql 自動生成測試表和資料
- --auto-generate-sql-load-type=name 測試語句的類型。取值包括:read,key,write,update和mixed(多種混合,預設)。
- -C, --compress 如果伺服器和用戶端支援都壓縮,則壓縮資訊傳遞
- --commit=N 多少條DML後送出一次
- --create-schema=name 指定資料庫測試,不要和:auto-generate-sql 參數一起使用!
- --detach=N 執行N條語句後斷開重連
- --concurrency 指定并發數
- -T, --debug-info 列印記憶體和CPU的資訊
- --number-of-queries=1000 指定查詢總次數
- -i, --iterations=5 重複多次(疊代多次)
- -e, --engine=name 指定引擎
- --only-print 隻輸出測試語句而不實際執行
- -q, --query=name 使用自定義腳本執行測試,例如可以調用自定義的一個存儲過程或者sql語句來執行測試。
- [root@localhost ~]# mysqlslap --auto-generate-sql --concurrency=50,100,500 --number-of-queries=1000 --iterations=5 --debug-info --engine=myisam,innodb -uroot -p
- Enter password:
- Benchmark
- Running for engine myisam
- Average number of seconds to run all queries: 0.108 seconds
- Minimum number of seconds to run all queries: 0.083 seconds
- Maximum number of seconds to run all queries: 0.119 seconds
- Number of clients running queries: 50
- Average number of queries per client: 20
- Benchmark
- Running for engine myisam
- Average number of seconds to run all queries: 0.127 seconds
- Minimum number of seconds to run all queries: 0.094 seconds
- Maximum number of seconds to run all queries: 0.150 seconds
- Number of clients running queries: 100
- Average number of queries per client: 10
- Benchmark
- Running for engine myisam
- Average number of seconds to run all queries: 0.182 seconds
- Minimum number of seconds to run all queries: 0.161 seconds
- Maximum number of seconds to run all queries: 0.204 seconds
- Number of clients running queries: 500
- Average number of queries per client: 2
- Benchmark
- Running for engine innodb
- Average number of seconds to run all queries: 0.305 seconds
- Minimum number of seconds to run all queries: 0.294 seconds
- Maximum number of seconds to run all queries: 0.322 seconds
- Number of clients running queries: 50
- Average number of queries per client: 20
- Benchmark
- Running for engine innodb
- Average number of seconds to run all queries: 0.315 seconds
- Minimum number of seconds to run all queries: 0.304 seconds
- Maximum number of seconds to run all queries: 0.338 seconds
- Number of clients running queries: 100
- Average number of queries per client: 10
- Benchmark
- Running for engine innodb
- Average number of seconds to run all queries: 0.515 seconds
- Minimum number of seconds to run all queries: 0.485 seconds
- Maximum number of seconds to run all queries: 0.548 seconds
- Number of clients running queries: 500
- Average number of queries per client: 2
- User time 2.48, System time 2.47
- Maximum resident set size 27284, Integral resident set size 0
- Non-physical pagefaults 55681, Physical pagefaults 0, Swaps 0
- Blocks in 0 out 0, Messages in 0 out 0, Signals 0
- Voluntary context switches 101688, Involuntary context switches 95927