Нередко бывает так, что в большом проекте в силу тех или иных причин — зачастую исторических, хотя бывает по-всякому — его части могут использовать различные СУБД для хранения и поиска критически важных данных. В числе прочего, этому разнообразию способствует конкуренция и развитие технологий, но, так или иначе, взаимодействие между СУБД описывает стандарт SQL/MED 2003 (Management of External Data), который вводит определение Foreign Data Wrappers (FDW) и Datalink.
Первая часть стандарта предлагает средства для чтения данных как набора реляционных таблиц под управлением одного или нескольких внешних источников; FDW также может представлять возможность использовать SQL-интерфейс для доступа к не SQL данным, таким, как файлы или, например, список писем в почтовом ящике. Вторая часть, Datalink, позволяет управлять удаленным SQL-сервером.
Эти две части были реализованы еще в PostgreSQL 9.1 и называются FDW и dblink соответственно. FDW в PostgreSQL сделан максимально гибко, что позволяет разрабатывать wrapper'ы для большого количества внешних источников. В настоящее время мне известны такие FDW, как PostgreSQL, Oracle, SQL Server, MySQL, Cassandra, Redis, RethinkDB, Ldap, а также FDW к файлам типа CSV, JSON, XML и т.п.
В нашей статье мы поговорим о том, как настроить подключение PostgreSQL к MySQL и эффективно выполнять получающиеся запросы.
Для начала собираем и устанавливаем mysql_fdw:
git clone https://github.com/EnterpriseDB/mysql_fdw.git
cd mysql_fdw
# во всех rhel-like дистрибутивов pg_config не попадает в PATH, он лежит в /usr/pgsql-9.5/bin:
PATH=$PATH:/usr/pgsql-9.5/bin USE_PGXS=1 make installУстаналиваем extension на базу, чтобы загрузились необходимые библиотеки:
CREATE EXTENSION mysql_fdw ;Создаем сервер:
CREATE SERVER mysql_server_data FOREIGN DATA WRAPPER mysql_fdw
OPTIONS (host '127.0.0.1', port '3306');И mapping текущего пользователя в PostgreSQL в пользователя MySQL:
CREATE USER MAPPING FOR user SERVER mysql_server_data
OPTIONS (username 'data', password 'datapass');После этого мы имеем возможность подключить таблицу MySQL в PostgreSQL:
CREATE FOREIGN TABLE
orders_2014 (
id int,
customer_id int,
order_date timestamp)
SERVER mysql_server_data
OPTIONS (dbname 'data', table_name 'orders');Допустим, мы храним справочник customers в PostgreSQL:
CREATE TABLE customers (id serial, name text);Попробуем выбрать 5 самых активных покупателей в январе 2014 года:
explain (analyze,verbose)
select
count(o2014.id),
c.name
from orders_2014 o2014
inner join customers c on c.id = o2014.customer_id
where
extract('month' from o2014.order_date) = 1 and
extract('year' from o2014.order_date) = 2014
group by 2 order by 1 desc limit 5; Limit (cost=1285.32..1285.34 rows=5 width=36) (actual time=0.276..0.276 rows=5 loops=1)
Output: (count(o2014.id)), c.name
-> Sort (cost=1285.32..1285.82 rows=200 width=36) (actual time=0.275..0.275 rows=5 loops=1)
Output: (count(o2014.id)), c.name
Sort Key: (count(o2014.id)) DESC
Sort Method: quicksort Memory: 25kB
-> HashAggregate (cost=1280.00..1282.00 rows=200 width=36) (actual time=0.270..0.271 rows=5 loops=1)
Output: count(o2014.id), c.name
Group Key: c.name
-> Merge Join (cost=1148.00..1248.25 rows=6350 width=36) (actual time=0.255..0.264 rows=8 loops=1)
Output: o2014.id, c.name
Merge Cond: (o2014.customer_id = c.id)
-> Sort (cost=1059.83..1062.33 rows=1000 width=8) (actual time=0.240..0.241 rows=8 loops=1)
Output: o2014.id, o2014.customer_id
Sort Key: o2014.customer_id
Sort Method: quicksort Memory: 25kB
-> Foreign Scan on public.orders_2014 o2014 (cost=10.00..1010.00 rows=1000 width=8) (actual time=0.065..0.233 rows=8 loops=1)
Output: o2014.id, o2014.customer_id
Filter: ((date_part('month'::text, o2014.order_date) = '1'::double precision) AND (date_part('year'::text, o2014.order_date) = '2014'::double precision))
Rows Removed by Filter: 58
Local server startup cost: 10
Remote query: SELECT `id`, `customer_id`, `order_date` FROM `data`.`orders`
-> Sort (cost=88.17..91.35 rows=1270 width=36) (actual time=0.011..0.011 rows=9 loops=1)
Output: c.name, c.id
Sort Key: c.id
Sort Method: quicksort Memory: 25kB
-> Seq Scan on public.customers c (cost=0.00..22.70 rows=1270 width=36) (actual time=0.004..0.005 rows=12 loops=1)
Output: c.name, c.idКак мы видим, запрос неэффективный, так как со стороны MySQL было получено содержимой всей таблицы: SELECT id, customer_id, order_date FROM data.orders. Сервер, в силу естественных ограничений драйвера MySQL, не в состоянии трансформировать запрос таким образом, чтобы для получения корректного результата этот запрос было бы возможно выполнить на стороне MySQL, и поэтому сначала получает таблицу целиком, а потом уже осуществляет фильтрацию. Однако при изменении запроса можно добиться того, чтобы фильтрация по дате осуществлялась на стороне MySQL:
explain (analyze,verbose)
select
count(o2014.id),
c.name
from orders_2014 o2014
inner join customers c on c.id = o2014.customer_id
where
o2014.order_date between ('2014-01-01') and ('2014-02-01'::timestamptz - '1 sec'::interval)
group by 2 order by 1 desc limit 5;Сравнение order_date с ('2014-02-01'::timestamp - '1 sec'::interval) неправильно, так как timestamptz хранится с большей точность, чем секунда, но это значение выбрано не случайно, посмотрите:
Limit (cost=1285.32..1285.34 rows=5 width=36) (actual time=0.130..0.130 rows=0 loops=1)
Output: (count(o2014.id)), c.name
-> Sort (cost=1285.32..1285.82 rows=200 width=36) (actual time=0.129..0.129 rows=0 loops=1)
Output: (count(o2014.id)), c.name
Sort Key: (count(o2014.id)) DESC
Sort Method: quicksort Memory: 25kB
-> HashAggregate (cost=1280.00..1282.00 rows=200 width=36) (actual time=0.114..0.114 rows=0 loops=1)
Output: count(o2014.id), c.name
Group Key: c.name
-> Merge Join (cost=1148.00..1248.25 rows=6350 width=36) (actual time=0.111..0.111 rows=0 loops=1)
Output: o2014.id, c.name
Merge Cond: (o2014.customer_id = c.id)
-> Sort (cost=1059.83..1062.33 rows=1000 width=8) (actual time=0.110..0.110 rows=0 loops=1)
Output: o2014.id, o2014.customer_id
Sort Key: o2014.customer_id
Sort Method: quicksort Memory: 25kB
-> Foreign Scan on public.orders_2014 o2014 (cost=10.00..1010.00 rows=1000 width=8) (actual time=0.093..0.093 rows=0 loops=1)
Output: o2014.id, o2014.customer_id
Local server startup cost: 10
Remote query: SELECT `id`, `customer_id` FROM `data`.`orders` WHERE ((`order_date` >= '2014-01-01 00:00:00+00')) AND ((`order_date` <= ('2014-01-02 00:00:00+00' - '00:00:01')))
-> Sort (cost=88.17..91.35 rows=1270 width=36) (never executed)
Output: c.name, c.id
Sort Key: c.id
-> Seq Scan on public.customers c (cost=0.00..22.70 rows=1270 width=36) (never executed)
Output: c.name, c.idТут нас поджидает проблема, из-за которой стоит использовать mysql_fdw с большой осторожностью:
SELECT `id`, `customer_id` FROM `data`.`orders` WHERE ((`order_date` >= '2014-01-01 00:00:00+00')) AND ((`order_date` <= ('2014-01-02 00:00:00+00' - '00:00:01')))Как мы видим, between, представляя из себя синтаксический сахар, был развернут в два условия, одно из которых не вычислено на стороне PostgreSQL: ('2014-02-01'::timestamp - '1 sec'::interval) и преобразовано в разницу двух строк (а не даты и интервала):
mysql> select '2014-01-02 00:00:00+00' - '00:00:01';
+---------------------------------------+
| '2014-01-02 00:00:00+00' - '00:00:01' |
+---------------------------------------+
| 2014 |
+---------------------------------------+
1 row in set, 2 warnings (0.00 sec)В итоге запрос возвращает неправильный результат.
С подобной проблемой столкнулся один из наших клиентов. Проблема была исправлена в форке PostgresPro, https://github.com/postgrespro/mysql_fdw и создан pull-реквест в основной репозиторий EnterpriseDB. Устанавливаем исправленную версию:
git clone https://github.com/postgrespro/mysql_fdw.git mysql_fdw_pgpro
cd mysql_fdw_pgpro
PATH=$PATH:/usr/pgsql-9.5/bin USE_PGXS=1 make installТеперь план запроса выглядит так:
Limit (cost=1285.32..1285.34 rows=5 width=36) (actual time=0.219..0.219 rows=5 loops=1)
Output: (count(o2014.id)), c.name
-> Sort (cost=1285.32..1285.82 rows=200 width=36) (actual time=0.218..0.218 rows=5 loops=1)
Output: (count(o2014.id)), c.name
Sort Key: (count(o2014.id)) DESC
Sort Method: quicksort Memory: 25kB
-> HashAggregate (cost=1280.00..1282.00 rows=200 width=36) (actual time=0.199..0.201 rows=5 loops=1)
Output: count(o2014.id), c.name
Group Key: c.name
-> Merge Join (cost=1148.00..1248.25 rows=6350 width=36) (actual time=0.183..0.185 rows=8 loops=1)
Output: o2014.id, c.name
Merge Cond: (o2014.customer_id = c.id)
-> Sort (cost=1059.83..1062.33 rows=1000 width=8) (actual time=0.151..0.151 rows=8 loops=1)
Output: o2014.id, o2014.customer_id
Sort Key: o2014.customer_id
Sort Method: quicksort Memory: 25kB
-> Foreign Scan on public.orders_2014 o2014 (cost=10.00..1010.00 rows=1000 width=8) (actual time=0.116..0.120 rows=8 loops=1)
Output: o2014.id, o2014.customer_id
Local server startup cost: 10
Remote query: SELECT `id`, `customer_id` FROM `data`.`orders` WHERE ((`order_date` >= '2014-01-01 00:00:00+00')) AND ((`order_date` <= ('2014-01-02 00:00:00+00' - INTERVAL 1 SECOND)))
-> Sort (cost=88.17..91.35 rows=1270 width=36) (actual time=0.030..0.030 rows=9 loops=1)
Output: c.name, c.id
Sort Key: c.id
Sort Method: quicksort Memory: 25kB
-> Seq Scan on public.customers c (cost=0.00..22.70 rows=1270 width=36) (actual time=0.018..0.020 rows=12 loops=1)
Output: c.name, c.idЗапрос стал быстрее по сравнению с первым, так как с MySQL мы возвращаем значение более точечного запроса:
SELECT `id`, `customer_id` FROM `data`.`orders` WHERE ((`order_date` >= '2014-01-01 00:00:00+00')) AND ((`order_date` <= ('2014-01-02 00:00:00+00' - INTERVAL 1 SECOND)))Операция фильтрации выполняется теперь на стороне MySQL. При определенных условиях будет использоваться индекс по order_date, если он создан.
Таким образом мы ускорили выполнение запроса. На простом примере мы почуствовали силу Open Source и мощь PostgreSQL в расширяемости.
Спасибо за внимание!
» Подробнее про SQL-MED
» Скачать исправленную версию mysql_fdw
Комментарии (16)
AlexSam
30.08.2016 16:08+4Помнится лет 15 назад нам нужно было интегрировать phpBB в какой-то сайт, который крутился на PostgreSQL. Сам phpBB работал на mysql'e и нужно было добавлять в его базу новых пользователей, которые зарегались на головном сайте. Так вот, ничего лучшего мы не придумали как подключить к Постгресу Perl, написать на перле хранимую постгресовскую процедуру, которая вызывалась триггером при вставке нового юзера в базу, которая посредством curl дергала PHPшный скрипт, который в свою очередь добавлял нового юзера. Затем по всей этой цепочке возвращался ответ и парсился в перле, который при ошибке выдавал её наверх и уже в PHP скрипте мы разруливали эту ошибку…
#суровоеИТ :)
QuickJoey
30.08.2016 16:42в качестве дополнения. обращение из текущей БД PostgreSQL к другой БД этого же PostgreSQL, даже внутри одного сервера, тоже приходится делать через FOREIGN TABLE. неприятно, что «чужие» таблицы не видны в общем списке таблиц.
vadv
30.08.2016 16:45Это известная проблема, что PostgreSQL прикидывается, что не видит соседние бд, но при этом кластер использует общий xid, shared buffers и тд и тп.
akhkmed
31.08.2016 11:40Подскажите пожалуйста, как сейчас PostgreSQL узнаёт статистику данных в foreign table, чтобы выбрать правильный план в случае с join?
vadv
31.08.2016 11:41Это никак не предусмотренно. Вы можете изменить свой запрос для удаленного сервера, чтобы он эффективнее выполнялся.
akhkmed
31.08.2016 13:05Как-нибудь ещё, кроме как завернув в функцию с указанием rows, можно дать планировщику понять, сколько данных в foreign table?
vadv
31.08.2016 20:08вы абсолютно правы, надо сделать функции под типичные запросы, другого способа подсказать планеру нет (как и хинтов). скорее всего сообщество не пойдет на подобные "хинты", а реализовать подобное без поддержки ядра не возможно.
Komzpa
31.08.2016 21:11А решает ли это https://habrahabr.ru/post/169751/?
Можно ли им прокинуть статистику?vadv
31.08.2016 21:26Александр — мой руководитель, и ему под силу написать в ядро патчи, которые необходимы. Но если это не примут в ядро (а хинтов нет в ядре), то поддерживать компанией пачку таких расширений на голом энтузиазме невозможно.
Loriowar
09.09.2016 20:14В случае, если допустИм некоторый лаг по времени (ну и очевидные накладные расходы), можно натянуть materialized view на FDW и получить весь профит статистики, собственных индексов (в том числе GIN/GiST, которых в MySQL, на сколько я помню, толком нет) и прочих прелестей постгреса.
Komzpa
31.08.2016 21:07Почему вы рекомендуете make install, даже не checkinstall, не говоря уже о сборке пакета дистрибутива? :)
Как правильно пакетировать экстеншены для постгреса?vadv
31.08.2016 21:31+1Потому что я администратор, я собрал не одну сотню пакетов под разные дистрибутивы, и не рекомендую превращать рабочую машину в мусор. Cоветую поддержку повесить все-таки на плечи администратора или дистрибутива. А администратору контролировать права на сервере :)
Но перед тем, как это окажется на вашем сервер, вы должны попробовать локально, как — описано в статье.
Loriowar
09.09.2016 20:20Правильно ли я понимаю, что в первой части вы описываете как заставить работать pushdown из FDW для join'а? Если да, то есть ли планы рассказать подробнее об этом механизме, быть может, на примере различных FDW?
slaykovsky
Тут 404…
vadv
Спасибо, Алексей, исправил
slaykovsky
Всегда пожалуйста, Дмитрий :)