PostgreSQL与MySQL随机更新性能对比测试

Posted on 2015-06-22 11:42:58 by osdba

1. 测试环境说明

  • CPU个数:2颗8核
  • CPU类型: Intel(R) Xeon(R) CPU E5-2640 v3 @ 2.60GHz
  • 内存: 64G
  • 测试工具:sysbench0.5
  • 数据文件放在FusionIO上
  • redolog之类的放在有Raid卡Cache的Intel 3500 600G的一块SSD上
  • PostgreSQL版本: 9.4.3
  • MySQL的版本: 5.6.23

2. 测试准备

2.1 准备文件系统

做放redolog的文件系统:

mkfs.xfs -f -i size=512 -Lssd01 -l size=32m,lazy-count=1 -d agcount=16 /dev/sdb1
mkdir /ssd01
mount -o noatime,nodiratime,nobarrier LABEL=ssd01 /ssd01

做数据盘:

mkfs.xfs -b size=4096 -f -i size=512 -Lfiodata -l size=128m,lazy-count=1 -d agcount=16 /dev/fioa
mkdir /fiodata
mount -o noatime,nodiratime,nobarrier LABEL=fiodata /fiodata

3. PostgreSQL的测试

3.1 PostgreSQL的配置

测试时修改的PostgreSQL参数如下(其它保持默认值):

max_connections = 500
shared_buffers = 10240MB
full_page_writes = off
commit_delay = 100
commit_siblings = 5
checkpoint_segments = 30
logging_collector = on

3.2 PostgreSQL随机更新非索引键的测试

测试的lua脚本update_non_index_commit_pg2.lua内容如下:

pathtest = string.match(test, "(.*/)") or ""
dofile(pathtest .. "common.lua")
function thread_init(thread_id)
local table_name
set_vars()
table_name = "sbtest".. (thread_id+1)
db_query("PREPARE testplan (text, int) AS UPDATE ".. table_name .." SET c=$1 WHERE id=$2")
end
function event(thread_id)
local c_val
local query
c_val = sb_rand_str("###########-###########-###########-###########-###########-###########-###########-###########-###########-###########")
query = "EXECUTE testplan('" .. c_val .. "'," .. sb_rand(1, oltp_table_size) ..')'
db_query("BEGIN")
db_query(query)
db_query("COMMIT")
end

测试的sysbench命令如下:

time ./sysbench_pg --test=lua/update_non_index_commit_pg2.lua \
--oltp-table-name=sysbench \
--oltp-table-size=100000 \
--db-driver=pgsql \
--pgsql-host=127.0.0.1 \
--pgsql-port=5432 \
--pgsql-user=postgres \
--pgsql-db=postgres \
--max-time=180 \
--max-requests=100000000 \
--num-threads=48 \
--db-driver=mysql \
--report-interval=1 \
--oltp-tables-count=48 \
$@

为了测试出极限,同时运行两个sysbench。

第一个sysbench的最终结果:

