Как ускорить вставку, если много индексов?

Question

[Андрей Суржиков]

MySQL, таблица InnoDB.
В ней 37 столбцов; 23 млн записей (суммарно 13.2 ГиБ)

Из-за необходимость быстрого поиска ПО ВСЕМ столбцам таблицы (в этом смысл проекта) - пришлось допустить один из смертных грехов программиста - и почти на все поля сделать индексы. Итого получилось 18 простых индексов и 10 фуллтекст :(
Скорость извлечения данных - стала удовлетворительной. Но раз в неделю надо обновлять данные (примерно 10000 записей) - и это происходит мучительно долго.

Запросы на сохранение происходят быстро, но вставка индексов очень долго.
insert/update запрос присходит быстро, но через какое-то время начинается ошибка SQLSTATE[HY000] [2002] Connection refused. В итоге записи вообще не сохраняются.

Какие способы решить такую проблема?

Answer 1

[Philipp]

Чтобы решить эту проблему, нужно выбросить MySQL и использовать колоночные СУБД, которые разработаны специально для таких задач.
Смотрите в сторону Vertica, Cassandra, HBase.

Comments

[Евгений Одинец]

Соглашусь с комментарием выше. Либо у вас проблемы с архитектурой, либо проблемы с выбором иструментов для решения ваших задач.

[Fortop]

Для таких - это каких?

[Philipp]

Fortop, поиск по большому количеству столбцов с записью и фоновым перестроением индексов.

[Fortop]

Philipp, только задачи у колоночных СУБД другие

[Philipp]

Fortop, какие же тогда, расскажите пожалуйста.

[Fortop]

Philipp,

• аналитика
  
• достаточно частые изменения структуры данных на больших объемах
  

У топикстартера агрегации нет, структура тоже устоявшаяся. То есть основной массы профита он просто не получит.

P.S. ну и статейку бы свою что кидали сами бы и почитывали. Там о применимости таких баз тоже написано

[Андрей Суржиков]

Philipp, с поисков все не плохо, единственная проблема - создание большого количества индексов при вставке.

[Андрей Суржиков]

Судя по статье Хайлода
можно подумать в сторону колоночных субд

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

[Philipp]

Андрей Суржиков, проанализируйте то, что действительно используется.
Если какие-то текствовые данные повторяются, например какая-нибудь форма юридического лица, то ее легко можно либо вынести в отдельную таблицу, либо замапить прямо в коде, а в базу вместо текста ложить какой-нибудь беззнаковый byte. В результате такой индекс займет в тысячу раз меньше места.
Также иногда можно вместо 10 полнотекстовых отдельных индексов создать один по составному полю.
Если вам приходится всталять много за раз, то делаете это пачкой обернув в одну транзакцию.

Answer 2

[Fortop]

Для начала определитесь насколько вам нужны все поля.
Какая селективность индексов по ним.
Ведется ли агрегация, или же это просто выборки по условиям...

Если основная задача это полнотекстовый поиск с сопутствующими документам атрибутами, то загоните это все в Sphinx

Comments

[Андрей Суржиков]

Это плоская таблица Юрлиц.
Все поля нужны по требованию от заказчика.
По части полей - типа ИНН, ОГРН - поиск по обычным индексам
Для полного наименования - полнотекстовые..

[Fortop]

Андрей Суржиков, то есть агрегации нет.

Загоните для тестов в Sphinx, главное чтобы памяти на сервере хватало.
Если ранее им пользовались, то 2-3 часа вам хватит для понимания достаточно ли его возможностей в рамках ваших мощностей.

[Андрей Суржиков]

Fortop, как это решит производительность вставки?
Я ведь не смогу удалить все индексы, если подключу сфинкс?

[Philipp]

Андрей Суржиков, вы удалите полнотекстовые индексы. Это снизит нагрузку.

[Fortop]

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

Сфинкс достаточно быстр, если полнотекстовый поиск у вас используется значимо, а не в 1-2 случаях, то выигрыш будет большой.

Вставка новых данных в Сфинкс тоже достаточно быстра за счет дельта-индексов.
Мускль же у вас разгрузится по объему индексов, и если они не помещались в памяти, то могут начать помещаться (нужно смотреть конкретно систему), что тоже ускорит работу.