Тормоз при запросах к БД
Для теста производительности движка импортировал базу данных [4092 юзера, 4113 блогов (включая личные), 4845 топиков, 27231 комментириев] с работающего сайта из другого движка.
По данным du -hc /каталог/базы данных, размер базы, включая индексы, составляет 53 МБайта в бинарном виде.
При входе на сайт при первом запросе каких-либо данных (например каждой следующей страницы топиков) включаются тормоза. Очень сильные, достигающие 1мин, до тех пор, пока данные не достанутся из базы и поместятся в кеш.
Факт, что узким местом являются запросы к базе подтверждается и блоком статистики внизу страницы и менеджером процессов top (ОС Debian Linux 4.0).
Использование memcached вместо файлового хранилища ускоряет загрузку, но уже после выполнения запросов и при повторных обращениях, когда роль БД минимальна.
При беглом анализе структуры таблицы топиков, видна ее частичная неоптимальность, как например хранение текста топика в 3х экземплярах (тизер, полный текст и оригинал до обработки парсером-типографом).
Есть у уважаемого сообщества какие-либо идеи по поводу узкого места в работе с БД и/или предложения по нейтрализации сего узного места?
Отдельно хочу поинтересоваться у автора о причинах такого неоптимального хранения текста топиков?
По данным du -hc /каталог/базы данных, размер базы, включая индексы, составляет 53 МБайта в бинарном виде.
При входе на сайт при первом запросе каких-либо данных (например каждой следующей страницы топиков) включаются тормоза. Очень сильные, достигающие 1мин, до тех пор, пока данные не достанутся из базы и поместятся в кеш.
Факт, что узким местом являются запросы к базе подтверждается и блоком статистики внизу страницы и менеджером процессов top (ОС Debian Linux 4.0).
Использование memcached вместо файлового хранилища ускоряет загрузку, но уже после выполнения запросов и при повторных обращениях, когда роль БД минимальна.
При беглом анализе структуры таблицы топиков, видна ее частичная неоптимальность, как например хранение текста топика в 3х экземплярах (тизер, полный текст и оригинал до обработки парсером-типографом).
Есть у уважаемого сообщества какие-либо идеи по поводу узкого места в работе с БД и/или предложения по нейтрализации сего узного места?
Отдельно хочу поинтересоваться у автора о причинах такого неоптимального хранения текста топиков?
52 комментария
для попадания в эфир..., сорритекст хранится в 3-х видах как раз для оптимальности: анонс — чтоб не воротить большой текст при выводе списка блогов, сам текст и исходный текст до обработки парсером — без этого никак, иначе будет много проблем с парсером.
если тормаза из-за ордер бая при больших текстах то это решается через селфджойн. Вобщем мне нужна большая БД чтоб что то сделать :)
только там дамп «грязный» — комментов в базе нет, теги в таблице для поиска по тегу не перенесены, где-то слеши лишние экранирующие не удалены. короче чисто база для экспериментов по быстродействию. но даже на этой базе без комментов, только что проверил, тормоза более чем заметны
кинул в личку ссылку.
В качестве версии могу предположить вот что:
запросы
$rows = getAllRows()
foreach($rows as $row){ ... }
значительно медленнее, чем
while($row = getSingleRow()) { ... }
так как в первом случае приходится все ряды загружать в память php-интерпретатора/модуля веб-севрера, а во втором только по одному. при использовании смарти, к сожалению, из коробки возможен только первый вариант :( если отделять логику и представление и передавать ее объекту результирующий массив.
ну и, конечно, Limit большое число, другое большое число значительно тормознутее, чем where чтото >= чегото AND чтото < чего-то или чтото BETWEEN чегото AND чегото, т.к. в первом случае мускль сначала выбирает все ряды, удовлетворяющие where-у и условиям JOIN-ов, создает временную таблицу и из нее уже отдает что указано LIMIT-ом. создание временных таблиц операция дорогая до памяти и времени, а во 2-м и 3-м случае этого не происходит
while($row = getSingleRow()) { ... }
Вы считаете, что запрос в цикле быстрее? Ой-ой!
Limit большое число, другое большое число
MySQL оптимизирует LIMIT, т.к. при выборе нескольких строк он не выполняет полное сканирование таблицы, а использует индексы. ;)
про цикл вайл:
ессно имелось ввиду не запросы по одному к базе :-)
я имел ввиду примерно
$res = mysqli_query(...);
while($row = $res->fetch_assoc()) { ...}
другими словами, запрос к базе ессно один (парсинг SQL один раз, если хотите), а вот получение результирующих рядов по одному
надеюсь с тем, что mysqli быстрее mysql, спорить не будете? :)
А! Ну это:
$res = mysqli_query(...);
while($row = $res->fetch_assoc()) { ...}
правило хорошего тона уже :)
Я вообще не спорю, а высказываю свое имхо :)
по спорить сори)
Смарти вообще урод! Зачем его использовать в языке, который изначально создан для отделения кода от представления. ;)
<html><body><?=$var?></body></html>
будет выполнятся точно быстрее, чем отпарсенное смарти:
<html><body>{var}</body></html>
Но раз автор движка выбрал его — значит руководствовался некими соображениями на этот счет.
Быстрее, имхо, ничего не придумано. :)
<b>PHP is an HTML-embedded scripting language</b>. The goal of the language is to allow web developers to write dynamically generated pages quickly.
Но это всё лирика! Макс всё равно БОЛЬШОЙ МОЛОДЕЦ!!! Проделать такую огромную работу за такой короткий срок! А оптимизация — дело техники. ;)
Про смарти: она делает написание шаблонов более приятным, предостерегая при этом от смешение MС и V, из аббривиатуры, что называется. очень полезными, подчастую, оказываются ее пре/пост-фильтры, различные хелперы и тп. но из шаблонизаторов это один из самых тормознутых движков :( хотя, наверное, и самый мощный.
Само-собой, что с выбором автора спорить не приходится, можно только мириться и выссказывать свои мысли по поводу улучшения того или иного в движке.
Один из плюсов — встанет на любую БД, нет проблем с переходом проекта, скажем, с MySQL на MSSQL.
Я кстати сплю и вижу, что лайвстрит переведут на CodeIgniter, а mootools заменят jquery :) ну это наверно от нешироты угла зрения: )
Я всё никак не соберусь выложить исходники блога :)
Цитата:
Инногда достаточно большой проблемой является LIMIT в запросах, я не буду тут говорить, что некоторые вытягивают 100 записей, а иногда и 1000 если реально используют 10; скажу следующее — польза от лимита есть только тогда когда в запросе используется индекс по полю, которое сортируем, т.к. в противном случае Using temporary; Using filesort нивелируют всю пользу от лимита. Также стоит избегать лимитов следующего вида LIMIT 1000000, 25 т.к. выбраны все равно будут 1000025 записей, и только потом 1000000 отброшен. Такое часто используется для pagination, и многие программисты часто оправдываются тем, что пользователи все равно в основном ходят на новые страницы (последние в хронологическом порядке), т.е. запросы с такими лимитами выполняются достаточно редко… Да, пользователи заходят на страницы годичной, двухгодичной давности не часто, но если на сайт зайдет поисковый бот, то он зайдет на все страницы, и этот бот, индексирующий контент сайта, положит нам сервер БД.
Всё верно. Выше я отметил, что mysql использует индексы вместо полного сканирования таблицы. Если индексы по этим полям есть! +))
SELECT
*
FROM
`table`
WHERE
id>X*Y-1
LIMIT
X;
Зачот! :)
про limit — это проканает если нет дополнительных условий, а они как правило всегда есть
Есть выборка с покрывающим индексом, как добавить в такую выборку дополнительное условие по связанной таблице сохранив преимущество покрывающего индекса?
Построение покрывающих индексов оправдано до тех пор, пока суммарная длина всех входящих в индекс столбцов остается значительно меньше длины строки таблицы. Всё остальное — от лукавого!
По определению покрывающим индексом называется такой индекс, который содержит все столбцы, упомянутые в операторах SELECT, UPDATE или DELETE. Запрос при этом называется покрываемым запросом. Поскольку не кластеризованный индекс содержит на уровне листьев запись для каждой строки в таблице, то вся информация для выполнения запроса находится в индексе. В силу этого процессор запросов может сканировать не огромную таблицу, а только небольшой индекс. В общем случае, если вам удастся построить покрывающий индекс, то вы сразу почувствуете значительное улучшение производительности обработки запросов. Это объясняется тем, что индекс содержит не всю строку таблицы, а только ее подмножество. Однако оборотная сторона медали состоит в том, что введение в индекс дополнительных столбцов приводит к тому, что на странице индекса умещается меньше записей. Это, в свою очередь, вызывает увеличение места, занимаемого индексом, и возрастание числа операций ввода/вывода, необходимых для считывания индекса в кэш. Построение покрывающих индексов оправдано до тех пор, пока суммарная длина всех входящих в индекс столбцов остается значительно меньше длины строки таблицы.
Морис Льюис
Надеюсь доступно объяснил своё непонимание :)
О! У мну ща VDS есть на firstVDS — 300MHz, 64 метра памяти. Вот куда я ща тестовую базу-то залью! ГЫ! )))
* MySql
запросов: 11
время: 2,173
* Cache
запросов: 43
set: 17
get: 25
время: 0,62973
* PHP
загрузка модулей:0,482
общее время:3,983
Учитывая мощь тестовой машины, весьма неплохо. )
а мощь у тебя сильно большая. на шаред-хостинге никто никогда не даст занять столько ресурсов одному клиенту, а кто готов дедика под сайт поставить, не будут использоваться чужой движок, а напишут свой.
да, действительно. но тогда единственное, что остается — что в моей базе посты сильно больше его тестовой. других версий у меня нет
Посты небольшие — 100 символов.
высоконагруженных систем». Надеюсь после её посещения в моей голове будет ясно, где искать слабые места и как их оптимизировать
если не секрет, с докладом или просто слушателем?
ВНИМАНИЕ!!! Это изменение приведет к неработоспособности движка, это изменение только для энтузиастов желающих проверить работу на большом числе топиков. Для обновления необходимо выполнить первую часть(создание таблицы и индекса) patch.sql, потом запустить convert.php и только потом выполнить вторую часть patch.sql!!! Иначе вы потеряете все тексты топиков.
оттестировал последний транк. все супер. ракета взлетела :-)
[2008-09-28 00:26:23][32267][DEBUG][SELECT
c.*,
u.user_login as user_login,
u.user_profile_avatar as user_profile_avatar,
u.user_profile_avatar_type as user_profile_avatar_type,
c.comment_id as ARRAY_KEY,
c.comment_pid as PARENT_KEY,
IF(cv.comment_id IS NULL,0,1) as user_is_vote,
cv.vote_delta as user_vote_delta
FROM
prefix_topic_comment as c
LEFT JOIN (
SELECT
comment_id,
vote_delta
FROM prefix_topic_comment_vote
WHERE user_voter_id = 2
) AS cv ON cv.comment_id = c.comment_id,
prefix_user as u
WHERE
c.topic_id = 6028
AND
c.user_id=u.user_id
ORDER by c.comment_id asc;
]
[2008-09-28 00:26:23][32267][DEBUG][ -- 7 ms = 6+0+1; returned 16 row(s)]
MySql
запросов: 13
время: 0,063
при чтении конкретного топика