Огромная база в мускуле

[Unico]

Ребяты, а где-нить уже выработаны рекомендации...

что делать с огромными базами.

 

у меня сервер с 4 гигами оперативы.

на форуме - 1,2 млн сообщенией.

база по пхпмайадмин - 2 гига.

 

----

 

седня проблема возникла после ежедневного бэкапа...

пришлось будить админа. ниже отрывки из переписки, если вам интересно:

 

----

 

Клиент (05:28:56 10/06/2010)

mySQL error: User b00001 has already more than 'max_user_connections' active connections

 

Админ (05:55:25 10/06/2010)

мусль похоже забил временными файлами раздел var несмотря на то что сами базы в другом разделе

 

Админ (05:55:40 10/06/2010)

у тебя нет мега больших таблиц которые между собой джойнятся?

 

Админ (06:05:41 10/06/2010)

джойница это селект запросы с join когда один запрос по двум таблицам сразу идет тогда создается временная таблица на диске

 

Клиент (06:08:23 10/06/2010)

фиг знает... спросить надо на ibresource.ru

стандартный движок форума...

 

Админ (06:16:43 10/06/2010)

я мускулю изменил папку для временных файлов посмотрим как он с этим жить будет

 

Админ (06:22:24 10/06/2010)

3.2G /usr/var/db/mysql/b00001_1

почему так много?

 

Админ (06:23:03 10/06/2010)

1.2G ibfposts.MYD

767M ibfposts.MYI

1.2G ibfposts.TMD

10K ibfposts.frm

 

Клиент (06:23:37 10/06/2010)

ну да...

1.2 posts

это таблица с постами форума

у меня 1,2 млн сообщений

 

Клиент (06:27:10 10/06/2010)

а вот тут 4 млн. сообщений

teron.ru

 

Админ (06:28:20 10/06/2010)

в версии 5.1 мускуля есть хорошая вещь переписывать движок не надо просто меняешь саму таблицу немного и у тебя таблица хранится по годам например

 

Админ (06:28:34 10/06/2010)

http://m.habrahabr.ru/post/66151/

 

Админ (06:29:00 10/06/2010)

у тебя пока старая версия стоит 4.1.22,

 

Админ (06:31:52 10/06/2010)

в общем такие таблицы большие надо или разбивать как тут написанно http://m.habrahabr.ru/post/66151/

либо переводить на другой тип - типа innodb

 

----

 

Вопрос всё тот же..

что нужно делать для оптимизации работы больших форумов?

[FatCat]

Скорее всего разрослась ibf_posts.

ИМХО, всего 2 варианта:

1. Выносить старые топики в другую базу на копии движка в режим только для чтения. В действии можно посмотреть на форуме Экслера.

2. Мой вариант (на vesvalo.net) с упаковкой старых топиков и с раздельным хранением в БД и в файлах в зависимости от размера поста: всё, что больше 4 Кб хранится не в БД, а в файлах.

[Unico]

Fatcat, а как тебе идея с этим партиционированием?

ссылка на хабр

[Sannis]

Админ (06:05:41 10/06/2010)

джойница это селект запросы с join когда один запрос по двум таблицам сразу идет тогда создается временная таблица на диске

Школоло атакуе.

[Unico]

FatCat, вариант экслера с сохранением старых урлов можно сделать?

поставив статус архивный в админке, мы снизим нагрузку на майэскуль-сервер?

может, еще кеширование запросов к этой базе архивной как-то можно сделать?

 

Sannis, ежели, что по делу напишите еще... буду благодарен

 

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

ну нужно ж как-то предусмотреть на будущее... разделение постов на несколько табличек..

зачем всё в одно место валить.

 

во всех этих новых версиях по сути одна косметика

[Sannis]

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

ну нужно ж как-то предусмотреть на будущее... разделение постов на несколько табличек..

зачем всё в одно место валить.

Это делается на уровне БД, школоло дало вам в общем-то полезную ссылку.

 

P.S. IPB в массовом виде не для этого разрабатывается. Стоимость работы PHP инженеров, которыми так кичиться IPS и крутых спецов по highload разнятся на порядок.

[Unico]

Зря вы насчет "школоло"...

просто человек мне объяснял как чайнику.

а так у него около сотни серверов или даже больше на администрировании.

 

по ссылке я плохо соображаю.. что получится в итоге.

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

у меня на форуме много тем старых.. их постоянно поднимают.

 

разве будет существенная разница в плане производительности?

если запрос будет к одной большой таблице...

либо к частям этой таблицы будет несколько запросов одновременно?

 

как выглядит этот процесс партиционирования...

просто выполнить запрос в базе и всё? или надо в код вносить изменения?

