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 (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.
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
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, …