在200m表格上进行慢速INSERT查询 [英] Slow INSERT query on 200m table

问题描述

我们有下表，其中包含约2亿条记录:

We have the following table with about 200 million records:

CREATE TABLE IF NOT EXISTS `history` (
  `airline` char(2) NOT NULL,
  `org` char(3) NOT NULL,
  `dst` char(3) NOT NULL,
  `departat` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `arriveat` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `validon` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `price` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
/*!50500 PARTITION BY RANGE  COLUMNS(org)
(PARTITION p0 VALUES LESS THAN ('AHI') ENGINE = MyISAM,
 PARTITION p1 VALUES LESS THAN ('ARO') ENGINE = MyISAM,
 ...
 PARTITION p39 VALUES LESS THAN ('WMA') ENGINE = MyISAM,
 PARTITION p40 VALUES LESS THAN (MAXVALUE) ENGINE = MyISAM) */;

--
-- Indexes for table `history`
--
ALTER TABLE `history`
 ADD KEY `tail` (`org`,`dst`,`departat`);

我们经常进行一些VALUES的批量插入，通常在简单的INSERT查询中最多存储1000条记录，并且没有任何修饰，例如ON DUPLICATE KEY(索引也不是唯一的).

We're doing bulk inserts of some VALUES frequently, usually up to 1000 records in simple INSERT queries, without any decoration such as ON DUPLICATE KEY (the index is not unique anyway).

有时，当我进入phpMyAdmin中的服务器状态时，看到一堆INSERT语句彼此等待，有时长达300-400秒.在特定时间，服务器上似乎没有其他任何事情.我们获得了32 GB的存储空间，并且还有其他出色的性能.

Sometimes when I go to server status in phpMyAdmin, a see a bunch of INSERT statements waiting for each other, sometimes for up to 300-400 seconds. Nothing else seems to be going on the server at the particular time. We got 32 GB and otherwise excellent performance.

如何解决此问题?感谢您的帮助.

How to troubleshoot this issue? Thanks for help.

推荐答案

可能的第一步是使用通常，您会做类似的事情:

Usually you'd do something like:

SET LOCAL PROFILING=ON;
-- run your INSERT, like:
INSERT INTO yourtable (id) VALUES (1),(2),(3);

SHOW PROFILES;
+----------+------------+------------------------------------------------+
| Query_ID | Duration   | Query                                          |
+----------+------------+------------------------------------------------+
|     1012 | 6.25220000 | INSERT INTO yourtable (id) VALUES (1),(2),(3); |
+----------+------------+------------------------------------------------+

这告诉您非常基本的信息，例如查询持续时间(在这种情况下为6.25秒).要获取实际的详细信息，您需要提取用于该查询的配置文件:

This tells you very basic information, like duration of the query (6.25 sec in this case). To get the actual details you need to pull up the profile for said query:

SHOW PROFILE FOR QUERY 1025; 
+------------------------------+----------+
| Status                       | Duration |
+------------------------------+----------+
| starting                     | 0.004356 |
| checking permissions         | 0.000015 |
| Opening tables               | 6.202999 |
| System lock                  | 0.000017 |
| init                         | 0.000342 |
| update                       | 0.023951 |
| Waiting for query cache lock | 0.000008 |
| update                       | 0.000007 |
| end                          | 0.000011 |
| query end                    | 0.019984 |
| closing tables               | 0.000019 |
| freeing items                | 0.000304 |
| logging slow query           | 0.000006 |
| cleaning up                  | 0.000181 |
+------------------------------+----------+

您可能会注意到打开桌子" 花了很长时间.在此示例中，通过另一个进程锁定表(LOCK TABLES)来延迟执行，从而延迟了查询执行.有关状态的更多信息，请参见手册.

You may notice that 'Opening tables' took very long. In this example query execution was delayed by locking the table (LOCK TABLES) by another process to delay the execution. Further information about the states is available in the manual.

这篇关于在200m表格上进行慢速INSERT查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！