Форум Сайтостроение Веб-строительство

Оптимизация БД - как ускорить?

Metal Messiah
На сайте с 01.08.2010
Offline
163
Metal Messiah
1202

Доброго времени суток!

Сегодня случилось то что должно было случиться - меня поперли с 1-долларового хостинга за нагрузку (новостной портал 3000+ в сутки на самописном движке) :crazy:

А именно за нагрузку на БД (хотя не только, на самом деле там БД не учитывалась в дисковый тариф и весила в 2 раза больше нормы и еще кое-что...). Последний раз сотрудники писали мне весной, тогда я закешировал на файлах все блоки в сайдбарах (топ новости, последние комментарии и т.д.), в результате осталось только 2-3 запроса к БД на страницу.

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

Вообщем, перенес все на VPS, на котором уже висели несколько сайтов суммарной посещаемостью 1000 просмотров, сменил DNS и увидел что сервер потихоньку начинает захлебываться. Работает на nginx+apache2. Настроил кеширование mysql запросов, кеширование nginx, вроде стало лучше, но время генерации страницы от 1 до 10 секунд - не дело.

Заменил кругом в коде mysql_query() на это: 

	function mysql_query_ex($query)

	{

		$t1=getmicrotime();

		$r=mysql_query($query);

		$t2=getmicrotime();

		

		$file = fopen ("./mysql.txt", "a+");

		$str=date("d.m.Y H:i:s", time())."	".($t2-$t1)."	".$query."	".$_SERVER["REQUEST_URI"]."\n";

		fputs ($file, $str);

		fclose($file);	

		

		return $r;

	}

и начал смотреть в лог, а там такая ерунда: 

8.600607157	SELECT * FROM news WHERE id=212320 LIMIT 1

8.10000205	SELECT * FROM news WHERE id=211519 LIMIT 1

6.579179049	SELECT * FROM news WHERE id=218635 LIMIT 1

5.680623055	SELECT * FROM news WHERE id=212320 LIMIT 1

5.382102013	SELECT * FROM news WHERE id=213905 LIMIT 1

5.100635052	SELECT * FROM news WHERE id=211519 LIMIT 1

3.265857935	SELECT * FROM news WHERE id=213905 LIMIT 1

2.693847895	SELECT * FROM news WHERE id=212320 LIMIT 1

2.378823996	SELECT * FROM news WHERE id=217046 LIMIT 1

2.179720879	SELECT * FROM news WHERE id=211519 LIMIT 1

0.000367165	SELECT * FROM news WHERE id=201596 LIMIT 1

9.89E-05	SELECT * FROM news WHERE id=212320 LIMIT 1

0.000105143	SELECT * FROM news WHERE id=217046 LIMIT 1

0.000108004	SELECT * FROM news WHERE id=211519 LIMIT 1

9.70E-05	SELECT * FROM news WHERE id=212320 LIMIT 1

Видно что на один и тот же запрос поиска (id это primary key) выполняется от 9E-5 до 8 секунд. Это одна проблема, причин возникновения понять не могу. Одновременные SELECT'ы гасят друг друга?

Вот структура таблицы:

