Настройка конфигурации MySQL для поддержки сотен соединений, блокирующих одну и ту же таблицу

У меня есть таблица ресурсов с 2000 строк. Двигатель innodb. Есть поле 'free_at' (индексированное). По каждому запросу мне нужно заблокировать таблицу, получить бесплатный ресурс (упорядоченный по столбцу «free_at»), обновить эту строку до несвободной и снять блокировку.

Это базовая реализация пула, которую я использовал, и она отлично работала со 100-200 подключениями и менее чем 1000 строк (ресурсы в пуле).

Сейчас около 800 процессов, которые постоянно запрашивают ресурсы из таблицы (каждый раз в 10-15 секунд, то есть в среднем до 80\с).

Моим узким местом является время ожидания блокировки, которое составляет от 30 до 60 секунд (!) для каждого запроса. Я уверен, что есть какая-то конфигурация, которую я должен изменить, чтобы ускорить блокировку и освобождение.

Я попытался изменить тип двигателя на ПАМЯТЬ, но это не улучшило время ожидания блокировки.

Должен ли я искать другое решение для пула, которое не основано на MySQL и может распределять ресурсы по приоритету (в моем случае это поле «free_at»)?

РЕДАКТИРОВАТЬ:

для блокировки я использую LOCK TABLES table_name WRITE

Затем выберите SELECT * FROM table_name WHERE (free_at < NOW() OR free_at is null) ORDER BY free_at ASC

Обновление поля «free_at» UPDATE table_name SET free_at = NOW() + INTERVAL 5 MINUTE WHERE id= 1234

Наконец разблокировка UNLOCK TABLES

Схема таблицы

`resources` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `free_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `free_at` (`free_at`),
) ENGINE=InnoDB

mysql design-patterns pool
person Roey B    schedule 27.01.2014    source источник
comment
Можете ли вы показать схему таблицы и код SQL, который вы используете? Вы блокируете всю таблицу или только строку, используя SELECT ... FOR UPDATE?   -  person O. Jones    schedule 27.01.2014
comment
Использование free_at IS NULL окончательно исключает использование индекса в вашем выражении SELECT.   -  person O. Jones    schedule 28.01.2014

Ответы (2)


arrow_upward
0
arrow_downward

Вам нужно заблокировать строки в таблице, а не всю таблицу. Как вы обнаружили, блокировка всей таблицы не увеличивает масштаб.

Придерживайтесь InnoDB. Методы доступа MEMORY и MyISAM этого не делают.

Предполагая, что ваша таблица resource имеет следующие столбцы (у нее могут быть и другие):

resource_id int not null primary key
free        int not null 1 means free, 0 means in use
free_at     timestamp not null

И если вам нужна транзакция, которая захватит самую старую строку свободных ресурсов (free = 1), вот что вы делаете с каждым клиентом, нуждающимся в ресурсе.

 START TRANSACTION;

 SELECT resource_id
   FROM resource
  WHERE free = 1
  ORDER BY free_at ASC
  LIMIT 1
    FOR UPDATE;

На этом этапе ваше приложение получит либо один resource_id, который вы можете выделить, либо не получит ни одного. Если он не получает ничего, это означает, что он должен подождать и повторить попытку.

Если у вас есть resource_id, обновите его, чтобы указать, что он используется.

 UPDATE resource
    SET free = 0,
        free_at = NOW()
  WHERE resource_id = 'the resource ID you just got';

Затем, как только сможете, сделайте

 COMMIT;

чтобы завершить начатую транзакцию и снять блокировку этой строки. Этот способ распределения ресурсов работает лучше, чем блокировка таблицы, потому что каждому соединению нужно заблокировать только свою собственную строку.

Если вы не получили идентификатор ресурса из запроса SELECT ... FOR UPDATE, вам нужно немедленно сделать

 ROLLBACK;

чтобы отменить транзакцию, которую вы начали в этой строке. Затем нужно подождать и повторить попытку. Подождите значительное количество времени: по крайней мере, время, которое требуется вашему приложению, чтобы использовать один из ваших ресурсов и освободить его. Если вы подождете меньше времени, ваши многочисленные соединения будут забиты в таблице ресурсов и замедлят работу. Если вам нужно больше ресурсов, добавьте их.

Когда ваше программное обеспечение закончит работу с выделенным ему ресурсом, сделайте это, чтобы выпустить его обратно в пул. Вы можете просто выполнить эту операцию в режиме автоматической фиксации; здесь нет необходимости в явной транзакции. Но убедитесь, что для этого у ваших клиентов включен режим автоматической фиксации.

 UPDATE resource
    SET free = 1
  WHERE resource_id = 'the resource ID you have been using';

Пожалуйста, обрати внимание; Я не совсем понимаю, как вы используете free_at, поэтому я могу ошибаться в этой части логики.

Операция SELECT по поиску свободного ресурса, скорее всего, будет ускорена путем добавления составного индекса, покрывающего индекса, на

 (free, free_at, resource_id)
person O. Jones    schedule 27.01.2014
comment
Я пытался использовать SELECT FOR UPDATE раньше и получал исключения из тупиковой блокировки. Вот почему я вместо этого переключился на блокировку всей строки. - person Roey B; 28.01.2014
comment
Вы представили проблему XY: вы хотите сделать X и думаете, что Y — лучший способ сделать это. Вместо того, чтобы спрашивать о X, вы спрашиваете об Y. - person O. Jones; 28.01.2014
comment
Я обязательно попробую предложенное решение еще раз, просто предположил, что может быть лучший способ создать пул, используя блокировку всей таблицы. - person Roey B; 28.01.2014
comment
реализация этого увеличила время ожидания блокировки. что происходит, когда строка заблокирована (в транзакции), а несколько других подключений пытаются отсортировать таблицу и получить следующую строку? разве это не проблема? - person Roey B; 28.01.2014
comment
Попробуйте пропустить ORDER BY и просто выполнить LIMIT 1. Обязательно зафиксируйте транзакцию как можно быстрее. Если нет какой-либо причины, по которой вы должны циклически использовать ресурсы, просто возьмите первый подходящий. Убедитесь, что доступные для поиска столбцы в вашей таблице объявлены NOT NULL, и что вы исключили OR col IS NULL из предложения WHERE. - person O. Jones; 28.01.2014
comment
для справки в будущем это решение не решило проблему для меня, и фактически добавление индекса фактически снизило производительность. Я принял ответ, так как уверен, что он может быть полезен другим людям в других сценариях. - person Roey B; 29.01.2014

arrow_upward
0
arrow_downward

Мне удалось решить эту проблему путем внутренней буферизации ресурсов. Вместо того, чтобы извлекать по 1 ресурсу за раз, я извлекаю 5 из них, тем самым уменьшая количество вызовов и блокировок для этой таблицы в 5 раз.

person Roey B    schedule 29.01.2014