Some applications have a heavy write workload on a few records – for instance when incrementing a global counter: this is called a write hotspot. Because you cannot update the same row simultaneously from multiple threads, this can lead to performance degradation. When using Percona XtraDB Cluster (PXC), some users try to solve this specific issue by writing on multiple nodes at the same time. Good idea or bad idea? Read on!
Simultaneous writes on a standalone InnoDB server
Say you have these 3 transactions being run simultaneously (id is the primary key of the table):
# T1 UPDATE t SET ... WHERE id = 100 # T2 UPDATE t SET ... WHERE id = 100 # T3 UPDATE t SET ... WHERE id = 101
All transactions will require a row lock on the record they want to modify. So T3 can commit at the same time than T1 and/or T2, because it will …
[Read more]