index builds are very slow

from Doug Merrill: dmerrill@lakecountyil.gov Created 950 tables using dbschema. Disabled, triggers, constraints, and indexes. Loaded data using load in parallel. Database size is 200gb. Enabling the indexes has been running for 48 hours. Enabling the indexes is still running! Why are index builds(or enables) so slow????????????????????????????? ### Platform ###### IBM Informix Dynamic Server Version 10.00.FC4 -- On-Line -- Up 3 days 11:25:04 -- 18799640 Kbytes Red Hat Enterprise Linux AS release 4 (Nahant Update 6) Linux taxtst.lakeco.org 2.6.9-67.ELlargesmp #1 SMP Wed Nov 7 14:07:22 EST 2007 x86_64 x86_64 x86_64 GNU/Linux ################### Wed May 26 10:21:59 CDT 2010 onstat -p IBM Informix Dynamic Server Version 10.00.FC4 -- On-Line -- Up 3 days 11:25:04 -- 18799640 Kbytes Profile dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 91921459 4344217958 2640181145 99.81 195510417 199383464 2380367396 91.79 isamtot open start read write rewrite delete commit rollbk 4787344911 2410154 6068368 18965177 1840194224 582908 18024 548552 145 gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs 5 0 0 0 0 0 8 ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes 0 0 35553 103224.38 34824.39 1018 5622 bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans 62107 0 1939505533 0 0 1334 35505 367095 ixda-RA idx-RA da-RA RA-pgsused lchwaits 10962 0 1489753 1500323 22708419 Wed May 26 10:21:59 CDT 2010 onstat -d IBM Informix Dynamic Server Version 10.00.FC4 -- On-Line -- Up 3 days 11:25:04 -- 18799640 Kbytes Dbspaces address number flags fchunk nchunks pgsize flags owner name 13fb4be78 1 0x60001 1 1 2048 N B informix rootdbs 141466848 2 0x60001 2 1 2048 N B informix plogdbs 1414669e0 3 0x60001 3 1 2048 N B informix llogdbs 141466b78 4 0x60001 4 1 2048 N B informix llogdbs2 141466d10 5 0x42001 5 5 2048 N TB informix temp1 141467028 6 0x42001 10 5 2048 N TB informix temp2 1414671c0 7 0x42001 11 5 2048 N TB informix temp3 141467358 8 0x42001 12 5 2048 N TB informix temp4 1415896e8 9 0x60001 25 1 2048 N B informix indexsp 1414674f0 10 0x60001 26 1 2048 N B informix dbasesp 14153cdc8 11 0x60001 27 1 4096 N B informix statementsp 14151fe68 12 0x60001 28 1 4096 N B informix stmt_instsp 141585d08 13 0x60001 29 1 4096 N B informix stmt_linesp 1415cae90 14 0x60001 30 1 2048 N B informix countysp 1415b0ab0 15 0x60001 31 6 2048 N B informix provalsp 141467688 25 0x68001 41 4 2048 N SB informix sbspace2 16 active, 2047 maximum Chunks address chunk/dbs offset size free bpages flags pathname 13fb4c028 1 1 0 25000 22118 PO-B /opt/admspaces/rootdbs 141463778 2 2 0 2621440 2365387 PO-B /opt/admspaces/plogdbs 141463918 3 3 0 2621440 58887 PO-B /opt/admspaces/llogdbs 141463ab8 4 4 0 2621440 58887 PO-B /opt/admspaces/llogdbs2 141463c58 5 5 0 1048576 1048523 PO-B /dat/links/tempsp/temp1/temp1_lnk.01 141463df8 6 5 0 1048576 1048573 PO-B /dat/links/tempsp/temp1/temp1_lnk.02 141464028 7 5 0 1048576 1048573 PO-B /dat/links/tempsp/temp1/temp1_lnk.03 1414641c8 8 5 0 1048576 1048573 PO-B /dat/links/tempsp/temp1/temp1_lnk.04 141464368 9 5 0 1048576 1048573 PO-B /dat/links/tempsp/temp1/temp1_lnk.05 141464508 10 6 0 1048576 1048523 PO-B /dat/links/tempsp/temp2/temp2_lnk.01 1414646a8 11 7 0 1048576 1048523 PO-B /dat/links/tempsp/temp3/temp3_lnk.01 141464848 12 8 0 1048576 1048523 PO-B /dat/links/tempsp/temp4/temp4_lnk.01 1414649e8 13 6 0 1048576 1048573 PO-B /dat/links/tempsp/temp2/temp2_lnk.02 141464b88 14 6 0 1048576 1048573 PO-B /dat/links/tempsp/temp2/temp2_lnk.03 141464d28 15 6 0 1048576 1048573 PO-B /dat/links/tempsp/temp2/temp2_lnk.04 141465028 16 6 0 1048576 1048573 PO-B /dat/links/tempsp/temp2/temp2_lnk.05 1414651c8 17 7 0 1048576 1048573 PO-B /dat/links/tempsp/temp3/temp3_lnk.02 141465368 18 7 0 1048576 1048573 PO-B /dat/links/tempsp/temp3/temp3_lnk.03 141465508 19 7 0 1048576 1048573 PO-B /dat/links/tempsp/temp3/temp3_lnk.04 1414656a8 20 7 0 1048576 1048573 PO-B /dat/links/tempsp/temp3/temp3_lnk.05 141465848 21 8 0 1048576 1048573 PO-B /dat/links/tempsp/temp4/temp4_lnk.02 1414659e8 22 8 0 1048576 1048573 PO-B /dat/links/tempsp/temp4/temp4_lnk.03 141465b88 23 8 0 1048576 1048573 PO-B /dat/links/tempsp/temp4/temp4_lnk.04 141465d28 24 8 0 1048576 1048573 PO-B /dat/links/tempsp/temp4/temp4_lnk.05 141589880 25 9 0 232783872 157456643 PO-B /idx/links/indexsp_lnk.01 141466028 26 10 0 262144000 150158396 PO-B /dat/links/dbasesp/dbasesp_lnk.01 14151fcc8 27 11 0 13107200 9607147 PO-B /dat/links/statementsp/statement_lnk.01 1415855a8 28 12 0 13107200 11982147 PO-B /dat/links/stmt_instsp/stmt_inst_dat.01 14158b028 29 13 0 13107200 11732147 PO-B /dat/links/stmt_linesp/stmt_line_dat.01 1415f6050 30 14 0 78643200 47271051 PO-B /dat/links/countysp/countysp_lnk.01 1415b0fc8 31 15 0 26214400 13 PO-B /dat/links/provalsp/provalsp_lnk.01 141c78e18 32 15 0 13107200 3534 PO-B /dat/links/provalsp/provalsp_lnk.02 1415a2dc8 33 15 0 13107200 8515 PO-B /dat/links/provalsp/provalsp_lnk.03 1419d8c40 34 15 0 13107200 3864697 PO-B /dat/links/provalsp/provalsp_lnk.04 141d737f0 35 15 0 13107200 13107197 PO-B /dat/links/provalsp/provalsp_lnk.05 141a42ce0 36 15 0 13107200 13107197 PO-B /dat/links/provalsp/provalsp_lnk.06 1414661c8 41 25 0 5242880 4889938 4889938 POSB /dat/links/grp2/sbspace_lnk.01 Metadata 352889 36870 352889 141466368 56 25 0 2621440 2444977 2444977 POSB /dat/links/grp2/sbspace_lnk.02 Metadata 176460 176460 176460 141466508 59 25 0 5242880 4889985 4889985 POSB /dat/links/grp2/sbspace_lnk.03 Metadata 352892 352892 352892 1414666a8 60 25 0 5242880 4889985 4889985 POSB /dat/links/grp2/sbspace_lnk.04 Metadata 352892 352892 352892 40 active, 32766 maximum NOTE: The values in the "size" and "free" columns for DBspace chunks are displayed in terms of "pgsize" of the DBspace to which they belong. Expanded chunk capacity mode: always Wed May 26 10:21:59 CDT 2010 onstat -g iof IBM Informix Dynamic Server Version 10.00.FC4 -- On-Line -- Up 3 days 11:25:04 -- 18799640 Kbytes AIO global files: gfd pathname totalops dskread dskwrite io/s 3 rootdbs 23274 10260 13014 0.1 4 plogdbs 18137 24 18113 0.1 5 llogdbs 23 23 0 0.0 6 llogdbs2 448500 32 448468 1.5 7 temp1_lnk.01 24 21 3 0.0 8 temp1_lnk.02 13 11 2 0.0 9 temp1_lnk.03 13 11 2 0.0 10 temp1_lnk.04 13 11 2 0.0 11 temp1_lnk.05 13 11 2 0.0 12 temp2_lnk.01 24 21 3 0.0 13 temp3_lnk.01 24 21 3 0.0 14 temp4_lnk.01 24 21 3 0.0 15 temp2_lnk.02 13 11 2 0.0 16 temp2_lnk.03 13 11 2 0.0 17 temp2_lnk.04 13 11 2 0.0 18 temp2_lnk.05 13 11 2 0.0 19 temp3_lnk.02 13 11 2 0.0 20 temp3_lnk.03 13 11 2 0.0 21 temp3_lnk.04 13 11 2 0.0 22 temp3_lnk.05 13 11 2 0.0 23 temp4_lnk.02 13 11 2 0.0 24 temp4_lnk.03 13 11 2 0.0 25 temp4_lnk.04 13 11 2 0.0 26 temp4_lnk.05 13 11 2 0.0 27 dbasesp_lnk.01 105159407 62458567 42700840 350.2 28 sbspace_lnk.01 157 157 0 0.0 29 sbspace_lnk.02 12 12 0 0.0 30 sbspace_lnk.03 12 12 0 0.0 31 sbspace_lnk.04 12 12 0 0.0 32 indexsp_lnk.01 25449726 170810 25278916 84.7 33 statement_lnk.01 2377042 1380983 996059 7.9 34 stmt_inst_dat.01 1092627 707896 384731

DOUGLAS MERRILL wrote: > from Doug Merrill: dmerrill@lakecountyil.gov > > Created 950 tables using dbschema. > Disabled, triggers, constraints, and indexes. > Loaded data using load in parallel. > Database size is 200gb. > Enabling the indexes has been running for 48 hours. > Enabling the indexes is still running! > Why are index builds(or enables) so slow????????????????????????????? Probably because it is creating indexes serially and also you are almost certainly not using fragmented tables and PDQ. -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com I will now proceed to pleasure myself with this fish. -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.