за денежку кто-нить согласится сделать.. сколько стоить будет?

 

но самый главный вопрос.. поможет ли это

Изменено 10 Июня 2010 пользователем Unico

[FatCat]

ссылка на хабр

Где?

[xRay]

ссылка на хабр

Где?

в его посте вот такая ссылка есть ка крах на Хабр

http://m.habrahabr.ru/post/66151/

ну или вот так

http://habrahabr.ru/post/66151/

[FatCat]

Закешировалась "трешка" не по-детски. У меня в прошлый раз половины постов передо мной не было видно...

С партициями интересная возможность, поэкспериментирую в локалке. Спасибо за информацию!

 

FatCat, вариант экслера с сохранением старых урлов можно сделать?

Там перенос в другую БД; соответственно и урлы меняются. Другой вопрос, что можно заморочиться и сделать для перенесенных урлов внешние линки с 301-м редиректом.

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

[Kvendi]

Зря вы насчет "школоло"...

просто человек мне объяснял как чайнику.

а так у него около сотни серверов или даже больше на администрировании.

 

по ссылке я плохо соображаю.. что получится в итоге.

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

у меня на форуме много тем старых.. их постоянно поднимают.

 

разве будет существенная разница в плане производительности?

если запрос будет к одной большой таблице...

либо к частям этой таблицы будет несколько запросов одновременно?

 

как выглядит этот процесс партиционирования...

просто выполнить запрос в базе и всё? или надо в код вносить изменения?

за денежку кто-нить согласится сделать.. сколько стоить будет?

 

но самый главный вопрос.. поможет ли это

Разбиение для вас ничего не даст.

Любой безусловный count(*) и иже с ними убьет все на корню (будет только медленней, чем сейчас).

Разбиение без правки core ipb не имеет смысла.

Хотя я могу ошибаться, но спецы поправят: в IPB кешированные счетчики же выбираются безусловно ? т.е. прямыми count-ами ?

Далее - при входе в тему оно все равно же будет забирать кол-во ответов для паджинации ?

ну и далее в том же духе. В общем если я не ошибся в своих предположениях на счет схемы работы ipb разбивать таблицы без правки кода смысла не несет. (Хотя есть вариант в подмене стандартных таблиц ipb представлениями с аналогичным названием, в которых ограничивать выборку из уже разбитых таблиц. Это будет даже лучше).

А в вашем случае- включить логирование slow queries и no index. Поставьте в качестве порога медленных запросов для начала секунд 10. Затем берите каждый запрос из лога, анализируйте explain-ом и улучшайте саму структуру БД.

[FatCat]

Разбиение без правки core ipb не имеет смысла.

Подтверждаю.

Экспериментировал с миллионной базой прежде, чем делать свой мод раздельного хренения информации. Вот эксперимент:

В первом случае средний размер сообщения 1000 символов, размер базы 1.2 Гб; во втором случае вместо сообщений одиночный пробел, размер базы около 70 Мб.

Денвер5 на ноутбуке.

Выполнялся один и тот же запрос:

SELECT * FROM ibf_posts WHERE topic_id = 999 ORDER BY pid ASC LIMIT 15,15

 

В первом случае не хватило 30 секунд таймлимита, во втором случае меньше секунды.

[Kvendi]

Разбиение без правки core ipb не имеет смысла.

Подтверждаю.

Экспериментировал с миллионной базой прежде, чем делать свой мод раздельного хренения информации. Вот эксперимент:

В первом случае средний размер сообщения 1000 символов, размер базы 1.2 Гб; во втором случае вместо сообщений одиночный пробел, размер базы около 70 Мб.

Денвер5 на ноутбуке.

Выполнялся один и тот же запрос:

SELECT * FROM ibf_posts WHERE topic_id = 999 ORDER BY pid ASC LIMIT 15,15

 

В первом случае не хватило 30 секунд таймлимита, во втором случае меньше секунды.

Сдается мне это из-за fulltext индекса в ibf_posts (если там он был, или просто из-за индекса по полю текста сообщения). Можно попробовать решить select no index в этом случае.

[Sannis]

Руки чешутся проверить, но не сейчас.

А от фултекста нужно в любом случае на больших форумах переходить на Сфинкс.

[Ritsuka]

мод раздельного хренения информации

Извините, не сдержался

 

Мне пока такие проблемы не грозят, но из любопытства зачитывался одно время этой веткой на IPS. Так вот - у ребят есть базы и по 20, и по 100Gb. Используют InnoDB и мощные сервера, и не жалуются...

[FatCat]

Сдается мне это из-за fulltext индекса в ibf_posts

Убит давным-давно.

[Kvendi]

Сдается мне это из-за fulltext индекса в ibf_posts

