使用MySQL命令登录数据库,并创建测试库“loadtest”,用弹性云服务器ECS的私网IP替换命令中的【mysql-ip】命令如下
mysql -u root -h 【mysql-ip】 -p -e "create database loadtest"
使用sysbench命令导入测试数据到“loadtest”数据库;用弹性云服务器ECS的私网IP替换命令中的【host】,命令如下
sysbench /root/sysbench/tests/include/oltp_legacy/oltp.lua --db-driver=mysql --mysql-db=loadtest --mysql-user=root --mysql-password='Cloud12#$' --mysql-port=3306 --mysql-host='【host】' --oltp-tables-count=10 --oltp-table-size=10000 --threads=20 prepare
oltp.lua文件
pathtest = string.match(test, "(.*/)") if pathtest then dofile(pathtest .. "common.lua") else require("common") end function thread_init() set_vars() if (((db_driver == "mysql") or (db_driver == "attachsql")) and mysql_table_engine == "myisam") then local i local tables = {} for i=1, oltp_tables_count do tables[i] = string.format("sbtest%i WRITE", i) end begin_query = "LOCK TABLES " .. table.concat(tables, " ,") commit_query = "UNLOCK TABLES" else begin_query = "BEGIN" commit_query = "COMMIT" end end function get_range_str() local start = sb_rand(1, oltp_table_size) return string.format(" WHERE id BETWEEN %u AND %u", start, start + oltp_range_size - 1) end function event() local rs local i local table_name local c_val local pad_val local query table_name = "sbtest".. sb_rand_uniform(1, oltp_tables_count) if not oltp_skip_trx then db_query(begin_query) end if not oltp_write_only then for i=1, oltp_point_selects do rs = db_query("SELECT c FROM ".. table_name .." WHERE id=" .. sb_rand(1, oltp_table_size)) end if oltp_range_selects then for i=1, oltp_simple_ranges do rs = db_query("SELECT c FROM ".. table_name .. get_range_str()) end for i=1, oltp_sum_ranges do rs = db_query("SELECT SUM(K) FROM ".. table_name .. get_range_str()) end for i=1, oltp_order_ranges do rs = db_query("SELECT c FROM ".. table_name .. get_range_str() .. " ORDER BY c") end for i=1, oltp_distinct_ranges do rs = db_query("SELECT DISTINCT c FROM ".. table_name .. get_range_str() .. " ORDER BY c") end end end if not oltp_read_only then for i=1, oltp_index_updates do rs = db_query("UPDATE " .. table_name .. " SET k=k+1 WHERE id=" .. sb_rand(1, oltp_table_size)) end for i=1, oltp_non_index_updates do c_val = sb_rand_str("###########-###########-###########-###########-###########-###########-###########-###########-###########-###########") query = "UPDATE " .. table_name .. " SET c='" .. c_val .. "' WHERE id=" .. sb_rand(1, oltp_table_size) rs = db_query(query) if rs then print(query) end end for i=1, oltp_delete_inserts do i = sb_rand(1, oltp_table_size) rs = db_query("DELETE FROM " .. table_name .. " WHERE id=" .. i) c_val = sb_rand_str([[ ###########-###########-###########-###########-###########-###########-###########-###########-###########-###########]]) pad_val = sb_rand_str([[ ###########-###########-###########-###########-###########]]) rs = db_query("INSERT INTO " .. table_name .. " (id, k, c, pad) VALUES " .. string.format("(%d, %d, '%s', '%s')",i, sb_rand(1, oltp_table_size) , c_val, pad_val)) end end -- oltp_read_only if not oltp_skip_trx then db_query(commit_query) end end
执行测试【保持命令行开启】,用弹性云服务器ECS的私网IP替换命令中的【host】,命令如下:
sysbench /root/sysbench/tests/include/oltp_legacy/insert.lua --db-driver=mysql --mysql-db=loadtest --mysql-user=root --mysql-password='Cloud12#$' --mysql-port=3306 --mysql-host='【host】' --oltp-tables-count=10 --oltp-table-size=1000 --max-time=3600 --max-requests=0 --threads=10 --report-interval=3 --rate=20 --forced-shutdown=1 run
insert.lua文件
pathtest = string.match(test, "(.*/)") if pathtest then dofile(pathtest .. "common.lua") else require("common") end function thread_init(thread_id) set_vars() end function event() local table_name local i local c_val local k_val local pad_val table_name = "sbtest".. sb_rand_uniform(1, oltp_tables_count) k_val = sb_rand(1, oltp_table_size) c_val = sb_rand_str([[ ###########-###########-###########-###########-###########-###########-###########-###########-###########-###########]]) pad_val = sb_rand_str([[ ###########-###########-###########-###########-###########]]) if (db_driver == "pgsql" and oltp_auto_inc) then rs = db_query("INSERT INTO " .. table_name .. " (k, c, pad) VALUES " .. string.format("(%d, '%s', '%s')", k_val, c_val, pad_val)) else if (oltp_auto_inc) then i = 0 else i = sb_rand_uniq() end rs = db_query("INSERT INTO " .. table_name .. " (id, k, c, pad) VALUES " .. string.format("(%d, %d, '%s', '%s')", i, k_val, c_val, pad_val)) end end
查看CPU情况
top
查看IO情况
iostat -d vda vdb -m 1 10