Advice on adding column to large post table

[Buffaloed]

We're looking for advice on the best way to add a column to a large post table. The table is 17GB. Taking the site down to do it isn't an option. The database servers are a 2 server ring configuration. Thanks!

 

[Xon]

Use the latest MySQL version (or MariaDb version), and add the column as nullable to the end of the table and it should end-up being instant.

 

[Buffaloed]

I should have provided the query we were told to run as it's specifically declared not null.

ALTER TABLE \xf_post` ADD COLUMN `hide_signature_waindigo` BOOLEAN NOT NULL DEFAULT 0`

We're trying to install Disable Signature in Posts by Waindigo
Mysql server has gone away error occurs when attempting standard install despite increasing timeout. Author running the query to add column manually due to size of table.

 

[MySiteGuy]

ALTER TABLE xf_post ADD COLUMN `hide_signature_waindigo` BOOLEAN NOT NULL DEFAULT 0, ALGORITHM=INPLACE, LOCK=NONE;

Since the new column isn't going to have anything written to it during the alter, its safe not to lock the table. So lock=none takes care of that. Now, usually, when adding a non-null column the reason its slow is the table is copied to a new one. algorithm=inplace tells it to alter the table without a copy. This won't be instant, but it will be much faster than without these options, and will not bring down the site during operation.

 

[Buffaloed]

Thanks I appreciate the help

 

[MySiteGuy]

Happy to be of service!