MySQL VS MongoDB 你会如何选择？(2)

4.2 测试环境

• Dell PowerEdge R410
• RAM：64G
• Kernel: 2.6.32
• MongoDB：3.0.7
• MySQL：5.5.40
• Linux OS：CentOS release 6.5 x86_64
• CPU：Intel(R) Xeon(R) CPU X5650 @2.67GHz / 24 cores

4.3 测试过程

测试的数据量变量名假定为size,分别取值为200w、400w、600w、800w、1000w.

每次使用单进程对单个表压测,每个表均含有4个字段,每次压测时间不超过30分钟.

为了让数据更加符合我们想要的结果,每轮测试完成后,我们都重启mysqld和mongodb实例,并且用下面的方法删除系统cache,释放swap.

#sync — 将脏数据刷新到磁盘
#echo 3 > /proc/sys/vm/drop_caches — 清除OS Cache
#swapoff -a && swapon -a

4.3.1 sysbench

4.3.1.1 准备工作

(1) 创建测试库

mysql> drop database if exists sysbench_test;
mysql> create database if not exists sysbench_test character set utf8 collate utf8_general_ci;

(2) 查看表结构

`id` int(10) unsigned NOT NULL auto_increment,
`k` int(10) unsigned NOT NULL default ‘0’,
`c` char(120) NOT NULL default ”,
`pad` char(60) NOT NULL default ”,
PRIMARY KEY (`id`),
KEY `k` (`k`));

4.3.1.2 只读测试

测试命令：

# ./bin/sysbench –db-driver=mysql –test=oltp.lua –num-threads=1 \
–oltp_tables_count=1 –oltp-table-size=${size} –report-interval=10 \
–oltp-dist-type=uniform –rand-init=on –max-requests=0 \
–oltp-test-mode=nontrx –oltp-nontrx-mode=select \
–oltp-read-only=on –oltp-skip-trx=on –mysql-table-engine=innodb \
–mysql-socket=/tmp/mysql.sock –mysql-user=root –mysql-host=localhost \
–mysql-password=`cat /data/save/mysql_root` –mysql-port=3306 \
–mysql-db=sysbench_test [ prepare | run | cleanup ]

4.3.1.3 只更新测试

测试命令：

#./bin/sysbench –db-driver=mysql –test=./share/sysbench/update_index.lua \
–num-threads=1 –oltp_tables_count=1 –oltp-table-size=${size} –report-interval=10 \
–oltp-dist-type=uniform –rand-init=on –max-requests=0 \
–oltp-read-only=off –mysql-table-engine=innodb –mysql-socket=/tmp/mysql.sock \
–mysql-user=root –mysql-host=localhost –mysql-password=`cat /data/save/mysql_root` \
–mysql-port=3306 –mysql-db=sysbench_test [ prepare | –max-time=1800 run | cleanup ]

4.3.1.4 只插入测试

测试命令：

#./bin/sysbench –db-driver=mysql –test=./share/sysbench/insert.lua –num-threads=1 \
–oltp_tables_count=1 –oltp-table-size=${size} –report-interval=10 \
–oltp-dist-type=uniform –rand-init=on –max-requests=0 –oltp-read-only=off \
–mysql-table-engine=innodb –mysql-socket=/tmp/mysql.sock –mysql-user=root \
–mysql-host=localhost –mysql-password=`cat /data/save/mysql_root` –mysql-port=3306 \
–mysql-db=sysbench_test [ prepare | –max-time=1800 run | cleanup ]

4.3.1.5 混合模式测试

测试命令：

#./bin/sysbench –test=./share/sysbench/oltp.lua –mysql-host=localhost –mysql-port=3306 \
–mysql-user=root –mysql-password=`cat /data/save/mysql_root` \
–mysql-db=sysbench_test –mysql-table-engine=innodb \
–oltp-table-size=${size} –report-interval=10 –rand-init=on –max-requests=0 \
–oltp-read-only=off –oltp-point-selects=$((size/3)) –oltp-point-inserts=$((size/3)) \
–oltp-index-updates=$((size/3)) –num-threads=1 –mysql-ignore-errors=all \
–oltp-skip-trx=off –oltp_tables_count=1 [ prepare | –max-time=1800 run | cleanup ]

参数含义请参考上面的使用说明章节.

4.3.2 ycsb

4.3.2.1 准备工作

编辑workload文件,分别修改readproportion、updateproportion、insertproportion为相应的值并分别命名为workload_select、workload_update、workload_insert、workload_complex比如只读测试workload_select文件内容：

workload=com.yahoo.ycsb.workloads.CoreWorkload
recordcount=${size}
operationcount=${size}
insertstart=0
fieldcount=4
fieldlength=100
readallfields=true
writeallfields=false
fieldlengthdistribution=zipfian
readproportion=1
updateproportion=0
insertproportion=0
requestdistribution=zipfian
table=ycsb_test
maxexecutiontime=1800

4.3.2.2 只读测试

测试命令：

./bin/ycsb load mongodb -s -threads 1 -P workloads/workload_select
./bin/ycsb run mongodb -s -threads 1 -P workloads/workload_select

4.3.2.3 只更新测试

测试命令：

./bin/ycsb load mongodb -s -threads 1 -P workloads/workload_update
./bin/ycsb run mongodb -s -threads 1 -P workloads/workload_update

4.3.2.4 只插入测试

测试命令：

./bin/ycsb load mongodb -s -threads 1 -P workloads/workload_insert
./bin/ycsb run mongodb -s -threads 1 -P workloads/workload_insert

4.3.2.5 混合模式测试

测试命令：

./bin/ycsb load mongodb -s -threads 1 -P workloads/workload_complex
./bin/ycsb run mongodb -s -threads 1 -P workloads/workload_complex

参数含义请参考上面的使用说明章节.

4.4 测试结果

（编辑：ASP站长网）