1

I have a MySQL table with 3GB data and the ALTER TABLE to create a new field lasts about 15 minutes in pre-production.

The software can both do read- and write-queries with the new field or without it, so I can update the database without altering the software.

Question:

Is there any way to run this in some way like "background" and let all the CPU cycles to attend first the "other queries" and only if there is free CPU then run the ALTER?

What is the correct way to do a "long alter table" in production?

2
  • Without seeing the actual query and knowing details about the query, nobody can really give you a solid answer. Oct 28 '17 at 2:21
  • Too broad for sure.... you need to increase the performance of the query then consider making the query a stored procedure and then call or execute the proc rather than sending the query from the app, etc. Consider using explain and show status like 'last_query_cost' after the query is run to see how you can increase performance and how the SQL is working behind the scenes, etc. Oct 30 '17 at 11:46
2

CPU is rarely the real bottleneck. It's usually the disk, with the amount of memory available to the buffer pool also being a constraint.

The problem is, the resources required to do an ALTER TABLE are usually not your actual problem... it's the fact that the table will likely be locked during the operation.

There is no way to de-prioritize queries in MySQL, but if there were, it would likely only prolong your agony as the table-level lock would just persist longer.

Try this:

ALTER TABLE t1
  ADD COLUMN c2 BIGINT UNSIGNED NOT NULL AFTER c1,
  LOCK=NONE,
  ALGORITHM=INPLACE;

If the server won't let you do this -- which should solve your problem -- then the error message should explain the reason.

If the reason is related to foreign keys, yet your ALTER query will not do anything to disturb the foreign key integrity, you can disable the checks that would prevent you from doing an in place alter without locks.

SET @@FOREIGN_KEY_CHECKS = 0; -- before ALTER
SET @@FOREIGN_KEY_CHECKS = 1; -- after ALTER

This does not disable the foreign key constraints on the table. It only exempts any queries you run from your current connection from the necessity to take steps to avoid violating relational integrity. As long as you aren't doing anything to disturb relational integrity, it is a safe strategy to use, but only when you need it.

https://dev.mysql.com/doc/refman/5.7/en/alter-table.html

1

Yes you can. A simple Google search found this answer on our sister SE site: https://stackoverflow.com/questions/24704086/low-priority-query-in-mysql

You can use the LOW_PRIORITY or HIGH_PRIORITY in your queries depending on the type of query you execute:

INSERT [LOW_PRIORITY | HIGH_PRIORITY] INTO ...
SELECT [HIGH_PRIORITY] * FROM ...
UPDATE [LOW_PRIORITY] table ...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.