Skip to content

Instantly share code, notes, and snippets.

@mysqlboy

mysqlboy/schema-change.md

Created October 10, 2022 06:50
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save mysqlboy/8c53fcec73069961f2187ad707dc550b to your computer and use it in GitHub Desktop.
Save mysqlboy/8c53fcec73069961f2187ad707dc550b to your computer and use it in GitHub Desktop.
Download ZIP
KL Schema Change Request
Raw
schema-change.md

Schema Change

Review:

Dimensions of table to be altered:

+------------------+----------+----------+--------------+
| tbl              | Size_MB  | rows_mil | data_free_MB |
+------------------+----------+----------+--------------+
| cheateo.bot_logs | 56741.95 |  25.4484 |      1255.00 |
+------------------+----------+----------+--------------+

DDL Command provided

ALTER TABLE bot_logs CHANGE height height DOUBLE PRECISION DEFAULT '0' NOT NULL

Online Schema change command

pt-online-schema-change \
--alter="CHANGE height height DOUBLE PRECISION DEFAULT '0' NOT NULL" \
--recursion-method=NONE \
--alter-foreign-keys-method=auto \
--set-vars innodb_lock_wait_timeout=2 \
--critical-load Threads_running=200 \
D=cheateo,t=bot_logs \
--dry-run
bot_logs pre schema change

cheateo.bot_logs

MariaDB [cheateo]> show create table bot_logs\G
*************************** 1. row ***************************
       Table: bot_logs
Create Table: CREATE TABLE `bot_logs` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` mediumint(8) unsigned NOT NULL,
  `project_id` mediumint(8) unsigned NOT NULL,
  `bot_id` mediumint(8) unsigned NOT NULL,
  `state` enum('WAITING','IN_QUEUE','RUNNING','FINISHED','ERROR','EXCEPTION') NOT NULL DEFAULT 'WAITING',
  `tries` int(11) NOT NULL DEFAULT 0,
  `origin` enum('DEMO','DEMO-SCREENSHOT','MANUAL','AUTOMATIC','API') DEFAULT NULL,
  `type` varchar(255) NOT NULL,
  `debug` tinyint(1) NOT NULL DEFAULT 0,
  `params` text NOT NULL,
  `result` text NOT NULL,
  `result_raw` text NOT NULL,
  `height` tinyint(4) unsigned NOT NULL DEFAULT 0,
  `execute_time` smallint(6) NOT NULL DEFAULT 0,
  `data_length` int(11) NOT NULL DEFAULT 0,
  `data_length_encoded` int(11) NOT NULL DEFAULT 0,
  `data_length_encoded_lf` int(11) NOT NULL DEFAULT 0,
  `server` varchar(255) NOT NULL,
  `ended_at` timestamp NULL DEFAULT NULL,
  `ended_at_date` date DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT '1970-01-01 06:00:01',
  `updated_at` timestamp NOT NULL DEFAULT '1970-01-01 06:00:01',
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id_origin_state` (`user_id`,`origin`,`state`),
  KEY `state` (`state`),
  KEY `user_state_ended_at_date_index` (`user_id`,`state`,`ended_at_date`),
  KEY `bot_state_ended_at_date` (`bot_id`,`state`,`ended_at_date`),
  KEY `ended_at_date` (`ended_at_date`),
  KEY `project_state_ended_at_date_index` (`project_id`,`state`,`ended_at_date`),
  KEY `state_ended_at` (`state`,`ended_at`),
  KEY `ended_at` (`ended_at`),
  KEY `project_id_origin_state` (`project_id`,`origin`,`state`),
  KEY `bot_id_origin_state` (`bot_id`,`origin`,`state`),
  KEY `state_server` (`state`,`server`),
  KEY `state_created_at` (`state`,`created_at`)
) ENGINE=InnoDB AUTO_INCREMENT=970731347 DEFAULT CHARSET=latin1
1 row in set (0.000 sec)
bot_logs post schema change

cheateo.bot_logs

MariaDB [cheateo]> show create table bot_logs\G
*************************** 1. row ***************************
       Table: bot_logs
