mysql 性能调优教程及结果对比
2024-05-31 14:02:08 # software

安装

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
[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

测试

测试环境设置

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

配置数据库链接

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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新版本的用户认证插件，我也没什么办法。

虚拟用户设置

1
2
3
4
5
vuset vu 10
vuset logtotemp 1
vuset unique 1
vuset iterations 1
vucreate

测试任务启动

1
vurun

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

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

结果

以下内容由 ChatGPT 创作

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

对比结果表格

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

分析

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

以上内容由 ChatGPT 创作

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

Prev
2024-05-31 14:02:08 # software