SSD和HDD的pgbench性能对比

SSD的IO性能优于HDD，特别是随机IO。现在用的PC里就有一块普通硬盘（WD3200AAJS）和一块SSD(OCZ-VERTEX4),下面实际测一下，它们在作为数据库存储时的表现。

一,测试环境
CPU:Intel Core2 DuoCPU E7500 @2.93GHz
MEM:2G DDR3
OS: Win XP
DB:PostgreSQL 9.1.6

二，性能数据
WD3200AAJS：

OCZ-VERTEX4：

这台PC比较老，还是IDE接口，严重限制了OCZ-VERTEX4的潜能，但性能比起WD3200AAJS已经相当优越了。

三，pgbench测试数据
使用PostgreSQL的默认配置进行测试。结果显示，在最考验随机写入的测试(transaction type: TPC-B,scaling factor: 300)中,WD3200AAJS的tps为44，OCZ-VERTEX4的tps为775。

WD3200AAJS：
C:\Documents and Settings\chj>pgbench -c 50 -j 10 -t 100 -S pgdb100
starting vacuum...end.
transaction type: SELECT only
scaling factor: 100
query mode: simple
number of clients: 50
number of threads: 10
number of transactions per client: 100
number of transactions actually processed: 5000/5000
tps = 2319.326503 (including connections establishing)
tps = 6457.962531 (excluding connections establishing)

C:\Documents and Settings\chj>pgbench -c 50 -j 10 -t 100  pgdb100
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 100
query mode: simple
number of clients: 50
number of threads: 10
number of transactions per client: 100
number of transactions actually processed: 5000/5000
tps = 178.538523 (including connections establishing)
tps = 186.534720 (excluding connections establishing)

C:\Documents and Settings\chj>pgbench -c 50 -j 10 -t 100 -S pgdb100
starting vacuum...end.
transaction type: SELECT only
scaling factor: 300
query mode: simple
number of clients: 50
number of threads: 10
number of transactions per client: 100
number of transactions actually processed: 5000/5000
tps = 72.010603 (including connections establishing)
tps = 75.560511 (excluding connections establishing)

C:\Documents and Settings\chj>pgbench -c 50 -j 10 -t 100  pgdb100
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 300
query mode: simple
number of clients: 50
number of threads: 10
number of transactions per client: 100
number of transactions actually processed: 5000/5000
tps = 43.564647 (including connections establishing)
tps = 44.129713 (excluding connections establishing)

OCZ-VERTEX4：
C:\Documents and Settings\chj>pgbench -p 9100  -c 50 -j 10 -t 100 -S pgdb1
starting vacuum...end.
transaction type: SELECT only
scaling factor: 1
query mode: simple
number of clients: 50
number of threads: 10
number of transactions per client: 100
number of transactions actually processed: 5000/5000
tps = 2305.247430 (including connections establishing)
tps = 7798.504410 (excluding connections establishing)

C:\Documents and Settings\chj>pgbench -p 9100  -c 50 -j 10 -t 100  pgdb1
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: simple
number of clients: 50
number of threads: 10
number of transactions per client: 100
number of transactions actually processed: 5000/5000
tps = 565.058589 (including connections establishing)
tps = 666.951735 (excluding connections establishing)

C:\Documents and Settings\chj>pgbench -p 9100  -c 50 -j 10 -t 100 -S pgdb300
starting vacuum...end.
transaction type: SELECT only
scaling factor: 300
query mode: simple
number of clients: 50
number of threads: 10
number of transactions per client: 100
number of transactions actually processed: 5000/5000
tps = 1989.756480 (including connections establishing)
tps = 5504.493891 (excluding connections establishing)

C:\Documents and Settings\chj>pgbench -p 9100  -c 50 -j 10 -t 100  pgdb300
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 300
query mode: simple
number of clients: 50
number of threads: 10
number of transactions per client: 100
number of transactions actually processed: 5000/5000
tps = 613.481048 (including connections establishing)
tps = 775.337927 (excluding connections establishing)

C:\Documents and Settings\chj>pgbench -p 9100  -c 50 -j 10 -t 1000 -S pgdb300

starting vacuum...end.
transaction type: SELECT only
scaling factor: 300
query mode: simple
number of clients: 50
number of threads: 10
number of transactions per client: 1000
number of transactions actually processed: 50000/50000
tps = 3481.141102 (including connections establishing)
tps = 4090.565401 (excluding connections establishing)

跑下面这个测试时，CPU 占用100%，瓶颈已是CPU了。

C:\Documents and Settings\chj>pgbench -p 9100  -c 50 -j 10 -t 1000 -S pgdb1
starting vacuum...end.
transaction type: SELECT only
scaling factor: 1
query mode: simple
number of clients: 50
number of threads: 10
number of transactions per client: 1000
number of transactions actually processed: 50000/50000
tps = 5727.081326 (including connections establishing)
tps = 7352.381685 (excluding connections establishing)