PostgreSQL DBA(8) - sysbench簡介
pgbench是一款開源的基準測試工具,支援多執行緒和多種資料庫(包括MySQL、PostgreSQL等);透過測試可衡量作業系統的CPU、磁碟IO、記憶體、POSIX執行緒效能和資料庫效能。
一、安裝
下載sysbench,當前最新版本是1.0.15
cd /tmp/install
wget -O "sysbench-1.0.15.zip"
unzip sysbench-1.0.15.zip
cd sysbench-1.0.15
執行安裝
yum install automake libtool mysql-devel postgresql-devel –y
./autogen.sh
./configure --with-pgsql --with-pgsql-includes=/appdb/atlasdb/pg11beta2/include --with-pgsql-libs=/appdb/atlasdb/pg11beta2/lib
make
make install
安裝成功
[root@localhost sysbench-1.0.15]# sysbench --version
sysbench 1.0.15
二、引數說明
透過sysbench --help命令可以檢視詳細的引數設定.
[root@localhost include]# sysbench --help
Usage:
sysbench [options]... [testname] [command]
Commands implemented by most tests: prepare run cleanup help
General options:
--threads=N number of threads to use [1]
...
其中:
oltp-tables-count
生成測試資料表數量
--threads=N
sysbench使用的工作執行緒數
--time=N
執行時長,以秒為單位
--report-interval=N
報告間隔,以秒為單位
--config-file=FILENAME
配置檔案
--db-driver=STRING
資料庫驅動mysql/pgsql
pgsql options:
--pgsql-host=STRING PostgreSQL server host [localhost]
--pgsql-port=N PostgreSQL server port [5432]
--pgsql-user=STRING PostgreSQL user [sbtest]
--pgsql-password=STRING PostgreSQL password []
--pgsql-db=STRING PostgreSQL database name [sbtest]
三、基本使用
連線資料庫,建立測試資料庫sysbench
$createdb sysbench
準備階段
執行以下命令,sysbench執行自帶的測試指令碼
sysbench /usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua \
--oltp-tables-count=10 --oltp-table-size=500000 \
--db-driver=pgsql --pgsql-host=192.168.26.103 --pgsql-port=5432 \
--pgsql-user=atlasdb --pgsql-db=sysbench \
prepare
命令輸出
Creating table 'sbtest1'...
Inserting 500000 records into 'sbtest1'
Creating secondary indexes on 'sbtest1'...
Creating table 'sbtest2'...
Inserting 500000 records into 'sbtest2'
Creating secondary indexes on 'sbtest2'...
Creating table 'sbtest3'...
Inserting 500000 records into 'sbtest3'
Creating secondary indexes on 'sbtest3'...
Creating table 'sbtest4'...
Inserting 500000 records into 'sbtest4'
Creating secondary indexes on 'sbtest4'...
Creating table 'sbtest5'...
Inserting 500000 records into 'sbtest5'
Creating secondary indexes on 'sbtest5'...
Creating table 'sbtest6'...
Inserting 500000 records into 'sbtest6'
Creating secondary indexes on 'sbtest6'...
Creating table 'sbtest7'...
Inserting 500000 records into 'sbtest7'
Creating secondary indexes on 'sbtest7'...
Creating table 'sbtest8'...
Inserting 500000 records into 'sbtest8'
Creating secondary indexes on 'sbtest8'...
Creating table 'sbtest9'...
Inserting 500000 records into 'sbtest9'
Creating secondary indexes on 'sbtest9'...
Creating table 'sbtest10'...
Inserting 500000 records into 'sbtest10'
Creating secondary indexes on 'sbtest10'..
執行基準測試
sysbench /usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua \
--report-interval=5 \
--threads=4 --time=120 --db-driver=pgsql --pgsql-host=192.168.26.103 --pgsql-port=5432 \
--pgsql-user=atlasdb --pgsql-db=sysbench \
run
執行完畢,4執行緒的執行過程和結果如下:
sysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 4
Report intermediate results every 5 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 5s ] thds: 4 tps: 107.50 qps: 2160.56 (r/w/o: 1513.97/430.79/215.80) lat (ms,95%): 51.94 err/s: 0.00 reconn/s: 0.00
[ 10s ] thds: 4 tps: 80.79 qps: 1611.00 (r/w/o: 1126.26/323.16/161.58) lat (ms,95%): 71.83 err/s: 0.00 reconn/s: 0.00
[ 15s ] thds: 4 tps: 105.45 qps: 2114.74 (r/w/o: 1482.66/421.19/210.89) lat (ms,95%): 61.08 err/s: 0.00 reconn/s: 0.00
...
[ 120s ] thds: 4 tps: 88.00 qps: 1762.33 (r/w/o: 1232.95/353.39/175.99) lat (ms,95%): 77.19 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 181496
write: 51856
other: 25928
total: 259280
transactions: 12964 (108.00 per sec.)
queries: 259280 (2160.08 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 120.0312s
total number of events: 12964
Latency (ms):
min: 18.34
avg: 37.02
max: 122.90
95th percentile: 58.92
sum: 479962.76
Threads fairness:
events (avg/stddev): 3241.0000/6.96
execution time (avg/stddev): 119.9907/0.01
四、參考資料
How to Benchmark Performance of MySQL & MariaDB using SysBench
How to Benchmark PostgreSQL Performance Using Sysbench
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/6906/viewspace-2374847/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- PostgreSQL DBA(87) - benchmarksql簡介SQL
- PostgreSQL DBA(7) - pgbench簡介SQL
- Sysbench使用簡介
- PostgreSQL DBA(18) - pg_waldump工具簡介SQL
- 【工具】sysbench 0.5 簡介
- PostgreSQL DBA(90) - Linux(stress-ng簡介)SQLLinux
- 《MySQL 8 DBA基礎教程》簡介MySql
- PostgreSQL DBA(3) - 日誌分析工具pgbadger簡介SQL
- PostgreSQL:引數簡介SQL
- PostgreSQL簡介及安裝SQL
- PostgreSQL DBA(45) - Hypothetical Indexes in PostgreSQLSQLIndex
- PostgreSQL中索引與CTE簡介SQL索引
- PostgreSQL DBA(35) - CTESQL
- PostgreSQL DBA(42) - localeSQL
- PostgreSQL DBA(133) - Extension(postgresql_anonymizer)SQL
- PostgreSQL簡介及安裝步驟SQL
- 使用sysbench測試mysql及postgresql(完整版)MySql
- PostgreSQL DBA(59) - Index(Bloom)SQLIndexOOM
- PostgreSQL DBA(53) - Index(BRIN)SQLIndex
- PostgreSQL DBA(58) - DBLinkSQL
- PostgreSQL DBA(48) - Index(GiST)SQLIndex
- PostgreSQL DBA(51) - Index(GIN)SQLIndex
- PostgreSQL DBA(52) - Index(RUM)SQLIndex
- PostgreSQL DBA(47) - Index(Btree)SQLIndex
- PostgreSQL DBA(43) - Index(Hash)SQLIndex
- PostgreSQL DBA(191) - CollationSQL
- PostgreSQL DBA(182) - HOTSQL
- PostgreSQL DBA(185) - watchSQL
- PostgreSQL DBA(55) - MVCC#8(對全表掃描的影響)SQLMVCC#
- PostgreSQL DBA(181) - Using PostgreSQL as a Data WarehouseSQL
- 面向 DBA 的 Linux Shell 指令碼簡介Linux指令碼
- PostgreSQL 原始碼解讀(240)- HTAB簡介SQL原始碼
- 簡單介紹PostgreSQL解析URL的方法SQL
- 8 語言模型簡介模型
- k8s 簡介K8S
- k8s簡介K8S
- sysbench安裝及簡單使用
- PostgreSQL DBA(175) - Cost EST(SeqScan)SQL