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

OPTIMIZE table not replicating

    XMLWordPrintable

    Details

      Description

      We are trying to optimize a table which is around 50GB and that after the optimization should reduce to around 14GB.

      We have noticed that running the optimize on the master (10.4.19+STATEMENT or 10.4.21) it doesn't get written on the binlog and hence it doesn't get replicated to any of the slaves.

      	=== db2123 (s5 codfw master): ===
       
      mysql:root@localhost [dewiki]> alter table logging engine=InnoDB;
      Query OK, 0 rows affected (8 min 47.942 sec)
      Records: 0  Duplicates: 0  Warnings: 0
       
      mysql:root@localhost [dewiki]> optimize table logging;
      +----------------+----------+----------+-------------------------------------------------------------------+
      | Table          | Op       | Msg_type | Msg_text                                                          |
      +----------------+----------+----------+-------------------------------------------------------------------+
      | dewiki.logging | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
      | dewiki.logging | optimize | status   | OK                                                                |
      +----------------+----------+----------+-------------------------------------------------------------------+
      2 rows in set (8 min 31.735 sec)
       
      root@db2123:/srv/sqldata# mysqlbinlog --base64-output=DECODE-ROWS db2123-bin.001717 | grep "optimize table"
      root@db2123:/srv/sqldata# mysqlbinlog --base64-output=DECODE-ROWS db2123-bin.001717 | grep "alter table"
      alter table logging engine=InnoDB
      root@db2123:/srv/sqldata#
       
      === db2111 (s5 codfw slave - happens everywhere): === 
       
      root@db2111:/srv/sqldata#  mysqlbinlog -vvv   db2111-relay-bin.001406 | grep "alter table"
      alter table logging engine=InnoDB
      root@db2111:/srv/sqldata#  mysqlbinlog -vvv   db2111-relay-bin.001406 | grep "optimize table"
      root@db2111:/srv/sqldata#
      

      Using alter table engine=InnoDB does get replicated.

      This is a different host (runs ROW based):

      mysql:root@localhost [dewiki]> optimize table logging;
      +----------------+----------+----------+-------------------------------------------------------------------+
      | Table          | Op       | Msg_type | Msg_text                                                          |
      +----------------+----------+----------+-------------------------------------------------------------------+
      | dewiki.logging | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
      | dewiki.logging | optimize | status   | OK                                                                |
      +----------------+----------+----------+-------------------------------------------------------------------+
      2 rows in set (13 min 12.645 sec)
       
      mysql:root@localhost [dewiki]> alter table logging engine=InnoDB ROW_FORMAT=COMPRESSED;
      Query OK, 0 rows affected (13 min 6.271 sec)
      Records: 0  Duplicates: 0  Warnings: 0
       
      root@db1154:/srv/sqldata.s5#  mysqlbinlog -vvv --base64-output=DECODE-ROWS db1154-bin.000430 | grep -i "alter table"
      alter table logging engine=InnoDB ROW_FORMAT=COMPRESSED
       
      root@db1154:/srv/sqldata.s5#  mysqlbinlog -vvv --base64-output=DECODE-ROWS db1154-bin.000430 | grep -i "optimize table"
      root@db1154:/srv/sqldata.s5#
      

      We have seen this on 10.4.19 and 10.4.21
      Both hosts are intermediate masters

      Table definition (happens with both compressed and non compressed version of this table):

      *************************** 1. row ***************************
             Table: logging
      Create Table: CREATE TABLE `logging` (
        `log_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
        `log_type` varbinary(32) NOT NULL DEFAULT '',
        `log_action` varbinary(32) NOT NULL DEFAULT '',
        `log_timestamp` varbinary(14) NOT NULL DEFAULT '19700101000000',
        `log_namespace` int(11) NOT NULL DEFAULT 0,
        `log_title` varbinary(255) NOT NULL DEFAULT '',
        `log_comment_id` bigint(20) unsigned NOT NULL,
        `log_params` blob NOT NULL,
        `log_deleted` tinyint(3) unsigned NOT NULL DEFAULT 0,
        `log_actor` bigint(20) unsigned NOT NULL,
        `log_page` int(10) unsigned DEFAULT NULL,
        PRIMARY KEY (`log_id`),
        KEY `type_time` (`log_type`,`log_timestamp`),
        KEY `page_time` (`log_namespace`,`log_title`,`log_timestamp`),
        KEY `times` (`log_timestamp`),
        KEY `log_page_id_time` (`log_page`,`log_timestamp`),
        KEY `actor_time` (`log_actor`,`log_timestamp`),
        KEY `log_actor_type_time` (`log_actor`,`log_type`,`log_timestamp`),
        KEY `log_type_action` (`log_type`,`log_action`,`log_timestamp`)
      ) ENGINE=InnoDB AUTO_INCREMENT=133843953 DEFAULT CHARSET=binary
      

      Server variables: https://phabricator.wikimedia.org/P17279

      All this made me doubt if OPTIMIZE (or even ANALYZE) gets replicated by default and I double checked it does:

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              monty Michael Widenius
              Reporter:
              marostegui Manuel Arostegui
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

                Created:
                Updated:

                  Git Integration