Агрегация большого кол-ва записей из БД?

Question

[lloyd67]

В общем суть такая, есть сырые данные от пользователей (дата, какой раздел открывал, какое действие совершал, продолжительность нахождения в разделах и т.п.) которые непрерывно пишутся в таблицу (сейчас это MySQL). Примерно 200 миллионов записей и оно неуклонно растет, к концу года будет уже 400-500 миллионов.

На основе этих данных, необходимо строить различные отчеты по неделям, месяцам, кварталам, годам и т.п. (Sum, AVG, Count, Count Distinct и т.п).

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

Какие тут могут быть решения? Вообще что обычно используют для сбора и аналитики подобных статистических данных? Уместно ли вообще использовать MySQL для хранения постоянного потока данных (думаю что нет)?

Comments

[lega]

сколько весит одна запись?

[lloyd67]

300-500 байт примерно в JSON формате

[Олег Петров]

Если хотите то могу помочь с обработкой с помощью машинного обучения. Люблю, когда много данных!

Answer 1

[Алексей Черемисин]

Посоветую elasicsearch. Закидывайте в него свои данные (в вашем случае подойдет и logstash). Индексы бейте или на месяцы или на недели, организуйте их по годам/месяцам/дням через алиасы. Отчеты можете делать или через kibana, или сами дергать агрегированные данные из своих приложений. Индексы удобно ротировать, архивировать и удалять старые.
И будет щазтие.
Ну и да, если у вас только аналитика этих данных, то мускул здесь совсем не нужен!

Comments

[lloyd67]

Спасибо! Кажется, это как раз то что мне нужно.

[Алексей Черемисин]

lloyd67, Тут немного потренировался на вашем json

PUT mydata
{
    "mappings": {
      "_doc": {
        "properties": {
          "action": {
            "type": "keyword"
          },
          "brand": {
            "type": "keyword"
          },
          "city_id": {
            "type": "long"
          },
          "date_end": {
            "type": "date",
            "format": "yyyy-MM-dd HH:mm:ss"
           },
          "date_start": {
            "type": "date",
            "format": "yyyy-MM-dd HH:mm:ss"
          },
          "device_id": {
            "type": "long"
          },
          "duration": {
            "type": "long"
          },
          "id": {
            "type": "long"
          },
          "param": {
            "type": "keyword"
          },
          "params": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "screen": {
            "type": "long"
          }
        }
      }
    }
  }

POST /mydata/_doc
{
      "id": 14838494,
      "action": "PLAY_CHANNEL",
      "param": "3057",
      "params": "Music Box TV",
      "device_id": 11807,
      "date_end": "2017-01-01 00:26:55",
      "date_start": "2017-01-01 00:26:52",
      "duration": 3527000,
      "city_id": 2096,
      "screen": 40,
      "brand": "JVC"
}

GET mydata/_search
{ "size": 0,
  "query": {
    "bool": {
      "should": [
        {"range": {
          "date_end": {
            "gte": "2017-01-01 00:00:00",
            "lte": "2017-01-02 00:00:00"
          }
        }},
        { "range": {
            "date_start": {
              "gte": "2017-01-01 00:00:00",
              "lte": "2017-01-02 00:00:00"
            }
          }
        }
      ],
      "must": [
        {"term": {
          "action": {
            "value": "PLAY_CHANNEL"
          }
        }}
      ]
    }
  },
  "aggs": {
    "brand_name": {
      "terms": {
        "field": "brand",
        "size": 10
      },
      "aggs": {
        "screen_size": {
          "terms": {
            "field": "screen",
            "size": 10
          },
          "aggs": {
            "stats": {
              "stats": {
                "field": "duration"
              }
            },
            "devices": { "value_count": {
              "field": "device_id"
            }}
          }
        }
      }
    }
  }
}

Результат

{
  "took": 8,
  "timed_out": false,
  "_shards": {
    "total": 5,
    "successful": 5,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": 5,
    "max_score": 0,
    "hits": []
  },
  "aggregations": {
    "brand_name": {
      "doc_count_error_upper_bound": 0,
      "sum_other_doc_count": 0,
      "buckets": [
        {
          "key": "SONY",
          "doc_count": 3,
          "screen_size": {
            "doc_count_error_upper_bound": 0,
            "sum_other_doc_count": 0,
            "buckets": [
              {
                "key": 50,
                "doc_count": 2,
                "devices": {
                  "value": 2
                },
                "stats": {
                  "count": 2,
                  "min": 3527000,
                  "max": 3527000,
                  "avg": 3527000,
                  "sum": 7054000
                }
              },
              {
                "key": 42,
                "doc_count": 1,
                "devices": {
                  "value": 1
                },
                "stats": {
                  "count": 1,
                  "min": 3527000,
                  "max": 3527000,
                  "avg": 3527000,
                  "sum": 3527000
                }
              }
            ]
          }
        },
        {
          "key": "JVC",
          "doc_count": 2,
          "screen_size": {
            "doc_count_error_upper_bound": 0,
            "sum_other_doc_count": 0,
            "buckets": [
              {
                "key": 40,
                "doc_count": 1,
                "devices": {
                  "value": 1
                },
                "stats": {
                  "count": 1,
                  "min": 3527000,
                  "max": 3527000,
                  "avg": 3527000,
                  "sum": 3527000
                }
              },
              {
                "key": 42,
                "doc_count": 1,
                "devices": {
                  "value": 1
                },
                "stats": {
                  "count": 1,
                  "min": 3527000,
                  "max": 3527000,
                  "avg": 3527000,
                  "sum": 3527000
                }
              }
            ]
          }
        }
      ]
    }
  }
}

