第1页 / 共14页
第2页 / 共14页
第3页 / 共14页
第4页 / 共14页
第5页 / 共14页
第6页 / 共14页
第7页 / 共14页
第8页 / 共14页
数据库性能测试报告.docx
数据库性能测试目的：得出TPS值
测试环境
测试流程
1.新建数据库性能测试账号：按照系统提示输入密码：
2.在性能测试账号下，新建测试数据库
3.初始化性能测试数据库
4. 开始测试
❖模拟50个客户端，一个线程
❖模拟50个客户端，两个线程
❖模拟50个客户端，使用扩张调用接口，一个线程
❖模拟50个客户端，使用扩张调用接口，两个个线程
❖模拟80个客户端，一个线程
❖模拟80个客户端，两个线程
❖模拟100个客户端，一个线程
❖模拟100个客户端，五个线程
按照目前线上客户端连接数6测试为
在主从流环境下的性能测试
❖模拟6个客户端，1个线程
❖模拟50个客户端，一个线程
❖模拟80个客户端，一个线程
拯救数据库项目--数据库性能测试报告
数据库性能测试目的：得出 TPS 值
测试环境
硬件配置：CPU:E5-2660（双路）,内存：64G, 磁盘：7 块 SSD，做 raid10
软件工具参考：https://www.postgresql.org/docs/9.3/static/pgbench.html
pgbench 是一个简单的给 PostgreSQL 做性能测试的程序。它反复运行同样的 SQL 命令序
列，可能是在多个并发数据库会话上头，然后检查平均的事务速度（每秒的事务数 tps）。
缺省的时候，pgbench 测试一个（松散的）接近 TPC-B 的情况，每个事务包括五个
SELECT，UPDATE，和 INSERT 命令
测试工具输出如下：
transaction type: TPC-B (sort of)
scaling factor: 10
number of clients: 10
number of transactions per client: 1000
number of transactions actually processed: 10000/10000
tps = 85.184871 (including connections establishing)
tps = 85.296346 (excluding connections establishing)
我们主要关心的是最后的输出报告中的 TPS 值，里面有两个,一个是包含连接开销(including),
另一个是不包含连接开销的(excluding),这个值是反映的每秒处理的事务数，这里也列出了每个
事务所消耗的平均时间。一般认为能将硬件用到极致，速度越快越好。
测试流程
1.新建数据库性能测试账号：按照系统提示输入密码：
createuser -P xhj;
2.在性能测试账号下，新建测试数据库
createdb pgbench --owner xhj --encoding=UTF8;
3.初始化性能测试数据库
[root@hjd-master-db bin]# ./pgbench -U xhj -i pgbench
creating tables...
100000 of 100000 tuples (100%) done (elapsed 0.06 s, remaining 0.00 s)
vacuum...
set primary keys...
done.
4. 开始测试
❖ 模拟 50 个客户端，一个线程
10 秒 50 个并发，共插入 31557 条数据，每条数据插入耗时 15.844 毫秒；平均每秒插入
3156 条数据；
[root@hjd-master-db bin]# ./pgbench -U xhj -M simple -c 50 -T 10 -r pgbench
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: simple
number of clients: 50
number of threads: 1
duration: 10 s
number of transactions actually processed: 31557
latency average: 15.844 ms
tps = 3149.427914 (including connections establishing)
tps = 3150.644350 (excluding connections establishing)
statement latencies in milliseconds:
0.001971
\set nbranches 1 * :scale
0.000668
\set ntellers 10 * :scale
0.000597
\set naccounts 100000 * :scale
0.000933
\setrandom aid 1 :naccounts
0.000624
\setrandom bid 1 :nbranches
0.000580
\setrandom tid 1 :ntellers
0.000685
\setrandom delta -5000 5000
0.040691
BEGIN;
0.155414
UPDATE pgbench_accounts SET abalance = abalance + :delta
WHERE aid = :aid;
0.105338
SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
12.632544
UPDATE pgbench_tellers SET tbalance = tbalance + :delta
WHERE tid = :tid;
2.380206
UPDATE pgbench_branches SET bbalance = bbalance + :delta
WHERE bid = :bid;
0.098487
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime)
VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
0.127183
END;
❖ 模拟 50 个客户端，两个线程
10 秒 50 个并发，共插入 33820 条数据，每条数据插入耗时 14.784 毫秒；平均每秒插入 3382
条数据；
[root@hjd-master-db bin]# ./pgbench -U xhj -M simple -c 50 -T 10 -j 2 -r pgbench
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: simple
number of clients: 50
number of threads: 2
duration: 10 s
number of transactions actually processed: 33820
latency average: 14.784 ms
tps = 3377.368952 (including connections establishing)
tps = 3378.641522 (excluding connections establishing)
statement latencies in milliseconds:
0.001846
0.000643
0.000569
0.000880
0.000600
0.000583
0.000673
0.030064
0.143514
\set nbranches 1 * :scale
\set ntellers 10 * :scale
\set naccounts 100000 * :scale
\setrandom aid 1 :naccounts
\setrandom bid 1 :nbranches
\setrandom tid 1 :ntellers
\setrandom delta -5000 5000
BEGIN;
UPDATE pgbench_accounts SET abalance = abalance + :delta
WHERE aid = :aid;
0.092524
11.975457
tid = :tid;
2.198434
WHERE bid = :bid;
SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE
UPDATE pgbench_branches SET bbalance = bbalance + :delta
0.088867
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES
(:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
0.115995
END;
❖ 模拟 50 个客户端，使用扩张调用接口，一个线程
10 秒 50 个并发，共插入 29004 条数据，每条数据插入耗时 17.239 毫秒；平均每秒插
入 2900 条数据；
[root@hjd-master-db bin]# ./pgbench -U xhj -M extended -c 50 -T 10 -r pgbench
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: extended
number of clients: 50
number of threads: 1
duration: 10 s
number of transactions actually processed: 29004
latency average: 17.239 ms
tps = 2892.360395 (including connections establishing)
tps = 2893.421461 (excluding connections establishing)
statement latencies in milliseconds:
0.002088
\set nbranches 1 * :scale
0.000689
\set ntellers 10 * :scale
0.000591
\set naccounts 100000 * :scale
0.000938
\setrandom aid 1 :naccounts
0.000629
\setrandom bid 1 :nbranches
0.000586
\setrandom tid 1 :ntellers
0.000693
\setrandom delta -5000 5000
0.048848
BEGIN;
0.181780
UPDATE pgbench_accounts SET abalance = abalance + :delta
WHERE aid = :aid;
0.122386
SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
13.693402
UPDATE pgbench_tellers SET tbalance = tbalance + :delta
WHERE tid = :tid;
2.627090
UPDATE pgbench_branches SET bbalance = bbalance + :delta
WHERE bid = :bid;
0.124916
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime)
VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
0.137854
END;
❖ 模拟 50 个客户端，使用扩张调用接口，两个个线程
10 秒 50 个并发，共插入 31059 条数据，每条数据插入耗时 16.098 毫秒；平均每秒插入 3106 条
数据；
[root@hjd-master-db bin]# ./pgbench -U xhj -M extended -c 50 -T 10 -j 2 -r
pgbench
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: extended
number of clients: 50
number of threads: 2
duration: 10 s
number of transactions actually processed: 31059
latency average: 16.098 ms
tps = 3100.144891 (including connections establishing)
tps = 3101.375992 (excluding connections establishing)
statement latencies in milliseconds:
0.001838
\set nbranches 1 * :scale
0.000646
\set ntellers 10 * :scale
0.000572
\set naccounts 100000 * :scale
0.000882
\setrandom aid 1 :naccounts
0.000610
\setrandom bid 1 :nbranches
0.000589
\setrandom tid 1 :ntellers
0.000677
\setrandom delta -5000 5000
0.036120
BEGIN;
0.168477
UPDATE pgbench_accounts SET abalance = abalance + :delta
WHERE aid = :aid;
0.107377
SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
12.985970
UPDATE pgbench_tellers SET tbalance = tbalance + :delta
WHERE tid = :tid;
2.406841
UPDATE pgbench_branches SET bbalance = bbalance + :delta
WHERE bid = :bid;
0.111336
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime)
VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
0.125953
END;
❖ 模拟 80 个客户端，一个线程
10 秒 80 个并发，共插入 25693 条数据，每条数据插入耗时 31.137 毫秒；平均每秒插入
2569 条数据；
[root@hjd-master-db bin]# ./pgbench -U xhj -M simple -c 80 -T 10 -r pgbench
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: simple
number of clients: 80
number of threads: 1
duration: 10 s
number of transactions actually processed: 25693
latency average: 31.137 ms
tps = 2561.312547 (including connections establishing)
tps = 2562.214251 (excluding connections establishing)
statement latencies in milliseconds:
0.002135
0.000669
0.000576
0.000872
0.000608
0.000580
0.000670
0.062661
0.193747
\set nbranches 1 * :scale
\set ntellers 10 * :scale
\set naccounts 100000 * :scale
\setrandom aid 1 :naccounts
\setrandom bid 1 :nbranches
\setrandom tid 1 :ntellers
\setrandom delta -5000 5000
BEGIN;
UPDATE pgbench_accounts SET abalance = abalance + :delta
SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
UPDATE pgbench_tellers SET tbalance = tbalance + :delta
UPDATE pgbench_branches SET bbalance = bbalance + :delta
WHERE aid = :aid;
0.114447
26.501614
WHERE tid = :tid;
3.180320
WHERE bid = :bid;
0.101617
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime)
VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
0.139868
END;
❖ 模拟 80 个客户端，两个线程
10 秒 80 个并发，共插入 26573 条数据，每条数据插入耗时 30.106 毫秒；平均每秒插入
2657 条数据；
[root@hjd-master-db bin]# ./pgbench -U xhj -M simple -c 80 -T 10 -j 2 -r pgbench
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: simple
number of clients: 80
number of threads: 2
duration: 10 s
number of transactions actually processed: 26573
latency average: 30.106 ms
tps = 2650.339678 (including connections establishing)
tps = 2651.435714 (excluding connections establishing)
statement latencies in milliseconds:
0.002364
0.000703
0.000638
0.000944
0.000672
0.000630
0.000734
0.037288
0.216944
\set nbranches 1 * :scale
\set ntellers 10 * :scale
\set naccounts 100000 * :scale
\setrandom aid 1 :naccounts
\setrandom bid 1 :nbranches
\setrandom tid 1 :ntellers
\setrandom delta -5000 5000
BEGIN;
UPDATE pgbench_accounts SET abalance = abalance + :delta
WHERE aid = :aid;
0.099963
25.984207
WHERE tid = :tid;
3.072375
WHERE bid = :bid;
SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
UPDATE pgbench_tellers SET tbalance = tbalance + :delta
UPDATE pgbench_branches SET bbalance = bbalance + :delta
0.094614
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime)
VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
0.127126
END;
❖ 模拟 100 个客户端，一个线程
10 秒 95 个并发，共插入 23180 条数据，每条数据插入耗时 40.984 毫秒；平均每秒插入
2318 条数据；
相关推荐
2023年江西萍乡中考道德与法治真题及答案.doc
2012年重庆南川中考生物真题及答案.doc
2013年江西师范大学地理学综合及文艺理论基础考研真题.doc
2020年四川甘孜小升初语文真题及答案I卷.doc
2020年注册岩土工程师专业基础考试真题及答案.doc
2023-2024学年福建省厦门市九年级上学期数学月考试题及答案.doc
2021-2022学年辽宁省沈阳市大东区九年级上学期语文期末试题及答案.doc
2022-2023学年北京东城区初三第一学期物理期末试卷及答案.doc
2018上半年江西教师资格初中地理学科知识与教学能力真题及答案.doc
2012年河北国家公务员申论考试真题及答案-省级.doc
2020-2021学年江苏省扬州市江都区邵樊片九年级上学期数学第一次质量检测试题及答案.doc
2022下半年黑龙江教师资格证中学综合素质真题及答案.doc
