MySQL - Azure VM 上的选择查询比本地 VM 慢 10 倍

Question

我们一直在开展一个项目，将 MySQL 数据库从本地 Linux 服务器迁移到 Azure 上的 Windows VM（IaaS）。（出于特定原因，我们选择了 IaaS 选项，而不是 Azure MySQL PaaS 产品）。

迁移后，我们发现新服务器上 MySQL 数据库的查询速度明显变慢（大约 10 倍）。虚拟机配置了 64 个 CPU 和 256 GB RAM（本地虚拟机配置了 48 个 CPU 和 256 GB RAM）。

数据库中的所有表都使用 InnoDB 引擎。我们已经阅读了大量有关 InnoDB 表查询缓慢的信息，其中大部分似乎都指向 innodb_buffer_pool_size - 我已经将其配置为 185 GB（约占总 RAM 的 70%）。我们还尝试在配置中进行许多其他更改，my.ini例如

key_buffer_size = 20MB
innodb_io_capacity = 2000
query_cache_size = 0
query_cache_type = 0
increasing thread_cache_size
innodb_read_io_threads
innodb_write_io_threads

等等。但对于查询性能来说，似乎没有什么帮助。

我们比较了两台服务器上的索引，发现它们是相同的。从总体上看，Azure VM 上的索引似乎没有损坏。此外，我们正尝试通过在 Azure VM 内运行 MySQL 工作台来测量性能，因此网络带宽应该不是问题。

• 有人可以建议我们可以尝试的其他选项来提高性能吗？

另外几点。

• 我们注意到，尽管有些查询30+ 分钟运行，（它们似乎在本地服务器中运行5分钟)，VM 上的 CPU 使用率仍然很低（不到 10%）。是否有一些设置（如 innodb_buffer_pool_size）来为 MySQL 服务器分配一定数量的 CPU？

正如我之前提到的，本地 VM 是基于 Linux 的，而 Azure VM 在 Windows 上运行 - 这会是个问题吗？我找不到任何确凿的证据证明 Windows 上的 MySQL 会导致如此严重的性能下降。

我的完整 my.ini 配置如下：

# Other default tuning values
# MySQL Server Instance Configuration File
# ----------------------------------------------------------------------
# Generated by the MySQL Server Instance Configuration Wizard
#
#
# Installation Instructions
# ----------------------------------------------------------------------
#
# On Linux you can copy this file to /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options
# (@localstatedir@ for this installation) or to
# ~/.my.cnf to set user-specific options.
#
# On Windows you should keep this file in the installation directory 
# of your server (e.g. C:\Program Files\MySQL\MySQL Server X.Y). To
# make sure the server reads the config file use the startup option 
# "--defaults-file". 
#
# To run the server from the command line, execute this in a 
# command line shell, e.g.
# mysqld --defaults-file="C:\Program Files\MySQL\MySQL Server X.Y\my.ini"
#
# To install the server as a Windows service manually, execute this in a 
# command line shell, e.g.
# mysqld --install MySQLXY --defaults-file="C:\Program Files\MySQL\MySQL Server X.Y\my.ini"
#
# And then execute this in a command line shell to start the server, e.g.
# net start MySQLXY
#
#
# Guidelines for editing this file
# ----------------------------------------------------------------------
#
# In this file, you can use all long options that the program supports.
# If you want to know the options a program supports, start the program
# with the "--help" option.
#
# More detailed information about the individual options can also be
# found in the manual.
#
# For advice on how to change settings please see
# https://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
#
#
# CLIENT SECTION
# ----------------------------------------------------------------------
#
# The following options will be read by MySQL client applications.
# Note that only client applications shipped by MySQL are guaranteed
# to read this section. If you want your own MySQL client program to
# honor these values, you need to specify it as an option during the
# MySQL client library initialization.
#
[client]

# pipe=

# socket=MYSQL

port=3306

[mysql]
no-beep

# default-character-set=

# SERVER SECTION
# ----------------------------------------------------------------------
#
# The following options will be read by the MySQL Server. Make sure that
# you have installed the server correctly (see above) so it reads this 
# file.
#
# server_type=1
[mysqld]
plugin-load-add=validate_password.dll
validate-password=FORCE_PLUS_PERMANENT
# The next three options are mutually exclusive to SERVER_PORT below.
# skip-networking
# enable-named-pipe
# shared-memory

# shared-memory-base-name=MYSQL

# The Pipe the MySQL Server will use
# socket=MYSQL

# The TCP/IP Port the MySQL Server will listen on
port=3306

# Path to installation directory. All paths are usually resolved relative to this.
# basedir="C:/Program Files/MySQL/MySQL Server 5.7/"

# Path to the database root
datadir=F:\MySQL\Data

