А!!! Спасите!!!! :D

Balancer, 27.04.2004 20:10:21 :

А вот, наконец, удалось впервые явно поймать "запрос-убийцу"

code mysql

1. SELECT p.pid FROM ib_posts p LEFT JOIN ib_bodies s ON (s.pid=p.pid)WHERE   p.forum_id IN (3) AND p.queued <> 1  AND ( LOWER(s.post) LIKE '%тб-7%' )

Время выполнения было 979 секунд, на время которых форумы, естественно, не отвечали.

С трудом представляю, что тут можно придумать в плане оптимизации, кроме перевода всего на FULLTEXT-индексы. Есть мнения?

 

Мы на предыдущей работе ушли от LIKE %xxx% специально создав словарик и разбив текст на слова - почти собственный FULLTEXT индекс. Этот словарик получился не очень большим, а вот таблица связи - приличной. И все равно, поиск по словарю с помощью LIKE проходил достаточно быстро. Но для этого понадобились усилия по написанию процедур по разбиению текста на слова и постоянном пополнении базы.

По поводу запроса-убийцы.
Хотелось бы посмотреть по одной строчке из обеих таблиц для примера.
В ib_bodies колонка post - это наверное текст сообщения?
А какой объем этой таблицы?

Надо бы сказать, что здесь вполне реально время порядка 1000 сек при условии, что таблица размером в гиг, потому что ... LOWER(s.post) LIKE ....
И ничего тут не поделаешь (Поиск должен быть case-insensitive, и LOWER не выкинешь). На пока можно использовать ограничение глубины поиска, например с использованием LIMIT (Я не уверен насчет точного синтаксиса, а проверить негде):
SELECT p.pid FROM ib_posts p LIMIT 100 LEFT JOIN ib_bodies s ON (s.pid=p.pid)WHERE   p.forum_id IN (3) AND p.queued <> 1  AND ( LOWER(s.post) LIKE '%тб-7%' )

Неплохо бы еще и приоритет такого поиска понизить. По типу как в:
insert LOW_PRIORITY ...

Попозже подумаю, сейчас время поджимает

SEA, 28.04.2004 19:09:54 :

По поводу запроса-убийцы.
Хотелось бы посмотреть по одной строчке из обеих таблиц для примера.

 

Сперва структура:

1. CREATE TABLE `ib_posts` (
2.   `append_edit` tinyint(1) default '0',
3.   `edit_time` int(10) default NULL,
4.   `pid` int(10) NOT NULL auto_increment,
5.   `ubb_topic` varchar(11) default NULL,
6.   `ubb_post` int(11) default '0',
7.   `author_id` mediumint(8) NOT NULL default '0',
8.   `author_name` varchar(32) default NULL,
9.   `use_sig` tinyint(1) NOT NULL default '0',
10.   `use_emo` tinyint(1) NOT NULL default '0',
11.   `ip_address` varchar(16) NOT NULL default '',
12.   `post_date` int(10) NOT NULL default '0',
13.   `icon_id` smallint(3) default NULL,
14.   `queued` tinyint(1) default NULL,
15.   `topic_id` int(10) NOT NULL default '0',
16.   `forum_id` smallint(5) NOT NULL default '0',
17.   `attach_id` varchar(64) default NULL,
18.   `attach_hits` int(10) default NULL,
19.   `attach_type` varchar(128) default NULL,
20.   `attach_file` varchar(255) default NULL,
21.   `post_title` varchar(255) default NULL,
22.   `new_topic` tinyint(1) default '0',
23.   `edit_name` varchar(255) default NULL,
24.   `post_type` varchar(4) default NULL,
25.   PRIMARY KEY  (`pid`),
26.   KEY `topic_id` (`topic_id`,`author_id`),
27.   KEY `author_id` (`author_id`),
28.   KEY `forum_id` (`forum_id`,`post_date`),
29.   KEY `pid` (`pid`)
30. ) TYPE=MyISAM DEFAULT CHARSET=utf8

1. CREATE TABLE `ib_bodies` (
2.   `pid` int(10) NOT NULL default '0',
3.   `post` text,
4.   UNIQUE KEY `pid_2` (`pid`),
5.   KEY `pid` (`pid`)
6. ) TYPE=MyISAM DEFAULT CHARSET=utf8

Теперь примеры:
(сейчас, блин, свернётся...)

