Very slow DROP INDEX, ADD PRIMARY KEY index

Question

This is probably related to another question I had, but not sure.

So my table url_meta is 25 GB including data and indexes, 70 million records.

Data        18.3 GiB
Index       7.3 GiB
Overhead    5.0 MiB
Effective   25.5 GiB
Total       25.5 GiB

I wanted to change a index column url_hash to be primary, so I ran this query:

ALTER TABLE url_meta DROP INDEX url_hash, ADD PRIMARY KEY (url_hash) USING BTREE

And it runs for 7 hours and counting, the ssd ls almost out of space and I assume query will fail.

To me it seems that it takes way to long to process 25GB

Server has 128 GB ram, 12 threads Ryzen CPU, nvme ssd.

Only options in /etc/my.cnf:

skip-log-bin

innodb_buffer_pool_size = 100G

sort_buffer_size = 1G

PhpMyAdmin says:

Many temporary tables are being written to disk instead of being kept in memory. Recommendation: Increasing max_heap_table_size and tmp_table_size might help

So these are the two variables:

tmp_table_size      16,​777,​216
max_heap_table_size 16,​777,​216

• Are the two above variables related to my slow ALTER TABLE queries?
• If yes, what values should these two be in my situation ?
• If not these 2, what variables should I tune to make this alter tables faster ?
• What other variables should I check / edit in order to use more memory and less of the SSD ?
• Table contains a full text index that I assume it got really big, is that wahat causes my problems ?

This is the table fomat:

CREATE TABLE `url_meta` (
  `url_hash` char(16) NOT NULL,
  `url_sharding` char(2) DEFAULT NULL,
  `url` varchar(512) NOT NULL,
  `url_title` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `url_description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
  `url_keywords` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `url_paragraphs` mediumtext,
  `url_total_links_in` smallint NOT NULL DEFAULT '0' COMMENT 'max SMALLINT is 65535',
  `url_meta_date` int NOT NULL DEFAULT '0',
  `url_misc` tinyint DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


-- Indexes

ALTER TABLE `url_meta`
  ADD UNIQUE KEY `url_hash` (`url_hash`),
  ADD KEY `url_total_links_in` (`url_total_links_in`) USING BTREE,
  ADD KEY `url_sharding` (`url_sharding`),
  ADD KEY `url_misc` (`url_misc`);
ALTER TABLE `url_meta` ADD FULLTEXT KEY `url_meta_index` (`url_title`,`url_description`,`url_keywords`,`url_paragraphs`);
COMMIT;

My large files in /var/lib/mysql/db_name

ls -lhaS /var/lib/mysql/my_db/fts*
-rw-r----- 1 mysql mysql  81G Dec 24 18:24 /var/lib/mysql/my_db/fts_0000000000000961_0000000000000a57_index_1.ibd
-rw-r----- 1 mysql mysql  40G Dec 25 23:58 /var/lib/mysql/my_db/fts_0000000000000a3d_0000000000000bae_index_1.ibd
-rw-r----- 1 mysql mysql  13G Dec 23 23:06 /var/lib/mysql/my_db/fts_0000000000000961_0000000000000a57_index_6.ibd
-rw-r----- 1 mysql mysql 9.3G Dec 25 04:15 /var/lib/mysql/my_db/fts_0000000000000a31_0000000000000b9b_index_1.ibd
-rw-r----- 1 mysql mysql 6.5G Dec 22 04:30 /var/lib/mysql/my_db/fts_00000000000009ec_0000000000000b2f_index_1.ibd
-rw-r----- 1 mysql mysql 6.5G Dec 23 04:37 /var/lib/mysql/my_db/fts_0000000000000a20_0000000000000b7d_index_1.ibd
-rw-r----- 1 mysql mysql 6.4G Dec 25 23:58 /var/lib/mysql/my_db/fts_0000000000000a3d_0000000000000bae_index_6.ibd
-rw-r----- 1 mysql mysql 1.1G Dec 25 04:15 /var/lib/mysql/my_db/fts_0000000000000a31_0000000000000b9b_index_6.ibd
-rw-r----- 1 mysql mysql 844M Dec 22 04:30 /var/lib/mysql/my_db/fts_00000000000009ec_0000000000000b2f_index_6.ibd
# more smaller ones ...

My current memory usage during this long query, not sure, but maybe it has room to use more memory and write less to disk ? It shows it is swapping too, no ? I assume this is bad.

free -h
               total        used        free      shared  buff/cache   available
Mem:           124Gi        91Gi       804Mi        60Mi        33Gi        33Gi
Swap:           31Gi        22Gi       8.3Gi

Answer 1

https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html#online-ddl-primary-key-operations says:

Restructuring the clustered index always requires copying of table data.

You can still use INPLACE (with some exceptions, see the manual I linked to), but this doesn't change the requirement to copy the data to a new tablespace, which requires a lot more storage space. In fact, INPLACE is a misleading name for that feature. It just means you can continue to do DML statements against the table while it is rebuilding (changes are queued up, which uses even more disk space). For more on this, read https://klouddb.io/understanding-how-online-ddl-inplace-works-in-mysql/

The time it takes to run this alter is not only related to the size of the table. It could take longer if the server is heavily loaded with other work.

Besides that, something else doesn't add up. The FTS files you show total over 160GB. This seems overly large because you said your entire table is only 25GB. I know that fulltext indexes can be bulky, but I expect it to be roughly 1.5x the size of the text data. Yours is more than 6x.

There should be a file /var/lib/mysql/my_db/url_meta.ibd that is the actual tablespace. How large is that file?

If you are running out of disk space, you might need to drop the fulltext index while you are performing the ALTER TABLE. You can rebuild the fulltext index after it's done.

You should always make sure you have enough disk space. If you are running out of space so that you can't perform a table-copy, you're already past the point when you either need to increase disk space, or else move some data to another server.

Answer 2

I can't explain the lengthy ALTER, but I have something to tell you. The ALTER was unnecessary. The table already had url_hash as the PK. Every InnoDB table has a PK, from this list:

1. An explicit PRIMARY KEY (what you were striving for);
2. The first UNIQUE key with only NOT NULL columns (what you had);
3. A hidden 6-byte number.

See SHOW FULL COLUMNS FROM url_meta;` and notice "PRI" in the "Key" column.

A side question... Is the hash a hex value? That could be shrunk via UNHEX to BINARY(8), which would shrink the table some.

Your buffer_pool size seems plenty generous. The "free -h" is inconclusive. The two "_table_size" setting are, I think, irrelevant.

More

As I understand it, any change the the PRIMARY KEY (such as DROP or ADD) will only be done via ALGORITHM=COPY. Also, I believe that ALTER will use the most efficient algorithm, but based on a simple analysis of what you request.

What MySQL version? The recent versions will do DROP INDEX 'instantly', and do ADD INDEX apparently instantly. Actually the work is done in the background, to be usable sometime later. (Check for exceptions.) Adding an index later will _probably scan the entire table, write to a disk file, sort that file, put the index in place, and finally make it "visible". This should have an effect equivalent to OPTIMIZE in that the B+Tree for that index will be without gaps. (I don't see DROP + ADD on the same index being being worth doing.)