A few days ago, we faced an interesting problem on one of our
customer’s slave mysqld servers. An Alter for adding a new
column was run on master server took 542 seconds where as it took
few hours on the slave server to complete due to a SELECT
blocking the Alter was not allowed to complete.
Here is the the select on the master server and it’s execution time from the binary logs.
# at 825737566 #110720 19:55:21 server id 31415 end_log_pos 825737730 Query thread_id=83250629 exec_time=542 error_code=0 use collect/*!*/; SET TIMESTAMP=1311216921/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; ALTER TABLE `track` ADD `source_track_id` int(10) UNSIGNED NULL DEFAULT NULL AFTER `vendor_track_id` /*!*/;
Alter statement completed well on the master and it got blocked by a SELECT on the slave where as the time frames of the …[Read more]