Adam Chow's Life Journals

Appearance

下面我将详细介绍如何自行搭建 MySQL 数据库，并使用 Sysbench 工具进行性能测试，最后编写测试报告的完整过程。

搭建 MySQL 数据库 ​

这里我们以常见的 Linux 环境（例如 Ubuntu 或 CentOS）为例，介绍如何手动安装和配置 MySQL Community Server。

步骤 1：环境准备 ​

  • 操作系统: 推荐使用 Linux 系统，如 Ubuntu Server, CentOS 等。确保系统已更新到最新状态。
  • 硬件资源: 根据你的测试需求和预期负载，准备足够的 CPU、内存和磁盘空间。对于简单的 Sysbench 测试，一台普通的虚拟机或物理机即可。
  • 网络连接: 确保服务器可以连接互联网以下载 MySQL 安装包（如果选择在线安装方式）。
  • 防火墙: 如果服务器启用了防火墙，请确保开放 MySQL 默认端口 3306，以便 Sysbench 可以连接到数据库。

步骤 2：下载 MySQL Community Server ​

访问 MySQL 官方网站：https://dev.mysql.com/downloads/mysql/

  • 选择 "MySQL Community Server"。
  • 选择你的操作系统和对应的版本 (例如 "Linux - Generic" 或针对特定发行版的版本)。
  • 选择下载方式：
    • APT Repository (Ubuntu/Debian): 推荐使用 APT 仓库安装，方便后续更新。
    • YUM Repository (CentOS/RHEL): 推荐使用 YUM 仓库安装，方便后续更新。
    • RPM Package (RPM-based Linux): 手动下载 RPM 包安装。
    • DEB Package (Debian-based Linux): 手动下载 DEB 包安装。
    • Tarball (Generic Binary): 下载 Tarball 压缩包，手动解压和配置。

我们以 Ubuntu 使用 APT 仓库安装为例：

bash
# 更新 apt 软件包索引
sudo apt update

# 安装 MySQL 官方 APT 仓库配置包
wget https://dev.mysql.com//mysql-apt-config_0.8.29-1_all.deb  # 请替换为最新版本
sudo dpkg -i mysql-apt-config_0.8.29-1_all.deb

# 在安装配置过程中，会弹出配置界面，选择你需要的 MySQL 版本 (例如 MySQL 8.0) 和其他组件。

# 再次更新 apt 软件包索引，以包含 MySQL 仓库
sudo apt update

# 安装 MySQL Server
sudo apt install mysql-server

对于 CentOS 使用 YUM 仓库安装，可以参考 MySQL 官方文档： https://dev.mysql.com/doc/mysql-yum-repo-quick-guide/en/

步骤 3：配置 MySQL Server ​

安装过程中，会提示你设置 MySQL root 用户的密码。请务必设置一个强密码并妥善保管。

安装完成后，MySQL 服务会自动启动。你可以使用以下命令检查 MySQL 服务状态：

bash
sudo systemctl status mysql

基本配置 (可选但推荐):

  • 修改配置文件: MySQL 的配置文件通常位于 /etc/mysql/mysql.conf.d/mysqld.cnf 或 /etc/my.cnf。

    • bind-address: 默认情况下，MySQL 监听所有网络接口 (0.0.0.0)。如果只需要本地访问，可以设置为 127.0.0.1。如果需要远程访问，需要设置为服务器的 IP 地址或 0.0.0.0。 注意安全风险，开放远程访问时请做好安全防护。
    • port: MySQL 默认端口为 3306。
    • innodb_buffer_pool_size: InnoDB 缓冲池大小，影响 InnoDB 存储引擎的性能。通常建议设置为物理内存的 50%-80%。例如，如果服务器有 8GB 内存，可以设置为 innodb_buffer_pool_size = 4G。
    • innodb_log_file_size 和 innodb_log_group_home_dir: InnoDB 日志文件配置。
    • query_cache_type 和 query_cache_size (MySQL 8.0 已移除 Query Cache): 查询缓存配置 (MySQL 8.0 之前版本)。
    • max_connections: 最大连接数。

    修改配置文件后，需要重启 MySQL 服务才能生效：

    bash
    sudo systemctl restart mysql

  • 安全初始化 (推荐): 运行 mysql_secure_installation 脚本，进行安全初始化设置，例如：

    • 设置 root 密码 (如果安装时没有设置)
    • 删除匿名用户
    • 禁止 root 用户远程登录 (如果需要远程访问，请谨慎操作)
    • 删除 test 数据库
    bash
    sudo mysql_secure_installation

步骤 4：创建测试数据库和用户 ​

连接到 MySQL 服务器：

bash
mysql -u root -p

输入 root 密码后，进入 MySQL 命令行。

创建测试数据库 (例如 sysbench_test):

sql
CREATE DATABASE sysbench_test;

创建 Sysbench 测试用户 (例如 sysbench_user) 并授予 sysbench_test 数据库的权限：