# The default character set that will be used when a new schema or table is
# created and no character set is defined
# character-set-server=

# The default storage engine that will be used when create new tables when
default-storage-engine=INNODB

# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

# General and Slow logging.
log-output=FILE

general-log=0

general_log_file="ZEUWPJIRA01.log"

slow-query-log=1

slow_query_log_file="ZEUWPJIRA01-slow.log"

long_query_time=10

# Error Logging.
log-error="ZEUWPJIRA01.err"

# ***** Group Replication Related *****
# Specifies the base name to use for binary log files. With binary logging
# enabled, the server logs all statements that change data to the binary
# log, which is used for backup and replication.
# log-bin

# ***** Group Replication Related *****
# Specifies the server ID. For servers that are used in a replication topology,
# you must specify a unique server ID for each replication server, in the
# range from 1 to 2^32 − 1. “Unique” means that each ID must be different
# from every other ID in use by any other replication source or replica.
server-id=26
# ***** Group Replication Related *****
# The host name or IP address of the replica to be reported to the source
# during replica registration. This value appears in the output of SHOW SLAVE HOSTS
# on the source server. Leave the value unset if you do not want the replica to
# register itself with the source.
# report_host=0.0

# ***** Group Replication Related *****
# Defines the algorithm used to hash the writes extracted during a transaction. If you
# are using Group Replication, this variable must be set to XXHASH64 because the process
# of extracting the writes from a transaction is required for conflict detection on all
# group members.
# transaction_write_set_extraction=0.0
lower_case_table_names=1

# Secure File Priv.
secure-file-priv="C:/ProgramData/MySQL/MySQL Server 5.7/Uploads"

# The maximum amount of concurrent sessions the MySQL server will
# allow. One of these connections will be reserved for a user with
# SUPER privileges to allow the administrator to login even if the
# connection limit has been reached.
#max_connections=151
#[29062021]changed by Sridharan to increase max concurrent sessions
max_connections=1000
#[29062021]changed by Sridharan to increase max connection errors
max_connect_errors=500

# The number of open tables for all threads. Increasing this value
# increases the number of file descriptors that mysqld requires.
# Therefore you have to make sure to set the amount of open files
# allowed to at least 4096 in the variable "open-files-limit" in
# section [mysqld_safe]
#table_open_cache=2000
table_open_cache =2048
table_definition_cache = 2048
myisam_sort_buffer_size = 8M
#skip-external-locking
# Maximum size for internal (in-memory) temporary tables. If a table
# grows larger than this value, it is automatically converted to disk
# based table This limitation is for a single table. There can be many
# of them.
tmp_table_size=4G

# How many threads we should keep in a cache for reuse. When a client
# disconnects, the client's threads are put in the cache if there aren't
# more than thread_cache_size threads from before.  This greatly reduces
# the amount of thread creations needed if you have a lot of new
# connections. (Normally this doesn't give a notable performance
# improvement if you have a good thread implementation.)
# thread_cache_size=10
thread_cache_size=64
query_cache_size =0
query_cache_type = 0

#*** MyISAM Specific options
# The maximum size of the temporary file MySQL is allowed to use while
# recreating the index (during REPAIR, ALTER TABLE or LOAD DATA INFILE.
# If the file-size would be bigger than this, the index will be created
# through the key cache (which is slower).
myisam_max_sort_file_size=100G

# The size of the buffer that is allocated when sorting MyISAM indexes
# during a REPAIR TABLE or when creating indexes with CREATE INDEX
# or ALTER TABLE.
myisam_sort_buffer_size=6G

# Size of the Key Buffer, used to cache index blocks for MyISAM tables.
# Do not set it larger than 30% of your available memory, as some memory
# is also required by the OS to cache rows. Even if you're not using
# MyISAM tables, you should still set it to 8-64M as it will also be
# used for internal temporary disk tables.
key_buffer_size=20M

# Size of the buffer used for doing full table scans of MyISAM tables.
# Allocated per thread, if a full scan is needed.
read_buffer_size=64K

read_rnd_buffer_size=256K
tmp_table_size=64M
max_heap_table_size=64M

#*** INNODB Specific options ***
# innodb_data_home_dir=

# Use this option if you have a MySQL server with InnoDB support enabled
# but you do not plan to use it. This will save memory and disk space
# and speed up some things.
# skip-innodb

# If set to 1, InnoDB will flush (fsync) the transaction logs to the
# disk at each commit, which offers full ACID behavior. If you are
# willing to compromise this safety, and you are running small
# transactions, you may set this to 0 or 2 to reduce disk I/O to the
# logs. Value 0 means that the log is only written to the log file and
# the log file flushed to disk approximately once per second. Value 2
# means the log is written to the log file at each commit, but the log
# file is only flushed to disk approximately once per second.
innodb_flush_log_at_trx_commit=2

