主页 / dba / 问题 / 29913
Accepted
inohiro
Asked: 2012-12-06 23:13:31 +0800 CST

InnoDB 插入速度更快

  • 772

我是一名研究生，正在与 Mondrian OLAP 一起研究 OLAP。所以我想在初始加载时更快地将数据插入 InnoDB (MySQL 5.5)。在这种环境下，唯一的用户是我，所以我认为可以允许更宽松的插入速度设置。目前，我正在使用以下技术。

  • 禁用log_bin
  • 使能够skip-innodb-doublewrite
  • 设置transaction_isolation为READ-COMMITTED或READ-UNCOMMITTED（实际上READ-COMMITED）
  • 设置innodb_flush_log_at_trx_commit为0或2（实际上0）
  • 设置innodb_buffer_pool_size为 5GB（系统有 6GB RAM）

是否有更多技术可以在 InnoDB 上更快地插入？我必须修改innodb_io_read_threadandinnodb_io_write_thread吗？如果您需要更多信息，请告诉我。

mysql innodb

4 个回答

  1. Best Answer

    建议 #1

    如果您的机器有多个核心，则需要增加以下内容：

    [mysqld]
innodb_read_io_threads = 64
innodb_write_io_threads = 64
innodb_io_capacity = 5000

    这些是什么？

    建议 #2

    为了从系统表空间（ibdata1）中分离数据和索引，您需要执行 InnoDB 的完整重组。听起来很复杂，但非常简单。我在DBA StackExchange（2012 年 8 月 29 日）和StackOverflow（2010 年 10 月 29 日）中对此进行了介绍。基本步骤是

    • 跑SET GLOBAL innodb_fast_shutdown = 0;
    • mysqldump 所有数据到 SQL 转储
    • service mysql stop
    • 删除以下文件
      • ibdata1
      • ib_logfile0
      • ib_logfile1
    • service mysql start

    在运行之前service mysql start，将此行添加到my.cnf

    innodb_open_files=32768

    这样，将有专用于每个单独表的文件句柄。默认值为 300。已知文件句柄会被缓存。如果您将其设置得非常高并迅速达到天花板，将会出现减速。如果您正在处理少量表，则不应出现这种情况。

    • 12

  2. 有一个完整的文档专门用于将数据批量加载到 InnoDB 中。要点：

    1. 禁用自动提交以避免每个插入语句的额外日志刷新：SET autocommit=0;...sql import;COMMIT;

    2. 禁用外部和唯一检查（您不能完全禁用所有索引）：

      SET unique_checks=0;
SET foreign_key_checks=0;
    3. 可能将innodb_autoinc_lock_mode设置为 2，而不是 1（默认值）。这是有关此设置的文档。

    第三个可能会或可能不会帮助您，因此我建议阅读该链接以了解您最初是如何加载数据的。例如，如果您将负载分成多个插入以同时运行，它肯定会帮助您将值设置为 2。如果您正在执行一个大型多行插入，它不会做太多（如果有的话）帮助。

    由于您正在为此初始插入打开二进制日志，因此您不应该关心自动增量数字中的间隙（如果进行并发插入）。

    • 6

  3. 您可以使用以下方法来加快插入速度：

    • 如果您同时从同一个客户端插入多行，请使用INSERT具有多个列表的语句一次VALUES插入多行。INSERT这比使用单独的单行语句要快得多（在某些情况下快很多倍） 。如果要向非空表添加数据，则可以调整 bulk_insert_buffer_size 变量以使数据插入更快。
    • 从文本文件加载表格时，使用LOAD DATA INFILE. 这通常比使用INSERT语句快 20 倍。看
    • 利用列具有默认值的事实。仅当要插入的值与默认值不同时才显式插入值。这减少了 MySQL 必须做的解析并提高了插入速度。
    • 有关 InnoDB 表的特定提示，请参阅第 9.5.5 节，“ InnoDB 表的批量数据加载”。
    • 1

  4. 计划 A：“批量”插入——每个插入语句多行。建议每条语句大约 1000 行。autocommit=on，没有明确的 BEGIN...COMMIT

    计划 B：加载数据

    如果一次插入太多行，InnoDB 必须做更多工作才能在发生崩溃时回滚插入。出于这个原因，我不同意 autocommit=off，它会将整个集合放入一个事务中。

    整组行的 LOAD DATA 可能会有同样的问题，但速度相当快。

    buffer_pool = 6G 中的 5G 处于太大的边缘。如果有任何交换，性能将直线下降。

    分区可能会使它变慢。

    SHOW CREATE TABLE -- 辅助键可能是一个严重的障碍。

    你在使用 InnoDB 吗？还是 XtraDB？

    • 0

相关问题