akubintsev
@akubintsev
Опытный backend разработчик

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

Прекрасно известно, что сортировать удобно по индексируемому полю.
Однако также известно, что использование индексов на часто изменяемых данных приводит к большой деградации производительности.
Ситуация такая.
Есть шарды с таблицами с игровых аккаунтов, где живёт порядка 50 млн записей. Среди разных полей имеется и поле с балансом игровой валюты. Баланс этот соответственно часто изменяется.

Есть фоновая задача с регулярным построением рейтинга/топа на основе данных о балансе раз в пару часов. Запрос включает в себя конечно же конструкцию "ORDER BY balance DESC". Интервал 2 часа - потому что построение идёт больше часа. Хотелось бы по возможности и чаще.

Проблема: создаётся очень серьёзная нагрузка на дисковую подсистему серверов базы данных, IOLA порядка 90%, доходит почти до 100%.
Возможные и нежелательные решения:
1) Сделать реплику БД на другом сервере и её мучать запросами. Но нужно и под неё делать резервирование. Так же возможно снижение частоты обновления, т.к. железо будет менее мощное.
2) Поставить ещё более производительный массив SSD - дорого
3) Использовать какое-то решение NoSQL конкретно под эту задачу. Добавит избыточности и вероятно неконсистентности. Опять же надо делать резервирование.

Хочется решить проблему более элегантно, на софтовом уровне с имеющейся инфраструктурой.
Может ли помочь использование MATERIALIZED VIEW? Допустим сделать отдельно представление нужных данных для постройки рейтинга и наложить на поле balance индекс, а обновлять допустим раз в полчаса?
  • Вопрос задан
  • 1314 просмотров
Решения вопроса 3
@dimuska139
Backend developer
А есть вариант хранить в redis значения ID пользователей и их баланса? Например, используя Sorted sets. При каждом изменении баланса каждого пользователя обновлять значения. То есть вытащить топ из редиса, по идее, проблемы не составит. И дёргать можно сколь угодно часто, а данные всегда актуальны.
Ответ написан
Melkij
@Melkij
PostgreSQL DBA
explain (analyze,buffers) с включенным track_io_timing покажите.

Варианты:
- вы проводите весь час времени не на сортировке, а где-то ещё о чём решили не писать. Соответственно вопрос неактуален и искать надо там где обронили, а не где светло.
- у вас несоответствующие настройки автовакуума (читай дефолтные)
- время занимает filesort, а от ssd у вас по ошибке стоят read-optimised.
Ответ написан
sarapinit
@sarapinit
Точу водой камень
Вспомогательная таблица с полями "баланс", "user_id". Уникальный индекс по user_id, кластерный индекс(это важно!) по полю "баланс"
Триггер на изменение баланса в основной таблице исправляет значение во вспомогательной.

In cases where you are accessing single rows randomly within a table, the actual order of the data in the table is unimportant. However, if you tend to access some data more than others, and there is an index that groups them together, you will benefit from using CLUSTER. If you are requesting a range of indexed values from a table, or a single indexed value that has multiple rows that match, CLUSTER will help because once the index identifies the table page for the first row that matches, all other rows that match are probably already on the same table page, and so you save disk accesses and speed up the query.


https://www.postgresql.org/docs/9.1/sql-cluster.html

UPD

Я придумал другое решение.
1) разберитесь с размером основной таблицы, скорее всего её раздуло
https://www.youtube.com/watch?v=-GNHIHEHDmQ
2) сделайте индекс по полю Balance с опцией Include Columns и добавьте в него user_id. Тогда при запросе

select balance, user_id from your_table_name order by balance

все данные будут вычитаны из индекса, а это значительно сократит вам нагрузку
Ответ написан
Пригласить эксперта
Ответы на вопрос 2
Sergei_Erjemin
@Sergei_Erjemin
Улыбайся, будь самураем...
Надо посмотреть как часто игроки сморят на рейтинг и попробовать организационно решить. Например отвязать рейтинг от очков в игре и ввести "магический" прогресс-параметр (темп набора очков) . Тогда, по сути, точно ли ты посчитал место в рейтинге или нет -- пользователю проверить уже невозможно. Кроме того, можно предположить, что те кто с 1-го по 1000-ое место, интересуются своим рейтингом более серьезно, чем те что с 100000-го по 500000-е... И тогда простор и раздолье, как все можно реорганизовать. Например, раздели пользователей на группы -- Гуру, Профи, Салаги, Микроорганизмы (причем поделить можно по частоте обращений к рейтингу) -- для каждой группы своя таблица (можно даже в БД на отдельной ноде), а в таблицу пользователей добавь указатель к какой группе он принадлежит... Соответственно очки пиши в обе таблицы (это быстро), но индекс будет только на той таблице, что поменьше... При запросе рейтинга показывать пользователю, место в рейтинге только в его группе, а делать "большую сортировку" и раскидывать по группам -- раз в день во время низкой активности...

Вариантов такой организационной переделки для снижения нагрузки может быть много. Возможно я не прав, но не всегда имеет смысл решать техническую задачу в лоб.
Ответ написан
mayton2019
@mayton2019
Bigdata Engineer
Зачем вам сортировать все 50 миллионов? Задача топа - чтобы взять например top 10.
Сделайте себе временную табличку и по триггеру сливайте в нее по правилу паретто или больше 95%
где баланс больше X. И там будет не 50 миллионов а 100 тыс.

И эта мелкая табличка легко отсортируется и опубликуется.
Ответ написан
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы