Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.4.31, 10.5.23, 10.11.6
-
None
Description
There was apparently some regression introduced between versions 10.4.21 and 10.4.31, as the same test shows a dramatic decrease in SELECT COUNT(*) performance when a big enough transaction is active with uncommitted row changes.
How to reproduce:
– prepare 10M sysbench table
– start trx and delete random 4k rows
– run select count(*)
Example runs for various versions:
$ cat test_count_in_trx.sql
|
|
|
use db1 |
select count(*) from sbtest1; |
|
|
start transaction; |
DELETE FROM sbtest1 ORDER BY RAND() LIMIT 4000; |
select count(*) from sbtest1; |
rollback; |
MariaDB 10.4.21
$ msb_10_4_21/use -vvv < test_count_in_trx.sql |
--------------
|
select count(*) from sbtest1 |
--------------
|
|
|
+----------+ |
| count(*) | |
+----------+ |
| 10000000 |
|
+----------+ |
1 row in set (2.070 sec) |
|
|
--------------
|
start transaction |
--------------
|
|
|
Query OK, 0 rows affected (0.000 sec) |
|
|
--------------
|
DELETE FROM sbtest1 ORDER BY RAND() LIMIT 4000 |
--------------
|
|
|
Query OK, 4000 rows affected (12.723 sec) |
|
|
--------------
|
select count(*) from sbtest1 |
--------------
|
|
|
+----------+ |
| count(*) | |
+----------+ |
| 9996000 |
|
+----------+ |
1 row in set (53.875 sec) |
|
|
--------------
|
rollback
|
--------------
|
|
|
Query OK, 0 rows affected (0.451 sec) |
MariaDB 10.4.31
$ msb_10_4_31/use db1 -vvv < test_count_in_trx.sql |
--------------
|
select count(*) from sbtest1 |
--------------
|
|
|
+----------+ |
| count(*) | |
+----------+ |
| 10000000 |
|
+----------+ |
1 row in set (1.959 sec) |
|
|
--------------
|
start transaction |
--------------
|
|
|
Query OK, 0 rows affected (0.000 sec) |
|
|
--------------
|
DELETE FROM sbtest1 ORDER BY RAND() LIMIT 4000 |
--------------
|
|
|
Query OK, 4000 rows affected (11.911 sec) |
|
|
--------------
|
select count(*) from sbtest1 |
--------------
|
|
|
+----------+ |
| count(*) | |
+----------+ |
| 9996000 |
|
+----------+ |
1 row in set (11 min 41.352 sec) |
|
|
--------------
|
rollback
|
--------------
|
|
|
Query OK, 0 rows affected (1.270 sec) |
MariaDB 10.5.23
$ msb_10_5_23/use db1 -vvv < test_count_in_trx.sql |
--------------
|
select count(*) from sbtest1 |
--------------
|
|
|
+----------+ |
| count(*) | |
+----------+ |
| 10000000 |
|
+----------+ |
1 row in set (1.940 sec) |
|
|
--------------
|
start transaction |
--------------
|
|
|
Query OK, 0 rows affected (0.000 sec) |
|
|
--------------
|
DELETE FROM sbtest1 ORDER BY RAND() LIMIT 4000 |
--------------
|
|
|
Query OK, 4000 rows affected (13.320 sec) |
|
|
--------------
|
select count(*) from sbtest1 |
--------------
|
|
|
1 row in set (13 min 14.588 sec) |
|
|
--------------
|
rollback
|
--------------
|
|
|
Query OK, 0 rows affected (1.370 sec) |
MySQL 8.0.35
$ msb_8_0_35/use db1 -vvv < test_count_in_trx.sql |
--------------
|
select count(*) from sbtest1 |
--------------
|
|
|
+----------+ |
| count(*) | |
+----------+ |
| 10000000 |
|
+----------+ |
1 row in set (7.17 sec) |
|
|
--------------
|
start transaction |
--------------
|
|
|
Query OK, 0 rows affected (0.00 sec) |
|
|
--------------
|
DELETE FROM sbtest1 ORDER BY RAND() LIMIT 4000 |
--------------
|
|
|
Query OK, 4000 rows affected (14.78 sec) |
|
|
--------------
|
select count(*) from sbtest1 |
--------------
|
|
|
+----------+ |
| count(*) | |
+----------+ |
| 9996000 |
|
+----------+ |
1 row in set (9.46 sec) |
|
|
--------------
|
rollback
|
--------------
|
|
|
Query OK, 0 rows affected (2.50 sec) |
MySQL 5.7.44
$ msb_5_7_44/use -vvv < test_count_in_trx.sql |
--------------
|
select count(*) from sbtest1 |
--------------
|
|
|
+----------+ |
| count(*) | |
+----------+ |
| 10000000 |
|
+----------+ |
1 row in set (2.17 sec) |
|
|
--------------
|
start transaction |
--------------
|
|
|
Query OK, 0 rows affected (0.00 sec) |
|
|
--------------
|
DELETE FROM sbtest1 ORDER BY RAND() LIMIT 4000 |
--------------
|
|
|
Query OK, 4000 rows affected (15.24 sec) |
|
|
--------------
|
select count(*) from sbtest1 |
--------------
|
|
|
+----------+ |
| count(*) | |
+----------+ |
| 9996000 |
|
+----------+ |
1 row in set (8.08 sec) |
|
|
--------------
|
rollback
|
--------------
|
|
|
Query OK, 0 rows affected (1.27 sec) |
Ran all tests with default settings, i.e., buffer pool at 128MB.