机器
cat /etc/
RedHat-release | xargs echo '版本 ' && dmidecode -s system-product-name | xargs echo '是否虚拟化 ' && cat /proc/cpuinfo |grep "processor"|wc -l | xargs echo 'cpu核数 '
版本
CentOS Linux release 7.5.1804 (Core)
是否虚拟化 KVM
cpu核数 4
1、分别在mysql5.7和mysql8.0 中创建5个库，每个库中30个表（共1500个表），每个表记录数为10000
user=admin
passwd=admin
port=57222
host=127.0.0.1
#创建5个库,分别在每个库中创建30个表
for i in {1..5};do
mysql -u$user -p$passwd -P$port -h$host<<EOF
create database if not exists test_${i};
EOF
sysbench /usr/local/share/sysbench/oltp_read_write.lua --mysql_storage_engine=innodb --table-size=10000 --tables=30 --mysql-db=test_${i} --mysql-user=$user --mysql-password=$passwd --mysql-port=$port --mysql-host=$host --threads=8 --time=10 --report-interval=1 --events=0 --db-driver=mysql prepare
done;
2、自定义访问I_S.tables表的sysbench脚本
cat tests/mytest.lua
require("oltp_common")
function thread_init(thread_id)
drv=sysbench.sql.driver()
con=drv:connect()
end
local function get_rand_db()
return sysbench.rand.uniform(1, 5)
end
function event(thread_id)
local vid1
local dbprefix
vid1=get_rand_db()
dbprefix = "test_"
-- 生成5个db中的随机一个db,如 test_1 ,test_5
-- vid2=string.format("'%s%s'",test_,vid1)
vid2="'" .. dbprefix .. vid1 .. "'"
con:query("SELECT table_name, CONCAT(FORMAT(data_length / 1024 / 1024, 2), 'M') AS dbdata_size, CONCAT(FORMAT(index_length / 1024 / 1024, 2), 'M') AS dbindex_size, CONCAT(
FORMAT((data_length + index_length) / 1024 / 1024 / 1024,2), 'G') AS `db_size(G)`, AVG_ROW_LENGTH, table_rows, update_time FROM information_schema.tables WHERE table_schema =" .. vid2)
end
function thread_done()
con:disconnect()
end
3、脚本通过sysbench测试访问I_S.tables的qps
cat test_I_S_access.sh
user=admin
passwd=admin
host=127.0.0.1
#输入mysql端口参数
port=$1
# port=8015
#输入sysbench测试时间参数多少秒
run_time=$2
function get_create_tmp_tables(){
mysql -u$user -p$passwd -P$port -h$host<<EOF
select @@version as 'mysql version';
show global status like '%tmp_table%';
EOF
} 2>/dev/null
function begin_test(){
cd /usr/local/share/sysbench
sysbench ./tests/mytest.lua --mysql-db=test_1 --mysql-host=$host --mysql-port=$port --mysql-user=$user --mysql-password=$passwd --threads=40 --report-interval=10 --rand-type=uniform --time=$run_time run
}
service mysqld${port} restart
get_create_tmp_tables
begin_test
get_create_tmp_tables
