MySQL性能优化最佳实践

# 已有test库的话先drop掉
drop database test;
create database test;

# prepare准备阶段，构建压测环境
sysbench /usr/local/share/sysbench/tests/include/oltp_legacy/select.lua 
--oltp-table-size=20000 --mysql-table-engine=innodb --db-driver=mysql 
--mysql-user=root --mysql-password=root123 --mysql-port=3306 
--mysql-host=10.245.231.202 --mysql-db=test 
--events=0 --time=60 --oltp-tables-count=20 --report-interval=10 --threads=2 prepare

# 开始压测
sysbench /usr/local/share/sysbench/tests/include/oltp_legacy/select.lua 
--oltp-table-size=20000 --mysql-table-engine=innodb --db-driver=mysql 
--mysql-user=root --mysql-password=root123 --mysql-port=3306 
--mysql-host=10.245.231.202 --mysql-db=test 
--events=0 --time=60 --oltp-tables-count=20 --report-interval=10 --threads=2 run

sysbench 1.0.5 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:
Number of threads: 2
Report intermediate results every 10 second(s)
Initializing random number generator from current time


Initializing worker threads...

Threads started!

[ 10s ] thds: 2 tps: 8087.29 qps: 8087.29 (r/w/o: 8087.29/0.00/0.00) lat (ms,95%): 0.34 err/ s: 0.00 reconn/s: 0.00
[ 20s ] thds: 2 tps: 6949.28 qps: 6949.28 (r/w/o: 6949.28/0.00/0.00) lat (ms,95%): 0.35 err/ s: 0.00 reconn/s: 0.00
[ 30s ] thds: 2 tps: 7251.71 qps: 7251.71 (r/w/o: 7251.71/0.00/0.00) lat (ms,95%): 0.34 err/ s: 0.00 reconn/s: 0.00
[ 40s ] thds: 2 tps: 6927.19 qps: 6927.19 (r/w/o: 6927.19/0.00/0.00) lat (ms,95%): 0.35 err/ s: 0.00 reconn/s: 0.00
[ 50s ] thds: 2 tps: 7387.64 qps: 7387.64 (r/w/o: 7387.64/0.00/0.00) lat (ms,95%): 0.32 err/ s: 0.00 reconn/s: 0.00
[ 60s ] thds: 2 tps: 10171.21 qps: 10171.21 (r/w/o: 10171.21/0.00/0.00) lat (ms,95%): 0.26 err/ s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            467780
        write:                           0
        other:                           0
        total:                           467780
    transactions:                        467780 (7795.29 per sec.)
    queries:                             467780 (7795.29 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.0008s
    total number of events:              467780

Latency (ms):
         min:                                  0.11
         avg:                                  0.25
         max:                                 27.63
         95th percentile:                      0.34
         sum:                             119092.82

Threads fairness:
    events (avg/stddev):           233890.0000/242.00
    execution time (avg/stddev):   59.5464/0.