MySQL插入性能在大表上降低 [英] MySQL Insert performance degrades on a large table
问题描述
我正在处理一个有250+百万行的巨大表.模式很简单.
I'm working with a huge table which has 250+ million rows. The schema is simple.
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亿行，过去通常需要5-6秒才能插入10,000行.现在，它已经上升了2-4倍. Innodb的ibdata文件已增加到107 GB. Innodb的配置参数如下.
The performance of insert has dropped significantly. Upto 150 million rows in the table, it used to take 5-6 seconds to insert 10,000 rows. Now it has gone up by 2-4 times. Innodb's ibdata file has grown to 107 GB. Innodb configuration parameters are as follows.
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
如top所示，
IO等待时间已经增加.我曾尝试将flush方法更改为O_DSYNC，但没有帮助.该磁盘是从硬件RAID 10设置中提取出来的.在单个磁盘的早期设置中，IO不是问题.
IO wait time has gone up as seen with top. I have tried changing the flush method to O_DSYNC, but it didn't help. The disk is carved out of hardware RAID 10 setup. In an earlier setup with single disk, IO was not a problem.
是否仅对表进行分区选项?将单个100G文件拆分为较小"的文件有帮助吗?是否需要为RAID调整任何变量?
Is partitioning the table only option? Can splitting single 100G file into "smaller" files help? Are there any variables that need to be tuned for RAID?
更新:这是一个测试系统.我可以自由进行任何所需的更改.
Update: This is a test system. I have the freedom to make any changes required.
推荐答案
您没有说这是测试系统还是产品；我假设它是生产中的.
You didn't say whether this was a test system or production; I'm assuming it's production.
表的大小可能已使其索引(或全部)不再容纳在内存中.
It is likely that you've got the table to a size where its indexes (or the whole lot) no longer fits in memory.
这意味着InnoDB必须在插入期间读取页面(取决于新行的索引值的分布).读取页面(随机读取)的速度确实很慢，因此应尽可能避免.
This means that InnoDB must read pages in during inserts (depending on the distribution of your new rows' index values). Reading pages (random reads) is really slow and needs to be avoided if possible.
分区似乎是最明显的解决方案，但是MySQL的分区可能不适合您的用例.
Partitioning seems like the most obvious solution, but MySQL's partitioning may not fit your use-case.
您当然应该考虑所有可能的选项-将表放在实验室中的测试服务器上以查看其行为.
You should certainly consider all possible options - get the table on to a test server in your lab to see how it behaves.
您的主键在我看来好像不是必需的(您有另一个唯一索引)，因此消除该键是一个选择.
Your primary key looks to me as if it's possibly not required (you have another unique index), so eliminating that is one option.
还考虑使用innodb插件和压缩，这将使您的innodb_buffer_pool更进一步.
Also consider the innodb plugin and compression, this will make your innodb_buffer_pool go further.
您确实需要分析用例，以确定是否实际上需要保留所有这些数据，以及分区是否是明智的解决方案.
You really need to analyse your use-cases to decide whether you actually need to keep all this data, and whether partitioning is a sensible solution.
对此应用程序进行任何更改很可能会给您的用户带来新的性能问题，因此您在这里要特别小心.如果找到提高插入性能的方法，则可能会降低搜索性能或其他操作的性能.在发布此类更改之前，您将需要对生产级硬件进行全面的性能测试.
Making any changes on this application are likely to introduce new performance problems for your users, so you want to be really careful here. If you find a way to improve insert performance, it is possible that it will reduce search performance or performance of other operations. You will need to do a thorough performance test on production-grade hardware before releasing such a change.
这篇关于MySQL插入性能在大表上降低的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
