RE: Slow loading/lots of checkpoints

You could greatly speed the load process by loading with logging: 1) Change the database to non-logging mode (ondblog nolog <db-name>) 2) Force a checkpoint: (onmode -c) 2) Load your data 3) Create all indexes and constraints 4) Change the database to a logging mode: (ondblog unbuf <db-name>) 5) Take a level 0 backup. The following ONCONFIG changes can provide better throughput during the load process even if you have not disabled logging first: 1) Change LRU_MAX_DIRTY from 20 to 80 2) Change LUR_MIN_DIRTY from 5 to 70 3) Change LURS from 4 to 32 4) Change CLEANERS from 3 to 32 (s/b >= LRUS) 5) Change CKPTINTVL from 600 to 1800 6) CHANGE NUMAOIVPS from 1 to 2 In either case you should disable indexes and constraints before loading your data. For example: BEGIN WORK; SET CONSTRAINTS, INDEXES FOR TABLE x DISABLED; LOCK TABLE x in EXCLUSIVE MODE; load from x1.out insert into x; SET CONSTRAINTS, INDEXES FOR TABLE x ENABLED; COMMIT WORK; A larger number of BUFFERS would also help, provided that it does cause an increase in system paging or swapping. On a side issue, the LOAD command will load your data as one unit of work. Unless you place an exclusive lock on the target table before issuing the LOAD command, you could run out of LOCKS. If you generate a checkpoint (onmode -c) immediately before loading data, physical logging will be miminized. Good luck, Rick -----Original Message----- From: SPasco@unibiz.com To: informix-list@iiug.org Sent: 6/8/00 3:51 PM Subject: Slow loading/lots of checkpoints Hi all, I am using IDS Workgroup 7.30 TC3 for Windows NT 4, SP3. I had unloaded about 5 million rows from a table on one server and am attempting to load it into an identical table on another server. The server I am loading the data into is a small IBM Netfinity 3000 350MHz processor with 192M RAM. There is adequate hdd space but I am having the following problem: I am loading the data via dbaccess with this command 'load from x1.out insert into x' where 'x1.out' is the unloaded file and 'x' is the name of the table. It seems as though data will load for a few seconds and then everything stops while a checkpoint is performed. My checkpoints are taking about 25-30 seconds. I have 7 logical logs that are 25M each. Is there anyway to setup the onconfig file to work on this meager machine? ONCONFIG file is attached. (See attached file: ONCONFIG.ol_ubs_nt_server) TIA SMP <<ONCONFIG.ol_ubs_nt_server>>

Use dbload utility, it's more efficient than LOAD statement and follow step tell you Rick In article <8hpenh$5bl$1@news.xmission.com>, "Bernstein, Rick" <rbernste@alarismed.com> wrote: > > You could greatly speed the load process by loading with logging: > 1) Change the database to non-logging mode (ondblog nolog <db-name>) > 2) Force a checkpoint: (onmode -c) > 2) Load your data > 3) Create all indexes and constraints > 4) Change the database to a logging mode: (ondblog unbuf <db-name>) > 5) Take a level 0 backup. > > The following ONCONFIG changes can provide better throughput during the > load process even if you have not disabled logging first: > 1) Change LRU_MAX_DIRTY from 20 to 80 > 2) Change LUR_MIN_DIRTY from 5 to 70 > 3) Change LURS from 4 to 32 > 4) Change CLEANERS from 3 to 32 (s/b >= LRUS) > 5) Change CKPTINTVL from 600 to 1800 > 6) CHANGE NUMAOIVPS from 1 to 2 > > In either case you should disable indexes and constraints before loading > your data. For example: > > BEGIN WORK; > SET CONSTRAINTS, INDEXES FOR TABLE x DISABLED; > LOCK TABLE x in EXCLUSIVE MODE; > load from x1.out insert into x;> SET CONSTRAINTS, INDEXES FOR TABLE x ENABLED; > COMMIT WORK; > > A larger number of BUFFERS would also help, provided that it does cause > an increase in system paging or swapping. > > On a side issue, the LOAD command will load your data as one unit of work. > Unless you place an exclusive lock on the target table before issuing > the LOAD command, you could run out of LOCKS. If you generate a > checkpoint (onmode -c) immediately before loading data, physical logging > will be miminized. > > Good luck, > Rick > > -----Original Message----- > From: SPasco@unibiz.com > To: informix-list@iiug.org > Sent: 6/8/00 3:51 PM > Subject: Slow loading/lots of checkpoints > > Hi all, > > I am using IDS Workgroup 7.30 TC3 for Windows NT 4, SP3. > I had unloaded about 5 million rows from a table on one server > and am attempting to load it into an identical table on another server. > > The server I am loading the data into is a small IBM Netfinity 3000 > 350MHz processor with 192M RAM. There is adequate hdd space > but I am having the following problem: > > I am loading the data via dbaccess with this command > 'load from x1.out insert into x' > where 'x1.out' is the unloaded file and 'x' is the name of the table. > It seems as though data will load for a few seconds and then > everything stops while a checkpoint is performed. My checkpoints > are taking about 25-30 seconds. > > I have 7 logical logs that are 25M each. > > Is there anyway to setup the onconfig file to work on this meager > machine? > ONCONFIG file is attached. > > (See attached file: ONCONFIG.ol_ubs_nt_server) > > TIA > SMP > <<ONCONFIG.ol_ubs_nt_server>> > Sent via Deja.com http://www.deja.com/ Before you buy.