SysBench简单安装测试

一、安装

[root@observer3-2 ~]# yum install make automake autoconf libtool gcc mariadb-devel mariadb -y

本次环境已经安装jdk1.8

[root@observer3-2 ~]# yum install java-1.8.0-openjdk.x86_64 -y

[admin@observer3-2 app]$ wget https://github.com/akopytov/sysbench/archive/refs/tags/1.0.20.zip

[admin@observer3-2 app]$ unzip 1.0.20.zip

[admin@observer3-2 app]$ cd sysbench-1.0.20/

[admin@observer3-2 sysbench-1.0.20]$ ./autogen.sh

[admin@observer3-2 sysbench-1.0.20]$ ./configure --prefix=/usr/sysbench/ --with-mysql-includes=/usr/include/mysql/ --with-mysql-libs=/usr/lib64/mysql/ --with-mysql

[admin@observer3-2 sysbench-1.0.20]$ make

[root@observer3-2 sysbench-1.0.20]# make install

[root@observer3-2 sysbench-1.0.20]# cp -r /usr/sysbench/share/sysbench/* /usr/sysbench/bin/

[admin@observer3-2 sysbench-1.0.20]$ ./src/sysbench --help

参数说明：

二、手动进行Sysbench测试

2.1 环境调优

observer调优

ALTER system SET enable_sql_audit=false;

ALTER system SET enable_perf_event=false;

ALTER system SET syslog_level='PERF';

alter system set enable_record_trace_log=false;

2.2 操作步骤

1.运行以下命令，初始化数据库

/usr/sysbench/bin/sysbench oltp_read_write.lua --mysql-host=192.168.220.117 --mysql-port=2881 --mysql-db=test --mysql-user=root@obcp_t1 --mysql-password=LIUjie\!\@34 --table_size=1000000 --tables=30 --threads=150 --report-interval=10 --rand-type=uniform --time=60 cleanup

2.运行以下命令，新建表

/usr/sysbench/bin/sysbench oltp_read_write.lua --mysql-host=192.168.220.117 --mysql-port=2881 --mysql-db=test --mysql-user=root@obcp_t1 --mysql-password=LIUjie\!\@34 --table_size=1000000 --tables=30 --threads=150 --report-interval=10 --rand-type=uniform --time=60 prepare

3.运行以下命令，执行测试

/usr/sysbench/bin/sysbench oltp_read_write.lua --mysql-host=192.168.220.117 --mysql-port=2881 --mysql-db=test --mysql-user=root@obcp_t1 --mysql-password=LIUjie\!\@34 --table_size=100000 --tables=30 --threads=150 --report-interval=10 --time=60 --rand-type=uniform --db-ps-mode=disable run

[admin@ocp3 lua]$ /usr/sysbench/bin/sysbench oltp_read_write.lua --mysql-host=192.168.220.117 --mysql-port=2881 --mysql-db=test --mysql-user=root@obcp_t1 --mysql-password=LIUjie\!\@34 --table_size=100000 --tables=30 --threads=150 --report-interval=10 --time=60 --rand-type=uniform --db-ps-mode=disable run

sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:

Number of threads: 150

Report intermediate results every 10 second(s)

Initializing random number generator from current time

Initializing worker threads...

Threads started!

[ 10s ] thds: 150 tps: 0.39 qps: 280.90 (r/w/o: 206.98/58.55/15.38) lat (ms,95%): 10158.80 err/s: 0.00 reconn/s: 0.00

[ 20s ] thds: 150 tps: 29.80 qps: 530.70 (r/w/o: 405.30/65.71/59.69) lat (ms,95%): 10722.67 err/s: 0.00 reconn/s: 0.00

[ 30s ] thds: 150 tps: 30.01 qps: 598.26 (r/w/o: 418.38/119.75/60.13) lat (ms,95%): 5709.50 err/s: 0.00 reconn/s: 0.00

