非常慢的Innodb插入:空表中1K条目需要65秒

Question

我注意到我的mysql插入非常慢。为了进行测试和演示，我使用了下表：

+----------+-----------------------------------------------+
| Table    | Create Table                                  |
+----------+-----------------------------------------------+
| ik_b64_8 | CREATE TABLE `incr_tbl` (
  `cnt` int(11) NOT NULL,
  PRIMARY KEY (`cnt`)
) ENGINE=InnoDB DEFAULT CHARSET=ascii COLLATE=ascii_bin |
+----------+-----------------------------------------------+

和蟒蛇代码：

def profile_basic(cnt):
    db = database.Connection("localhost","testing_delme","root", "")
    t1 = time.time()
    for ii in range(cnt):
        db.execute("INSERT INTO testing_delme.incr_tbl VALUES (%s)", ii)
    print time.time() - t1

当我在空表上运行此仅插入代码时，1K 插入需要 65 秒。我有 innodb_flush_log_at_trx_commit = 1，我需要它，因为表格无法承受丢失任何数据。我的问题是，有了这套，插入会这么慢吗？还是我也错过了别的东西？

Answer 1

"

不能丢失任何数据。这只是程度的问题。如果不刷新，您可能会丢失最后一秒的数据。使用刷新时，您可能只会丢失当时正在编写的内容。你真的想承受巨大的性能打击吗？

此外，如果您的磁盘损坏，无论如何您都会丢失自上次备份以来的所有数据，从长远来看，这是不可避免的，并且涉及更多数据，所以我更担心频繁备份。

禁用刷新。我认为每次插入很容易花费数十甚至数百毫秒，因为所有磁盘活动。表上有几个索引会使情况变得更糟。

尽管如此，如果绝对必须在每次写入时刷新，那么如果您将数据库放在 SSD 上，您还将看到巨大的性能改进。

Answer 2

我认为您达到了每秒事务数的限制。每个插入都被视为一个单独的事务，您必须处于自动提交模式。

（1）在单个SQL语句中插入许多行，就可以了：

insert into incr_tbl values (1),(2),(3)....

Python 等效物是这样的：

db.execute("insert into incr_tbl values %s" % ",".join(["(%s)" % i for i in range(xx)]))

（2） 或者，您可以明确地开始交易：

db = ...(autocommit=False)
db.begin_transaction()
for i in xx: db.execute(... insert i ...)
db.commit()

如果你有一个不错的服务器，你的tx速率应该远高于每秒15，所以检查你的机器和mysql设置。即使您提交到磁盘并每次等待 （sqlite），您在商用硬盘驱动器上也应该是 100 tx/s。我只在数据库位于USB闪存时看到过这么低的速率。另一种可能的解释是，你的python离数据库很远，网络延迟会降低性能，在这种情况下（1）有帮助，（2）没有帮助。

Answer 3

使用db.execute()，您将一个接一个地插入，并且会非常慢。使用循环构建列表，然后进行一次批量插入，即 db.executemany()

def profile_basic(cnt):
    import mysql.connector, time
    cnx = mysql.connector.connect("localhost","testing_delme","root", "")
    db = cnx.cursor()
    list_ii = []
    t1 = time.time()
    for ii in range(cnt):
        list_ii.append(ii)
    # One bulk insert
    db.executemany("INSERT INTO testing_delme.incr_tbl VALUES (%s)", list_ii)
    # Don't forget to commit
    db.commit()
    print time.time() - t1