26
MS SQL Full-Text Search vs Sphinx
Программирование
Tagged Under : contains, full text, full text search, Microsoft, ms sql, search, sphinx, sql, поиск
Собственно, к сравнению меня подвигла довольно длинная предыстория, если кратко, то в сети достаточно просто найти сравнения различных средств организации полнотекстового поиска, но я так и не увидел в кандидатах подобных «соревнований» полнотекстовый поиск от Microsoft (он же MS SQL Full-Text Search), который я успешно использую для решения задач.
Почему Sphinx? Я давно слышу об этой поисковой системе, различные реализации поиска на базе Sphinx мне многократно приводили в качестве примера того, как должен работать поиск на тех сайтах, в разработке которых я участвовал.
Итак, начнём. Прежде всего поставим задачу: нужно в таблице улиц найти все записи соответствующие запросам. Текст запроса может быть полным названием улицы или же частью названия.
Что имеется в наличии:
- Машина с ОС Windows 7 Ultimate 64x;
- ОЗУ: 2Гб, ЦП: Intel Core 2 Duo T5470 1.6ГГц;
- MS SQL Server 2008 SP1;
- База (citiesdb) с таблицей улиц (Streets), количество записей – 823276;
- На таблице висит полнотекстовый индекс на поле Name (размер индекса 23 Мб).
Итак, прежде всего мне потребовалось установить сам Sphinx:
- Качаем версию 2.0.1-beta (Win32 binaries w/MySQL+PgSQL+libstemmer+id64 support)
- Распаковываем и содержимое переносим в папку c:sphinx (по крайней мере у меня такая)
- Настраиваем конфиг
- Строим индекс: indexer.exe –config c:sphinxsphinx.conf.in –all (если сервис searchd уже установлен и запущен добавляем –rotate)
- Устанавливаем сервис searchd: searchd.exe –install –config c:sphinxsphinx.conf.in –servicename sphinx
- Запускаем сервис из консоли: net start sphinx
{
type = mssql
sql_host = KOSFIZ-PC
sql_user = test
sql_pass = ******
sql_db = citiesdb
sql_port = 1433
mssql_unicode = 1
sql_query =
SELECT [ID], [Name]
FROM Streets
}
index StreetsIndex2
{
source = cities
path = f:indexiesstreets2
morphology = stem_enru
min_word_len = 2
charset_type = utf-8
min_prefix_len = 0
enable_star = 1
html_strip = 0
}
searchd
{
address = 127.0.0.1
port = 3312
log = c:sphinxlogsearchd.log
query_log = c:sphinxlogquery.log
read_timeout = 5
max_children = 0
pid_file = c:sphinxlogsearchd.pid
}
Размер индекса составил 15Мб с небольшим. Время ~ 6 с.
Для тестирования MS SQL Full-Text Search’а таблица была предварительно подготовлена: добавлен полнотекстовый индекс. И хранимая процедура:
@query nvarchar(400)
AS
BEGIN
select top 1000 ID, Name from Streets with(nolock)
where CONTAINS(Name, @query)
END
Отмечу, что при тестировании выборка будет из 1000 строк. FREETEXT я решил не использовать поскольку использование * с ним невозможно, да и к CONTAINS я прикипел всей душой.
Основные методы, используемые в тесте, выглядят следующим образом:
{
Stopwatch sw = new Stopwatch();
sw.Start();
using (ConnectionBase connection = new PersistentTcpConnection("localhost", 3312))
{
SearchQuery searchQuery = new SearchQuery(query);
searchQuery.MatchMode = MatchMode.Extended2;
searchQuery.Indexes.Add("StreetsIndex2");
searchQuery.Limit = 1000;
SearchCommand searchCommand = new SearchCommand(connection);
searchCommand.QueryList.Add(searchQuery);
searchCommand.Execute();
}
sw.Stop();
results.Add(sw.ElapsedMilliseconds);
}
private static void MSFullText(string query)
{
Stopwatch sw = new Stopwatch();
sw.Start();
DataTable dt = null;
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = conn;
cmd.CommandText = "GetStreets";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@query", query);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
dt = new DataTable();
adapter.Fill(dt);
}
}
sw.Stop();
results.Add(sw.ElapsedMilliseconds);
}
private static string PrepareString(string query)
{
return string.Format("FORMSOF(INFLECTIONAL, {0})", query);
}
private static string PrepareStringWithWildcard(string query)
{
return string.Format(""{0}"", query);
}
Для работы со Sphinx’ом использовал sphinx-dotnet-client.
Были проведены следующие тесты:
- Получение результатов по полному названию улицы (для чистоты эксперимента проводилось 10 прогонов). Тестовая строка «ленина». Приведено среднее время за 1 вызов.
MS SQL (CONTAINS) = 6,4 мс
Sphinx = 182,4 мс - Получение результатов по части названия улицы (для чистоты эксперимента проводилось 10 прогонов). Тестовая строка «ленинск*». Приведено среднее время за 1 вызов.
MS SQL (CONTAINS) = 4,5 мс
Sphinx = 189,3 мс - Получение результатов по полному названию улицы (для чистоты эксперимента проводилось 10 прогонов). Приведено среднее время за 10 вызов, поток 1. Набор из 10 строк предопределён.
MS SQL (CONTAINS) = 6,3 мс
Sphinx = 23,6 мс - Получение результатов по части названия улицы (для чистоты эксперимента проводилось 10 прогонов). Приведено среднее время за 10 вызов, поток 1. Набор из 10 строк предопределён.
MS SQL (CONTAINS) = 7,9 мс
Sphinx = 23,4 мс - Получение результатов по полному названию улицы (для чистоты эксперимента проводилось 10 прогонов). Приведено среднее время за 10 вызов, потоков 10. Набор из 10 строк предопределён.
MS SQL (CONTAINS) = 43 мс
Sphinx = 41,5 мс - Получение результатов по части названия улицы (для чистоты эксперимента проводилось 10 прогонов). Приведено среднее время за 10 вызов, потоков 10. Набор из 10 строк предопределён.
MS SQL (CONTAINS) = 30,6 мс
Sphinx = 40,9 мс - Получение результатов по полному названию улицы (для чистоты эксперимента проводилось 10 прогонов). Приведено среднее время за 10 вызов, потоков 10. На каждом вызове строка случайна из набора в 10000 строк, набор состоит из названий улиц, имеющихся в базе.
MS SQL (CONTAINS) = 41,7 мс
Sphinx = 44,2 мс - Получение результатов по части названия улицы (для чистоты эксперимента проводилось 10 прогонов). Приведено среднее время за 10 вызов, потоков 10. На каждом вызове строка случайна из набора в 10000 строк, набор состоит из названий улиц с добавлением * в конец слова, имеющихся в базе.
MS SQL (CONTAINS) = 43 мс
Sphinx = 47 мс
Проведём анализ полученных в ходе тестирования результатов.
В тестах 1 и 2 Sphinx проявил себя крайне плохо, вызвано это тем, что при первом запросе файл индекса помещается в память, т.е. попросту кешируется, но на это требуется значительное время. Хранимая процедура, используемая для Full-Text Search’а, будучи вызванной однажды уже скомпилирована и план запроса закэширован (правда сколько план выполнения пролежит в кэше заранее неизвестно), поэтому на вызов и получение данных затрачено минимальное время.
В тестах 3 и 4 явно видно снижение среднего времени запросов за результатами к Sphinx’у, что объясняется длительным первым запросом и быстрыми остальными. CONTAINS держится на том же временном уровне.
Оставшиеся тесты явно свидетельствуют о том, что при увеличении числа потоков начинает деградировать величина скорость обработки запросов, т.е. поиск начинает подтормаживать. Причём деградация наблюдается и для MS SQL Full-Text Search’а и для Sphinx’а. На коэффициент разный, скорость поиска у первого более сильно зависит от кол-ва потоков, примерно в два раза.
Далее я пробовал увеличивать кол-во потоков, что привело к ещё большему снижению скорости получения результатов, но Sphinx справлялся гораздо лучше с поставленной задачей.
И в тот момент, когда я уже начал смотреть в сторону Sphinx’а как в сторону поискового движка будущих проектов мне в голову пришла простая идея: раз полнотекстовый поиск от MS начинает загинаться при больших кол-вах одновременных запросов, но при этом показывает хорошие результаты, выполняя их по очереди, то почему бы не реализовать своеобразную многозадачность по известному сценарию: выполнять запросы от всех потоков по очереди, т.е. сначала один запрос от первого потока, потом от второго, затем третьего и т.д., потом по второму кругу, третьему и до конца.
В итоге такой подход оправдал себя: среднее время на 10 запросов вернулось к значениям 1 – 4 тестов. Добился я этого изменив метод MSFullText следующим образом:
private static void MSFullText(string query)
{
Stopwatch sw = new Stopwatch();
sw.Start();
DataTable dt = null;
lock(locker)
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = conn;
cmd.CommandText = "GetStreets";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@query", query);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
dt = new DataTable();
adapter.Fill(dt);
}
}
sw.Stop();
results.Add(sw.ElapsedMilliseconds);
}
Поступив аналогично для случая с Sphinx’ом, тоже удастся сократить среднее время поиска и приблизить его к среднему времени выдаваемому полнотекстовым поиском от MS.
Собственно, на этом я прекратил тестирование.
Итак, для чего же я для себя вынес из данного сравнения:
- Полнотекстовый SQL-поиск от MS при возрастании кол-ва одновременных потоков начинает сильно деградировать в скорости получения результатов, чуть меньше деградирует Sphinx. Но последний хак относительно многопоточности показывает обе технологии поиска в выгодном свете.
- Для редких одиночных запросов использовать Sphinx нет смысла, ибо он будет уступать полнотекстовому SQL-поиску от MS даже в случае, если план запроса не кеширован (100-140 мс);
- Использовать поиск от MS удобно тем, что последние изменения сразу же попадают в индекс при включенном Change Tracking’е, а в случае со Sphinx’ом надо делать какое-то расписание или надстройку по отслеживанию изменений в таблице;
- Sphinx криво работает с *. Например, по строке «лен*» ничего не найдёт, хотя в конфиге явно прописано min_prefix_len = 0. Зато для «ленинск*» отрабатывает корректно;
- Оба метода поиска требуют предварительной обработки строки поиска, например, Sphinx падает при наличии в строке поиска /. Для учёта морфологии CONTAINS’у нужно прописать FORMSOF и INFLECTIONAL;
- Sphinx при нагрузке загружает ЦП на 100% при этом много времени начинает отъедать ядро ОС, с MS SQL Full-Text Search ситуация совсем другая, что не может не радовать.
Первый пик – MS SQL Full-Text Search, второй – Sphinx.
Для наблюдательных отмечу: да, вы не ошиблись Sphinx пробежался быстрее и среднее время запроса составило 3 мс в то время как MS SQL Full-Text Search отработал в среднем за 5 мс на запрос. Общее кол-во запросов 4000, что соответствует интервалу времени 12-20 секунд.
6. При 40 потоках полнотекстовый поиск от MS сильно затормозил, Sphinx оборвал соединения и не принимал новые;