1. +-------------+-----------+-----+-----------+----------+-----------+---------------------------+---------+---------+------------+------------+---------+--------+----------+----------+-----------+-------------+-------------+-------------+------------+-----------+-----------+-----------+
2. | append_edit | edit_time | pid | ubb_topic | ubb_post | author_id | author_name               | use_sig | use_emo | ip_address | post_date  | icon_id | queued | topic_id | forum_id | attach_id | attach_hits | attach_type | attach_file | post_title | new_topic | edit_name | post_type |
3. +-------------+-----------+-----+-----------+----------+-----------+---------------------------+---------+---------+------------+------------+---------+--------+----------+----------+-----------+-------------+-------------+-------------+------------+-----------+-----------+-----------+
4. |           0 |      NULL |   1 | 0         |        0 |         1 | Invision Power Board Team |       0 |       1 | 127.0.0.1  | 1051519549 |       0 |      0 |        1 |        1 |           |           0 |             |             | NULL       |         1 | NULL      |           |
5. +-------------+-----------+-----+-----------+----------+-----------+---------------------------+---------+---------+------------+------------+---------+--------+----------+----------+-----------+-------------+-------------+-------------+------------+-----------+-----------+-----------+

1. ---------------------------------------------------+
2. |   1 | Welcome to your new Invision Power Board!..|
3. +-----+--------------------------------------------+

>В ib_bodies колонка post - это наверное текст сообщения?

Наоборот. ib_post - это вся инфа о сообщение, ib_bodies - текст сообщений. Изначально были вместе, текст шёл вместе с другой инфой, но при 500Мб базе при сортировках начало уходить в своп и тормозить. Пришлось тексты вынести в отдельную таблицу и JOIN'ить в тех запросах, где нужно.

>А какой объем этой таблицы?

336 тыс. записей, 478Мб.

>Надо бы сказать, что здесь вполне реально время порядка 1000 сек при условии, что таблица размером в гиг, потому что ... LOWER(s.post) LIKE ....
И ничего тут не поделаешь

Дык, оно понятно. Я и хочу давно поиск на FULLTEXT перевести, но не переводится! При создании в лоб система не заканчивает его создание даже за 70000 секунд (19часов). Пытался я переносить по одной записи в готовую пустую таблицу, через трое суток (ненепрерывной) работы при очередной записи произошёл сбой, а REPAIR такой большой таблицы уходит в себя с теми же симптомами, что и создание индекса.

На самом деле - глюк с этими FULLTEXT. Таблица со страницами Авибазы на 5000 записей и 25Мб после пары вставок записей по 15..20кБ отваливается и лечится только REPAIR'ом (минут по 10 идёт). Чтобы снова отвалиться при очередной паре вставок.

>(Поиск должен быть case-insensitive, и LOWER не выкинешь).

Ну, на старом форуме я держал просто тупо параллельный "индекс" переведённый в lower и с выброшенными знаками препинания. Но тут хочется понять, что за проблемы с FULLTEXT.

>На пока можно использовать ограничение глубины поиска, например с использованием LIMIT

Боюсь, что не поможет, т.к. результатов всё равно обычно не так уж много.

>Неплохо бы еще и приоритет такого поиска понизить. По типу как в:
insert LOW_PRIORITY ...

Думаю, не поможет. Механизм затыка такой: MySQL начинает долгий поиск по базе. На это время изменения базы блокируются (что и понятно). Пока идёт чтение - всё ок. Как только кто-то попытался что-то записать, всё, остальные запросы лочатся, т.к. им нужно же читать уже изменённые данные... И все дружно ждут пока закончится поиск.

>Попозже подумаю, сейчас время поджимает

Угу, спасибо.

Лечится. Только не так просто. Поскольку уже сталкивался с такой проблемой. Ускорение достигнуто на два порядка. Только надо поработать.
Выкладываю в том варианте, как мы решали.

Вариант 1.
Самый легкий - за счет дополнительной памяти. Много не даст, но даст возможность быстро попробовать. В ib_body или в отдельной таблице храниться копия текста - только заранее преобразованная в lower case. Памяти в два раза больше тратиться, insert немного помедленнее, но LOWER(s.post) уходит из запроса, так как делается раз и навсегда.

Вариант 2.
Создается словарь, где слова это первые несколько букв. У нас было 4 для английского. Описывать подробнее не буду, т.к. практически эквивалентен следующему варианту, а именно он дал выигрыш.

Вариант 3.
Создаются следующие 2 таблицы:

