两种MySQL-Proxy架构的测试对比记录

为观察读写分离效果，可以先停掉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虚拟机，由于多种原因可能导致数据不准确性，所以本次测试的数据仅作为参考，目的是实验，学习