MySQL Insert 性能在大表上下降

Question

我正在处理一个包含 250 多万行的巨大表格。模式很简单。

CREATE TABLE MyTable (
        id BIGINT PRIMARY KEY AUTO_INCREMENT,
        oid INT NOT NULL,
        long1 BIGINT NOT NULL,
        str1 VARCHAR(30) DEFAULT NULL,
        str2 VARCHAR(30) DEFAULT NULL,
        str2 VARCHAR(200) DEFAULT NULL,
        str4 VARCHAR(50) DEFAULT NULL,
        int1 INT(6) DEFAULT NULL,
        str5 VARCHAR(300) DEFAULT NULL,
        date1 DATE DEFAULT NULL,
        date2 DATE DEFAULT NULL,
        lastUpdated TIMESTAMP NOT NULL,
        hashcode INT NOT NULL,
        active TINYINT(1) DEFAULT 1,
        KEY oid(oid),
        KEY lastUpdated(lastUpdated),
        UNIQUE KEY (hashcode, active),
        KEY (active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 MAX_ROWS=1000000000;

insert 的性能明显下降。高达1.5亿行的表，以前插入10000行需要5-6秒。现在涨了2-4倍。 Innodb 的 ibdata 文件已经增长到 107 GB。 Innodb配置参数如下。

innodb_buffer_pool_size = 36G # Machine has 48G memory
innodb_additional_mem_pool_size = 20M
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_file_size = 50M
innodb_log_buffer_size = 20M
innodb_log_files_in_group=2
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
innodb_thread_concurrency = 8
innodb_flush_method = O_DIRECT
expire_logs_days = 4

如 top 所示，IO 等待时间增加了。我曾尝试将刷新方法更改为 O_DSYNC，但没有帮助。该磁盘是从硬件 RAID 10 设置中分割出来的。在早期的单磁盘设置中，IO 不是问题。

分区表是唯一的选择吗？将单个 100G 文件拆分成“更小”的文件有帮助吗？是否有任何变量需要针对 RAID 进行调整？

更新:这是一个测试系统。我可以自由地进行任何必要的更改。

Answer 1

您没有说明这是测试系统还是生产系统；我假设它正在生产。

很可能您已将表的大小设置为其索引(或全部索引)不再适合内存。

这意味着 InnoDB 必须在插入期间读入页面(取决于新行索引值的分布)。阅读页面(随机阅读)真的很慢，需要尽可能避免。

分区似乎是最明显的解决方案，但 MySQL 的分区可能不适合您的用例。

您当然应该考虑所有可能的选择 - 将表放到您实验室的测试服务器上以查看其行为方式。

在我看来，您的主键似乎不是必需的(您有另一个唯一索引)，因此消除它是一种选择。

还要考虑 innodb 插件和压缩，这将使你的 innodb_buffer_pool 更进一步。

您确实需要分析您的用例，以确定您是否真的需要保留所有这些数据，以及分区是否是一个明智的解决方案。

对此应用程序进行任何更改都可能会给您的用户带来新的性能问题，因此您在这里要格外小心。如果您找到提高插入性能的方法，它可能会降低搜索性能或其他操作的性能。在发布此类更改之前，您需要对生产级硬件进行全面的性能测试。