onload consuming shared memory

Hi all, we're just about to start a full database restore to recover from a failed onload session. I just want to try and determine what might have gone wrong to see if how we can do it better next time. We want to onload a table with blob data of about 300000 rows and 11GB of onload files size. The onload ran for some three hours with an estimated running time of about 4.5 hours. All looked just fine, except maybe that we had thought it would be a little faster than that. Then the load stopped with "Not enough core" as error message from onload. The online log complains on ENOMEM when trying to allocate another virtual segment. I looked at onstat -g seg and it had allocated some 30 extra segments. Interestingly enough, the allocation of new segments came mostly from the last half hour, where it allocated a new segment about once every minute. Even more interesting, each segment was a little larger than the previous one (it started on 16MB segments and ended with sizes almost up at 20MB...). This system is on 9.21.HC7X3 on an HP-UX 11. I've got a couple of questions that you may be able to answer: (a) Why does onload need this memory? Can we avoid it somehow? (b) If we cannot avoid, can we predict how much it might need? I mean, even if we increase the size of each allocated segment to lower the number of segments, we still need to know that we will stay within the total limits of the machine (OS). (c) Anyone seen virtual segments increasing in size as more of them are allocated...looks like kind of buggy behaviour to me? Well, gotta go back to the team and check that they are on track on the full restore. thanks for listening. Johan __________________________________ Do you Yahoo!? Yahoo! Mail Address AutoComplete - You start. We finish. http://promotions.yahoo.com/new_mail

Johan, Are you building indexes as you load the data? As more and more rows get filled, it takes more and more memory to do the sorting needed to create the indexes. I recommend you build all indexes after the load, a few at a time. Also, if you know your data (like from a previous Db dump) you can consider disabeling constraints. Hope this helps some. Jamie "Johan Backlund " <jbacklund@yahoo.com> Sent by: forum.subscriber@iiug.org 06/20/2004 08:58 AM To: ids@iiug.org cc: Subject: onload consuming shared memory [3138] Hi all, we're just about to start a full database restore to recover from a failed onload session. I just want to try and determine what might have gone wrong to see if how we can do it better next time. We want to onload a table with blob data of about 300000 rows and 11GB of onload files size. The onload ran for some three hours with an estimated running time of about 4.5 hours. All looked just fine, except maybe that we had thought it would be a little faster than that. Then the load stopped with "Not enough core" as error message from onload. The online log complains on ENOMEM when trying to allocate another virtual segment. I looked at onstat -g seg and it had allocated some 30 extra segments. Interestingly enough, the allocation of new segments came mostly from the last half hour, where it allocated a new segment about once every minute. Even more interesting, each segment was a little larger than the previous one (it started on 16MB segments and ended with sizes almost up at 20MB...). This system is on 9.21.HC7X3 on an HP-UX 11. I've got a couple of questions that you may be able to answer: (a) Why does onload need this memory? Can we avoid it somehow? (b) If we cannot avoid, can we predict how much it might need? I mean, even if we increase the size of each allocated segment to lower the number of segments, we still need to know that we will stay within the total limits of the machine (OS). (c) Anyone seen virtual segments increasing in size as more of them are allocated...looks like kind of buggy behaviour to me? Well, gotta go back to the team and check that they are on track on the full restore. thanks for listening. Johan __________________________________ Do you Yahoo!? Yahoo! Mail Address AutoComplete - You start. We finish. http://promotions.yahoo.com/new_mail

