共计 5839 个字符，预计需要花费 15 分钟才能阅读完成。
因游戏业务需求，经常需要创建新的数据库，有时候在已经启着数据库实例的机器上纠结，是在原来的实例中直接加个库呢，还是在另起一个实例，哪个性能更好呢？所以就有了本次的测试。
物理机 6 台，配置如下
CPU:Intel(R) Xeon(R) CPU E5-2630 v3 @ 2.40GHz
MEM:256G
DISK:SSD
2 台 MySQL 机器，分别部署单实例多库环境和多实例单库环境，8 个数据库，8 个实例，每个数据库数据大概 6G 左右，机器的大内存是为了以后上线用的，测试为了节省创建数据的时间，所以没有采用大内存，只需要数据大于内存即可。
4 台压力源，每台上压测时起 2 个进程进行测试。
10.11.0.159 单实例多库 buffer_pool_size16G 1instance 8db
10.11.0.160 多实例单库 buffer_pool_size2G 8instance 1db
10.11.0.159 单实例多库上测试命令：
./sysbench –test=./tests/db/oltp.lua–mysql-table-engine=innodb –mysql-db=sbtest1 –oltp-table-size=200000–mysql-user=thunder –mysql-password=thunder –mysql-host=10.11.0.159–mysql-port=3306 –oltp-tables-count=100 –num-threads=32 –max-requests=200000–report-interval=2 run
./sysbench –test=./tests/db/oltp.lua–mysql-table-engine=innodb –mysql-db=sbtest2 –oltp-table-size=200000–mysql-user=thunder –mysql-password=thunder –mysql-host=10.11.0.159–mysql-port=3306 –oltp-tables-count=100 –num-threads=32 –max-requests=200000–report-interval=2 run
./sysbench –test=./tests/db/oltp.lua–mysql-table-engine=innodb –mysql-db=sbtest3 –oltp-table-size=200000–mysql-user=thunder –mysql-password=thunder –mysql-host=10.11.0.159–mysql-port=3306 –oltp-tables-count=100 –num-threads=32–max-requests=200000 –report-interval=2 run
./sysbench –test=./tests/db/oltp.lua–mysql-table-engine=innodb –mysql-db=sbtest4 –oltp-table-size=200000–mysql-user=thunder –mysql-password=thunder –mysql-host=10.11.0.159 –mysql-port=3306–oltp-tables-count=100 –num-threads=32 –max-requests=200000–report-interval=2 run
./sysbench –test=./tests/db/oltp.lua–mysql-table-engine=innodb –mysql-db=sbtest5 –oltp-table-size=200000–mysql-user=thunder –mysql-password=thunder –mysql-host=10.11.0.159–mysql-port=3306 –oltp-tables-count=100 –num-threads=32–max-requests=200000 –report-interval=2 run
./sysbench –test=./tests/db/oltp.lua–mysql-table-engine=innodb –mysql-db=sbtest6 –oltp-table-size=200000 –mysql-user=thunder–mysql-password=thunder –mysql-host=10.11.0.159 –mysql-port=3306–oltp-tables-count=100 –num-threads=32 –max-requests=200000–report-interval=2 run
./sysbench –test=./tests/db/oltp.lua–mysql-table-engine=innodb –mysql-db=sbtest7 –oltp-table-size=200000–mysql-user=thunder –mysql-password=thunder –mysql-host=10.11.0.159–mysql-port=3306 –oltp-tables-count=100 –num-threads=32–max-requests=200000 –report-interval=2 run
./sysbench –test=./tests/db/oltp.lua –mysql-table-engine=innodb–mysql-db=sbtest8 –oltp-table-size=200000 –mysql-user=thunder–mysql-password=thunder –mysql-host=10.11.0.159 –mysql-port=3306–oltp-tables-count=100 –num-threads=32 –max-requests=200000–report-interval=2 run
10.11.0.160 多实例单库上测试命令
./sysbench –test=./tests/db/oltp.lua–mysql-table-engine=innodb –oltp-table-size=200000 –mysql-user=thunder–mysql-password=thunder –mysql-host=10.11.0.160 –mysql-port=3301–oltp-tables-count=100 –num-threads=32 –max-requests=200000–report-interval=2 run
./sysbench –test=./tests/db/oltp.lua–mysql-table-engine=innodb –oltp-table-size=200000 –mysql-user=thunder–mysql-password=thunder –mysql-host=10.11.0.160 –mysql-port=3302–oltp-tables-count=100 –num-threads=32 –max-requests=200000–report-interval=2 run
./sysbench –test=./tests/db/oltp.lua–mysql-table-engine=innodb –oltp-table-size=200000 –mysql-user=thunder–mysql-password=thunder –mysql-host=10.11.0.160 –mysql-port=3303–oltp-tables-count=100 –num-threads=32 –max-requests=200000–report-interval=2 run
./sysbench –test=./tests/db/oltp.lua–mysql-table-engine=innodb –oltp-table-size=200000 –mysql-user=thunder–mysql-password=thunder –mysql-host=10.11.0.160 –mysql-port=3304–oltp-tables-count=100 –num-threads=32 –max-requests=200000–report-interval=2 run
./sysbench –test=./tests/db/oltp.lua–mysql-table-engine=innodb –oltp-table-size=200000 –mysql-user=thunder–mysql-password=thunder –mysql-host=10.11.0.160 –mysql-port=3305–oltp-tables-count=100 –num-threads=32 –max-requests=200000–report-interval=2 run
./sysbench –test=./tests/db/oltp.lua–mysql-table-engine=innodb –oltp-table-size=200000 –mysql-user=thunder–mysql-password=thunder –mysql-host=10.11.0.160 –mysql-port=3306–oltp-tables-count=100 –num-threads=32 –max-requests=200000 –report-interval=2run
./sysbench –test=./tests/db/oltp.lua–mysql-table-engine=innodb –oltp-table-size=200000 –mysql-user=thunder–mysql-password=thunder –mysql-host=10.11.0.160 –mysql-port=3307–oltp-tables-count=100 –num-threads=32 –max-requests=200000–report-interval=2 run
./sysbench –test=./tests/db/oltp.lua–mysql-table-engine=innodb –oltp-table-size=200000 –mysql-user=thunder–mysql-password=thunder –mysql-host=10.11.0.160 –mysql-port=3308–oltp-tables-count=100 –num-threads=32 –max-requests=200000–report-interval=2 run
测试数据：
多实例非本机 | TPS | QPS | 单实例非本机 | ||||
32 | 3301 | 359.47 | 6470.44 | 32 | test1 | 356.54 | 6417.81 |
32 | 3302 | 358.97 | 6461.41 | 32 | test2 | 355.76 | 6403.69 |
32 | 3303 | 358.92 | 6460.60 | 32 | test3 | 356.82 | 6422.81 |
32 | 3304 | 358.53 | 6453.60 | 32 | test4 | 356.38 | 6414.78 |
32 | 3305 | 358.39 | 6451.04 | 32 | test5 | 357.08 | 6427.42 |
32 | 3306 | 358.62 | 6455.22 | 32 | test6 | 355.87 | 6405.58 |
32 | 3307 | 358.56 | 6454.11 | 32 | test7 | 355.75 | 6403.54 |
32 | 3308 | 358.58 | 6454.50 | 32 | test8 | 355.54 | 6399.70 |
汇总 | 2870.04 | 51660.92 | 汇总 | 2849.74 | 51295.33 | ||
多实例本机 | 单实例本机 | ||||||
32 | 3301 | 787.49 | 14174.89 | 32 | test1 | 608.37 | 10950.61 |
32 | 3302 | 789.45 | 14210.17 | 32 | test2 | 616.14 | 11090.59 |
32 | 3303 | 789.00 | 14201.93 | 32 | test3 | 606.96 | 10925.27 |
32 | 3304 | 786.23 | 14152.08 | 32 | test4 | 611.49 | 11006.75 |
32 | 3305 | 788.75 | 14197.46 | 32 | test5 | 607.00 | 10926.00 |
32 | 3306 | 786.87 | 14163.71 | 32 | test6 | 614.76 | 11065.62 |
32 | 3307 | 791.78 | 14251.98 | 32 | test7 | 607.01 | 10926.18 |
32 | 3308 | 789.85 | 14217.25 | 32 | test8 | 611.99 | 11015.82 |
汇总 | 6309.42 | 113569.47 | 汇总 | 4883.72 | 87906.84 | ||
总结：
当压力源为内网其它机器时，可以看出 2 台机器 TPS/QPS 相差不大，其原因为 MySQL 服务器的物理机网卡已经跑满，此时的瓶颈为网卡。
当压力源为 MySQL 服务器本机时，可以看出多实例单库的性能要比单实例多库的性能高出 20% 左右，但是其本机负载也很高，最高达到了 225，CPU idle 值为 0，机器资源已经用光，而单实例多库机器的负载最高为 65，机器还有剩余资源，CPUidle 值为 6 左右，此时的瓶颈为 CPU。
以上测试没有针对数据库进行较精细的测试，只是通过 sqlbench 测试相同配置机器多实例单库与单实例多库性能的对比，已经有相关进行参考。
通过上面的数据，肯定很多人会选择多实例单库，而我却没有采用多实例的方式，原因如下：
对于业务来说，一般情况下是服务与数据库不在同一台机器，通过测试可以看出，在网络为瓶颈的情况下，两种方式的 TPS/QPS 是相差不多的，单实例多库这种方式维护起来又很方便。
本文永久更新链接地址 ：http://www.linuxidc.com/Linux/2016-12/138203.htm