CREATE TABLE IF NOT EXISTS `news` (
`id` int(8) unsigned NOT NULL auto_increment,
`date` int(4) unsigned default NULL,
`section` int(1) unsigned default NULL,
`title` varchar(255) default NULL,
`text` text,
`img` varchar(255) NOT NULL,
`views` int(4) unsigned default NULL,
`gog` int(4) unsigned default NULL,
`yan` int(4) unsigned default NULL,
`yah` int(4) unsigned default NULL,
KEY `section` (`section`),
FULLTEXT KEY `title` (`title`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251 AUTO_INCREMENT=218750 ;

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

SELECT * FROM news WHERE id<>218504 AND date>'1404067471' AND (title LIKE '%shell%' OR title LIKE '%бизнеса%' OR title LIKE '%грозит%' OR title LIKE '%конфискацией%' OR title LIKE '%осква%' OR title LIKE '%оссии%') ORDER BY date DESC LIMIT 10

Поле title является FULLTEXT индексом, но несмотря на это, запрос добавляет 0.5-1.9 секунды ко времени генерации. Фишку с AND date>'1404067471' я добавил позже чтобы отсеять лишние и не сравнивать по текстовому индексу, но прироста производительности не заметил.

Как считаете - создавать дополнительную таблицу ключей с числовыми индексами (а при текущих объемах она будет содержать что-то около лимона записей) и искать id новостей по теме по ней - будет ли это быстрее чем поиск по FULLTEXT?

anonymous, думай что говоришь и не забывай подписать отзыв :)
M
На сайте с 12.11.2005
Offline
122
modelist
#1
Metal_Messiah:
id это primary key

Из представленной структуры этого не видно.

Metal_Messiah:
Фишку с AND date>'1404067471'

У Вас date по структуре состоит из 4 цифр. Речь о той же самой таблице?

Добавьте индекс на date, посмотрите результат.

Metal Messiah
На сайте с 01.08.2010
Offline
163
Metal Messiah
#2

блин отрезал лишнее :)

там еще пару полей в конце таблицы было, используемых редко но это мегаприват потому их решил выкусить, к проблеме они отношения не имеют, заодно и id зацепил. Есть он там. 1й пост отредактировать не могу.

У меня date это timestamp, и чуть ли не у каждой новости он свой, повторов мало, не думаю что это хорошая идея делать индекс на такой огромной выборке.

Давайте учить PHP! [MySQL] Round-robin выборка строки. MySQL GROUP BY и
siv1987
На сайте с 02.04.2009
Offline
427
siv1987
#3

Покажите нормальнно структуру таблицы SHOW CREATE TABLE.

Покажите результат EXPLAIN SELECT проблемный запрос.

Для полно текстового поиска используется оператор MATCH AGAINST.

Glueon
На сайте с 26.07.2013
Offline
172
Glueon
#4 

) ENGINE=MyISAM

Может быть это и table level locking во время update-ов. Поэтому в этом лучшем случае замена на InnoDB может помочь.

Но в целом неплохо бы посмотреть ту доп. информацию, которую у вас запросили выше.

Есть много IP-сетей в аренду под прокси, парсинг, рассылки (optin), vpn и хостинг. Телега: @contactroot ⚒ ContactRoot команда опытных сисадминов (/ru/forum/861038), свой LIR: сдаем в аренду сети IPv4/v6 (/ru/forum/1012475).
MySQL - too many Бэкап MySQL базы при Пролема с locked mysql
Metal Messiah
На сайте с 01.08.2010
Offline
163
Metal Messiah
#5

Спасибо за ответы. SHOW CREATE TABLE:

CREATE TABLE `news` (
`id` int(8) unsigned NOT NULL AUTO_INCREMENT,
`date` int(4) unsigned DEFAULT NULL,
`section` int(1) unsigned DEFAULT NULL,
`title` varchar(255) DEFAULT NULL,
`text` text,
`img` varchar(255) NOT NULL,
`views` int(4) unsigned DEFAULT NULL,
`gog` int(4) unsigned DEFAULT NULL,
`yan` int(4) unsigned DEFAULT NULL,
`yah` int(4) unsigned DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `section` (`section`),
FULLTEXT KEY `title` (`title`)
) ENGINE=MyISAM AUTO_INCREMENT=219058 DEFAULT CHARSET=cp1251

Немного EXPLAIN: любой запрос

SELECT * FROM news WHERE id=(ЧИСЛО) LIMIT 1

выдает одинаковую табличку

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE news const PRIMARY PRIMARY 4 const 1

Приведенный выше

SELECT * FROM news WHERE id<>218504 AND date>'1404067471' AND (title LIKE '%shell%' OR title LIKE '%бизнеса%' OR title LIKE '%грозит%' OR title LIKE '%конфискацией%' OR title LIKE '%осква%' OR title LIKE '%оссии%') ORDER BY date DESC LIMIT 10

показывает

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE news ALL PRIMARY NULL NULL NULL 218069 Using where; Using filesort

