Здравствуйте, 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% запросов, реально улетевших в базу должен быть покрыт индексами
При правильном проектировании это все можно сделать без показа устаревших данных.
Здравствуйте, rosencrantz, Вы писали:
R>Здравствуйте, Vladek, Вы писали:
V>>Добавил бы столбцы SchoolCount, ClassCount, StudentCount в соответствующие таблицы. Обновлял бы их при изменении данных. И всё.
R>Триггером или кодом приложения?
Лучше триггером. Это не часть бизес-логики, это исправление косяков БД, которая не умеет в материализованные представления и нормальные индексы.
Если поменять базу на другую, то триггеры можно будет выкинуть.
Поэтому не стоит тащить такую логику в программу.
Здравствуйте, rosencrantz, Вы писали:
R>Есть например аппликейшн для управления данными об учениках в школах. Например Mysql база, в которой хранится иерархия: Schools, Classes, Students. Ожидаемые объёмы данных скажем 1000 школ x 1000 классов на школу x 100 студентов на класс. Т.е. всего 1000 школ, 1млн классов, 100млн студентов.
R>Данные нужно показывать моментально. К этим страничкам обращаются параллельно по 100 запросов в секунду.
R>Как бы вы стали реализовывать функциональность подсчёта? Стали бы использовать триггеры в БД или стали бы делать изменение счётчиков в коде? Если статистике разрешается отставать от данных скажем на 1 час — как это повлияло бы на решение?
При заявленных цифрах можно использовать каноничные примеры из учебника — пиписочные масштабы.
Можно даже в памяти хранить, вообще без СУБД.
_____________________
С уважением,
Stanislav V. Zudin
Здравствуйте, Sinclair, Вы писали:
S>Заодно это положительно влияет на производительность. Из-за того, что код исполняется ближе к данным.
А что будет с производительностью во время batch-update?
Например, когда обновятся ВСЕ ученики школы. Т.е. все старые будут удалены, а все новые добавлены.
Допустим в школе 2000 учеников. Триггер выполнит 4000 обновлений агрегированной таблицы?
Плюс такая операция заблокирует запись других писателей в эту таблицу.
Выполнить COUNT было бы намного быстрее для этой школы. И блокировки удалось бы избежать.
В общем, ответ зависит от частоты и типа обновлений исходной таблицы. И триггер часто не лучшее решение.
Здравствуйте, Sharov, Вы писали:
S>Здравствуйте, Vladek, Вы писали:
S>Я по-прежнему не понимаю зачем моя программа должна брать на себя задачи СУБД, если вторая явно с этим лучше справится, а S>я потрачу время и напложу багов ? Если уж связались с СУБД, то надо использовать возможности инструмента по-максимуму, а не S>переписывать с нуля отлаженный функционал СУБД у себя в программе.
Тогда получается приложение СУБД, а не программа. Это уже философский вопрос. Вообще вера в то, что внутри СУБД уже есть программа заказчика, реализованная и отлаженная, надо её только оттуда извлечь, приводит к нулевой интеллектуальной ценности написанного кода — это будет простой интеграционный код тасования байтов от устройств ввода в базу данных и обратно. Код, который был написан уже тысячу раз, иногда авторы даже тащат целые куски из предыдущих проектов в новый проект.
Код должен моделировать бизнес-процессы и автоматизировать необходимые части, то есть отражать суть того, чем занят бизнес — если считать количество школ и студентов важно, это должно быть отражено в коде. Если правила подсчёта изменятся, код тоже изменится.
Если код примитивен, то у бизнеса вся автоматизация будет сводиться к накоплению данных в базе данных. Это значит, при развитии бизнеса и смене обстоятельств (это может быть что угодно, от смены облачного провайдера из-за дороговизны, до необходимости поддерживать новые платформы), софт, скорее всего, устареет и не будет адаптирован — бизнес сможет использовать только старую базу данных, а код придётся писать заново.
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>Я бы хотел этот вариант рассмотреть. Стали бы вы делать update SchoolStats set studentCount = studentCount + 1 where schoolId = 123 в коде аппликейшна или через триггер?
Я бы сделал через триггер, но вот где он есть кроме Oralc'а и MsSql?
R>Меня интересует конкретно как так сделать, чтобы эти счётчики в какой-то момент не разъехались с данными из-за того, что в каком-то месте забыли этот +1 дёрнуть.
Для этого +1 надо дергать ровно 1 месте, желательно в триггере, чтобы сама бд все это делала.
Здравствуйте, Sinclair, Вы писали:
S>Здравствуйте, Буравчик, Вы писали:
Б>>В общем, ответ зависит от частоты и типа обновлений исходной таблицы. И триггер часто не лучшее решение. S>Триггер — лучшее решение по сравнению с application-side триггером, когда мы уже решили использовать предагрегированные таблицы, но строим их руками в коде приложения, а не внутри RDBMS.
У меня по факту код аппликейшна обновляет счётчики в БД. Когда кто-то редактирует руками, происходит update ... count = count + 1 (ну или -1 при удалении), а при балк инсертах то же самое, только сразу count=count+100500.
Переписал на триггерах из интереса. Триггер на insert делает +1, и на delete делает -1. Здорово выкинулось много кода из аппликейшна, но балк инсерты стали в 1.5 (полтора) раза медленнее. Под балк инсертами понимается insert from select, аппликейшн в тормозах никак не участвует.
Здравствуйте, Sharov, Вы писали:
S>Здравствуйте, gandjustas, Вы писали:
G>>3) Если же запросов много и мы упираемся в базу, то делаем кэширование в приложении. Сохраняем результаты запроса в кэше по ключу.
S>Речь идет об app server'е или о клиенте?
Сервер.
S>Что выступает в роли ключа?
"Тип запроса-ключ". А если у вас REST, то прям сегмент URL.
G>>К чему стоит стремиться если вы хотите делать нагруженное приложение: G>>1) 90% запросов на чтение должно отдаваться из кэша клиента (с помощью 304) G>>2) Из оставшихся 90% должно отдаваться из кэша сервера (статика, inmemory-кэши итд)
S>Речь о 10%, что не кэшом клиента обслуживаются?
да, такое часто бывает для часто меняющихся данных.
У меня было такое в дашборде коллцентра. Запросы приходят примерно раз в минуту и обновляются с такой же периодичностью. Поэтому почти все запросы долетали до сервера. Но данные в течении интервала обновления считывались один раз и отдавались всем клиентам из кэша сервера.
G>>3) Оставшийся 1% запросов, реально улетевших в базу должен быть покрыт индексами S>10% или 1%?
сли из 100% отнять 90%, то останется 10%. Если из оставшихся 10% отнять 90%, то останется 1% от первоначального количества.
S>Т.е. как я понял 90% обслуживаются кэшем клиента, оставшиеся 10% бьются на кэш сервера(9%) и базу (1%). S>Так?
Да
Здравствуйте, Буравчик, Вы писали:
Б>А что будет с производительностью во время batch-update?
Это зависит от того, насколько взрослую СУБД вы используете. Б>Например, когда обновятся ВСЕ ученики школы. Т.е. все старые будут удалены, а все новые добавлены. Б>Допустим в школе 2000 учеников. Триггер выполнит 4000 обновлений агрегированной таблицы?
Во взрослых СУБД триггер активируется 1 раз на стейтмент. То есть если у вас выполняется один delete, а потом один батч insert, то триггер сработает 2 раза.
И параметрами триггера будут таблицы inserted и deleted, над которыми можно выполнять агрегатные операции. Итого, будет выполнено 2 update стейтмента для агрегированной таблицы.
Б>Плюс такая операция заблокирует запись других писателей в эту таблицу.
Именно поэтому я сделал оговорку про соотношение объёмов чтений и записей. Если у вас запись относительно редка (а для школ это так и есть), то триггеры и материализованные представления заруливают агрегацию в минуса.
Б>Выполнить COUNT было бы намного быстрее для этой школы. И блокировки удалось бы избежать.
Быстрее, чем что? один count будет быстрее, чем один update from select count(). А вот тысяча count уже окажутся сильно медленнее, чем один update плюс тысяча select from preaggregatedCount where id = @ Б>В общем, ответ зависит от частоты и типа обновлений исходной таблицы. И триггер часто не лучшее решение.
Триггер — лучшее решение по сравнению с application-side триггером, когда мы уже решили использовать предагрегированные таблицы, но строим их руками в коде приложения, а не внутри RDBMS.
Кроме всяких интересных случаев с порядком блокировок: обычно способов управлять порядком срабатывания триггера нет; это может вызывать всякие false locking conflicts вплоть до дедлоков. Ну, и редкая СУБД сейчас понимает коммутативность инкрементальных апдейтов. Как я понимаю, как раз материализованные представления являются хорошим ответом на эту проблему: мало того, что не нужно самостоятельно изобретать код триггера, рискуя накосячить, так ещё и СУБД может догадаться, что две транзакции, одна из которых удаляет учеников, а другая добавляет, не конфликтуют ни по какому ресурсу. Ну, потому что не важно, в каком порядке выполнять update view set count = count + 2 и update view set count = count — 3
Но, положа руку на сердце, сам я это не проверял.
Уйдемте отсюда, Румата! У вас слишком богатые погреба.
Здравствуйте, rosencrantz, Вы писали: S>>Что за СУБД? S>>В MS SQL триггер будет делать count=count+100500. R>Mysql 8.
Да, для вашей СУБД реализация триггеров на стороне приложения — меньшее зло.
Уйдемте отсюда, Румата! У вас слишком богатые погреба.
Здравствуйте, rosencrantz, Вы писали:
R>Вы говорите о кешировании — мне интересно откуда всё-таки вы предлагаете результат для кеширования брать? Типа удалили 1 юзера, сделали этот зверский select count, запомнили. Второго удалили — то же самое? Или раз в час select count делать (и тогда статистика будет отставать на 1 час конечно).
Вообще-то это тяжелый аналитический запрос, который собрались гонять на OLTP базе. Примитивное кеширование здесь плохая идея, т.к. даже "раз в час" такой селект будет просаживать персентили остальных запросов.
Что сделал бы я:
— либо убедил, что это полностью бессмысленная метрика, либо что нужна полноценная аналитика (а-ля отчеты по среднему кол-ву школьников в школе)
— если это природное любопытство руководства, сделал бы им отдельную кнопку "посчитать кол-во школьников всего"
— если им нужен крутящийся счетик на главной, хранил бы общий count в базе и обновлял его при модификации базы. Возможно, через очередь, чтобы разгрузить базу при массовой вставке.
— если все предыдущие варианты не подходят (включая отдельную аналитическую базу, где это быстрый запрос), то завел бы таблицу "кол-во школьников в каждой школе" и обновлял её после каждой вставки, по каждой измененной школе.
R>InnoDB processes SELECT COUNT(*) statements by traversing the smallest available secondary index unless an index or optimizer hint directs the optimizer to use a different index. If a secondary index is not present, InnoDB processes SELECT COUNT(*) statements by scanning the clustered index.
Судя по этому сообщению надо сделать некластерный индекс по PK, иначе запрос считывает всю таблицу.
R>
R>Processing SELECT COUNT(*) statements takes some time if index records are not entirely in the buffer pool. For a faster count, create a counter table and let your application update it according to the inserts and deletes it does.
Здравствуйте, rosencrantz, Вы писали:
R>Здравствуйте, gandjustas, Вы писали:
R>>>
R>>>InnoDB processes SELECT COUNT(*) statements by traversing the smallest available secondary index unless an index or optimizer hint directs the optimizer to use a different index. If a secondary index is not present, InnoDB processes SELECT COUNT(*) statements by scanning the clustered index.
G>>Судя по этому сообщению надо сделать некластерный индекс по PK, иначе запрос считывает всю таблицу.
R>Да там на самом деле были индексы по email, по username — я думаю кого-то из них он и использовал. Ради эксперимента сейчас добавил явный индекс по PK, explain select count(*) сказал, что будет использовать именно этот новый индекс. Запустил. Те же 7 минут.
Переходи на нормальную СУБД.
Здравствуйте, Sharov, Вы писали: S>Для этого +1 надо дергать ровно 1 месте, желательно в триггере, чтобы сама бд все это делала.
Заодно это положительно влияет на производительность. Из-за того, что код исполняется ближе к данным.
Запиливая код "триггера" в приложении, мы увеличиваем трафик между приложением и СУБД.
Уйдемте отсюда, Румата! У вас слишком богатые погреба.
Есть например аппликейшн для управления данными об учениках в школах. Например Mysql база, в которой хранится иерархия: Schools, Classes, Students. Ожидаемые объёмы данных скажем 1000 школ x 1000 классов на школу x 100 студентов на класс. Т.е. всего 1000 школ, 1млн классов, 100млн студентов. Редактировать-добавлять-удалять можно как отдельные записи через CRUD UI, так и балком — целый класс, целую школу, прям сразу вообще всё — это например через аплоад CSV файлов. Когда удаляешь школу, удаляются все её классы и все ученики из этих классов. Когда удаляешь класс, удаляются все ученики.
В аппликейшне при этом есть несколько страничек, на которых нужно показывать актуальную статистику:
1. Сколько вообще всего школ, классов и учеников
2. Глядя на школу — сколько в ней классов и учеников
3. Глядя на класс — сколько в нём учеников
Данные нужно показывать моментально. К этим страничкам обращаются параллельно по 100 запросов в секунду.
Как бы вы стали реализовывать функциональность подсчёта? Стали бы использовать триггеры в БД или стали бы делать изменение счётчиков в коде? Если статистике разрешается отставать от данных скажем на 1 час — как это повлияло бы на решение?
Здравствуйте, rosencrantz, Вы писали:
R>Как бы вы стали реализовывать функциональность подсчёта? Стали бы использовать триггеры в БД или стали бы делать изменение счётчиков в коде? Если статистике разрешается отставать от данных скажем на 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>Утверждаете ли вы, что мне нужен сервер со 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.
Уйдемте отсюда, Румата! У вас слишком богатые погреба.
Здравствуйте, rosencrantz, Вы писали:
R>Здравствуйте, scf, Вы писали:
scf>>Соглашусь с предыдущим оратором, обычная реляционная СУБД потянет такую нагрузку в реалтайм. Если вдруг случится невероятное и не потянет, то рядом с реляционной базой с мастер-данными нужно развернуть аналитическую базу и пополнять её асинхронно. Причем лучше не связываться с триггерами и закодить на уровне приложения.
R>На моей тестовой базе запрос select count(*) from Users отрабатывает за 7 минут при том, что там 50млн строчек, а не 100 (это AWS RDS — t3.micro). Или вы имеете в виду, что на сервере за 100 баксов в день это будет работать на много порядков быстрее? Каким образом вы принимаете решение о необходимой производительности сервера?
Для InnoDB движка надо count(PK) писать.
Соглашусь с коллегами, что лучше взять postgres. У MySQL слишком много особенностей, помогающих писать тормозные запросы.
G>3) Если же запросов много и мы упираемся в базу, то делаем кэширование в приложении. Сохраняем результаты запроса в кэше по ключу.
Речь идет об app server'е или о клиенте? Что выступает в роли ключа?
G>К чему стоит стремиться если вы хотите делать нагруженное приложение: G>1) 90% запросов на чтение должно отдаваться из кэша клиента (с помощью 304) G>2) Из оставшихся 90% должно отдаваться из кэша сервера (статика, inmemory-кэши итд)
Речь о 10%, что не кэшом клиента обслуживаются?
G>3) Оставшийся 1% запросов, реально улетевших в базу должен быть покрыт индексами
10% или 1%?
Т.е. как я понял 90% обслуживаются кэшем клиента, оставшиеся 10% бьются на кэш сервера(9%) и базу (1%).
Так?
Здравствуйте, Sinclair, Вы писали:
S>Здравствуйте, rosencrantz, Вы писали: R>>Имеете ли вы в виду, что select count(*) from Students по таблице 100млн строчек 100 раз в секунду — это рабочий вариант? S>Нет конечно — надо считать по индексу.
На тестовой базе оба варианта отрабатывают за одинаковое время — 7 минут:
select count(*) from Students;
select count(id) from Students;
Здравствуйте, Sinclair, Вы писали:
S>Здравствуйте, rosencrantz, Вы писали: R>>На моей тестовой базе запрос select count(*) from Users отрабатывает за 7 минут при том, что там 50млн строчек, а не 100 (это AWS RDS — t3.micro). S>А индекс у вас там есть?
Есть первичный ключ и его индекс.
S>З.Ы. Mysql — недоСУБД. Пока ещё не всё прополимерено, возьмите хотя бы postgres.
Я для общего развития спрашиваю. Нет у меня требований показывать эти счётчики
Здравствуйте, DenisCh, Вы писали:
DC>Здравствуйте, rosencrantz, Вы писали:
R>>На моей тестовой базе запрос select count(*) from Users отрабатывает за 7 минут при том, что там 50млн строчек
DC>А если select count(pk_field) from Users, где pk_field — индексированное поле первичного ключа таблицы?
Здравствуйте, gandjustas, Вы писали:
G>Здравствуйте, rosencrantz, Вы писали:
R>>Здравствуйте, scf, Вы писали:
scf>>>Соглашусь с предыдущим оратором, обычная реляционная СУБД потянет такую нагрузку в реалтайм. Если вдруг случится невероятное и не потянет, то рядом с реляционной базой с мастер-данными нужно развернуть аналитическую базу и пополнять её асинхронно. Причем лучше не связываться с триггерами и закодить на уровне приложения.
R>>На моей тестовой базе запрос select count(*) from Users отрабатывает за 7 минут при том, что там 50млн строчек, а не 100 (это AWS RDS — t3.micro). Или вы имеете в виду, что на сервере за 100 баксов в день это будет работать на много порядков быстрее? Каким образом вы принимаете решение о необходимой производительности сервера?
G>Для InnoDB движка надо count(PK) писать.
InnoDB processes SELECT COUNT(*) statements by traversing the smallest available secondary index unless an index or optimizer hint directs the optimizer to use a different index. If a secondary index is not present, InnoDB processes SELECT COUNT(*) statements by scanning the clustered index.
Processing SELECT COUNT(*) statements takes some time if index records are not entirely in the buffer pool. For a faster count, create a counter table and let your application update it according to the inserts and deletes it does.
G>Соглашусь с коллегами, что лучше взять postgres. У MySQL слишком много особенностей, помогающих писать тормозные запросы.
R>>InnoDB processes SELECT COUNT(*) statements by traversing the smallest available secondary index unless an index or optimizer hint directs the optimizer to use a different index. If a secondary index is not present, InnoDB processes SELECT COUNT(*) statements by scanning the clustered index.
G>Судя по этому сообщению надо сделать некластерный индекс по PK, иначе запрос считывает всю таблицу.
Да там на самом деле были индексы по email, по username — я думаю кого-то из них он и использовал. Ради эксперимента сейчас добавил явный индекс по PK, explain select count(*) сказал, что будет использовать именно этот новый индекс. Запустил. Те же 7 минут.
Здравствуйте, 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 час — как это повлияло бы на решение?
Добавил бы столбцы SchoolCount, ClassCount, StudentCount в соответствующие таблицы. Обновлял бы их при изменении данных. И всё.
Здравствуйте, Vladek, Вы писали:
V>Добавил бы столбцы SchoolCount, ClassCount, StudentCount в соответствующие таблицы. Обновлял бы их при изменении данных. И всё.
Здравствуйте, rosencrantz, Вы писали:
R>Здравствуйте, Vladek, Вы писали:
V>>Добавил бы столбцы SchoolCount, ClassCount, StudentCount в соответствующие таблицы. Обновлял бы их при изменении данных. И всё.
R>Триггером или кодом приложения?
В коде. Чтобы потом иметь возможность обновить эти данные в любой удобный момент.
Здравствуйте, Vladek, Вы писали:
R>>Триггером или кодом приложения? V>В коде. Чтобы потом иметь возможность обновить эти данные в любой удобный момент.
А почему не триггером и потом просто спросить цифирь у базы? А так получается, что выполняем работу за базу.
Здравствуйте, Sinclair, Вы писали:
S>Прекрасно изложено. Я бы ещё попробовал сделать materialized view между 2 и 3. Особенно если частота чтений << частоты записей.
Тут правильное отношение ? Чтений намного МЕНЬШЕ чем записей ?
Здравствуйте, IID, Вы писали:
S>>Прекрасно изложено. Я бы ещё попробовал сделать materialized view между 2 и 3. Особенно если частота чтений << частоты записей. IID>Тут правильное отношение ? Чтений намного МЕНЬШЕ чем записей ?
Да, ибо речь про бд. Что-то можно закэшировать и не лезть лишний раз в бд.
Здравствуйте, Буравчик, Вы писали:
Б>Здравствуйте, Sinclair, Вы писали:
S>>Заодно это положительно влияет на производительность. Из-за того, что код исполняется ближе к данным.
Б>А что будет с производительностью во время batch-update? Б>Например, когда обновятся ВСЕ ученики школы. Т.е. все старые будут удалены, а все новые добавлены.
Б>Допустим в школе 2000 учеников. Триггер выполнит 4000 обновлений агрегированной таблицы? Б>Плюс такая операция заблокирует запись других писателей в эту таблицу. Б>Выполнить COUNT было бы намного быстрее для этой школы. И блокировки удалось бы избежать.
Б>В общем, ответ зависит от частоты и типа обновлений исходной таблицы. И триггер часто не лучшее решение.
Здорово, что дискуссия наконец-то добралась до этих деталей. Именно из-за таких размышлений я упомянул в вопросе балк инсерты. Читая из CSV файлов хоть миллион учеников, подсчитать их легко — и потом за 1 апдейт сделать этот самый +1млн. Триггерами ни разу в жизни не пользовался — интересно какой оверхед они добавят если делать этот 1млн плюсадинов.
Здравствуйте, Sharov, Вы писали:
S>Здравствуйте, IID, Вы писали:
S>>>Прекрасно изложено. Я бы ещё попробовал сделать materialized view между 2 и 3. Особенно если частота чтений << частоты записей. IID>>Тут правильное отношение ? Чтений намного МЕНЬШЕ чем записей ?
S>Да, ибо речь про бд. Что-то можно закэшировать и не лезть лишний раз в бд.
Не понимаю.
Кеширование же будет эффективно если записей МЕНЬШЕ. Или ты записи кешировать собрался ?
Здравствуйте, IID, Вы писали:
S>>Да, ибо речь про бд. Что-то можно закэшировать и не лезть лишний раз в бд. IID>Не понимаю. IID>Кеширование же будет эффективно если записей МЕНЬШЕ. Или ты записи кешировать собрался ?
Так необязательно сразу кэш инвалидироавать, некоторое кол-во запросов можно и старым кэшем обслужить.
Здравствуйте, Sharov, Вы писали:
S>Здравствуйте, Vladek, Вы писали:
R>>>Триггером или кодом приложения? V>>В коде. Чтобы потом иметь возможность обновить эти данные в любой удобный момент.
S>А почему не триггером и потом просто спросить цифирь у базы? А так получается, что выполняем работу за базу.
Триггер — часть СУБД, а код — часть программы, ты им владеешь и можешь его использовать как угодно когда угодно (посчитать и сохранить в бд, посчитать и показать в отчете, посчитать и сравнить с бд).
Чтобы использовать триггер, нужен ритуал общения с СУБД — ты не владеешь триггером, это часть СУБД, поэтому надо совершать какие-то посторонние действия, чтобы триггер срабатывал. Придётся доверять ответу СУБД, потому что его будет не с чем сравнивать — знание как получить ответ было пожертвовано СУБД, в программе его нет. Придётся уделять время обслуживанию СУБД, погружаться в детали её работы — проблемы с СУБД будут означать проблемы с программой.
СУБД вполне может дублировать функциональность твоей программы — это только увеличит надёжность. Но не замещать — это потеря функциональности и полезности твоей программы.
Здравствуйте, Vladek, Вы писали:
V>Триггер — часть СУБД, а код — часть программы, ты им владеешь и можешь его использовать как угодно когда угодно (посчитать и сохранить в бд, посчитать и показать в отчете, посчитать и сравнить с бд).
Зачем самому делать то, что хорошо делает бд? Тем более если еще надо будет сравнивать с бд для чего-то
V>Чтобы использовать триггер, нужен ритуал общения с СУБД — ты не владеешь триггером, это часть СУБД, поэтому надо совершать какие-то посторонние действия, чтобы триггер срабатывал.
Ну да, СУБД вообще хранит все пользовательские данные, это вообще центральное и ключевое место всего сервиса. Почему бы
там не хранить еще одну цифирь, которую СУБД может лучше и точнее подсчитать?
V>Придётся доверять ответу СУБД, потому что его будет не с чем сравнивать — знание как получить ответ было пожертвовано СУБД, в программе его нет. Придётся уделять время обслуживанию СУБД, погружаться в детали её работы — проблемы с СУБД будут означать проблемы с программой.
Тут отчасти согласен, надо изучать инструмент. Другой вопрос зачем тогда вообще связываться с СУБД? Раз уж связались, в чем
проблема разобраться с триггером?
V>СУБД вполне может дублировать функциональность твоей программы — это только увеличит надёжность. Но не замещать — это потеря функциональности и полезности твоей программы.
Я по-прежнему не понимаю зачем моя программа должна брать на себя задачи СУБД, если вторая явно с этим лучше справится, а
я потрачу время и напложу багов ? Если уж связались с СУБД, то надо использовать возможности инструмента по-максимуму, а не
переписывать с нуля отлаженный функционал СУБД у себя в программе.
S>>Я по-прежнему не понимаю зачем моя программа должна брать на себя задачи СУБД, если вторая явно с этим лучше справится, а S>>я потрачу время и напложу багов ? Если уж связались с СУБД, то надо использовать возможности инструмента по-максимуму, а не S>>переписывать с нуля отлаженный функционал СУБД у себя в программе.
V>Тогда получается приложение СУБД, а не программа. Это уже философский вопрос.Вообще вера в то, что внутри СУБД уже есть программа заказчика, реализованная и отлаженная, надо её только оттуда извлечь, приводит к нулевой интеллектуальной ценности написанного кода — это будет простой интеграционный код тасования байтов от устройств ввода в базу данных и обратно. Код, который был написан уже тысячу раз, иногда авторы даже тащат целые куски из предыдущих проектов в новый проект.
Я думаю, что для большинства бэка и всяческих 3-х звенок это действительно так. База первична, железо не под BL
докупают, а под базу. И BL во многом пляшет от базы. Я сейчас рассматриваю типовые сценарии, коих большинство, особенно
на дотнет стеке.
V>Код должен моделировать бизнес-процессы и автоматизировать необходимые части, то есть отражать суть того, чем занят бизнес — если считать количество школ и студентов важно, это должно быть отражено в коде. Если правила подсчёта изменятся, код тоже изменится.
Согласен, но если этот функционал пересекается с бд, почему бы не использовать то, что уже сделано и многократно
отлажено? Ну если конкретно речь идет о ф-ии count(), зачем ее самому воспроизводить, наверняка есть
не менее важные задачи (ф-ии), которые с бд не пересекаются?
V>Если код примитивен, то у бизнеса вся автоматизация будет сводиться к накоплению данных в базе данных. Это значит, при развитии бизнеса и смене обстоятельств (это может быть что угодно, от смены облачного провайдера из-за дороговизны, до необходимости поддерживать новые платформы), софт, скорее всего, устареет и не будет адаптирован — бизнес сможет использовать только старую базу данных, а код придётся писать заново.
Полностью согласен, хотя если код на nodejs или ruby, то особых проблем с новыми платформами не будет,
а вот переезд в облака или смена облачного провайдера инвестиций в любой код потребует. Но это такие, глобальные вещи.
Но вообще да, всем нужны данные и потом из этих данных как-то извлекать прибыль. Поэтому по началу бд сильно
первична, поэтому желательно все делать "поближе" к бд.
Здравствуйте, rosencrantz, Вы писали: R>Переписал на триггерах из интереса. Триггер на insert делает +1, и на delete делает -1. Здорово выкинулось много кода из аппликейшна, но балк инсерты стали в 1.5 (полтора) раза медленнее. Под балк инсертами понимается insert from select, аппликейшн в тормозах никак не участвует.
Что за СУБД?
В MS SQL триггер будет делать count=count+100500.
Уйдемте отсюда, Румата! У вас слишком богатые погреба.
Здравствуйте, Sinclair, Вы писали:
S>Здравствуйте, rosencrantz, Вы писали: R>>Переписал на триггерах из интереса. Триггер на insert делает +1, и на delete делает -1. Здорово выкинулось много кода из аппликейшна, но балк инсерты стали в 1.5 (полтора) раза медленнее. Под балк инсертами понимается insert from select, аппликейшн в тормозах никак не участвует. S>Что за СУБД? S>В MS SQL триггер будет делать count=count+100500.
? Если уж связались с СУБД, то надо использовать возможности инструмента по-максимуму, а не