[ 40s ] thds: 150 tps: 30.09 qps: 584.77 (r/w/o: 402.91/121.77/60.09) lat (ms,95%): 5124.81 err/s: 0.00 reconn/s: 0.00

[ 50s ] thds: 150 tps: 29.73 qps: 565.24 (r/w/o: 390.46/115.22/59.56) lat (ms,95%): 6594.16 err/s: 0.00 reconn/s: 0.00

[ 60s ] thds: 150 tps: 30.48 qps: 648.62 (r/w/o: 458.04/129.62/60.95) lat (ms,95%): 5312.73 err/s: 0.00 reconn/s: 0.00

SQL statistics:

queries performed:

read: 23156

write: 6616

other: 3308

total: 33080

transactions: 1654 (26.71 per sec.)

queries: 33080 (534.13 per sec.)

ignored errors: 0 (0.00 per sec.)

reconnects: 0 (0.00 per sec.)

General statistics:

total time: 61.9296s

total number of events: 1654

Latency (ms):

min: 1808.67

avg: 5541.98

max: 10974.40

95th percentile: 10343.39

sum: 9166431.27

Threads fairness:

events (avg/stddev): 11.0267/0.16

execution time (avg/stddev): 61.1095/0.34

参数配置

三、报错

报错1

解决：

[admin@ocp3 sysbench-1.0.20]$ cd src

[admin@ocp3 src]$ ls

CMakeLists.txt Makefile sb_ck_pr.h sb_histogram.h sb_lua.c sb_rand.c sb_timer.c sb_win.c tests

db_driver.c Makefile.am sb_counter.c sb_histogram.o sb_lua.h sb_rand.h sb_timer.h sb_win.h xoroshiro128plus.h

db_driver.h Makefile.in sb_counter.h sb_list.h sb_lua.o sb_rand.o sb_timer.o sysbench

db_driver.o sb_barrier.c sb_counter.o sb_logger.c sb_options.c sb_thread.c sb_util.c sysbench.c

drivers sb_barrier.h sb_global.h sb_logger.h sb_options.h sb_thread.h sb_util.h sysbench.h

lua sb_barrier.o sb_histogram.c sb_logger.o sb_options.o sb_thread.o sb_util.o sysbench.o

[admin@ocp3 src]$ cd lua/

[admin@ocp3 lua]$ ls

bulk_insert.lua Makefile.am oltp_delete.lua oltp_read_only.lua oltp_update_non_index.lua select_random_ranges.lua

internal Makefile.in oltp_insert.lua oltp_read_write.lua oltp_write_only.lua

Makefile oltp_common.lua oltp_point_select.lua oltp_update_index.lua select_random_points.lua

[admin@ocp3 lua]$ ./oltp_read_write.lua

sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

FATAL: ./oltp_common.lua:28: Command is required. Supported commands: prepare, prewarm, run, cleanup, help

报错2

解决

obclient [(none)]> show variables like '%timeout%';

+---------------------+------------------+

| Variable_name | Value |

+---------------------+------------------+

| connect_timeout | 10 |

| interactive_timeout | 28800 |

| lock_wait_timeout | 31536000 |

| net_read_timeout | 30 |

| net_write_timeout | 60 |

| ob_pl_block_timeout | 3216672000000000 |

| ob_query_timeout | 10000000 |

| ob_trx_idle_timeout | 120000000 |

| ob_trx_lock_timeout | -1 |

| ob_trx_timeout | 100000000 |

| wait_timeout | 28800 |

+---------------------+------------------+

11 rows in set (0.019 sec)

obclient [(none)]> set global ob_query_timeout=10000000000000;

Query OK, 0 rows affected (0.322 sec)

obclient [(none)]> set global ob_trx_idle_timeout=20000000000;

Query OK, 0 rows affected (0.055 sec)

obclient [(none)]> set global ob_trx_timeout=18000000000;

Query OK, 0 rows affected (0.108 sec)

解决之后，删除重建