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 COMMIT;

• 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.

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 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 0 to 1 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, …