主页 / dba / 问题 / 310254
Accepted
Antonio Sordellini
Asked: 2022-03-29 10:56:19 +0800 CST

同一个 MySQL 数据库在不同服务器上的性能是不同的

  • 772

我有两台服务器：

  • 服务器 A：AMD Epyc 7371 - 16c / 32t - 3.1 GHz / 3.8 GHz 256 GB ECC 2400 MHz 2 × 960 GB NVMe SSD MySql 8.0.26
  • 服务器 B：双 Intel Xeon Gold 6242R - 20c / 40t - 3.1 GHz / 4.1 GHz 384 GB ECC 2933 MHz 6 × 3.84 TB NVMe SSD 2 × 480 GB SATA SSD

在服务器 A 上，尽管它更小，但数据库运行良好，事务非常快，并发用户超过 200 个。

在更大的服务器 B 上，这发生在我身上：最多 50 个并发用户不是问题，并且数据库运行良好，但是在用户数量超过 50 之后，数据库开始变慢。

两台服务器都有相同的数据库、相同的表、相同的存储过程、相同的应用程序，实际上它们是彼此的副本。

我怎样才能找出问题出在哪里？

这是配置文件：

[client]
pipe=
socket=MYSQL
port=3306
[mysql] no-beep
default-character-set=
server_type=1
[mysqld]
port=3306
datadir=E:/ProgramData/MySQL/MySQL Server 8.0\Data
character-set-server=
default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"
log-output=FILE
general-log=0
general_log_file="NS31525947.log"
slow-query-log=1
slow_query_log_file="NS31525947-slow.log"
long_query_time=10
log-error="NS31525947.err"
log-bin="NS31525947-bin"
server-id=1
lower_case_table_names=1
secure-file-priv="C:/ProgramData/MySQL/MySQL Server 8.0/Uploads"
max_connections = 500
table_open_cache=3G
tmp_table_size=3G
thread_cache_size=100
myisam_max_sort_file_size=10G
myisam_sort_buffer_size=68G
key_buffer_size=61M
read_buffer_size=23M
read_rnd_buffer_size=256K
innodb_flush_log_at_trx_commit=0
innodb_log_buffer_size=512M
innodb_buffer_pool_size=10G
innodb_log_file_size=1G
innodb_thread_concurrency=0
innodb_autoextend_increment=64
innodb_buffer_pool_instances=8
innodb_concurrency_tickets=5000
innodb_old_blocks_time=1000
innodb_stats_on_metadata=0
innodb_file_per_table=1
innodb_checksum_algorithm=0
back_log=80
flush_time=0
join_buffer_size=256K
max_allowed_packet=4M
max_connect_errors=100
open_files_limit=10000
sort_buffer_size=256K
table_definition_cache=1400
binlog_row_event_max_size=8K
sync_relay_log=10000
sync_relay_log_info=10000
loose_mysqlx_port=33060
default_authentication_plugin = mysql_native_password

我还尝试将my.ini来自服务器 A 的服务器 B 放在服务器 B 上，但没有任何改变。

我输入了数据库状态引用 现在

网络状态

服务器状态

mysql php

2 个回答

  1. 引起我注意的一个词是 SATA。SATA比SSD慢

    有一个网页名为Types of hard drive: SATA vs. SSD vs. NVMe

    它说部分

    SSD 代表固态驱动器。这些磁盘没有任何移动部件。相反，所有数据都存储在非易失性闪存中。这意味着无需移动指针即可读取或写入数据，而且它们的速度明显快于 SATA 驱动器。很难找到准确的速度，因为它因制造商和外形尺寸而异，但即使是性能较低的驱动器也可以与 SATA 驱动器相媲美。

    我建议把所有东西都放在 SSD 上。我在我的旧帖子 MySQL/Percona Server 中提到摆脱 SATA需要很长时间，我宁愿跳过所有这些等待，即使这意味着数据丢失

    我还建议使用单独的磁盘将重做日志与数据分开（请参阅我在 SSD 上的旧帖子 MySQL - 有什么缺点？）

    我还将 innodb_thread_concurrency 设置为 64（请参阅我的旧帖子MariaDB 10.1.22 以使用更多 RAM 而不是 CPU）

    • 2
  2. Best Answer
    table_open_cache=3G  -- NO! That's files, not bytes!  Drop to a few thousand
innodb_buffer_pool_size=10G  -- increase to about 70% of RAM

    不超过 1% 的可用 RAM（每个）：

    tmp_table_size=3G            --> 200M
max_heap_table_size          --> 200M
myisam_sort_buffer_size=68G  --> 200M

    如果您想进一步分析设置，请参阅http://mysql.rjweb.org/doc.php/mysql_analysis#tuning 该分析将包括发现您是否受 I/O 限制，因此确认 SATA 与 NVMe 是一个大问题问题。

    加速查询是另一种扩展方式；我们来分析一下SlowLog

    关于 SSD 的答案导致需要加快“慢”查询。请注意，慢日志测量经过的时间，因此捕获磁盘 I/O。一定要设置long_query_time = 1.0（或更低）。

    更多的

    10G的buffer_pool；具有 13.8GB 的​​数据文件夹...当服务器首次启动时，buffer_pool 中没有任何内容，查询运行速度比最终慢。这是由于 I/O。（正如已经讨论过的，机器具有不同的 I/O 速度。）

    最终，buffer_pool 将被填满，进一步的操作可能（或可能不会，取决于“工作集大小”）导致抖动——I/O 会在缓存中弹出内容以加载新内容。如果查询正在执行全表扫描，则此 I/O 将特别成问题。

    由于你有很多 RAM，你应该增加innodb_buffer_pool_size. 但是，如果您的数据量继续增长，您最终可能会再次陷入低迷状态。您应该找到任何“慢”查询并考虑如何避免“全表扫描”（或任何使它们变慢的原因）。

    • 2

相关问题