[Перевод] Тестирование производительности аналитических запросов в PostgreSQL, ClickHouse и clickhousedb_fdw (PostgreSQL)22.07.2020 11:17

В этом исследовании я хотел посмотреть, какие улучшения производительности можно получить, используя источник данных ClickHouse, а не PostgreSQL. Я знаю, какие преимущества производительности при использовании ClickHouse я получаю. Будут ли эти преимущества сохранены, если я получу доступ к ClickHouse из PostgreSQL с помощью внешней оболочки данных (FDW)?

Исследуемыми средами баз данных являются PostgreSQL v11, clickhousedb_fdw и база данных ClickHouse. В конечном счете, из PostgreSQL v11 мы будем запускать различные SQL-запросы, маршрутизируемые через наш clickhousedb_fdw в базу данных ClickHouse. Затем мы увидим, как производительность FDW сравнивается с теми же запросами, выполняемыми в нативном PostgreSQL и нативном ClickHouse.


База данных Clickhouse

ClickHouse — это система управления базами данных на основе колонок с открытым исходным кодом, которая может достигать производительности в 100–1000 раз быстрее, чем традиционные подходы к базам данных, способная обрабатывать более миллиарда строк менее чем за секунду.


Clickhousedb_fdw

clickhousedb_fdw — оболочка внешних данных базы данных ClickHouse, или FDW, является проектом с открытым исходным кодом от Percona. Вот ссылка на репозиторий проекта GitHub:

https://github.com/Percona-Lab/clickhousedb_fdw

В марте я написал блог, который рассказывает вам больше о нашем FDW: https://www.percona.com/blog/2019/03/29/postgresql-access-clickhouse-one-of-the-fastest-column-dbmss-with-clickhousedb_fdw/

Как вы увидите, это обеспечивает FDW для ClickHouse, который позволяет SELECT from, и INSERT INTO, базу данных ClickHouse с сервера PostgreSQL v11.

FDW поддерживает расширенные функции, такие как aggregate и join. Это значительно повышает производительность за счет использования ресурсов удаленного сервера для этих ресурсоемких операций.


Benchmark environment


  • Supermicro server:
    • Intel® Xeon® CPU E5–2683 v3 @ 2.00GHz
    • 2 sockets / 28 cores / 56 threads
    • Memory: 256GB of RAM
    • Storage: Samsung SM863 1.9TB Enterprise SSD
    • Filesystem: ext4/xfs
  • OS: Linux smblade01 4.15.0–42-generic #45~16.04.1-Ubuntu
  • PostgreSQL: version 11


Benchmark tests

Вместо того, чтобы использовать какой-то набор данных, сгенерированный машиной, для этого теста, мы использовали данные «Производительность по времени, сообщаемая о времени работы оператора» с 1987 по 2018 год. Вы можете получить доступ к данным с помощью нашего скрипта, доступного здесь:

https://github.com/Percona-Lab/ontime-airline-performance/blob/master/download.sh

Размер базы данных составляет 85 ГБ, обеспечивая одну таблицу из 109 столбцов.


Benchmark Queries

Вот запросы, которые я использовал для сравнения ClickHouse, clickhousedb_fdw и PostgreSQL.

Table-1: Queries used in benchmark


Query executions

Вот результаты каждого из запросов при выполнении в разных настройках базы данных: PostgreSQL с индексами и без них, собственный ClickHouse и clickhousedb_fdw. Время показывается в миллисекундах.

Table-1: Time taken to execute the queries used in benchmark

Просмотр результатов

График показывает время выполнения запроса в миллисекундах, ось X показывает номер запроса из таблиц выше, а ось Y показывает время выполнения в миллисекундах. Результаты ClickHouse и данные, полученные из postgres с помощью clickhousedb_fdw, показаны. Из таблицы видно, что существует огромная разница между PostgreSQL и ClickHouse, но минимальная разница между ClickHouse и clickhousedb_fdw.

4pgo3q4mxl5izqbkri-tfugqew4.png

Этот график показывает разницу между ClickhouseDB и clickhousedb_fdw. В большинстве запросов накладные расходы FDW не так велики и едва ли значительны, кроме Q12. Этот запрос включает в себя объединения и предложение ORDER BY. Из-за предложения ORDER BY GROUP/BY и ORDER BY не опускаются до ClickHouse.

