随着表在mysql中的增长，插入速度变慢 [英] Insertion speed slowdown as the table grows in mysql

问题描述

我正试图更好地了解自定义产品在mysql中的插入速度和性能模式.我有两个表，我一直向其追加新行.这两个表的定义如下:

I am trying to get a better understanding about insertion speed and performance patterns in mysql for a custom product. I have two tables to which I keep appending new rows. The two tables are defined as follows:

CREATE TABLE events (
 added_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
 id BINARY(16) NOT NULL,
 body MEDIUMBLOB,
 UNIQUE KEY (id)) ENGINE InnoDB;

CREATE TABLE index_fpid (
 fpid VARCHAR(255) NOT NULL,
 event_id BINARY(16) NOT NULL UNIQUE,
 PRIMARY KEY (fpid, event_id)) ENGINE InnoDB;

我一直将新对象插入到两个表中(对于每个新对象，我在一个事务中将相关信息插入到两个表中).最初，我得到了大约600次插入/秒，但是在大约30000行之后，我得到了显着的减慢(大约200次插入/秒)，然后变得更慢，但是仍然很明显.

And I keep inserting new objects to both tables (for each new object, I insert the relevant information to both tables in one transaction). At first, I get around 600 insertions / sec, but after ~ 30000 rows, I get a significant slowdown (around 200 insertions/sec), and then a more slower, but still noticeable slowdown.

我看到随着表的增长，IO等待数越来越高.我首先想到的是索引占用的内存，但是这些操作是在具有768 Mb的VM上完成的，并且仅用于此任务(2/3的内存未使用).此外，我很难看到30000行占用了如此多的内存，甚至索引也是如此(整个mysql数据目录<100 Mb仍然如此).为了确认这一点，我为VM分配了很少的内存(64 Mb)，并且减速模式几乎相同(即，在插入相同数量的插入后出现减速)，因此我怀疑某些配置问题，尤其是因为我相对较新数据库.

I can see that as the table grows, the IO wait numbers get higher and higher. My first thought was memory taken by the index, but those are done on a VM which has 768 Mb, and is dedicated to this task alone (2/3 of memory are unused). Also, I have a hard time seeing 30000 rows taking so much memory, even more so just the indexes (the whole mysql data dir < 100 Mb anyway). To confirm this, I allocated very little memory to the VM (64 Mb), and the slowdown pattern is almost identical (i.e. slowdown appears after the same numbers of insertions), so I suspect some configuration issues, especially since I am relatively new to databases.

该模式如下所示:

我有一个独立的python脚本，可以重现该问题，如果有帮助的话，我可以提供.

I have a self-contained python script which reproduces the issue, that I can make available if that's helpful.

配置:

• Ubuntu 10.04，在KVM上运行32位，分配了760 Mb.
• Mysql 5.1，具有针对表的单独文件的开箱即用配置

非常感谢埃里克·霍尔姆伯格(Eric Holmberg)，他钉上了钉子.以下是将innodb_buffer_pool_size固定为合理值后的图形:

Thank you very much to Eric Holmberg, he nailed it. Here are the graphs after fixing the innodb_buffer_pool_size to a reasonable value:

推荐答案

编辑/etc/mysql/my.cnf文件，并确保为InnoDB缓冲池分配了足够的内存.如果这是一个专用服务器，则可能会使用多达80％的系统内存.

Edit your /etc/mysql/my.cnf file and make sure you allocate enough memory to the InnoDB buffer pool. If this is a dedicated sever, you could probably use up to 80% of your system memory.

# Provide a buffer pool for InnoDB - up to 80% of memory for a dedicated database server
innodb_buffer_pool_size=614M

主键是B树，因此插入将始终花费O(logN)时间，一旦缓存用完，它们将像疯了一样开始交换.发生这种情况时，您可能需要对数据进行分区以保持插入速度.参见 http://dev.mysql.com/doc/refman/5.1/zh_cn/partitioning.html 了解有关分区的更多信息.

The primary keys are B Trees so inserts will always take O(logN) time and once you run out of cache, they will start swapping like mad. When this happens, you will probably want to partition the data to keep your insertion speed up. See http://dev.mysql.com/doc/refman/5.1/en/partitioning.html for more info on partitioning.

祝你好运！

这篇关于随着表在mysql中的增长，插入速度变慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！