Como logar todas as consultas do MySQL que esperaram por um bloqueio de tabela?

Question

Estamos tentando diagnosticar um problema de deadlock do MySQL 5.0. Atualmente, todas as tabelas são tabelas MyISAM e, portanto, todos os bloqueios são bloqueios de tabela. Temos uma boa proporção table_locks_waited para table_locks_immediate (menor que 1: 1000), mas em alguns minutos temos alguns bloqueios. Isso só acontece no Amazon EC2, mas não em nenhum de nossos outros servidores.

Existe uma maneira de registrar todas as consultas que não puderam obter um bloqueio de tabela imediatamente? Queremos ver todas as consultas que contribuíram para o table_locks_waited number.

    

Answer 1

No log lento, um dos campos exibidos é o tempo de bloqueio

Aqui está um exemplo com duas consultas que foram registradas

# Time: 110726  1:00:09
# User@Host: mysql[mysql] @  [10.64.100.208]
# Query_time: 8  Lock_time: 0  Rows_sent: 0  Rows_examined: 852669
insert into rates_old select * from rates;
# Time: 110726  1:30:56
# User@Host: mysql[mysql] @  [10.64.100.208]
# Query_time: 50  Lock_time: 0  Rows_sent: 0  Rows_examined: 11015414
SET timestamp=1311661856;
delete a.*,  b.* from zillowDump a, zillowHistory b where a.loanRequestId=b.loanRequestId and a.addDate < date_sub(now(), INTERVAL 2 week);

Por favor, note o campo Lock_time:.

CAVEAT

No MySQL 5.1+ o campo aparece na tabela padrão mysql.slow_log

mysql> show create table mysql.slow_log\G
*************************** 1. row ***************************
       Table: slow_log
Create Table: CREATE TABLE 'slow_log' (
  'start_time' timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  'user_host' mediumtext NOT NULL,
  'query_time' time NOT NULL,
  'lock_time' time NOT NULL,
  'rows_sent' int(11) NOT NULL,
  'rows_examined' int(11) NOT NULL,
  'db' varchar(512) NOT NULL,
  'last_insert_id' int(11) NOT NULL,
  'insert_id' int(11) NOT NULL,
  'server_id' int(10) unsigned NOT NULL,
  'sql_text' mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'
1 row in set (0.00 sec)

De qualquer forma, você pode obter o tempo de bloqueio de qualquer consulta

    

Answer 2

Tente observar as consultas e bloqueios em tempo real usando este comando:

watch -n 0.5 'mysqladmin -u root -ppassword "processlist"'

Se o bloqueio ocorrer por qualquer período de tempo, você deve ter uma boa ideia do que está acontecendo.