MySQL中的磁盘读取慢速插入

Question

我试图优化MariaDB (10.0.31)上InnoDB表上大型插入查询的速度。

以下是表的结构(1.31亿行)：

Field__     Type___     Null    Key     Default     Extra   
ID_num_     bigint(45)  NO      PRI     NULL    
Content     varchar(250)YES             NULL    
User_ID     bigint(24)  NO      MUL     NULL    
Location    varchar(70) YES             NULL    
Date_creat  datetime    NO      MUL     NULL    
Retweet_ct  int(7)      NO              NULL    
isRetweet   tinyint(1)  NO              NULL    
hasReetwet  tinyint(1)  NO              NULL    
Original    bigint(45)  YES             NULL    
Url____     varchar(150)YES             NULL    
Favorite_c  int(7)      NO              NULL    
Selected    int(11)     NO              0   
Sentiment   int(11)     NO              0   

这是CREATE TABLE的输出

CREATE TABLE `Twit` (
 `ID_num` bigint(45) NOT NULL,
 `Content` varchar(250) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `User_ID` bigint(24) NOT NULL,
 `Location` varchar(70) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `Date_create` datetime NOT NULL,
 `Retweet_count` int(7) NOT NULL,
 `isRetweet` tinyint(1) NOT NULL,
 `hasReetweet` tinyint(1) NOT NULL,
 `Original` bigint(45) DEFAULT NULL,
 `Url` varchar(150) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `Favorite_count` int(7) NOT NULL,
 `Selected` int(11) NOT NULL DEFAULT '0',
 `Sentiment` int(11) NOT NULL DEFAULT '0',
 PRIMARY KEY (`ID_num`),
 KEY `User_ID` (`User_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

以下是索引的结构：

Table   Non_unique  Key_name    Seq_in_index    Column_name     Collation   Cardinality     Sub_part Packed     Null    Index_type  Comment     Index_comment   
Twit    0           PRIMARY     1               ID_num          A           124139401       NULL     NULL       BTREE       
Twit    1           User_ID     1               User_ID         A           535083          NULL     NULL       BTREE       

这是show engine innodb status

BUFFER POOL AND MEMORY
----------------------
Total memory allocated 8942256128; in additional pool allocated 0
Total memory allocated by read views 184
Internal hash tables (constant factor + variable factor)
   Adaptive hash index 141954688 (141606424 + 348264)
   Page hash           4426024 (buffer pool 0 only)
   Dictionary cache    35656039 (35403184 + 252855)
   File system         845872 (812272 + 33600)
   Lock system         21251648 (21250568 + 1080)
   Recovery system     0 (0 + 0)
Dictionary memory allocated 252855
Buffer pool size        524286
Buffer pool size, bytes 8589901824
Free buffers            448720
Database pages          75545
Old database pages      27926
Modified db pages       0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 74639, created 906, written 39133
0.12 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 75545, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]

我使用下面的Python代码从第三方源代码下载数据，然后用它填充我的表：

add_twit = (" INSERT INTO Table (ID_num, Content,....) VALUES (%s, %s, ....)")
testtime=0
t0 = time.time()
data_twit = []

#### Data Retrieving  ####
for page in limit_handled(...):
    for status in page:
        data_twit.append(processed_tweet)
####


##### MySQL Insert 
tt0 = time.time()
cursorSQL.executemany(add_twit, data_twit)
testtime += time.time() - tt0
####

cnx.commit()
print('Total_TIME ' + str(time.time()-t0))
print('Sqlexecute_TIME ' + str(testtime))

该守则是做什么的：

它从第三方提供程序获得twits，其中16页，每页有200个twits (状态)，因此每个iteratin (用户)总共要向表中添加3200行。我尝试在每个tweet中插入一个查询(使用cursorSQL.execute(add_twit, data_twit)，在列表中也有16个查询200个tweet，但最快的几秒钟就是使用优化的cursorSQL.executemany函数对3200条tweet进行一次查询。

对于3200条推特，下载它们需要10秒钟，将它们写入数据库需要大约75秒钟，考虑到一条推文(行)目前只需0.2ko，那么3200就是640 Ko。不需要75秒钟..。

使用iotop监视磁盘使用情况时会发生什么？

• 在数据检索部分代码(在第一次迭代之后)：
  • 读为= 0.00 B/s
  • Write= 6.50M/s

​

磁盘实际上在大插入后以6 6Mbs/s的速率写了几分钟。

• 在SQL-插入部分代码期间：

- Read = 1.5 M/s
- Write= 300 K/s

看起来像磁盘读取(为了索引的目的，我猜？)使写作率下降。

我试过的是：

• 尝试拆分插入查询(而不是1*3200行，我尝试了16*200行和3200*1行，没有改变任何东西，1*3200行略快)
• 优化表(提高了15%的速度)
• 删除不必要的索引

我的问题：

• 为什么当我提交插入查询而不是写入时磁盘开始读取？有什么办法可以防止这种情况吗？
• 是否会删除所有索引以帮助加速插入？
• 我是否需要删除主键(不是列，只是其中唯一的索引)，即使这听起来是个坏主意，而且( MySQL slows down after INSERT )建议不要？
• 还有其他建议吗？
• 此外，为什么磁盘在大插入数分钟后仍以6.00Mb/s的速度写入？

Answer 1

• 桌子上大约有60 in？
• 在User_ID索引中大约5GB？(见Index_length in SHOW TABLE STATUS LIKE 'Twit.)
• 每个INSERT大约有3200个新行？如果这是错误的，那么这就是主要的问题。
• 您正在计算ID_num，而不是使用AUTO_INCREMENT
• ID_num是单调增长的？(或至少大约。)如果这是错误的，那它就是主要的问题。
• User_ID是相当随机的。

分析和结论：

• 数据被“附加到”；这对缓存(buffer_pool，8GB)没有太大影响。
• User_ID索引正在被随机更新；这使得大部分索引保存在缓存中，或者，可能是溢出。如果您刚刚开始溢出，那么性能正在下降，而且随着缓存丢失的增加，性能会越来越差。
• “写完后继续输入/输出”--这是正常的。有关血淋淋的细节，请查阅"InnoDB更改缓冲“。摘要：INDEX(User_ID)的更新是延迟的，但最终必须发生。

部分解决办法：

• 更多内存。
• 将innodb_buffer_pool_size提高到内存的70%，确保不会导致交换。
• 你肯定没有超过40亿的用户？将User_ID从BIGINT (8字节)收缩为INT UNSIGNED (4字节)。这将使二级指数收缩约25%。
• DROP INDEX(User_ID) --你肯定需要它吗？
• 你在其他地方使用ID_num吗？如果没有，请解释它的存在。
• 在适当情况下，从NULL更改为NOT NULL。(这无助于提高速度，但却是一次清理。)
• 使用AUTO_INCREMENT而不是手写的id。(可能帮不上忙)

基准：

• 我不会使用任何“原始”I/O指标--它们对InnoDB的“阻塞”和更改缓冲区感到困惑。
• 等待“稳定状态”。也就是说，避免小桌子，冷冻机，爆裂等。一个图表，每3200花多长时间将有起伏，因为这样的事情。但最终它将达到一个“稳定的状态”。但是，根据我对二级索引的分析，这可能会下降到3200行，需要32秒(如果使用旋转磁盘)。
• 在7秒内3200没有意义。我想我真的需要看到生成的SQL。

Answer 2

如果您有索引，那么您将有磁盘读取来查找这些索引。当插入磁盘以找到合适的位置时，总是会发生一些读取。

删除索引将加快插入速度，而牺牲以后的读取操作。

是否删除主索引很大程度上取决于用例，有多大程度上信任数据源不存在完全复制。但是，使用主键读取数据库所需的任何内容以后都会在性能上付出很大的代价。然而，这将加快写操作。

您可能需要考虑RDBMS的其他设置，例如分片，这将允许您分发负载。没有硬件扩展，或者至少是某种并行性，就可以解决许多问题，这些问题可能不适合您的用例。