Re: Serious problem with dbimport

David Williams <djw@smooth1.demon.co.uk> wrote: >In article <65uqq4$psr@camel20.mindspring.com>, Barry Leb ><barryleb@atl.mindspring.com> writes >>I am having a serious problem with a dbimport on one and only one of >>our systems. A dbexport of a 5 gig database runs fine. However, when >>I try to dbimport back on the system, the import runs, but all >>estimates point to the import taking in excess of 72 hours to >>complete. I don't know the exact time because we can't afford to wait >>that long. I resolved the problem by loading the tables manually from >>the exported files. I would like to know what the problem is. >> >>I actually had this problem approximately 8 mos. ago, but I thought it >>was related the version of the engine. At that time it is was 6.0. >>The current version of the engine is 7.14ud5. This obviously throws >>the previous theory out the window. >> >>Here are more specifics. The database supports a PeopleSoft >>Financials application. The hardware is an HP9000 h70 with 256meg of >>memory and 2 cpus. The OS is HPUX 9.04. The disks are the HP versions >>of the 2 gig DG Clarions, and they are configred as 2 RAID 5 >>devices.(Don't ask, it wasn't my doing. It will be changed later this >>month). >> >>When the problem showed up again, I immediately tried the same >>dbimport to a second similar, but not identical, system. The second >>system was an HP k100 with 1 cpu, a disk farm with standard disks, no >>RAID or mirroring, HPUX 10.2 and and 288meg memory. This import ran >>in approximately 10 hrs. >> >>I have pretty much ruled out the RAID 5 as the problem because the >>straight load statements ran against the exported files loaded all the >>data in a reasonable amount of time(apprx 10 hrs). >> > Did this include index creation? Yes. As a side note, since this system only has one instance of Informix and is solely used for PeopleSoft, I set pdqpriority = 100 at the beginning of the .sql script. I did this on both systems. It helps with the index builds. > Remember a lot of time is spent sorting keys for index building. > Try checking what the environment variable PSORT_NPROCS is set to > or setting it = to number of cpus. Not set. > What do the following commands give? > > sar -u 1 1 > sar -d 1 1 > sar -p 1 1 > sar -w 1 1 > vmstat 1 1 > mpstat 1 1 > Try doing an onstat -z before the load then 2 hours into the load > doing Did this but did not capture to a file. > onstat -p Nothing out of the ordinary. Good cache percentages - both over 90%. Good cpu ratios. > onstat -g ath > onstat -D > onstat -g mem > onstat -g glo Did not do these, but onstat -g iov and iof showed nothing peculiar. > We need more information.. I realize the info I provided is limited. Unfortunately, when you have to have the system online by a certain deadline, you don't have a lot of time for experimentation(most ot the time is spent scrambling for fall-back plans to load the data). Unfortunately, I can't reproduce the problem on any other system, and I can't take down the production system to test further. I can provide the following additional information as I remember it. I would run an onstat -ur on both systems. One significant difference was the nwrites for the dbimport sessions. On the good performing system, the nwrites would increment by anywhere from 200 to 1000 every 5 sec. On the poor performance system, it would increment by approximately 20 to 25. Also when I switched to loading the data manually through a load command, one table at a time, the nwrite numbers on the poor system jumped those seen on the good performing system during the dbimport. As a matter of fact, they looked almost identical. > >>It would seem that the problem is somewhere in the OS. However, I'm >>not sure where to look. I'm looking for anyone who might point me in >>the right direction. >> Barry Leb National Linen Service 1420 Peachtree Street MS #314 Atlanta, GA 30309 (404) 853-6119 (404) 853-6485 fax e-mail: barryleb@mindspring.com