OLTP test statistics:
queries performed:
read: 0
write: 0
other: 27717990
total: 27717990
transactions: 9239314 (51328.82 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 0 (0.00 per sec.)
other operations: 27717990 (153986.71 per sec.)
General statistics:
total time: 180.0025s
total number of events: 9239314
total time taken by event execution: 8626.2482s
response time:
min: 0.17ms
avg: 0.93ms
max: 285.33ms
approx. 95 percentile: 2.13ms
Threads fairness:
events (avg/stddev): 192485.7083/267.42
execution time (avg/stddev): 179.7135/0.00
real 3m0.032s
user 6m20.004s
sys 4m31.163s

第二个sysbench的最终结果:

OLTP test statistics:
queries performed:
read: 0
write: 0
other: 27697905
total: 27697905
transactions: 9232619 (51292.15 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 0 (0.00 per sec.)
other operations: 27697905 (153876.71 per sec.)
General statistics:
total time: 180.0006s
total number of events: 9232619
total time taken by event execution: 8626.2837s
response time:
min: 0.15ms
avg: 0.93ms
max: 274.68ms
approx. 95 percentile: 2.13ms
Threads fairness:
events (avg/stddev): 192346.2292/262.94
execution time (avg/stddev): 179.7142/0.00
real 3m0.042s
user 6m18.477s
sys 4m31.727s

合计TPS为:51328+51292=102620

4. MySQL的测试

4.1 MySQL的配置

MySQL的主要参数如下:

datadir=/fiodata/mysql/data
log-bin=/fiodata/mysql/binlog/mysql-bin
binlog-format=ROW
binlog_row_image=minimal
binlog-checksum=CRC32
binlog-rows-query-log-events=1
binlog_max_flush_queue_time=1000
max_binlog_size = 512M
sync_binlog=1
innodb_flush_log_at_trx_commit = 1
innodb_data_home_dir = /fiodata/mysql/data
innodb_log_group_home_dir = /fiodata/mysql/innodb_log
innodb_log_buffer_size = 64M
innodb_log_file_size = 1G
innodb_log_files_in_group = 2
performance_schema=off
innodb_read_io_threads = 4
innodb_write_io_threads = 12
innodb_io_capacity = 40000
innodb_use_native_aio = 1
innodb_buffer_pool_size = 10G
transaction_isolation = READ-COMMITTED

把MySQL的binlog和innodb的redo移到ssd盘下:

cd /fiodata/mysql
mv binlog /ssd01/.
mv innodb_log /ssd01/.
ln -sf /ramdata/binlog binlog
ln -sf /ramdata/innodb_log innodb_log

建sysbench用户:

DELETE FROM mysql.user WHERE User='';
CREATE USER sysbench IDENTIFIED BY 'sysbench';
FLUSH PRIVILEGES;

4.2 MySQL的随机更新非索键的测试

测试的sysbench脚本如下:

time ./sysbench_mysql --test=lua/update_non_index_commit_mysql.lua \
--oltp-table-name=sysbench \
--oltp-table-size=100000 \
--mysql-host=127.0.0.1 \
--mysql-port=3306 \
--mysql-user=sysbench \
--mysql-password=sysbench \
--mysql-db=test \
--mysql-table-engine=innodb \
--max-time=180 \
--max-requests=100000000 \
--num-threads=48 \
--db-driver=mysql \
--report-interval=1 \
--oltp-tables-count=48 \
$@

update_non_index_commit_mysql.lua有内容如下:

pathtest = string.match(test, "(.*/)") or ""
dofile(pathtest .. "common.lua")
function thread_init(thread_id)
set_vars()
end
function event(thread_id)
local table_name
local c_val
local query
table_name = "sbtest".. sb_rand_uniform(1, oltp_tables_count)
c_val = sb_rand_str("###########-###########-###########-###########-###########-###########-###########-###########-###########-###########")
query = "UPDATE " .. table_name .. " SET c='" .. c_val .. "' WHERE id=" .. sb_rand(1, oltp_table_size)
rs = db_query(query)
db_query("COMMIT")
enduery("COMMIT")
end

第一个sysbench的最终结果:

OLTP test statistics:
queries performed:
read: 0
write: 9720330
other: 9720330
total: 19440660
transactions: 9720330 (54001.53 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 9720330 (54001.53 per sec.)
other operations: 9720330 (54001.53 per sec.)
General statistics:
total time: 180.0010s
total number of events: 9720330
total time taken by event execution: 8626.2642s
response time:
min: 0.07ms
avg: 0.89ms
max: 16.39ms
approx. 95 percentile: 2.19ms
Threads fairness:
events (avg/stddev): 202506.8750/355.93
execution time (avg/stddev): 179.7138/0.00
real 3m0.031s
user 4m55.331s
sys 2m55.120s

第二个sysbench的最终结果:

OLTP test statistics:
queries performed:
read: 0
write: 9727950
other: 9727950
total: 19455900
transactions: 9727950 (54044.01 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 9727950 (54044.01 per sec.)
other operations: 9727950 (54044.01 per sec.)
General statistics:
total time: 180.0005s
total number of events: 9727950
total time taken by event execution: 8626.3989s
response time:
min: 0.07ms
avg: 0.89ms
max: 16.34ms
approx. 95 percentile: 2.18ms
Threads fairness:
events (avg/stddev): 202665.6250/452.86
execution time (avg/stddev): 179.7166/0.00
real 3m0.034s
user 4m54.431s
sys 2m55.706s

合计TPS为:54001+54044=108045

在MySQL的测试过出现过MySQL服务器hang住的情况,出现这种情况后,无法做任何更新,过一会会恢复,但再过一会还会在hang住。上面的测试结果是取其中没有hang的情况。

5. 结论

结论:随机更新PostgreSQL与MySQL的性能差不多。

本文使用的sysbench工具:

  • 编译好的sysbech测试工具(Rhel6.x)可以从github 下载。
  • 相关的sysbench的lua脚本可以从 github 下载。