Вопрос по нагрузке на проц

Добрый день!

Меня волнует следующая проблема: имеется mysql база, по которой надо производить поиск. Поиск производиться по двум текстовым полям (varchar(255)). Условие поиска генерируется динамически, в среднем получается что-то вроде этого:

SELECT * FROM links WHERE (LOWER(site_title) LIKE '%group%' OR LOWER(site_title) LIKE '%orgy%' AND LOWER(site_title) LIKE '%party%') OR (LOWER(site_desc) LIKE '%group%' OR LOWER(site_desc) LIKE '%orgy%' AND LOWER(site_desc) LIKE '%party%') AND is_active=1 ORDER BY add_date DESC, site_rank DESC LIMIT 0,20

Может сгенерироваться и попроще, может и посложнее.
Количество записей предполагается измерять в сотнях тысяч.

Теперь, собственно вопрос: какие хостинговые мощности необходимы для всего этого, исходя из количества посетителей, скажем, 15к-20к в сутки? (Интересует память и процессор(ы)).

Если все так плохо, то каким путем можно оптимизировать поиск? Может, есть альтернативные БД с индексацией текстовых полей (не fullindex).

Тестировал на своем компе на 5к записях - результат выдается мгновенно, при одном запросе загрузка проца прыгает в среднем до 38% и сразу же обратно к 2%. (PIII-870MHz, 448mb ram).

В общем принимаются любые советы и критика. Всем +

мне кажется (!), что при наличии индекса это будет работать достаточно быстро и в mysql. другое дело что при наличии индекса добавление в эту таблицу будет происходить гораздо медленнее (т.к. надо обновить индекс будет). но я так понимаю это тебя не пугает в такой постановке задачи, т.к. поиск будет производиться _гораздо_ чаще чем добавление.
если это будет тормозить, то здесь можно оптимизировать.
например, можно классифицировать это список "в офлайне". объясню. я так понимаю количество вариантов LIKE "что-то" ограничено и задается где-то в другом списке. так вот пробегись по этому списку отдельной задачей и классифицируй каждую запись на предмет попадания этого слова и потом уже бегай с поиском только по классификатору.

P.S. под словом "достаточно быстро" имелось виду "даже и не заморачивайся".

stillen писал:

SELECT * FROM links WHERE (LOWER(site_title) LIKE '%group%' OR LOWER(site_title) LIKE '%orgy%' AND LOWER(site_title) LIKE '%party%') OR (LOWER(site_desc) LIKE '%group%' OR LOWER(site_desc) LIKE '%orgy%' AND LOWER(site_desc) LIKE '%party%') AND is_active=1 ORDER BY add_date DESC, site_rank DESC LIMIT 0,20

а зачем lower ? это можно и до этого сделать , имхо лишнее .
По поводу структуры запроса нечего не могу сказать т.к. изночально не знаю для чего это нужно и какой ожидаеться резултат и как это все должно выводиться.Если база будет не слишком "динамична" надо еще делать кэш чтобы поменьше обращаться к базе.


А по сути ты не должен этого хотеть проще записать все это в обычный файл , написать небольшой API для работы с файлом, но зато работать будет веселей .

Спасибо, ответ оценил.

Хотел бы уточнить по поводу индексов: я так понял, когда ковырял доки, что mysql поддерживает только fulltextindex, т.е. индексируется строка полностью, и при поиске подстроки (like) этот индекс вроде никакого значения не играет.. Или я не прав?

+ не совсем про "оффлайн" понятно пока. буду перечитывать еще хотя, если найдется немного веремени, можешь повторить, и помедленнее ;)

ну и все-таки хотелось бы услышать про железо, на которое необходимо расчитывать.

2 JpS: все, я понял что имелось ввиду
в том-то все и дело, что запросы по LIKE не ограничены и там может быть все, что угодно. поисковый запрос вводится непосредственно юзером.


2 mr.GOD

можно про кэш поподробнее?

в общем смысл приблизно такой: это просто поисковик. юзер вводит инфу в текстбокс, парсится запрос, создаются условия (WHERE) и запрос выполняется. поиск происходит по одной таблице, связей нет.
Результаты выводятся по 20 штук на страницу. Если юзер идет на вторую страницу, то опять выполняется такой же запрос, но уже LIMIT 19, 20 и т.д.