sql
CREATE USER 'sysbench_user'@'%' IDENTIFIED BY 'your_password';  # 替换 your_password 为你的密码
GRANT ALL PRIVILEGES ON sysbench_test.* TO 'sysbench_user'@'%';
FLUSH PRIVILEGES;
EXIT;

使用 Sysbench 进行性能测试 ​

步骤 1：安装 Sysbench ​

bash
# Ubuntu/Debian
sudo apt update
sudo apt install sysbench

# CentOS/RHEL
sudo yum install sysbench

步骤 2：准备测试数据 ​

使用 Sysbench 的 prepare 命令初始化测试数据。我们需要指定连接 MySQL 的信息和测试数据库。

bash
sysbench oltp_read_only prepare --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=sysbench_user --mysql-password=your_password --mysql-db=sysbench_test --tables=4 --table-size=10000

参数解释：

  • oltp_read_only: 指定 Sysbench 测试场景 (这里先用只读场景，后面会介绍其他场景)。
  • prepare: Sysbench 命令，用于准备测试数据。
  • --mysql-host: MySQL 服务器地址 (这里使用本地地址)。
  • --mysql-port: MySQL 服务器端口。
  • --mysql-user: 连接 MySQL 的用户名 (我们创建的 sysbench_user)。
  • --mysql-password: 连接 MySQL 的密码。
  • --mysql-db: 测试数据库名称 (sysbench_test)。
  • --tables: 创建的表数量 (这里创建 4 个表)。
  • --table-size: 每个表的数据行数 (这里每个表 10000 行)。

步骤 3：运行性能测试 (不同场景) ​

Sysbench 常用的测试场景包括：

  • 只读 (Read-Only): 模拟高并发只读场景，例如网站浏览、查询等。
  • 读写 (Read-Write): 模拟混合读写场景，例如电商网站的商品浏览和下单等。
  • 只写 (Write-Only): 模拟高并发写入场景，例如日志记录、消息队列等。
  • 更新 (Update): 模拟高并发更新场景，例如在线游戏、社交网络等。

下面分别介绍不同场景的 Sysbench 命令：

1. 只读 (Read-Only) 测试:

bash
sysbench oltp_read_only run --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=sysbench_user --mysql-password=your_password --mysql-db=sysbench_test --threads=32 --time=60 --report-interval=10

参数解释：

  • run: Sysbench 命令，运行测试。
  • --threads: 模拟并发线程数 (这里设置为 32，根据你的服务器性能调整)。
  • --time: 测试持续时间，单位秒 (这里设置为 60 秒)。
  • --report-interval: 报告输出间隔，单位秒 (每 10 秒输出一次报告)。

2. 读写 (Read-Write) 测试:

bash
sysbench oltp_read_write run --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=sysbench_user --mysql-password=your_password --mysql-db=sysbench_test --threads=32 --time=60 --report-interval=10

3. 只写 (Write-Only) 测试:

bash
sysbench oltp_write_only run --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=sysbench_user --mysql-password=your_password --mysql-db=sysbench_test --threads=32 --time=60 --report-interval=10

4. 更新 (Update) 测试:

bash
sysbench oltp_update_index run --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=sysbench_user --mysql-password=your_password --mysql-db=sysbench_test --threads=32 --time=60 --report-interval=10

其他常用 Sysbench 参数：

  • --tables=N: 指定测试的表数量。
  • --table-size=N: 指定每个表的数据行数。
  • --db-driver=mysql: 指定数据库驱动 (默认是 MySQL)。
  • --oltp-point-selects=N: 指定点查询操作的数量 (用于调整读写比例)。
  • --oltp-simple-ranges=N: 指定简单范围查询操作的数量。
  • --oltp-sum-ranges=N: 指定聚合范围查询操作的数量。
  • --oltp-complex-ranges=N: 指定复杂范围查询操作的数量。
  • --oltp-order-ranges=N: 指定排序范围查询操作的数量。
  • --oltp-distinct-ranges=N: 指定去重范围查询操作的数量。
  • --mysql-table-engine=innodb: 指定表存储引擎 (默认为 InnoDB)。
  • --percentile=95: 输出指定百分位的延迟信息 (例如 95% 延迟)。

步骤 4：清理测试数据 ​

测试完成后，使用 cleanup 命令清理 Sysbench 创建的测试数据：

bash
sysbench oltp_read_only cleanup --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=sysbench_user --mysql-password=your_password --mysql-db=sysbench_test

编写性能测试报告 ​

一个完整的性能测试报告应包含以下内容：

1. 报告标题和基本信息

  • 报告标题: 例如 "MySQL 数据库 Sysbench 性能测试报告"
  • 测试日期: 测试执行的日期和时间
  • 测试人员: 测试报告编写人员
  • 测试目标: 简述本次性能测试的目的，例如评估 MySQL 在不同负载下的性能表现。

