Почему агрегированные функции SQL работают намного медленнее, чем Python и Java (или OLAP)

Ответ 1

Postgres делает намного больше, чем выглядит (сохраняя согласованность данных для начала!)

Если значения не должны быть на 100% точнее, или если таблица обновляется редко, но вы часто используете этот расчет, вам может понадобиться посмотреть в Materialized Views, чтобы ускорить его.

(Заметьте, я не использовал материализованные представления в Postgres, они смотрят на мало хаки, но могут удовлетворить вашу ситуацию).

Материализованные виды

Также рассмотрите накладные расходы на фактическое подключение к серверу и обратную связь, требуемую для отправки запроса на сервер и обратно.

Я бы подумал о 200 мс, чтобы что-то вроде этого было довольно хорошим, быстрый тест на моем сервере oracle, та же структура таблицы с примерно 500 тыс. строк и без индексов занимает около 1 - 1,5 секунды, что почти все просто оракула сосать данные с диска.

Реальный вопрос: достаточно ли 200 мс?

-------------- Подробнее --------------------

Мне было интересно решить это с помощью материализованных представлений, так как я никогда с ними не играл. Это в оракуле.

Сначала я создал MV, который обновляется каждую минуту.

create materialized view mv_so_x 
build immediate 
refresh complete 
START WITH SYSDATE NEXT SYSDATE + 1/24/60
 as select count(*),avg(a),avg(b),avg(c),avg(d) from so_x;

Пока он обновляется, нет возвращенных строк

SQL> select * from mv_so_x;

no rows selected

Elapsed: 00:00:00.00

Как только он обновится, его МНОГО быстрее, чем выполнение необработанного запроса

SQL> select count(*),avg(a),avg(b),avg(c),avg(d) from so_x;

  COUNT(*)     AVG(A)     AVG(B)     AVG(C)     AVG(D)
---------- ---------- ---------- ---------- ----------
   1899459 7495.38839 22.2905454 5.00276131 2.13432836

Elapsed: 00:00:05.74
SQL> select * from mv_so_x;

  COUNT(*)     AVG(A)     AVG(B)     AVG(C)     AVG(D)
---------- ---------- ---------- ---------- ----------
   1899459 7495.38839 22.2905454 5.00276131 2.13432836

Elapsed: 00:00:00.00
SQL> 

Если мы вставим в базовую таблицу, результат не будет сразу доступен для просмотра MV.

SQL> insert into so_x values (1,2,3,4,5);

1 row created.

Elapsed: 00:00:00.00
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
SQL> select * from mv_so_x;

  COUNT(*)     AVG(A)     AVG(B)     AVG(C)     AVG(D)
---------- ---------- ---------- ---------- ----------
   1899459 7495.38839 22.2905454 5.00276131 2.13432836

Elapsed: 00:00:00.00
SQL> 

Но подождите минуту или около того, и MV будет обновляться за кулисами, и результат возвращается быстро, как вы могли бы хотеть.

SQL> /

  COUNT(*)     AVG(A)     AVG(B)     AVG(C)     AVG(D)
---------- ---------- ---------- ---------- ----------
   1899460 7495.35823 22.2905352 5.00276078 2.17647059

Elapsed: 00:00:00.00
SQL> 

Это не идеально. для начала, его не в реальном времени, вставки/обновления не будут сразу видны. Кроме того, у вас есть запрос на обновление MV, нужен вам это или нет (это может быть настроено на любой временной интервал или по требованию). Но это показывает, насколько быстрее MV может заставить его казаться конечному пользователю, если вы можете жить со значениями, которые не совсем соответствуют второму точному.

Ответ 2

Я бы сказал, что ваша тестовая схема не очень полезна. Чтобы выполнить запрос db, сервер db проходит несколько этапов:

• проанализировать SQL
• выполнить план запроса, т.е. е. определить, какие индексы использовать (если есть), оптимизировать и т.д.
• Если используется индекс, найдите его для указателей на фактические данные, затем перейдите в соответствующее место в данных или
• Если индекс не используется, сканируйте всю таблицу, чтобы определить, какие строки необходимы
• загружать данные с диска во временное место (надеюсь, но не обязательно, память)
• выполнить вычисления count() и avg()

Итак, создание массива в Python и получение среднего значения в основном пропускают все эти шаги, сохраняя последний. Поскольку дисковый ввод-вывод является одним из самых дорогих операций, которые должна выполнить программа, это основной недостаток теста (см. Также ответы на этот вопрос. Я спросил здесь до). Даже если вы читаете данные с диска в другом тесте, процесс совершенно другой, и трудно сказать, насколько важны результаты.

Чтобы получить дополнительную информацию о том, где Postgres проводит свое время, я бы предложил следующие тесты:

• Сравните время выполнения вашего запроса с SELECT без агрегационных функций (например, шаг 5)
• Если вы обнаружите, что агрегация приводит к значительному замедлению, попробуйте, если Python сделает это быстрее, получив необработанные данные через простой SELECT из сравнения.

Чтобы ускорить ваш запрос, сначала снимите доступ к диску. Я очень сомневаюсь, что это агрегация, которая занимает время.

Есть несколько способов сделать это:

• Данные кэша (в памяти!) для последующего доступа либо через собственные возможности модуля db, либо с помощью таких инструментов, как memcached
• Уменьшить размер сохраненных данных.
• Оптимизируйте использование индексов. Иногда это может означать пропустить использование индекса вообще (в конце концов, это и доступ к диску). Для MySQL я, похоже, помню, что рекомендуется пропускать индексы, если вы предполагаете, что запрос извлекает более 10% всех данных в таблице.
• Если ваш запрос хорошо использует индексы, я знаю, что для баз данных MySQL он позволяет помещать индексы и данные на отдельные физические диски. Однако я не знаю, применимо ли это для Postgres.
• Также могут возникнуть более сложные проблемы, такие как замена строк на диск, если по какой-либо причине набор результатов не может быть полностью обработан в памяти. Но я бы оставил такие исследования, пока не столкнулся с серьезными проблемами с производительностью, и я не могу найти другого способа исправить, поскольку для этого требуются знания о небольшом количестве деталей под капотом в вашем процессе.

Update:

Я просто понял, что вы, похоже, не используете индексы для вышеуказанного запроса и, скорее всего, тоже не используете его, поэтому мой совет по индексам, вероятно, не помог. Сожалею. Тем не менее, я бы сказал, что агрегация - это не проблема, а доступ к диску. В любом случае я останусь в индексировании.

Ответ 3

Я повторно протестировал MySQL, указав ENGINE = MEMORY, и он ничего не меняет (еще 200 мс). Sqlite3 с использованием встроенной памяти db дает аналогичные тайминги (250 мс).

Математика здесь выглядит правильно (по крайней мере, размер, так как размер sqlite db равен: -)

Я просто не покупаю аргумент disk-cause-slowowness, так как есть все признаки того, что таблицы хранятся в памяти (ребята из postgres все предупреждают о том, чтобы слишком сильно пытаться привязывать таблицы к памяти, поскольку они клянутся, что ОС сделает это лучше чем программист)

Чтобы уточнить тайминги, код Java не читает с диска, что делает его абсолютно несправедливым, если Postgres читает с диска и вычисляет сложный запрос, но это, действительно, помимо точки, БД должно быть достаточно умным, чтобы вставьте небольшую таблицу в память и предварительно скомпилируйте хранимую процедуру IMHO.

UPDATE (в ответ на первый комментарий ниже):

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

Ответ 4

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

Можно ли предварительно скомпилировать запрос и план оптимизации? Я бы подумал, что хранимая процедура будет делать это, но это действительно не помогает.

Чтобы избежать доступа к диску, необходимо кэшировать всю таблицу в памяти, могу ли я заставить Postgres сделать это? Я думаю, что это уже делает это, поскольку запрос выполняется всего за 200 мс после повторных прогонов.

Можно ли сообщить Postgres, что таблица только для чтения, поэтому она может оптимизировать любой код блокировки?

Я думаю, что можно оценить затраты на построение запроса с пустой таблицей (тайминги варьируются от 20-60 мс)

Я все еще не понимаю, почему тесты Java/Python недействительны. Postgres просто не делает гораздо больше работы (хотя я до сих пор не рассматривал аспект concurrency, просто построение кэширования и запроса)

