两种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