mysql table get lock on adding new field

Question

We want to add new field into table but table got lot its take 3 min on stage env but in production its taking more then 10 min(we killed the process after this)

ALTER TABLE `posts_topic` ADD COLUMN `hindi_description` longtext NULL, LOCK=NONE;

SHOW FULL PROCESSLIST;
+---------+-----------------+--------------------+---------------+---------+---------+---------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Id      | User            | Host               | db            | Command | Time    | State                           | Info                                                                                                                                                                                                                                                                          |
+---------+-----------------+--------------------+---------------+---------+---------+---------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|       5 | event_scheduler | localhost          | NULL          | Daemon  | 6044590 | Waiting on empty queue          | NULL                                                                                                                                                                                                                                                                          |
| 1511667 | admin           | 172.31.32.92:52604 | orchestra_cms | Sleep   |    1221 |                                 | NULL                                                                                                                                                                                                                                                                          |
| 1589284 | rdsadmin        | localhost          | NULL          | Sleep   |      11 |                                 | NULL                                                                                                                                                                                                                                                                          |
| 1678479 | admin           | 172.31.32.92:47982 | orchestra_cms | Sleep   |    8465 |                                 | NULL                                                                                                                                                                                                                                                                          |
| 1680873 | admin           | 172.31.32.92:60246 | orchestra_cms | Query   |     286 | Waiting for table metadata lock | ALTER TABLE `posts_topic` ADD COLUMN `hindi_description_11` longtext NULL, LOCK=NONE                                                                                                                                                                                          |
| 1680907 | admin           | 172.31.32.92:51524 | orchestra_cms | Query   |       0 | init                            | SHOW FULL PROCESSLIST                                                                                                                                                                                                                                                         |
| 1681014 | admin           | 172.31.32.92:51602 | orchestra_cms | Query   |     135 | Waiting for table metadata lock | SELECT (1) AS `a` FROM `posts_posttopic` INNER JOIN `posts_topic` ON (`posts_posttopic`.`topic_id` = `posts_topic`.`id`) WHERE (NOT (`posts_posttopic`.`object_status` = 0) AND `posts_posttopic`.`post_id` = 'kuqDLdvyodbd' AND `posts_topic`.`name` LIKE 'TOPNEWS') LIMIT 1 |
| 1681019 | admin           | 172.31.32.92:43246 | orchestra_cms | Sleep   |     135 |                                 | NULL                                                                                                                                                                                                                                                                          |
+---------+-----------------+--------------------+---------------+---------+---------+---------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
SHOW ENGINE INNODB STATUS;
------------
TRANSACTIONS
------------
Trx id counter 13578859
Purge done for trx's n:o < 13578454 undo n:o < 0 state: running but idle
History list length 121
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 304207347323672, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 304207347325288, not started
mysql tables in use 1, locked 1
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 304207347322864, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 304207347322056, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 304207347321248, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 304207347320440, not started
0 lock struct(s), heap size 1128, 0 row lock(s)'

SHOW OPEN TABLES WHERE  In_use > 0;
+---------------+-------------+--------+-------------+
| Database      | Table       | In_use | Name_locked |
+---------------+-------------+--------+-------------+
| orchxxxxx_xxx | posts_topic |      1 |           0 |
+---------------+-------------+--------+-------------+