InnoDB обычно не использую, более того отключаю в конфиге чтобы увеличить производительность VPS и уменьшить расход памяти. Все работало на MyISAM несколько лет на дешевом хостинге, не думаю что там оборудование намного мощнее моего VPS. Исходя из этих EXPLAIN можно сказать что переход на InnoDB может помочь?

Еще из тестов:

SELECT * FROM news WHERE id<>218504 AND (MATCH (title) AGAINST ('shell') OR MATCH (title) AGAINST ('бизнеса') OR MATCH (title) AGAINST ('грозит') OR MATCH (title) AGAINST ('конфискацией') OR MATCH (title) AGAINST ('осква')) ORDER BY date DESC LIMIT 10;

1 запуск 3.6 sec

2 запуск 7.3 sec

далее пошла выдача 7.3 страницы phpMyAdmin из кеша о_О

SELECT * FROM news WHERE id<>218504 AND (title LIKE '%shell%' OR title LIKE '%бизнеса%' OR title LIKE '%грозит%' OR title LIKE '%конфискацией%' OR title LIKE '%осква%') ORDER BY date DESC LIMIT 10

4.38 sec

5.57 sec

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

Что если использовать один Оптимизация MYSQL DB - Ускорения импорта дампа для
siv1987
На сайте с 02.04.2009
Offline
427
siv1987
#6

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

По второму запросу - попробуйте добавить индекс по полю date.

Опять, полно-текстовой индекс используется не так - MATCH (title) AGAINST ('shell бизнеса грозит конференцией')

Условие id<>218504 выведете в конце запроса.

SELECT * FROM news WHERE date>1404067471 AND MATCH (title) AGAINST ('shell бизнеса грозит конфискацией') AND id<>218504;

SELECT * FROM news WHERE date>1404067471 AND MATCH (title) AGAINST ('shell бизнеса грозит конфискацией') AND id<>218504 ORDER BY date DESC;

Если присутствие id<> так обязательна, можно попробовать создать составной индекс из date + id - KEY date (date, id)

SELECT * FROM news WHERE date>1404067471 AND id<>218504 AND MATCH (title) AGAINST ('shell бизнеса грозит конфискацией');

БД гигантского размера MySQL GROUP BY и База синонимов, MySQL. Помогите
T
На сайте с 09.12.2011
Offline
55
tls
#7

Metal_Messiah, вообще, дебажить большую базу на непустом VPS - это неправильно. Я бы рекомендовал перенести сайт на время на отдельный VPS (например, DigitalOcean, OVH) и начать с MySQLTuner'а и статистики в phpMyAdmin. А судя по большим тормозам при выборке по индексу, текущий VPS может просто не тянуть новую нагрузку.

Яндекс.Дзен начинает рекомендовать посты Новая логика работы операторов Роскомнадзор составил протоколы в
Metal Messiah
На сайте с 01.08.2010
Offline
163
Metal Messiah
#8

siv1987, спасибо, помогло

SELECT * FROM news WHERE MATCH (title) AGAINST ('shell бизнеса грозит конфискацией') AND id<>218504;

проверку даты убрал.

Плюс к тому у меня был косяк - если новостей по теме найдено меньше N, делался 2й запрос выборки M последних по дате новостей, где использовалось WHERE date>(time()-86400) - т.о. запрос каждую секунду менялся и не кешировался, округлил это число до 5000 секунд, теперь кешируется.

tls,

текущий VPS может просто не тянуть новую нагрузку.

по сути этот перенесенный сайт и есть вся нагрузка. остальное - мелочи, машина почти простаивала.

Пока как результат после десятка просмотренных страниц время порядка 0.05 сек, максимум выброс 1 секунда с мелочью.

Завтра еще посмотрю в конце дня по логу MYSQL запросы в пиковое время, на которые уходит более секунды, если что буду пробовать InnoDB.

PHP + MYSQL кэширование MariaDB, storage engine и нагрузка на сервер

Авторизуйтесь или зарегистрируйтесь, чтобы оставить комментарий