Всем привет. Помогите решить такую задачу.

Имеется общий фонд (пул) для накопления джекпота. С каждой ставки в игре этот фонд увеличивается на какую-то сумму (процент от ставки). В табличном виде что-то типа:
,где с каждой ставки идёт

Когда это всё делали, то игроков было немного и борьба за захват записи была небольшой. Но с увеличением кол-ва игроков само-собой началась борьба за этот ресурс. Попробовали добавить дополнительное поле part_id - разбить джекпот на части, но это не дало ощутимой разницы, что очень удивило, так как при, скажем, 50 игроках и без part_id задержек практически нет, а при
800 игроках и 64 part_id задержки появляются, при том, что борьба за общую запись должна была снизиться.

Есть определенные требования, которые необходимо соблюсти:
1) Нельзя пропускать ни одной ставки. То есть все финансы должны точно совпадать
2) Сумма обновленного джекпота должна быть возвращена сразу после обновления (это, так скажем, крайне желательное условие)
3) Нельзя аккумулировать джекпот отдельно для каждого пользователя (то есть нельзя добавить user_id в эту таблицу), так как пользователей, учавствующих в розыгрыше джекпота, может быть сотни тысяч, и:
а) выборка общей суммы джекпота становится тяжелой.
б) джекпот могут часто выигрывать, и необходимо лочить все записи.

Есть ли какие-то идеи как лучше (правильнее) организовать накопление джекпота?

Maxim BogukThamerlan,

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

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

PS: включите log_lock_waits в конфиге, может что то интересное увидите.

--Maxim Boguk
www.postgresql-consulting.ru

Максим, я специально не стал указывать про железо, логи и прочее. Просто мне, в идеале, хотелось бы найти альтернативные решения задачи. Но если их нет, тогда конечно последний выход это оптимизация имеющегося решения.
Так вот по железу:
1) С дисковой проблем нет (ssd в 5 или 6 раиде)
2) Нехватка CPU имеет место быть. E5-2620 0 @ 2.00GHz с 1 cpu c 12 ядрами, причем на vmware. Но как я уже писал, я ожидал улучшения после разбивки фонда джекпота на части, но его не получил. То есть понятно, что перейдя на машинку с 40 ядрами я улучшу ситуацию, но проблему в принципе не решу.

По логам:
log_lock_waits на 1сек включал. При "50 игроках и без part_id" ничего не получал. Ну может пару-тройку раз было за 10 минутный stress test.
При "800 игроках и 64 part_id" засыпало wait'ами по 4-8 секунд при пополнении фонда, и временами до 30 секунд при выигрыше джекпота.

А вот насчёт этого:
Maxim BogukТам например возможен вариант конкуренции да, но вызванной не собственно обновлением строки а долгой транзакцией охватывающей этот update.
наверное буду думать. Транзакцией управляет приложение на java. Хотя, ява-разрабы говорят, что сразу после вызова хранимки на увеличение джекпота делают коммит. Но наверное стоит попробовать самому вызывать через dblink функцию пополнения с моментальным коммитом.


Ivan Durakпропущено...
имхо, асинхронно.
т.е. отдельный процесс, который постоянно пробегает по новым ставкам и апдейтит джекпот.
Он один - никаких ожиданий бокировок.
Да, фоновая обработка с предварительным аггрегированием это логичное и красивое решение, но в данном случае реальное состояние джекпота должно быть доступно моментально.
И выигрыш (если он был) тоже должен произойти сразу после выигрышной ставки (до следующей ставки).

думаеццаps
2. в длине цепи из N конкурентов участвует еще и длина единичной "бесконкурентной" транзакции. если она простая -- пишем только данные и сумму -- то это, скорее всего [там должно быть шаманство с записью не напрямую в таблицы, а послежовательно в "журналки", чтобы не позиционировать головки], время последовательного доступа на запись, и, если в память таблица сумм не ложится [что вряд ли] -- время произвольного [-- что плохо] доступа на чтение. для 1000 доступов к суммам 1000 джекпотов, которые почему-то на разных страницах и может быть нехорошо, но если все суммы в памяти -- время рендом-доступа выпадет и все станет много приличнее. т.е. предлагаю сначала пошерстить длину единичной транзакции. если там много левой логики -- попытаться всю левизну скинуть в асинхрон. ну а параллельное не блокирующее синхронное накопление я описывал выше
... много левой логики. Да с этим буду разбираться. Как уже написал выше, попробую внешний вызов через SQL/MED, чтобы понять, что лишняя логика АПП не является тут причиной.

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

Ivan Durakна тебе еще одну идею - вместо
UPDATE table SET amount=amount+<%_от_ставки> WHERE jackpot_id = <тут_id>;
2. делай Insert into table (id, jackpot_id, amount) select sequence.nextval, <тут_id>, <%_от_ставки> + (select amount from table
where jackpot_id = <тут_id> and id = sequence.curval)


Интересная версия хранить актуальную сумму в последней записи, только я не уверен, что sequence.curval всегда даст последнее правильное значение фонда при большой параллельной нагрузке. Тогда лучше, наверное , advisory lock использовать для организации последовательного доступа к последней записи. И фрагментация таблицы из-за удалений неактуальных значений будет огого. Но я возьму эту идею на подумать. Спасибо.

Maxim BogukThamerlan,

вообще совет для тестирования
1)dealock_timeout=100ms + log_lock_waits
+
2)log_min_duration_statement=0 + log_line_prefix=%m %p %u@%d from %h [vxid:%v txid:%x] [%i]

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

--Maxim Boguk
www.postgresql-consulting.ru

В данном случае я и так знаю, что lock'и идут из-за борьбы за этот update. Уменьшив "dealock_timeout=100ms + log_lock_waits" я узнаю ещё много нового, но хотелось бы сначала побороть главный bottleneck :)

NikolayV81
Есть простое решение, джепот в память, в базу штамп последнего розыгрыша, в случае падения сервера джекпот пересчитывать при старте с момента после штампа розыгрыша.

Так как я явно упираюсь в CPU, а не в IO, то джекпот в памяти не даст в данном случае никакого прироста производительности, так как там тоже надо организовывать последовательный доступ к общему ресурсу. Разве что только сам Postgresql медленно ставит/снимает lock'и с ресурса и другое "хранилище" будет просто это делать быстрее.

Попробовал сделать commit сразу после UPDATE (через dblink). Как и ожидалось - драка за ресурс сразу упала до минимума, то есть java в самом деле делала commit с микро задержкой, что всё-таки сильно влияло на освобождение русурса. И всё же, даже в таком случае видны 20-150мс задержки в логе.
Поэтому буду таки продавливать "не совсем честное" решение с фоновым пересчётом (набор дельта инсертов + потом один update фонда и delete дельты).

Всем спасибо за идеи.

tadminА какой пул использует ваше java приложение, и сколько коннектов к pg поднимает пул под нагрузкой?
Не используете ли временные таблицы on commit delete rows?

Hibernate. при стресс тестах (600 пользователей) в определённые моменты доходит до 400 соединений (разрешено конечно больше), но в основном около 200-300 открытых соединений и большинство, по pg_stat_activity, неактивны в конкретный момент.
Временные таблицы не используем.

P.s.
Не предлагайте перейти на i/my-batis или pgbouncer. В данный момент это нереально. :)