CODEWITHSUNDEEP
mysqlperformance
Uday
Uday

Reputation: 1490

What does these stats tell me?

I have run a test today. to see the variants for insert performance on an innodb table which is empty with source file having 10K records.

I used " mysql -uuname -pxxxxxx -Ddbanme < insert_file.sql "

Here are the different stats that came out.

    BINARY LOGGING IS ENABLED
    innodb_flush_log_trx_commit = 1     Time: 25 minutes
    innodb_flush_log_trx_commit = 2     Time: 4 seconds

    BINARY LOGGING IS DISABLED
    innodb_flush_log_trx_commit = 1     Time: 6 minutes
    innodb_flush_log_trx_commit = 2     Time: 3 seconds

I am not sure on what to understand from this. Please share your thoughts on this test details.

Upvotes: 0

Views: 114

Answers (1)

Aleksandar Vucetic
Aleksandar Vucetic

Reputation: 14953

Take a look here you will see that when innodb_flush_log_trx_commit is 1 MySQL writes to flush log more often. As you can see in the documentation, it has to be 1 in order to achieve ACID compliance. In your case, when importing data from file, if you have value 1 and something breaks, your whole transaction will fail (table will stay empty), with value of 2, if something breaks, your table will contain records that succeeded (before import failed).

Regarding BINARY LOGGING, when there is no binary logging your insert works faster. You don't need binary logging if you don't have slave servers and don't want to replicate commands from your master server, so you can turn it off since that will increase your performance.

Upvotes: 2

Related Questions