CODEWITHSUNDEEP
mysqlinsertinnodbdatabase-performance
Ethan
Ethan

Reputation: 4995

Very slow Innodb insert: 65secs for 1K entries in empty table

I notice that my mysql inserts are very slow. To test and demonstrate, I used the following table:

+----------+-----------------------------------------------+
| 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 |
+----------+-----------------------------------------------+

and python code:

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

When I run this insert-only code on an empty table, it consumes 65 secs for 1K inserts. I have innodb_flush_log_at_trx_commit = 1 and I need that as the tables can't afford to loose any data. My question is that with this set, can the insert get so slow? Or am I missing something else as well?

Upvotes: 0

Views: 1282

Answers (3)

Delicious
Delicious

Reputation: 968

With db.execute() you are inserting one-by-one and it will be very slow. Use the loop to build a list then do one bulk insert i.e. 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

Upvotes: 0

Dima Tisnek
Dima Tisnek

Reputation: 11781

I think you hit a limit in number of transactions per second. Each of your inserts is treated as a separate transaction, you must be in autocommit mode.

(1) Insert many rows in a single SQL statement, and you're ok:

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

Python equivalent is something like this:

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

(2) Alternatively you can start transaction explicitly:

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

If you have a decent server, you tx rate should be much much higher than 15 a second, so check your machine and mysql settings. Even if you commit to disk and wait every time (sqlite) you should be 100 tx/s on commodity hard drive. I have only seen rate this low when database was located usb flash. Another possible explanation is your python is very far away from the database and network latency kills performance, in this case (1) helps, (2) doesn't.

Upvotes: 0

Thomas
Thomas

Reputation: 181735

"Can't afford to lose any data." It's just a matter of degree. Without flushing you might lose the last second of data. With flushing, you might lose only the thing that was being written at the time. Do you really want to take a massive performance hit over that?

Also, if your disk breaks, you'll lose all data since your last backup anyway, which is inevitable in the long run and concerns much more data, so I'd be more worried about making frequent backups.

Disable the flushing. I reckon that'll easily take tens, if not hundreds of milliseconds per insert, because of all the disk activity. Having a few indexes on the table will make it even worse.

If, despite all this, you absolutely must flush on every write, you would also see big performance improvements if you put your database on an SSD.

Upvotes: 1

Related Questions