MySQL之benchmark优化一

2019独角兽企业重金招聘Python工程师标准>>>

环境: MySQL5.7，Ubuntu16 64位操作系统，4核CPU，8G运行内存

/etc/mysql/my.cnf中的配置

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/percona-server.conf.d/
[mysqld]
max_connections=600

back_log=600 # default size is 170
max_allowed_packet=16M
sort_buffer_size=2M
join_buffer_size=2M
thread_cache_size=300
max_prepared_stmt_count=20000

 

250个线程测试

1. prepare命令，准备数据

    List-1

sysbench /usr/share/sysbench/oltp_read_write.lua --db-driver=mysql --table_size=1000000 --tables=4 --mysql-host=localhost  --mysql-db=foo --mysql-user=root --mysql-password=密码 prepare

2. run

    List-2

sysbench /usr/share/sysbench/oltp_read_write.lua --num-threads=250 --db-driver=mysql --table_size=1000000 --tables=4 --mysql-host=localhost  --mysql-db=foo --mysql-user=root --mysql-password=密码 run

    List-3 List-2的结果

WARNING: --num-threads is deprecated, use --threads instead
sysbench 1.0.14 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:
Number of threads: 250
Initializing random number generator from current time


Initializing worker threads...

Threads started!

SQL statistics:
    queries performed:
        read:                            63154
        write:                           18026
        other:                           9015
        total:                           90195
    transactions:                        4504   (442.58 per sec.)
    queries:                             90195  (8862.98 per sec.)
    ignored errors:                      7      (0.69 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          10.1751s
    total number of events:              4504

Latency (ms):
         min:                                    4.32
         avg:                                  559.02
         max:                                 5581.71
         95th percentile:                     1739.68
         sum:                              2517847.32

Threads fairness:
    events (avg/stddev):           18.0160/4.48
    execution time (avg/stddev):   10.0714/0.04

 

500个线程测试

1. prepare数据

    List-4

sysbench /usr/share/sysbench/oltp_read_write.lua --db-driver=mysql --table_size=1000000 --tables=4 --mysql-host=localhost  --mysql-db=foo --mysql-user=root --mysql-password=密码 prepare

2. run分析结果

    List-5

WARNING: --num-threads is deprecated, use --threads instead
sysbench 1.0.14 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:
Number of threads: 500
Initializing random number generator from current time


Initializing worker threads...

Threads started!

SQL statistics:
    queries performed:
        read:                            56126
        write:                           16001
        other:                           8006
        total:                           80133
    transactions:                        3997   (380.36 per sec.)
    queries:                             80133  (7625.52 per sec.)
    ignored errors:                      12     (1.14 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          10.5067s
    total number of events:              3997

Latency (ms):
         min:                                    8.67
         avg:                                 1284.46
         max:                                10394.83
         95th percentile:                     4055.23
         sum:                              5133991.11

Threads fairness:
    events (avg/stddev):           7.9940/3.22
    execution time (avg/stddev):   10.2680/0.14

    从List-3和List-5看出，线程个数从250增加到500后:

•     TPS从442调到380
•     QPS从8862掉到7625

转载于:https://my.oschina.net/u/2518341/blog/1831478