mysql - 优化 InnoDB 插入查询

Question

根据慢查询日志，以下查询(和类似查询)偶尔会花费大约 2 秒的时间来执行:

INSERT INTO incoming_gprs_data (data,type) VALUES ('3782379837891273|890128398120983891823881abcabc','GT100');

表结构:

CREATE TABLE `incoming_gprs_data` (
 `id` int(200) NOT NULL AUTO_INCREMENT,
 `dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `data` text NOT NULL,
 `type` char(10) NOT NULL,
 `test_udp_id` int(20) NOT NULL,
 `parse_result` text NOT NULL,
 `completed` tinyint(1) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `completed` (`completed`)
) ENGINE=InnoDB AUTO_INCREMENT=5478246 DEFAULT CHARSET=latin1

与此表相关的事件:

1. 每秒大约有 200 行被插入到这个表中。传入的数据来自不同的来源(因此，它不会在一个进程中发生，而是每秒处理多个)。
2. cron 进程将通过 SELECT * FROM incoming_gprs_data WHERE completed = 0 获取行来处理这些行，处理它们并更新 completed = 1
3. 另一个 cron 进程(每 15 分钟运行一次)将删除已完成的行(即 completed = 1)以使表更 slim 。
4. 慢速日志查询并不表示任何与表相关的慢速 SELECT 查询。
5. 表的大小相对较小，不到 20 万行。

我们执行#2 和#3 的原因是因为之前我们发现删除已完成的行需要时间，因为需要重建索引。因此，我们添加了 completed 标志并降低了执行删除的频率。这些更改有助于减少慢速查询的数量。

这是我们拥有的 innodb_settings:

+---------------------------------+------------------------+
| Variable_name                   | Value                  |
+---------------------------------+------------------------+
| have_innodb                     | YES                    |
| ignore_builtin_innodb           | OFF                    |
| innodb_adaptive_flushing        | ON                     |
| innodb_adaptive_hash_index      | ON                     |
| innodb_additional_mem_pool_size | 8388608                |
| innodb_autoextend_increment     | 8                      |
| innodb_autoinc_lock_mode        | 1                      |
| innodb_buffer_pool_instances    | 2                      |
| innodb_buffer_pool_size         | 6442450944             |
| innodb_change_buffering         | all                    |
| innodb_checksums                | ON                     |
| innodb_commit_concurrency       | 0                      |
| innodb_concurrency_tickets      | 500                    |
| innodb_data_file_path           | ibdata1:10M:autoextend |
| innodb_data_home_dir            |                        |
| innodb_doublewrite              | OFF                    |
| innodb_fast_shutdown            | 1                      |
| innodb_file_format              | Antelope               |
| innodb_file_format_check        | ON                     |
| innodb_file_format_max          | Antelope               |
| innodb_file_per_table           | ON                     |
| innodb_flush_log_at_trx_commit  | 2                      |
| innodb_flush_method             | O_DIRECT               |
| innodb_force_load_corrupted     | OFF                    |
| innodb_force_recovery           | 0                      |
| innodb_io_capacity              | 200                    |
| innodb_large_prefix             | OFF                    |
| innodb_lock_wait_timeout        | 50                     |
| innodb_locks_unsafe_for_binlog  | OFF                    |
| innodb_log_buffer_size          | 67108864               |
| innodb_log_file_size            | 536870912              |
| innodb_log_files_in_group       | 2                      |
| innodb_log_group_home_dir       | ./                     |
| innodb_max_dirty_pages_pct      | 75                     |
| innodb_max_purge_lag            | 0                      |
| innodb_mirrored_log_groups      | 1                      |
| innodb_old_blocks_pct           | 37                     |
| innodb_old_blocks_time          | 0                      |
| innodb_open_files               | 300                    |
| innodb_purge_batch_size         | 20                     |
| innodb_purge_threads            | 0                      |
| innodb_random_read_ahead        | OFF                    |
| innodb_read_ahead_threshold     | 56                     |
| innodb_read_io_threads          | 4                      |
| innodb_replication_delay        | 0                      |
| innodb_rollback_on_timeout      | OFF                    |
| innodb_rollback_segments        | 128                    |
| innodb_spin_wait_delay          | 6                      |
| innodb_stats_method             | nulls_equal            |
| innodb_stats_on_metadata        | OFF                    |
| innodb_stats_sample_pages       | 8                      |
| innodb_strict_mode              | OFF                    |
| innodb_support_xa               | ON                     |
| innodb_sync_spin_loops          | 30                     |
| innodb_table_locks              | ON                     |
| innodb_thread_concurrency       | 0                      |
| innodb_thread_sleep_delay       | 10000                  |
| innodb_use_native_aio           | OFF                    |
| innodb_use_sys_malloc           | ON                     |
| innodb_version                  | 1.1.8                  |
| innodb_write_io_threads         | 4                      |
+---------------------------------+------------------------+

在使用以下 SQL 查询计算后，我们将 innodb_buffer_pool_size 设置为 6G:

SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM (SELECT SUM(data_length+index_length) Total_InnoDB_Bytes FROM information_schema.tables WHERE engine='InnoDB') A;

并生成5GB的结果。我们估计它不会超过我们的 InnoDB 表的这个大小。

我们目前主要关心的是如何加快对表的insert 查询以及导致偶尔缓慢插入查询的原因。

Answer 1

如您所知，每秒插入 200 行很多。尝试在这种规模的应用程序上优化此数据流是值得的。

InnoDB 使用 database transactions on all insertions .也就是说，每个插入看起来像这样:

 START TRANSACTION;
 INSERT something...;
 COMMIT;

如果您不指定这些事务，您将获得自动提交行为。

进行大量插入的秘诀是在每笔交易中进行多次插入，如下所示:

 START TRANSACTION;
 INSERT something...;
 INSERT something...;
 ...
 INSERT something...;
 INSERT something...;
 INSERT something...;
 COMMIT;
 START TRANSACTION;
 INSERT something...;
 INSERT something...;
 ...
 INSERT something...;
 INSERT something...;
 INSERT something...;
 COMMIT;
 START TRANSACTION;
 INSERT something...;
 INSERT something...;
 ...
 INSERT something...;
 INSERT something...;
 INSERT something...;
 COMMIT;

在每个 COMMIT; 之前，我已经成功地执行了多达一百个 INSERT 命令

不要忘记最后的 COMMIT! 不要问我怎么知道要给出这个建议。 :-)

在 MySQL 中执行此操作的另一种方法是使用多行 INSERT 命令在您的情况下，它们可能看起来像这样。

INSERT INTO incoming_gprs_data (data,type) VALUES
    ('3782379837891273|890128398120983891823881abcabc','GT100'),
    ('3782379837891273|890128398120983891823881abcabd','GT101'),
    ('3782379837891273|890128398120983891823881abcabe','GT102'),
       ...
    ('3782379837891273|890128398120983891823881abcabf','GT103'),
    ('3782379837891273|890128398120983891823881abcac0','GT104');

第三种方法，最难也是性能最高的方法，要获得非常高的插入率，是将批量数据存储在文本文件中，然后使用 the LOAD DATA INFILE command将数据放入表中。这种技术确实可以非常快，特别是如果文件可以直接从 MySQL 服务器的文件系统加载。

我建议您先尝试事务处理，看看是否能获得所需的性能。

另一件事:如果白天或晚上比较安静，您可以删除已完成的行，而不是每十五分钟删除一次。无论如何，当您读回这些行以进行处理或删除时，您应该使用这样的事务批处理过程:

   done = false   /* pseudocode for your programming language */
   while not done {
       DELETE FROM table WHERE completed = 1 LIMIT 50;
       if that query handled zero rows {
           done = true
       }
   }

这将以合理大小的事务批处理执行您的删除操作。您偶尔出现的两秒插入延迟可能是由于您处理或删除的事务批处理非常大的结果。