Skip to content

Commit 52d8473

Browse files
AliSQLAliSQL
AliSQL
authored and
AliSQL
committed
[perf] Issue#31 OPTIMIZE CHECK/GRANT OF INNODB TABLE LOCK
Description: ------------ Since most workload in production environment is normal DML/SELECT (this should be very common on most OLTP workload). So for InnoDB table lock: 1. most time there's only LOCK_IX/LOCK_IS table lock 2. LOCK_S/LOCK_X rarely happen This patch adds some counters to identify if LOCK_S/LOCK_X is granted and quickly exit function lock_table_other_has_incompatible(). This will improve the performance especially when all DML focused on the same table. For more deatail, please refer: http://bugs.mysql.com/bug.php?id=72948. This patch also fixes bug#68647 which reduce the overhead of 'SHOW ENGINE INNODB STATUS' and removes some complier warinings.
1 parent 44a1da5 commit 52d8473

9 files changed

Lines changed: 186 additions & 33 deletions

File tree

‎mysql-test/suite/innodb/r/perf_innodb_table_lock_issue31.result‎

Lines changed: 33 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,33 @@
1+
CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT, c3 INT, key(c1));
2+
BEGIN;
3+
INSERT INTO t1 VALUES (1,2,3),(2,3,4),(3,4,5),(5,6,7),(6,7,8);
4+
COMMIT;
5+
BEGIN;
6+
UPDATE t1 SET c3=c3+1 WHERE c1 = 2;
7+
BEGIN;
8+
UPDATE t1 SET c3=c3+1 WHERE c1 = 5;
9+
BEGIN;
10+
INSERT INTO t1 VALUES (7,8,9);
11+
COMMIT;
12+
COMMIT;
13+
COMMIT;
14+
BEGIN;
15+
UPDATE t1 SET c3=c3+1 WHERE c1 = 1;
16+
BEGIN;
17+
INSERT INTO t1 VALUES (8,9,10);
18+
UPDATE t1 SET c3=c3+1 WHERE c1 = 2;
19+
UPDATE t1 SET c3=c3+1 WHERE c1 = 6;
20+
UPDATE t1 SET c3=c3+1 WHERE c1 = 1;;
21+
UPDATE t1 SET c3=c3+1 WHERE c1 = 6;
22+
ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
23+
COMMIT;
24+
COMMIT;
25+
BEGIN;
26+
UPDATE t1 SET c3=c3+1 WHERE c1 = 2;
27+
UPDATE t1 SET c3=c3+1 WHERE c1 = 2;;
28+
COMMIT;
29+
DROP TABLE t1;
30+
CREATE TABLE t1 (a BLOB, b TEXT, PRIMARY KEY(a(1))) ENGINE=InnoDB;
31+
INSERT INTO t1 VALUES(REPEAT('a',8000),REPEAT('b',8000));
32+
UPDATE t1 SET a=REPEAT('a',7999);
33+
DROP TABLE t1;

‎mysql-test/suite/innodb/t/perf_innodb_table_lock_issue31.test‎

Lines changed: 78 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,78 @@
1+
--source include/have_innodb.inc
2+
--source include/have_debug.inc
3+
--source include/not_embedded.inc
4+
5+
connect(con1,localhost,root,,);
6+
connect(con2,localhost,root,,);
7+
connect(con3,localhost,root,,);
8+
9+
connection con1;
10+
CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT, c3 INT, key(c1));
11+
BEGIN;
12+
INSERT INTO t1 VALUES (1,2,3),(2,3,4),(3,4,5),(5,6,7),(6,7,8);
13+
COMMIT;
14+
15+
BEGIN;
16+
UPDATE t1 SET c3=c3+1 WHERE c1 = 2;
17+
18+
connection con2;
19+
BEGIN;
20+
UPDATE t1 SET c3=c3+1 WHERE c1 = 5;
21+
22+
connection con3;
23+
BEGIN;
24+
INSERT INTO t1 VALUES (7,8,9);
25+
26+
connection con1;
27+
COMMIT;
28+
connection con2;
29+
COMMIT;
30+
connection con3;
31+
COMMIT;
32+
33+
connection con1;
34+
BEGIN;
35+
UPDATE t1 SET c3=c3+1 WHERE c1 = 1;
36+
37+
connection con2;
38+
BEGIN;
39+
INSERT INTO t1 VALUES (8,9,10);
40+
UPDATE t1 SET c3=c3+1 WHERE c1 = 2;
41+
42+
UPDATE t1 SET c3=c3+1 WHERE c1 = 6;
43+
--send UPDATE t1 SET c3=c3+1 WHERE c1 = 1;
44+
45+
connection con1;
46+
--error 1213
47+
UPDATE t1 SET c3=c3+1 WHERE c1 = 6;
48+
COMMIT;
49+
50+
connection con2;
51+
reap;
52+
COMMIT;
53+
54+
connection con1;
55+
BEGIN;
56+
UPDATE t1 SET c3=c3+1 WHERE c1 = 2;
57+
58+
connection con2;
59+
--send UPDATE t1 SET c3=c3+1 WHERE c1 = 2;
60+
61+
connection con1;
62+
COMMIT;
63+
64+
connection con2;
65+
reap;
66+
67+
#for coverage.
68+
connection con1;
69+
DROP TABLE t1;
70+
CREATE TABLE t1 (a BLOB, b TEXT, PRIMARY KEY(a(1))) ENGINE=InnoDB;
71+
INSERT INTO t1 VALUES(REPEAT('a',8000),REPEAT('b',8000));
72+
UPDATE t1 SET a=REPEAT('a',7999);
73+
74+
#cleanup
75+
DROP TABLE t1;
76+
disconnect con1;
77+
disconnect con2;
78+
disconnect con3;