Убит давным-давно.

мм, тогда давайте ради интереса разберемся- покажите структуру с индексами и explain запроса, интересно даже )

[FatCat]

давайте ради интереса разберемся- покажите структуру с индексами и explain запроса

Структура: обычная ibf_posts от 1.3 с убитыми текстовыми индексами.

Сейчас повторить запрос не смогу, просто нет базы-миллионника; для экспериментов делал ее искуственно, дублируя 20 раз 50-тысячную таблицу.

Экспериментов делал много, и выявил, что тормоза не зависят от числа записей в базе, а зависят от размера самой таблицы; а если совершенно точно, то от размера файла ibf_posts.MYD

Где-то до 60-70 Мб размера файла запросы выполняются моментально - несколько милисекунд.

В интервале 70-200 Мб скорость запроса ощутимо нарастает, но может быть снижена "дефрагментацией" файла путем простейшей манипуляции:

SELECT * INTO OUTFILE 'ibf_posts.sql' FROM ibf_posts ORDER BY topic_id;
TRUNCATE ibf_posts;
LOAD DATA INFILE "ibf_posts.sql" INTO TABLE ibf_posts;

Больше 200 Мб происходит скачкообразный прирост времени выполнения запроса.

Я предполагаю, что sql грузит всю таблицу в оперативную память, а лимит оперативы порядка 200 Мб, выше которого винда начинает свопить.

Это подтверждается расхождениями цифр времени выполнения запроса с реальным временем ожидания; нередко цифры пишет меньше секунды, а ожидание ответа измеряется десятками секунд.

Не знаю, может быть тормоза возникают не на самом sql, а на связке апача с базой; мне в принципе все равно, мне важно, что тормоза есть и на локалке под виндой, и на стандартном юникс-хостинге.

[Kvendi]

давайте ради интереса разберемся- покажите структуру с индексами и explain запроса

Структура: обычная ibf_posts от 1.3 с убитыми текстовыми индексами.

Сейчас повторить запрос не смогу, просто нет базы-миллионника; для экспериментов делал ее искуственно, дублируя 20 раз 50-тысячную таблицу.

Экспериментов делал много, и выявил, что тормоза не зависят от числа записей в базе, а зависят от размера самой таблицы; а если совершенно точно, то от размера файла ibf_posts.MYD

Где-то до 60-70 Мб размера файла запросы выполняются моментально - несколько милисекунд.

В интервале 70-200 Мб скорость запроса ощутимо нарастает, но может быть снижена "дефрагментацией" файла путем простейшей манипуляции:

SELECT * INTO OUTFILE 'ibf_posts.sql' FROM ibf_posts ORDER BY topic_id;
TRUNCATE ibf_posts;
LOAD DATA INFILE "ibf_posts.sql" INTO TABLE ibf_posts;

Больше 200 Мб происходит скачкообразный прирост времени выполнения запроса.

Я предполагаю, что sql грузит всю таблицу в оперативную память, а лимит оперативы порядка 200 Мб, выше которого винда начинает свопить.

Это подтверждается расхождениями цифр времени выполнения запроса с реальным временем ожидания; нередко цифры пишет меньше секунды, а ожидание ответа измеряется десятками секунд.

Не знаю, может быть тормоза возникают не на самом sql, а на связке апача с базой; мне в принципе все равно, мне важно, что тормоза есть и на локалке под виндой, и на стандартном юникс-хостинге.

Ну на самом деле с 200-ми метрами оперативки нет смысла что-то тестить или оптимизировать - все равно упремся в узкое место дискового массива.

[FatCat]

с 200-ми метрами оперативки нет смысла что-то тестить или оптимизировать - все равно упремся в узкое место дискового массива.

В это похоже и уперся топикстартер.

 

Так получилось, что у меня один из форумов - форум некоммерческой организации, и он весь в принципе ориентирован на волонтерскую работу, а не на заработок. Соответственно, по мере роста форума, не вкладывались деньги в увеличение возможностей хостинга, а добровольно развивался движок форума по вопросам ресурсоемкости. Получился движок, выдерживающий сотни одновременных посетителей на обычном шареде с гигабайтом дискового пространства, почти гигабайт сообщений в форуме занимает на диске на сегодня 250 Мб: все крупные сообщения сжаты gZIP-ом и разжимаются динамически по мере надобности.

[Kvendi]

с 200-ми метрами оперативки нет смысла что-то тестить или оптимизировать - все равно упремся в узкое место дискового массива.

В это похоже и уперся топикстартер.

 

