Intellect Board — cистема управления сайтом

И так! Рано или поздно все столкнуться с этой проблемой! - перегрузка. Когда даже собственный сервер насинает загибаться под запросами к mysql. Мало ли кому понадобится это руконоговодство.
Суть в том, что когда БД начинает весить от 400 мегабайт (у каждого своя производительность жедеза) и число записей подходит к миллиону, mysql с большим трудом находит там нужные данные. 600-700тысяч сообщений, это порог после которого время запроса к _Post таблице резко возрастает с 0,01 до 2-5 секунд.
Лекарство:
- дорогой апгрейд сервера - т.к. происходит перегрузка дисковой подсистемы: установа SATA3 контроллера (если его нет), вынос БД на отдельные SAS или SSD диски и прочй головняк.
- допилить _Post таблицу до партицианирования. Как это сделать:

Руководство подразумевает, что читатель представляет в общих чертах, что такое партиционирование и с чем его едят. Если нет, то в гугль, и сюда глянуть можно сюда и сюда
Также, подразумевается что есть доступ к ssh консоли сервера и читатель представляет как работать с командами оболочки mysql под linux через ssh. Ну или хотябы раз входил в оболочку mysql.

1) На всякий случай сделайте бэкап всей БД через mysqldump

2) Чтобы при случае долго не рыться в поисках таблицы, отдельно задампим её
mysqldump -p ваша_бд_форума --tables XXX_Post >XXX_Post_all.dmp

3) Отдельно дампим данные
mysqldump -p ваша_бд_форума --tables --no-create-info XXX_Post >XXX_Post_data.dmp

4) Отдельно структура
mysqldump -p ваша_бд_форума --tables --no-data XXX_Post >XXX_Post_str.dmp

5) Модифицируем структуру таблицы в файле XXX_Post_str.dmp, примерно таким образом:
a. PRIMARY KEY (`p_id`,`p_tid`,`p_uid`,`p__premoderate`), добавили p_tid. Его тут раньше не было
b Заремим — FULLTEXT KEY `topics` (`p_text`,`p_title`) - добавили перед строкой вот это: --
Иначе партиция не создаётся.
c. В предыдущей строке убрали запятую.Стало так: KEY `tidkey` (`p_tid`,`p__premoderate`,`p__time`)
d. Переделываем конец запроса к такому виду:
) ENGINE=MyISAM AUTO_INCREMENT=XXXXXXX DEFAULT CHARSET=cp1251
PARTITION BY RANGE(`p_tid`) (
PARTITION post_tid_0050 VALUES LESS THAN(50),
PARTITION post_tid_0150 VALUES LESS THAN(150),
PARTITION post_tid_0200 VALUES LESS THAN(200),
PARTITION post_tid_0250 VALUES LESS THAN(250),
PARTITION post_tid_0300 VALUES LESS THAN(300),
PARTITION post_tid_0350 VALUES LESS THAN(350),
PARTITION post_tid_0400 VALUES LESS THAN(400),
PARTITION post_tid_0450 VALUES LESS THAN(450),
PARTITION post_tid_0500 VALUES LESS THAN(500),
.........
Сколько там у вас тем есть и ожидается. Разбивка 50 тем на партицию.
.........
PARTITION post_tid_5000 VALUES LESS THAN(50-0),
PARTITION post_tid_max VALUES LESS THAN(MAXVALUE)
);
Сохраняете файл.
e. Заходите в mysql.
f. \u ваша_бд_форума (выбираем базу)
g. \.XXX_Post_str.dmp XXX_Post_str.dmp (Загружаем структуру)
h. \.XXX_Post_data.dmp XXX_Post_data.dmp (загружаете данные)
i. exit
Всё. Чёрное дело сделано.
* примечание XXX - префикс вашей таблицы.

6) Проверяем как работает.
Пример с темой 144. Выберете свою тему.
mysql> explain partitions select count(p_id) from ваш_префикс_Post where p_tid=144;
+----+-------------+-----------+---------------+------+---------------+------+---------+-------+------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+---------------+------+---------------+------+---------+-------+------+-------+
| 1 | SIMPLE | XXX_Post | post_tid_0150 | ref | tid,tidkey | tid | 4 | const | 298 | |
+----+-------------+-----------+---------------+------+---------------+------+---------+-------+------+-------+
1 row in set (0.01 sec)

До запуска партицианирования выполнялось около 1 секунды. Тут видно, что используется партиция post_tid_0150

Если что-то совсем не понятно - ищите админа fisher.spb.ru

Gram
Запросы менять не нужно. Но пока как показала практика отвалился поиск по fulltext индексам. Проблема находится в стадии решения. В ближайшее время решу и напишу тут как лечить

Прелесть партиционирования - всю эту возню выполняет mysql не напрягая программиста.

Приветы всем! А если сервер держит 2 000 посетителей одновременно- нужно эту доработку делать?

Что касается "не прошенного" но "предвиденного" эффекта (перефразируя Булгакова) - отвалившийся поиск по причине отключения fulltext индексов. Как пишут в одном из багрепортов - это недокументированная фича mysql. fulltext индексы - только для не партиционируемых таблиц! Надеюсь, что разработчики форума примут этот факт во внимание, тем более что фатальных различий по времени поиска между fulltext и LIKE методами я не заметил. Ну думает LIKE какое-то время . Так fulltext не быстр!
В печку лирику!

