Все мы знаем, что обновления InnoDB блокируют строки, а обновления MyISAM блокируют таблицы, но как вы это объясните? Вот результаты SHOW FULL PROCESSLIST, когда проблема находится в процессе возникновения:
╔═════════╦════════╦═════════╦════════════════════════════════╗
║ Command ║ Time ║ State ║ Info ║
╠═════════╬════════╬═════════╬════════════════════════════════╣
║ Query ║ 0 ║ NULL ║ SHOW FULL PROCESSLIST ║
║ Query ║ 121 ║ end ║ UPDATE [InnoDB table] ║
║ Query ║ 121 ║ update ║ INSERT INTO [MyISAM table] ║
║ Query ║ 121 ║ Locked ║ INSERT INTO [MyISAM table] ║
║ Query ║ 120 ║ Locked ║ INSERT INTO [MyISAM table] ║
║ Query ║ 120 ║ Locked ║ INSERT INTO [MyISAM table] ║
╚═════════╩════════╩═════════╩════════════════════════════════╝
Я оставил некоторые столбцы для экономии места. Все вышеперечисленное происходит в одной базе данных и относится к одной таблице InnoDB и одной таблице MyISAM. Под этим было много других рядов, подобных трем нижним, но я снова их не учел.
Проблема начинается с UPDATE в таблице InnoDB, которая затем блокирует таблицу MyISAM. База данных в целом не заблокирована, поскольку каждую секунду выполнялись сотни запросов SELECT, и они не зависали. Обратите внимание, что состояние первого INSERT — «обновление», что, по-видимому, является причиной блокировки последующих вставок, но тогда возникает вопрос: что такое обновление INSERT? Автоинкремент? Индексы таблиц? Предположительно. Но почему такое обновление блокируется ОБНОВЛЕНИЕМ другой таблицы? Подробнее о том, что означают эти состояния, см. здесь:
http://dev.mysql.com/doc/refman/5.1/en/general-thread-states.html
Вот что я понял до сих пор:
- В запросах нет ничего плохого — UPDATE индексируется соответствующим образом, а запросы INSERT и UPDATE обычно выполняются ‹ 100 мс, но, как вы можете видеть, этот запрос занимает более 2 минут.
- Почти все запросы, выполняемые к базе данных, являются SELECT и INSERT, за исключением UPDATE.
- Это определенно ОБНОВЛЕНИЕ, вызывающее проблему, поскольку оно полувоспроизводимо - если я выполняю аналогичный запрос, иногда он воссоздает проблему, иногда он выполняется быстро, как обычно.
- Только эта конкретная таблица InnoDB кажется проблемой, поскольку другие таблицы InnoDB в базе данных обновляются без проблем, но зато она самая большая - она содержит ~ 1 миллион строк и имеет размер ~ 40 МБ.
- ОБНОВЛЕНИЕ реплицируется на несколько серверов, но длительное время выполнения, по-видимому, происходит только на серверах со значительной нагрузкой.
- Я проверил загрузку ЦП и свободную оперативную память на сервере во время возникновения проблемы, и они оба в порядке.
Спецификации сервера:
- Сжатие Debian
- MySQL 5.1
- 8 ядер
- 32 ГБ ОЗУ
Прежде чем вы спросите, вот некоторые настройки конфигурации [mysqld]:
skip-external-locking
innodb_file_per_table
innodb_flush_method = O_DIRECT
innodb_buffer_pool_size = 512M
key_buffer = 1500M
read_rnd_buffer_size = 512k
table_open_cache = 4096
tmp_table_size = 256M
max_heap_table_size = 256M
concurrent_insert = 2
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
max_connections = 2000
open_files_limit = 60000
query_cache_limit = 1M
query_cache_size = 512M
После обширного гугления я все еще в недоумении, поэтому любая помощь будет принята с благодарностью!
Добавлен
Создайте синтаксис для таблицы InnoDB:
CREATE TABLE `tablename` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`column1` bigint(20) unsigned NOT NULL,
`column2` int(10) unsigned NOT NULL,
`column3` int(10) unsigned NOT NULL,
`column4` int(10) unsigned NOT NULL,
`column5` int(10) unsigned DEFAULT NULL,
`column6` enum('yes','no') NOT NULL DEFAULT 'no',
PRIMARY KEY (`id`),
UNIQUE KEY (`column1`),
KEY (`column2`),
KEY (`column5`)
) ENGINE=InnoDB AUTO_INCREMENT=993266 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;
id
= [значение_идентификатора]», гдеid
является автоматически увеличивающимся первичным ключом. Когда вы говорите определение таблицы, вы имеете в виду синтаксис CREATE? - person Philip   schedule 06.11.2013