@alex-zzx
разработчик

Как сделать сервис статистики для 20млн записей в сутки?

Доброго дня
Прошу всех специалистов в теме highload помочь со следующим заданием:

1) Задача: на базе PHP+MySQL нужно реализовать сервис приёма статистики игровых данных.
Структура:
- ID игрока
- игровое время события
- ID устройства ( строка , 64 символа )
- платформа устройства (iPad, iPhone)
- набор произвольных данных (например, устройство отсылает событие с параметрами Event=Start, Money=15444 и т.д.)
2) Требования:
- сервис должен выдерживать приём 20 млн обращений в сутки.
- необходимо предусмотреть возможность выборки и удаления данных за предыдущий день ( например, 10 -го числа забираются и удаляются данные за 9 -е)
- сервис должен отвечать клиенту, что приём прошёл удачно либо неудачно

Свои идеи:
1. Структура БД
Т.к. данные будет писаться часто и много, а удаляться и читаться редко то вижу два варианта структуры БД:

1.1 Каждый день кроном (cron) создавать таблицу на следующий день - куда будут складываться данные.
Тогда удаление и чтение данных не будет оказывать влияния на вновь записываемые данные.
CREATE TABLE IF NOT EXISTS `day_data` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `user_id` int(10) NOT NULL,
  `datetime` int(10) NOT NULL,
  `device_id` varchar(64) NOT NULL,
  `platform` enum('ipad','iphone') NOT NULL,
  `data` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0;


1.2 Таблица создаётся одна, но в ней делаются партиции(PARTITION) по дню месяца(1-31)

