Re: De-fragmenting a table
What values do you recommend for following parameters to speed up Index builds : psort_nprocs pdqpriority ds_max_queries ds_max_scans ds_total_memory System details : 5 CPUs, HP UX 10.20, Informix 7.24, 20 GB Database, 2 GB table size, Row size 197, No. of columns 24, 4 indexes (1 unique), 20 HDDs, RAID 5 Vinod Bhansali >From: Rudy Fernandes <rferdy@americasm01.nt.com> >Reply-To: Rudy Fernandes <rferdy@americasm01.nt.com> >To: informix-list@iiug.org >Subject: Re: De-fragmenting a table >Date: Thu, 18 May 2000 17:32:33 -0400 > >You could consider the following : > >1. INIT into a new dbspace on different disk(s). This will allow efficient >reads and writes during the ALTER FRAGMENT. > >2. Consider disabling indexes before the INIT. Then enable them one at a >time >in descending order of priority. This will, in effect, break down a single >monolithic task into multiple tasks. This can be particularly useful if >there >are some indexes that you can, temporarily, do without (e.g one set of >Business >Analysts can do without querying). > >3. Detach indexes into their own disk to speed up the index build process - >which mean, drop indexes->alter frag->rebuild index in their own dbspace. > >4. PSORT_NPROCS, PDQPRIORITY settings for the Index builds. > >5. Unique Indexes/Primary Key (A bit of heresy). Save (a lot of!) time by >changing them to non-unique during this run. When you next get a window, >revert >them back to unique. > >6. Grab this opportunity to suggest archiving of "old" data :-) [getting >desperate now!] > >Rudy > >Neil Truby wrote: > > > 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 > ________________________________________________________________________ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com
Vinod Bhansali wrote: > > What values do you recommend for following parameters to speed up Index > builds : Please understand . . . . this is for my environment . . . . your mileage may vary. PDQPRIORITY=40; MEM=100M;ACTIVE=4; SCANS=20 > psort_nprocs 4 > pdqpriority 40 > ds_max_queries 4 > ds_max_scans 20 > ds_total_memory 100M > > System details : > 5 CPUs, HP UX 10.20, Informix 7.24, 20 GB Database, 2 GB table size, Row > size 197, No. of columns 24, 4 indexes (1 unique), 20 HDDs, RAID 5 > > Vinod Bhansali > > >From: Rudy Fernandes <rferdy@americasm01.nt.com> > >Reply-To: Rudy Fernandes <rferdy@americasm01.nt.com> > >To: informix-list@iiug.org > >Subject: Re: De-fragmenting a table > >Date: Thu, 18 May 2000 17:32:33 -0400 > > > >You could consider the following : > > > >1. INIT into a new dbspace on different disk(s). This will allow efficient > >reads and writes during the ALTER FRAGMENT. > > > >2. Consider disabling indexes before the INIT. Then enable them one at a > >time > >in descending order of priority. This will, in effect, break down a single > >monolithic task into multiple tasks. This can be particularly useful if > >there > >are some indexes that you can, temporarily, do without (e.g one set of > >Business > >Analysts can do without querying). > > > >3. Detach indexes into their own disk to speed up the index build process - > >which mean, drop indexes->alter frag->rebuild index in their own dbspace. > > > >4. PSORT_NPROCS, PDQPRIORITY settings for the Index builds. > > > >5. Unique Indexes/Primary Key (A bit of heresy). Save (a lot of!) time by > >changing them to non-unique during this run. When you next get a window, > >revert > >them back to unique. > > > >6. Grab this opportunity to suggest archiving of "old" data :-) [getting > >desperate now!] > > > >Rudy > > > >Neil Truby wrote: > > > > > 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 > > > > ________________________________________________________________________ > Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com -- John Carlson Informix DBA WHSmith USA #include std_disclaimer.h /* These are my opinions, not my company's opinion */
Vinod Bhansali wrote: > > What values do you recommend for following parameters to speed up Index > builds : > psort_nprocs PSORT_NPROCS=15 >40 PSORT_DBTEMP=<list of AT LEAST 3 independent filesystems> > pdqpriority PDQPRIORITY=50 >100 On HP Make sure SHMVIRTSIZE is large enough to hold the sortwork memory space so no additional virtual segments are created! > ds_max_queries > ds_max_scans > ds_total_memory > > System details : > 5 CPUs, HP UX 10.20, Informix 7.24, 20 GB Database, 2 GB table size, Row > size 197, No. of columns 24, 4 indexes (1 unique), 20 HDDs, RAID 5 NO RAID5 NO RAID5 NO RAID5 NO RAID5 NO RAID5 NO RAID5 NO RAID5 RAID5 is slowing down you writes by as much as 50%! Get 15 more hard drives and reconfigure to RAID10, approx cost $15K-30K, cost of DBA nervous breakdown from trying to solve near impossible problems daily? $$$$$$$$! Not to mention the cost of data lost to RAID5's vaporous safety someday! WHERE'S the magical savings from RAID5 over RAID10? NO WHERE! Art S. Kagel > Vinod Bhansali > > >From: Rudy Fernandes <rferdy@americasm01.nt.com> > >Reply-To: Rudy Fernandes <rferdy@americasm01.nt.com> > >To: informix-list@iiug.org > >Subject: Re: De-fragmenting a table > >Date: Thu, 18 May 2000 17:32:33 -0400 > > > >You could consider the following : > > > >1. INIT into a new dbspace on different disk(s). This will allow efficient > >reads and writes during the ALTER FRAGMENT. > > > >2. Consider disabling indexes before the INIT. Then enable them one at a > >time > >in descending order of priority. This will, in effect, break down a single > >monolithic task into multiple tasks. This can be particularly useful if > >there > >are some indexes that you can, temporarily, do without (e.g one set of > >Business > >Analysts can do without querying). > > > >3. Detach indexes into their own disk to speed up the index build process - > >which mean, drop indexes->alter frag->rebuild index in their own dbspace. > > > >4. PSORT_NPROCS, PDQPRIORITY settings for the Index builds. > > > >5. Unique Indexes/Primary Key (A bit of heresy). Save (a lot of!) time by > >changing them to non-unique during this run. When you next get a window, > >revert > >them back to unique. > > > >6. Grab this opportunity to suggest archiving of "old" data :-) [getting > >desperate now!] > > > >Rudy > > > >Neil Truby wrote: > > > > > 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 > > > > ________________________________________________________________________ > Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com