CREATE TABLE `posts_posttopic` (
  `id` int NOT NULL AUTO_INCREMENT,
  `object_status` smallint NOT NULL,
  `leaning` smallint unsigned DEFAULT NULL,
  `leaning_direction` smallint unsigned NOT NULL,
  `created_on` datetime(6) NOT NULL,
  `post_id` varchar(15) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `topic_id` varchar(15) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `posts_posttopic_post_id_73ce25a9_fk_posts_post_id` (`post_id`),
  KEY `posts_posttopic_topic_id_04b92641_fk_posts_topic_id` (`topic_id`),
  CONSTRAINT `posts_posttopic_post_id_73ce25a9_fk_posts_post_id` FOREIGN KEY (`post_id`) REFERENCES `posts_post` (`id`),
  CONSTRAINT `posts_posttopic_topic_id_04b92641_fk_posts_topic_id` FOREIGN KEY (`topic_id`) REFERENCES `posts_topic` (`id`),
  CONSTRAINT `posts_posttopic_chk_1` CHECK ((`leaning` >= 0)),
  CONSTRAINT `posts_posttopic_chk_2` CHECK ((`leaning_direction` >= 0))
) ENGINE=InnoDB AUTO_INCREMENT=1945751 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

 CREATE TABLE `posts_topic` (
  `object_status` smallint NOT NULL,
  `id` varchar(15) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `post_count` int NOT NULL,
  `display_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `slug` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `image_url` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `is_qualified` tinyint(1) NOT NULL,
  `created_by` varchar(15) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_on` datetime(6) NOT NULL,
  `updated_on` datetime(6) NOT NULL,
  `is_section` int NOT NULL DEFAULT '0',
  `is_language` smallint NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `posts_topic_slug_8ebc1796` (`slug`),
  KEY `is_section` (`is_section`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

If I clone the table posts_topic as posts_topic_copy and add field its take no time. might be issue with foreign key between posts_topic and posts_post_topic table.

Im not sure why but as i killed this processesi its worked

| 1511667 | admin           | 172.31.32.92:52604 | orchestra_cms | Sleep   |    1221 |                                 | NULL                                                                                                                                                                                                                                                                          |
| 1589284 | rdsadmin        | localhost          | NULL          | Sleep   |      11 |                                 | NULL                                                                                                                                                                                                                                                                          |

Answer 1

From the processlist you shared, looks like your ALTER was waiting for the lock.

| 1680873 | admin           | 172.31.32.92:60246 | orchestra_cms | Query   |     286 | Waiting for table metadata lock | ALTER TABLE `posts_topic` ADD COLUMN `hindi_description_11` longtext NULL, LOCK=NONE                                                                                                                                                                                          |

So, that means, at that moment, the ALTER hasn't even started, but requested for a lock on the table (Also, this will lock furter, since new connections trying to use the table will ahve to wait until the ALTER is done).

There are a few things to consider; first I would recommend executing this command before DDLs:

SET SESSION innodb_lock_wait_timeout=5;
ALTER ...

With that, you'll avoid locking further: The ALTER will wait 5 seconds, if it can't get a lock, it will end.

As for the lock itself, next time you can try looking for the locking threads with this query:

SELECT r.trx_id AS waiting_trx_id, r.trx_mysql_thread_id AS waiting_thread, TIMESTAMPDIFF(SECOND, r.trx_wait_started, CURRENT_TIMESTAMP) AS wait_time, substring(r.trx_query,1,10) AS waiting_query, l.lock_table AS waiting_table_lock, b.trx_id AS blocking_trx_id, b.trx_mysql_thread_id AS blocking_thread, SUBSTRING(p.host, 1, INSTR(p.host, ':') - 1) AS blocking_host, SUBSTRING(p.host, INSTR(p.host, ':') +1) AS blocking_port, IF(p.command = "Sleep", p.time, 0) AS idle_in_trx, substring(b.trx_query,1,20) AS blocking_query FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS AS w INNER JOIN INFORMATION_SCHEMA.INNODB_TRX AS b ON b.trx_id = w.blocking_trx_id INNER JOIN INFORMATION_SCHEMA.INNODB_TRX AS r ON r.trx_id = w.requesting_trx_id INNER JOIN INFORMATION_SCHEMA.INNODB_LOCKS AS l ON w.requested_lock_id = l.lock_id LEFT JOIN INFORMATION_SCHEMA.PROCESSLIST AS p ON p.id = b.trx_mysql_thread_id ORDER BY wait_time DESC;

So you can see which trx is locking your DDL.

Since doing it in the clone is fast enough, the real issue could be the waiting for the lock. One last option you could try is executing the alter using pt-online-schema-change.

Answer 2

This may help by making the query faster, though it may not solve the current problem with the lock.

posts_posttopic smells like a many-to-many mapping table. Such a table would benefit from changing from

PRIMARY KEY (`id`),
KEY `posts_posttopic_post_id_73ce25a9_fk_posts_post_id` (`post_id`),
KEY `posts_posttopic_topic_id_04b92641_fk_posts_topic_id` (`topic_id`),

to just

PRIMARY KEY(post_id, topic_id),
INDEX(topic_id, post_id)

plus get rid of the column id.

Also, I am wondering why you have a SELECT (1) ... LIMIT 1. That seems very similar to EXISTS ( SELECT ... ) (No Limit), which runs a lot faster in some situations.

Also, what will you do with the "1"? Perhaps this can be folded into the next query?? See INSERT ... ON DUPLICATE KEY UPDATE ...

Answer 3

Here's an approach to adding a column(s) without having to wait for ALTER TABLE.

1. Create a new table with the new column(s) plus the same column(s) as the main table's PRIMARY KEY.

2. Populate it.

3. Change SELECTs on the main table to be something like

   SELECT a..., b... FROM main_table AS a LEFT JOIN new_table AS b ON a.id = b.id

and note that if there is not yet a column in b, that will deliver NULLs.