Why MySQL deadlock detection can severely degrade TPS

In a large number of clients, updating the same row of a data table results in a significant decrease in the throughput of the database.

Many of the database's predecessors and peers through the experiment and the source of methods, to locate the culprit----MySQL deadlock detection

Experimental method: http://blog.csdn.net/zhaiwx1987/article/details/6952285

SOURCE mode: http://www.gpfeng.com/?p=426

Please pay particular attention to this code

#####

Lock_mutex_enter ();

Ut_ad (Lock_table_has (Thr_get_trx (THR), index->table, Lock_ix));

Err = Lock_rec_lock (TRUE, lock_x | Lock_rec_not_gap,
block, Heap_no, index, THR);

Monitor_inc (Monitor_num_reclock_req);

Lock_mutex_exit ();

#####

As you can see, MySQL will have lock_mutex_enter and Lock_mutex_exit protection during the attempt to acquire the lock.

In general, Lock_rec_lock execution is fast, so it's not a problem.

However, if there is a large number of lock waits, such as multiple clients trying to update the same row, the process is very slow. Thus the entire INNODB layer is changed from parallel to serial, significantly reducing TPS.

Workaround:

Keep the lock waiting as small as possible by setting the wait queue at the database layer to achieve this effect, and onesql built-in this scenario.

Test Case:

Update Miaosha set mount=mount+1 where id=1;

Test environment

The key parameters for MySQL and Onesql are configured as follows and are not turned on Binlog

Database	Innodb_flush_log_at_trx_commit	Innodb_log_file_size	Innodb_buffer_pool_size
Onesql	1	1000M	8G
Mysql	1	1000M	8G

Hardware environment

Memory	Cpu	Disk
32g	8c two hyper-threading on each core Intel (R) Xeon (R) CPUs E5620 @ 2.40GHz	2 pieces of RAID0 7500r

Test results:

In the case of 512 threads, TPS is 500/s

I actually test, the same environment using ONESQL,TPS can achieve 15682/s, performance improvement of about 30 times times.

If you have any questions, please contact onesoft007

Why MySQL deadlock detection can severely degrade TPS