为观察读写分离效果,可以先停掉slave复制同步(slave stop),通过打开log参数,tail
-f sql.log观察,单独进行读写测试,select查询语句基本上都是在slave中进行的,进行单独写测试,观察sql日志,全部都是在master上进行的。
单独写(Insert)语句测试:
[root@localhost
~]# ./mysqlslap -uadmin -p29019853 -h 192.168.3.19
--concurrency=10 --number-of-queries=200 --debug-info --create-schema=t1
--auto-generate-sql --auto-generate-sql-load-type=write
Benchmark
Average number of seconds to run all
queries: 0.159 seconds
Minimum number of seconds to run all
queries: 0.159 seconds
Maximum number of seconds to run all
queries: 0.159 seconds
Number of clients running queries: 10
Average number of queries per client: 20
User
time 0.00, System time 0.00
Maximum
resident set size 0, Integral resident set size 0
Non-physical
pagefaults 479, Physical pagefaults 0, Swaps 0
Blocks
in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary
context switches 424, Involuntary context switches 34
单独写(Update)语句测试:
[root@localhost
~]# ./mysqlslap -uadmin -p29019853 -h 192.168.3.19
--concurrency=10 --number-of-queries=200 --debug-info --create-schema=t2
--auto-generate-sql --auto-generate-sql-load-type=update
Benchmark
Average number of seconds to run all
queries: 0.528 seconds
Minimum number of seconds to run all
queries: 0.528 seconds
Maximum number of seconds to run all
queries: 0.528 seconds
Number of clients running queries: 10
Average number of queries per client:
20
User
time 0.00, System time 0.00
Maximum
resident set size 0, Integral resident set size 0
Non-physical
pagefaults 478, Physical pagefaults 0, Swaps 0
Blocks
in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary
context switches 443, Involuntary context switches 3
单独读(select)语句测试:
[root@localhost
~]# ./mysqlslap -uadmin -p29019853 -h 192.168.3.19
--concurrency=10 --number-of-queries=200 --debug-info --create-schema=t1
--auto-generate-sql --auto-generate-sql-load-type=read
Benchmark
Average number of seconds to run all
queries: 0.342 seconds
Minimum number of seconds to run all
queries: 0.342 seconds
Maximum number of seconds to run all
queries: 0.342 seconds
Number of clients running queries: 10
Average number of queries per client:
20
User
time 0.00, System time 0.01
Maximum
resident set size 0, Integral resident set size 0
Non-physical
pagefaults 484, Physical pagefaults 0, Swaps 0
Blocks
in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary
context switches 1465, Involuntary context switches 32
混合测试:需开启slave
[root@localhost ~]# ./mysqlslap -uadmin -p29019853 -h 192.168.3.19
--concurrency=10 --number-of-queries=200 --debug-info --create-schema=t1 --auto-generate-sql
--auto-generate-sql-load-type=mixed
Benchmark
Average number of seconds to run all
queries: 1.724 seconds
Minimum number of seconds to run all
queries: 1.724 seconds
Maximum number of seconds to run all
queries: 1.724 seconds
Number of clients running queries: 10
Average number of queries per client:
20
User
time 0.02, System time 0.03
Maximum
resident set size 0, Integral resident set size 0
Non-physical
pagefaults 529, Physical pagefaults 0, Swaps 0
Blocks
in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary
context switches 6858, Involuntary context switches 2
下面测试只有2台服务器master和slave的情况,mysql-proxy安装在master上:
[root@localhost
~]#./mysqlslap -uadmin -p29019853 -h 192.168.3.21
--concurrency=10 --number-of-queries=200 --debug-info --create-schema=t1
--auto-generate-sql --auto-generate-sql-load-type=mixed
Benchmark
Average number of seconds to run all
queries: 1.259 seconds
Minimum number of seconds to run all
queries: 1.259 seconds
Maximum number of seconds to run all
queries: 1.259 seconds
Number of clients running queries: 10
Average number of queries per client:
20
User
time 0.01, System time 0.02
Maximum
resident set size 0, Integral resident set size 0
Non-physical
pagefaults 526, Physical pagefaults 0, Swaps 0
Blocks
in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary
context switches 4065, Involuntary context switches 3
[root@localhost
~]# ./mysqlslap -uadmin -p29019853 -h 192.168.3.21
--concurrency=10 --number-of-queries=200 --debug-info --create-schema=t1
--auto-generate-sql --auto-generate-sql-load-type=read
Benchmark
Average number of seconds to run all
queries: 1.231 seconds
Minimum number of seconds to run all
queries: 1.231 seconds
Maximum number of seconds to run all
queries: 1.231 seconds
Number of clients running queries: 10
Average number of queries per client:
20
User
time 0.02, System time 0.07
Maximum
resident set size 0, Integral resident set size 0
Non-physical
pagefaults 532, Physical pagefaults 0, Swaps 0
Blocks
in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary
context switches 12487, Involuntary context switches 61
[root@localhost
~]# ./mysqlslap -uadmin -p29019853 -h 192.168.3.21
--concurrency=10 --number-of-queries=200 --debug-info --create-schema=t2
--auto-generate-sql --auto-generate-sql-load-type=write
Benchmark
Average number of seconds to run all
queries: 0.490 seconds
Minimum number of seconds to run all
queries: 0.490 seconds
Maximum number of seconds to run all
queries: 0.490 seconds
Number of clients running queries: 10
Average number of queries per client:
20
User
time 0.00, System time 0.00
Maximum
resident set size 0, Integral resident set size 0
Non-physical
pagefaults 480, Physical pagefaults 0, Swaps 0
Blocks
in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary
context switches 361, Involuntary context switches 4
[root@localhost
~]# ./mysqlslap -uadmin -p29019853 -h 192.168.3.19 --concurrency=20
--number-of-queries=400 --debug-info --create-schema=t1 --auto-generate-sql
--auto-generate-sql-load-type=mixed
Benchmark
Average number of seconds to run all
queries: 4.345 seconds
Minimum number of seconds to run all
queries: 4.345 seconds
Maximum number of seconds to run all
queries: 4.345 seconds
Number of clients running queries: 20
Average number of queries per client:
20
User
time 0.05, System time 0.12
Maximum
resident set size 0, Integral resident set size 0
Non-physical
pagefaults 644, Physical pagefaults 0, Swaps 0
Blocks
in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary
context switches 21711, Involuntary context switches 55
[root@localhost
~]# ./mysqlslap -uadmin -p29019853 -h 192.168.3.21 --concurrency=20
--number-of-queries=400 --debug-info --create-schema=t1 --auto-generate-sql
--auto-generate-sql-load-type=mixed
Benchmark
Average number of seconds to run all
queries: 8.757 seconds
Minimum number of seconds to run all
queries: 8.757 seconds
Maximum number of seconds to run all
queries: 8.757 seconds
Number of clients running queries: 20
Average number of queries per client:
20
User
time 0.03, System time 0.10
Maximum
resident set size 0, Integral resident set size 0
Non-physical
pagefaults 640, Physical pagefaults 0, Swaps 0
Blocks
in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary
context switches 15632, Involuntary context switches 34
架构
指标
①MySQL-Proxy+Master/Slave
②Master(MySQL-Proxy)/Slave
Read
Write
Mixed
Read
Write
Mixed
运行所有语句的
平均秒数
0.342
0.159
1.724
4.345
1.231
0.490
1.259
8.757
concurrency
10
10
10
20
10
10
10
20
number of queries
200
200
200
400
200
200
200
400
IP/Port
MySQL-Proxy:
192.168.3.19:3306
Master:
192.168.3.21:3307
Slave:
192.168.3.22:3307
MySQL-Proxy:
192.168.3.21:3306
Master:
192.168.3.21:3307
Slave:
192.168.3.22:3307
mysqlslap -uadmin
-p29019853 -h 192.168.3.21 --concurrency=10 --number-of-queries=200
--debug-info --create-schema=t1 --auto-generate-sql
--auto-generate-sql-load-type=
总结:
使用一台单独的server作为MySQL-Proxy主机,单独读/写操作性能明显优于集成在Master上的方式,同时包含读/写操作的混合查询时在查询量不多的情况下第二种架构比第一种架构处理起来可能会(看具体测得的数据,本人测试结果有时快些,有时慢些)更快些,但当查询达到一定量后,前者性能的优势便凸显而出了。
结论:架构①的整体性能和单项性能都明显优于架构②
注:本次测试使用的都是VMWARE虚拟机,由于多种原因可能导致数据不准确性,所以本次测试的数据仅作为参考,目的是实验,学习