Johan, One more thing. Contiguous memory is more efficient than fragmented memory. Consider increasing SHMVIRTSIZE and SHMADD for the load. They can always be put back to normal operating sizes after the load. When Informix initializes, it will attempt to grab SHMVIRTSIZE in one contiguous block. Also, if additional segments are needed, it is better to grab a large segment rather than many small segments. Also, see my previous msg about delaying index building until after the load. Your "net net" will be much better that way. Your load will zip through much faster without the indexes being updated as each row is added. Jamie "Johan Backlund " <jbacklund@yahoo.com> Sent by: forum.subscriber@iiug.org 06/20/2004 08:58 AM To: ids@iiug.org cc: Subject: onload consuming shared memory [3138] Hi all, we're just about to start a full database restore to recover from a failed onload session. I just want to try and determine what might have gone wrong to see if how we can do it better next time. We want to onload a table with blob data of about 300000 rows and 11GB of onload files size. The onload ran for some three hours with an estimated running time of about 4.5 hours. All looked just fine, except maybe that we had thought it would be a little faster than that. Then the load stopped with "Not enough core" as error message from onload. The online log complains on ENOMEM when trying to allocate another virtual segment. I looked at onstat -g seg and it had allocated some 30 extra segments. Interestingly enough, the allocation of new segments came mostly from the last half hour, where it allocated a new segment about once every minute. Even more interesting, each segment was a little larger than the previous one (it started on 16MB segments and ended with sizes almost up at 20MB...). This system is on 9.21.HC7X3 on an HP-UX 11. I've got a couple of questions that you may be able to answer: (a) Why does onload need this memory? Can we avoid it somehow? (b) If we cannot avoid, can we predict how much it might need? I mean, even if we increase the size of each allocated segment to lower the number of segments, we still need to know that we will stay within the total limits of the machine (OS). (c) Anyone seen virtual segments increasing in size as more of them are allocated...looks like kind of buggy behaviour to me? Well, gotta go back to the team and check that they are on track on the full restore. thanks for listening. Johan __________________________________ Do you Yahoo!? Yahoo! Mail Address AutoComplete - You start. We finish. http://promotions.yahoo.com/new_mail

I believe onload is simply laying down binary pages. It shouldn't care about indexes being there or not. If you are doing a table level onunload/onload you shouldn't get any triggers or constraints either - at least according to what I've read in the manual. Rob Schmitz -----Original Message----- From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org]On Behalf Of JHAYS2@sears.com Sent: Monday, June 21, 2004 9:30 AM To: ids@iiug.org Subject: Re: onload consuming shared memory [3139] Johan, Are you building indexes as you load the data? As more and more rows get filled, it takes more and more memory to do the sorting needed to create the indexes. I recommend you build all indexes after the load, a few at a time. Also, if you know your data (like from a previous Db dump) you can consider disabeling constraints. Hope this helps some. Jamie "Johan Backlund " <jbacklund@yahoo.com> Sent by: forum.subscriber@iiug.org 06/20/2004 08:58 AM To: ids@iiug.org cc: Subject: onload consuming shared memory [3138] Hi all, we're just about to start a full database restore to recover from a failed onload session. I just want to try and determine what might have gone wrong to see if how we can do it better next time. We want to onload a table with blob data of about 300000 rows and 11GB of onload files size. The onload ran for some three hours with an estimated running time of about 4.5 hours. All looked just fine, except maybe that we had thought it would be a little faster than that. Then the load stopped with "Not enough core" as error message from onload. The online log complains on ENOMEM when trying to allocate another virtual segment. I looked at onstat -g seg and it had allocated some 30 extra segments. Interestingly enough, the allocation of new segments came mostly from the last half hour, where it allocated a new segment about once every minute. Even more interesting, each segment was a little larger than the previous one (it started on 16MB segments and ended with sizes almost up at 20MB...). This system is on 9.21.HC7X3 on an HP-UX 11. I've got a couple of questions that you may be able to answer: (a) Why does onload need this memory? Can we avoid it somehow? (b) If we cannot avoid, can we predict how much it might need? I mean, even if we increase the size of each allocated segment to lower the number of segments, we still need to know that we will stay within the total limits of the machine (OS). (c) Anyone seen virtual segments increasing in size as more of them are allocated...looks like kind of buggy behaviour to me? Well, gotta go back to the team and check that they are on track on the full restore. thanks for listening. Johan __________________________________ Do you Yahoo!? Yahoo! Mail Address AutoComplete - You start. We finish. http://promotions.yahoo.com/new_mail

> -----Original Message----- > From: JHAYS2@sears.com [mailto:JHAYS2@sears.com] > Subject: Re: onload consuming shared memory [3140] > > One more thing. Contiguous memory is more efficient than fragmented > memory. Not true for database servers on multi-CPU machines Alexey

