4核4G 查看MySQL系统变量的命令 SHOW VARIABLES; 改变系统变量值 set GLOBAL 变量名=变量值; 测试命令 mysqlslap --concurrency=20,100,200,500,1000 --iterations=1 --create-schema='test' --query='select * from test_load_file limit 0,10;' --number-of-queries=100000 --debug-info -uroot -p Benchmark Average number of seconds to run all queries: 1.660 seconds Minimum number of seconds to run all queries: 1.660 seconds Maximum number of seconds to run all queries: 1.660 seconds Number of clients running queries: 20 Average number of queries per client: 5000 Benchmark Average number of seconds to run all queries: 1.774 seconds Minimum number of seconds to run all queries: 1.774 seconds Maximum number of seconds to run all queries: 1.774 seconds Number of clients running queries: 100 Average number of queries per client: 1000 Benchmark Average number of seconds to run all queries: 2.721 seconds Minimum number of seconds to run all queries: 2.721 seconds Maximum number of seconds to run all queries: 2.721 seconds Number of clients running queries: 200 Average number of queries per client: 500 Benchmark Average number of seconds to run all queries: 1.848 seconds Minimum number of seconds to run all queries: 1.848 seconds Maximum number of seconds to run all queries: 1.848 seconds Number of clients running queries: 500 Average number of queries per client: 200 Benchmark Average number of seconds to run all queries: 1.873 seconds Minimum number of seconds to run all queries: 1.873 seconds Maximum number of seconds to run all queries: 1.873 seconds Number of clients running queries: 1000 Average number of queries per client: 100 执行过程报大量如下错误 mysqlslap: Error when connecting to server: 1040 Too many connections 修改max_connections = 100000 #这种方式为动态设置，要永久改变需在my.cnf文件中修改 mysqlslap --concurrency=20,100,200,500,1000,1500,2000,3000,5000,7000,10000 --iterations=1 --create-schema='test' --query='select * from test_load_file limit 0,10;' --number-of-queries=100000 --debug-info -uroot -p Enter password: Benchmark Average number of seconds to run all queries: 1.777 seconds Minimum number of seconds to run all queries: 1.777 seconds Maximum number of seconds to run all queries: 1.777 seconds Number of clients running queries: 20 Average number of queries per client: 5000 Benchmark Average number of seconds to run all queries: 1.861 seconds Minimum number of seconds to run all queries: 1.861 seconds Maximum number of seconds to run all queries: 1.861 seconds Number of clients running queries: 100 Average number of queries per client: 1000 Benchmark Average number of seconds to run all queries: 5.356 seconds Minimum number of seconds to run all queries: 5.356 seconds Maximum number of seconds to run all queries: 5.356 seconds Number of clients running queries: 200 Average number of queries per client: 500 Benchmark Average number of seconds to run all queries: 6.730 seconds Minimum number of seconds to run all queries: 6.730 seconds Maximum number of seconds to run all queries: 6.730 seconds Number of clients running queries: 500 Average number of queries per client: 200 Benchmark Average number of seconds to run all queries: 9.716 seconds Minimum number of seconds to run all queries: 9.716 seconds Maximum number of seconds to run all queries: 9.716 seconds Number of clients running queries: 1000 Average number of queries per client: 100 Benchmark Average number of seconds to run all queries: 8.843 seconds Minimum number of seconds to run all queries: 8.843 seconds Maximum number of seconds to run all queries: 8.843 seconds Number of clients running queries: 1500 Average number of queries per client: 66 Benchmark Average number of seconds to run all queries: 8.474 seconds Minimum number of seconds to run all queries: 8.474 seconds Maximum number of seconds to run all queries: 8.474 seconds Number of clients running queries: 2000 Average number of queries per client: 50 Benchmark Average number of seconds to run all queries: 9.203 seconds Minimum number of seconds to run all queries: 9.203 seconds Maximum number of seconds to run all queries: 9.203 seconds Number of clients running queries: 3000 Average number of queries per client: 33 Benchmark Average number of seconds to run all queries: 9.169 seconds Minimum number of seconds to run all queries: 9.169 seconds Maximum number of seconds to run all queries: 9.169 seconds Number of clients running queries: 5000 Average number of queries per client: 20 Benchmark Average number of seconds to run all queries: 10.035 seconds Minimum number of seconds to run all queries: 10.035 seconds Maximum number of seconds to run all queries: 10.035 seconds Number of clients running queries: 7000 Average number of queries per client: 14 Benchmark Average number of seconds to run all queries: 10.194 seconds Minimum number of seconds to run all queries: 10.194 seconds Maximum number of seconds to run all queries: 10.194 seconds Number of clients running queries: 10000 Average number of queries per client: 10 User time 8.35, System time 44.07 Maximum resident set size 498816, Integral resident set size 0 Non-physical pagefaults 178634, Physical pagefaults 0, Swaps 0 Blocks in 0 out 0, Messages in 0 out 0, Signals 0 Voluntary context switches 2442869, Involuntary context switches 1401 设置只读 show variables like '%only%'; set GLOBAL read_only = true; #这种方式为动态设置，要永久改变需在my.cnf文件中修改 mysqlslap --concurrency=20,100,200,500,1000,1500,2000,3000,5000,7000,10000 --iterations=1 --create-schema='test' --query='select * from test_load_file limit 0,10;' --number-of-queries=100000 --debug-info -uroot -p Enter password: Benchmark Average number of seconds to run all queries: 1.788 seconds Minimum number of seconds to run all queries: 1.788 seconds Maximum number of seconds to run all queries: 1.788 seconds Number of clients running queries: 20 Average number of queries per client: 5000 Benchmark Average number of seconds to run all queries: 1.978 seconds Minimum number of seconds to run all queries: 1.978 seconds Maximum number of seconds to run all queries: 1.978 seconds Number of clients running queries: 100 Average number of queries per client: 1000 Benchmark Average number of seconds to run all queries: 4.862 seconds Minimum number of seconds to run all queries: 4.862 seconds Maximum number of seconds to run all queries: 4.862 seconds Number of clients running queries: 200 Average number of queries per client: 500 Benchmark Average number of seconds to run all queries: 6.971 seconds Minimum number of seconds to run all queries: 6.971 seconds Maximum number of seconds to run all queries: 6.971 seconds Number of clients running queries: 500 Average number of queries per client: 200 Benchmark Average number of seconds to run all queries: 9.059 seconds Minimum number of seconds to run all queries: 9.059 seconds Maximum number of seconds to run all queries: 9.059 seconds Number of clients running queries: 1000 Average number of queries per client: 100 Benchmark Average number of seconds to run all queries: 7.644 seconds Minimum number of seconds to run all queries: 7.644 seconds Maximum number of seconds to run all queries: 7.644 seconds Number of clients running queries: 1500 Average number of queries per client: 66 Benchmark Average number of seconds to run all queries: 7.887 seconds Minimum number of seconds to run all queries: 7.887 seconds Maximum number of seconds to run all queries: 7.887 seconds Number of clients running queries: 2000 Average number of queries per client: 50 Benchmark Average number of seconds to run all queries: 8.252 seconds Minimum number of seconds to run all queries: 8.252 seconds Maximum number of seconds to run all queries: 8.252 seconds Number of clients running queries: 3000 Average number of queries per client: 33 Benchmark Average number of seconds to run all queries: 9.649 seconds Minimum number of seconds to run all queries: 9.649 seconds Maximum number of seconds to run all queries: 9.649 seconds Number of clients running queries: 5000 Average number of queries per client: 20 Benchmark Average number of seconds to run all queries: 8.551 seconds Minimum number of seconds to run all queries: 8.551 seconds Maximum number of seconds to run all queries: 8.551 seconds Number of clients running queries: 7000 Average number of queries per client: 14 Benchmark Average number of seconds to run all queries: 9.014 seconds Minimum number of seconds to run all queries: 9.014 seconds Maximum number of seconds to run all queries: 9.014 seconds Number of clients running queries: 10000 Average number of queries per client: 10 User time 9.15, System time 42.83 Maximum resident set size 438148, Integral resident set size 0 Non-physical pagefaults 194852, Physical pagefaults 0, Swaps 0 Blocks in 0 out 0, Messages in 0 out 0, Signals 0 Voluntary context switches 2437427, Involuntary context switches 749 设置缓冲池 show variables like '%buffer%'; #innodb-buffer-pool-size是只读变量，所以只能在配置文件设置 vi /etc/my.cof innodb-buffer-pool-size=3000000000 show variables like '%buffer%'; mysqlslap --concurrency=20,100,200,500,1000,1500,2000,3000,5000,7000,10000 --iterations=1 --create-schema='test' --query='select * from test_load_file limit 0,10;' --number-of-queries=100000 --debug-info -uroot -p Enter password: Benchmark Average number of seconds to run all queries: 1.701 seconds Minimum number of seconds to run all queries: 1.701 seconds Maximum number of seconds to run all queries: 1.701 seconds Number of clients running queries: 20 Average number of queries per client: 5000 Benchmark Average number of seconds to run all queries: 1.926 seconds Minimum number of seconds to run all queries: 1.926 seconds Maximum number of seconds to run all queries: 1.926 seconds Number of clients running queries: 100 Average number of queries per client: 1000 Benchmark Average number of seconds to run all queries: 5.067 seconds Minimum number of seconds to run all queries: 5.067 seconds Maximum number of seconds to run all queries: 5.067 seconds Number of clients running queries: 200 Average number of queries per client: 500 Benchmark Average number of seconds to run all queries: 6.991 seconds Minimum number of seconds to run all queries: 6.991 seconds Maximum number of seconds to run all queries: 6.991 seconds Number of clients running queries: 500 Average number of queries per client: 200 Benchmark Average number of seconds to run all queries: 10.308 seconds Minimum number of seconds to run all queries: 10.308 seconds Maximum number of seconds to run all queries: 10.308 seconds Number of clients running queries: 1000 Average number of queries per client: 100 Benchmark Average number of seconds to run all queries: 13.233 seconds Minimum number of seconds to run all queries: 13.233 seconds Maximum number of seconds to run all queries: 13.233 seconds Number of clients running queries: 1500 Average number of queries per client: 66 Benchmark Average number of seconds to run all queries: 16.205 seconds Minimum number of seconds to run all queries: 16.205 seconds Maximum number of seconds to run all queries: 16.205 seconds Number of clients running queries: 2000 Average number of queries per client: 50 Benchmark Average number of seconds to run all queries: 21.198 seconds Minimum number of seconds to run all queries: 21.198 seconds Maximum number of seconds to run all queries: 21.198 seconds Number of clients running queries: 3000 Average number of queries per client: 33 Benchmark Average number of seconds to run all queries: 37.803 seconds Minimum number of seconds to run all queries: 37.803 seconds Maximum number of seconds to run all queries: 37.803 seconds Number of clients running queries: 5000 Average number of queries per client: 20 Benchmark Average number of seconds to run all queries: 50.114 seconds Minimum number of seconds to run all queries: 50.114 seconds Maximum number of seconds to run all queries: 50.114 seconds Number of clients running queries: 7000 Average number of queries per client: 14 Benchmark Average number of seconds to run all queries: 71.776 seconds Minimum number of seconds to run all queries: 71.776 seconds Maximum number of seconds to run all queries: 71.776 seconds Number of clients running queries: 10000 Average number of queries per client: 10 User time 7.54, System time 69.57 Maximum resident set size 509632, Integral resident set size 0 Non-physical pagefaults 212383, Physical pagefaults 0, Swaps 0 Blocks in 0 out 0, Messages in 0 out 0, Signals 0 Voluntary context switches 2480577, Involuntary context switches 1309 设置查询缓冲 mysqlslap --concurrency=20,100,200,500,1000,1500,2000,3000,5000,7000,10000 --iterations=1 --create-schema='test' --query='select * from test_load_file limit 0,10;' --number-of-queries=100000 --debug-info -uroot -p Enter password: Benchmark Average number of seconds to run all queries: 0.536 seconds Minimum number of seconds to run all queries: 0.536 seconds Maximum number of seconds to run all queries: 0.536 seconds Number of clients running queries: 20 Average number of queries per client: 5000 Benchmark Average number of seconds to run all queries: 0.920 seconds Minimum number of seconds to run all queries: 0.920 seconds Maximum number of seconds to run all queries: 0.920 seconds Number of clients running queries: 100 Average number of queries per client: 1000 Benchmark Average number of seconds to run all queries: 1.411 seconds Minimum number of seconds to run all queries: 1.411 seconds Maximum number of seconds to run all queries: 1.411 seconds Number of clients running queries: 200 Average number of queries per client: 500 Benchmark Average number of seconds to run all queries: 2.127 seconds Minimum number of seconds to run all queries: 2.127 seconds Maximum number of seconds to run all queries: 2.127 seconds Number of clients running queries: 500 Average number of queries per client: 200 Benchmark Average number of seconds to run all queries: 2.728 seconds Minimum number of seconds to run all queries: 2.728 seconds Maximum number of seconds to run all queries: 2.728 seconds Number of clients running queries: 1000 Average number of queries per client: 100 Benchmark Average number of seconds to run all queries: 2.503 seconds Minimum number of seconds to run all queries: 2.503 seconds Maximum number of seconds to run all queries: 2.503 seconds Number of clients running queries: 1500 Average number of queries per client: 66 Benchmark Average number of seconds to run all queries: 2.349 seconds Minimum number of seconds to run all queries: 2.349 seconds Maximum number of seconds to run all queries: 2.349 seconds Number of clients running queries: 2000 Average number of queries per client: 50 Benchmark Average number of seconds to run all queries: 2.933 seconds Minimum number of seconds to run all queries: 2.933 seconds Maximum number of seconds to run all queries: 2.933 seconds Number of clients running queries: 3000 Average number of queries per client: 33 Benchmark Average number of seconds to run all queries: 3.528 seconds Minimum number of seconds to run all queries: 3.528 seconds Maximum number of seconds to run all queries: 3.528 seconds Number of clients running queries: 5000 Average number of queries per client: 20 Benchmark Average number of seconds to run all queries: 5.511 seconds Minimum number of seconds to run all queries: 5.511 seconds Maximum number of seconds to run all queries: 5.511 seconds Number of clients running queries: 7000 Average number of queries per client: 14 Benchmark Average number of seconds to run all queries: 11.559 seconds Minimum number of seconds to run all queries: 11.559 seconds Maximum number of seconds to run all queries: 11.559 seconds Number of clients running queries: 10000 Average number of queries per client: 10 User time 10.44, System time 18.86 Maximum resident set size 490524, Integral resident set size 0 Non-physical pagefaults 215534, Physical pagefaults 0, Swaps 0 Blocks in 0 out 0, Messages in 0 out 0, Signals 0 Voluntary context switches 2391566, Involuntary context switches 766 mysqlslap --concurrency=20,100,200,500,1000,1500,2000,3000,5000,7000,10000 --iterations=1 --create-schema='test' --query='select * from test_load_file limit 0,10;' --number-of-queries=100000 --debug-info -uroot -p Enter password: Benchmark Average number of seconds to run all queries: 0.600 seconds Minimum number of seconds to run all queries: 0.600 seconds Maximum number of seconds to run all queries: 0.600 seconds Number of clients running queries: 20 Average number of queries per client: 5000 Benchmark Average number of seconds to run all queries: 0.894 seconds Minimum number of seconds to run all queries: 0.894 seconds Maximum number of seconds to run all queries: 0.894 seconds Number of clients running queries: 100 Average number of queries per client: 1000 Benchmark Average number of seconds to run all queries: 1.394 seconds Minimum number of seconds to run all queries: 1.394 seconds Maximum number of seconds to run all queries: 1.394 seconds Number of clients running queries: 200 Average number of queries per client: 500 Benchmark Average number of seconds to run all queries: 2.168 seconds Minimum number of seconds to run all queries: 2.168 seconds Maximum number of seconds to run all queries: 2.168 seconds Number of clients running queries: 500 Average number of queries per client: 200 Benchmark Average number of seconds to run all queries: 2.571 seconds Minimum number of seconds to run all queries: 2.571 seconds Maximum number of seconds to run all queries: 2.571 seconds Number of clients running queries: 1000 Average number of queries per client: 100 Benchmark Average number of seconds to run all queries: 2.535 seconds Minimum number of seconds to run all queries: 2.535 seconds Maximum number of seconds to run all queries: 2.535 seconds Number of clients running queries: 1500 Average number of queries per client: 66 Benchmark Average number of seconds to run all queries: 2.496 seconds Minimum number of seconds to run all queries: 2.496 seconds Maximum number of seconds to run all queries: 2.496 seconds Number of clients running queries: 2000 Average number of queries per client: 50 Benchmark Average number of seconds to run all queries: 2.721 seconds Minimum number of seconds to run all queries: 2.721 seconds Maximum number of seconds to run all queries: 2.721 seconds Number of clients running queries: 3000 Average number of queries per client: 33 Benchmark Average number of seconds to run all queries: 3.070 seconds Minimum number of seconds to run all queries: 3.070 seconds Maximum number of seconds to run all queries: 3.070 seconds Number of clients running queries: 5000 Average number of queries per client: 20 Benchmark Average number of seconds to run all queries: 5.322 seconds Minimum number of seconds to run all queries: 5.322 seconds Maximum number of seconds to run all queries: 5.322 seconds Number of clients running queries: 7000 Average number of queries per client: 14 Benchmark Average number of seconds to run all queries: 9.205 seconds Minimum number of seconds to run all queries: 9.205 seconds Maximum number of seconds to run all queries: 9.205 seconds Number of clients running queries: 10000 Average number of queries per client: 10 User time 10.60, System time 19.07 Maximum resident set size 524096, Integral resident set size 0 Non-physical pagefaults 225681, Physical pagefaults 0, Swaps 0 Blocks in 0 out 0, Messages in 0 out 0, Signals 0 Voluntary context switches 2423089, Involuntary context switches 639 mysqlslap --concurrency=20,100,200,500,1000,1500,2000,3000,5000,7000,10000 --iterations=1 --create-schema='test' --query='select * from test_load_file limit 0,10;' --number-of-queries=100000 --debug-info -uroot -p Enter password: Benchmark Average number of seconds to run all queries: 0.541 seconds Minimum number of seconds to run all queries: 0.541 seconds Maximum number of seconds to run all queries: 0.541 seconds Number of clients running queries: 20 Average number of queries per client: 5000 Benchmark Average number of seconds to run all queries: 0.918 seconds Minimum number of seconds to run all queries: 0.918 seconds Maximum number of seconds to run all queries: 0.918 seconds Number of clients running queries: 100 Average number of queries per client: 1000 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: 200 Average number of queries per client: 500 Benchmark Average number of seconds to run all queries: 1.823 seconds Minimum number of seconds to run all queries: 1.823 seconds Maximum number of seconds to run all queries: 1.823 seconds Number of clients running queries: 500 Average number of queries per client: 200 Benchmark Average number of seconds to run all queries: 1.840 seconds Minimum number of seconds to run all queries: 1.840 seconds Maximum number of seconds to run all queries: 1.840 seconds Number of clients running queries: 1000 Average number of queries per client: 100 Benchmark Average number of seconds to run all queries: 2.313 seconds Minimum number of seconds to run all queries: 2.313 seconds Maximum number of seconds to run all queries: 2.313 seconds Number of clients running queries: 1500 Average number of queries per client: 66 Benchmark Average number of seconds to run all queries: 2.178 seconds Minimum number of seconds to run all queries: 2.178 seconds Maximum number of seconds to run all queries: 2.178 seconds Number of clients running queries: 2000 Average number of queries per client: 50 Benchmark Average number of seconds to run all queries: 2.396 seconds Minimum number of seconds to run all queries: 2.396 seconds Maximum number of seconds to run all queries: 2.396 seconds Number of clients running queries: 3000 Average number of queries per client: 33 Benchmark Average number of seconds to run all queries: 2.904 seconds Minimum number of seconds to run all queries: 2.904 seconds Maximum number of seconds to run all queries: 2.904 seconds Number of clients running queries: 5000 Average number of queries per client: 20 Benchmark Average number of seconds to run all queries: 2.713 seconds Minimum number of seconds to run all queries: 2.713 seconds Maximum number of seconds to run all queries: 2.713 seconds Number of clients running queries: 7000 Average number of queries per client: 14 Benchmark Average number of seconds to run all queries: 2.823 seconds Minimum number of seconds to run all queries: 2.823 seconds Maximum number of seconds to run all queries: 2.823 seconds Number of clients running queries: 10000 Average number of queries per client: 10 User time 9.39, System time 18.31 Maximum resident set size 465836, Integral resident set size 0 Non-physical pagefaults 202149, Physical pagefaults 0, Swaps 0 Blocks in 0 out 0, Messages in 0 out 0, Signals 0 Voluntary context switches 2562914, Involuntary context switches 4684 结果整理分析 read only能带来小幅的速度提升 查询缓冲可以带来查询速度近4倍增长，但是结果一旦修改很难更新缓冲，考虑memcache/redis代替查询缓冲