| 1 |
################################################################## |
| 2 |
# Test intended for provoking issues related to large transactions. |
| 3 |
# Includes, but not limited to: |
| 4 |
# - 'Job buffer full' due to commit/abort not limiting number of |
| 5 |
# outstanding signals. |
| 6 |
# - 'struck in thread', 'oversleept' and 'sleeploop 10!' due to |
| 7 |
# handling of many locks not scaling well. |
| 8 |
# |
| 9 |
################################################################## |
| 10 |
--source include/have_ndb.inc |
| 11 |
|
| 12 |
|
| 13 |
create table t1 (a int primary key) engine=ndb; |
| 14 |
create table t2 (a int primary key) engine=ndb; |
| 15 |
|
| 16 |
delimiter %; |
| 17 |
|
| 18 |
# Insert batches of 32 rows, just to speed up test |
| 19 |
create procedure do_insert(count int) |
| 20 |
begin |
| 21 |
set @x = 0; |
| 22 |
repeat |
| 23 |
insert into t1 values |
| 24 |
(@x+0), (@x+1), (@x+2), (@x+3), |
| 25 |
(@x+4), (@x+5), (@x+6), (@x+7), |
| 26 |
(@x+8), (@x+9), (@x+10), (@x+11), |
| 27 |
(@x+12), (@x+13), (@x+14), (@x+15), |
| 28 |
(@x+16), (@x+17), (@x+18), (@x+19), |
| 29 |
(@x+20), (@x+21), (@x+22), (@x+23), |
| 30 |
(@x+24), (@x+25), (@x+26), (@x+27), |
| 31 |
(@x+28), (@x+29), (@x+30), (@x+31); |
| 32 |
set @x = @x + 32; |
| 33 |
until @x >= count |
| 34 |
end repeat; |
| 35 |
end % |
| 36 |
|
| 37 |
|
| 38 |
delimiter ;% |
| 39 |
|
| 40 |
# Generate large transactions, abort or commit them: |
| 41 |
begin; |
| 42 |
call do_insert(500000); |
| 43 |
rollback; |
| 44 |
|
| 45 |
begin; |
| 46 |
call do_insert(500000); |
| 47 |
commit; |
| 48 |
|
| 49 |
begin; |
| 50 |
insert into t2 select * from t1; |
| 51 |
rollback; |
| 52 |
|
| 53 |
begin; |
| 54 |
insert into t2 select * from t1; |
| 55 |
commit; |
| 56 |
|
| 57 |
begin; |
| 58 |
delete from t2; |
| 59 |
commit; |
| 60 |
|
| 61 |
begin; |
| 62 |
delete from t1; |
| 63 |
commit; |
| 64 |
|
| 65 |
drop procedure do_insert; |
| 66 |
drop table t1,t2; |
| 67 |
|
| 68 |
--echo ############################## |
| 69 |
--echo # |
| 70 |
--echo # Bug#34189965 |
| 71 |
--echo # DbAcc Validate(WITH_DEBUG) of Unique Key locks has |
| 72 |
--echo # exponential overhead. |
| 73 |
--echo # |
| 74 |
--echo # In order to guarante read consistency when using an unique index, |
| 75 |
--echo # shared read locks are set (and held) on the rows. When e.g. a |
| 76 |
--echo # join query join in the *same* row multiple times, each access |
| 77 |
--echo # results in additional locks being set. Such locks are all added |
| 78 |
--echo # to the same 'parallel queue' the the ACC block, which can end up |
| 79 |
--echo # with thousands of locks in the same parallel queue. |
| 80 |
--echo # |
| 81 |
--echo # When debug compiled, ACC validates the consistency of the queues, |
| 82 |
--echo # iterating the entire queue. The overhead of that may become so |
| 83 |
--echo # huge that the block thread scheduler starts complaining about |
| 84 |
--echo # being 'struck in thread', 'oversleept' and 'sleeploop 10!' |
| 85 |
|
| 86 |
create table t ( |
| 87 |
pk int primary key, |
| 88 |
uq int, |
| 89 |
i int, |
| 90 |
unique key (uq), |
| 91 |
key(i) |
| 92 |
) engine = ndbcluster; |
| 93 |
|
| 94 |
create table t100000 ( |
| 95 |
i int |
| 96 |
) engine = ndbcluster; |
| 97 |
|
| 98 |
insert into t values |
| 99 |
(0,0,1), (1,1,1), (2,2,1), (3,3,1), (4,4,1), |
| 100 |
(5,5,1), (6,6,1), (7,7,1), (8,8,1), (9,9,1); |
| 101 |
|
| 102 |
# Fill with 100K rows |
| 103 |
insert into t100000 |
| 104 |
select t1.i from |
| 105 |
t as t1 |
| 106 |
straight_join t as t2 on t2.i=t1.i |
| 107 |
straight_join t as t3 on t3.i=t2.i |
| 108 |
straight_join t as t4 on t4.i=t3.i |
| 109 |
straight_join t as t5 on t5.i=t4.i; |
| 110 |
|
| 111 |
# join-pushdown do not set locks, turn it off |
| 112 |
set ndb_join_pushdown=off; |
| 113 |
|
| 114 |
# Will look up the same table t row 100k times. |
| 115 |
# Note that BKA join hint is just to speed up test case |
| 116 |
select /*+ BKA(t) */ count(*) from |
| 117 |
t100000 as t1 |
| 118 |
straight_join t on t.uq=t1.i; |
| 119 |
|
| 120 |
set ndb_join_pushdown=default; |
| 121 |
|
| 122 |
drop table t; |
| 123 |
drop table t100000; |