PHP+mysql is running web service, but when I added one column in ALTER TABLE during the day to a table with a lot of update queries (transactions), the DB load increased and I couldn't access the site, so I had to restart mysql.
According to Mr. Han's website, I thought it would be burdensome.
In conclusion, mySQL ALTER
TABLE re-copies all the data in the table.You may think, "What a waste!?" but dynamic table definition (schema) changes require support from the storage engine, and few storage engines still support dynamic schema changes.
How do you run ALTER TABLE on sites where services are running?
Do you stop the service?Or do it late at night, etc.
■ About
version: 5.1.73
storage engines:INNODB
The version will be 8.0.12~, but it seems that a feature called instant add column has been added.
What this means is that when DDL runs, the InnoDB does not change, but only updates the metadata of the Data Dictionary introduced from MySQL 8 to complete some DDLs in an instant, such as ADD COLUMN.
-- https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html#online-ddl-column-operations
ALTER TABLE t1 ADD COLUMN c2 INT, ADD COLUMN c3 INT, ALGORITHM = INSTANT;
Also, I have discovered the following page as a commentary in Japanese.
https://y-asaba.hatenablog.com/entry/2018/12/21/002811
Also, even if it doesn't reach 8, it seems that the INPLACE ADD COLUMN will be available from around 5.6.This is how it works so that you can browse and update while doing the add column.
So, even if it doesn't go up to 8, I think it's a problem that can be solved by giving the MySQL version.
© 2024 OneMinuteCode. All rights reserved.