Consultas presas no comando 'Connect'

Question

Então, pequena história de volta. Nós temos uma configuração mestre-escravo e várias vezes por dia, vemos algo como isso no banco de dados escravo, tentando replicar o que vem do mestre

    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)                                      

A Consulta (que é muito simples, toma < 1s para ser executada manualmente, todas as vezes) parece travar no comando 'Conectar' e nunca chega ao comando Consulta.

Alguém tem alguma ideia de por que ela iria parar aqui?

Outra coisa digna de nota, a consulta de atualização, é executada mais de 3000 vezes por dia e, na maioria das vezes, a consulta é executada corretamente e não é bloqueada.

Eu sei que as pessoas vão perguntar sobre índices, mas infelizmente suas informações privilegiadas, e tudo o que posso dizer é que tenho certeza que elas estão indexadas corretamente. Eu verifiquei explique os planos e os índices várias vezes.

configurações innodb

"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"

    

Answer 1

Primeiro, vamos ver o 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)                                      

Como funciona a replicação, você verá dois encadeamentos pertencentes a system user : O encadeamento de E / S e o Encadeamento SQL. O ID do processo nº 1 é o segmento SQL porque está tentando executar a instrução SQL e o db é foodb .

A tabela de destino está usando o MyISAM como você declarou no seu comentário para a pergunta.

Em que circunstâncias uma tabela MyISAM seria bloqueada? Qualquer INSERT, UPDATE ou DELETE para uma tabela MyISAM resulta em um bloqueio total de tabela.

Por favor, procure por qualquer trabalho de crontab que faça gravações moderadas na tabela foo . Além disso, verifique o sistema operacional para ver se há muita troca de disco acontecendo.