mysql proxy性能差_两种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虚拟机，由于多种原因可能导致数据不准确性，所以本次测试的数据仅作为参考，目的是实验，学习