RE: De-fragmenting a table

I have found that large buffer pools and chunk writes speed my database maintenance activities. When I perform similar maintenance activities, I run with a different $ONCONFIG file. In it I make several modifications, including: * BUFFERS modified from 150000 to 290000. (4K page size on AIX) * SHMVIRTSIZE modified from 786432 to 262144. * LRU_MAX_DIRTY modified from 2 to 90. * LRU_MIN_DIRTY modified from 1 to 75. My BUFFERS are set as high as possible, but so high as to cause excessive paging or swapping. Additionally, I run a shell script in the background. It issues an onmode -c before the percent of dirty pages reaches LRU_MAX_DIRTY.This methodology prevent LRU writes and forces chunk writes. You should also set PDQPRIORITY and PSORT_NPROCS in order to speed the recreation of indexes. -----Original Message----- From: Neil Truby [mailto:ntruby@netcomuk.co.uk] Sent: Thursday, May 18, 2000 13:09 To: informix-list@iiug.org Subject: De-fragmenting a table IDS v7.24 on HP-UX 10.20. I've got a 13GByte, 12 million row table in 175 extents and I need to defrag it. Urgently! I ran an ALTER TABLE FRAGMENT INIT last night and, fourteen hours later it still hadn't finished. this was with logging temporarily turned off for the database. I only have a 12 hour downtime wondow. Any other ideas? I'm testing a High perfoamnce load but, although therows take only 20 minutes or so, the indexes are going to be damned slow. thanks Neil

In article <8g1q4m$kh1$1@news.xmission.com>, Bernstein, Rick <rbernste@alarismed.com> writes > >I have found that large buffer pools and chunk writes speed my >database maintenance activities. > >When I perform similar maintenance activities, I run with a different >$ONCONFIG file. In it I make several modifications, including: >* BUFFERS modified from 150000 to 290000. (4K page size on AIX) >* SHMVIRTSIZE modified from 786432 to 262144. ^^^^^^^^^^^ ^^^^^^^^^^^^^^^^^ >* LRU_MAX_DIRTY modified from 2 to 90. >* LRU_MIN_DIRTY modified from 1 to 75. >My BUFFERS are set as high as possible, but so high as to cause >excessive paging or swapping. >Additionally, I run a shell script in the background. It issues an >onmode -c before the percent of dirty pages reaches LRU_MAX_DIRTY.>This methodology prevent LRU writes and forces chunk writes. > >You should also set PDQPRIORITY and PSORT_NPROCS in order to speed >the recreation of indexes. > > >-----Original Message----- >From: Neil Truby [mailto:ntruby@netcomuk.co.uk] >Sent: Thursday, May 18, 2000 13:09 >To: informix-list@iiug.org >Subject: De-fragmenting a table > > >IDS v7.24 on HP-UX 10.20. ^^^^^ Check the online.log did Informix allocates extra shared memory segments? HP-UX has a problem where allocating > 3 shared memory segments (1 resident, 1 message and 1 virtual) wiil slow it down. > >I've got a 13GByte, 12 million row table in 175 extents and I need to defrag >it. Urgently! > >I ran an ALTER TABLE FRAGMENT INIT last night and, fourteen hours later it >still hadn't finished. this was with logging temporarily turned off for the >database. I only have a 12 hour downtime wondow. > >Any other ideas? I'm testing a High perfoamnce load but, although therows >take only 20 minutes or so, the indexes are going to be damned slow. > Drop indexes HPL the data Add indexes how many CPUS do you have? how much memory? Use PDQPRIORITY 100 PSORT_NPROCS = Number of CPUS. On Suns 2x number of CPUS is quicker not sure about HP machines. This may help. >thanks >Neil > > -- David Williams