Форум пользователей MySQL

al420 · 04.01.2011 13:41:54

Требуется совет опытного специалиста в организации базы данных личных сообщений. Задача кажется простейшей, но их очень много (уже порядка 80 млн) и работают они под большой нагрузкой (до 1 тыс запросов в сек).

Сейчас всё организовано в три таблицы, с использованием партицирования.

-- Входящие. Используется партицирование по pm_user_id - пользователю, который получает сообщение. Партиций на самом деле больше.

CREATE TABLE IF NOT EXISTS `pm_in` (

  `pm_id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `pm_user_id` int(10) unsigned NOT NULL DEFAULT '0',

  `pm_fromuser_id` int(10) unsigned NOT NULL DEFAULT '0',

  `pm_status` enum('0','1') NOT NULL DEFAULT '0',

  `pm_delete` enum('0','1') NOT NULL DEFAULT '0',

  KEY `pm_id` (`pm_id`),

  KEY `pm_user_id` (`pm_user_id`)

) ENGINE=MyISAM  DEFAULT CHARSET=utf8

PARTITION BY RANGE (`pm_user_id`)

(PARTITION p0 VALUES LESS THAN (20000) ENGINE = MyISAM,

 PARTITION p1 VALUES LESS THAN (40000) ENGINE = MyISAM,

 PARTITION p2 VALUES LESS THAN (60000) ENGINE = MyISAM);

-- Исходящие. То же самое почти, только партиции организованы по pm_fromuser_id - юзеру от которого сообщение. И ещё `pm_delete` тоже разные, в одном случае - если удалено во входящих, в другом если в исходящих. pm_id во всех трёх таблицах совпадают для одного сообщения.

CREATE TABLE IF NOT EXISTS `pm_out` (

  `pm_id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `pm_user_id` int(10) unsigned NOT NULL DEFAULT '0',

  `pm_fromuser_id` int(10) unsigned NOT NULL DEFAULT '0',

  `pm_status` enum('0','1') NOT NULL DEFAULT '0',

  `pm_delete` enum('0','1') NOT NULL DEFAULT '0',

  KEY `pm_id` (`pm_id`),

  KEY `pm_authoruser_id` (`pm_authoruser_id`)

) ENGINE=MyISAM  DEFAULT CHARSET=utf8

PARTITION BY RANGE (`pm_authoruser_id`)

(PARTITION p0 VALUES LESS THAN (20000) ENGINE = MyISAM,

 PARTITION p1 VALUES LESS THAN (40000) ENGINE = MyISAM,

 PARTITION p2 VALUES LESS THAN (60000) ENGINE = MyISAM);

-- Таблица для хранения темы и текста сообщения

