4

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.

Improve this question
7
  • What is the replication topology? Single Master plus multiple Slaves? Galera Cluster? InnoDB Cluster?
    – Rick James
    Commented May 26, 2018 at 0:08
  • InnoDB cluster.
    – Tom Taylor
    Commented May 26, 2018 at 2:54
  • Ok, this is Group Replication / InnoDB cluster, but which version of MySQL ? Also, does your ABC table contains FOREIGN KEYs (or maybe is the target of FOREIGN KEYs) ?
    – jfg956
    Commented May 27, 2018 at 9:14
  • No they do not have any foreign key relationships. My MySql version is 5.5.21
    – Tom Taylor
    Commented May 27, 2018 at 9:18
  • You probably mean 5.7.21 (Group Replication only appeared in 5.7). Another question about the Group Replication configuration: is is single-primary or multiple writers (what is the value of the group_replication_single_primary_mode global variable) ?
    – jfg956
    Commented May 27, 2018 at 9:37

1 Answer 1

Reset to default
3

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.

Improve this answer
2
  • Is there a lock on information_schema tables, too, during the ALTER? This might explain new connections being slow, some ORMs / clients are getting metadata info for all tables, immediately after connection.
    – ypercubeᵀᴹ
    Commented May 17, 2018 at 9:30
  • I tested this on our dev server - adding a field to a table doesn't lock information_schema.tables.
    – Vatev
    Commented May 17, 2018 at 10:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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