CREATE TABLE `bot_logs` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` mediumint(8) unsigned NOT NULL,
  `project_id` mediumint(8) unsigned NOT NULL,
  `bot_id` mediumint(8) unsigned NOT NULL,
  `state` enum('WAITING','IN_QUEUE','RUNNING','FINISHED','ERROR','EXCEPTION') NOT NULL DEFAULT 'WAITING',
  `tries` int(11) NOT NULL DEFAULT 0,
  `origin` enum('DEMO','DEMO-SCREENSHOT','MANUAL','AUTOMATIC','API') DEFAULT NULL,
  `type` varchar(255) NOT NULL,
  `debug` tinyint(1) NOT NULL DEFAULT 0,
  `params` text NOT NULL,
  `result` text NOT NULL,
  `result_raw` text NOT NULL,
  `height` double NOT NULL DEFAULT 0,
  `execute_time` smallint(6) NOT NULL DEFAULT 0,
  `data_length` int(11) NOT NULL DEFAULT 0,
  `data_length_encoded` int(11) NOT NULL DEFAULT 0,
  `data_length_encoded_lf` int(11) NOT NULL DEFAULT 0,
  `server` varchar(255) NOT NULL,
  `ended_at` timestamp NULL DEFAULT NULL,
  `ended_at_date` date DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT '1970-01-01 06:00:01',
  `updated_at` timestamp NOT NULL DEFAULT '1970-01-01 06:00:01',
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id_origin_state` (`user_id`,`origin`,`state`),
  KEY `state` (`state`),
  KEY `user_state_ended_at_date_index` (`user_id`,`state`,`ended_at_date`),
  KEY `bot_state_ended_at_date` (`bot_id`,`state`,`ended_at_date`),
  KEY `ended_at_date` (`ended_at_date`),
  KEY `project_state_ended_at_date_index` (`project_id`,`state`,`ended_at_date`),
  KEY `state_ended_at` (`state`,`ended_at`),
  KEY `ended_at` (`ended_at`),
  KEY `project_id_origin_state` (`project_id`,`origin`,`state`),
  KEY `bot_id_origin_state` (`bot_id`,`origin`,`state`),
  KEY `state_server` (`state`,`server`),
  KEY `state_created_at` (`state`,`created_at`)
) ENGINE=InnoDB AUTO_INCREMENT=970864943
bot_logs schema change process output

pt-online-schema-change

No slaves found.  See --recursion-method if host bt-services has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
No foreign keys reference `cheateo`.`bot_logs`; ignoring --alter-foreign-keys-method.
Altering `cheateo`.`bot_logs`...
Creating new table...
Created new table cheateo._bot_logs_new OK.
Altering new table...
Altered `cheateo`.`_bot_logs_new` OK.
2022-10-10T00:16:11 Creating triggers...
2022-10-10T00:16:11 Created triggers OK.
2022-10-10T00:16:11 Copying approximately 25079052 rows...
Copying `cheateo`.`bot_logs`:   2% 21:54 remain
Copying `cheateo`.`bot_logs`:   3% 30:02 remain
Copying `cheateo`.`bot_logs`:   4% 33:44 remain
Copying `cheateo`.`bot_logs`:   5% 36:02 remain
...
Copying `cheateo`.`bot_logs`:  95% 03:14 remain
Copying `cheateo`.`bot_logs`:  96% 02:53 remain
Copying `cheateo`.`bot_logs`:  97% 01:45 remain
Copying `cheateo`.`bot_logs`:  98% 01:21 remain
Copying `cheateo`.`bot_logs`:  99% 00:07 remain
2022-10-10T01:39:59 Copied rows OK.
2022-10-10T01:39:59 Analyzing new table...
2022-10-10T01:39:59 Swapping tables...
2022-10-10T01:44:57 Swapped original and new tables OK.
2022-10-10T01:44:57 Dropping old table...
2022-10-10T01:45:00 Dropped old table `cheateo`.`_bot_logs_old` OK.
2022-10-10T01:45:00 Dropping triggers...
2022-10-10T01:45:00 Dropped triggers OK.
Successfully altered `cheateo`.`bot_logs`.

Dimensions of table after alter:

+------------------+----------+----------+--------------+
| tbl              | Size_MB  | rows_mil | data_free_MB |
+------------------+----------+----------+--------------+
| cheateo.bot_logs | 33372.23 |  25.4484 |         6.00 |
+------------------+----------+----------+--------------+
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment