#mysql #indexing #throttling
#mysql #индексирование #регулирование
Вопрос:
Мне нужно загрузить большой набор данных в производственную базу данных.
Необходимо загрузить каждый из 15 файлов и вставить в таблицу. Каждая из них составляет около 500 Мб.
У меня есть два столбца ID, которые необходимо проиндексировать. Если я загружаю файлы с установленными индексами, загрузка занимает около 3 часов. Если я удаляю индексы, загружаю данные в локальный файл, а затем повторно добавляю индексы, вся операция занимает около 30 минут.
Проблема в том, что отзывчивость базы данных сильно страдает при индексации недавно импортированных данных. Есть ли способ заставить индексирование выполняться с «низким приоритетом», чтобы другие запросы по-прежнему выполняли скорость 95-100%, а индексация выполнялась в фоновом режиме?
Я использую Amazon RDS, поэтому у меня нет возможности просто загрузить на другой сервер, а затем скопировать файлы таблицы.
Добавляю к этому бонус, поскольку я все еще хочу посмотреть, есть ли способ получить хорошую производительность при индексации в определенном поле.
Комментарии:
1. Пожалуйста, запустите SHOW CREATE TABLE tblname G для загружаемой таблицы. Нам нужно посмотреть, какой механизм хранения используется для таблицы. Пожалуйста, покажите нам файл /etc /my.cnf.
2. В настоящее время я использую myisam, но я также пробовал innodb. Я открыт для использования любого из них. Опять же, большая проблема заключается в попытке ограничить индекс, а не в попытке повысить производительность.
3. неясно, как часто вы выполняете эту операцию, но вы, кажется, предполагаете, что это одноразовый. Из любопытства, в чем проблема простоя около 30 минут? Разве это нельзя было выполнить в периоды низкого использования (ночное время / обеденное время)?
4. Загрузка данных должна происходить не реже одного раза в неделю, но иногда и чаще одного раза в день. Мне все равно, если они займут 6 часов, я просто хочу ограничить их, чтобы они не влияли на остальные запросы.
Ответ №1:
Ну, я так и не нашел способа ограничить, но я нашел способ облегчить мою проблему. Решение было уникальным для моей проблемы, но я опубликую его на случай, если кто-то еще сочтет его полезным.
Я написал класс с именем CautiousIndexer .
- Сначала я сохранил инструкцию create table, чтобы воссоздать структуру таблицы без индексов. Я сохранил массив считываемых подчиненных баз данных, прошелся по ним, переименовав таблицу с неиндексированными данными в
prevent_indexing_($name). - Затем я запустил инструкцию create table только для подчиненных устройств. Это эффективно убрало данные с пути операторов индексации, которые выполнялись бы на главном сервере.
- Затем я запустил индексный запрос к основному. Чтение подчиненных устройств не повлияло на производительность, пока мастер индексировал, потому что вновь созданные таблицы были пустыми.
- Когда ведущее устройство завершило индексацию, я вывел 1 из подчиненных устройств из производственной ротации, удалил пустую таблицу, вернул полную таблицу на место, затем проиндексировал таблицу на нерабочем ведомом устройстве.
- Когда это закончилось, я вернул его в рабочее состояние и повторил процедуру индексации подчиненных устройств на оставшихся подчиненных устройствах.
- Когда все подчиненные устройства были проиндексированы, я запустил таблицу в производство.
С точки зрения эффективности это все еще было нормально, но во время индексации на главном сервере производительность записи была неприемлемо снижена. Все еще ищу способ индексирования с регулированием.
Комментарии:
1. Это решение помогло, но я обнаружил, что этого недостаточно. Некоторые запросы по-прежнему требуют прямого доступа к главной базе данных для транзакций, и они недопустимо замедляются во время индексации.
Ответ №2:
Хорошим решением для этого является скрипт, который выполняет непрерывное обновление. Вы бы применили индекс к каждому подчиненному устройству нереплицирующимся образом. Грубая иллюстрация:
for host in $hosts
do
mysql -h $host -e "STOP SLAVE;
SET sql_log_bin=0;
FLUSH TABLE t;
ALTER TABLE t ADD INDEX a (b,c);
SET sql_log_bin=1;
START SLAVE;"
done
Отключив репликацию, следует уменьшить объем дисковой активности и увеличить скорость операции индексации. Если у вас есть требования к задержке базы данных для ваших подчиненных устройств, вы можете полностью удалить пул подчиненных устройств и включить логику для повторного пула подчиненных устройств, когда он возобновит работу с задержкой в ноль секунд.
Комментарии:
1. В настоящее время я делаю почти именно это, но этого все еще недостаточно. Индексация данных на главном сервере приводит к тому, что .5% запросов, которым требуются основные данные, выполняются слишком медленно. Мне нужен способ ограничить индексацию, чтобы она могла индексироваться, оставаясь при этом отзывчивой к другим таблицам.
2. Зак, у тебя есть отличный бизнес-кейс для покупки большего количества оборудования! Другая возможность — выполнить индексацию таблицы на подчиненном устройстве, скопировать ее в master, а затем переименовать ее следующим образом
SET sql_log_bin=0; flush table t; rename t to dugout_t, t_atbat to t; SET sql_log_bin=1;3. Я вроде как в ловушке 22. Я перешел на RDS, чтобы избежать расходов системного администратора, но платить больше за машинный час. Однако RDS не позволит вам переместить индекс или таблицу на место, потому что у вас нет прямого доступа к файловой системе. Если я увеличу размер своей базы данных в RDS, я потеряю всю экономию на расходах системного администратора, поскольку базы данных multiAZ, которые я использую, действительно начинают дорожать!
4. Это также можно решить с помощью политики и обмена сообщениями с клиентами. Есть ли у вашего сайта конкретное соглашение SLA с клиентами? Подумайте о создании «уведомления об обслуживании сайта» на страницах входа ваших клиентов, предупреждающего их об ухудшении качества работы или об отключении сервиса, затем выполните неприятные действия в дату и время, о которых вы их предупредили.
Ответ №3:
Это не точное решение, которое вы ищете, но вы можете запустить второй экземпляр mysqld в качестве ведомого в этом единственном окне и перенаправлять SELECT запросы к нему по мере необходимости. Существует MySQL Proxy, который может помочь вам выполнить это без перезаписи клиентских приложений.
Вы также можете почерпнуть некоторые идеи из использования MySQL в FriendFeed. Они хранят фактические индексы в других таблицах и используют их для поиска. Если вы сохраните копию своих данных в другой таблице даже на другом сервере и запустите там индексы, вы сможете получить доступ к основным данным как можно скорее на полной скорости и получать более быстрые запросы позже, используя другой сервер.
Это похоже на то, как если бы вы добавили индексы на подчиненном устройстве для запросов типа поиска и запускали только поиск по первичному ключу на главном.
Комментарии:
1. Это было бы прекрасно, если бы мне не нужно было также обрабатывать транзакции. Мне нужен мастер, чтобы я мог писать в него. Во время индексации слишком сильно замедляются операции записи (в других таблицах).
2. Я добавил заметку о том, что на самом деле они сделали в FriendFeed.
Ответ №4:
Идея, которую ранее не пробовали, также не касается регулирования индекса, что, если вы создадите резервную таблицу и обновите ее способом, о котором вы упомянули, с меньшим промежутком времени, чем преобразование / переименование таблиц. Я рекомендую написать свои мысли, потому что вам нужно знать способ.
Ответ №5:
Вы можете отключить любые неуникальные индексы при вставке и повторно включить их после завершения. Взгляните на клавиши отключения / включения. Но это работает только для неуникальных индексов.
Вы также можете ускорить вставки, если используете инструкции insert с несколькими значениями (вставить в таблицу (…) значения(…),(…),(…)…
Кстати, загрузка данных в файл, кажется, самый быстрый способ вставить много данных в mysql.
Комментарии:
1. Да, я могу загрузить все данные примерно за 5 минут с отключенными ключами.. Но когда я включаю keys .. происходит индексация! Это то, что снижает производительность моей базы данных.
Ответ №6:
Вы пробовали увеличить настройки индекса для импорта? Это может значительно повысить производительность импорта. sort_buffer_size — для любого типа таблицы, myisam_sort_buffer_size — для таблиц MyISAM. innodb_buffer_pool_size — это своего рода ваш «кэш ключей» для Innodb. Увеличьте их для импорта в зависимости от вашего типа таблицы. То, что вы пытаетесь сделать, это избежать сортировки файлов во время создания индекса.
Возможно, вам удастся сократить время импорта / индексации до 10-15 минут или меньше. Это не регулирование, но это значительно сократит период воздействия.
Или, если вы используете таблицы MyISAM, возможно, таблица слияния является вариантом? Создайте новую таблицу, выполните импорт, затем добавьте новую таблицу в таблицу СЛИЯНИЯ. Во время импорта не будет никакого воздействия на базу данных. Помимо сервера, выполняющего задачу.