InnoDB полнотекстовый поиск в MySQL 5.6: часть 3, производительность

Это третья часть серии из трех статей, в которой рассматриваются новые функции полнотекстового поиска InnoDB в MySQL 5.6. Чтобы узнать о предыдущих частях, смотрите часть 1 или часть 2

Некоторые из вас могут вспомнить несколько месяцев назад, что я обещал третью часть в моей серии полнотекстового поиска (FTS) InnoDB, в которой я бы на самом деле посмотрел на производительность InnoDB FTS в MySQL 5.6 по сравнению с традиционной MyISAM FTS. Я не планировал такого разрыва между частью 2 и частью 3, но, как говорится, лучше поздно, чем никогда. Напомним, что мы работали с двумя наборами данных, один из которых я называю SEO (веб-страницы, заполненные 8000 ключевыми словами), а другой — DIR (записи каталога 800K), и мы сравниваем MyISAM FTS в MySQL 5.5.30 с InnoDB FTS в MySQL 5.6.10.

Для справки, хотя на самом деле это не то, что я бы назвал тестовым прогоном, используемая здесь платформа — это Core i7-2600 3,4 ГГц, 32 ГБ ОЗУ и 2 твердотельных накопителя Samsung 256 ГБ 830 в RAID-0. Операционная система — CentOS 6.4, а файловая система — XFS с dm-crypt / LUKS. Все настройки MySQL являются их соответствующими значениями по умолчанию, кроме innodb_ft_min_token_size, который установлен в 4 (вместо значения по умолчанию 3), чтобы соответствовать заданному по умолчанию в MyISAM ft_min_word_len.

Также напомним, что определение таблицы для набора данных DIR:

CREATE TABLE dir_test (
  id INT UNSIGNED NOT NULL PRIMARY KEY,
  full_name VARCHAR(100),
  details TEXT
);

Определение таблицы для набора данных SEO:

CREATE TABLE seo_test (
 id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
 title VARCHAR(255),
 body MEDIUMTEXT
);

Загрузка таблицы / создание индекса

Во-первых, давайте попробуем загрузить данные и создать наши индексы FT за один проход — то есть мы создадим индексы FT как часть самого исходного определения таблицы. В частности, это означает добавление «FULLTEXT KEY ( full_name, details)» в наши таблицы DIR и добавление «FULLTEXT KEY ( title, body)» в таблицы SEO. Затем мы удалим эти таблицы, удалим наш файловый кеш, перезапустим MySQL и попробуем тот же процесс в два этапа: сначала мы загрузим таблицу, а затем сделаем ALTER для добавления индексов FT. Все время в секундах.

Интересно. Что касается MyISAM, мы можем сказать, что на самом деле не имеет большого значения, каким образом вы будете действовать, поскольку числа из однопроходной нагрузки и двухпроходной нагрузки находятся в пределах нескольких процентов друг от друга, но для InnoDB мы смешанное поведение. С меньшим набором данных SEO имеет больше смысла делать это в однопроходном процессе, но при большем наборе данных DIR двухпроходная загрузка намного быстрее.

Напомним, что при добавлении первого индекса FT в таблицу InnoDB сама таблица должна быть перестроена для добавления столбца FTS_DOC_ID, поэтому я подозреваю, что размер таблицы при ее перестроении во многом связан с разницей в производительности на меньший набор данных. Набор данных SEO полностью помещается в буферный пул, а набор данных DIR — нет. Это также говорит о том, что стоит сравнить время, необходимое для добавления второго индекса FT (на этот раз мы просто проиндексируем поле TEXT / MEDIUMTEXT каждой таблицы). Пока мы на этом, давайте посмотрим на время, необходимое для падения второго индекса FT также. Опять все время в секундах.

InnoDB побеждает во втором тесте. Я бы приписал выигрыш InnoDB здесь отчасти из-за того, что нет необходимости перестраивать всю таблицу со вторыми (и последующими) индексами, но также к тому факту, что по крайней мере некоторые данные InnoDB уже были в буферном пуле с момента создания первого индекса FT , Кроме того, мы знаем, что InnoDB обычно очень быстро удаляет индексы, в то время как MyISAM требует перестройки файла .MYI, поэтому победа InnoDB в тесте на сброс не удивительна.

Производительность запросов

Вспомните запросы, которые использовались в предыдущем посте из этой серии:

1. SELECT id, title, MATCH(title, body) AGAINST ('arizona business records'
   IN NATURAL LANGUAGE MODE) AS score FROM seo_test_{myisam,innodb} ORDER BY 3
   DESC LIMIT 5;
2. SELECT id, title, MATCH(title, body) AGAINST ('corporation commission forms'
   IN NATURAL LANGUAGE MODE) AS score FROM seo_test_{myisam,innodb} ORDER BY 3 DESC
   LIMIT 5;
3. SELECT id, full_name, MATCH(full_name, details) AGAINST ('+james +peterson +arizona'
   IN BOOLEAN MODE) AS score FROM dir_test_{myisam,innodb} ORDER BY 3 DESC LIMIT 5;
4. SELECT id, full_name, MATCH(full_name, details) AGAINST ('+james +peterson arizona'
   IN BOOLEAN MODE) AS score FROM dir_test_{myisam,innodb} ORDER BY 3 DESC LIMIT 5;
