时间：2021-07-01 10:21:17 帮助过：14人阅读
二：my.cnf配置（两个节点都要安装）
1 server-id = 189 2 3 #Binary Logging. 4 log-bin=mysql-bin 5 binlog-format=row 6 7 #Gtid Mode 8 gtid-mode=ON 9 enforce-gtid-consistency 10 11 default-storage-engine=INNODB 12 13 default-storage-engine=ROCKSDBView Code
三：安装插件（两个节点都要安装）
1 # ps-admin --enable-rocksdb -u root -prooT_258 -P 3306 -h 192.168.202.189 2 3 [root@rocksdb ~]# ps-admin --enable-rocksdb -u root -prooT_258 -P 3306 -h 192.168.202.189 4 Checking if RocksDB plugin is available for installation ... 5 INFO: ha_rocksdb.so library for RocksDB found at /usr/lib64/mysql/plugin/ha_rocksdb.so. 6 7 Checking RocksDB engine plugin status... 8 INFO: RocksDB engine plugin is not installed. 9 10 Installing RocksDB engine... 11 INFO: Successfully installed RocksDB engine plugin.View Code
四：主从搭建
1 GRANT REPLICATION SLAVE ON *.* TO ‘repl‘@‘192.168.202.190‘ IDENTIFIED BY ‘rooT_258‘; 2 CHANGE MASTER TO MASTER_HOST=‘192.168.202.189‘, MASTER_USER=‘repl‘, MASTER_PASSWORD=‘rooT_258‘, master_auto_position=1;View Code
五：sysbench安装
1 cd /usr/local/src 2 wget https://codeload.github.com/akopytov/sysbench/tar.gz/1.0.17 3 mv /usr/local/src/1.0.17 /usr/local/src/sysbench-1.0.17.tar.gz 4 tar -xzvf /usr/local/src/sysbench-1.0.17.tar.gz -C /usr/local/ 5 6 cd /usr/local/sysbench-1.0.17 7 8 yum -y install make automake libtool pkgconfig libaio-devel 9 yum -y install Percona-Server-devel-57.x86_64 openssl-devel 10 11 12 ##Build and Install 13 ./autogen.sh 14 # Add --with-pgsql to build with PostgreSQL support 15 ./configure 16 make -j 17 make install 18 19 sysbench --version
六：sysbench测试
准备数据：
sysbench /usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua \
--mysql-host=localhost \
--mysql-port=3306 \
--mysql-user=root \
--mysql-password=rooT_258 \
--mysql-db=tt \
--oltp-tables-count=10 \
--oltp-table-size=1000000 \
--oltp-dist-type=uniform \
--oltp-read-only=off \
--oltp-test-mode=complex \
--rand-init=on \
--db-driver=mysql \
prepare
sysbench --test=/usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua \
--mysql-db=sbtest \
--oltp-tables-count=10 \
--oltp-table-size=1000000 \
--oltp-read-only=off \
--init-rng=on \
--num-threads=16 \
--max-requests=0 \
--oltp-dist-type=uniform \
--max-time=1800 \
--mysql-user=root \
--mysql-socket=/var/lib/mysql/mysql.sock \
--mysql-password=rooT_258 \
--db-driver=mysql \
--mysql-table-engine=rocksdb \
--oltp-test-mode=complex \
prepare
Innodb读写测试
1 [root@rocksdb sysbench-1.0.17]# sysbench /usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua 2 > --mysql-host=localhost 3 > --mysql-port=3306 4 > --mysql-user=root 5 > --mysql-password=rooT_258 6 > --mysql-db=tt 7 > --oltp-tables-count=10 8 > --oltp-table-size=10000000 9 > --oltp-dist-type=uniform 10 > --oltp-read-only=off 11 > --oltp-test-mode=complex 12 > --rand-init=on 13 > --db-driver=mysql 14 > --report-interval=10 15 > --threads=16 16 > --time=120 17 > run 18 sysbench 1.0.17 (using bundled LuaJIT 2.1.0-beta2) 19 20 Running the test with following options: 21 Number of threads: 16 22 Report intermediate results every 10 second(s) 23 Initializing random number generator from current time 24 25 26 Initializing worker threads... 27 28 Threads started! 29 30 [ 10s ] thds: 16 tps: 343.03 qps: 6959.41 (r/w/o: 4878.02/345.83/1735.55) lat (ms,95%): 101.13 err/s: 3.80 reconn/s: 0.00 31 [ 20s ] thds: 16 tps: 355.71 qps: 7110.18 (r/w/o: 4978.39/359.01/1772.77) lat (ms,95%): 94.10 err/s: 0.20 reconn/s: 0.00 32 [ 30s ] thds: 16 tps: 419.20 qps: 8377.29 (r/w/o: 5863.30/428.40/2085.60) lat (ms,95%): 58.92 err/s: 0.00 reconn/s: 0.00 33 [ 40s ] thds: 16 tps: 416.99 qps: 8346.96 (r/w/o: 5843.30/429.49/2074.17) lat (ms,95%): 57.87 err/s: 0.10 reconn/s: 0.00 34 [ 50s ] thds: 16 tps: 368.01 qps: 7371.01 (r/w/o: 5159.38/383.60/1828.03) lat (ms,95%): 73.13 err/s: 0.10 reconn/s: 0.00 35 [ 60s ] thds: 16 tps: 394.21 qps: 7875.75 (r/w/o: 5512.58/414.42/1948.76) lat (ms,95%): 70.55 err/s: 0.00 reconn/s: 0.00 36 [ 70s ] thds: 16 tps: 406.79 qps: 8139.10 (r/w/o: 5699.26/427.48/2012.36) lat (ms,95%): 63.32 err/s: 0.10 reconn/s: 0.00 37 [ 80s ] thds: 16 tps: 405.22 qps: 8098.23 (r/w/o: 5670.00/431.43/1996.80) lat (ms,95%): 69.29 err/s: 0.00 reconn/s: 0.00 38 [ 90s ] thds: 16 tps: 406.39 qps: 8136.07 (r/w/o: 5694.94/437.29/2003.84) lat (ms,95%): 55.82 err/s: 0.10 reconn/s: 0.00 39 [ 100s ] thds: 16 tps: 405.39 qps: 8105.84 (r/w/o: 5675.19/438.59/1992.06) lat (ms,95%): 57.87 err/s: 0.00 reconn/s: 0.00 40 [ 110s ] thds: 16 tps: 421.30 qps: 8429.48 (r/w/o: 5899.49/460.40/2069.59) lat (ms,95%): 53.85 err/s: 0.10 reconn/s: 0.00 41 [ 120s ] thds: 16 tps: 418.51 qps: 8367.62 (r/w/o: 5858.55/463.61/2045.45) lat (ms,95%): 53.85 err/s: 0.00 reconn/s: 0.00 42 SQL statistics: 43 queries performed: 44 read: 667380 45 write: 50214 46 other: 235716 47 total: 953310 48 transactions: 47625 (396.78 per sec.) 49 queries: 953310 (7942.43 per sec.) 50 ignored errors: 45 (0.37 per sec.) 51 reconnects: 0 (0.00 per sec.) 52 53 General statistics: 54 total time: 120.0261s 55 total number of events: 47625 56 57 Latency (ms): 58 min: 4.10 59 avg: 40.32 60 max: 970.72 61 95th percentile: 63.32 62 sum: 1920083.95 63 64 Threads fairness: 65 events (avg/stddev): 2976.5625/17.56 66 execution time (avg/stddev): 120.0052/0.00
Innodb只读测试
1 [root@rocksdb sysbench-1.0.17]# sysbench /usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua 2 > --mysql-host=localhost 3 > --mysql-port=3306 4 > --mysql-user=root 5 > --mysql-password=rooT_258 6 > --mysql-db=tt 7 > --oltp-tables-count=10 8 > --oltp-table-size=10000000 9 > --oltp-dist-type=uniform 10 > --oltp-read-only=on 11 > --oltp-test-mode=nontrx 12 > --oltp-nontrx-mode=select 13 > --rand-init=on 14 > --db-driver=mysql 15 > --report-interval=10 16 > --threads=16 17 > --time=120 18 > run 19 sysbench 1.0.17 (using bundled LuaJIT 2.1.0-beta2) 20 21 Running the test with following options: 22 Number of threads: 16 23 Report intermediate results every 10 second(s) 24 Initializing random number generator from current time 25 26 27 Initializing worker threads... 28 29 Threads started! 30 31 [ 10s ] thds: 16 tps: 744.45 qps: 11923.36 (r/w/o: 10432.95/0.00/1490.41) lat (ms,95%): 26.68 err/s: 0.00 reconn/s: 0.00 32 [ 20s ] thds: 16 tps: 739.11 qps: 11827.06 (r/w/o: 10348.74/0.00/1478.32) lat (ms,95%): 26.68 err/s: 0.00 reconn/s: 0.00 33 [ 30s ] thds: 16 tps: 751.78 qps: 12027.51 (r/w/o: 10524.04/0.00/1503.46) lat (ms,95%): 25.74 err/s: 0.00 reconn/s: 0.00 34 [ 40s ] thds: 16 tps: 753.45 qps: 12060.75 (r/w/o: 10553.76/0.00/1506.99) lat (ms,95%): 26.68 err/s: 0.00 reconn/s: 0.00 35 [ 50s ] thds: 16 tps: 781.27 qps: 12501.24 (r/w/o: 10938.81/0.00/1562.43) lat (ms,95%): 24.38 err/s: 0.00 reconn/s: 0.00 36 [ 60s ] thds: 16 tps: 787.14 qps: 12590.15 (r/w/o: 11015.77/0.00/1574.38) lat (ms,95%): 24.38 err/s: 0.00 reconn/s: 0.00 37 [ 70s ] thds: 16 tps: 771.21 qps: 12335.20 (r/w/o: 10792.89/0.00/1542.31) lat (ms,95%): 25.28 err/s: 0.00 reconn/s: 0.00 38 [ 80s ] thds: 16 tps: 761.03 qps: 12180.13 (r/w/o: 10657.97/0.00/1522.15) lat (ms,95%): 24.83 err/s: 0.00 reconn/s: 0.00 39 [ 90s ] thds: 16 tps: 787.41 qps: 12597.36 (r/w/o: 11022.54/0.00/1574.82) lat (ms,95%): 24.38 err/s: 0.00 reconn/s: 0.00 40 [ 100s ] thds: 16 tps: 768.70 qps: 12301.62 (r/w/o: 10764.22/0.00/1537.40) lat (ms,95%): 25.28 err/s: 0.00 reconn/s: 0.00 41 [ 110s ] thds: 16 tps: 783.80 qps: 12536.26 (r/w/o: 10968.65/0.00/1567.61) lat (ms,95%): 24.38 err/s: 0.00 reconn/s: 0.00 42 [ 120s ] thds: 16 tps: 781.75 qps: 12509.80 (r/w/o: 10946.30/0.00/1563.50) lat (ms,95%): 24.83 err/s: 0.00 reconn/s: 0.00 43 SQL statistics: 44 queries performed: 45 read: 1289792 46 write: 0 47 other: 184256 48 total: 1474048 49 transactions: 92128 (767.60 per sec.) 50 queries: 1474048 (12281.53 per sec.) 51 ignored errors: 0 (0.00 per sec.) 52 reconnects: 0 (0.00 per sec.) 53 54 General statistics: 55 total time: 120.0202s 56 total number of events: 92128 57 58 Latency (ms): 59 min: 1.03 60 avg: 20.84 61 max: 3263.49 62 95th percentile: 25.28 63 sum: 1919661.87 64 65 Threads fairness: 66 events (avg/stddev): 5758.0000/427.33 67 execution time (avg/stddev): 119.9789/0.01
rocksdb读写测试
1 [root@rocksdb sysbench-1.0.17]# sysbench /usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua 2 > --mysql-host=localhost 3 > --mysql-port=3306 4 > --mysql-user=root 5 > --mysql-password=rooT_258 6 > --mysql-db=sbtest 7 > --oltp-tables-count=10 8 > --oltp-table-size=10000000 9 > --oltp-dist-type=uniform 10 > --oltp-read-only=off 11 > --oltp-test-mode=complex 12 > --rand-init=on 13 > --db-driver=mysql 14 > --report-interval=10 15 > --threads=16 16 > --time=120 17 > run 18 sysbench 1.0.17 (using bundled LuaJIT 2.1.0-beta2) 19 20 Running the test with following options: 21 Number of threads: 16 22 Report intermediate results every 10 second(s) 23 Initializing random number generator from current time 24 25 26 Initializing worker threads... 27 28 Threads started! 29 30 [ 10s ] thds: 16 tps: 306.75 qps: 6154.40 (r/w/o: 4311.00/308.15/1535.25) lat (ms,95%): 78.60 err/s: 0.00 reconn/s: 0.00 31 [ 20s ] thds: 16 tps: 319.19 qps: 6392.81 (r/w/o: 4473.17/325.29/1594.35) lat (ms,95%): 73.13 err/s: 0.00 reconn/s: 0.00 32 [ 30s ] thds: 16 tps: 316.99 qps: 6333.45 (r/w/o: 4436.32/321.99/1575.14) lat (ms,95%): 73.13 err/s: 0.00 reconn/s: 0.00 33 [ 40s ] thds: 16 tps: 324.53 qps: 6488.83 (r/w/o: 4540.17/332.63/1616.03) lat (ms,95%): 69.29 err/s: 0.10 reconn/s: 0.00 34 [ 50s ] thds: 16 tps: 321.39 qps: 6435.15 (r/w/o: 4505.53/333.69/1595.94) lat (ms,95%): 75.82 err/s: 0.00 reconn/s: 0.00 35 [ 60s ] thds: 16 tps: 306.11 qps: 6110.35 (r/w/o: 4276.00/319.11/1515.24) lat (ms,95%): 81.48 err/s: 0.00 reconn/s: 0.00 36 [ 70s ] thds: 16 tps: 320.90 qps: 6423.65 (r/w/o: 4496.83/337.10/1589.71) lat (ms,95%): 71.83 err/s: 0.00 reconn/s: 0.00 37 [ 80s ] thds: 16 tps: 298.00 qps: 5965.01 (r/w/o: 4177.04/314.40/1473.58) lat (ms,95%): 84.47 err/s: 0.10 reconn/s: 0.00 38 [ 90s ] thds: 16 tps: 308.90 qps: 6172.84 (r/w/o: 4319.96/326.00/1526.88) lat (ms,95%): 78.60 err/s: 0.00 reconn/s: 0.00 39 [ 100s ] thds: 16 tps: 314.10 qps: 6286.15 (r/w/o: 4402.14/337.00/1547.01) lat (ms,95%): 73.13 err/s: 0.00 reconn/s: 0.00 40 [ 110s ] thds: 16 tps: 320.60 qps: 6404.45 (r/w/o: 4481.94/345.00/1577.51) lat (ms,95%): 70.55 err/s: 0.00 reconn/s: 0.00 41 [ 120s ] thds: 16 tps: 313.70 qps: 6289.50 (r/w/o: 4401.27/342.61/1545.62) lat (ms,95%): 73.13 err/s: 0.00 reconn/s: 0.00 42 SQL statistics: 43 queries performed: 44 read: 528220 45 write: 39430 46 other: 186941 47 total: 754591 48 transactions: 37728 (314.36 per sec.) 49 queries: 754591 (6287.44 per sec.) 50 ignored errors: 2 (0.02 per sec.) 51 reconnects: 0 (0.00 per sec.) 52 53 General statistics: 54 total time: 120.0142s 55 total number of events: 37728 56 57 Latency (ms): 58 min: 6.91 59 avg: 50.89 60 max: 541.98 61 95th percentile: 74.46 62 sum: 1919973.66 63 64 Threads fairness: 65 events (avg/stddev): 2358.0000/62.04 66 execution time (avg/stddev): 119.9984/0.00
rocksdb只读测试
1 [root@rocksdb sysbench-1.0.17]# sysbench /usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua 2 > --mysql-host=localhost 3 > --mysql-port=3306 4 > --mysql-user=root 5 > --mysql-password=rooT_258 6 > --mysql-db=sbtest 7 > --oltp-tables-count=10 8 > --oltp-table-size=10000000 9 > --oltp-dist-type=uniform 10 > --oltp-read-only=on 11 > --oltp-test-mode=nontrx 12 > --oltp-nontrx-mode=select 13 > --rand-init=on 14 > --db-driver=mysql 15 > --report-interval=10 16 > --threads=16 17 > --time=120 18 > run 19 sysbench 1.0.17 (using bundled LuaJIT 2.1.0-beta2) 20 21 Running the test with following options: 22 Number of threads: 16 23 Report intermediate results every 10 second(s) 24 Initializing random number generator from current time 25 26 27 Initializing worker threads... 28 29 Threads started! 30 31 [ 10s ] thds: 16 tps: 496.85 qps: 7967.30 (r/w/o: 6972.01/0.00/995.29) lat (ms,95%): 41.10 err/s: 0.00 reconn/s: 0.00 32 [ 20s ] thds: 16 tps: 486.83 qps: 7788.72 (r/w/o: 6815.05/0.00/973.66) lat (ms,95%): 42.61 err/s: 0.00 reconn/s: 0.00 33 [ 30s ] thds: 16 tps: 491.92 qps: 7864.35 (r/w/o: 6880.51/0.00/983.84) lat (ms,95%): 41.85 err/s: 0.00 reconn/s: 0.00 34 [ 40s ] thds: 16 tps: 480.90 qps: 7702.69 (r/w/o: 6740.99/0.00/961.70) lat (ms,95%): 43.39 err/s: 0.00 reconn/s: 0.00 35 [ 50s ] thds: 16 tps: 492.40 qps: 7873.26 (r/w/o: 6888.37/0.00/984.90) lat (ms,95%): 41.85 err/s: 0.00 reconn/s: 0.00 36 [ 60s ] thds: 16 tps: 491.01 qps: 7858.11 (r/w/o: 6876.20/0.00/981.91) lat (ms,95%): 42.61 err/s: 0.00 reconn/s: 0.00 37 [ 70s ] thds: 16 tps: 507.61 qps: 8120.91 (r/w/o: 7105.80/0.00/1015.11) lat (ms,95%): 38.94 err/s: 0.00 reconn/s: 0.00 38 [ 80s ] thds: 16 tps: 492.30 qps: 7875.69 (r/w/o: 6890.99/0.00/984.70) lat (ms,95%): 41.10 err/s: 0.00 reconn/s: 0.00 39 [ 90s ] thds: 16 tps: 497.10 qps: 7952.73 (r/w/o: 6958.42/0.00/994.30) lat (ms,95%): 40.37 err/s: 0.00 reconn/s: 0.00 40 [ 100s ] thds: 16 tps: 503.09 qps: 8046.67 (r/w/o: 7040.50/0.00/1006.17) lat (ms,95%): 39.65 err/s: 0.00 reconn/s: 0.00 41 [ 110s ] thds: 16 tps: 433.50 qps: 6941.71 (r/w/o: 6074.71/0.00/867.00) lat (ms,95%): 55.82 err/s: 0.00 reconn/s: 0.00 42 [ 120s ] thds: 16 tps: 370.69 qps: 5930.89 (r/w/o: 5189.80/0.00/741.09) lat (ms,95%): 80.03 err/s: 0.00 reconn/s: 0.00 43 SQL statistics: 44 queries performed: 45 read: 804454 46 write: 0 47 other: 114922 48 total: 919376 49 transactions: 57461 (478.73 per sec.) 50 queries: 919376 (7659.72 per sec.) 51 ignored errors: 0 (0.00 per sec.) 52 reconnects: 0 (0.00 per sec.) 53 54 General statistics: 55 total time: 120.0260s 56 total number of events: 57461 57 58 Latency (ms): 59 min: 2.97 60 avg: 33.41 61 max: 484.33 62 95th percentile: 44.98 63 sum: 1919958.53 64 65 Threads fairness: 66 events (avg/stddev): 3591.3125/134.95 67 execution time (avg/stddev): 119.9974/0.01
innodb压缩读写测试
1 [root@rocksdb sysbench-1.0.17]# sysbench /usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua 2 > --mysql-host=localhost 3 > --mysql-port=3306 4 > --mysql-user=root 5 > --mysql-password=rooT_258 6 > --mysql-db=tt 7 > --oltp-tables-count=10 8 > --oltp-table-size=10000000 9 > --oltp-dist-type=uniform 10 > --oltp-read-only=off 11 > --oltp-test-mode=complex 12 > --rand-init=on 13 > --db-driver=mysql 14 > --report-interval=10 15 > --threads=16 16 > --time=120 17 > run 18 sysbench 1.0.17 (using bundled LuaJIT 2.1.0-beta2) 19 20 Running the test with following options: 21 Number of threads: 16 22 Report intermediate results every 10 second(s) 23 Initializing random number generator from
