代码之家 › 专栏 › 技术社区 › Shashikant Kore

MySQL插入性能在大表上下降

innodb performance mysql

Shashikant Kore · 技术社区 · 14 年前

我正在处理一个有2.5亿行的大桌子。这个模式很简单。

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;

插入的性能显著下降。表中多达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

IO等待时间增加了 top . 我试过将flush方法改为o-dsync,但没用。磁盘由硬件RAID 10设置雕刻而成。在以前的单盘安装中,IO不是问题。

是否只对表进行分区?将单个100g文件拆分为“较小的”文件是否有帮助?是否有任何变量需要针对RAID进行调整?

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

6 回复 | 直到 11 年前

MarkR 14 年前

你没有说这是测试系统还是产品;我假设它是产品。

很可能您已经将表的索引(或整批索引)调整到了不再适合内存的大小。

这意味着innodb必须在插入期间读取页面(取决于新行索引值的分布)。阅读页面(随机阅读)非常慢,如果可能的话需要避免。

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

当然,您应该考虑所有可能的选项——将表放到实验室中的测试服务器上,以查看它的行为。

在我看来,您的主键可能不是必需的(您有另一个唯一索引),因此消除它是一个选项。

同时考虑到innodb插件和压缩,这将使您的innodb缓冲池更进一步。

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

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

abdollar 14 年前

从我对InnoDB的经验来看,它似乎达到了写密集型系统的极限,即使您有一个真正优化的磁盘子系统。我很惊讶你能达到100GB。

这就是Twitter不久前遇到的,并意识到它需要分享-看 http://github.com/twitter/gizzard .

这完全取决于您的用例,但是您也可以从MySQL迁移到Cassandra,因为它对于写密集型应用程序的性能非常好。

Morgan Tocker 14 年前

正如markr上面所评论的,当索引不再适合缓冲池时,插入性能会变得更差。InnoDB有一个随机的IO减少机制(称为插入缓冲区),可以防止某些问题,但是它不能在您的唯一索引上工作。每次插入时都必须检查(hashcode,active)的索引,确保没有插入重复的条目。如果哈希代码不“跟随”主键,则此检查可能是随机IO。

您有可能更改模式吗?

你最好的选择是:

(A)在批量插入之前,将hashcode设置为顺序的,或者按hashcode排序(这本身会有所帮助,因为随机读取会减少)。

(B)使(hashcode,active)成为主键,并按排序顺序插入数据。我猜您的应用程序可能是通过哈希代码读取的——而且主键查找速度更快。

David Jeske 14 年前

您没有提到您的工作负载是什么样子的,但是如果没有太多的读取或者您有足够的主内存,另一个选项是使用MySQL的写优化后端,而不是InnoDB。Tokutek声称,随着数据集的增长,插入速度提高了18倍,性能曲线更平坦。

TokTykcom

http://tokutek.com/downloads/tokudb-performance-brief.pdf

Joshua Martell 14 年前

我会附和@markr关于减少索引的评论。另一件你应该注意的事情是增加你的InnoDB日志文件的大小。它会增加崩溃恢复时间,但会有所帮助。请注意,在重新启动服务器之前需要删除旧文件。

一般InnoDB调优提示: http://www.mysqlperformanceblog.com/2007/11/01/innodb-performance-optimization-basics/

你也应该知道 LOAD DATA INFILE 用于插入。速度快得多。

Andrei Sfat systemfreund 11 年前

从增加 innodb_log_file_size = 50M 到 innodb_log_file_size = 500M

以及 innodb_flush_log_at_trx_commit 如果承受1秒的数据丢失,则应为0。