InnoDB inserts 100 times slower than SQL Server inserts

I have a simple table with two columns: id (autonumber) and name (char(255).

When I use C# to insert 100 records in a loop it takes about 30 milliseconds per insert. This is way too slow. So I browsed around on this site and found this setting:

set global innodb_flush_log_at_trx_commit = 2;

Now the inserts are as fast as SQL Server: .2 millisecond, or about 100 times faster than with the default setting.

But I've read that with setting innodb_flush_log_at_trx_commit to 2 you lose ACID compliance.

These are my measurements:

• MySql InnoDB innodb_flush_log_at_trx_commit = 1: 30 ms per insert.

• MySql InnoDB innodb_flush_log_at_trx_commit = 2: 0.2 ms per insert.

• Sql Server: 0.2 ms per insert.

I have a few questions:

• Is the default setting for Sql Server comparable to "innodb_flush_log_at_trx_commit = 1" or to "innodb_flush_log_at_trx_commit = 2"?

• Is there any other way to speed up MySql InnoDB which doesn't have influence on ACID compliance?

• Do you guys feel my measurements for the default InnoDB settings are correct? I almost can't believe MySql is 100 times slower than SQL Server for inserts?

Update 1

This is the contenct of the my.ini file:

```
# Other default tuning values
# MySQL Server Instance Configuration File
# ----------------------------------------------------------------------
# Generated by the MySQL Server Instance Configuration Wizard
#
#
# Installation Instructions
# ----------------------------------------------------------------------
#
# On Linux you can copy this file to /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options
# (@localstatedir@ for this installation) or to
# ~/.my.cnf to set user-specific options.
#
# On Windows you should keep this file in the installation directory
# of your server (e.g. C:\Program Files\MySQL\MySQL Server X.Y). To
# make sure the server reads the config file use the startup option
# "--defaults-file".
#
# To run run the server from the command

Is the default setting for Sql Server comparable to "innodb_flush_log_at_trx_commit = 1" or to "innodb_flush_log_at_trx_commit = 2"?

No. Assuming similar hardware, SQL Server should be just as slow as MySQL, since both would have to flush the commit for every row. Therefore there must be something else at play, very likely in the (not posted) C# code you use for your 'test'. Batch commit (a single xact spanning all 100 rows) would explain the behavior.

Note that SQL Server does have an equivalent of setting flush_log_at_trx_commit, namely Delayed durability. This is onyl available in SQL Server 2014 and forward, and must be explicitly configured.


The C# code, MySql and Sql Server are all running on my laptop.

Does your laptop do Write caching? If so, you must repeat your tests with the write caching off, as is not supported.

StackExchange Database Administrators Q#83858, answer score: 3