‎sql/binlog.h‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -38,7 +38,7 @@ class Stage_manager {
3838
friend class Stage_manager;
3939
public:
4040
Mutex_queue()
41-
: m_first(NULL), m_last(&m_first), m_last_thd(NULL), group_prepared_engine(NULL)
41+
: m_first(NULL), m_last_thd(NULL), m_last(&m_first), group_prepared_engine(NULL)
4242
{
4343
}
4444

‎sql/mysqld.h‎

Lines changed: 11 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -50,8 +50,15 @@ class engine_lsn_map {
5050
public:
5151
engine_lsn_map()
5252
{
53-
/* ALways contain trx engine, at least InnoDB.*/
54-
DBUG_ASSERT(global_trx_engine.size() != 0);
53+
/* If all trx engines are skipped, ignore memory allocating. */
54+
if (global_trx_engine.size() == 0)
55+
{
56+
m_count= 0;
57+
m_empty= true;
58+
maps= NULL;
59+
return;
60+
}
61+
5562
m_count= global_trx_engine.size();
5663
m_empty= true;
5764

@@ -79,7 +86,8 @@ class engine_lsn_map {
7986
maps[i]= NULL;
8087
}
8188

82-
my_free(maps);
89+
if (m_count)
90+
my_free(maps);
8391
}
8492

8593
lsn_map* get_map_by_type(int db_type)

‎storage/innobase/include/dict0mem.h‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1194,6 +1194,8 @@ struct dict_table_t{
11941194
UT_LIST_BASE_NODE_T(lock_t)
11951195
locks; /*!< list of locks on the table; protected
11961196
by lock_sys->mutex */
1197+
long lock_counter[LOCK_NUM];
1198+
/*!< lock counter array for each lock mode */
11971199
#endif /* !UNIV_HOTBACKUP */
11981200

11991201
#ifdef UNIV_DEBUG

‎storage/innobase/include/trx0trx.h‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -643,6 +643,7 @@ struct trx_lock_t {
643643
mutex to prevent recursive deadlocks.
644644
Protected by both the lock sys mutex
645645
and the trx_t::mutex. */
646+
long n_rec_locks; /*!< number of rec locks in this trx */
646647
};
647648

648649
#define TRX_MAGIC_N 91118598

‎storage/innobase/lock/lock0lock.cc‎

Lines changed: 50 additions & 21 deletions
Original file line numberDiff line numberDiff line change
@@ -1093,6 +1093,8 @@ lock_rec_set_nth_bit(
10931093
bit_index = i % 8;
10941094

10951095
((byte*) &lock[1])[byte_index] |= 1 << bit_index;
1096+
1097+
++lock->trx->lock.n_rec_locks;
10961098
}
10971099

10981100
/**********************************************************************//**
@@ -1140,6 +1142,10 @@ lock_rec_reset_nth_bit(
11401142
bit_index = i % 8;
11411143

11421144
((byte*) &lock[1])[byte_index] &= ~(1 << bit_index);
1145+
1146+
--lock->trx->lock.n_rec_locks;
1147+
1148+
ut_ad(lock->trx->lock.n_rec_locks >= 0);
11431149
}
11441150

11451151
/*********************************************************************//**
@@ -1750,28 +1756,9 @@ lock_number_of_rows_locked(
17501756
/*=======================*/
17511757
const trx_lock_t* trx_lock) /*!< in: transaction locks */
17521758
{
1753-
const lock_t* lock;
1754-
ulint n_records = 0;
1755-
17561759
ut_ad(lock_mutex_own());
17571760

1758-
for (lock = UT_LIST_GET_FIRST(trx_lock->trx_locks);
1759-
lock != NULL;
1760-
lock = UT_LIST_GET_NEXT(trx_locks, lock)) {
1761-
1762-
if (lock_get_type_low(lock) == LOCK_REC) {
1763-
ulint n_bit;
1764-
ulint n_bits = lock_rec_get_n_bits(lock);
1765-
1766-
for (n_bit = 0; n_bit < n_bits; n_bit++) {
1767-
if (lock_rec_get_nth_bit(lock, n_bit)) {
1768-
n_records++;
1769-
}
1770-
}
1771-
}
1772-
}
1773-
1774-
return(n_records);
1761+
return(trx_lock->n_rec_locks);
17751762
}
17761763

17771764
/*============== RECORD LOCK CREATION AND QUEUE MANAGEMENT =============*/
@@ -4076,10 +4063,14 @@ lock_table_create(
40764063
any locks. */
40774064
assert_trx_in_list(trx);
40784065

4079-
if ((type_mode & LOCK_MODE_MASK) == LOCK_AUTO_INC) {
4066+
ulint extract_mode = (type_mode & LOCK_MODE_MASK);
4067+
if (extract_mode == LOCK_AUTO_INC) {
4068+
40804069
++table->n_waiting_or_granted_auto_inc_locks;
40814070
}
40824071

4072+
table->lock_counter[extract_mode]++;
4073+
40834074
/* For AUTOINC locking we reuse the lock instance only if
40844075
there is no wait involved else we allocate the waiting lock
40854076
from the transaction lock heap. */
@@ -4245,6 +4236,9 @@ lock_table_remove_low(
42454236
UT_LIST_REMOVE(trx_locks, trx->lock.trx_locks, lock);
42464237
UT_LIST_REMOVE(un_member.tab_lock.locks, table->locks, lock);
42474238

4239+
table->lock_counter[lock_get_mode(lock)]--;
4240+
ut_ad(table->lock_counter[lock_get_mode(lock)] >= 0);
4241+
42484242
MONITOR_INC(MONITOR_TABLELOCK_REMOVED);
42494243
MONITOR_DEC(MONITOR_NUM_TABLELOCK);
42504244
}
@@ -4348,6 +4342,21 @@ lock_table_enqueue_waiting(
43484342
return(DB_LOCK_WAIT);
43494343
}
43504344

4345+
/*********************************************************************//**
4346+
Checks if there are table locks incompatible with current lock type.
4347+
@return true if compatible. */
4348+
static
4349+
my_bool
4350+
lock_table_compatible_fast_check(
4351+
/*=============================*/
4352+
enum lock_mode mode, /*!< in: lock mode */
4353+
const dict_table_t* table) /*!< in: table */
4354+
{
4355+
return ((mode == LOCK_IS || mode == LOCK_IX)
4356+
&& table->lock_counter[LOCK_S] == 0
4357+
&& table->lock_counter[LOCK_X] == 0);
4358+
}
4359+
43514360
/*********************************************************************//**
43524361
Checks if other transactions have an incompatible mode lock request in
43534362
the lock queue.
@@ -4368,6 +4377,11 @@ lock_table_other_has_incompatible(
43684377

43694378
ut_ad(lock_mutex_own());
43704379

4380+
if (lock_table_compatible_fast_check(mode, table)) {
4381+
4382+
return(NULL);
4383+
}
4384+
43714385
for (lock = UT_LIST_GET_LAST(table->locks);
43724386
lock != NULL;
43734387
lock = UT_LIST_GET_PREV(un_member.tab_lock.locks, lock)) {
@@ -4526,14 +4540,27 @@ lock_table_dequeue(
45264540
they are now qualified to it */
45274541
{
45284542
lock_t* lock;
4543+
dict_table_t* table;
4544+
enum lock_mode type = lock_get_mode(in_lock);
45294545

45304546
ut_ad(lock_mutex_own());
45314547
ut_a(lock_get_type_low(in_lock) == LOCK_TABLE);
45324548

45334549
lock = UT_LIST_GET_NEXT(un_member.tab_lock.locks, in_lock);
45344550

4551+
table = in_lock->un_member.tab_lock.table;
4552+
45354553
lock_table_remove_low(in_lock);
45364554

4555+
/* If there isn't any LOCK_S/LOCK_X request, and current lock type is
4556+
LOCK_IX/LOCK_IS, then we know we don't need to grant for any table
4557+
lock request because LOCK_IX/LOCK_IS only conflict with
4558+
LOCK_S/LOCK_X. */
4559+
if (lock_table_compatible_fast_check(type, table)) {
4560+
4561+
return;
4562+
}
4563+
45374564
/* Check if waiting locks in the queue can now be granted: grant
45384565
locks if there are no conflicting locks ahead. */
45394566

@@ -6885,6 +6912,8 @@ lock_trx_release_locks(
68856912

68866913
lock_release(trx);
68876914

6915+
trx->lock.n_rec_locks = 0;
6916+
68886917
lock_mutex_exit();
68896918
}
68906919

‎storage/innobase/trx/trx0trx.cc‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -289,6 +289,8 @@ trx_create(void)
289289
trx->lock.table_locks = ib_vector_create(
290290
heap_alloc, sizeof(void**), 32);
291291

292+
trx->lock.n_rec_locks = 0;
293+
292294
return(trx);
293295
}
294296

‎storage/myisam/ha_myisam.cc‎

Lines changed: 8 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -1675,7 +1675,7 @@ int ha_myisam::index_read_map(uchar *buf, const uchar *key,
16751675
rows_read++;
16761676

16771677
uint inx = (active_index == MAX_KEY) ? file->lastinx : active_index;
1678-
if (inx >= 0 && inx < MAX_KEY)
1678+
if (inx < MAX_KEY)
16791679
index_rows_read[inx]++;
16801680
}
16811681
return error;
@@ -1697,7 +1697,7 @@ int ha_myisam::index_read_idx_map(uchar *buf, uint index, const uchar *key,
16971697
rows_read++;
16981698

16991699
uint inx = (active_index == MAX_KEY) ? file->lastinx : active_index;
1700-
if (inx >= 0 && inx < MAX_KEY)
1700+
if (inx < MAX_KEY)
17011701
index_rows_read[inx]++;
17021702
}
17031703
return error;
@@ -1719,7 +1719,7 @@ int ha_myisam::index_read_last_map(uchar *buf, const uchar *key,
17191719
rows_read++;
17201720

17211721
uint inx = (active_index == MAX_KEY) ? file->lastinx : active_index;
1722-
if (inx >= 0 && inx < MAX_KEY)
1722+
if (inx < MAX_KEY)
17231723
index_rows_read[inx]++;
17241724
}
17251725
DBUG_RETURN(error);
@@ -1738,7 +1738,7 @@ int ha_myisam::index_next(uchar *buf)
17381738
rows_read++;
17391739

17401740
uint inx = (active_index == MAX_KEY) ? file->lastinx : active_index;
1741-
if (inx >= 0 && inx < MAX_KEY)
1741+
if (inx < MAX_KEY)
17421742
index_rows_read[inx]++;
17431743
}
17441744
return error;
@@ -1757,7 +1757,7 @@ int ha_myisam::index_prev(uchar *buf)
17571757
rows_read++;
17581758

17591759
uint inx = (active_index == MAX_KEY) ? file->lastinx : active_index;
1760-
if (inx >= 0 && inx < MAX_KEY)
1760+
if (inx < MAX_KEY)
17611761
index_rows_read[inx]++;
17621762
}
17631763
return error;
@@ -1776,7 +1776,7 @@ int ha_myisam::index_first(uchar *buf)
17761776
rows_read++;
17771777

17781778
uint inx = (active_index == MAX_KEY) ? file->lastinx : active_index;
1779-
if (inx >= 0 && inx < MAX_KEY)
1779+
if (inx < MAX_KEY)
17801780
index_rows_read[inx]++;
17811781
}
17821782
return error;
@@ -1795,7 +1795,7 @@ int ha_myisam::index_last(uchar *buf)
17951795
rows_read++;
17961796

17971797
uint inx = (active_index == MAX_KEY) ? file->lastinx : active_index;
1798-
if (inx >= 0 && inx < MAX_KEY)
1798+
if (inx < MAX_KEY)
17991799
index_rows_read[inx]++;
18001800
}
18011801
return error;
@@ -1820,7 +1820,7 @@ int ha_myisam::index_next_same(uchar *buf,
18201820
rows_read++;
18211821

18221822
uint inx = (active_index == MAX_KEY) ? file->lastinx : active_index;
1823-
if (inx >= 0 && inx < MAX_KEY)
1823+
if (inx < MAX_KEY)
18241824
index_rows_read[inx]++;
18251825
}
18261826
return error;

0 commit comments

Comments
 (0)