Производительность MySQL — выбор и удаление из большой таблицы

У меня есть большая таблица под названием «очередь». Сейчас у него 12 миллионов записей.

CREATE TABLE `queue` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `userid` varchar(64) DEFAULT NULL,
  `action` varchar(32) DEFAULT NULL,
  `target` varchar(64) DEFAULT NULL,
  `name` varchar(64) DEFAULT NULL,
  `state` int(11) DEFAULT '0',
  `timestamp` int(11) DEFAULT '0',
  `errors` int(11) DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_unique` (`userid`,`action`,`target`),
  KEY `idx_userid` (`userid`),
  KEY `idx_state` (`state`)
) ENGINE=InnoDB;

Несколько воркеров PHP (150) используют эту таблицу одновременно.

Они выбирают запись, выполняют сетевой запрос с использованием выбранных данных, а затем удаляют запись.

Я получаю смешанные времена выполнения от запросов выбора и удаления. Команда удаления блокирует таблицу?

Что было бы лучшим подходом для этого сценария?

  1. ВЫБЕРИТЕ запись + СЕТЕВОЙ запрос + УДАЛЕНИЕ записи

  2. ВЫБЕРИТЕ запись + СЕТЕВОЙ запрос + ПОМЕТИТЬ запись как завершенную + УДАЛИТЬ завершенные записи, используя время от времени cron (мне не нужна еще большая таблица).

Примечание. Очередь получает новые записи каждую минуту, но запрос INSERT здесь не является проблемой.

Любая помощь приветствуется.


mysql performance query-performance
person Robert Neagu    schedule 03.12.2017    source источник
comment
Если вы используете базу данных в качестве очереди и работаете с ней с нескольких клиентов, вы обязательно столкнетесь с конкуренцией за блокировку. Каждый борется с этим, когда пытается использовать базу данных в качестве очереди. Вместо этого вы должны использовать какое-то реальное программное обеспечение очереди сообщений, а не базу данных. Что-то вроде ActiveMQ, Beanstalkd, RabbitMQ или Resque.   -  person Bill Karwin    schedule 03.12.2017
comment
Вам может быть полезно прочитать это, особенно часть о производительности запросов. meta.stackoverflow.com/a/271056 Тогда вам может понадобиться отредактируйте свой вопрос, чтобы дать более подробную информацию.   -  person O. Jones    schedule 03.12.2017

Ответы (1)


arrow_upward
2
arrow_downward

«Не стой в очереди, просто делай». То есть, если задачи достаточно быстрые, то лучше просто выполнить действие, а не ставить его в очередь. Базы данных не являются хорошими механизмами очередей.

DELETE не блокирует таблицу InnoDB. Тем не менее, вы можете написать DELETE, который покажется непослушным. Давайте посмотрим на ваш фактический SQL, чтобы мы могли поработать над его улучшением.

12М записей? Это огромное отставание; Как дела?

Сократите типы данных, чтобы таблица не занимала гигабайты:

  • action — это лишь небольшой набор возможных значений? Нормализуйте его до 1-байтового ENUM или TINYINT UNSIGNED.
  • То же самое для state -- ведь ему не нужен 4-байтовый код?
  • Нет необходимости в INDEX(userid), так как уже есть индекс (UNIQUE), начинающийся с userid.
  • Если state имеет всего несколько значений, индекс использоваться не будет. Давайте посмотрим ваши запросы на постановку и удаление из очереди, чтобы мы могли обсудить, как избавиться от этого индекса или сделать его «составным» (и полезным).
  • Каково текущее значение MAX(id)? Угрожает ли это превышению вашего текущего лимита примерно в 4 миллиарда для INT UNSIGNED?
  • Как PHP использует очередь? Зависает ли он на элементе через транзакцию InnoDB? Это побеждает любой параллелизм! Или это меняет state. Покажите нам код; возможно, блокировку и разблокировку можно сделать менее инвазивной. Должна быть возможность запуска одного автофиксированного UPDATE для захвата строки и ее id. Затем, позже, выполните автофиксацию DELETE с очень небольшим влиянием.
  • Я не вижу хорошего индекса для захвата ожидающего элемента. Опять же, давайте посмотрим код.
  • 150 – это много. Вы пробовали меньше? Они могут спотыкаться друг о друга.
  • Включен ли Slowlog (с низким значением long_query_time)? Если да, то мне интересно, какой запрос является «худшим». В подобных ситуациях ответ может быть неожиданным.
person Rick James    schedule 03.12.2017
comment
Спасибо за ваше предложение. Сначала я попытаюсь реализовать некоторые из предложенных изменений и вернусь с некоторыми результатами. Еще раз спасибо! - person Robert Neagu; 05.12.2017
comment
* Действие столбца имеет только 7 возможных значений * Состояние столбца имеет только 2 возможных значения * Максимум (id) составляет около 64577039 * Рабочие выполняют задачи только для идентификатора пользователя % 150 = 0 (они не перекрывают свою работу) * Мне нужно так много рабочие, чтобы выполнить эту огромную очередь, так как каждая операция занимает около 2 секунд, и новые операции добавляются в очередь постоянно - person Robert Neagu; 05.12.2017
comment
Вы привязаны к вводу-выводу? Привязанный к процессору? Оба? Рабочие на одной машине? Еще одна машина? Несколько других машин? Вы потребляете 150 предметов каждые 2 секунды? Это скорость, с которой они ставятся в очередь? - person Rick James; 05.12.2017
comment
Почему бы не удалить сразу, без отложенной задачи cron? - person Rick James; 05.12.2017
comment
@RobertNeagu - и что-нибудь из медленного журнала? - person Rick James; 05.12.2017