my06_sysbench install for mysql 并初始化表数据
sysbench安装
**************************************************************
安装sysbench依赖包
rpm -q automake libtool m4 autoconf
yum install -y automake libtool m4 autoconf
mysql链接库检查
# ll /usr/local/mysql/lib/libmysqlclient_r.so
ls: cannot access /usr/local/mysql/lib/libmysqlclient_r.so: No such file or directory
# ll /usr/lib64/libmysqlclient.so.20
ls: cannot access /usr/lib64/libmysqlclient.so.20: No such file or directory
cd /usr/local/mysql/lib/
ln -s libmysqlclient.so.20 libmysqlclient_r.so
ln -s /usr/local/mysql/lib/libmysqlclient.so.2 /usr/lib64/libmysqlclient.so.20
sysbench使用的mysql为mysql5.7（在使用mysql8.0时，make阶段报错）
# mysql --version
mysql Ver 14.14 Distrib 5.7.19, for linux-glibc2.12 (x86_64) using EditLine wrapper
使用bzr安装sysbench
yum install -y epel-release
yum install -y bzr
bzr branch lp:sysbench
cd sysbench
./autogen.sh
./configure --prefix=/usr/local/sysbench --with-mysql-includes=/usr/local/mysql/include --with-mysql-libs=/usr/local/mysql/lib
make
make install
vim /etc/profile
PATH=/usr/local/sysbench/bin:$PATH
# sysbench --version
sysbench 0.5
sysbench初始化表数据
***************************************************************
要测试的数据库配置-->1U1G的配置，mysql8.0、innodb+row+gtid
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.11 |
+-----------+
1 row in set (0.00 sec)
单表的初始化与清除，每张表1000行记录
sysbench --test=/opt/soft/sysbench/sysbench/tests/db/parallel_prepare.lua --oltp-table-size=1000 --mysql-table-engine=innodb --mysql-user=automng --mysql-password=Automng_123 --mysql-port=3306 --mysql-host=itoracle --mysql-db=vodb run
sysbench --test=/opt/soft/sysbench/sysbench/tests/db/parallel_prepare.lua --oltp-table-size=1000 --mysql-table-engine=innodb --mysql-user=automng --mysql-password=Automng_123 --mysql-port=3306 --mysql-host=itoracle --mysql-db=vodb cleanup
确认一下sysbench自动创建表信息
mysql> desc sbtest1; +-------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | k | int(10) unsigned | NO | MUL | 0 | | | c | char(120) | NO | | | | | pad | char(60) | NO | | | | +-------+------------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) mysql> show index from sbtest1; +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+ | sbtest1 | 0 | PRIMARY | 1 | id | A | 1000 | NULL | NULL | | BTREE | | | YES | | sbtest1 | 1 | k_1 | 1 | k | A | 177 | NULL | NULL | | BTREE | | | YES | +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+ 2 rows in set (0.04 sec)
共四字段，一个主键，一个普通索引列，两个char类型列（列值基本各不相同，由数字和"-"组成，长度为109字节与54字节）。
mysql> select * from sbtest1 limit 3; +----+-----+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+ | id | k | c | pad | +----+-----+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+ | 1 | 500 | 08566691963-88624912351-16662227201-46648573979-64646226163-77505759394-75470094713-41097360717-15161106334-50535565977 | 63188288836-92351140030-06390587585-66802097351-49282961843 | | 2 | 503 | 95969429576-20587925969-20202408199-67602281819-18293380360-38184587501-73192830026-41693404212-56705243222-89212376805 | 09512147864-77936258834-40901700703-13541171421-15205431759 | | 3 | 329 | 26283585383-48610978532-72166636310-67148386979-89643583984-06169170732-23477134062-17788128188-73465768032-24619558652 | 21979564480-87492594656-60524686334-78820761788-57684966682 | +----+-----+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+ 3 rows in set (0.01 sec)
多表的创建与清除，在不指定表大小的情况下，默认每张表1万行记录，此处启动2个线程，创建5张表，每张表1万行记录。
sysbench --test=/opt/soft/sysbench/sysbench/tests/db/parallel_prepare.lua --oltp-tables-count=5 --num-threads=2 --mysql-table-engine=innodb --mysql-user=automng --mysql-password=Automng_123 --mysql-port=3306 --mysql-host=itoracle --mysql-db=vodb run
sysbench --test=/opt/soft/sysbench/sysbench/tests/db/parallel_prepare.lua --oltp-tables-count=5 --num-threads=2 --mysql-table-engine=innodb --mysql-user=automng --mysql-password=Automng_123 --mysql-port=3306 --mysql-host=itoracle --mysql-db=vodb cleanup
