Без вступления сразу к делу.
Проблема проявляется только на очень крупных и посещаемых форумах.
После превышения порога в 1 млн постов(размер более 1 Гб) заметно снижается скорость выборки из таблицы post. Более того, любой запрос к ней полностью блокирует остальные, что вызывает заметное падение скорости работы форума и соответственно постоянные жалобы пользователей. До поры до времени данная проблема решается более мощным сервером, потом выделенным mysql сервером, но на самом деле решение её лежит в другой плоскости!
Итак, проблема: таблица post в формате MyISAM по причине наличия в ней FULLTEXT индекса.
Возникает известное
конвертировать в InnoDB нельзя по форуму искать.
Тем не менее, решение нашлось. post все же конвертируем в InnoDB, а для поиска создаем дублирующую таблицу postsearch. За скорость работы придется заплатить двойным объемом дискового пространства. postsearch будет автоматически обновляться при помощи триггеров и использоваться исключительно при полнотекстовом поиске. Для использования триггеров требуется MySQL версии не ниже 5.0.2 А поскольку таблицы под основной нагрузкой будут в формате InnoDB, то рекомендую версию 5.5(
подробнее о новшествах)
Инструкция
1. Создайте новую таблицу postsearch
PHP Code:
CREATE TABLE IF NOT EXISTS `postsearch` (
`postid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`threadid` int(10) unsigned NOT NULL,
`userid` int(10) unsigned NOT NULL,
`title` varchar(250) NOT NULL DEFAULT '',
`dateline` int(10) unsigned NOT NULL,
`pagetext` mediumtext,
`visible` enum('1','2') NOT NULL DEFAULT '1',
PRIMARY KEY (`postid`),
KEY `threadid` (`threadid`,`userid`),
KEY `dateline` (`dateline`),
FULLTEXT KEY `title` (`title`,`pagetext`)
) ENGINE=MyISAM;
2. Отключите форум.
3. Сделайте дамп таблицы post при помощи mysqldump
обязательно с ключем --add-drop-table=FALSE.
4. Переименуйте старую таблицу , она нам еще пригодится. Да и просто так, на всякий случай.
PHP Code:
RENAME TABLE post TO post2
5. Создайте новую таблицу post без FULLTEXT индекс и с ENGINE=InnoDB, поля те же.
Загрузите файл дампа. Запаситесь терпением! 1 млн постов будут загружаться не менее часа(если у вас слабый сервер, то в разы дольше).
Эстеты могут выполнить запрос INSERT ... SELECT, но выполняться он будет в несколько раз дольше чем отдельные операции в пп.3-5
6. Создайте триггеры. Таблица postsearch будет обновляться автоматически при добавлении и изменении сообщений в основной таблице.
Дополнительно: хранимая процедура для удаления цитат из поискового индекса
PHP Code:
DROP TRIGGER IF EXISTS post_insert;
DROP TRIGGER IF EXISTS post_update;
DROP TRIGGER IF EXISTS post_delete;
DELIMITER //
CREATE TRIGGER `post_insert` AFTER INSERT ON `post`
FOR EACH ROW BEGIN
REPLACE DELAYED postsearch SET postid=NEW.postid, threadid=NEW.threadid, userid=NEW.userid, title=NEW.title, dateline=NEW.dateline, pagetext=strip_quote(NEW.pagetext), visible=NEW.visible;
END //
CREATE TRIGGER `post_update` AFTER UPDATE ON `post`
FOR EACH ROW BEGIN
IF NEW.visible=1 THEN REPLACE DELAYED postsearch SET postid=NEW.postid, threadid=NEW.threadid, userid=NEW.userid, title=NEW.title, dateline=NEW.dateline, pagetext=strip_quote(NEW.pagetext), visible=NEW.visible;
ELSE DELETE QUICK IGNORE FROM postsearch WHERE postid=OLD.postid;
END IF;
END //
CREATE TRIGGER `post_delete` BEFORE DELETE ON `post`
FOR EACH ROW BEGIN
DELETE FROM postsearch WHERE postid=OLD.postid;
END //
DROP FUNCTION IF EXISTS strip_quote;
DELIMITER //
CREATE FUNCTION strip_quote( Dirty text) RETURNS text DETERMINISTIC
BEGIN
DECLARE iStart, iEnd, iLength int;
WHILE LOCATE( '[quote', Dirty ) > 0 And LOCATE( '[/quote]', Dirty, LOCATE( '[quote', Dirty )) > 0 DO
BEGIN
SET iStart = LOCATE('[quote', Dirty)-1, iEnd = LOCATE('[/quote]', Dirty, LOCATE('[quote', Dirty))+8;
SET Dirty = CONCAT(SUBSTR(Dirty, 1, iStart), SUBSTR( Dirty, iEnd));
END;
END WHILE;
RETURN Dirty;
END//
7. Если ваши постоянные пользователи начали собираться на митинг по причине отключенного форума, включайте.
Проверьте, что новые сообщения добавляются в таблицу postsearch, а измененные обновляются.
8. Чтобы не испытывать терпение пользователей еще раз, мы воспользуемся сохраненной таблицей post2 для копирования постов
PHP Code:
INSERT IGNORE INTO postsearch SELECT `postid`, `threadid`, `userid`, `title`, `dateline`, `pagetext`, `visible` FROM `post2` WHERE visible=1
Заметтье, что триггеры уже включены.
Продвинутые администраторы могут заальтерить таблицу post2(см.пункт 1), но тогда возникнет вопрос очистки от удаленных постов.
В любом случае теперь можно идти спать, перестроение FULLTEXT индекса на гигобайтных базах занимает очень много времени.
9. Исправьте search.php, чтобы использовалась новая таблица postsearch, всего 2 запроса: название таблицы выборки post исправляем на postsearch
Номера строк могут отличаться, у меня версия 3.8.6PL1
Строка 1649
PHP Code:
$threads = $db->query_read_slave("
SELECT
" . implode(', ', $thread_select_logic) . "
FROM " . TABLE_PREFIX . "thread AS thread $userid_index
$tag_join
" . ((!empty($post_query_logic) OR !empty($post_join_query_logic)) ? "INNER JOIN " . TABLE_PREFIX . "postsearch AS post ON(thread.threadid = post.threadid $post_join_query_logic)" : "") . "
" . (!empty($querylogic) ? "WHERE " . implode(" AND ", $querylogic) : "") . "
$nl_query_limit
");
Строка 1727
PHP Code:
$posts = $db->query_read_slave("
SELECT postid, post.dateline
" . iif($vbulletin->GPC['sortby'] == 'rank' AND !$rank_select_logic, ', IF(thread.views=0, thread.replycount+1, thread.views) as views, thread.replycount, thread.votenum, thread.votetotal') . "
" . (!empty($rank_select_logic) ? ", $rank_select_logic" : "") . "
FROM " . TABLE_PREFIX . "postsearch AS post $userid_index
" . ($do_thread_join ? "INNER JOIN " . TABLE_PREFIX . "thread AS thread ON(thread.threadid = post.threadid)" : '') . "
$tag_join
" . (!empty($querylogic) ? "WHERE " . implode(" AND ", $querylogic) : "") . "
$nl_query_limit
");
Включите и проверьте поиск.
Вот и всё - теперь можно быстро общаться, наслаждаясь отсутствием блокировок таблиц.
Буду благодарен за плюсик в карму на
хабре, хотел и там статью разместить.
UPDATE от 01.09.2011
Если используете репликацию, измените формат лога на binlog-format=mixed
Автор: Роман Медведев, 2011 год
Решение проблемы блокировки таблиц на высоконагруженном форуме