насчет фулиндекса - хорошее замечание. не готов ответить. тут ты мне даже мысль для рассуждения подкинул. порой доки, если это действительно так, как ты говоришь, то да, такой индекс не поможет. однако знаю, что при создании индекса в интерфейсе галочка fullindex либо ставится, либо нет (имеется ввиду, что ты можешь то же самый индекс делать и как fulindex и как нет), но это надо смотреть и экспериментировать.
по железу - я думаю что минимального целерона баксов за 100 - хватит. или разговор идет о вируале? возможно потянет и виртуал. была у меня похожая задача. жило на вируале, пока трафа не стало 100К. но там гораздо более сложные запросы делались, с джойнами и пр. на дедике - жило и на 100К.
насчет помедленнее:
смотри. делаешь таблицу со всеми возможными типами LIKE, где у каждого слова есть ID (numeric). далее делаешь еще одну таблицу, где перечислены все сайты из твоей таблицы в виде: ID сайта (тоже numeric) и ID слова, которое там нашлось. т.е. на каждый сайт по нескольку записей с ID слов. количество записей там станет на порядок больше, однако в этой таблице будут только numeric-и и искать там будет гораздо быстрее. другой вопрос, что вот эту таблицу тебе нужно будет обновлять каждый раз когда появится новый "тип" слова, т.е. были group, fetish, а добавилось lesbi. если количество "типов" слов у тебя постояно меняется или скажем юзер вводит их сам где-то, то такой способ оптимизации конечно же не подойдет, но посмотри, так ли уж много различных типов этих поисков, может быть и не давать юзеру их вводить, а давать скажем выбирать из списка.

JpS писал:

насчет помедленнее: смотри. делаешь таблицу со всеми возможными типами LIKE, где у каждого слова есть ID (numeric). далее делаешь еще одну таблицу, где перечислены все сайты из твоей таблицы в виде: ID сайта (тоже numeric) и ID слова, которое там нашлось. т.е. на каждый сайт по нескольку записей с ID слов. количество записей там станет на порядок больше, однако в этой таблице будут только numeric-и и искать там будет гораздо быстрее. другой вопрос, что вот эту таблицу тебе нужно будет обновлять каждый раз когда появится новый "тип" слова, т.е. были group, fetish, а добавилось lesbi. если количество "типов" слов у тебя постояно меняется или скажем юзер вводит их сам где-то, то такой способ оптимизации конечно же не подойдет, но посмотри, так ли уж много различных типов этих поисков, может быть и не давать юзеру их вводить, а давать скажем выбирать из списка.

такая схема например реализована в поиске по phpbb2

Ёперный театр. Почитал дальше доку, оказывается, все очень хорошо.

Фулиндекс для этих целей нормально подходит, только нужно по другому запрос организовывать. Более того, поддерживается поиск in BOOLEAN MODE (с логическими операторами). А я как дурак, парсил посимвольно запрос, искал логические комманды, пасставлял для них приоритеты и т.п.

Оказывется, все офигенно просто. Для тех, кому интересно, но лень читать доки:

Создание таблицы:

mysql> CREATE TABLE articles (
-> id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
-> title VARCHAR(200),
-> body TEXT,
-> FULLTEXT (title,body)
-> );
Query OK, 0 rows affected (0.00 sec)


Заполнение:

mysql> INSERT INTO articles VALUES
-> (NULL,'MySQL Tutorial', 'DBMS stands for DataBase ...'),
-> (NULL,'How To Use MySQL Efficiently', 'After you went through a ...'),
-> (NULL,'Optimising MySQL','In this tutorial we will show ...'),
-> (NULL,'1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
-> (NULL,'MySQL vs. YourSQL', 'In the following database comparison ...'),
-> (NULL,'MySQL Security', 'When configured properly, MySQL ...');
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0


Теперь, собственно самое главное - простой поиск по слову, с использованием FullTextIndex:

mysql> SELECT * FROM articles
-> WHERE MATCH (title,body) AGAINST ('database');

(запросы данного типа не чувствительны к регистру)

Один из важных моментов: сдесь реализован полноценный поиск с подсчетом релевантности, которую тоже можно получить.. Сортировка идет по релевантности.

Личное предположение: поиск по такой схеме будет работать только с английским языком (латиница).


Ну и наконец - поиск с использованием логических операторов:

mysql> SELECT * FROM articles WHERE MATCH (title,body)
-> AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE);


Поддерживаемые операторы:

+ обязательное присутствие слова
- наоборот
< > увеличение/уменьшение релевантности при встрече слова
( ) группировка
"" поиск полноценной фразы

еще есть ~ и *, не совсем понял, что это такое.



В общем, пойду-ка я двигло переписывать

2stillen

По поводу кэширования тут все просто , если вся система не критически чувствительна к изменениям , т.е. если добавить 10 записей и юзверь их может не увидеть сразу же , то каждый запрос нужно писать например в отдельный файл , имя в ввиде кейворда который ввел юзер , добавить небольшую логику чтобы отсевались файлы к запросами котороым меньше всего а рефрешь кэша, делать например по количеству добавленных записей или руками.
Можно даже отдельно бота на крон посадить и пусть думает что лучше кэшировать а что нет ... вообщем тут можно об этом говорить до бесконечности , все ограничиваеться только тем на сколько у тебя больное воображение

LOWER тут лишние - MySQL LIKE делает без различия регистра пока ему BINARY ненапишеш.
Потом поповоду оптимизации - онине оптимизиркет OR в запросе в принципе...поэтому нагрузка при иаком запросе будет немаленькая даже если испорльзовать индексы
Кэш поможет если все будут искать одно и тоже, иначе пострадает память. он это все постарается хранить....а если в результате много выбирается то БД раздует как мыльный пузырь. Я бы попробовал его разбить на части или изменить структуру. Может в этом случае потеряеш в скорости, но выиграеш в вмысле нагрузки на железо.