Thanks for the responses I have received. However, I want to emphasize that the primary problem is the fact that onload causes IDS to allocate huge amounts of shared memory - as I said, we were seeing 16-20MB being allocated every minute in the last half hour. Therefore, changing the shared memory allocation parameters might improve the situation for a short time, but if IDS continues to allocate 20MB per minute during the last 90 minutes of the load, we will hit the total shared memory limit for a 32-bit application on HP-UX 11 including all tweaking. I would like to know what the memory is to be used for, so that we can predict the amount of shared memory we will need. The speed of the load is ok if it comes in under 5 hours, even if it would be great to cut it down. Correct me if I am wrong, but since we are using onload, I am assuming that indes are created after load automagically? I can't see how indexes could be updated row by row when data is loaded page by page in binary format. We have not had the time to do another test. We have verified that the data is ok with oncheck, just to get that out of the equation. As I see it, we will probably resort to using dbimport instead, and take the hit of having a very long system down in favour of getting our migration done. Again, thanks for you responses. /Johan --- JHAYS2@sears.com wrote: > Johan, > > One more thing. Contiguous memory is more efficient than fragmented > memory. Consider increasing SHMVIRTSIZE and SHMADD for the load. > They > can always be put back to normal operating sizes after the load. > When > Informix initializes, it will attempt to grab SHMVIRTSIZE in one > contiguous block. Also, if additional segments are needed, it is > better > to grab a large segment rather than many small segments. > > Also, see my previous msg about delaying index building until after > the > load. Your "net net" will be much better that way. Your load will > zip > through much faster without the indexes being updated as each row is > added. > > Jamie > > > > > "Johan Backlund " <jbacklund@yahoo.com> > Sent by: forum.subscriber@iiug.org > 06/20/2004 08:58 AM > > > To: ids@iiug.org > cc: > Subject: onload consuming shared memory [3138] > > > Hi all, > > we're just about to start a full database restore to recover from a > failed onload session. I just want to try and determine what might > have > gone wrong to see if how we can do it better next time. > > We want to onload a table with blob data of about 300000 rows and > 11GB > of onload files size. The onload ran for some three hours with an > estimated running time of about 4.5 hours. All looked just fine, > except > maybe that we had thought it would be a little faster than that. > > Then the load stopped with "Not enough core" as error message from > onload. The online log complains on ENOMEM when trying to allocate > another virtual segment. I looked at onstat -g seg and it had > allocated > some 30 extra segments. Interestingly enough, the allocation of new > segments came mostly from the last half hour, where it allocated a > new > segment about once every minute. Even more interesting, each segment > was a little larger than the previous one (it started on 16MB > segments > and ended with sizes almost up at 20MB...). > > This system is on 9.21.HC7X3 on an HP-UX 11. > > I've got a couple of questions that you may be able to answer: > > (a) Why does onload need this memory? Can we avoid it somehow? > > (b) If we cannot avoid, can we predict how much it might need? I > mean, > even if we increase the size of each allocated segment to lower the > number of segments, we still need to know that we will stay within > the > total limits of the machine (OS). > > (c) Anyone seen virtual segments increasing in size as more of them > are > allocated...looks like kind of buggy behaviour to me? > > Well, gotta go back to the team and check that they are on track on > the > full restore. > > thanks for listening. > > Johan > > > > > __________________________________ > Do you Yahoo!? > Yahoo! Mail Address AutoComplete - You start. We finish. > http://promotions.yahoo.com/new_mail > > > > __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com