Так получилось, что у меня один из форумов - форум некоммерческой организации, и он весь в принципе ориентирован на волонтерскую работу, а не на заработок. Соответственно, по мере роста форума, не вкладывались деньги в увеличение возможностей хостинга, а добровольно развивался движок форума по вопросам ресурсоемкости. Получился движок, выдерживающий сотни одновременных посетителей на обычном шареде с гигабайтом дискового пространства, почти гигабайт сообщений в форуме занимает на диске на сегодня 250 Мб: все крупные сообщения сжаты gZIP-ом и разжимаются динамически по мере надобности.

ну оптимизаций/ускорений не на уровне БД можно придумать, думаю, множество. Но то, что модель БД в ipb не выдерживает никакой критики остается правдой )

[Unico]

Ребяты, в общем админ мне просто сделал другую папку для временных файлов...

раньше они создавались в var-разделе вроде как.

Вот при бэкапе и repair ежесуточном ночном var-раздел забился и там что-то глюкнуло.

 

Сейчас вроде все нормально.

Работа форума быстрая.

напомню оперативы 4 гига

база по пхпмайадмин 2 гига

 

Но вопрос по выработке общих рекомендаций при больших базах по-прежнему актуальный.

 

>>>>>>Мне пока такие проблемы не грозят, но из любопытства зачитывался одно время этой веткой на IPS. Так вот - у ребят есть базы и по 20, и по 100Gb. Используют InnoDB и мощные сервера, и не жалуются...

 

этот момент заинтересовал

[Song]

Джойнится ibf_posts с ibf_topics при выводе топиков.

Это давно известный напряг.

Отображение тем в Forums.php, тяжёлый запрос Оптимизация 1.x

[Unico]

Song, по этой ссылке:

http://www.sysman.ru/index.php?showtopic=15999

 

вот это

>>>Исправление: отключение выполнения запроса в админке

 

и это ниже

>>>По аналогии с профилем оптимизируем запрос подсчёта количества тем в разделе.

 

это два одинаково эффективнных способа решения одной проблемы? на выбор делать либо то, либо другое?

то есть "точечки" можно оставить, а исправить вторым способом?

 

---

 

или это две разные проблемы с двумя соответственно разными решениями...?

Изменено 18 Июня 2010 пользователем Unico

[WildRAID]

Не знаю насчет MySQL, но на PostgreSQL секционирование больших таблиц через INHERIT дает огромный прирост производительности. Причем почти ничего даже не нужно переписывать - для приложения это по-прежнему будет единая таблица ibf_posts. Нужно будет поменять INSERT (указывать, в какую секцию вставлять новый пост), а также следить за созданием секций (раз в полгода-год добавлять новую).

 

Альтернативный вариант: использовать ltree. Это вообще улётная штука.

Добавляем в ibf_posts поле path для выборки в формате 'id_форума.id_топика', вешаем GIST-индекс с ltree_ops.

 

При обращении к странице темы делаем два основных запроса:

 

SELECT * FROM tbl_topics WHERE tid=X

 

SELECT *
FROM (
SELECT * 
FROM ibf_posts
WHERE path ~ '$forum_id.$topic_id' AND -- тут куча условий на любой вкус
ORDER BY id DESC --ORDER-стадию можно выкинуть грамотным CLUSTER!
LIMIT 30
OFFSET 0 -- странички
) AS p

JOIN (
tbl_members m
JOIN tbl_member_extra me ON (m.id=me.id)
) ON (m.id=p.author_id)

JOIN --и т.д. любые join'ы

 

По двум миллионам записей время сканирования индекса - меньше 30 мс.

http://www.sai.msu.su/~megera/postgres/gist/ltree/

 

И нет проблем.

 

Единственное - сравнительно медленные операции перемещения нодов. На огромной таблице может висеть секунд по 5-10. Но при этом не блокируются запросы на чтение. А посты .. часто ли вы их перемещаете?

 

Можно сделать совсем lighting-speed, добавив третий уровень в дерево - страницу.

Будет 'id_форума.id_топика.номер_страницы'

 

Правда, придется отказаться от премодерации, либо смириться с тем, что на одной странице может быть меньше постов.

 

Но зато - мы полностью выкидываем LIMIT, OFFSET и ORDER. Условие

WHERE path ~ '5.117.2'

одним index scan'ом выберет именно то, что нам нужно. И даже в нужной последовательности.

 

Profit! А главное, сделать легко.

[Song]

вот это

>>>Исправление: отключение выполнения запроса в админке

 

и это ниже

>>>По аналогии с профилем оптимизируем запрос подсчёта количества тем в разделе.

 

это два одинаково эффективнных способа решения одной проблемы? на выбор делать либо то, либо другое?

то есть "точечки" можно оставить, а исправить вторым способом?

 

Нет, это просто ещё одна оптимизация.