Unloading blobs to NFS-mounted disk

Posted in 2015

Topics: Migration, Import/Export & Data Conversion

SNORRI BERGMANN — Thu, 19 Nov 2015 13:12:40 — source: IIUG Forums & Mailing Lists

I was exporting a database (myexport w/External tables) to a NFS-mounted disk. We have one table in the database with roughly 3.2 million rows with one blob column, all together roughly 200GB. The export for this table took about 15 hours. Then I exported it to a local disk and it took only 43 minutes! I also tried unload to file select * from table and it was just as slow. The NFS server is fast, when copying files I achieve 100-200 MB/sec. The NFS rsize=131072 and wsize=131072. Has anyone experienced this? Is it possible that the unload is doing a lot of seeks in the blobxxxx.xxx files that are apparently dead slow over NFS? Thanks in advance, -Snorri

↪ replying to SNORRI BERGMANN

Art Kagel — Thu, 19 Nov 2015 13:29:00 — source: IIUG Forums & Mailing Lists

Snorri: I have experienced similar slow performance using NFS mounted drives for Informix work. That included exports and ontape archives. I don't know why honestly. Maybe some IBMer can join this discussion and enlighten us or let us know what NFS options might mitigate the issues. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Nov 19, 2015 at 8:12 AM, SNORRI BERGMANN <snorri@init.is> wrote: > I was exporting a database (myexport w/External tables) to a NFS-mounted > disk. > We have one table in the database with roughly 3.2 million rows with one > blob > column, all together roughly 200GB. > The export for this table took about 15 hours. Then I exported it to a > local > disk and it took only 43 minutes! > I also tried unload to file select * from table and it was just as slow. > > The NFS server is fast, when copying files I achieve 100-200 MB/sec. The > NFS > rsize=131072 and wsize=131072. > > Has anyone experienced this? Is it possible that the unload is doing a lot > of > seeks in the blobxxxx.xxx files that are apparently dead slow over NFS? > > Thanks in advance, > -Snorri > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11349ceebc0f1e0524e4bda6

↪ replying to Art Kagel

Madison Pruet — Thu, 19 Nov 2015 13:33:51 — source: IIUG Forums & Mailing Lists

blockquote, div.yahoo_quoted { margin-left: 0 !important; border-left:1px #715FFA solid !important; padding-left:1ex !important; background-color:white !important; } OS buffering? Sent from Yahoo Mail for iPad On Thursday, November 19, 2015, 7:29 AM, Art Kagel <art.kagel@gmail.com> wrote: Snorri: I have experienced similar slow performance using NFS mounted drives for Informix work. That included exports and ontape archives. I don't know why honestly. Maybe some IBMer can join this discussion and enlighten us or let us know what NFS options might mitigate the issues. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Nov 19, 2015 at 8:12 AM, SNORRI BERGMANN <snorri@init.is> wrote: > I was exporting a database (myexport w/External tables) to a NFS-mounted > disk. > We have one table in the database with roughly 3.2 million rows with one > blob > column, all together roughly 200GB. > The export for this table took about 15 hours. Then I exported it to a > local > disk and it took only 43 minutes! > I also tried unload to file select * from table and it was just as slow. > > The NFS server is fast, when copying files I achieve 100-200 MB/sec. The > NFS > rsize=131072 and wsize=131072. > > Has anyone experienced this? Is it possible that the unload is doing a lot > of > seeks in the blobxxxx.xxx files that are apparently dead slow over NFS? > > Thanks in advance, > -Snorri > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11349ceebc0f1e0524e4bda6 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to Art Kagel

SNORRI BERGMANN — Thu, 19 Nov 2015 13:35:07 — source: IIUG Forums & Mailing Lists

Thanks Art. I forgot to mention though that unloading "normal" tables was very fast, I did not see any major differences between local and NFS-mounted disks.

↪ replying to SNORRI BERGMANN

SNORRI BERGMANN — Mon, 23 Nov 2015 11:20:39 — source: IIUG Forums & Mailing Lists

This problem is known to IBM, the have APAR IC85979 regarding this. Basically, "For an unload/dbexport to a local file system the performance will be at least an order of magnitude better." Unloading using EXTERNAL TABLES is just as bad. Best Regs, -Snorri