☰

MySQL 的 InnoDb 性能下降

tag-icon • tag-icon mysql performance innodb
MySQL 的 InnoDb 性能下降

我正在尝试使用 InnoDb 配置 MySQL 生产服务器，但无法获得我想要的性能。我曾使用Percona 工具给我以下配置：

[mysql]

# CLIENT #
port = 3306
socket = /var/lib/mysql/mysql.sock

[mysqld]

# GENERAL #
user = mysql
default-storage-engine = InnoDB
socket = /var/lib/mysql/mysql.sock
pid-file = /var/lib/mysql/mysql.pid

# MyISAM #
key-buffer-size = 32M
myisam-recover = FORCE,BACKUP

# SAFETY #
max-allowed-packet = 16M
max-connect-errors = 1000000

# DATA STORAGE #
datadir = /var/lib/mysql/

# BINARY LOGGING #
log-bin = /var/lib/mysql/mysql-bin
expire-logs-days = 14
sync-binlog = 1

# CACHES AND LIMITS #
tmp-table-size = 32M
max-heap-table-size = 32M
query-cache-type = 0
query-cache-size = 0
max-connections = 500
thread-cache-size = 50
open-files-limit = 65535
table-definition-cache = 4096
table-open-cache = 4096

# INNODB #
innodb-flush-method = O_DIRECT
innodb-log-files-in-group = 2
innodb-log-file-size = 128M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table = 1
innodb-buffer-pool-size = 1456M

# LOGGING #
log-error = /var/lib/mysql/mysql-error.log
log-queries-not-using-indexes = 1
slow-query-log = 1
slow-query-log-file = /var/lib/mysql/mysql-slow.log

如您所见，MySQL 的 InnoDb 缓冲区设置为近 1.5GB 的 RAM（占我服务器总 RAM 的 37% 左右）。问题是，当我在此服务器上运行某个特定查询时，它需要 70 秒才能运行，但在我的开发机器上执行相同的查询仅需 0.02 秒（使用完全相同的数据）。这是因为我的开发机器只有 130mB innodb_buffer_pool_size。当我使用查看 MySQL 使用了多少内存时，问题变得复杂了top，它只使用了 7.6%（分配给它的内存为 37%）。

还有一个线索是，当我连续运行两次相同的查询时，第二次所花费的时间与第一次一样多，就好像没有可用的缓存一样。

有人建议去哪里看吗？

[更新]

对于我提供的误导性信息，我深感抱歉，但该查询在我的开发机器上需要 1.23 秒。

答案1

服务器上还运行着什么？如果您只为 InnoDB 分配了 37% 的服务器内存，我假设还有其他东西在使用剩余的内存 - 如果是专用的，为什么不将其分配到接近 80% 左右？不过，考虑到您所说的，这可能不是问题所在。我的第一个想法是，有如此明显的差异 - 70 秒 vs 0.02 秒 - 一定有什么东西占用了您的 CPU，或者更可能是您的磁盘 I/O。此外，您是否检查过 MySQL 日志以查看它们是否给出了有关正在发生的事情的任何指示？

如果您不介意帮我提供有关数据库中数据和查询的一些信息，这可以帮助我为您提供更好的建议。确保表中存在所有索引和键，然后运行“EXPLAIN [query]”。它将为您提供一些有关执行该查询时 InnoDB 可能检查的内容的信息。

不幸的是，如果没有更多信息，我只能做到这些。祝你好运。

答案2

一个会减慢数据修改速度的设置是sync_binlog=1。它会导致 MySQL 在每次写入数据库后将查询日志条目物理写入磁盘。使用sync_binlog=0，写入将被缓存以便稍后写入磁盘。

使用 时sync_binlog=0，如果操作系统在将 binlog 条目写入数据库之前崩溃，则从属复制数据可能会损坏。否则，binlog 条目并不那么重要。

相关内容