MySQL 8 ALGORITHM=INSTANT taking a long time

Question

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, '', '')

Answer 1

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.