|
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.
|