5. SELECT id, full_name, MATCH(full_name, details) AGAINST ('"Thomas B Smith"'
   IN BOOLEAN MODE) AS score FROM dir_test_{myisam,innodb} ORDER BY 3 DESC LIMIT 1;

Запросы выполнялись последовательно сверху вниз, всего по 10 раз каждый. Вот результаты в табличном формате:

Не много различий во времени выполнения для данного запроса, так что это хорошо, но InnoDB всегда возвращается медленнее, чем MyISAM. В общем, я не удивлен, что MyISAM работает быстрее; это простой однопоточный, только для чтения тест, так что ни одна из областей , где InnoDB блестит (например, одновременное чтение / запись) не в настоящее время осуществляется здесь, но я буду очень удивлен запросами # 3 и # 5, где InnoDB только курит.

Я запустил обе версии запроса 5 с включенным профилированием, и по большей части время, проведенное в каждом состоянии запроса, было одинаковым для версий запроса InnoDB и MyISAM, за одним исключением.

InnoDB: | Создание индекса сортировки | 0.626529 |
MyISAM: | Создание индекса сортировки | 0,014588 |

Вот где основная часть времени выполнения. Согласно документам , это состояние потока означает, что поток обрабатывает SELECT, для которого требуется внутренняя временная таблица. Хорошо, конечно, это имеет смысл, но на самом деле это не объясняет, почему InnoDB занимает так много времени, и вот тут все становится немного интереснее. Если вы вспомните часть 2 из этой серии, запрос 5 фактически вернул 0 результатов при запуске с InnoDB с конфигурацией по умолчанию из-за среднего начального значения «B», и мне пришлось установить innodb_ft_min_token_size в 1, чтобы получить результаты обратно. Ради полноты я сделал это здесь снова, затем перезапустил сервер и пересоздал свой индекс FT. Результаты, достижения? Время выполнения сократилось на 50%, а «Создание индекса сортировки» даже не появилось в профиле запроса:

mysql [localhost] {msandbox} (test): SELECT id, full_name, MATCH(full_name, details) AGAINST
('"Thomas B Smith"' IN BOOLEAN MODE) AS score FROM dir_test_innodb ORDER BY 3 DESC LIMIT 1;
+-------+----------------+-------------------+
| id    | full_name      | score             |
+-------+----------------+-------------------+
| 62633 | Thomas B Smith | 32.89915466308594 |
+-------+----------------+-------------------+
1 row in set (0.31 sec)
mysql [localhost] {msandbox} (test): show profile;
+-------------------------+----------+
| Status                  | Duration |
+-------------------------+----------+
| starting                | 0.000090 |
| checking permissions    | 0.000007 |
| Opening tables          | 0.000017 |
| init                    | 0.000034 |
| System lock             | 0.000012 |
| optimizing              | 0.000008 |
| statistics              | 0.000027 |
| preparing               | 0.000012 |
| FULLTEXT initialization | 0.304933 |
| executing               | 0.000008 |
| Sending data            | 0.000684 |
| end                     | 0.000006 |
| query end               | 0.000006 |
| closing tables          | 0.000011 |
| freeing items           | 0.000019 |
| cleaning up             | 0.000003 |
+-------------------------+----------+

Гектометр Это немного медленнее, чем MyISAM, но намного быстрее, чем раньше. Причина в том, что он быстрее, потому что он нашел точное совпадение, и я попросил только одну строку, но если я изменю LIMIT 1 на LIMIT 2 (или ограничиваю N> 1), то «Создание индекса сортировки» возвращает к значению примерно 0,5 0,6 секунды, и «инициализация FULLTEXT» остается на 0,3 секунды. Таким образом, это отвечает на еще один давний вопрос: использование более низкого значения innodb_ft_min_token_size (ifmts) значительно влияет на производительность и может работать на вас или против вас, в зависимости от ваших запросов и количества строк, которые вы ищете. Время, затрачиваемое на «Создание индекса сортировки», не сильно меняется (возможно, 0,05 с) между ifmts = 1 и ifmts = 4, но время, затрачиваемое на инициализацию FULLTEXT с ifmts = 4, обычно составляет всего несколько миллисекунд, в отличие от 300 мс видели здесь.

Наконец, я попытался поэкспериментировать с различными размерами пула буферов, временными размерами таблиц, размерами буферов для каждого потока, а также попытался перейти с антилопы (ROW_FORMAT = COMPACT) на барракуду (ROW_FORMAT = DYNAMIC) и переключить наборы символов с utf8 на latin1, но ничто из этого не имело никакого значения. Единственное, что, казалось бы, немного улучшило производительность — это обновление до 5.6.12. Время выполнения запросов InnoDB FTS в 5.6.12 было примерно на 5-10 процентов быстрее, чем в 5.6.10, и запрос № 2 фактически выполнялся немного лучше в InnoDB, чем MyISAM (среднее время выполнения на 0,00075 секунды быстрее), но кроме что MyISAM по-прежнему выигрывает в сырой производительности SELECT.

Спустя три сообщения в блоге, каково мое общее мнение о InnoDB FTS в MySQL 5.6? Я не думаю, что это здорово, но это исправно. Производительность запросов BOOLEAN MODE определенно оставляет желать лучшего, но я думаю, что InnoDB FTS удовлетворяет потребность тех людей, которые хотят функции и возможности InnoDB, но не могут модифицировать свои существующие приложения или у которых просто недостаточно трафика FTS. чтобы оправдать создание решения на основе Sphinx / Solr / Lucene.