Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-33099

Regression in select count query while large transaction keeps undo logs

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Incomplete
    • 10.4.31, 10.5.23, 10.11.6
    • N/A
    • Server
    • 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.

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-36472 10.6 History length regression

          • Major - Major loss of function.
          • Open

          Activity

            People

              axel Axel Schwenke
              przemek@mysqlmaniac.com Przemek
              Votes:
              6 Vote for this issue
              Watchers:
              12 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.