В таблице 2 мы видим скачок времени в запросах Q12 и Q13. Повторюсь, это вызвано предложением ORDER BY. Чтобы подтвердить это, я выполнил запросы Q-14 и Q-15 с предложением ORDER BY и без него. Без предложения ORDER BY время завершения составляет 259 мс, а с предложением ORDER BY — 1364212. Для отладки этого запроса я объясняю оба запроса, а здесь приведены результаты объяснения.

Q15: Without ORDER BY Clause

bm=# EXPLAIN VERBOSE SELECT a."Year", c1/c2 
     FROM (SELECT "Year", count(*)*1000 AS c1 FROM fontime WHERE "DepDelay" > 10 GROUP BY "Year") a
     INNER JOIN(SELECT "Year", count(*) AS c2 FROM fontime GROUP BY "Year") b ON a."Year"=b."Year";

Q15: Query Without ORDER BY Clause

QUERY PLAN                                                      
Hash Join  (cost=2250.00..128516.06 rows=50000000 width=12)  
Output: fontime."Year", (((count(*) * 1000)) / b.c2)  
Inner Unique: true   Hash Cond: (fontime."Year" = b."Year")  
->  Foreign Scan  (cost=1.00..-1.00 rows=100000 width=12)        
Output: fontime."Year", ((count(*) * 1000))        
Relations: Aggregate on (fontime)        
Remote SQL: SELECT "Year", (count(*) * 1000) FROM "default".ontime WHERE (("DepDelay" > 10)) GROUP BY "Year"  
->  Hash  (cost=999.00..999.00 rows=100000 width=12)        
Output: b.c2, b."Year"        
->  Subquery Scan on b  (cost=1.00..999.00 rows=100000 width=12)              
Output: b.c2, b."Year"              
->  Foreign Scan  (cost=1.00..-1.00 rows=100000 width=12)                    
Output: fontime_1."Year", (count(*))                    
Relations: Aggregate on (fontime)                    
Remote SQL: SELECT "Year", count(*) FROM "default".ontime GROUP BY "Year"(16 rows)

Q14: Query With ORDER BY Clause

bm=# EXPLAIN VERBOSE SELECT a."Year", c1/c2 FROM(SELECT "Year", count(*)*1000 AS c1 FROM fontime WHERE "DepDelay" > 10 GROUP BY "Year") a 
     INNER JOIN(SELECT "Year", count(*) as c2 FROM fontime GROUP BY "Year") b  ON a."Year"= b."Year" 
     ORDER BY a."Year";

Q14: Query Plan with ORDER BY Clause

QUERY PLAN 
Merge Join  (cost=2.00..628498.02 rows=50000000 width=12)   
Output: fontime."Year", (((count(*) * 1000)) / (count(*)))   
Inner Unique: true   Merge Cond: (fontime."Year" = fontime_1."Year")   
->  GroupAggregate  (cost=1.00..499.01 rows=1 width=12)        
Output: fontime."Year", (count(*) * 1000)         
Group Key: fontime."Year"         
->  Foreign Scan on public.fontime  (cost=1.00..-1.00 rows=100000 width=4)               
Remote SQL: SELECT "Year" FROM "default".ontime WHERE (("DepDelay" > 10)) 
            ORDER BY "Year" ASC   
->  GroupAggregate  (cost=1.00..499.01 rows=1 width=12)         
Output: fontime_1."Year", count(*)         Group Key: fontime_1."Year"         
->  Foreign Scan on public.fontime fontime_1  (cost=1.00..-1.00 rows=100000 width=4) 
              
Remote SQL: SELECT "Year" FROM "default".ontime ORDER BY "Year" ASC(16 rows)

Вывод

Результаты этих экспериментов показывают, что ClickHouse предлагает действительно хорошую производительность, а clickhousedb_fdw предлагает преимущества производительности ClickHouse из PostgreSQL. Хотя при использовании clickhousedb_fdw есть некоторые накладные расходы, они незначительны и сопоставимы с производительностью, достигнутой при естественном запуске в базе данных ClickHouse. Это также подтверждает, что fdw в PostgreSQL обеспечивает замечательные результаты.

Телеграм чат по Clickhouse https://t.me/clickhouse_ru
Телеграм чат по PostgreSQL https://t.me/pgsql

© Habrahabr.ru