Keith's Ramblings…

As promised in my previous post on the PostgreSQL Oracle FDW, I’ve done some performance testing verses our old method of using DBI-Link to replicate tables from an Oracle 8i instance to PG 9.1.2. I’ve got good news on several fronts!

First being that there is a definite improvement on the replication speed. Here’s some stats on our old process that copied an entire table of about 1.5million rows

      job_name          |         timestamp          |    completion_timestamp    |    duration     
------------------------+----------------------------+----------------------------+-----------------
 DBI-LINK TABLE REFRESH | 2012-02-10 05:00:07.720262 | 2012-02-10 05:07:40.509401 | 00:07:32.789139
 DBI-LINK TABLE REFRESH | 2012-02-09 05:00:07.674933 | 2012-02-09 05:07:59.087194 | 00:07:51.412261
 DBI-LINK TABLE REFRESH | 2012-02-08 05:00:07.880216 | 2012-02-08 05:07:31.350684 | 00:07:23.470468
 DBI-LINK TABLE REFRESH | 2012-02-07 05:00:08.1697   | 2012-02-07 05:07:47.219942 | 00:07:39.050242
 DBI-LINK TABLE REFRESH | 2012-02-06 05:00:07.793401 | 2012-02-06 05:07:48.86486  | 00:07:41.071459
 DBI-LINK TABLE REFRESH | 2012-02-05 05:00:08.158177 | 2012-02-05 05:07:42.889557 | 00:07:34.73138
 DBI-LINK TABLE REFRESH | 2012-02-04 05:00:07.665676 | 2012-02-04 05:07:12.19166  | 00:07:04.525984
 DBI-LINK TABLE REFRESH | 2012-02-03 05:00:08.077096 | 2012-02-03 05:07:09.999919 | 00:07:01.922823
 DBI-LINK TABLE REFRESH | 2012-02-02 05:00:07.727106 | 2012-02-02 05:07:36.716676 | 00:07:28.98957
 DBI-LINK TABLE REFRESH | 2012-02-01 05:00:07.767412 | 2012-02-01 05:07:15.261486 | 00:07:07.494074

So you can see on average this job took about 7 1/2 minutes. Now lets see what using the FDW tables does for this process

    job_name       |         timestamp          |    completion_timestamp    |    duration     
-------------------+----------------------------+----------------------------+-----------------
 FDW TABLE REFRESH | 2012-02-20 05:00:10.347331 | 2012-02-20 05:04:29.999205 | 00:04:19.651874
 FDW TABLE REFRESH | 2012-02-19 05:00:07.649312 | 2012-02-19 05:03:41.616585 | 00:03:33.967273
 FDW TABLE REFRESH | 2012-02-18 05:00:10.010323 | 2012-02-18 05:04:01.782797 | 00:03:51.772474
 FDW TABLE REFRESH | 2012-02-17 05:00:10.373712 | 2012-02-17 05:04:18.442016 | 00:04:08.068304
 FDW TABLE REFRESH | 2012-02-16 05:00:08.5677   | 2012-02-16 05:04:16.405462 | 00:04:07.837762
 FDW TABLE REFRESH | 2012-02-15 05:00:10.68691  | 2012-02-15 05:05:09.513021 | 00:04:58.826111
 FDW TABLE REFRESH | 2012-02-14 05:00:07.595426 | 2012-02-14 05:03:47.697134 | 00:03:40.101708
 FDW TABLE REFRESH | 2012-02-13 05:00:07.557273 | 2012-02-13 05:03:54.2805   | 00:03:46.723227
 FDW TABLE REFRESH | 2012-02-12 05:00:08.176132 | 2012-02-12 05:03:49.145094 | 00:03:40.968962
 FDW TABLE REFRESH | 2012-02-11 05:00:08.053425 | 2012-02-11 05:04:03.345984 | 00:03:55.292559

On average about 4 minutes. That’s almost a 50% decrease! We have a lot of tables being refreshed in this manner, some much larger and others many times per day, so in total this will have a pretty big impact on the time spent in replication. We will still need to keep DBI_Link around, though, since some of our jobs require writing back to Oracle which FDW does not support at this time.

I’ve generalized some of the code we use for doing these materialized snapshot views and made it available in case others may find it useful. Note that I only have Oracle 8i to work with at the moment, so some different setup steps may be required for your (hopefully) more modern versions. If the setup is different for other versions, I would appreciate anyone contributing back a setup file. I think the most useful part is really the function to automatically make an FDW table given only a remote Oracle table name. I’ll probably try and make that a little more general in the future so it’s not tied quite so much to the whole snapshot system (Update 4/27/2012: This has been done and committed to github).

https://github.com/keithf4/oracle_fdw_snapshot

And some other good news out of this testing was helping the Oracle FDW author out and letting him know about a bug that came up when a query on an FDW table would fail under certain conditions and invalidate all further FDW queries for that session. If you’ve run into this issue as well, he was able to get it fixed and now it handles query errors much more smoothly.

Looking forward to seeing how this will help make the transition from Oracle to PostgreSQL that much easier in the future.

So one of our clients is still stuck running an old Oracle 8i database. Please, no comments on how there’s no more support and they should have moved off this long ago. We know. They know. Moving on…

The introduction of a new Oracle Foreign Data Wrapper peaked our interest. Could it possibly still work with 8i? Working with our SA team, they found that the oldest client libraries still available from Oracle are 10.2. We’re not exactly sure when Oracle dropped 8i support from their client libraries, so instead of experimenting at this time they went with known working client libraries for our currently used client which is 10.1.0.2.0 (looking at the package info). So they compiled up some packages for our Solaris environment and off I went.

With the packages installed, setting up the extension couldnt’ve been easier

CREATE EXTENSION oracle_fdw;

This was my first attempt with using Foreign Data Wrappers, so the next hour or so was spent reading the oracle_fdw docs and jumping around the postgres docs to see how it all works. We already have a connection between PostgreSQL and Oracle working with the dbi_link package, so the Oracle Client connection was already setup and working (explaining that setup is a little out of scope for this blog post). The commands to create the server, user mapping & foreign table follow…

CREATE SERVER oracle_server
FOREIGN DATA WRAPPER oracle_fdw
OPTIONS (dbserver 'ORACLE_DBNAME');

CREATE USER MAPPING FOR CURRENT_USER
SERVER oracle_server
OPTIONS (user 'oracle_user', password '######');

CREATE FOREIGN TABLE keith.fdw_test (
    userid      numeric,
    username    text,
    email       text
    ) 
SERVER oracle_server
OPTIONS ( schema 'keith', table 'fdw_test');

Then run a select and see…

pgsql=# select * from keith.fdw_test;
 userid | username |       email       
--------+----------+-------------------
      1 | keith    | keith@example.com
(1 row)

It works! This will make the (hopeful) migration off of Oracle 8i that much easier.

Could this possibly be faster than dbi_link for replicating data from Oracle to Postgres? Will be working on rewriting some of our data replication functions to use the FDW and run comparisons. I’ll share the results in a future post.