# The size of the buffer InnoDB uses for buffering log data. As soon as
# it is full, InnoDB will have to flush it to disk. As it is flushed
# once per second anyway, it does not make sense to have it very large
# (even with long transactions).
innodb_log_buffer_size=200M

# InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and
# row data. The bigger you set this the less disk I/O is needed to
# access data in tables. On a dedicated database server you may set this
# parameter up to 80% of the machine physical memory size. Do not set it
# too large, though, because competition of the physical memory may
# cause paging in the operating system.  Note that on 32bit systems you
# might be limited to 2-3.5G of user level memory per process, so do not
# set it too high.
#[29062021]changed by Sridharan to increase innodb buffer pool size from 16G to 24G
#[06072021]changed by Sridharan to increase innodb buffer pool size from 24G to 48G
innodb_buffer_pool_size=185G

# Size of each log file in a log group. You should set the combined size
# of log files to about 25%-100% of your buffer pool size to avoid
# unneeded buffer pool flush activity on log file overwrite. However,
# note that a larger logfile size will increase the time needed for the
# recovery process.
innodb_log_file_size=4G

# Number of threads allowed inside the InnoDB kernel. The optimal value
# depends highly on the application, hardware as well as the OS
# scheduler properties. A too high value may lead to thread thrashing.
#innodb_thread_concurrency=17
innodb_thread_concurrency=32

# The increment size (in MB) for extending the size of an auto-extend InnoDB system tablespace file when it becomes full.
innodb_autoextend_increment=64

# The number of regions that the InnoDB buffer pool is divided into.
# For systems with buffer pools in the multi-gigabyte range, dividing the buffer pool into separate instances can improve concurrency,
# by reducing contention as different threads read and write to cached pages.
#innodb_buffer_pool_instances=8
#[29062021 Sridharan] increased buffer pool instances from 8 to 12
innodb_buffer_pool_instances=12

#[29062021 Sridharan] added the io_capacity, read_io_threads and write_io_threads configurations to speed-up queries
innodb_read_io_threads = 32
innodb_write_io_threads = 32
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000

# Determines the number of threads that can enter InnoDB concurrently.
innodb_concurrency_tickets=5000

# Specifies how long in milliseconds (ms) a block inserted into the old sublist must stay there after its first access before
# it can be moved to the new sublist.
innodb_old_blocks_time=1000

# It specifies the maximum number of .ibd files that MySQL can keep open at one time. The minimum value is 10.
innodb_open_files=300

# When this variable is enabled, InnoDB updates statistics during metadata statements.
innodb_stats_on_metadata=0

# When innodb_file_per_table is enabled (the default in 5.6.6 and higher), InnoDB stores the data and indexes for each newly created table
# in a separate .ibd file, rather than in the system tablespace.
innodb_file_per_table=1

# Use the following list of values: 0 for crc32, 1 for strict_crc32, 2 for innodb, 3 for strict_innodb, 4 for none, 5 for strict_none.
innodb_checksum_algorithm=0

# The number of outstanding connection requests MySQL can have.
# This option is useful when the main MySQL thread gets many connection requests in a very short time.
# It then takes some time (although very little) for the main thread to check the connection and start a new thread.
# The back_log value indicates how many requests can be stacked during this short time before MySQL momentarily
# stops answering new requests.
# You need to increase this only if you expect a large number of connections in a short period of time.
back_log=80

# If this is set to a nonzero value, all tables are closed every flush_time seconds to free up resources and
# synchronize unflushed data to disk.
# This option is best used only on systems with minimal resources.
flush_time=0

# The minimum size of the buffer that is used for plain index scans, range index scans, and joins that do not use
# indexes and thus perform full table scans.
join_buffer_size=256K

# The maximum size of one packet or any generated or intermediate string, or any parameter sent by the
# mysql_stmt_send_long_data() C API function.
max_allowed_packet=1024M

# If more than this many successive connection requests from a host are interrupted without a successful connection,
# the server blocks that host from performing further connections.
max_connect_errors=100

# Changes the number of file descriptors available to mysqld.
# You should try increasing the value of this option if mysqld gives you the error "Too many open files".
open_files_limit=4161

# If you see many sort_merge_passes per second in SHOW GLOBAL STATUS output, you can consider increasing the
# sort_buffer_size value to speed up ORDER BY or GROUP BY operations that cannot be improved with query optimization
# or improved indexing.
sort_buffer_size=256K

# The number of table definitions (from .frm files) that can be stored in the definition cache.
# If you use a large number of tables, you can create a large table definition cache to speed up opening of tables.
# The table definition cache takes less space and does not use file descriptors, unlike the normal table cache.
# The minimum and default values are both 400.
table_definition_cache=1400

