时间戳

首页 > 解决方案 > 在写入操作和复制环境中使用 MySQL 优化表请求是否安全？

mysql - 在写入操作和复制环境中使用 MySQL 优化表请求是否安全？

问题描述

我有多达 300 个结构相似的 InnoDB 表的 MySQL DB，每个表有大约 700k 行，在正常情况下大小约为 160M。这些表相互独立，没有外键。

该表被大量用于 RW 操作，表使用模式如下所示：

  1. 每 15 分钟向每个表中插入新的数据部分（300-1200 行）
  2. 每天一次，从每个表中删除超过 30 天的数据。
  3. 根据用户请求定期从随机表中读取数据。

几个月后，数据库性能显着下降。排查问题发现：表文件变大了，每个都占用了300M左右的磁盘空间。运行手动表优化后，问题得到解决，但几周后数据库性能再次开始下降。

作为保持数据库健康的解决方案，在每次删除数据后添加了 OPTIMIZE TABLE 请求。

问题是：

  1. MySQL性能下降问题还有其他解决方案吗？
  2. OPTIMIZE TABLE 运行时插入表中的数据是否可能丢失（如果在 OPTIMIZE TABLE 请求仍在进行时执行 INSERT 请求）？
  3. OPTIMIZE TABLE 请求对于 MySQL 服务器意外关闭是否安全（如果在 INSERT 操作完成但 OPTIMIZE TABLE 请求未完成的情况下发生 PC 断电，是否可能丢失已提交的数据）？
  4. 在简单复制的情况下使用 OPTIMIZE TABLE 请求是否安全（在主服务器上执行 OPTIMIZE TABLE 请求并在仅用作备份设施并且除了复制之外没有 IO 的从属服务器上复制）？

编辑：MySQL 5.7.15，InnoDB 表。使用 MySQL 8.0.4 RC 构建复制环境。

编辑2：表结构：

CREATE TABLE `data_2235353676` (
`id` BIGINT(20) NOT NULL,
`inst` VARCHAR(100) NULL DEFAULT NULL,
`if_i` BIGINT(20) NOT NULL,
`prt` BIGINT(20) NULL DEFAULT NULL,
`if_t` BIGINT(20) NULL DEFAULT NULL,
`path` BIGINT(20) NULL DEFAULT NULL,
`period` BIGINT(20) NOT NULL,
`type` BIGINT(20) NOT NULL,
`servicetype` INT(11) NOT NULL,
`sdv_time` BIGINT(20) NULL DEFAULT NULL,
`srv_time` BIGINT(20) NOT NULL,
`err_c` BIGINT(20) NULL DEFAULT NULL,
`err_s` BIGINT(20) NULL DEFAULT NULL,
`srv_err_s` BIGINT(20) NULL DEFAULT NULL,
`una_s` BIGINT(20) NULL DEFAULT NULL,
`valid` BIGINT(1) NULL DEFAULT NULL,
`r_err` BIGINT(20) NULL DEFAULT NULL,
`k_err` BIGINT(20) NULL DEFAULT NULL,
`l2CounterType` BIGINT(20) NOT NULL,
`l2Count` BIGINT(20) NULL DEFAULT NULL,
PRIMARY KEY (`id`, `if_i`, `period`, `type`, `servicetype`, `srv_time`, `l2CounterType`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
;

标签： mysqldatabase-performance

解决方案

INSERT并OPTIMIZE互相争斗；我的建议将通过多种方式减少这种冲突，最重要的是摆脱OPTIMIZE.

8.0 的 GA 可用 -- 请更新到它；您的 RC 版本在生产中不应该被信任。

哪一列控制“超过 30 天”？在未分区的表中，看看是否可以将其放在PRIMARY KEY. 通过将所有插入在一起的行“聚集”在一起，I/O 显着减少。在分区表中（如下所述），尝试将该列移动到 PK 中的后面位置。（分区将减少 I/O，因为您将只在“最后一个”分区中插入。）

确保您正在使用innodb_file_per_table=ON.

不要盲目使用 8-byte BIGINT；找到一个合适的味道INT会更小，但有足够的范围。

用于PARTITION BY RANGE(TO_DAYS(...))将表分成大约 32 个分区。这将使旧数据的删除非常快速和高效，而无需OPTIMIZE TABLE. 更多讨论：http: //mysql.rjweb.org/doc.php/partitionmaint

注意：分区表有一些额外的“可用空间”，所以我的建议仍然会导致每个表大约 300MB。但是，可用空间不会影响性能。如果可行，可以使用更少的分区——比如 12 个为期 3 天的分区。

你是怎么做插入的？单LOAD DATA？一个“批次” INSERT？（我希望你不是一次插入一行。）

大多数列是真的NULLable吗？

OPTIMIZE在所有方面都是“安全的”，因为它锁定表，复制所有数据，然后重命名新副本代替旧副本。（实际上有一个很小的漏洞窗口，但 8.0 用它的“数据字典”覆盖了它。）

推荐阅读