UPDATE: Я не думаю, что справедливо сравнивать SELECTS, как было предложено, вытащив 350 000 с помощью шагов драйвера и сериализации в Python для запуска агрегации и даже для исключения агрегации, поскольку накладные расходы при форматировании и отображении трудно отделить от времени. Если оба двигателя работают в данных памяти, это должно быть сравнение яблок с яблоками, я не уверен, как гарантировать, что это уже происходит.

Я не могу понять, как добавлять комментарии, может быть, у меня недостаточно репутации?

Ответ 5

Я сам парень MS-SQL, и мы будем использовать DBCC PINTABLE, чтобы сохранить таблицу в кеше и SET STATISTICS IO, чтобы увидеть, что он читает из кеша, а не из диска.

Я ничего не могу найти в Postgres, чтобы имитировать PINTABLE, но pg_buffercache, похоже, дает подробную информацию о том, что находится в кеше, - вы можете хотите проверить это и посмотреть, действительно ли ваша таблица кэшируется.

Быстрое вычисление конверта заставляет меня подозревать, что вы paging с диска. Предполагая, что Postgres использует 4-байтовые целые числа, у вас есть (6 * 4) байт на строку, поэтому ваша таблица имеет минимум (24 * 350 000) байтов ~ 8,4 МБ. Предполагая, что на вашем жестком диске поддерживается 40 МБ/с, вы просматриваете около 200 мс для чтения данных (что, как указано ниже, должно быть где почти все время расходуется).

Если я не испортил свою математику где-то, я не вижу, как возможно, что вы можете читать 8 МБ в своем приложении Java и обрабатывать его в то время, когда вы показываете - если этот файл уже не кэширован ни диска или вашей ОС.

Ответ 6

Я не думаю, что ваши результаты все это удивительно - если что-то такое, что Postgres так быстро.

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

Если этот уровень производительности является проблемой для вашего приложения на практике, но вам нужна RDBMS по другим причинам, вы можете посмотреть memcached. Тогда у вас был бы быстрый кешированный доступ к необработанным данным и мог бы выполнять вычисления в коде.

Ответ 7

Используете ли вы TCP для доступа к Postgres? В этом случае Нагле возится с вашим временем.

Ответ 8

Еще одна вещь, которую СУБД обычно делает для вас, заключается в предоставлении concurrency, защищая вас от одновременного доступа другим процессом. Это делается путем размещения блокировок, и из-за этого возникают некоторые накладные расходы.

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

Ответ 9

Вам нужно увеличить кэширование postgres до такой степени, что весь рабочий набор вписывается в память, прежде чем вы сможете ожидать, что производительность сравнима с выполнением этого в памяти с помощью программы.

Ответ 10

Спасибо за тайм-ауты Oracle, что я ищу (что неутешительно: -)

Материализованные представления, вероятно, заслуживают внимания, поскольку я думаю, что я могу прекомпопировать наиболее интересные формы этого запроса для большинства пользователей.

Я не думаю, что время обратного хода запроса должно быть очень высоким, поскольку я запускаю запросы на том же компьютере, на котором запущен Postgres, поэтому он не может добавить много латентности?

Я также немного проверял размеры кеша, и кажется, что Postgres полагается на ОС для обработки кэширования, они конкретно упоминают BSD как идеальную ОС для этого, поэтому я думаю, что Mac OS должна быть довольно умной таблицу в память. Если у кого-то нет более специфических параметров, я думаю, что более конкретное кэширование вне моего контроля.

В конце концов, я могу, возможно, поработать с временем ответа 200 мс, но зная, что 7 мс - это возможная цель, заставляет меня чувствовать себя неудовлетворенным, так как даже 20-50 мс раз больше пользователей будет иметь больше актуальных запросов и избавиться от большого количества кэширования и предварительно вычисленных хаков.

Я просто проверял тайминги, используя MySQL 5, и они немного хуже Postgres. Поэтому, не допуская серьезных прорывов в кэшировании, я предполагаю, что это то, что я могу ожидать в реляционном маршруте db.

Я хотел бы проголосовать за некоторые ваши ответы, но пока у меня недостаточно очков.