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

ALTER TABLE...ALGORITHM=COPY should build indexes more efficiently

Details

    Description

      As noted in MDEV-26740, ALTER TABLE...ALGORITHM=COPY fails to make use of the MDEV-24621 optimization to pre-sort each index and to build them page by page.

      I tested this on MariaDB Server 11.2, which implements MDEV-16329:

      ./mtr --rr main.alter_table_online_debug,nobinlog

      I set a breakpoint inside ha_innobase::extra(HA_EXTRA_END_ALTER_COPY) and started to debug its surroundings:

      break ha_innodb.cc:15692
      		 
      continue
      		 
      break ha_innobase::write_row

      For the first statement that hits the first breakpoint (alter table t1 add b int NULL, algorithm= copy, lock= none), we can see that the row-level undo logging is being disabled as expected (MDEV-11415). But, we can also see that the initial copying phase is using the regular row-by-row-insert API if we set a data watchpoint on FIL_PAGE_LSN in the clustered index root page:

      11.2 96250c82691169921a38a38bc24910294294eb24

      		 
      #2  0x0000560582646449 in mtr_t::commit (this=...) at /mariadb/11/storage/innobase/mtr/mtr0mtr.cc:438
      		 
      #3  0x00005605826a68e8 in row_ins_clust_index_entry_low (flags=..., mode=..., index=..., n_uniq=..., entry=..., n_ext=..., thr=...) at /mariadb/11/storage/innobase/row/row0ins.cc:2895
      		 
      #4  0x00005605826a6bca in row_ins_clust_index_entry (index=..., entry=..., thr=..., n_ext=...) at /mariadb/11/storage/innobase/row/row0ins.cc:3243
      		 
      #5  0x00005605826a6e84 in row_ins_index_entry (index=..., entry=..., thr=...) at /mariadb/11/storage/innobase/row/row0ins.cc:3375
      		 
      #6  0x00005605826a6f50 in row_ins_index_entry_step (node=..., thr=...) at /mariadb/11/storage/innobase/row/row0ins.cc:3543
      		 
      #7  0x00005605826a71a0 in row_ins (node=..., thr=...) at /mariadb/11/storage/innobase/row/row0ins.cc:3660
      		 
      #8  0x00005605826a73d9 in row_ins_step (thr=...) at /mariadb/11/storage/innobase/row/row0ins.cc:3789
      		 
      #9  0x00005605826bd2a9 in row_insert_for_mysql (mysql_rec=..., prebuilt=..., ins_mode=...) at /mariadb/11/storage/innobase/row/row0mysql.cc:1314
      		 
      #10 0x00005605824d641b in ha_innobase::write_row (this=..., record=...) at /mariadb/11/storage/innobase/handler/ha_innodb.cc:7847
      		 
      #11 0x0000560581ff2a35 in handler::ha_write_row (this=..., buf=...) at /mariadb/11/sql/handler.cc:7852
      		 
      #12 0x00005605822adb11 in copy_data_between_tables (thd=..., from=..., to=..., ignore=..., order_num=..., order=..., copied=..., deleted=..., alter_info=..., alter_ctx=..., online=..., start_alter_id=...)
      		 
          at /mariadb/11/sql/sql_table.cc:12186
      		 
      #13 0x00005605822b0fce in mysql_alter_table (thd=..., new_db=..., new_name=..., create_info=..., table_list=..., recreate_info=..., alter_info=..., order_num=..., order=..., ignore=..., if_exists=...)
      		 
          at /mariadb/11/sql/sql_table.cc:11335
      		 
      #14 0x0000560582324b53 in Sql_cmd_alter_table::execute (this=..., thd=...) at /mariadb/11/sql/sql_alter.cc:701
      		 
      #15 0x00005605821eb5c3 in mysql_execute_command (thd=..., is_called_from_prepared_stmt=...) at /mariadb/11/sql/sql_parse.cc:5777
      		 
      #16 0x00005605821ec175 in mysql_parse (thd=..., rawbuf=..., length=..., parser_state=...) at /mariadb/11/sql/sql_parse.cc:7808

      Because this code path provides a work-around for MDEV-26740, we had better fix that bug before we can implement this optimization.

      It would be good to test the fix on 11.2 before applying it to the earliest supported version (10.11), because MDEV-16329 is extending the function copy_data_between_tables() with a call to online_alter_read_from_binlog().

      Attachments

        Issue Links

          causes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-34756 Validation of new foreign key skipped if innodb_alter_copy_bulk=ON

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-35237 Bulk insert fails to apply buffered operation during CREATE..SELECT statment

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-35386 Assertion `curr <= last' failed in rec_offs_validate

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-35475 Assertion `!rec_offs_nth_extern(offsets1, n)' failed in cmp_rec_rec_simple_field

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed
          is blocked by

          Task - A task that needs to be done. MDEV-24621 In bulk insert, pre-sort and build indexes one page at a time

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26740 Inplace alter rebuild increases file size

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-33809 Bulk insert or DDL fails if a BLOB is too long

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed
          relates to

          Task - A task that needs to be done. MDEV-16356 Allow ALGORITHM=NOCOPY for ADD CONSTRAINT

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-35163 InnoDB persistent statistics fail to update after ALTER TABLE...ALGORITHM=COPY

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-33094 row-by-row logging needs to be disabled for the online ALTER log application stage

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-33329 ALTER TABLE...FORCE fails to recalculate generated stored columns

          • Major - Major loss of function.
          • Open

          Activity

            Ascending order - Click to sort in descending order
            marko Marko Mäkelä created issue -
            marko Marko Mäkelä made changes -
            Field Original Value New Value
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            Status Open [ 1 ] Confirmed [ 10101 ]
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            thiru Thirunarayanan Balathandayuthapani made changes -
            Status Confirmed [ 10101 ] In Progress [ 3 ]
            thiru Thirunarayanan Balathandayuthapani made changes -
            Assignee Thirunarayanan Balathandayuthapani [ thiru ] Marko Mäkelä [ marko ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            Assignee Marko Mäkelä [ marko ] Thirunarayanan Balathandayuthapani [ thiru ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            thiru Thirunarayanan Balathandayuthapani made changes -
            Status Stalled [ 10000 ] In Progress [ 3 ]
            julien.fritsch Julien Fritsch made changes -
            Fix Version/s 11.0 [ 28320 ]
            Fix Version/s 11.3 [ 28565 ]
            thiru Thirunarayanan Balathandayuthapani made changes -
            Assignee Thirunarayanan Balathandayuthapani [ thiru ] Marko Mäkelä [ marko ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            marko Marko Mäkelä made changes -
            Assignee Marko Mäkelä [ marko ] Thirunarayanan Balathandayuthapani [ thiru ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            mariadb-jira-automation Jira Automation (IT) made changes -
            Zendesk Related Tickets 201933
            Zendesk active tickets 201933
            thiru Thirunarayanan Balathandayuthapani made changes -
            Fix Version/s 10.11.9 [ 29834 ]
            Fix Version/s 10.11 [ 27614 ]
            Fix Version/s 11.1 [ 28549 ]
            Fix Version/s 11.2 [ 28603 ]
            Resolution Fixed [ 1 ]
            Status Stalled [ 10000 ] Closed [ 6 ]
            JIraAutomate JiraAutomate made changes -
            Fix Version/s 11.1.6 [ 29835 ]
            Fix Version/s 11.2.5 [ 29836 ]
            Fix Version/s 11.4.3 [ 29837 ]
            evanelias Evan Elias made changes -
            marko Marko Mäkelä made changes -
            alice Alice Sherepa made changes -
            alice Alice Sherepa made changes -
            alice Alice Sherepa made changes -
            marko Marko Mäkelä made changes -
            elenst Elena Stepanova made changes -

            People

              thiru Thirunarayanan Balathandayuthapani
              marko Marko Mäkelä
              Votes:
              1 Vote for this issue
              Watchers:
              7 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.