CREATE TABLE IF NOT EXISTS `day_data` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `day_of_month` tinyint(4) NOT NULL,
  `user_id` int(10) NOT NULL,
  `datetime` int(10) NOT NULL,
  `device_id` varchar(64) NOT NULL,
  `platform` enum('ipad','iphone') NOT NULL,
  `data` text NOT NULL,
  PRIMARY KEY (`id`,`day_of_month`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0
PARTITION BY LIST (day_of_month)
(PARTITION p01 VALUES IN (1) ENGINE = InnoDB,
 PARTITION p02 VALUES IN (2) ENGINE = InnoDB,
 PARTITION p03 VALUES IN (3) ENGINE = InnoDB,
 PARTITION p04 VALUES IN (4) ENGINE = InnoDB,
 PARTITION p05 VALUES IN (5) ENGINE = InnoDB,
 PARTITION p06 VALUES IN (6) ENGINE = InnoDB,
 PARTITION p07 VALUES IN (7) ENGINE = InnoDB,
 PARTITION p08 VALUES IN (8) ENGINE = InnoDB,
 PARTITION p09 VALUES IN (9) ENGINE = InnoDB,
 PARTITION p10 VALUES IN (10) ENGINE = InnoDB,
 PARTITION p11 VALUES IN (11) ENGINE = InnoDB,
 PARTITION p12 VALUES IN (12) ENGINE = InnoDB,
 PARTITION p13 VALUES IN (13) ENGINE = InnoDB,
 PARTITION p14 VALUES IN (14) ENGINE = InnoDB,
 PARTITION p15 VALUES IN (15) ENGINE = InnoDB,
 PARTITION p16 VALUES IN (16) ENGINE = InnoDB,
 PARTITION p17 VALUES IN (17) ENGINE = InnoDB,
 PARTITION p18 VALUES IN (18) ENGINE = InnoDB,
 PARTITION p19 VALUES IN (19) ENGINE = InnoDB,
 PARTITION p20 VALUES IN (20) ENGINE = InnoDB,
 PARTITION p21 VALUES IN (21) ENGINE = InnoDB,
 PARTITION p22 VALUES IN (22) ENGINE = InnoDB,
 PARTITION p23 VALUES IN (23) ENGINE = InnoDB,
 PARTITION p24 VALUES IN (24) ENGINE = InnoDB,
 PARTITION p25 VALUES IN (25) ENGINE = InnoDB,
 PARTITION p26 VALUES IN (26) ENGINE = InnoDB,
 PARTITION p27 VALUES IN (27) ENGINE = InnoDB,
 PARTITION p28 VALUES IN (28) ENGINE = InnoDB,
 PARTITION p29 VALUES IN (29) ENGINE = InnoDB,
 PARTITION p30 VALUES IN (30) ENGINE = InnoDB,
 PARTITION p31 VALUES IN (31) ENGINE = InnoDB);


2. Удаление
С удалением проблем нет т.к. в первом варианте DROP таблицы происходит достаточно быстро
Во втором варианте делаем ALTER TABLE `day_data` TRUNCATE PARTITION p11 - это тоже быстро т.к. если очень грубо, то Mysql удаляет старый файл данных и создает новый. А эта операция выполняется значительно быстрее построчного удаления.

3. Чтение
Для того чтобы избежать потери данных и не вешать сервер на скачку 20млн записей на лету - предварительно сохраняем эти данные в csv файл на сервер и для скачки - даём ссылку на файл на сервере.
Сохранение производим в цикле по 10000 записей - устанавливая флаг в БД для данных уже сохранённх в файл.

4. Отдача клиенту
Ответ присылае в виде JSON
В случае удачи присылаем ok
В случае неудачи:
если данные неполные - ошибку с информацией о неполных данных
если есть ишибка при вставке в БД - информацию об ошибке вставки в БД и номер ошибки

5. Настройка БД
Для увеличения производительности - вероятно нужно дополнительно настроить БД затронув параметры:
innodb_buffer_pool_size
innodb_log_file_size
innodb_log_buffer_size
innodb_file_per_table
innodb_flush_method
innodb_flush_log_at_trx_commit
Можно здесь что-то подсказать

Заранее спасибо всем кто ответит.
  • Вопрос задан
  • 651 просмотр
Пригласить эксперта
Ответы на вопрос 5
@RidgeA
1. Почему MySQL?, возможно MongoDB или Cassandra подойдут лучше.
2. Какого рода статистика нужна по этим данным?
3. ИМХО лучше не класть в базу по одной записи, а аккумулировать записи и писать в базу (вне зависимости от базы) пачками. Для аккумуляции подойдет какой-то брокер очередей (например RabbitMQ). К. т. очереди позволят сгладить пики нагрузки - в базу будет писаться более-менее равномерно, в пики - просто будет увеличиваться очередь.
Ответ написан
Sanasol
@Sanasol
нельзя просто так взять и загуглить ошибку
20млн записей в сутки

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

Без подготовки и фейлов ака опыта ничего не выйдёт, сначала надо смотреть сколько вообще нагрузки будет.
А нагрузка зависит от того что и как там будет работать.
А здесь никто будущее не предсказывает.

К тому времени когда будет 20 лямов реальных событий в день, у вас уже будет достаточно скилла чтобы это обработать.
Это 230 инсертов в базу в секунду, если писать в одну таблицу. И это для mysql-сервера из коробки овердохуя.
Неговоря уже про нагрузку на сам вебсервер и сервер в целом.
Т.е. это не работа для одного дедика 100%.

В общем всё это опытным путём в ваших конкретных обстоятельствах узнаётся.
Ответ написан
begemot_sun
@begemot_sun
Программист в душе.
Использовать ClickHouse :)
Ответ написан
@lega
Самый простой и быстрый вариант - просто сбрасывать все реквесты в файл (можно прямиком из nginx log, но с конвертацией перед отправкой), потом этот файл и отдавать, опять же через nginx.
Будет держать 1000млн записей в сутки и более (зависит от железа).
Ответ написан
proudmore
@proudmore
Я бы на вашем месте посмотрел в сторону Яндекс ClickHouse. Почитайте, и если найдете удобным, попробуйте убедить вышестоящее начальство в обоснованности использования этой СУБД.
Ответ написан
Ваш ответ на вопрос

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

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