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

alex-zzx · 06.11.2017 12:00:52

Доброго дня
Прошу всех специалистов в теме 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
Можно здесь что-то подсказать

Заранее спасибо всем кто ответит.

klow · 06.11.2017 12:21:45

Игровой сервер выдает порциями (объем?) или по одной?

alex-zzx · 06.11.2017 12:29:19

klow написал:
Игровой сервер выдает порциями (объем?) или по одной?

ММММ
Не совсем понятен вопрос.
Это клиенты конектятся к серверу и ложат на него данные порциями но очень часто.

Отредактированно alex-zzx (06.11.2017 12:29:32)

klow · 06.11.2017 12:35:15

Вопрос, как правильно сохранять эти данные. Если порции достаточно большие, то рекомендую через "LOAD DATA". Это может существенно ускорить загрузку по сравнению с обычным инсерт.

alex-zzx · 06.11.2017 12:46:15

klow написал:
Вопрос, как правильно сохранять эти данные. Если порции достаточно большие, то рекомендую через "LOAD DATA". Это может существенно ускорить загрузку по сравнению с обычным инсерт.

Знаю про "LOAD DATA" - но в данном случае скорее всего это не то. "LOAD DATA" - грузит данные из файлов в БД.
В задаче ситуация другая:
Клиент с мобильного устройства подключился к серверу - оставил на нём данные и ушёл. И таких клиенты могут делать 230 инсертов в базу в секунду.

klow · 06.11.2017 12:56:37

Эта задача может решатся как и инсерт так и Load (предварительно сохранив в файл), но время выполнения задачи может быть разное (во много раз). Например, клиент скинул 200 тыс. записей. Если будешь инсертом загонять в базу (даже используя групповую вставку), это, думаю, будет существенно медленнее чем использовать Load. Но если будет только несколько записей, то Load (без накопления), скорее всего, будет в проигрыше. Думаю, время и, соответственно, нагрузка на БД при таком количестве данных имеет важное значение.

alex-zzx · 06.11.2017 14:49:56

klow написал:
Эта задача может решатся как и инсерт так и Load (предварительно сохранив в файл), но время выполнения задачи может быть разное (во много раз). Например, клиент скинул 200 тыс. записей. Если будешь инсертом загонять в базу (даже используя групповую вставку), это, думаю, будет существенно медленнее чем использовать Load. Но если будет только несколько записей, то Load (без накопления), скорее всего, будет в проигрыше. Думаю, время и, соответственно, нагрузка на БД при таком количестве данных имеет важное значение.

Спасибо за ответы.
Но вероятно мы не совсем понимаем друг друга.
Это не 1 клиент скидывает записи на сервер.
Множество разных клиентов пишут данные на сервер одновременно. Исходя из нагрузки в 20млн в сутки - множество разных клиентов может делать по 230 записей в секунду.
Если все эти клиенты будут писать в файл одновременно - то файл будет блочится каждым клиентом под себя а остальные будут вынуждены ждать пока файл не разблочится - в результате будут огромные задержки - разве нет?

С другой стороны, если можно напрямую писать в файл(без over задержек и подвисаний) - то вообще можно уйти от Mysql - и сразу писать данные в csv файлы по дням. А потом выдавать список дней по которым можно скачать статистику.

Но в задаче стоит условие - сохранять данные именно в БД чтобы потом их скачать.

По изначально предложенной структуре БД, идеии разделения на партициям - можете что-то подсказать?

klow · 06.11.2017 15:20:08

Похоже не совсем понимаем друг друга. Давайте будем уточнять.
1.Я не говорил про один файл. Файлы (временные) должны быть разные, один файл - на одну порцию данных (сессию). Подключился клиент, сохранили его данные в файл. Загрузили (Load) файл. Удалили файл. Ответили клиенту, что все нормально.
Если порция данных достаточно большая, то выигрыш (нагрузка, время) может быть существенный.
2. Сохранять данные всех клиентов в один файл (или один файл на клиента), а потом его грузить в БД один раз в день или периодически, например, 1 раз в час? Думаю, это неправильно. Мне это решение не очень нравиться.
3. Замечаний, предложений по структуре БД нет. Вроде, все логично.

alex-zzx · 06.11.2017 23:32:27

klow написал:
1.Я не говорил про один файл. Файлы (временные) должны быть разные, один файл - на одну порцию данных (сессию). Подключился клиент, сохранили его данные в файл. Загрузили (Load) файл. Удалили файл. Ответили клиенту, что все нормально.
Если порция данных достаточно большая, то выигрыш (нагрузка, время) может быть существенный.

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

klow написал:
2. Сохранять данные всех клиентов в один файл (или один файл на клиента), а потом его грузить в БД один раз в день или периодически, например, 1 раз в час? Думаю, это неправильно. Мне это решение не очень нравиться.

Здесь скорее имелась ввиду идея хранить данные по всем событиям за день в 1 csv файле и потом просто скачивать его.

klow написал:
3. Замечаний, предложений по структуре БД нет. Вроде, все логично.

Спасибо

klow · 06.11.2017 23:52:25

1. Все зависит от количества записей в одной порции данных. Проверь реализовав и то и то решение и увидишь разницу. Я был удивлен насколько Load быстрее Insert. В моем случае это было в сотни раз, но не факт, что в данном случае это будет быстрее.
2. Писать в один файл с разных источников в одно и тоже время - могут быть проблемы с реализацией. Кроме того, не факт, что этот файл после успешно загрузиться, а клиента уже проинформировал, что все ок.

alex-zzx · 07.11.2017 00:04:25

klow написал:
1. Все зависит от количества записей в одной порции данных. Проверь реализовав и то и то решение и увидишь разницу. Я был удивлен насколько Load быстрее Insert. В моем случае это было в сотни раз, но не факт, что в данном случае это будет быстрее.
2. Писать в один файл с разных источников в одно и тоже время - могут быть проблемы с реализацией. Кроме того, не факт, что этот файл после успешно загрузиться, а клиента уже проинформировал, что все ок.

1. Представляю разницу между Load и Insert. Разница как между восстановлением БД через phpMyAdmin и через консоль командой source
2. Тоже так думаю

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

#1 06.11.2017 12:00:52

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

#2 06.11.2017 12:21:45

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

#3 06.11.2017 12:29:19

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

klow написал:

#4 06.11.2017 12:35:15

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

#5 06.11.2017 12:46:15

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

klow написал:

#6 06.11.2017 12:56:37

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

#7 06.11.2017 14:49:56

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

klow написал:

#8 06.11.2017 15:20:08

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

#9 06.11.2017 23:32:27

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

klow написал:

klow написал:

klow написал:

#10 06.11.2017 23:52:25

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

#11 07.11.2017 00:04:25

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

klow написал:

Board footer