CREATE TABLE IF NOT EXISTS `pm_content` (

  `pm_id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `pm_date` int(10) NOT NULL,

  `pm_subject` varchar(50) NOT NULL,

  `pm_body` varchar(3000) NOT NULL,

  PRIMARY KEY (`pm_id`)

) ENGINE=InnoDB  DEFAULT CHARSET=utf8

Зачем две таблицы для входящих/исходящих и почему выбран MyISAM.

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

MyISAM - нужен чтобы избавится от ORDER BY, без сортировки получается быстрее. Если записи добавляются последовательно, но они и при запросе достаются с той же самой последовательностью, сортировка не нужна.

На чём реально можно выйграть в данном случае:
Мне кажется, как-то можно использовать тот факт, что личные сообщения организованы постранично, по 20 на странице. И дальше второй страницы пользователь лазит редко. Наверное можно как-то переложить часть ЛС в таблицы с такой же структурой, но только условно-архивные. Только как потом прозрачно организовать работу с этим всем....

Отредактированно al420 (04.01.2011 13:50:16)

paulus · 04.01.2011 14:13:24

Насколько я понимаю, Вы не удаляете строки (чтобы обеспечить хитрую сортировку).
Пусть у человека есть 1000 входящих сообщений. Он видит первые 20. Это сообщения,
которые пришли год назад? Или я что-то не понимаю в этой схеме?

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

Я бы не закладывался на недокументированные особенности хранилищ. Кажется, InnoDB
с нормально организованными индексами, должен держать такую нагрузку. Ну и, если
будете переписывать, заложите шардирование сразу — все равно понадобится вдаль-
нейшем.

al420 · 04.01.2011 14:24:26

Всё верно строки не удаляются, просто выставляется флаг удаления. Это позволяет пользователю восстановить сообщение в течение некоторого времени. И ещё, это позволяет нумеровать сообщения для каждого пользователя отдельно. Например, я у себя удаляю не 123546547ое сообщение (это реальный `pm_id` в базе), а всего-лишь 34ое. А 35го "нету", я его раньше удалил, оно лежит в базе и считается но не показывается. Есть 36ое, и.т.д. ВКонтакте реализована такая фишка. Благодаря этому хранение сообщений одного пользователя можно организовать как угодно, на сколько угодно таблиц, а порядковые номера сообщений останутся прежними.

Он видит первые 20. Это сообщения, которые пришли год назад? Или я что-то не понимаю в этой схеме?

Нет, первые 20 которые он видит - это последние 20 которые пришли. LIMIT в запросе тоже нету, запрашиваются все ID-шники сообщений этого пользователя, нумеруются по порядку c учётом удалённых, массив реверсируется и новые оказываются в начале, потом к тем ID-шникам которые пришлись на страницу - подтягиваем тему и текст сообщений из `pm_content`.

Вот, теперь вроде полностью расписал )

Можно и реально удалять строки. Приходится, чтобы было меньше записей. Удаляются сообщения, у которых во входящих и исходящих стоит флаг удаления. Но тогда после удаления требуется делать OPTIMIZE TABLE и потом ещё ALTER TABLE ... ORDER BY, чтобы не падала недокументированная сортировка

Интересно, а как практически реализовано в том же Контакте, есть идеи?

Отредактированно al420 (04.01.2011 14:40:20)

paulus · 04.01.2011 15:34:47

То есть вы вытаскиваете всегда все записи? А если их станет очень очень много?

У вконтакта есть шардирование. У них большой датацентр, в котором крутится
большое количество машин, которые этим занимаются. Наверняка, есть и собст-
венное хранилище данных, но про это не знаю точно. У facebook есть.

Я бы на Вашем месте попробовал ORDER BY + LIMIT. Ну и шардирование, разу-
меется.

al420 · 04.01.2011 16:02:26

Да, вытаскиваю все. Очень много - уже стало. Вот и пишу

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

Теперь, видимо, придётся лепить всё снова с одну таблицу, использовать ORDER BY + LIMIT, а старые сообщения (например, которым больше месяца) - перекладывать в архивную таблицу по ночам. Или не старые, а когда их больше 40 (не вошли в первые 2 страницы). Но в этом случае придется подгружать архив, если пользователь удалит все новые...

Спасибо за советы, попробую построить на этих принципах.

paulus · 06.01.2011 17:13:51

Если разумно организовать шардирование, то архивные таблицы не понадобятся.
Ну и на InnoDB посмотрите, не нужно будет делать сто тысяч разделов, чтобы
сэмулировать строчную блокировку

TK · 17.01.2011 21:25:00

решение с myisam, соритировкой и архивированием красивое, но послушайте еще одного человека: шардинг, партиции по необходимости, innodb, свежий mysql или innodb plugin. ну и если будет где потестить - handler socket.

Форум пользователей MySQL

#1 04.01.2011 13:41:54

Личные сообщения, оптимизация нагрузки

#2 04.01.2011 14:13:24

Re: Личные сообщения, оптимизация нагрузки

#3 04.01.2011 14:24:26

Re: Личные сообщения, оптимизация нагрузки

#4 04.01.2011 15:34:47

Re: Личные сообщения, оптимизация нагрузки

#5 04.01.2011 16:02:26

Re: Личные сообщения, оптимизация нагрузки

#6 06.01.2011 17:13:51

Re: Личные сообщения, оптимизация нагрузки

#7 17.01.2011 21:25:00

Re: Личные сообщения, оптимизация нагрузки

Board footer