Интеграция PostgreSQL с другими СУБД: делаем запросы в MySQL

    Нередко бывает так, что в большом проекте в силу тех или иных причин — зачастую исторических, хотя бывает по-всякому — его части могут использовать различные СУБД для хранения и поиска критически важных данных. В числе прочего, этому разнообразию способствует конкуренция и развитие технологий, но, так или иначе, взаимодействие между СУБД описывает стандарт 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;

    план PostgreSQL
     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 хранится с большей точность, чем секунда, но это значение выбрано не случайно, посмотрите:


    план PostgreSQL
     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

    Теперь план запроса выглядит так:


    план PostgreSQL
     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

    Postgres Professional 112,43
    Российский вендор PostgreSQL
    Поделиться публикацией
    Комментарии 16
    • +1
      Скачать исправленную версию mysql_fdw можно отсюда: https://github.com/PostgreSQLpro/mysql_fdw

      Тут 404…
      • +1

        Спасибо, Алексей, исправил

        • +1
          Всегда пожалуйста, Дмитрий :)
      • +4
        Помнится лет 15 назад нам нужно было интегрировать phpBB в какой-то сайт, который крутился на PostgreSQL. Сам phpBB работал на mysql'e и нужно было добавлять в его базу новых пользователей, которые зарегались на головном сайте. Так вот, ничего лучшего мы не придумали как подключить к Постгресу Perl, написать на перле хранимую постгресовскую процедуру, которая вызывалась триггером при вставке нового юзера в базу, которая посредством curl дергала PHPшный скрипт, который в свою очередь добавлял нового юзера. Затем по всей этой цепочке возвращался ответ и парсился в перле, который при ошибке выдавал её наверх и уже в PHP скрипте мы разруливали эту ошибку…
        #суровоеИТ :)
        • 0
          в качестве дополнения. обращение из текущей БД PostgreSQL к другой БД этого же PostgreSQL, даже внутри одного сервера, тоже приходится делать через FOREIGN TABLE. неприятно, что «чужие» таблицы не видны в общем списке таблиц.
          • 0

            Это известная проблема, что PostgreSQL прикидывается, что не видит соседние бд, но при этом кластер использует общий xid, shared buffers и тд и тп.

          • 0
            Подскажите пожалуйста, как сейчас PostgreSQL узнаёт статистику данных в foreign table, чтобы выбрать правильный план в случае с join?
            • 0

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

              • 0
                Как-нибудь ещё, кроме как завернув в функцию с указанием rows, можно дать планировщику понять, сколько данных в foreign table?
                • 0

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

                  • 0
                    А решает ли это https://habrahabr.ru/post/169751/?
                    Можно ли им прокинуть статистику?
                    • 0

                      Александр — мой руководитель, и ему под силу написать в ядро патчи, которые необходимы. Но если это не примут в ядро (а хинтов нет в ядре), то поддерживать компанией пачку таких расширений на голом энтузиазме невозможно.

              • 0

                В случае, если допустИм некоторый лаг по времени (ну и очевидные накладные расходы), можно натянуть materialized view на FDW и получить весь профит статистики, собственных индексов (в том числе GIN/GiST, которых в MySQL, на сколько я помню, толком нет) и прочих прелестей постгреса.

              • 0
                Почему вы рекомендуете make install, даже не checkinstall, не говоря уже о сборке пакета дистрибутива? :)
                Как правильно пакетировать экстеншены для постгреса?
                • +1

                  Потому что я администратор, я собрал не одну сотню пакетов под разные дистрибутивы, и не рекомендую превращать рабочую машину в мусор. Cоветую поддержку повесить все-таки на плечи администратора или дистрибутива. А администратору контролировать права на сервере :)
                  Но перед тем, как это окажется на вашем сервер, вы должны попробовать локально, как — описано в статье.

                • 0

                  Правильно ли я понимаю, что в первой части вы описываете как заставить работать pushdown из FDW для join'а? Если да, то есть ли планы рассказать подробнее об этом механизме, быть может, на примере различных FDW?

                  Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                  Самое читаемое