MySQL Query с большим количеством записей получает Killed

Я запускаю следующий запрос из своей оболочки:

    mysql -h my-host.net -u myuser -p -e "SELECT component_id, parent_component_id FROM myschema.components comp INNER JOIN my_second_schema.component_parents related_comp ON comp.id = related_comp.component_id ORDER BY component_id;" > /tmp/IT_component_parents.txt

Запрос выполняется для LONG time, а затем получает KILLED.

Однако, если я добавляю LIMIT 1000, запрос выполняется до конца, а вывод записывается в файл.

Далее я исследовал и нашел (используя COUNT (*)), что общее количество записей, которые будут возвращены, равно 239553163.

Некоторая информация о моем сервере находится здесь:

MySQL 5.5.27

    +----------------------------+----------+
    | Variable_name              | Value    |
    +----------------------------+----------+
    | connect_timeout            | 10       |
    | delayed_insert_timeout     | 300      |
    | innodb_lock_wait_timeout   | 50       |
    | innodb_rollback_on_timeout | OFF      |
    | interactive_timeout        | 28800    |
    | lock_wait_timeout          | 31536000 |
    | net_read_timeout           | 30       |
    | net_write_timeout          | 60       |
    | slave_net_timeout          | 3600     |
    | wait_timeout               | 28800    |
    +----------------------------+----------+

Здесь СОСТОЯНИЕ запроса, когда я контролировал:

    copying to tmp table on disk
    sorting results
    sending data
    writing to net
    sending data
    writing to net
    sending data
    writing to net
    sending data ...
    KILLED

Любые догадки о том, что здесь не так?

Ответ 1

Клиент mysql, вероятно, исчерпал память.

Используйте параметр --quick, чтобы не буферировать результаты в памяти.

Ответ 2

Неправильно то, что вы возвращаете 239 553 163 строк данных! Не удивляйтесь, что для обработки требуется много времени. Фактически, самая длинная часть может очень хорошо отправить результат обратно на ваш клиент.

Повторите набор результатов (вам действительно нужны все эти строки?). Или попытайтесь вывести данные меньшими партиями:

mysql -h my-host.net -u myuser -p -e "SELECT ... LIMIT 10000, 0" >> dump.txt
mysql -h my-host.net -u myuser -p -e "SELECT ... LIMIT 10000, 10000" >> dump.txt

Ответ 3

Предполагая, что вы имеете в виду 8 часов, когда вы говорите долгое время, значение 28800 для вашего wait_timeout приводит к тому, что соединение падает без дополнительной активности в 28 800 секунд, то есть 8 часов. Если вы не можете оптимизировать работу оператора менее чем за 8 часов, вы должны увеличить это значение.

Подробнее о переменной wait_timeout см. эту страницу.

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

Ответ 4

Вы можете использовать OUTFILE mechanizm, если вы собираетесь сбрасывать большие объемы данных. Это или mysql_dump будет намного более эффективным (и OUTFILE получил преимущество не блокировать таблицу).

Ответ 5

Вы сказали в комментарии, что ваш экземпляр MySQL находится в RDS. Это означает, что вы не можете запускать запрос с одного и того же хоста, поскольку вы не можете войти в RDS-узел. Я думаю, вы можете делать этот запрос через WAN из своей локальной сети.

У вас, скорее всего, проблемы из-за медленной сети. Ваше состояние процесса, часто показывающее "письмо в сеть", заставляет меня думать, что это ваше узкое место.

Вашим узким местом может быть сортировка. Ваш вид записывается в временную таблицу, и это может занять много времени для большого набора результатов. Можете ли вы пропустить ORDER BY?

Тем не менее, я бы не ожидал, что запрос будет убит, даже если он работает в течение 3100 секунд или более. Интересно, имеет ли ваш администратор базы данных несколько периодических заданий, связанных с долговременными запросами, например pt-kill. Спросите своего администратора базы данных.

Чтобы сократить время передачи по сети, вы можете попробовать использовать протокол сжатия. Вы можете использовать флаги --compress или -C для клиента mysql для этого (см. https://dev.mysql.com/doc/refman/5.7/en/mysql-command-options.html#option_mysql_compress)

В медленной сети сжатие может помочь. Например, прочитайте о некоторых сравнениях здесь: https://www.percona.com/blog/2007/12/20/large-result-sets-vs-compression-protocol/

Другой альтернативой является запуск запроса из экземпляра места EC2, работающего в том же AZ, что и ваш экземпляр RDS. Сеть между этими двумя экземплярами будет намного быстрее, поэтому она не задержит передачу данных. Сохраните вывод запроса в файл на экземпляре места EC2.

Как только результат запроса будет сохранен на вашем экземпляре EC2, вы можете загрузить его на свой локальный компьютер, используя scp или что-то, что должно быть более терпимым к медленным сетям.