[MySQL] Замедление поиска Опции

[Kreon]

Не совсем уверен, к какому разделу относится этот вопрос, - остановил выбор на этом.

Имеется системник P2-400, 192MB памяти, с осью FreeBSD-6.2 и MySQL 5.0.45, собранной со следующими параметрами:

Код

CFLAGS="-O6 -fomit-frame-pointer" \
CXX=gcc \
CXXFLAGS="-O6 -fomit-frame-pointer \
-felide-constructors -fno-exceptions -fno-rtti" \
./configure \
--enable-assembler \
--with-mysqld-user=mysql \
--with-mysqld-ldflags="-all-static" \
--with-unix-socket-path=/tmp/mysql.sock \
--prefix=/usr/local/mysql \
--with-charset=cp1251 \
--with-collation=cp1251_general_ci \
--with-extra-charset=all

Конфиг взят стандартный my-medium.cnf.
В базе содержится достаточно крупная таблица (~12 мегабайт), в которой изредка необходимо делать частичный поиск по колонке TEXT - т.е. поиск вида like '%somestring%', куда индексы видимо запихнуть нельзя ни с какой стороны.
Суть проблемы: периодически по непонятным причинам катастрофически в ~10 раз замедляется этот поиск.
В нормальном случае это выглядит так:

Код

mysql> select * from sometable where col like '%string%';
Empty set (0.71 sec)

В ненормальном, соответственно:

Код

mysql> select * from sometable where col like '%string%';
Empty set (9.41 sec)

Учитывая, что в программе, где это используется, всего одно подключение к MySQL и подобные запросы блокируют все остальные на эти самые 10 секунд, оставлять это в подобном виде совсем не есть правильно.
Лечится такое судя по всему либо через flush tables (хотя не факт, может так совпало время), либо ожиданием, т.к. через какое-то время скорость восстанавливается.
Я так понимаю, это заканчивается объем в каком-то из буферов. Вопрос только вот - в каком? Пробовал осторожно менять значения некоторых переменных типа key_buffer_size, myisam_sort_buffer_size, query_cache_limit, найденных по слову %buffer% или %cache% - это (вроде бы) ни к чему не привело.
Посоветуйте, что ещё попробовать потыкать.

[leah]

Посоветуйте, что ещё попробовать потыкать.

Построить индекс на столбец col, добавить в запрос еще какой-нибудь критерий (по этому стобцу тоже хорошо бы индекс иметь). А лучше всего прикрутить полнотекстовый поиск (тоже индекс) как описано здесь ]]>http://jeremy.zawodny.com/blog/archives/000576.html]]> (сцылка внешняя). Все эти способы немного разные и по производительности и по нагрузке. Но если у тебя именно такой запрос как ты написал, то тогда полнотекстовый поиск тебя спасет.

А что касается "зависания" базы на момент запроса, то используй пул соединений к базе и держи несколько открытых коннектов (здесь все зависит от языка программирования....)

Сообщение отредактировано leah - 30.08.2007 - 12:10

[Kreon]

Как его построить?
Колонка col имеет тип TEXT, преобразовать в VARCHAR её нельзя, т.к. не поместится. Других критериев запроса тоже нет, т.к. требуется искать записи, где совпадает только кусок строки с колонкой col. Как видно из документации и EXPLAIN'а, когда в поиске частичного совпадения запрос начинается с "%", никакие индексы не используются.
Полнотекстовый же индекс я пробовал прикрутить, - либо я не понял его работы, либо это не то, что мне нужно. Проблема заключается в том, что нужно искать ЧАСТЬ строки. Т.е. от запроса требуется найти запись, где совпадет и "s is the part of the stri", а не обязательно целиком "this is the part of the string". А этот индекс ищет только по целым словам.

А вопрос, имхо, не совсем в индексах. База без индексов ищет за 0.7-1.0 секунды до тех пор, пока не закончится какой-то из буферов. И может быть, если его увеличить - это и решит проблему. Только вот - какой это буфер?

Насчет пула соединений - для работы с mysql используется уже написанный модуль, в котором такого функционала нет. А переписывать его сейчас особо нет и времени, да и знаний, сдается, не хватит. Пока похоже остается только писать flush tables перед поиском, благо искать приходится редко.

И небольшое дополнение.
Путем экспериментов со всеми возможными действиями над таблицей обнаружено, что лаг как минимум начинается после выполнения DELETE на таблице sometable и сразу прекращается после выполнения FLUSH TABLE sometable. На лаг не влияет выполнение попутного FLUSH TABLES'у запроса RESET QUERY CACHE - да и вообще вроде бы QUERY CACHE по умолчанию отключен, - и при таком же SELECT'e из копии этой таблицы в другой БД того же сервера никаких замедлений нет.
Да и вообще, та же самая программа на другом компьютере с гигабайтом оперативки и несколькогигагерцевом процессоре никаких замедлений не дает никогда.

[Kreon]

Спасибо всем за столь оперативную и подробную помощь.
Решение проблемы:
Следовало использовать для удаления записей из таблицы запрос

SQL

DELETE FROM sometable WHERE id = '$id';

вместо

SQL

DELETE sometable FROM sometable WHERE id = '$id';

Таким образом никакие задержки не появляются.
Если же использовать второй вариант - то время выполнения SELECT'ов увеличивается в ~10 раз до тех пор, пока не будет выполнен запрос

SQL

FLUSH TABLE sometable;

Объяснил бы ещё кто, почему так.