1. CREATE TABLE `dictionary`
2. (
3.   `id` int(10) NOT NULL auto_increment,
4.   `word` varchar(32),
5.   PRIMARY KEY  (`id`)
6.   UNIQUE KEY `id_2` (`word`)
7. ) TYPE=MyISAM DEFAULT CHARSET=utf8
8.  
9. CREATE TABLE 'wordlink'
10. (
11.   'word_id' NOT NULL,
12.   'body_id' NOT NULL,
13.   KEY 'word_first ('word_id'),
14.   KEY 'body_first'('body_id')
15. ) TYPE=MyISAM

Теперь, пишеться небольная дописка к форуму. Каждый раз при новом посте, пост разбирается на слова, которые приводятся к lowercase и все вставляются в словарь. Поскольку там есть unique key, то ошибки дубляжа можно не обрабатывать. Слова (как определит их Рома) будут входить один раз. Количество слов будет велико, но умеренно велико - с учетом грамматических ошибок. Далее поиск будет идти не по LIKE %xxx%, а по LIKE xxx% - что гораздо быстрее. Иными словами - никаких поисков по подслову. Чаще всего он и не нужен. Это самая главная часть. После этого убийца примет вид (c учетом просто join-ов):

1. SELECT p.pid
2. FROM ib_posts p, dictionary d, wordlink l
3. WHERE d.word LIKE 'тб-7%'
4.   AND l.word_id = d.id
5.   AND l.body_id = p.pid
6.   AND p.forum_id IN (3)
7.   AND p.queued <> 1

Вставка будет выглядеть как:
1. принять сообщение
2. вставить сообщение
3. получить pid сообщения.
4. разбрать на слова.
5. каждое слово найти словаре - с его id. Если слова нет, то вставить и получить его id. Создать запись в wordlink пары (id,pid).

Предыдущее содержимое базы обрабатывается аналогично. Правила по разборке текста на слова должны быть одинаковы и для поиска (когда пользователь ввел строку для поиска) и для разбиения поста и занисения его в словарь.

Я пол-года убил, чтобы убедить наших сделать так. Работает до сих пор с 1997 года.

Миш, метод твой известен, но, увы, не позволяет искать точную подстроку
Я когда-то хотел попробоват его модифицированный вариант - кроме таблицы слов заводить ещё и бинарную таблицу их расположения в статье. Т.е. в каждом постинге все слова меняются на их цифровые индексы. Это будет помедленнее и потребует ещё больше памяти, но зато сохраняется структура документа. И подстроки искать можно, и частотный анализ проводить.

Но пока придётся попробовать с дублем с LOWER.

Кстати, при разбиении на слова много непоняток. У нас очень специфические слова. К примеру, Harrier GR Mk.3 какой-нибудь хорошо как целое слово искать. Вообще, алгоритм разбивки на слова хитрый должен быть. А то и Су-27 превратится в отдельно "Су" и отдельно "27"

Но по хорошему, конечно, добиться бы нормальной работы FULLTEXT.

Блин, пощупать, что ли, превью 5-й версии MySQL?

Таблица wordlink легко позволяет сохранить структуру документа путем добавления одного поля. Для просто частотного анализа - это поле будет счетчиком. А метод-то действительно известный. Кстати, не факт, что замененный текст будет длинее - надо считать среднюю длину слова в сообщениях. Если она больше 4 - а для русского языка это нормально, то упакованный таким образом текст будет короче.

А разбиение слов - это да - тут надо подумать. Но не обязательно "-" должен быть разделителем. Как и точку. Скажем пробел - да, точка пробел - да, " - " - да. А просто в слове - нет.

Мне 3й вариант нравится тем, что позволяет искать не точно соответствующие слова. ИМХО большой плюс, а заодно, уменьшит количество попыток поиска.
А не нравится тем, что как сказал Рома, проблема если надо искать точный текст.

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

То есть:
1) - запускаем поиск по 3му варианту (Mishka);
2) - если есть кавычки - запускаем поиск в постах, но только в тех, которые найдены в п.1)

Таким образом убиваем 2х зайцев: - и быстрый поиск, и возможность поиска близких вариантов, и возможность поиска точной фразы.

Согласен, тем более, что полный текст присутствует.

SEA, 19.04.2004 20:46:04 :

Роман, а как в теге [code] сделать тип 'php' вместо текст?
Как у тебя - Created with Colorer, type 'php'

 

Прости, сразу не заметил

[ code php ]
...
[ /code ]

(сижу сейчас и пишу свой поиск )