Система личных сообщений

[raketa]

Необходимо реализовать систему личных сообщения для высокой нагрузки
В данный момент все реализовано в одной достаточно стандартной табличке
user_from
user_to
created
read
subject
body
del_by_userfrom
del_by_userto
Ежедневно отправляется достаточно много сообщений и каждый день цифры увеличиваются, планируется как минимум ежемесячное увеличение таблицы на 1 млн. строк

В итоге узкие места
1. при серфинге по сайту каждый раз происходит запрос на кол-во новых сообщений count() where read=0 and user_to=99
2. очень часто происходит массовая отправка сообщение до 2000 вставок одновременно и пока порядка 200 пользователей которые теоретически могут отправить за день по несколько таких партий
3. пока в папке входящие выводится просто список сообщений по убыванию, нужно сделать группировку по диалогам в этом случае будет еще больше нагрузки

Из идей которые есть
1. разбить на две таблицы
первая это : pm_id, user_from, user_to, created, read, del_by_userfrom, del_by_userto
вторая: pm_id, subject, body
т.е. вынести поля фиксированной длины в отдельную таблицу

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

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

3. смешать и 1 и 2

4. организовать как key value базу
при отправке сообщения одна запись это входящие сообщение user_id, mess_type(inbox), mess , вторая запись исходящие сообщение user_id, mess_type(outbox), mess
как итог
+ при выборке будет меньше условий, можно будет получить диалог между пользователяи по запросу с использованием одного индекса по user_id
- при массовой отправке кол-во вставляемых строк удваивается
+ в будущем можно легко перенести данные в key value базу


Может кто поможет с реализацией на платной основе? нужен специалист кто занимался социальными сетями и высоконагруженными проектами

[bazik]

А сделать две вьюшки в БД?

[raketa]

А сделать две вьюшки в БД?

не совсем понял это как?

[wolandino]

А сделать две вьюшки в БД?

не совсем понял это как?

А вот так http://dev.mysql.com/doc/refman/5.6/en/create-view.html

[bazik]

Фишка в том, что ко вьюшкам можно будет обращаться, как к обычным таблицам.

[raketa]

не понятно что это мне дает в плане скорости

[because]

результат этой штуки count() where read=0 and user_to=99 можно сделать как поле-счетчик в таблице users, или завести отдельную табличку где хранить счетчики - число прочитанных, число непрочитанных

[raketa]

результат этой штуки count() where read=0 and user_to=99 можно сделать как поле-счетчик в таблице users, или завести отдельную табличку где хранить счетчики - число прочитанных, число непрочитанных

пробовал эту инфу хранить в кеше, пока неудачно счетчик сбивается (но это мой косяк где то)
это поможет но не полностью

[com]

1. при серфинге по сайту каждый раз происходит запрос на кол-во новых сообщений count() where read=0 and user_to=99

если очень напрягает подсчет количества непрочитанных сообщений, то в табличке юзеров завести поле "количество_непрочитанных", при добавлении нового сообщения соответствующему юзеру-получателю количество_непрочитанных = количество_непрочитанных+1, и заменить count() where read=0 and user_to=99 из таблицы сообщений на select unread where user=99 из таблицы юзеров. время выборки уменьшится на несколько порядков.
желательно поле unread поддерживать на уровне бд триггерами, иначе периодически будет нестыковка с количеством реально непрочитанных
насчет больших вставок - 2000 сообщений от 200 пользователей, это 400000 записей, да по несколько раз в день. а 1 млн в месяц - это 30000 в день. какая-то нестыковка, или я не до конца понял.
разбивать на несколько таблиц очень не рекомендую. если уж припрет, то правильней выгружать в отдельную таблицу точно такой же структуры архив, к примеру, сообщения старше определенной даты.
зы. написал, а потом заметил ,что это же самое советует because. непонятно, почему это поможет, но не полностью

[raketa]

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

Помимо кол-ва новых сообщений, вопрос еще в самой выборке как её ускорить с учетом того что будет группировка по диалогам и при этом чтобы скорость вставки не ухидшилась

желательно поле unread поддерживать на уровне бд триггерами, иначе периодически будет нестыковка с количеством реально непрочитанных

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


Что посоведуете со структурой БД, оставить как есть? и работать надо оптимизацией выборок

[com]

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