external load via named pipes
Ray Burns tested copying 100,000 rows between databases on 12.10 using Informix external tables, comparing a disk file (26s) against connected named pipes (40s), and asked why pipes were slower. Suggestions included INSERT INTO...SELECT and checking FIFO VPs. Art Kagel suggested his dbcopy utility: it matched disk at 26s, but with the -F flag dropped to 12s, and ~9s with the target set to RAW and indexes disabled/rebuilt - adopted as the best approach. A follow-up -1831 error on three tables was traced by Kagel to a CSDK/engine array-fetch bug with LVARCHAR columns, worked around by casting LVARCHAR to CHAR via dbcopy's -s SELECT option (or plain INSERT...SELECT for small tables).
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I've been doing some testing on 12.10. I want to copy 100,000 records from one table in a database to another table in another database. I used an external table to unload and reload the data. I performed two tests that are pretty much the same thing except that in the first I used a disk file for unload/load and in the second I used two named pipes that I connected together. The process is: Create the named pipes Alter the target table to RAW Disable the indexes on the target. execute the copy re-set the target to STANDARD renable the indexes. Essentially this is the same process as documented in Chapter 10 of the Administrator guide The named pipes took 40 seconds to copy while the DISK copy took 26 seconds. I had expected there to be performance improvement with the named pipes. Has anyone had any experience external copies via named pipes and is the sort of performance I should expect?
Are you using multiple files/pipes? What I have found is that using multiple pipes actually takes longer than a single pipe. The best I have figured is that the pipe overhead is playing into that ... or our network isn't that great .. Additionally have you tried doing an insert into ... select * from ... If you will only be moving 100K rows .. this is much simpler ... Peter Logan Senior Database Administrator Phone: 616/878-8309 From: "RAY BURNS" <ray.burns@velocityglobal.co.nz> To: ids@iiug.org, Date: 07/26/2013 04:31 PM Subject: external load via named pipes [30973] Sent by: ids-bounces@iiug.org I've been doing some testing on 12.10. I want to copy 100,000 records from one table in a database to another table in another database. I used an external table to unload and reload the data. I performed two tests that are pretty much the same thing except that in the first I used a disk file for unload/load and in the second I used two named pipes that I connected together. The process is: Create the named pipes Alter the target table to RAW Disable the indexes on the target. execute the copy re-set the target to STANDARD renable the indexes. Essentially this is the same process as documented in Chapter 10 of the Administrator guide The named pipes took 40 seconds to copy while the DISK copy took 26 seconds. I had expected there to be performance improvement with the named pipes. Has anyone had any experience external copies via named pipes and is the sort of performance I should expect? ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
Just for poops and giggles, how long does it take for my dbcopy utility to move the data? My estimates would put it at just under 10seconds, but I don't have your system to test on. Art Art S. Kagel Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 Fri, Jul 26, 2013 at 4:30 PM, RAY BURNS <ray.burns@velocityglobal.co.nz>wrote: > I've been doing some testing on 12.10. I want to copy 100,000 records from > one > table in a database to another table in another database. I used an > external > table to unload and reload the data. I performed two tests that are pretty > much the same thing except that in the first I used a disk file for > unload/load and in the second I used two named pipes that I connected > together. > > The process is: > Create the named pipes > Alter the target table to RAW > Disable the indexes on the target. > execute the copy > re-set the target to STANDARD > renable the indexes. > > Essentially this is the same process as documented in Chapter 10 of the > Administrator guide > > The named pipes took 40 seconds to copy while the DISK copy took 26 > seconds. I > had expected there to be performance improvement with the named pipes. > > Has anyone had any experience external copies via named pipes and is the > sort > of performance I should expect? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11c356e2de0e1404e2705363
26 Seconds.- exactly the same as the external copies via disk.
When using multiple pipes, did you use unload/load or onpload(HPL)? I cant
remember if an intermediate file is involved with named pipes. What does
onstat -g sql show when you execute it? Also, If FIFO VP = 1 the server wontread your second pipe until it finishes reading the first one.
WIth or without -F? Should be faster with -F than without it. Thanks for the feedback. Art Art S. Kagel Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 Fri, Jul 26, 2013 at 6:14 PM, RAY BURNS <ray.burns@velocityglobal.co.nz>wrote: > 26 Seconds.- exactly the same as the external copies via disk. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e013d1cf841cc2a04e271cb50
IB that Ray said he was using external tables with pipes.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 Fri, Jul 26, 2013 at 6:15 PM, FRANK DEVELOPER
<frankcomputer@ymail.com>wrote:
> When using multiple pipes, did you use unload/load or onpload(HPL)? I cant
> remember if an intermediate file is involved with named pipes. What does
> onstat -g sql show when you execute it? Also, If FIFO VP = 1 the server> wont
> read your second pipe until it finishes reading the first one.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001a1133b7daa5907304e271cfda
I agree and understood that in his question. You can use a named pipe to unload data to an external table from one Informix instance and load it into another instance without writing data to an intermediate file. See: http://pic.dhe.ibm.com/infocenter/informix/v121/index.jsp?topic=%2Fcom.ibm.admin .doc%2Fids_admin_1285.htm
Sorry, when we say "external table" we don't mean just any table external to the database, we mean an "informix database external table". As in the SQL statement: CREATE EXTERNAL TABLE blah SAMEAS blah blah USING ( DATAFILES ("PIPE: ... )) So it's unrelated to HPL. IBM say this is the highest performing external load / unload mechansim. One ends up with two external tables defined in the database and two fifo pipes defined at the OS level, then it's a case of INSERT INTO <source external table> SELECT from <source table> followed by a INSERT INTO <target table> SELECT * from <target pipe>. You need to use two pipes because the insert statements need an exclusive lock and therefore at the os level you need to cat the two pipes together. It's reasonably well documented in Ch 10. of the admin guide. I was just a little surprised that PIPE method resulted in half the performance of the DISK method.
Gads!!! that was without -F. With -F it dropped to 12 seconds. Big difference. When I wrap this around some SQL to set mode to RAW and disable the indexes then reset the mode and enable the indexes after the copy I drop to 8 seconds for the load and one more second for the indexing - 9 seconds. So your estimate of 10 seconds was pretty good!! Looks like this is the best approach. Thanks again. Ray
I didn't interpret it as an non-informix external file. Can you test unloading the 100K rows to disk, then loading the unl file to the target db with HPL? this may be the fastest method vs. using named pipes, which seems to have additional overhead in the engine (i.e. logical logs, locks, etc.)
I have an old DOS-based SE 4.10 app where users periodically "reorg" the pawns
table (~330K nrows, 927 rowsize). Pawns older than 5 years are archived to
another table.
My sql script:
UNLOAD TO "U:\\\\OLDPAWNS.UNL"
SELECT * FROM pawns
WHERE (TODAY - PawnDate) >= 1725
ORDER BY PawnDate DESC;
UNLOAD TO "U:\\\\PAWNS.UNL"
SELECT * FROM pawns
WHERE (TODAY - PawnDate) < 1725
ORDER BY CustomerID;
LOAD FROM "U:\\\\OLDPAWNS.UNL" INSERT INTO oldpawns;
DROP/CREATE TABLE pawns;
LOAD FROM "U:\\\\PAWNS.UNL" INSERT INTO pawns;
CREATE pawns INDEXES;
UPDATE STATS;
It does all this in less than 24 seconds and keeps the app humming smoothly.
If named pipes were available for SE 4.10, I'm sure that doing it with named
pipes would take a lot longer!
Yup. For reasonable row sizes (between about 100 and 500 bytes) dbcopy averages over 10,000 rows per second. And that's with a single copy of dbcopy running. If you can divide up the data using a primary key - or better a partition expression - and run multiple copies of dbcopy in parallel it scales up fairly well. Not linear, but darn close. So you could get say 18,000 rows per second from two copies, etc. Art Art S. Kagel Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 Fri, Jul 26, 2013 at 8:23 PM, RAY BURNS <ray.burns@velocityglobal.co.nz>wrote: > Gads!!! that was without -F. With -F it dropped to 12 seconds. Big > difference. > > When I wrap this around some SQL to set mode to RAW and disable the indexes > then reset the mode and enable the indexes after the copy I drop to 8 > seconds > for the load and one more second for the indexing - 9 seconds. > > So your estimate of 10 seconds was pretty good!! > > Looks like this is the best approach. > > Thanks again. > > Ray > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e0141a9ea510be604e29183ad
I changed some scripts and copied 496 tables from my database and all but three tables went swimingly. for each execution I used the -F -S -E 1 flags (as well as -d -D -t). I redirected the stderr to a file. For the three failing tables stderr file each reported the same error -1831. Finderr showed this as "Combination of FetArrSize, Deferred-PREPARE, and OPTOFC is not supported" I cannot see anything particularly different about these three tables as opposed to the other 493. Before I go drowning in code, are you able to shed any light on where I might begin?
Disable OPTTOFC - AFAIR set it to zero but no manual handy Cheers Paul > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of RAY > BURNS > Sent: Sunday, July 28, 2013 9:05 PM > To: ids@iiug.org > Subject: Re: external load via named pipes [30996] > > I changed some scripts and copied 496 tables from my database and all but > three tables went swimingly. for each execution I used the -F -S -E 1 flags > (as well as -d -D -t). I redirected the stderr to a file. > > For the three failing tables stderr file each reported the same error -1831. > Finderr showed this as "Combination of FetArrSize, Deferred-PREPARE, and > OPTOFC is not supported" > > I cannot see anything particularly different about these three tables as > opposed to the other 493. > > Before I go drowning in code, are you able to shed any light on where I might > begin? > > > ********************************************************** > ********************* > Forum Note: Use "Reply" to post a response in the discussion forum.
Ahh, you have hit two related bugs. One in the CSDK and one in the engine that I discovered about six months ago, not fixed by IBM yet. It involves using the array fetch feature with LVARCHAR columns. The fix is to cast the lvarchar's to CHAR using the -s "SELECT ..." feature in dbcopy. Until IBM fixes these two bugs this is the only workaround. The only downside of doing this is that the target table's lvarchars will be padded with trailing spaces to maximum length. You could trim them afterwards with an update but risk trimming off explicit spaces that were inserted into the original source table rows. If the lvarchar tables are small, you can just INSERT INTO ... SELECT instead of using dbcopy. That will avoid the trailing space problem. If they are large, then the workaround is the only way. The bugs first appear in one of the v11.50 engines with the corresponding CSDK v3.50 release that first supports wider buffers. Art Art S. Kagel Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 Sun, Jul 28, 2013 at 10:04 PM, RAY BURNS <ray.burns@velocityglobal.co.nz>wrote: > I changed some scripts and copied 496 tables from my database and all but > three tables went swimingly. for each execution I used the -F -S -E 1 flags > (as well as -d -D -t). I redirected the stderr to a file. > > For the three failing tables stderr file each reported the same error > -1831. > Finderr showed this as "Combination of FetArrSize, Deferred-PREPARE, and > OPTOFC is not supported" > > I cannot see anything particularly different about these three tables as > opposed to the other 493. > > Before I go drowning in code, are you able to shed any light on where I > might > begin? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a1133b7daed13c904e29f51a2
Thanks for your suggestion. I had it unset so it should default to zero according to the manual (SQL reference chapter 3). So I tried with both 0 and 1 and neither made any difference.