Skip to main content

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/crawlsonmy_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.

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/crawlson/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.

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.

Source Link
Bill Karwin
  • 16k
  • 2
  • 31
  • 42

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/crawlson/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.