Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.4.19, 10.4.21
-
None
-
debian buster
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
- blocks
-
-
- Open
-