我正在处理一个包含250+百万行的大型表。模式很简单。
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;
insert的性能显著下降。表中最多有1.5亿行,插入10,000行通常需要5-6秒。现在它已经上升了2-4倍。Innodb的ibdata文件已增长到107 GB。Innodb配置参数如下。
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
IO等待时间已经上升,就像在top
中看到的那样。我尝试过将刷新方法更改为O_DSYNC,但没有帮助。磁盘是由硬件RAID 10设置雕刻而成的。在使用单个磁盘的早期设置中,IO不是问题。
分区表是唯一的选项吗?将单个100 G文件拆分成“更小”的文件有帮助吗?是否有需要对RAID进行调优的变量?
更新:这是一个测试系统。我有自由作出任何必要的改变。
发布于 2010-09-09 12:08:20
您没有说这是测试系统还是生产;我假设这是生产。
很可能您已经将表的大小设置为其索引(或全部)不再适合内存的大小。
这意味着InnoDB必须在插入期间读取页面(取决于新行的索引值的分布)。阅读页面(随机读取)真的很慢,如果可能的话需要避免。
分区似乎是最明显的解决方案,但MySQL的分区可能不适合您的用例。
当然,您应该考虑所有可能的选项--把表放到实验室的测试服务器上,看看它是如何工作的。
在我看来,您的主键可能不是必需的(您有另一个唯一的索引),所以删除它是一种选择。
此外,考虑到无害数据库插件和压缩,这将使您的innodb_buffer_pool更进一步。
您确实需要分析您的用例,以确定是否确实需要保存所有这些数据,以及分区是否是一个明智的解决方案。
对此应用程序进行任何更改可能会给用户带来新的性能问题,因此您需要在这里非常小心。如果您找到了一种改进插入性能的方法,则可能会降低搜索性能或其他操作的性能。在发布这样的更改之前,您需要对生产级硬件进行彻底的性能测试。
发布于 2010-09-23 20:56:30
根据我使用Innodb的经验,即使您有一个真正优化的磁盘子系统,编写密集型系统似乎也受到了限制。我很惊讶你竟然能把它提高到100 up。
这是twitter在一段时间前遇到的,并意识到它需要切碎--参见http://github.com/twitter/gizzard。
这都取决于您的用例,但您也可以从mysql迁移到cassandra,因为它对于编写密集的应用程序非常好。(http://cassandra.apache.org)
发布于 2010-09-23 20:25:24
正如上面MarkR所评论的,当索引不再适合您的缓冲池时,插入性能会变得更差。InnoDB有一个随机的IO减少机制(称为insert缓冲区),它可以防止某些问题--但是它不能处理唯一的索引。必须在每次插入时检查索引(hashcode,active),确保没有插入重复的条目。如果哈希代码不“跟随”主键,则此检查可能是随机IO。
您有可能更改架构吗?
你最好的选择是:
(a)使散列码按顺序排列,或在大容量插入前按哈希码排序(这本身会有所帮助,因为随机读取将减少)。
(b)使(哈希码,活动)成为主键-并按排序顺序插入数据。我猜您的应用程序可能是通过hashcode读取的--主键查找更快。
https://stackoverflow.com/questions/3676209
复制相似问题