8.5.2 Optimizing InnoDB Transaction Management optimizing InnoDB Transaction Management

8.5.2 Optimizing InnoDB Transaction Management optimizing InnoDB Transaction Management

Optimize InnoDB transactions to find the perfect balance between performance overhead and server load. Like what

An application may experience performance problems if it submits thousands of times per second, with different performance issues if it is submitted every 2-3 hours.

Default MySQL settings autocommit=1 can leverage performance limitations. In practice, several related DML operations to a transaction, through the

Set autocommit=0 or a start TRANSACTION statement to execute a commit statement after making a change.

InnoDB must flush log to disk at the time of each transaction submission, if the transaction mode is changed to database.

When each change follows commit (default autocommit), I/O throughput of the storage device.

In addition, for transactions that consist of only a single SELECT statement, turning on autocommit helps InnoDB to identify read-only transactions and optimizes them.

Avoid rollback after insert,update or delete a large number of records, if a large transaction slows the performance of the server,

The rollback can make the problem worse, and the rollback may cost several times more than the normal DML operation time. Killing the database process is not helping,

Because server startup will roll back again.

To minimize the chance of this issue occurring:

1. Increase the size of the buffer pool so that all DML changes can be written to disk immediately compared to the cache

2. Set Innodb_change_buffering=all (default is All), update and delete operations will be buffered, in addition to inserts considered in the large DML operation, periodic execution commit, It is possible to break a single delete or update into multiple statements to manipulate fewer rows of data.

Once the runaway review takes place, increase the buffer_pool, roll back the Cpu-bound and runs fast,

or kill the server, restart settings innodb_force_recovery=3

This problem in MySQL 5.5 and the higher version is not prominent because of the default Innodb_change_buffering=all,

Allow update and delete operations to be cache in memory, so that they run faster in the first place, the rollback also becomes faster.

Make sure that when you use this parameter, there are a lot of inserts,updates or deletes in the process of running a transaction for a long time.

You can set up Innodb_flush_log_at_trx_commit =0 if you accept that the most recently committed transaction is lost when crash occurs.

InnoDB will flush log 1 seconds at a time, although flush is not mandatory, or set the value of Innodb_support_xa to 0

When the record is modified or deleted, the record and the associated undo logs are not physically deleted immediately,

Even after the transaction is committed. The old undo data is saved until the transaction begins or is completed concurrently.

These transactions can therefore access records that were previously modified or deleted. Therefore, long-running transactions can prevent InnoDB

Clear data, modified by a different transaction.

When a long transaction modifies or deletes a record, other transactions use the read commited and repeatable read isolation levels

More work needs to be done to refactor the old data (undo) If the same rows are read.