Queries Stuck on the 'Connect' Command

Question

So, little back story. We have a master-slave set-up and multiple times per day, we see something like this on the slave database, trying to replicate whats come from the master

    Id  User         Host                                 db      Command    Time  State                             Info                                                                                                                                                                                                                                                                                                                                                                                              
------  -----------  -----------------------------------  ------  -------  ------  --------------------------------  --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
     1  system user                                       foodb    Connect   59079  Locked                            UPDATE foo SET bar = 1 WHERE baz = 2;                                                                                                                                                                                                                                                                                                                                                                                   
     2  system user                                       (NULL)   Connect   62730  Waiting for master to send event  (NULL)                                                                                                                                                                                                                                                                                                                                                                                            
   940  foouser     ip-00-000-000-00.ec2.internal:55555   foodb    Sleep         4                                    (NULL)                                                                                                                                                                                                                                                                                                                                                                                            
   941  foouser     ip-00-000-000-00.ec2.internal:55555   foodb    Sleep         3                                    (NULL)

The Query (which is a very simple , takes < 1s to run manually, every single time) seems to hang on the 'Connect' command, and never gets to the Query command.

Does anyone have any idea why it would hang here?

Another thing worth noting, the update query, is ran over 3000 times per day, and a majority of the time, the query runs fine and doesnt lock up.

I know people are going to ask about indexes, but unfortunately its privileged information, and all i can say is i'm pretty sure its indexed properly. I've checked explain plans and indexes multiple times.

innodb settings

"Variable_name" "Value"
"innodb_adaptive_hash_index"    "ON"
"innodb_additional_mem_pool_size"   "1048576"
"innodb_autoextend_increment"   "8"
"innodb_autoinc_lock_mode"  "1"
"innodb_buffer_pool_size"   "8388608"
"innodb_checksums"  "ON"
"innodb_commit_concurrency" "0"
"innodb_concurrency_tickets"    "500"
"innodb_data_file_path" "ibdata1:10M:autoextend"
"innodb_data_home_dir"  ""
"innodb_doublewrite"    "ON"
"innodb_fast_shutdown"  "1"
"innodb_file_io_threads"    "4"
"innodb_file_per_table" "OFF"
"innodb_flush_log_at_trx_commit"    "1"
"innodb_flush_method"   ""
"innodb_force_recovery" "0"
"innodb_lock_wait_timeout"  "50"
"innodb_locks_unsafe_for_binlog"    "OFF"
"innodb_log_buffer_size"    "1048576"
"innodb_log_file_size"  "5242880"
"innodb_log_files_in_group" "2"
"innodb_log_group_home_dir" "./"
"innodb_max_dirty_pages_pct"    "90"
"innodb_max_purge_lag"  "0"
"innodb_mirrored_log_groups"    "1"
"innodb_open_files" "300"
"innodb_rollback_on_timeout"    "OFF"
"innodb_stats_on_metadata"  "ON"
"innodb_support_xa" "ON"
"innodb_sync_spin_loops"    "20"
"innodb_table_locks"    "ON"
"innodb_thread_concurrency" "8"
"innodb_thread_sleep_delay" "10000"
"innodb_use_legacy_cardinality_algorithm"   "ON"

Perhaps there are long-running SELECTs running while the update tries to update the table? — Janne Pikkarainen, Jul 16 '12 at 15:06

score 2 · Answer 1 · answered Jul 17 '12 at 19:10

First lets look at the SHOW PROCESSLIST;

    Id  User         Host                                 db      Command    Time  State                             Info                                                                                                                                                                                                                                                                                                                                                                                              
------  -----------  -----------------------------------  ------  -------  ------  --------------------------------  --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
     1  system user                                       foodb    Connect   59079  Locked                            UPDATE foo SET bar = 1 WHERE baz = 2;                                                                                                                                                                                                                                                                                                                                                                                   
     2  system user                                       (NULL)   Connect   62730  Waiting for master to send event  (NULL)                                                                                                                                                                                                                                                                                                                                                                                            
   940  foouser     ip-00-000-000-00.ec2.internal:55555   foodb    Sleep         4                                    (NULL)                                                                                                                                                                                                                                                                                                                                                                                            
   941  foouser     ip-00-000-000-00.ec2.internal:55555   foodb    Sleep         3                                    (NULL)

The way replication works you will see two threads belonging to system user: The I/O Thread and the SQL Thread. Process ID #1 is the SQL Thread because it is attempting to run the SQL statement and the db is foodb.

The target table is using MyISAM as you stated in your comment for the question.

Under what circumstances would a MyISAM table get locked? Any INSERT, UPDATE, or DELETE to a MyISAM table results in a full table lock.

Please look for any crontab jobs that do moderate writes to the foo table. Also, check the OS to see if there is a lot of disk swapping going on.

Also trying to perform any write operations during a very long running SELECT is 'fun' with MyISAM. — Janne Pikkarainen, Jul 17 '12 at 19:23
The disk i/o is relatively low, and in the norm of the server, however load is 30+ where the norm is below 1 — Ascherer, Jul 23 '12 at 19:08
Please post your innodb settings : `SHOW GLOBAL VARIABLES LIKE 'innodb%';` — RolandoMySQLDBA, Jul 23 '12 at 19:11

Queries Stuck on the 'Connect' Command

1 Answers1