Мой код, который могу вам предложить, к сожалению без напильника не пойдёт. Это связано с глубокой модификацией системы поиска, и этот код применим без адаптаций только к ней. На моём форуме он двухступенчатый. Я уже выкладывал когда-то доработку поисковой системы. Вот она: http://intboard.ru/99/1942/

Для совместимостью с партиционированием я изменил:
1) механизм релевантности (на втором этапе поиска она игнорируется, да и она не нужна теперь там)
2) на первом этапе поиска добавлена радиоконопка: Искать как: фразу () или () набор слов ? (Набор слов - по сути оригинальный поиск без кавычек. Фраза - оригинальный поиск с кавычками.) Все остальные условия доппоиска отстрелены как не нужные. Ими за 8 лет ни кто не пользовался ни разу (судя по логам).
3) механизм fulltext заменён на LIKE.

Далее представлю вашему внеманию пинцип модификации запросов:
1) Изменяем вычисление переменной condition.
переменная $s_set определят что ищем? Фразу или набор слов. 0 - фраза. Набор слов разбивается указанным кодом на подусловия LIKE. При чём делает это, прошу заметить, особо извращённым способом. Разметка текста по пробелам. Пробел между словами заменяется на это %\' AND p.p_title LIKE \'%. Кончено костылём таким эпическим попахивает... Но работает отлично!

[q]

//$condition="p.p_text LIKE '%$text%' OR p.p_title LIKE '%$text%'";
// блочим оригинальный
if ($s_set==0) $condition="p.p_text LIKE '%$text%' OR p.p_title LIKE '%$text%'";
else{
$text=trim($text);
$text=str_replace(' ',' ',$text);
$text=str_replace(' ',' ',$text);
$text=str_replace(' ',' ',$text);
$text=str_replace(' ',' ',$text);
$text=str_replace(' ',' ',$text);
$text=str_replace(' ',' ',$text);
$text_p=str_replace(' ','%\' AND p.p_text LIKE \'%',$text);
$text_t=str_replace(' ','%\' AND p.p_title LIKE \'%',$text);
$condition="p.p_text LIKE '%$text_p%' OR p.p_title LIKE '%$text_t%'";
}

[/q]

!!!! код выше отображается не совсем корректно! в грядке из 5 одинаковых str_replace разное количество пробелов. от 2 до 7. Это элемент защиты от дибильных ошибок, чтобы LIKE-и не размножались бесконтрольно!


Вот так доаботаны запросы 2 ступени :

[q]

$sql = "INSERT INTO ".$GLOBALS['DBprefix']."SearchResult (srid,srpid,relevancy) ".
"SELECT $id,p_id, 10 AS relevancy FROM ".$GLOBALS['DBprefix']."Forum f, ".$GLOBALS['DBprefix']."Topic t, ".$GLOBALS['DBprefix']."Post p ".
"WHERE ($condition) AND ($sqldata) AND p_tid=t_id AND t_fid=f_id AND p_tid=$stem_search AND p__premoderate=0 $timelimit ORDER BY $order $descr $limit";

[/q]

[q]

$sql = "INSERT INTO ".$GLOBALS['DBprefix']."SearchResult (srid,srpid,relevancy) ".
"SELECT $id,p_id, 10 AS relevancy FROM ".$GLOBALS['DBprefix']."Forum f, ".$GLOBALS['DBprefix']."Topic t, ".$GLOBALS['DBprefix']."Post p ".
"WHERE ($condition) AND ($sqldata) AND p_tid=t_id AND t_fid=f_id AND p__premoderate=0 $timelimit ORDER BY $order $descr $limit";

[/q]

Вместо relevancy по сути число 10. Первое, что в голову пришло. В общем-то реливаси тут уже роли не играет. $condition генерирует мой код выше.

Вот так доаботаны запросы 1 ступени :

[q]

$sql = "INSERT INTO ".$GLOBALS['DBprefix']."SearchResult (srid,srpid,relevancy) ".
"SELECT $id,t_id, count(p.p_id) AS relevancy ".
"FROM ".$GLOBALS['DBprefix']."Forum f, ".$GLOBALS['DBprefix']."Topic t, ".$GLOBALS['DBprefix']."Post p ".
"WHERE ($condition) AND ($sqldata) AND t_fid=f_id AND t__pcount>0 AND p_id=t__startpostid $timelimit GROUP BY t_id ORDER BY $order $descr $limit";

[/q]

Всё аналогично, но relevancy тут вычисляется тупо через count(p.p_id). по сути число совпадений в теме. Чем больше совпадений - тем выше relevancy - тем выше она в результате поиска первой ступени. Тоесть тут:
http://intboard.ru/file.php?a=...1068282538

По моим ощущениям - качество поиска кратно лучше!

Тут файл search.php директории styles/ваш_набор_стилей
ПРЕДУПРЕЖДАЮ! заливать можно себе только если выполнен этот "народный" мод http://intboard.ru/99/1942/