Slow InnoDB insert/update

If you’re migrating from MyISAM to InnoDB or you’re using MySQL 5.5.x or newer (InnoDB default engine) you’ll probably be disappointed with INSERT/UPDATE queries (with InnoDB tables). InnoDB is a transaction-safe, ACID compliant MySQL storage engine and with default settings, the log buffer is written out to the log file at each transaction commit and the flush to disk operation is performed on the log file. This can be very slow (but very safe – every transaction is 100% written to the disk).

Since MyISAM is not an option, we need to tune up our server so it can be used with InnoDB correctly. According to MySQL site, the next couple things should be considered:

  • Use OPTIMIZE TABLE statement to reorganize the table and compact any wasted space. Of course this operation won’t help if your database is empty
  • Use AUTO_INCREMENT column as the primary key
  • If you’re storing variable-length strings or if the column may contain NULL values, use the VARCHAR data type instead of CHAR (smaller tables fit better in the buffer pool and reduce disk I/O)
  • Since InnoDB must flush the log to disk at each transaction commit (if that transaction made modifications to the database), attach several queries into a single transaction to reduce the number of flush operations
  • In case you’re not building a finance application which can’t afford data loss if a crash occurs, you can set the parameter innodb_flush_log_at_trx_commit parameter to 0. In this case, InnoDB tries to flush the log once per second and not after every transaction (default setting is 1 which mean flush the log after every transaction).
  • To reduce the amount of disk I/O used by queries to access InnoDB tables, you can increase the innodb_buffer_pool_size.
  • Big disk-bound operations are always expensive. Use DROP TABLE and CREATE TABLE to empty a table, not DELETE FROM….Also TRUNCATE TABLE is much faster then DELETE * FROM…
  • innodb_flush_method parameter can also help but you must test yourself to see the right combination for your hardware and your database (possible values: fdatasync, O_DSYNC, O_DIRECT).
  • Make your log files big, even as big as the buffer pool and make the log buffer quite large as well
  • Disable autocommit during import operation (surround it with SET autocommit and COMMIT statements)
    SET autocommit=0;
     SQL queries
  • Temporarily turning off the uniqueness checks during the import session will help.
    SET unique_checks=0;
     SQL queries
    SET unique_checks=1;
  • Turn off foreign key checks during imports.
    SET foreign_key_checks=0;
     SQL queries
    SET foreign_key_checks=1;
  • If you often have repeating queries for tables that are not updated frequently, enable the query cache with
    query_cache_type = 1
    query_cache_size = 10M
  • Use the multiple-row INSERT syntax to reduce communication overhead between the client and the server if you need to insert many rows:
    INSERT INTO tbl VALUES (1,2), (5,5), ...;

The list above is not the final one. Please check the next link for more details about those parameters. Link
In my case, I won’t change a lot of parameters. The only parameter which I will change is the innodb_flush_log_at_trx_commit = 1 (default value is 1).

Before and after performance will be tested with Sysbench (Link). Since reading is not problem right now, I’ll stick with the write operations.

R/W test

sysbench --num-threads=16 --max-requests=10000 --test=oltp --oltp-table-size=500000 --mysql-socket=/var/lib/mysql/mysql.sock --oltp-test-mode=complex --mysql-user=TEST_USER --mysql-password=TEST_PASSWORD run

The result

OLTP test statistics:
    queries performed:
        read:                            146216
        write:                           52220
        other:                           20446
        total:                           218882
    transactions:                        10002  (181.90 per sec.)
    deadlocks:                           442    (8.04 per sec.)
    read/write requests:                 198436 (3608.90 per sec.)
    other operations:                    20446  (371.85 per sec.)
Test execution summary:
    total time:                          54.9852s
    total number of events:              10002
    total time taken by event execution: 879.1034
    per-request statistics:
         min:                                 33.38ms
         avg:                                 87.89ms
         max:                                480.77ms
         approx.  95 percentile:             135.31ms
Threads fairness:
    events (avg/stddev):           625.1250/2.29
    execution time (avg/stddev):   54.9440/0.03

Total time: 54.98s

Now, when I change innodb_flush_log_at_trx_commit to 0 (default value was 1), I get:

OLTP test statistics:
    queries performed:
        read:                            140000
        write:                           50000
        other:                           20000
        total:                           210000
    transactions:                        10000  (780.35 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 190000 (14826.69 per sec.)
    other operations:                    20000  (1560.70 per sec.)
Test execution summary:
    total time:                          12.8147s
    total number of events:              10000
    total time taken by event execution: 204.8297
    per-request statistics:
         min:                                  1.19ms
         avg:                                 20.48ms
         max:                               1669.69ms
         approx.  95 percentile:              44.50ms
Threads fairness:
    events (avg/stddev):           625.0000/19.56
    execution time (avg/stddev):   12.8019/0.00

Total time: 12.81s

As you can see, changing innodb_flush_log_at_trx_commit from 1 to 0 increases the write speed but we can lose data in some cases (hardware or power failures, etc). To avoid this problem, use battery backups, UPS, RAID, …

2 thoughts on “Slow InnoDB insert/update

  1. Are you sure “changing innodb_flush_log_at_trx_commit from 0 to 1 increases the write speed but we can lose data in some cases”. According to “A value of 1 is required for ACID compliance. You can achieve better performance by setting the value different from 1, but then you can lose at most one second worth of transactions in a crash”.
    Please, meke me sure, if you changed innodb_flush_log_at_trx_commit from 0 to 1 or 1 to 0.

  2. Thanks Tomas ! You’re right… I made a typo on two places (the last test was made with
    innodb_flush_log_at_trx_commit = 0 in my.cnf)

    I corrected the errors

    Best regards

Leave a Reply

Your email address will not be published. Required fields are marked *