Johan, For what it's worth, if you're considering dbexport, I'd consider writing a script to do an HPL unload/load. HPL is considerably faster than dbexport/dbimport (it's on par with onload/onunload in my experience), and you shouldn't have the memory allocation issues that you had with onload/onunload (though to be honest, I don't know why you did have those memory allocations; I've not used those tools much). There are two outputs you can get from an HPL unload; one is an ASCII file (somewhat slow, but still orders of magnitude faster than dbexport), and the other is a "proprietary" Informix file type (not quite binary, but not easily readable). The former is produced by a "standard" unload, the latter by a "no-convert" unload. The easiest way (in the 9.3 and below engines) to set up one of these unload/load jobs is through the GUI (ipload -n from an xterm window will get that running for you). Make sure that you set your INFORMIXSERVER to a value that connects TCP/IP; if you don't, you'll get an error that doesn't really tell you what the problem is, but the HPL GUI doesn't like trying to connect through shared memory. Walking through the setup is pretty straightforward. The manual is actually relatively good for this one, but if you have problems, feel free to write me back, and I can work with you on setting this up. Process should be: 1. Unload data 2. Verify the unloads (hard to do with the proprietary format; check the /tmp/<unload_job_name>.log file for row counts out 3. Drop the table 4. Rebuild the table (not the indexes) 5. Load the table 6. Rebuild indexes and constraints Couple of caveats; I've not had ANY luck at all with HPL and BLOB/CLOB/TEXT columns, and the documentation might actually specify that those column types make a table ineligible for HPL. I once had some problems with HPL complaining about not being able to allocate memory; there's a variable (I think it's PLSHMBASE, but I can't remember off the top of my head; it's in the documentation on HPL) that you can set that will help alleviate that problem. HPL WILL allocate memory, but not on the order of what you've been seeing. HPL is one of the most often overlooked gems in the Informix suite; it really is a useful tool that people just get intimidated by. Once you get used to it, though, it makes life for a DBA much easier... and the speed of the utility will give you something to brag about when you talk to your Oracle/SQL-Server/DB2 counterparts... <G> Good luck; let us know how it goes. Dan Michaelis -----Original Message----- From: Johan Backlund [mailto:jbacklund@yahoo.com] Sent: Thursday, June 24, 2004 4:15 PM To: ids@iiug.org Subject: Re: onload consuming shared memory [3154] Thanks for the responses I have received. However, I want to emphasize that the primary problem is the fact that onload causes IDS to allocate huge amounts of shared memory - as I said, we were seeing 16-20MB being allocated every minute in the last half hour. Therefore, changing the shared memory allocation parameters might improve the situation for a short time, but if IDS continues to allocate 20MB per minute during the last 90 minutes of the load, we will hit the total shared memory limit for a 32-bit application on HP-UX 11 including all tweaking. I would like to know what the memory is to be used for, so that we can predict the amount of shared memory we will need. The speed of the load is ok if it comes in under 5 hours, even if it would be great to cut it down. Correct me if I am wrong, but since we are using onload, I am assuming that indes are created after load automagically? I can't see how indexes could be updated row by row when data is loaded page by page in binary format. We have not had the time to do another test. We have verified that the data is ok with oncheck, just to get that out of the equation. As I see it, we will probably resort to using dbimport instead, and take the hit of having a very long system down in favour of getting our migration done. Again, thanks for you responses. /Johan --- JHAYS2@sears.com wrote: > Johan, > > One more thing. Contiguous memory is more efficient than fragmented > memory. Consider increasing SHMVIRTSIZE and SHMADD for the load. > They > can always be put back to normal operating sizes after the load. > When > Informix initializes, it will attempt to grab SHMVIRTSIZE in one > contiguous block. Also, if additional segments are needed, it is > better > to grab a large segment rather than many small segments. > > Also, see my previous msg about delaying index building until after > the > load. Your "net net" will be much better that way. Your load will > zip > through much faster without the indexes being updated as each row is > added. > > Jamie > > > > > "Johan Backlund " <jbacklund@yahoo.com> > Sent by: forum.subscriber@iiug.org > 06/20/2004 08:58 AM > > > To: ids@iiug.org > cc: > Subject: onload consuming shared memory [3138] > > > Hi all, > > we're just about to start a full database restore to recover from a > failed onload session. I just want to try and determine what might > have > gone wrong to see if how we can do it better next time. > > We want to onload a table with blob data of about 300000 rows and > 11GB > of onload files size. The onload ran for some three hours with an > estimated running time of about 4.5 hours. All looked just fine, > except > maybe that we had thought it would be a little faster than that. > > Then the load stopped with "Not enough core" as error message from > onload. The online log complains on ENOMEM when trying to allocate > another virtual segment. I looked at onstat -g seg and it had > allocated > some 30 extra segments. Interestingly enough, the allocation of new > segments came mostly from the last half hour, where it allocated a > new > segment about once every minute. Even more interesting, each segment > was a little larger than the previous one (it started on 16MB > segments > and ended with sizes almost up at 20MB...). > > This system is on 9.21.HC7X3 on an HP-UX 11. > > I've got a couple of questions that you may be able to answer: > > (a) Why does onload need this memory? Can we avoid it somehow? > > (b) If we cannot avoid, can we predict how much it might need? I > mean, > even if we increase the size of each allocated segment to lower the > number of segments, we still need to know that we will stay within > the > total limits of the machine (OS). > > (c) Anyone seen virtual segments increasing in size as more of them > are > allocated...looks like kind of buggy behaviour to me? > > Well, gotta go back to the team and check that they are on track on > the > full restore. > > thanks for listening. > > Johan > > > > > __________________________________ > Do you Yahoo!? > Yahoo! Mail Address AutoComplete - You start. We finish. > http://promotions.yahoo.com/new_mail > > > > ________________________________________