Question 222582 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL 8 ALGORITHM=INSTANT taking a long time

I just ran the following ALTER TABLE statement with ALGORITHM=INSTANT on Percona Server for MySQL 8.0.33-25 and the statement took over 100 seconds to run: ALTER TABLE documents ADD COLUMN test varchar(255), ALGORITHM=INSTANT;.

During that time, my database was also unreachable from any other connection so I couldn’t really check the processlist. As if all the connections were waiting.

This is a table with a good amount of traffic and over 300 000 000 rows. Yet, ALGORITHM=INSTANT should be a quick, non-locking operation and may only take a metadata lock breifly. In addition, based on MySQL’s documentation, when a table or statement does not support ALGORITHM=INSTANT it should raise an error and not execute.

I read the MySQL documentation on the requirements for an ADD COLUMN with ALGORITHM=INSTANT and made sure my table was valid according to the requirements. I use ROW_FORMAT=DYNAMIC and have no FULLTEXT indexes.

Edit

Here is some additional information:
SHOW CREATE TABLE documents;

CREATE TABLE `documents` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int DEFAULT NULL,
  `bucket` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `object_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `extension` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `document_type` int DEFAULT '0',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `original_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `documentable_id` int DEFAULT NULL,
  `documentable_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `slug` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `file_size_bytes` int DEFAULT '0',
  `creator_id` int DEFAULT NULL,
  `theirs` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_documents_on_slug` (`slug`) USING BTREE,
  KEY `index_documents_on_documentable_user_id_created_at` (`documentable_type`,`documentable_id`,`user_id`,`created_at`) USING BTREE,
  KEY `index_documents_on_user_id_and_bucket_and_object_name` (`user_id`,`bucket`,`object_name`) USING BTREE,
  KEY `index_documents_on_creator_id` (`creator_id`)
) ENGINE=InnoDB AUTO_INCREMENT=452407889 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

SHOW TABLE STATUS WHERE name = "documents";

('documents', 'InnoDB', 10, 'Dynamic', 319689298, 357, 114200215552, 0, 109812432896, 24437063680, 452409978, '2023-07-12 19:03:23', '2023-07-17 11:48:21', NULL, 'utf8mb4_unicode_ci', NULL, '', '')
Tags:

2

Answers


  1. 0

    MySQL’s ALGORITHM=INSTANT introduced in MySQL 8.0 is designed to make certain table alterations quicker and without blocking ongoing operations (MySQL 8.0 Reference Manual). But, if your ALTER TABLE statement is taking longer than expected, here’s what could be happening:

    Server Resources: If your server doesn’t have enough CPU, memory, or IO resources, the operation can slow down. (MySQL 8.0 Server Administration)

    Locking: Even though ALGORITHM=INSTANT is mostly non-blocking, it still needs a brief metadata lock. If another long operation is blocking this lock, your ALTER TABLE might get delayed. (MySQL 8.0 Locking)

    Potential Bugs: If you’ve checked all the prerequisites and your table is eligible for ALGORITHM=INSTANT, you might be facing a bug or some unexpected behavior. Reporting this to the MySQL or Percona team might help. (MySQL Bug Reports)

    Table Size: Although ALGORITHM=INSTANT is designed to be efficient, larger tables or complex indexes might still influence the time taken. (MySQL 8.0 Optimizing for InnoDB Tables)

    Connection Limits: If the server hits its connection or thread limit, it might become unreachable during the ALTER TABLE operation. (MySQL 8.0 Connection Handling)

    Check these first and see if it helps.

    Login or Signup to reply.
  2. 0

    Rate Per Second = RPS

    Suggestions to consider for your my.cnf [mysqld] section

    innob_open_files=8000  # from 400 to reduce opened_table RPS of 482     
table_open_cache=8000  # from 400 to match innodb_open_files (BEST PRACTICE) 
read_rnd_buffer_size=16384  # from 262144 to reduce handler_read_rnd_next RPS of 206,824
read_buffer_size=524288  # from 131072 to reduce Handler_read_next RPS of 882,751

    Stop/Start of MySQL suggested for the changes to take effect.

    There are many more opportunities to improve performance tuning.

    Your ulimit report indicates Open Files is set on 1024.
    To reduce delays when MySQL needs a table to be opened,
    from OS command prompt,

    ulimit -n 20000 and press Enter to raise the limit. A dynamic setting for OS.

    After testing these changes, get in touch for tips on making
    ulimit value persistent when rebooting server, please.

    Table OPEN thrashing should be significantly reduced.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search