2. 测试环境

  • 硬件环境:
    • 服务器型号和配置 (CPU 型号、核心数、内存大小、磁盘类型和大小)
    • 网络环境 (例如：本地网络、公有云环境)
  • 软件环境:
    • 操作系统版本 (例如：Ubuntu 20.04 Server, CentOS 7)
    • MySQL 版本 (例如：MySQL Community Server 8.0.28)
    • Sysbench 版本
    • 数据库连接驱动版本 (如果适用)
  • MySQL 配置参数: 列出 MySQL 关键配置参数，例如 innodb_buffer_pool_size, max_connections, query_cache_type 等 (重点是影响性能的参数)。

3. 测试方法和场景

  • 测试工具: Sysbench
  • 测试场景: 详细描述测试场景，例如：
    • 只读测试: 模拟高并发只读查询负载，测试数据库的只读性能。
    • 读写测试: 模拟混合读写负载，测试数据库的并发读写性能。
    • 只写测试: 模拟高并发写入负载，测试数据库的写入性能。
    • 更新测试: 模拟高并发更新负载，测试数据库的更新性能。
  • 测试参数: 详细列出每个测试场景使用的 Sysbench 命令和参数，例如线程数、测试时间、表数量、表大小等。

4. 测试结果

  • 性能指标: 记录 Sysbench 输出的关键性能指标：
    • Transactions per second (TPS): 每秒事务数，越高越好。
    • Queries per second (QPS): 每秒查询数，越高越好。
    • Latency (average, 95th percentile等): 延迟，越低越好。通常关注平均延迟和高百分位延迟 (例如 95th 或 99th)。
    • Threads fairness: 线程公平性，反映并发线程的执行效率。
  • 结果表格: 使用表格清晰地展示不同测试场景的性能指标数据。例如：
测试场景线程数测试时间 (秒)TPSQPS平均延迟 (ms)95% 延迟 (ms)
只读3260XXXX.XXYYYY.YYZ.ZZA.AA
读写3260XXXX.XXYYYY.YYZ.ZZA.AA
只写3260XXXX.XXYYYY.YYZ.ZZA.AA
更新3260XXXX.XXYYYY.YYZ.ZZA.AA
  • 图表 (可选): 可以使用图表更直观地展示性能数据，例如折线图、柱状图等。

5. 结果分析与结论

  • 性能分析: 根据测试结果，分析 MySQL 在不同场景下的性能表现。
    • 哪些场景性能表现良好？
    • 哪些场景可能存在性能瓶颈？
    • 是否达到了测试预期？
  • 瓶颈分析 (如果存在): 如果发现性能瓶颈，尝试分析可能的原因，例如：
    • CPU 瓶颈
    • 内存瓶颈 (例如 Buffer Pool 不足)
    • 磁盘 I/O 瓶颈
    • 网络瓶颈
    • SQL 语句效率问题
    • MySQL 配置不合理
  • 优化建议 (如果需要): 根据瓶颈分析，提出可能的优化建议，例如：
    • 调整 MySQL 配置参数 (例如 innodb_buffer_pool_size, max_connections)
    • 优化 SQL 语句和索引
    • 升级硬件资源 (CPU, 内存, 磁盘)
    • 使用性能更好的存储引擎 (例如 InnoDB)
    • 数据库分库分表
    • 使用缓存技术 (例如 Redis, Memcached)
  • 结论: 总结本次性能测试的结果和结论，例如：
    • 在当前配置下，MySQL 数据库在 XX 场景下性能良好，TPS 达到 XXXX，平均延迟为 Z.ZZ ms。
    • 在 YY 场景下，性能存在瓶颈，可能是由于 XX 原因导致，建议进行 ZZ 优化。

示例测试报告框架 (简略版): ​

MySQL 数据库 Sysbench 性能测试报告

1. 基本信息 * 测试日期: 2023-10-27 * 测试人员: XXX * 测试目标: 评估 MySQL 在不同负载下的性能表现

2. 测试环境 * 硬件: 虚拟机, 2 vCPU, 4GB RAM, 50GB SSD * 软件: Ubuntu 20.04 Server, MySQL 8.0.28, Sysbench 1.0.20 * MySQL 配置: innodb_buffer_pool_size = 2G, max_connections = 200

3. 测试方法和场景 * 工具: Sysbench * 场景: 只读, 读写, 只写, 更新 * 参数: 线程数 32, 测试时间 60 秒, 表数量 4, 表大小 10000

4. 测试结果

测试场景TPSQPS平均延迟 (ms)95% 延迟 (ms)
只读5890.2270682.65.4312.88
读写2345.8728150.413.6430.56
只写1987.5423850.516.0936.78
更新2100.3325204.015.2334.12

5. 结果分析与结论

  • 性能分析: 只读场景性能最佳，读写、只写、更新场景性能相对较低。
  • 瓶颈分析: 可能是 CPU 或磁盘 I/O 瓶颈，在并发读写和写入场景下压力增大。
  • 结论: 在当前配置下，MySQL 数据库可以承受一定的并发负载，但对于高并发读写和写入场景，可能需要进一步优化配置或升级硬件资源。