Answer 2

[asd111]

Берите яндекс clickhouse. Он как раз для отчетов и больших объемов и запросы идут напрямую. С ним можно искать по миллиарду записей за 5-20 секунд(core i5, ssd, 16Gb RAM). Для построения отчетов приемлемое время.
https://clickhouse.yandex/

Answer 3

[sim3x]

Зачем raw-data вообще хранится в мускуле?

Такое нормально скидывается в текстовик и жмется архиватором

А в мускул кладется инфа в 3NF
Тогда и запросы будут идти секунды (на адеквктном железе и настройках)

Comments

[lloyd67]

А можно по-подробней? И каким образом это ускорит запросы?
Вот пример raw-записи из таблицы:

{
	"data":
	[
		{
			"id": 14838494,
			"action": "PLAY_CHANNEL",
			"param": "3057",
			"params": "Music Box TV",
			"device_id": 11807,
			"date_end": "2017-01-01 00:26:55",
			"date_start": "2017-01-01 00:26:52",
			"duration": 3527000,
			"city_id": 2096,
			"screen": 40,
			"brand": "JVC",
		}
	]
}

[sim3x]

lloyd67,
И запрос покажите

[lloyd67]

Например такой:

SELECT 
	`param`, 
	`brand`, 
	`screen`,
	COUNT(`id`) AS `requests`, 
	SUM(`duration`) AS `duration_sum`,
	AVG(`duration`) AS `duration_avg`,
	COUNT(DISTINCT `device_id`) AS `devices` 
FROM `statistic_prolonged` 
WHERE (
  `date_start` BETWEEN '2018-01-01 00:00:00' AND '2018-12-31 23:59:59.999999' OR
  `date_end` BETWEEN '2018-01-01 00:00:00' AND '2018-12-31 23:59:59.999999')
AND `action` = 'OPEN_SECTION' 
GROUP BY `param`, `system_brandname`.`name`, `system_screensize`.`name`

[Алексей Черемисин]

lloyd67, С такими данными (json) вам elestic нужен!

[sim3x]

lloyd67, ну вот и создайте таблицу в которой будет агрегация по суткам

[sim3x]

Алексей Черемисин, он не сильно ускорит, если не затормозит вообще

[Алексей Черемисин]

sim3x, на чем основано это такое утверждение?!
У эластика:
1) совсем другой тип хранения данных
2) предназначен как раз для агрегации
3) в отличии от мускулов, может параллелиться во все стороны
4) у меня работает!

[d'Ivan]

lloyd67,
Есть ли какие-то индексы у таблице statistic_prolonged? Какие?

[lloyd67]

Роман Мирр, есть индексы на action, brand, param, screen и составной на (date_start, date_end)

[lloyd67]

sim3x, это не решает задачу подсчета уникальных пользователей (device_id) за периоды

[sim3x]

Алексей Черемисин, у еластика куча кешей внутри, те второй и последующие запросы будут быстрыми

Если юзкейс такой - то вперед
Тут я не вижу такого кейса

Answer 4

[lega]

Я уже на тостере пару раз описывал одно из решений (с расчетом на прирост до 2000 млн записей в день), в кратце:
* таблицы на свалку, нужно паковать чанками (например чанк - 1 час/день данных в разрезе раздела) с индексами в доль разрезов, можно использовать nosql (mongodb с шардингом, хотя вам и одного сервера наверно хватит)
* чанки паковать (экономия до 95% места)
* далее после завершения периодов запускаются задачи которые наполняют "кеш" - строят отчеты во всех разрезах + промежуточные результаты, что-бы пользователю выдавать результат моментально когда он кликает по интерфейсу.

я делал решение на питоне, там где расчет занимал длительное время - делал с++ вставки, в результате расчет выполнялся в ~ х70 раз быстрее, и питон прокачивал более 10млн записей в сек. в один поток с учетом выкачивания из БД

Answer 5

[Supme]

Так этож метрики. Influxdb, Prometeus, ClickHouse и тп

Answer 6

[santaatnas]

Смотрите в сторону Hadoop и/ или Clickhouse и будет вам счастье.

Comments

[d'Ivan]

Clickhouse шустрее Hadoop