近期遇到一个单机的MySQL, 因为部署在机械盘, 在高峰时期服务器负载很高, 修改 redo log 参数之后发现服务器负载有所降低, 索性再做个性能测试验证一些参数
服务器: Centos7.9 8c32g MySQL: 8.0.32 压测工具: sysbench
# 卸载其他MySQL、mariadb
rpm -qa | grep -i mariadb
rpm -evh mariadb-libs-5.5.60-1.el7-5.x86_64
查看mariadb依赖
yum list | grep -i mariadb
卸载依赖
yum remove -y mariadb-*
rm -rf /var/lib/mysql*
yum -y install openssl-devel
rpm -ivh mysql-community-common-8.0.32-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-plugins-8.0.32-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-8.0.32-1.el7.x86_64.rpm
rpm -ivh mysql-community-devel-8.0.32-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-compat-8.0.32-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-8.0.32-1.el7.x86_64.rpm
rpm -ivh mysql-community-icu-data-files-8.0.32-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-8.0.32-1.el7.x86_64.rpm启动MySQL
systemctl start mysqld获取MySQL密码
grep 'temporary password' /var/log/mysqld.log修改密码
mysql -uroot -pKino123.
SET PASSWORD FOR 'root'@'localhost'= "Kino123.";
update user set host='%' where user ='root';
FLUSH PRIVILEGES;yum install -y yum-utils device-mapper-persistent-data lvm2
yum-config-manager --add-repo http://mirrors.aliyun.com/docker-ce/linux/centos/docker-ce.repo
yum install docker-ce docker-ce-cli containerd.io -y
cat > /etc/docker/daemon.json << \EOF
{
"registry-mirrors": ["https://k0ki64fw.mirror.aliyuncs.com"]
}
EOF
systemctl restart docker
docker ps# /opt/prometheus/data目录，准备用来挂载放置prometheus的数据
# /opt/prometheus/config目录，准备用来放置prometheus的配置文件
# /opt/prometheus/rules目录，准备用来挂载放置prometheus的规则文件
mkdir -p /data/prometheus/{data,config,rules}
# 授权相关文件夹权限
chmod -R 777 /data/prometheus/data
chmod -R 777 /data/prometheus/config
chmod -R 777 /data/prometheus/rules
# 配置文件
cat > /data/prometheus/config/prometheus.yml << \EOF
global:
# 数据采集间隔
scrape_interval: 45s
# 告警检测间隔
evaluation_interval: 45s
# 告警规则
rule_files:
# 这里匹配指定目录下所有的.rules文件
- /prometheus/rules/*.rules
# 采集配置
scrape_configs:
# 采集项(prometheus)
- job_name: 'prometheus'
static_configs:
# prometheus自带了对自身的exporter监控程序，所以不需额外安装exporter就可配置采集项
- targets: ['localhost:9090']
EOF
# 查看一下配置文件
cat /data/prometheus/config/prometheus.yml
wget https://jzdata-gz.tos-cn-guangzhou.volces.com/kino/t.tar.gz
tar -zxvf t.tar.gz
docker load -i pro.img
docker load -i gra.img
# 启动prometheus
# config.file：指定容器中，配置文件的位置
# web.enable-lifecycle：启动此项后，当配置文件发生变化后，可通过HTTP API 发送 post 请求到 /-/reload，实现热加载，如：curl -X POST http://47.105.39.189:9090/-/reload
# -v /etc/localtime:/etc/localtime:ro表示让容器使用宿主机的时间, :ro表示只读(注：此方式只针对宿主机和容器的时区文件均为/etc/localtime)
docker rm -f prometheus
docker run --name prometheus -d \
-p 9090:9090 \
-v /etc/localtime:/etc/localtime:ro \
-v /data/prometheus/data:/prometheus/data \
-v /data/prometheus/config:/prometheus/config \
-v /data/prometheus/rules:/prometheus/rules \
prom/prometheus:v2.41.0 --web.enable-lifecycle --config.file=/prometheus/config/prometheus.yml
mkdir -p /data/grafana/{data,plugins,config}
# 授权相关文件夹权限
chmod -R 777 /data/grafana/data
chmod -R 777 /data/grafana/plugins
chmod -R 777 /data/grafana/config
# 先临时启动一个容器
docker run --name grafana-tmp -d -p 3000:3000 grafana/grafana:9.3.2
# 将容器中默认的配置文件拷贝到宿主机上
docker cp grafana-tmp:/etc/grafana/grafana.ini /data/grafana/config/grafana.ini
# 移除临时容器
docker rm -f grafana-tmp
# 启动prometheus
# 环境变量GF_SECURITY_ADMIN_PASSWORD：指定admin的密码
# 环境变量GF_INSTALL_PLUGINS：指定启动时需要安装得插件
# grafana-clock-panel代表时间插件
# grafana-simple-json-datasource代表json数据源插件
# grafana-piechart-panel代表饼图插件
docker run -d \
-p 3000:3000 \
--name=grafana \
-v /etc/localtime:/etc/localtime:ro \
-v /data/grafana/data:/var/lib/grafana \
-v /data/grafana/plugins/:/var/lib/grafana/plugins \
-v /data/grafana/config/grafana.ini:/etc/grafana/grafana.ini \
-e "GF_SECURITY_ADMIN_PASSWORD=admin" \
-e "GF_INSTALL_PLUGINS=grafana-clock-panel,grafana-simple-json-datasource,grafana-piechart-panel" \
grafana/grafana:9.3.2
wget https://jzdata-gz.tos-cn-guangzhou.volces.com/kino/mysqld_exporter-0.13.0.linux-amd64.tar.gz
tar xvfz mysqld_exporter-0.13.0.linux-amd64.tar.gz
sudo cp mysqld_exporter-0.13.0.linux-amd64/mysqld_exporter /usr/local/bin/
vim /usr/local/bin/.my.cnf
#写入以下内容
[client]
user=root
password=Kino123.
nohup mysqld_exporter --config.my-cnf="/usr/local/bin/.my.cnf" --log.level=error --web.listen-address=:9300 &
vim /data/prometheus/config/prometheus.yml
- job_name: mysql-status
static_configs:
- targets: ['172.29.254.70:9300']
docker restart prometheusyum -y install make automake libtool pkgconfig libaio-devel openssl-devel mysql-devel
cd /usr/src/
wget https://github.com/akopytov/sysbench/archive/refs/tags/1.0.20.tar.gz
tar xvf 1.0.20.tar.gz
cd sysbench-1.0.20/
./autogen.sh
./configure
make -j
make install# 造数
sysbench oltp_read_write --mysql-host=172.29.254.70 --mysql-port=3306 --mysql-user=root --mysql-password=Kino123. --mysql-db=sbtest --tables=30 --table-size=500000 --threads=30 prepare
# 预热加载到内存
sysbench oltp_read_write --mysql-host=172.29.254.70 --mysql-port=3306 --mysql-user=root --mysql-password=Kino123. --mysql-db=sbtest --tables=30 --table-size=500000 --threads=30 prewarm
# 清理
sysbench oltp_read_write --mysql-host=172.29.254.70 --mysql-port=3306 --mysql-user=root --mysql-password=Kino123. --mysql-db=sbtest --tables=30 cleanup
不设置 redo log 参数
sysbench oltp_read_write --mysql-host=172.29.254.70 --mysql-port=3306 --mysql-user=root --mysql-password=Kino123. --mysql-db=sbtest --tables=30 --table-size=500000 --threads=32 --time=600 --report-interval=10 run
[ 10s ] thds: 32 tps: 1152.15 qps: 23103.81 (r/w/o: 16174.90/4621.40/2307.50) lat (ms,95%): 35.59 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 32 tps: 1153.42 qps: 23042.82 (r/w/o: 16129.12/4606.86/2306.83) lat (ms,95%): 35.59 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 32 tps: 1150.12 qps: 23016.36 (r/w/o: 16114.05/4602.07/2300.24) lat (ms,95%): 36.24 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 32 tps: 1137.77 qps: 22749.28 (r/w/o: 15925.14/4548.60/2275.55) lat (ms,95%): 36.24 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 32 tps: 1128.93 qps: 22596.33 (r/w/o: 15815.04/4523.43/2257.86) lat (ms,95%): 36.89 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 32 tps: 1138.98 qps: 22774.50 (r/w/o: 15943.42/4553.12/2277.96) lat (ms,95%): 36.24 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 32 tps: 1139.79 qps: 22766.56 (r/w/o: 15934.30/4553.17/2279.09) lat (ms,95%): 36.89 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 32 tps: 1134.55 qps: 22712.77 (r/w/o: 15901.65/4541.51/2269.61) lat (ms,95%): 36.24 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 32 tps: 1128.48 qps: 22582.26 (r/w/o: 15805.89/4519.41/2256.96) lat (ms,95%): 36.89 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 32 tps: 1134.10 qps: 22677.40 (r/w/o: 15876.30/4532.90/2268.20) lat (ms,95%): 36.89 err/s: 0.00 reconn/s: 0.00服务器压力
top - 02:12:58 up 2:55, 2 users, load average: 12.48, 12.38, 12.49
Tasks: 136 total, 2 running, 134 sleeping, 0 stopped, 0 zombie
%Cpu(s): 0.1 us, 0.3 sy, 0.0 ni, 94.1 id, 5.4 wa, 0.0 hi, 0.0 si, 0.0 st
KiB Mem : 32779340 total, 388412 free, 6090668 used, 26300260 buff/cache
KiB Swap: 0 total, 0 free, 0 used. 26285144 avail Mem
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
27019 mysql 20 0 9134640 5.1g 19064 S 3.0 16.2 71:49.63 mysqld
1491 root 20 0 115444 8788 6800 S 0.7 0.0 0:36.66 AliYunDun
310 root 0 -20 0 0 0 S 0.3 0.0 0:55.47 kworker/0:1H
317 root 20 0 0 0 0 S 0.3 0.0 1:34.04 jbd2/vda1-8
1502 root 20 0 182756 41568 14012 S 0.3 0.1 1:05.83 AliYunDunMonito
1 root 20 0 191160 6164 2620 S 0.0 0.0 0:04.65 systemd
2 root 20 0 0 0 0 S 0.0 0.0 0:00.00 kthreaddmysql -uroot -pKino123. 50331648 2
设置 redo log 参数
vim /etc/my.cnf
innodb_log_file_size = 2147483648
innodb_log_files_in_group = 2
systemctl restart mysqldsysbench oltp_read_write --mysql-host=172.29.254.70 --mysql-port=3306 --mysql-user=root --mysql-password=Kino123. --mysql-db=sbtest --tables=30 --table-size=500000 --threads=32 --time=600 --report-interval=10 run
[ 10s ] thds: 32 tps: 280.88 qps: 5654.70 (r/w/o: 3963.84/1125.90/564.95) lat (ms,95%): 235.74 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 32 tps: 269.21 qps: 5383.49 (r/w/o: 3767.13/1077.94/538.42) lat (ms,95%): 223.34 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 32 tps: 346.80 qps: 6950.79 (r/w/o: 4866.29/1390.90/693.60) lat (ms,95%): 170.48 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 32 tps: 455.20 qps: 9088.15 (r/w/o: 6362.07/1815.69/910.40) lat (ms,95%): 132.49 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 32 tps: 567.10 qps: 11360.19 (r/w/o: 7950.06/2275.92/1134.21) lat (ms,95%): 108.68 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 32 tps: 697.50 qps: 13941.58 (r/w/o: 9759.76/2786.82/1395.01) lat (ms,95%): 84.47 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 32 tps: 834.00 qps: 16689.63 (r/w/o: 11682.05/3339.59/1667.99) lat (ms,95%): 64.47 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 32 tps: 918.50 qps: 18373.78 (r/w/o: 12860.78/3676.00/1837.00) lat (ms,95%): 55.82 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 32 tps: 973.17 qps: 19450.81 (r/w/o: 13619.92/3884.56/1946.33) lat (ms,95%): 49.21 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 32 tps: 1043.12 qps: 20863.20 (r/w/o: 14601.95/4175.00/2086.25) lat (ms,95%): 41.10 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 32 tps: 1058.70 qps: 21173.33 (r/w/o: 14822.32/4233.61/2117.40) lat (ms,95%): 42.61 err/s: 0.00 reconn/s: 0.00top - 01:49:32 up 2:32, 2 users, load average: 9.98, 13.50, 11.28
Threads: 69 total, 0 running, 69 sleeping, 0 stopped, 0 zombie
%Cpu(s): 0.1 us, 0.4 sy, 0.0 ni, 94.3 id, 5.2 wa, 0.0 hi, 0.0 si, 0.0 st
KiB Mem : 32779340 total, 383904 free, 6596756 used, 25798680 buff/cache
KiB Swap: 0 total, 0 free, 0 used. 25779056 avail Mem
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
26777 mysql 20 0 9575976 5.5g 18692 S 1.0 17.7 0:04.99 ib_pg_flush_co
26773 mysql 20 0 9575976 5.5g 18692 D 0.3 17.7 0:04.94 ib_io_wr-1
26774 mysql 20 0 9575976 5.5g 18692 S 0.3 17.7 0:05.03 ib_io_wr-2
26775 mysql 20 0 9575976 5.5g 18692 S 0.3 17.7 0:04.81 ib_io_wr-3
26776 mysql 20 0 9575976 5.5g 18692 S 0.3 17.7 0:05.28 ib_io_wr-4
26785 mysql 20 0 9575976 5.5g 18692 S 0.3 17.7 0:37.13 ib_log_writer
26786 mysql 20 0 9575976 5.5g 18692 S 0.3 17.7 0:03.02 ib_log_files_g
26806 mysql 20 0 9575976 5.5g 18692 S 0.3 17.7 0:14.25 ib_srv_purge
26756 mysql 20 0 9575976 5.5g 18692 S 0.0 17.7 0:00.78 mysqld
26767 mysql 20 0 9575976 5.5g 18692 S 0.0 17.7 0:00.03 ib_io_ibuf