Mysql – Does MySQL allows incoming queries while altering a table

alter-tableinnodbMySQLmysql-innodb-cluster

I'm adding a new column to an existing table say ABC with the following query,

ALTER TABLE ABC add column STATUS int(10) NOT NULL

This is done in master, slave and secondary slaves. Meanwhile, while altering the existing table (which does not contain any data) – other databases (other schemas and tables) were queried – both in master and slave databases.

After altering column in the table ABC has been completed I could see slowness in many app servers and few got killed stating "You have reached your max limit to DB Connections" and you could make no more DB connections and the app server got killed.

I also run a jsp in one of my servers – the thread gets hanged (stopped after making connection to one of secondary slave) in the mean time. I'm a no DB expert. I'm a novice MySQL learner and do not have any prior experience I do not know how to debug further.

I suspect whether my MySQL servers (either master or slave) does not accept any incoming connections while altering a table and once the table alteration gets completed it accepts the queued up requests and process them one by one.

Since the connections got queued up the app servers – which are waiting for previous query response – couldn't make any more requests. Can someone shed some insights to debug

on db connection getting exhausted and
the thread which invoked a jsp never ends (stops where it makes connection to mysql) – this happened during the timestamp when alter table happened.

Best Answer

During a DDL operation (e.g. alter table) MySQL locks only the objects in question - in this case the table.

Other tables and databases remain unaffected and new connections are accepted as usual.

On a slave, the DDL operation can't be executed in parallel, so it will delay the replication by whatever time it takes to execute.

The alter operation can increase the general server load CPU/MEM/IO, which can cause it to run out of resources. This rare (with a server with at least a few cores), and pretty much impossible with an empty table, because the alter takes < 1 sec.

The only way a DDL directly affects other connections is if they use the table/s which are modified.

Related Solutions

MySQL – ALTER TABLE on Large Table with Indexed Column

If you are a little adventurous, you could take matters into your hands by performing the ALTER TABLE in stages you can see. Suppose the table you want to change is called WorkingTable. You could perform the changes in stages like this:

#
#  Script 1
#  Alter table structure of a single column of a large table
#
CREATE TABLE WorkingTableNew LIKE WorkingTable;
ALTER TABLE WorkingTableNew MODIFY BigColumn VARCHAR(50);
INSERT INTO WorkingTableNew SELECT * FROM WorkingTable;
ALTER TABLE WorkingTable RENAME WorkingTableOld;
ALTER TABLE WorkingTableNew RENAME WorkingTable;
DROP TABLE WorkingTableOld;

You can perform this on all slaves. What about the master ??? How do you prevent this from replicating to the slaves. Simple: Don't send the SQL into the master's binary logs. Simply shut off binary logging in the session before doing the ALTER TABLE stuff:

#
#  Script 2
#  Alter table structure of a single column of a large table
#  while preventing it from replicating to slaves
#
SET SQL_LOG_BIN = 0;
CREATE TABLE WorkingTableNew LIKE WorkingTable;
ALTER TABLE WorkingTableNew MODIFY BigColumn VARCHAR(50);
INSERT INTO WorkingTableNew SELECT SQL_NO_CACHE * FROM WorkingTable;
ALTER TABLE WorkingTable RENAME WorkingTableOld;
ALTER TABLE WorkingTableNew RENAME WorkingTable;
DROP TABLE WorkingTableOld;

But wait !!! What about any new data that comes in while processing these commands ??? Renaming the table in the beginning of the operation should do the trick. Let alter this code a little to prevent entering new data in that respect:

#
#  Script 3
#  Alter table structure of a single column of a large table
#  while preventing it from replicating to slaves
#  and preventing new data from entering into the old table
#
SET SQL_LOG_BIN = 0;
ALTER TABLE WorkingTable RENAME WorkingTableOld;
CREATE TABLE WorkingTableNew LIKE WorkingTableOld;
ALTER TABLE WorkingTableNew MODIFY BigColumn VARCHAR(50);
INSERT INTO WorkingTableNew SELECT SQL_NO_CACHE * FROM WorkingTableOld;
ALTER TABLE WorkingTableNew RENAME WorkingTable;
DROP TABLE WorkingTableOld;

Script 1 can be executed on any slave that do not have binary logs enabled
Script 2 can be executed on any slave that does have binary logs enabled
Script 3 can be executed on a master or anywhere else

Give it a Try !!!

MySQL Slave lag in SHOW SLAVE STATUS does not match SHOW PROCESSLIST

The "Time" in the SQL thread is (I think) identical to Seconds_behind_master. It is "How long ago did this query start on the Master ".

All other Times are indicate when the query started on the Slave.

Some fluctuation is caused by what it is measuring (the Master's start time).

Sometimes (rarely), I see the value (both places) bouncing between 0 and some large value. I have yet to track this down. I have seen it on 4.0, 4.1, and 5.1. It eventually goes away, and becomes civilized.

There may be cases where no traffic leads to strange values. But I don't have any Master-Slave setups with little enough traffic for me to comment.

Suppose you do ALTER on the Master, and it took 1 hour (3600 seconds). Also, suppose not much else is going on. The ALTER replicates and starts running. Immediately, the Seconds_behind_master will be about 3600. After the ALTER finishes on the Slave (say, 3600 more seconds later), subsequent replication items will execute with (probably) smaller Times. Eventually replication catches up.

Best Answer

Related Solutions

MySQL – ALTER TABLE on Large Table with Indexed Column

MySQL Slave lag in SHOW SLAVE STATUS does not match SHOW PROCESSLIST

Related Question