# Specify the maximum size of a row-based binary log event, in bytes.
# Rows are grouped into events smaller than this size if possible. The value should be a multiple of 256.
binlog_row_event_max_size=8K

# If the value of this variable is greater than 0, a replica synchronizes its master.info file to disk.
# (using fdatasync()) after every sync_master_info events.
sync_master_info=10000

# If the value of this variable is greater than 0, the MySQL server synchronizes its relay log to disk.
# (using fdatasync()) after every sync_relay_log writes to the relay log.
sync_relay_log=10000

# If the value of this variable is greater than 0, a replica synchronizes its relay-log.info file to disk.
# (using fdatasync()) after every sync_relay_log_info transactions.
sync_relay_log_info=10000

# Load mysql plugins at start."plugin_x ; plugin_y".
# plugin_load

# The TCP/IP Port the MySQL Server X Protocol will listen on.
# loose_mysqlx_port=33060
# relay-log=C:/ProgramData/MySQL/MySQL Server 5.7/Data/mysql-relay-bin.log
relay-log=F:\MySQL\Data\mysql-relay-bin.log

# log_bin=C:/ProgramData/MySQL/MySQL Server 5.7/Data/mysql-bin.log
log_bin=F:\MySQL\Data\mysql-bin.log

binlog_do_db=jira_datacenter
binlog_format=MIXED

Answer 1

我建议你首先复制将现有的本地 MySql 配置迁移到 Azure VM。所有设置都相同。这样，您就可以进行同类比较。
现在，您有太多变化同时发生，而您无法查看哪些变化导致了问题。

...我们注意到，尽管有些查询需要 30 多分钟才能运行，（它们似乎只需 5 分钟就在本地服务器上运行）...

即使 5 分钟也是很久查询运行。
这些查询是否可以调整，即使在本地实例中也是如此？
他们是否通过网络提取大量数据？简单地说，您的 Azure VM 位于比您的本地实例更长的电子字符串的末端（并且可能受到各种网络路由、流量管理等的影响）。

...虚拟机上的 CPU 使用率仍然很低...

再次，这听起来像是大量的数据被移动，而服务器端的处理却很少
。磁盘很忙？执行大量更新或使用大量临时结果集的查询将痛击磁盘，而无需使用太多 CPU。（相反，大量的 [内存中]表扫描通常表现为高 CPU 负载和低磁盘活动）。

Answer 2

• 选择一个本地和 Azure 之间存在显著差异的查询，然后重点关注该查询。解决这个查询很可能会帮助解决所有其他查询的问题（因为这里我们不是在讨论更改查询或添加索引（如果优化查询的话），而是在讨论基础设施差异）。
• 使用资源监视器查看限制因素在哪里。例如，您说 CPU 不高，那么很可能不是这样（除非查询必须以单线程运行，因此只影响您的一个逻辑核心，因此平均 CPU 具有误导性；即检查所有逻辑核心而不是平均 CPU）。
• 与虚拟机的一个很大的区别是磁盘不是计算资源的本地磁盘，所以这可能是罪魁祸首。
  • 对于 SQL Server，我知道您可以将临时数据库文件托管在 VM 的 D 驱动器上（该驱动器本地连接到虚拟机管理程序；但如果您取消分配 VM 或将其移动到其他虚拟机管理程序，则会被清除）。这是可以的，因为临时数据库不包含我们需要保留的任何内容/可以在启动时重新创建，因此我们不会丢失任何内容。我对 MySQL 了解不够，无法确定是否也是如此，但我相信它会将其临时文件放在系统TMPDIR环境变量定义的位置；因此您可能需要尝试将其指向 D 驱动器以查看是否有帮助（向 MySQL 专家寻求建议以确认是否可以接受；或进行彻底测试，包括完全停止（而不仅仅是重新启动）使用此设置的 POC VM，以确保一切都按预期恢复）
  • 鉴于数据磁盘和虚拟机之间的延迟导致 IO 受到影响，请尝试通过选择具有更高 IOps 的磁盘来抵消这一点；例如高级 SSD。
  • 考虑修改磁盘的主机缓存为只读。再次强调，这里值得咨询 MySQL 专家；这当然对我更熟悉的 SQL Server 有帮助；这里我们的数据磁盘将主机缓存设置为ReadOnly，而我们的日志文件将写入主机缓存设置为 的磁盘None。
• 您还更改了您的操作系统；因此这里可能发生了很多您以前没有遇到过的事情。
  • 确保已在 AV 软件中为 MySQL 设置了相关排除项
  • 检查虚拟机上还运行着什么；例如，Windows 附带了许多服务，这些服务可能设置为自动运行，但对于您的方案来说并不是必需的。该Windows Search服务曾经是罪魁祸首之一。