MySQL - Finalizing Transactions

MySQL & MariaDB Other MySQL® Questions
1

Hi All,
I am investigating about the performance of MySQL 5. But I found some strange behaviours while taking performance.
For insert/delete operation, it takes 1 or 2 milliseconds. But for commit or rollback, it is taking around 20 milliseconds. I am using the default settings. Can you please help me whether we need to change the settings or I am missing something.
Thanks in advance.

Rgds,
Rathish

2

Hi,

the data you provided is not very much… so here some general statements…

I guess you are using InnoDB, since you talk about transactions?

In standard mode you do not need commit a insert / delete, when you do not have started a transaction manually (Start Transaction / Begin).
(You might have disabled autocommit, but this is not standard)

Inserting many rows are fastest with INSERT INTO table (fieldlist) VALUES (value_list_1), (value_list_2) syntax…
Do not insert to many rows in the same statement. A statement size between 512k and 4M should be fine…

A commit should be faster than a rollback,… since MySQL is optimistic and aligens the data for a fast commit during the transaction. So a commit is only flagging the new data valid, while it has to rewrite the data for a rollback.

During a transaction data changes for insert / deletes can be cached. But at the point of the commit MySQL has to make sure the data is written probably… waiting for the OS to confirm the data and the log… has been written (flushed to disk) probably.