本文主要目的是测试三种硬盘的PostgreSQL的select, update, insert性能, 反映这三种硬盘的IOPS能力.
这三种硬盘分别是 :
1. OCZ RevoDrive3 PCI-E 240GB
2. DELL SSD 2.5寸 SATA 200GB
3. DELL 10K转 2.5寸 SAS 146GB同插在一台DELL R610的服务器上.
OCZ硬盘使用请参见 :
http://blog.163.com/digoal@126/blog/static/1638770402012102613815282/
测试环境 :
CentOS 5.7 x64
PostgreSQL 9.2.1编译参数 :
./configure --prefix=/home/ocz/pgsql9.2.1 --with-pgport=9201 --with-perl --with-python --with-tcl --with-openssl --with-pam --without-ldap --with-libxml --with-libxslt --enable-thread-safety --with-wal-blocksize=16 --enable-debug --with-ossp-uuid --with-libs=/opt/uuid-1.6.2/lib配置文件 :
ocz@db-172-16-3-150-> cat postgresql.conf|grep -i "^\ *[a-z]"
listen_addresses = '0.0.0.0' # what IP address(es) to listen on;
port = 9201 # (change requires restart)
max_connections = 1000 # (change requires restart)
superuser_reserved_connections = 13 # (change requires restart)
unix_socket_directory = '.' # (change requires restart)
unix_socket_permissions = 0700 # begin with 0 to use octal notation
tcp_keepalives_idle = 60 # TCP_KEEPIDLE, in seconds;
tcp_keepalives_interval = 10 # TCP_KEEPINTVL, in seconds;
tcp_keepalives_count = 10 # TCP_KEEPCNT;
shared_buffers = 1024MB # min 128kB
maintenance_work_mem = 512MB # min 1MB
max_stack_depth = 8MB # min 100kB
shared_preload_libraries = 'pg_stat_statements' # (change requires restart)
bgwriter_delay = 10ms # 10-10000ms between rounds
wal_level = hot_standby # minimal, archive, or hot_standby
fsync = on # turns forced synchronization on or off
synchronous_commit = on # synchronization level;
wal_sync_method = open_sync # the default is the first option
full_page_writes = on # recover from partial page writes
wal_buffers = 16384kB # min 32kB, -1 sets based on shared_buffers
checkpoint_segments = 128 # in logfile segments, min 1, 16MB each
archive_mode = on # allows archiving to be done
archive_command = '/bin/date' # command to use to archive a logfile segment
max_wal_senders = 32 # max number of walsender processes
wal_keep_segments = 256 # in logfile segments, 16MB each; 0 disables
hot_standby = on # "on" allows queries during recovery
wal_receiver_status_interval = 1s # send replies at least this often
hot_standby_feedback = on # send info from standby to prevent
random_page_cost = 1.0 # same scale as above
effective_cache_size = 90000MB
log_destination = 'csvlog' # Valid values are combinations of
logging_collector = on # Enable capturing of stderr and csvlog
log_directory = 'pg_log' # directory where log files are written,
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # log file name pattern,
log_file_mode = 0600 # creation mode for log files,
log_truncate_on_rotation = on # If on, an existing log file with the
log_rotation_age = 1d # Automatic rotation of logfiles will
log_rotation_size = 10MB # Automatic rotation of logfiles will
log_checkpoints = on
log_error_verbosity = verbose # terse, default, or verbose messages
log_timezone = 'PRC'
datestyle = 'iso, mdy'
timezone = 'PRC'
lc_messages = 'C' # locale for system error message
lc_monetary = 'C' # locale for monetary formatting
lc_numeric = 'C' # locale for number formatting
lc_time = 'C' # locale for time formatting
default_text_search_config = 'pg_catalog.english'
pg_stat_statements.max = 1000
pg_stat_statements.track = all测试表 :
digoal=> create table ocz_test(id int, info text, crt_time timestamp without time zone);测试数据 :
digoal=> insert into ocz_test select generate_series(1,10000000),repeat(clock_timestamp()::text, 10),clock_timestamp();
digoal=> alter table ocz_test add constraint ocz_test_pkey primary key (id);
测试函数 :
测试读取,写入,更新.
digoal=> create or replace function f_ocz_test (i_id int) returns void as $BODY$
declare
begin
perform 1 from ocz_test where id=i_id;
if not found then
insert into ocz_test (id, info, crt_time) values (i_id, repeat(clock_timestamp()::text, 10), clock_timestamp());
else
update ocz_test set info=repeat(clock_timestamp()::text, 10), crt_time=clock_timestamp() where id=i_id;
end if;
return;
exception
when others then
return;
end;
$BODY$ language plpgsql;测试脚本 :
随即取数1到1亿, 超过基础数据, 所以有1/10的机会update, 9/10的机会insert, 100%的机会select.
ocz@db-172-16-3-150-> cat ocz_test.sql
\setrandom id 1 100000000
select f_ocz_test(:id);
ocz@db-172-16-3-150-> pgbench -M prepared -n -f ./ocz_test.sql -r -c 8 -j 8 -T 60 -U digoal digoal测试方法 :
关闭RAID write cache, 关闭OCZ write cache. 开启同步提交. 确保测试得出的数据真实的反映硬盘的IO能力.
关闭DELL 服务器上带的raid卡的写cache, 使用 write through :
关闭OCZ 硬盘的写cache.
[root@db-172-16-3-150 ~]# hdparm -W 0 /dev/mapper/mpath1
/dev/mapper/mpath1:
setting drive write-caching to 0 (off)OCZ SSD测试结果 :
1. fdatasync
wal_sync_method :
wal_level = hot_standby
fsync = on
synchronous_commit = on
wal_sync_method = fdatasync
ocz@db-172-16-3-150-> pg_ctl stop -m fast
waiting for server to shut down..... done
server stopped
ocz@db-172-16-3-150-> pg_ctl start
server starting
ocz@db-172-16-3-150-> LOG: 00000: loaded library "pg_stat_statements"
LOCATION: load_libraries, miscinit.c:1249
ocz@db-172-16-3-150-> pgbench -M prepared -n -f ./ocz_test.sql -r -c 8 -j 8 -T 60 -U digoal digoal
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 8
number of threads: 8
duration: 60 s
number of transactions actually processed: 867098
tps = 14450.950285 (including connections establishing)
tps = 14452.524843 (excluding connections establishing)
statement latencies in milliseconds:
0.002270 \setrandom id 1 100000000
0.548933 select f_ocz_test(:id);2. fsync
wal_sync_method :
wal_level = hot_standby
fsync = on
synchronous_commit = on
wal_sync_method = fsync
ocz@db-172-16-3-150-> pg_ctl stop -m fast
waiting for server to shut down..... done
server stopped
ocz@db-172-16-3-150-> pg_ctl start
server starting
ocz@db-172-16-3-150-> LOG: 00000: loaded library "pg_stat_statements"
LOCATION: load_libraries, miscinit.c:1249
ocz@db-172-16-3-150-> pgbench -M prepared -n -f ./ocz_test.sql -r -c 8 -j 8 -T 60 -U digoal digoal
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 8
number of threads: 8
duration: 60 s
number of transactions actually processed: 63334
tps = 1055.390434 (including connections establishing)
tps = 1055.525152 (excluding connections establishing)
statement latencies in milliseconds:
0.003106 \setrandom id 1 100000000
7.572679 select f_ocz_test(:id);3. open_sync
wal_sync_method :
wal_level = hot_standby
fsync = on
synchronous_commit = on
wal_sync_method = open_sync
ocz@db-172-16-3-150-> pg_ctl stop -m fast
waiting for server to shut down..... done
server stopped
ocz@db-172-16-3-150-> pg_ctl start
server starting
ocz@db-172-16-3-150-> LOG: 00000: loaded library "pg_stat_statements"
LOCATION: load_libraries, miscinit.c:1249
ocz@db-172-16-3-150-> pgbench -M prepared -n -f ./ocz_test.sql -r -c 8 -j 8 -T 60 -U digoal digoal
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 8
number of threads: 8
duration: 60 s
number of transactions actually processed: 864942
tps = 14410.387611 (including connections establishing)
tps = 14412.032858 (excluding connections establishing)
statement latencies in milliseconds:
0.002245 \setrandom id 1 100000000
0.550355 select f_ocz_test(:id);DELL SSD 2.5寸 SATA接口 200GB硬盘 :
数据目录和表空间目录移到DELL SSD硬盘后测试 :
1. fdatasync
wal_sync_method :
wal_level = hot_standby
fsync = on
synchronous_commit = on
wal_sync_method = fdatasync
ocz@db-172-16-3-150-> pg_ctl stop -m fast
waiting for server to shut down..... done
server stopped
ocz@db-172-16-3-150-> pg_ctl start
server starting
ocz@db-172-16-3-150-> LOG: 00000: loaded library "pg_stat_statements"
LOCATION: load_libraries, miscinit.c:1249
ocz@db-172-16-3-150-> pgbench -M prepared -n -f ./ocz_test.sql -r -c 8 -j 8 -T 60 -U digoal digoal
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 8
number of threads: 8
duration: 60 s
number of transactions actually processed: 41067
tps = 684.307504 (including connections establishing)
tps = 684.386309 (excluding connections establishing)
statement latencies in milliseconds:
0.003403 \setrandom id 1 100000000
11.681978 select f_ocz_test(:id);2. fsync
wal_sync_method :
wal_level = hot_standby
fsync = on
synchronous_commit = on
wal_sync_method = fsync
ocz@db-172-16-3-150-> pg_ctl stop -m fast
waiting for server to shut down..... done
server stopped
ocz@db-172-16-3-150-> pg_ctl start
server starting
ocz@db-172-16-3-150-> LOG: 00000: loaded library "pg_stat_statements"
LOCATION: load_libraries, miscinit.c:1249
ocz@db-172-16-3-150-> pgbench -M prepared -n -f ./ocz_test.sql -r -c 8 -j 8 -T 60 -U digoal digoal
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 8
number of threads: 8
duration: 60 s
number of transactions actually processed: 40289
tps = 671.344007 (including connections establishing)
tps = 671.420270 (excluding connections establishing)
statement latencies in milliseconds:
0.003300 \setrandom id 1 100000000
11.907794 select f_ocz_test(:id);3. open_sync
wal_sync_method :
wal_level = hot_standby
fsync = on
synchronous_commit = on
wal_sync_method = open_sync
ocz@db-172-16-3-150-> pg_ctl stop -m fast
waiting for server to shut down..... done
server stopped
ocz@db-172-16-3-150-> pg_ctl start
server starting
ocz@db-172-16-3-150-> LOG: 00000: loaded library "pg_stat_statements"
LOCATION: load_libraries, miscinit.c:1249
ocz@db-172-16-3-150-> pgbench -M prepared -n -f ./ocz_test.sql -r -c 8 -j 8 -T 60 -U digoal digoal
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 8
number of threads: 8
duration: 60 s
number of transactions actually processed: 41235
tps = 687.096892 (including connections establishing)
tps = 687.180406 (excluding connections establishing)
statement latencies in milliseconds:
0.003390 \setrandom id 1 100000000
11.634480 select f_ocz_test(:id);DELL SAS 2.5寸 SAS接口 146GB 10K转速 机械硬盘 :
数据目录和表空间目录移到DELL 机械硬盘后测试 :
1.fdatasync
wal_sync_method :
wal_level = hot_standby
fsync = on
synchronous_commit = on
wal_sync_method = fdatasync
ocz@db-172-16-3-150-> pg_ctl stop -m fast
waiting for server to shut down..... done
server stopped
ocz@db-172-16-3-150-> pg_ctl start
server starting
ocz@db-172-16-3-150-> LOG: 00000: loaded library "pg_stat_statements"
LOCATION: load_libraries, miscinit.c:1249
ocz@db-172-16-3-150-> pgbench -M prepared -n -f ./ocz_test.sql -r -c 8 -j 8 -T 60 -U digoal digoal
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 8
number of threads: 8
duration: 60 s
number of transactions actually processed: 37679
tps = 627.889642 (including connections establishing)
tps = 627.951509 (excluding connections establishing)
statement latencies in milliseconds:
0.003394 \setrandom id 1 100000000
12.732292 select f_ocz_test(:id);2. fsync
wal_sync_method :
wal_level = hot_standby
fsync = on
synchronous_commit = on
wal_sync_method = fsync
ocz@db-172-16-3-150-> pg_ctl stop -m fast
waiting for server to shut down..... done
server stopped
ocz@db-172-16-3-150-> pg_ctl start
server starting
ocz@db-172-16-3-150-> LOG: 00000: loaded library "pg_stat_statements"
LOCATION: load_libraries, miscinit.c:1249
ocz@db-172-16-3-150-> pgbench -M prepared -n -f ./ocz_test.sql -r -c 8 -j 8 -T 60 -U digoal digoal
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 8
number of threads: 8
duration: 60 s
number of transactions actually processed: 9831
tps = 163.736413 (including connections establishing)
tps = 163.759836 (excluding connections establishing)
statement latencies in milliseconds:
0.003614 \setrandom id 1 100000000
48.832661 select f_ocz_test(:id);3. open_sync
wal_sync_method :
wal_level = hot_standby
fsync = on
synchronous_commit = on
wal_sync_method = open_sync
ocz@db-172-16-3-150-> pg_ctl stop -m fast
waiting for server to shut down..... done
server stopped
ocz@db-172-16-3-150-> pg_ctl start
server starting
ocz@db-172-16-3-150-> LOG: 00000: loaded library "pg_stat_statements"
LOCATION: load_libraries, miscinit.c:1249
ocz@db-172-16-3-150-> pgbench -M prepared -n -f ./ocz_test.sql -r -c 8 -j 8 -T 60 -U digoal digoal
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 8
number of threads: 8
duration: 60 s
number of transactions actually processed: 37707
tps = 628.375098 (including connections establishing)
tps = 628.435232 (excluding connections establishing)
statement latencies in milliseconds:
0.003443 \setrandom id 1 100000000
12.722869 select f_ocz_test(:id);【测试结果对比】
【参考】
3. http://www.seagate.com/internal-hard-drives/solid-state-hybrid/pulsar-solid-state-drives/specs/#
时间： 2024-07-30 05:04:32