|
Задавайте вопросы, мы ответим
Вы не зашли.
Нужны практические советы по оптимизации структцры БД форума.
Уже несколько раз обращался за советом к форумчанам и всегда получал обстоятельные и точные ответы.
Поэтому обращаюсь еще раз, возможно советы из этой темы пригодятся кому-то еще.
-------------------------------------------------------
В бд форума есть основные таблицы:
1. USERS: id_user, username, passw, count_post.
2. POSTS: id_post, id_topic, id_poster, name_poster, date_time_post, post_text.
3. TOPICS: id_topic, id_razdel, title_lopic, id_author, name_author, last_post_id, last_poster_id, last_poster_name, date_time_last_post, count_posts.
4. RAZDEL: id_razdel, title_razdel.
Заранее хочу оптимизировать структуру БД под довольно большую нагрузку: 5-8 млн. сообщений, 300-500 тыс. тем, 30-50 тыс. хостов в день и около 200-300 тыс. просмотров в день. В онлайне постоянно 300-800 человек.
-------------------------------------------------------
1 глобальный ВОПРОС
При добавлении юзером сообщения оно добавляется в таблицу POSTS, одновременно в таблице USERS обновляется поле count_post, а так же в таблице TOPICS обновляются поля: last_post_id, last_poster_id, last_poster_name, date_time_last_post, count_posts.
Мне нужно сделать запрос на формирование главной страницы форума (например как на форуме sqlinfo.ru).
То есть нужно вытащить название раздела, количество тем и сообщений в разделе, автора и время последнего поста в разделе.
Сначала планировал сделать так:
1. Первым запросом запрашиваю из таблицы RAZDEL название разделов.
2. Из TOPICS запрашиваю все поля для тем, сгруппировав по разделам и найдя для каждого раздела тему с максимальным last_post_id.
То есть у меня будет для каждого раздела вытянуты параметры темы в которой написано последнее сообщение в разделе:
SELECT t1.id_topic, t1.title_topic, t1.id_author, t1.name_author, t1.last_post_id, t1.last_poster_id, t1.last_poster_name, t1.date_time_last_post
FROM TOPICS t1
WHERE t1.last_post_id = ( SELECT t2.last_post_id
FROM TOPICS t2
WHERE t2.id_razdel = t1.id_razdel
ORDER BY t2.last_post_id DESC LIMIT 1 )
ORDER BY t1.last_post_id DESC;
3. Третьим запросом вытаскиваю с помощью GROUP BY количество тем и постов для каждого раздела.
Теперь появилась идея в таблицу RAZDEL добавить поля: last_post_id, last_poster_id, last_poster_name, date_time_last_post, count_posts, count_topics.
Терерь при добавлении юзером сообщения, оно добавляется в таблицу POSTS, одновременно в таблице USERS обновляется поле count_post, в таблице TOPICS обновляются поля: last_post_id, last_poster_id, last_poster_name, date_time_last_post, count_posts,
в таблице RAZDEL обновляются поля: last_post_id, last_poster_id, last_poster_name, date_time_last_post, count_posts. И при создании темы обновляется count_topics.
Теперь одним простым запросом можно сформировать главную страницу.
Что с точки зрения оптимальности лучше сделать: первый вариант или второй (догадываюсь, что второй).
Но вот не слишком ли много АПДЕЙТОВ в 3-х таблицах при добавлении одного поста при довольно частом постинге? Не будет ли при многочисленных апдейтах тормозить сервер в моём случае при такой нагрузке?
Отредактированно new_saler (14.01.2013 12:21:29)
Неактивен
2 глобальный ВОПРОС
Это на счет хранения текста поста в таблице POSTS.
Как я уже писал: заранее хочу оптимизировать структуру БД под довольно большую нагрузку: 5-8 млн. сообщений, 300-500 тыс. тем, 30-50 тыс. хостов в день и около 200-300 тыс. просмотров в день. В онлайне постоянно 300-800 человек.
У данного поля post_text будет индекс FULLTEXT. Не лучше ли будет хранить текст постов в отдельной таблице, где будет id_post и post_text?
До этого в книге прочитал, что из-за наличия большого текстового поля в таблице и индекса FULLTEXT на нем может снижаться скорость доступа к таблице, а ведь при такой нагрузке из таблицы POSTS будут постоянно выбираться например 20 постов для формирования определённой страницы при просмотре темы.
А так же будут постоянно добавляться новые посты – не замедлит ли индекс FULLTEXT добавление данных (ведь, если я не ошибаюсь, при добавлении будет перестраиваться индекс для всей этой огромной таблицы.)
Не лучше ли будет выбрать id поста из таблицы POSTS а потом join-ом присоединить текст поста к данной выборке?
Неактивен
Если сильно много написал, то хотя бы этот вопрос:
1. Не слишком ли много АПДЕЙТОВ в 3-х таблицах (users, posts, topics, razdel) при добавлении поста?
Получается, что много апдейтов, НО формирование главной страницы происходит за один простой запрос (так же как и страницы просмотра тем).
Отредактированно new_saler (16.01.2013 13:34:05)
Неактивен
Возможно отвечу не совсем на заданные вопросы. Спросите еще, если что-то останется непонятным.
1. Заранее оптимизировать обычно нельзя (Преждевременная оптимизация - корень всех зол). В этом смысле избыточность информации за счет денормализации - то, что делается последним, после того как готова вся база. Чтобы оптимизировать скорость нужно иметь реалистичное представление о количестве тех или иных запросов (например см. открытую статистику liveinternet на sqlinfo, чтобы оценить отношение просмотров и постов).
2. Существуют реализации форумов, которые в той или иной степени решают проблему производительности. Среди таких могу отметить punbb (движок, используемый на sqlinfo) и vbulletin - платный, но не дорогой коммерческий движок. punbb лучше, так как его код предельно прозрачен. Можно установить такой движок, наполнить контентом и смоделировать его нагрузку. Это даст понимание о том, какие именно запросы являются узким местом системы. Именно на них нужно будет сконцентрировать оптимизационные усилия.
3. Обновлять 3 или 4 таблицы - разницы принципиальной нет (таблица razdel маленькая - ее апдейт будет быстрым). В случае сверходновременности, рекомендуется использовать движок Innodb, так как в нем будет существенно меньше блокировок при одновременных выборках и обновлениях. Innodb не поддерживает FULLTEXT-индексы, поэтому это является аргументом для использования отдельной таблицы для текстов сообщений в формате MyISAM. Остальные аргументы не являются значимыми - индекс при добавлении записи не перестраивается, а просто добавляются слова данной записи. Некоторое замедление при этом есть. vbulletin, например, предлагает выбрать - использовать fulltext-индексы или собственную индексирующую поисковую систему.
4. В punbb информация о последнем сообщении продублирована в таблице forums. Это важный аргумент в пользу того, чтобы так сделать.
Неактивен
rgbeast, прочитал, немного осмыслил.
Пока что у меня вырисовывается такая картина:
1. Таблицу USERS сделать типа InnoDB, на основании что при добавлени юзером поста в ней будет обновляться поле count_post. То есть в таблице будем много частых обновлений и одновременно чтений: при просмотре страницы определенного топика данные из таблицы users будут JOIN-иться к данным из таблицы постов.
2. Таблицу TOPICS сделать типа InnoDB на основании того что в ней будет много обновлений полей last_post_id, last_poster_id и одновременно чтений при просмотре пользователями страницы раздела с топиками.
3. Таблицу POSTS сделать типа MyISAM потому что в ней будет мало обновлений и много вставок и чтений. Ведь MyISAM лучше чем InnoDB подходит для быстрого чтения.
Но тут же возникает вопрос: ведь столько же чтений будет из таблицы USERS (при просмотре страницы топика) а я таблицу USERS уже сделал типа InnoDB, так как в ней много обновлений... как с этм быть?
4. Текст сообщения не отделять отдельно, а поместить в таблицу POSTS вместе с остальными данными поста.
Так как прочитал, что MySQL хранит поля text и blob отдельно от остальных данных...
10.5. Data Type Storage Requirements: […] For BLOB and TEXT data, the information is stored internally in a different area of memory than the row buffer. […]
(Хотя так же встречал противоречивые мнения по этому поводу - люди расходятся во мнениях)
Основной запрос к таблице постов будет на формирование определённой страницы при просмотре топика.
Например хочу просмотреть 3-ю страницу (на странице выводится по 20 постов) топика с ID = 21:
Отредактированно new_saler (21.01.2013 15:51:19)
Неактивен
Для такого запроса индекс на id_post будет использован для сортировки, поэтому mysql выберет 60 записей, а потом отделит нужные 20. Если LIMIT будет для 100-ой страницы, например, то может иметь смысл заменить SELECT * на SELECT id_post, а потом уже вторым запросом выбрать нужные тексты.
Innodb не медленее, чем MyISAM на нагруженных проектах. Если posts будет в MyISAM, то в момент размещения нового сообщения все запросы будут ждать в очереди (а к таблице posts обращается большинство запросов на просмотр форума). Те цифры посещения, которые вы привели в первом посте подразумевают, что MyISAM может быть использован только как исключение и только для FULLTEXT-индекса.
Неактивен
Спасибо за ответы.
rgbeast написал:
Для такого запроса индекс на id_post будет использован для сортировки, поэтому mysql выберет 60 записей, а потом отделит нужные 20. Если LIMIT будет для 100-ой страницы, например, то может иметь смысл заменить SELECT * на SELECT id_post, а потом уже вторым запросом выбрать нужные тексты.
С этим вопросом разберусь позже - все протестирую и выберу оптимальный вариант.
rgbeast написал:
Innodb не медленее, чем MyISAM на нагруженных проектах. Если posts будет в MyISAM, то в момент размещения нового сообщения все запросы будут ждать в очереди (а к таблице posts обращается большинство запросов на просмотр форума). Те цифры посещения, которые вы привели в первом посте подразумевают, что MyISAM может быть использован только как исключение и только для FULLTEXT-индекса.
На счет цифр в первом посте это я погорячился…
Данные цифры возможны в довольно далекой перспективе - но я хотел заранее запроектировать БД на подобную нагрузку, чтобы максимально избежать модификаций в последующем.
В начале же реалии будут такие: размещение проекта на виртуальном хостинге до достижения некого порога посещаемости и нагрузки на сервер. (На первом этапе о VPS со своими конфигами оборудования и речи идти не может.)
А начальная задача сводится к оптимальной и легкой работе приложения на вирт. хостинге.
Поэтому я задумался, не лучше ли будет для данного этапа все-таки использовать тип таблиц MyIsam...?
Так как прочитал про многие достоинства и недостатки InnoDB и MyISAM и думаю, что для виртуального хостинга на первом этапе существования проекта возможно движок InnoDB будет тяжеловат. Вот какие аргументы я встретил в сети:
....Innodb требует настройки ....... зачастую необходимо прикидывать расход оперативной памяти сервера (с MyISAM это будет в разы меньше, чем с InnoDB).
…Приложение должно быть готово для работы с Innodb. К примеру, оно должно быть готово к возникновению дедлоков, которые в Innodb могут случаться даже если вы не используете транзакции, но никогда не случаются в MyISAM. Разумеется перед переходом нужно тщательно протестировать приложение.
…..Дефолтные конфиги, кроме my-innodb-heavy-4G.cnf(ini), выделяют минимальный объём памяти под буфера InnoDB, позволяющие ему как-то функционировать, но, при сколько-нибудь значительном объёме данных в таблицах InnoDB, далеко не оптимально. Пока в ваших тестах на тысячу записей вы вряд ли столкнулись с этим моментом, но, для реального использования этого движка, конфиги надо будет подкорректировать….
Боюсь, что для вирт. хостинга InnoBD будет тяжеловат, здесь смущает:
1. Расход оперативной памяти в разы ниже в MyISAM.
2. Дедлоки в InnoDB – не знаю что за зверь такой? Как с этим бороться?
3. Конфиги InnoDB на вирт. Сервере (минимальный объём памяти под буфера InnoDB). Повлиять на конфиги сервера на таком хостинге не получится.
Скажу, что это всё данные 2-4 летней давности. И возможно что-то изменилось? ,
Сейчас сделал анализ нескольких форумов: уник. посетители, просмотры и кол-во постов в минуту:
1. 35 000 уник. 137 535 просмотров. 1 сообщение пишется в минуту. (570 тыс. сообщений в базе)
2. Посещаемость скрыта. 1,6 сообщений в минуту. (960 тыс. сообщений в базе)
Получается, если я не ошибаюсь, не очень много insert в таблицу posts и соответственно update(в таблицах users, topics, razdel) : 1-2 апдейта всех таблиц в минуту и это уже на довольно посещаемых проектах.
Что скажете на счет типа движка под данную, начальную, задачу?
Потом со временем я так понимаю поменять тип движка очень просто.
Отредактированно new_saler (25.01.2013 16:59:32)
Неактивен
Это меняет постановку задачи. Одно дело как пробежать 100 км за сутки, а другое дело если речь идет про 2 км. То же самое с памятью - под изначальную задачу сразу представляется сервер с 128 гигами оперативки - понятно, что вопрос размера буферов по-умолчанию не стоит. Ряд вопросов очень сильно зависят от контекста - например "одеть ли шапку" имеет разный смысл в зависимости от того на улице 0 или -50.
Для VPS память будет существенна, так как конфигурации обычно ограничены по памяти и лишние 50 мегабайт на Innodb могут потребовать более высокого тарифа. В этом случае отключение InnoDB дает заметную экономию. В случае виртуального сервера круг возможностей обсуждать нагрузки очень узкий - нужно использовать тот движок, который удобнее хостеру (скорее всего MyISAM) и стараться не писать очень медленных запросов. Если будет реальная посещаемость, возникнет нагрузка на CPU (превышение разрешенного ресурса CPU) и хостер попросит перейти на VPS.
Неактивен
