MySQL受限环境打压

1 硬件环境

i7-6700, 32GB内存, SSD硬盘

2 启动脚本

MySQL启动后,要调整一下最大连接数(docker的MySQL默认150)

mysql -h 127.0.0.1 -P 13306 -u root -p

# 默认是151
SHOW VARIABLES LIKE '%max_con%';
+---------------------------------------+-------+
| Variable_name                         | Value |
+---------------------------------------+-------+
| max_connect_errors                    | 100   |
| max_connections                       | 151   |
| mysqlx_max_connections                | 100   |
| performance_schema_max_cond_classes   | 100   |
| performance_schema_max_cond_instances | -1    |
+---------------------------------------+-------+

# 修改为100
SET GLOBAL max_connections = 10000;

3 打压脚本

先安装sysbench

curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.deb.sh | sudo bash
sudo apt -y install sysbench

执行打压脚本之前,一定要改一下nofile(打压机器的)

ulimit -n 65535

docker启动脚本

#!/bin/bash

NAME="perf_mysql"
VOLUME="/home/coder4/docker_data/perf_mysql"
PUID="1000"
PGID="1000"
MYSQL_DATABASE="perf"
MYSQL_USER="perf"
MYSQL_PASSWORD="perf123"

mkdir -p $VOLUME

    
# --memory 512M

docker network create nw
docker ps -q -a --filter "name=$NAME" | xargs -I {} docker rm -f {}
docker run \
    --hostname $NAME \
    --name $NAME \
    --cpus 1.8 \
    --memory 1024M \
    --volume "$VOLUME":/var/lib/mysql \
    --env MYSQL_ALLOW_EMPTY_PASSWORD=true \
    --env MYSQL_DATABASE=$MYSQL_DATABASE \
    --env MYSQL_USER=$MYSQL_USER \
    --env MYSQL_PASSWORD=$MYSQL_PASSWORD \
    --env PUID=$PUID \
    --env PGID=$PGID \
    --ulimit nofile=65536:65536 \
    -p 13306:3306 \
    --network nw \
    --detach \
    --restart always \
    mysql:8

上面的cpu根据限制进行修改

4 单写

打压脚本

#!/bin/bash

HOST="127.0.0.1"
PORT="13306"
DB="perf"
USER="perf"
PASS="perf123"

TABLES="5"
SIZE="10000"
THREADS="32"

CMD="sysbench /usr/share/sysbench/oltp_insert.lua --mysql-host=$HOST --mysql-port=$PORT --mysql-user=$USER --mysql-password=$PASS --mysql-db=$DB --tables=$TABLES --table-size=$SIZE --report-interval=10 --time=120 --events=0 --threads=$THREADS"

$CMD cleanup
$CMD prepare 
$CMD run

1核

2核

3核

4核

5 单读

打压脚本

#!/bin/bash

HOST="127.0.0.1"
PORT="13306"
DB="perf"
USER="perf"
PASS="perf123"

TABLES="5"
SIZE="10000"
THREADS="32"

CMD="sysbench /usr/share/sysbench/oltp_read_only.lua --mysql-host=$HOST --mysql-port=$PORT --mysql-user=$USER --mysql-password=$PASS --mysql-db=$DB --tables=$TABLES --table-size=$SIZE --report-interval=10 --time=120 --events=0 --threads=$THREADS"

$CMD cleanup
$CMD prepare 
$CMD run 

1核

2核

3核

4核

 

Leave a Reply

Your email address will not be published.