3

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"
4
  • MyISAM? InnoDB? Jul 16, 2012 at 14:39
  • the database is MyISAM
    – Ascherer
    Jul 16, 2012 at 15:02
  • Perhaps there are long-running SELECTs running while the update tries to update the table? Jul 16, 2012 at 15:06
  • What version of MySQL ??? Jul 17, 2012 at 18:30

1 Answer 1

2

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.

6
  • Also trying to perform any write operations during a very long running SELECT is 'fun' with MyISAM. Jul 17, 2012 at 19:23
  • waiting for it to happen again, ill let you know
    – Ascherer
    Jul 17, 2012 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, 2012 at 19:08
  • Please post your innodb settings : SHOW GLOBAL VARIABLES LIKE 'innodb%'; Jul 23, 2012 at 19:11
  • posted in the op
    – Ascherer
    Jul 24, 2012 at 14:18

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .