Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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"
share|improve this question
    
MyISAM? InnoDB? –  Janne Pikkarainen Jul 16 '12 at 14:39
    
the database is MyISAM –  Ascherer Jul 16 '12 at 15:02
    
Perhaps there are long-running SELECTs running while the update tries to update the table? –  Janne Pikkarainen Jul 16 '12 at 15:06
    
What version of MySQL ??? –  RolandoMySQLDBA Jul 17 '12 at 18:30

1 Answer 1

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.

share|improve this answer
    
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
    
waiting for it to happen again, ill let you know –  Ascherer Jul 17 '12 at 21:08
    
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
    
posted in the op –  Ascherer Jul 24 '12 at 14:18

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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