mysql 性能调优教程及结果对比

2024-05-31

#software

安装

为了便于对性能差异进行测试，本次使用 Docker 进行环境搭建。当然，这可能会导致性能瓶颈出现在磁盘和网络 I/O 上。

不过，就本次测试结果而言，并未出现这类问题。

services:
  mysql80_2gb:
    image: mysql:8.0
    container_name: mysql80_2gb
    environment:
      MYSQL_ROOT_PASSWORD: rootpassword
    deploy:
      resources:
        limits:
          memory: 2g
    volumes:
      - mysql80_2gb_data:/var/lib/mysql
    command: >
      --default-authentication-plugin=mysql_native_password

  mysql80_8gb:
    image: mysql:8.0
    container_name: mysql80_8gb
    environment:
      MYSQL_ROOT_PASSWORD: rootpassword
    deploy:
      resources:
        limits:
          memory: 8g
    volumes:
      - mysql80_8gb_data:/var/lib/mysql
    command: >
      --default-authentication-plugin=mysql_native_password

  mysql80_8gb_tuning:
    image: mysql:8.0
    container_name: mysql80_8gb
    environment:
      MYSQL_ROOT_PASSWORD: rootpassword
    deploy:
      resources:
        limits:
          memory: 8g
    volumes:
      - mysql80_8gb_tuning:/var/lib/mysql
      - ./mysql.cnf:/etc/mysql/conf.d/mysql.cnf
    command: >
      --default-authentication-plugin=mysql_native_password

  hammerdb:
    image: tpcorg/hammerdb
    container_name: hammerdb
    tty: true

volumes:
  mysql80_2gb_data:
  mysql80_8gb_data:
  mysql80_8gb_tuning:

mysql.cnf

[mysqld]
# InnoDB settings
innodb_buffer_pool_size = 6G
innodb_log_file_size = 1G
innodb_log_buffer_size = 128M
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table = 1
innodb_flush_method = O_DIRECT
innodb_read_io_threads = 4
innodb_write_io_threads = 4
innodb_io_capacity = 2000

# Cache settings
#query_cache_type = 0
#query_cache_size = 0
table_open_cache = 2000
table_open_cache_instances = 16

# Temp table settings
tmp_table_size = 256M
max_heap_table_size = 256M

# Buffer settings
key_buffer_size = 64M
join_buffer_size = 2M
sort_buffer_size = 4M
read_buffer_size = 2M
read_rnd_buffer_size = 8M

# Connection settings
max_connections = 500
thread_cache_size = 50

# General settings
max_allowed_packet = 64M
open_files_limit = 65535
table_definition_cache = 2000
sync_binlog = 1

# Slow query log settings
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = 1

测试

测试环境设置

docker exec -it hammerdb /bin/bash
./hammerdbcli

配置数据库链接

dbset db mysql
diset connection mysql_host mysql80_2gb
# 这一行在之后的步骤中重复修改
diset connection mysql_port 3306
diset tpcc mysql_user root
diset tpcc mysql_pass rootpassword
diset tpcc mysql_db tpcc
diset tpcc mysql_driver native
diset tpcc mysql_count_ware 10
diset tpcc mysql_storage_engine innodb
loadscript
print dict
buildschema
# 这一步是在数据库中创建所需要的表。
# 如果你坚持使用mysql新版本的用户认证插件，我也没什么办法。

虚拟用户设置

vuset vu 10
vuset logtotemp 1
vuset unique 1
vuset iterations 1
vucreate

测试任务启动

vurun

多次测试任务，需要重新运行 diset connection mysql_host mysql80_2gb

可能需要先运行 vudestroy 销毁现有的虚拟用户。

结果

以下内容由 ChatGPT 创作

按照 docker-compose.yml 文件顺序依次进行了三次测试。

对比结果表格

测试项	第一次运行结果	第二次运行结果	第三次运行结果
NOPM（新订单每分钟）	1278	1389	8263
TPM（事务每分钟）	2947	3199	19180
虚拟用户数	10	10	10
测试状态	成功	成功	成功

分析

显著提升的 NOPM 和 TPM ：
- NOPM 从第一次的 1278 提升到 8263，这意味着系统每分钟处理的新订单数增加了约 6.5 倍。
- TPM 从第一次的 2947 提升到 19180，这意味着系统每分钟处理的事务总数增加了约 6.5 倍。
虚拟用户数 ：
- 三次测试的虚拟用户数量相同，均为 10，表明测试负载一致。
测试状态 ：
- 三次测试均成功完成，表示测试结果可靠。

以上内容由 ChatGPT 创作

根据测试结果可以得出结论，在内存资源并不紧缺的情况下，优化默认配置文件相比单纯增加内存，可以显著提升系统性能。