Есть например аппликейшн для управления данными об учениках в школах. Например Mysql база, в которой хранится иерархия: Schools, Classes, Students. Ожидаемые объёмы данных скажем 1000 школ x 1000 классов на школу x 100 студентов на класс. Т.е. всего 1000 школ, 1млн классов, 100млн студентов. Редактировать-добавлять-удалять можно как отдельные записи через CRUD UI, так и балком — целый класс, целую школу, прям сразу вообще всё — это например через аплоад CSV файлов. Когда удаляешь школу, удаляются все её классы и все ученики из этих классов. Когда удаляешь класс, удаляются все ученики.
В аппликейшне при этом есть несколько страничек, на которых нужно показывать актуальную статистику:
1. Сколько вообще всего школ, классов и учеников
2. Глядя на школу — сколько в ней классов и учеников
3. Глядя на класс — сколько в нём учеников
Данные нужно показывать моментально. К этим страничкам обращаются параллельно по 100 запросов в секунду.
Как бы вы стали реализовывать функциональность подсчёта? Стали бы использовать триггеры в БД или стали бы делать изменение счётчиков в коде? Если статистике разрешается отставать от данных скажем на 1 час — как это повлияло бы на решение?
Здравствуйте, rosencrantz, Вы писали:
R>Есть например аппликейшн для управления данными об учениках в школах. Например Mysql база, в которой хранится иерархия: Schools, Classes, Students. Ожидаемые объёмы данных скажем 1000 школ x 1000 классов на школу x 100 студентов на класс. Т.е. всего 1000 школ, 1млн классов, 100млн студентов.
R>Данные нужно показывать моментально. К этим страничкам обращаются параллельно по 100 запросов в секунду.
R>Как бы вы стали реализовывать функциональность подсчёта? Стали бы использовать триггеры в БД или стали бы делать изменение счётчиков в коде? Если статистике разрешается отставать от данных скажем на 1 час — как это повлияло бы на решение?
При заявленных цифрах можно использовать каноничные примеры из учебника — пиписочные масштабы.
Можно даже в памяти хранить, вообще без СУБД.
_____________________
С уважением,
Stanislav V. Zudin
Здравствуйте, rosencrantz, Вы писали:
R>Как бы вы стали реализовывать функциональность подсчёта? Стали бы использовать триггеры в БД или стали бы делать изменение счётчиков в коде? Если статистике разрешается отставать от данных скажем на 1 час — как это повлияло бы на решение?
Соглашусь с предыдущим оратором, обычная реляционная СУБД потянет такую нагрузку в реалтайм. Если вдруг случится невероятное и не потянет, то рядом с реляционной базой с мастер-данными нужно развернуть аналитическую базу и пополнять её асинхронно. Причем лучше не связываться с триггерами и закодить на уровне приложения.
Здравствуйте, rosencrantz, Вы писали:
R>Есть например аппликейшн для управления данными об учениках в школах. Например Mysql база, в которой хранится иерархия: Schools, Classes, Students. Ожидаемые объёмы данных скажем 1000 школ x 1000 классов на школу x 100 студентов на класс. Т.е. всего 1000 школ, 1млн классов, 100млн студентов. Редактировать-добавлять-удалять можно как отдельные записи через CRUD UI, так и балком — целый класс, целую школу, прям сразу вообще всё — это например через аплоад CSV файлов. Когда удаляешь школу, удаляются все её классы и все ученики из этих классов. Когда удаляешь класс, удаляются все ученики.
R>В аппликейшне при этом есть несколько страничек, на которых нужно показывать актуальную статистику:
R>1. Сколько вообще всего школ, классов и учеников R>2. Глядя на школу — сколько в ней классов и учеников R>3. Глядя на класс — сколько в нём учеников
R>Данные нужно показывать моментально. К этим страничкам обращаются параллельно по 100 запросов в секунду.
R>Как бы вы стали реализовывать функциональность подсчёта? Стали бы использовать триггеры в БД или стали бы делать изменение счётчиков в коде? Если статистике разрешается отставать от данных скажем на 1 час — как это повлияло бы на решение?
1) Начинаем с простого: делаем БД в 3НФ и пишем нормальные запросы — с нужными проекциями и корректными предикатами.
2) Далее делаем в базе индексы, оптимизирующие эти запросы. Я думаю вам уже на этом этапе хватит производительности базы.
3) Если же запросов много и мы упираемся в базу, то делаем кэширование в приложении. Сохраняем результаты запроса в кэше по ключу. При записи сбрасываем элемент по этому ключу. Учитывая простую структуру базы управление кэшом не станет проблемой. Для начала достаточно кэша в памяти приложения.
4) Если после этого быстродействие начинает упираться в приложение — растет очередь запросов и падает среднее время ответа, то используем кэширование на клиенте. Для этого вместе со значением по ключу вы сохраняете дату последнего обновления. Отдаете страницу с Last-Modified заголовком, равным дате последнего обновления. При получении запроса с if-modified-since сравниваете дату со значением на сервере и отдаете 304 если совпадают.
К чему стоит стремиться если вы хотите делать нагруженное приложение:
1) 90% запросов на чтение должно отдаваться из кэша клиента (с помощью 304)
2) Из оставшихся 90% должно отдаваться из кэша сервера (статика, inmemory-кэши итд)
3) Оставшийся 1% запросов, реально улетевших в базу должен быть покрыт индексами
При правильном проектировании это все можно сделать без показа устаревших данных.
Здравствуйте, scf, Вы писали:
scf>Соглашусь с предыдущим оратором, обычная реляционная СУБД потянет такую нагрузку в реалтайм. Если вдруг случится невероятное и не потянет, то рядом с реляционной базой с мастер-данными нужно развернуть аналитическую базу и пополнять её асинхронно. Причем лучше не связываться с триггерами и закодить на уровне приложения.
На моей тестовой базе запрос select count(*) from Users отрабатывает за 7 минут при том, что там 50млн строчек, а не 100 (это AWS RDS — t3.micro). Или вы имеете в виду, что на сервере за 100 баксов в день это будет работать на много порядков быстрее? Каким образом вы принимаете решение о необходимой производительности сервера?
Здравствуйте, rosencrantz, Вы писали:
R>Имеете ли вы в виду, что select count(*) from Students по таблице 100млн строчек 100 раз в секунду — это рабочий вариант?
Нет, это fullscan, но это же единственный кейс, когда select count будет медленным
Такие вещи а) легко кешировать б) точное значение никому не интересно — какая разница, сколько на самом деле студентов в системе 1234567 или 1234568?
Здравствуйте, rosencrantz, Вы писали:
R>На моей тестовой базе запрос select count(*) from Users отрабатывает за 7 минут при том, что там 50млн строчек, а не 100 (это AWS RDS — t3.micro). Или вы имеете в виду, что на сервере за 100 баксов в день это будет работать на много порядков быстрее? Каким образом вы принимаете решение о необходимой производительности сервера?
Ну не 100 баксов в день, но быстро и бесплатно — так не бывает. На хосте с постгресом в идеале должно быть достаточно памяти, чтобы дисковое хранилище БД полностью поместилось в кеше операционной системы.
SSD тоже не повредит, если нужна вменяемая скорость вставки.
Например, разверните ту же базу с теми же данными на своей машине и сравните время запросов на прогретой базе.
Здравствуйте, scf, Вы писали:
scf>Здравствуйте, rosencrantz, Вы писали:
R>>Имеете ли вы в виду, что select count(*) from Students по таблице 100млн строчек 100 раз в секунду — это рабочий вариант?
scf>Нет, это fullscan, но это же единственный кейс, когда select count будет медленным scf>Такие вещи а) легко кешировать б) точное значение никому не интересно — какая разница, сколько на самом деле студентов в системе 1234567 или 1234568?
Ну в исходном сообщении: "нужно показывать актуальную статистику".
Вы говорите о кешировании — мне интересно откуда всё-таки вы предлагаете результат для кеширования брать? Типа удалили 1 юзера, сделали этот зверский select count, запомнили. Второго удалили — то же самое? Или раз в час select count делать (и тогда статистика будет отставать на 1 час конечно).
Здравствуйте, scf, Вы писали:
scf>Здравствуйте, rosencrantz, Вы писали:
R>>На моей тестовой базе запрос select count(*) from Users отрабатывает за 7 минут при том, что там 50млн строчек, а не 100 (это AWS RDS — t3.micro). Или вы имеете в виду, что на сервере за 100 баксов в день это будет работать на много порядков быстрее? Каким образом вы принимаете решение о необходимой производительности сервера?
scf>Ну не 100 баксов в день, но быстро и бесплатно — так не бывает. На хосте с постгресом в идеале должно быть достаточно памяти, чтобы дисковое хранилище БД полностью поместилось в кеше операционной системы. scf>SSD тоже не повредит, если нужна вменяемая скорость вставки.
scf>Например, разверните ту же базу с теми же данными на своей машине и сравните время запросов на прогретой базе.
База 150 Гб весит, на моём девелоперском компе столько нет
Утверждаете ли вы, что мне нужен сервер со 150 Гб памяти? У AWS RDS m5.12xlarge — 192 Гб за $4 в час, что как раз даёт $100 в день.
Здравствуйте, rosencrantz, Вы писали:
R>Вы говорите о кешировании — мне интересно откуда всё-таки вы предлагаете результат для кеширования брать? Типа удалили 1 юзера, сделали этот зверский select count, запомнили. Второго удалили — то же самое? Или раз в час select count делать (и тогда статистика будет отставать на 1 час конечно).
Вообще-то это тяжелый аналитический запрос, который собрались гонять на OLTP базе. Примитивное кеширование здесь плохая идея, т.к. даже "раз в час" такой селект будет просаживать персентили остальных запросов.
Что сделал бы я:
— либо убедил, что это полностью бессмысленная метрика, либо что нужна полноценная аналитика (а-ля отчеты по среднему кол-ву школьников в школе)
— если это природное любопытство руководства, сделал бы им отдельную кнопку "посчитать кол-во школьников всего"
— если им нужен крутящийся счетик на главной, хранил бы общий count в базе и обновлял его при модификации базы. Возможно, через очередь, чтобы разгрузить базу при массовой вставке.
— если все предыдущие варианты не подходят (включая отдельную аналитическую базу, где это быстрый запрос), то завел бы таблицу "кол-во школьников в каждой школе" и обновлял её после каждой вставки, по каждой измененной школе.
Здравствуйте, rosencrantz, Вы писали:
R>Утверждаете ли вы, что мне нужен сервер со 150 Гб памяти? У AWS RDS m5.12xlarge — 192 Гб за $4 в час, что как раз даёт $100 в день.
Нет, я утверждаю, что 150 Гб памяти — это оптимальный по быстродействию вариант, если нет "холодных" данных. Реальные требования к железу будут видны после нагрузочного тестирования.
Здравствуйте, scf, Вы писали:
scf>Здравствуйте, rosencrantz, Вы писали:
R>>Утверждаете ли вы, что мне нужен сервер со 150 Гб памяти? У AWS RDS m5.12xlarge — 192 Гб за $4 в час, что как раз даёт $100 в день.
scf>Нет, я утверждаю, что 150 Гб памяти — это оптимальный по быстродействию вариант, если нет "холодных" данных. Реальные требования к железу будут видны после нагрузочного тестирования.
Вот сделали select count по таблице Users. 7 минут он занимает. Это проблема дизайна (сама идея использования такого запроса) или это проблема сервера (тормозит)?
Здравствуйте, scf, Вы писали:
scf>Здравствуйте, rosencrantz, Вы писали:
R>>Вы говорите о кешировании — мне интересно откуда всё-таки вы предлагаете результат для кеширования брать? Типа удалили 1 юзера, сделали этот зверский select count, запомнили. Второго удалили — то же самое? Или раз в час select count делать (и тогда статистика будет отставать на 1 час конечно).
scf>Вообще-то это тяжелый аналитический запрос, который собрались гонять на OLTP базе.
Согласен. Я поэтому и удивился, когда мне указали на "каноничные примеры из учебника" и "пиписочные масштабы"
scf>- если все предыдущие варианты не подходят (включая отдельную аналитическую базу, где это быстрый запрос), то завел бы таблицу "кол-во школьников в каждой школе" и обновлял её после каждой вставки, по каждой измененной школе.
Я бы хотел этот вариант рассмотреть. Стали бы вы делать update SchoolStats set studentCount = studentCount + 1 where schoolId = 123 в коде аппликейшна или через триггер? Меня интересует конкретно как так сделать, чтобы эти счётчики в какой-то момент не разъехались с данными из-за того, что в каком-то месте забыли этот +1 дёрнуть.
Здравствуйте, rosencrantz, Вы писали:
R>Есть например аппликейшн для управления данными об учениках в школах. Например Mysql база, в которой хранится иерархия: Schools, Classes, Students. Ожидаемые объёмы данных скажем 1000 школ x 1000 классов на школу x 100 студентов на класс. Т.е. всего 1000 школ, 1млн классов, 100млн студентов. Редактировать-добавлять-удалять можно как отдельные записи через CRUD UI, так и балком — целый класс, целую школу, прям сразу вообще всё — это например через аплоад CSV файлов. Когда удаляешь школу, удаляются все её классы и все ученики из этих классов. Когда удаляешь класс, удаляются все ученики.
R>Данные нужно показывать моментально. К этим страничкам обращаются параллельно по 100 запросов в секунду.
R>Как бы вы стали реализовывать функциональность подсчёта? Стали бы использовать триггеры в БД или стали бы делать изменение счётчиков в коде? Если статистике разрешается отставать от данных скажем на 1 час — как это повлияло бы на решение?
При таком объеме данных можно даже пофамильные списки учеников полностью уместить в 2-3 Гб оперативной памяти 32 битного процесса.
Но для задачи просмотра в WEБ приложение даже этого не надо, если циклически обновлять статические странички по школам и классам.
Здравствуйте, rosencrantz, Вы писали: R>Имеете ли вы в виду, что select count(*) from Students по таблице 100млн строчек 100 раз в секунду — это рабочий вариант?
Нет конечно — надо считать по индексу.
Уйдемте отсюда, Румата! У вас слишком богатые погреба.
Здравствуйте, rosencrantz, Вы писали: R>На моей тестовой базе запрос select count(*) from Users отрабатывает за 7 минут при том, что там 50млн строчек, а не 100 (это AWS RDS — t3.micro).
А индекс у вас там есть?
З.Ы. Mysql — недоСУБД. Пока ещё не всё прополимерено, возьмите хотя бы postgres.
Уйдемте отсюда, Румата! У вас слишком богатые погреба.
G>1) Начинаем с простого: делаем БД в 3НФ и пишем нормальные запросы — с нужными проекциями и корректными предикатами. G>2) Далее делаем в базе индексы, оптимизирующие эти запросы. Я думаю вам уже на этом этапе хватит производительности базы. G>3) Если же запросов много и мы упираемся в базу, то делаем кэширование в приложении. Сохраняем результаты запроса в кэше по ключу. При записи сбрасываем элемент по этому ключу. Учитывая простую структуру базы управление кэшом не станет проблемой. Для начала достаточно кэша в памяти приложения. G>4) Если после этого быстродействие начинает упираться в приложение — растет очередь запросов и падает среднее время ответа, то используем кэширование на клиенте. Для этого вместе со значением по ключу вы сохраняете дату последнего обновления. Отдаете страницу с Last-Modified заголовком, равным дате последнего обновления. При получении запроса с if-modified-since сравниваете дату со значением на сервере и отдаете 304 если совпадают.
Прекрасно изложено. Я бы ещё попробовал сделать materialized view между 2 и 3. Особенно если частота чтений << частоты записей.
Просто иметь иерархию счётчиков типа count класса, count параллели, count школы, count района добавляет O(1) ко времени вставки/удаления, зато позволяет очень многие агрегатные запросы делать за O(1)
G>К чему стоит стремиться если вы хотите делать нагруженное приложение: G>1) 90% запросов на чтение должно отдаваться из кэша клиента (с помощью 304) G>2) Из оставшихся 90% должно отдаваться из кэша сервера (статика, inmemory-кэши итд) G>3) Оставшийся 1% запросов, реально улетевших в базу должен быть покрыт индексами
G>При правильном проектировании это все можно сделать без показа устаревших данных.
Уйдемте отсюда, Румата! У вас слишком богатые погреба.
Здравствуйте, rosencrantz, Вы писали:
R>Здравствуйте, scf, Вы писали:
scf>>Соглашусь с предыдущим оратором, обычная реляционная СУБД потянет такую нагрузку в реалтайм. Если вдруг случится невероятное и не потянет, то рядом с реляционной базой с мастер-данными нужно развернуть аналитическую базу и пополнять её асинхронно. Причем лучше не связываться с триггерами и закодить на уровне приложения.
R>На моей тестовой базе запрос select count(*) from Users отрабатывает за 7 минут при том, что там 50млн строчек, а не 100 (это AWS RDS — t3.micro). Или вы имеете в виду, что на сервере за 100 баксов в день это будет работать на много порядков быстрее? Каким образом вы принимаете решение о необходимой производительности сервера?
Для InnoDB движка надо count(PK) писать.
Соглашусь с коллегами, что лучше взять postgres. У MySQL слишком много особенностей, помогающих писать тормозные запросы.
