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"