Create index very slow

This is a multi-part message in MIME format. --------------4CF41BB9DAD092C4AEBBE3C6 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Hi everyone, It's been awhile, but here's my riddle for today: I'm running a create index statement and its been busy for the last 7 hours. The table's got 55 million rows (spread across 3 locations by expression). rtpm/top/... shows my one CPU (of six) at 100% continuously, while the others are idle. I've got affinity and all that jazz on. My onconfig follows. Any insights to help me explain the delay to my boss appreciated. Cheers -- /* ----------------------------------------------------- * * Willem Roos wroos@shoprite.co.za * * roosj@mweb.co.za * * 0(+27)21 980 4941 * * 0(+27)21 919 0198 * * ----------------------------------------------------- */ --------------4CF41BB9DAD092C4AEBBE3C6 Content-Type: text/plain; charset=us-ascii; name="onconfig.corpsrv" Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="onconfig.corpsrv" #************************************************************************** # # INFORMIX SOFTWARE, INC. # # Title: onconfig.corpsrv # Description: INFORMIX-OnLine Configuration Parameters # #************************************************************************** # Root Dbspace Configuration ROOTNAME rootdbs # Root dbspace nameROOTPATH /home/informix/dbspaces/corpsrv/rootdbs # Path for device containing root dbspace ROOTOFFSET 0 # Offset of root dbspace into device (Kbytes) ROOTSIZE 65526 # Size of root dbspace (Kbytes) # Disk Mirroring Configuration Parameters MIRROR 0 # Mirroring flag (Yes = 1, No = 0) MIRRORPATH # Path for device containing mirrored root MIRROROFFSET 0 # Offset into mirrored device (Kbytes) # Physical Log Configuration PHYSDBS phylog # Location (dbspace) of physical log PHYSFILE 260000 # Physical log file size (Kbytes) # Logical Log Configuration LOGFILES 20 # Number of logical log files LOGSIZE 2048 # Logical log size (Kbytes) # Diagnostics MSGPATH /home/informix/logs/corpsrv.log # System message log file path CONSOLE /home/informix/logs/corpsrv.console # System console message path ALARMPROGRAM /usr/informix/log_full.sh # Alarm program path # System Archive Tape Device #TAPEDEV chkic3:/dev/rmt/rtape1 # Tape device path #TAPEDEV /dev/rmt/rtape1 # Tape device path TAPEDEV /dev/rmt/rtape1 # Tape device path #TAPEDEV /dev/null TAPEBLK 16 # Tape block size (Kbytes) TAPESIZE 5000000 # Maximum amount of data to put on tape (Kbytes) # Log Archive Tape Device LTAPEDEV /dev/null # Log tape device path LTAPEBLK 16 # Log tape block size (Kbytes) LTAPESIZE 10240 # Max amount of data to put on log tape (Kbytes) # Optical STAGEBLOB # INFORMIX-OnLine/Optical staging area # System Configuration SERVERNUM 1 # Unique id corresponding to a OnLine instance DBSERVERNAME corpsrv_tli # Name of default database server DBSERVERALIASES corpsrv # List of alternate dbservernames DEADLOCK_TIMEOUT 60 # Max time to wait of lock in distributed env.#RESIDENT 0 # Forced residency flag (Yes = 1, No = 0) RESIDENT 1 # Forced residency flag (Yes = 1, No = 0) #MULTIPROCESSOR 0 # 0 for single-processor, 1 for multi-processor MULTIPROCESSOR 1 # 0 for single-processor, 1 for multi-processor#NUMCPUVPS 1 # Number of user (cpu) vps NUMCPUVPS 5 # Number of user (cpu) vps SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps to one #NOAGE 0 # Process aging NOAGE 1 # Process aging#AFF_SPROC 0 # Affinity start processor AFF_SPROC 1 # Affinity start processor#AFF_NPROCS 0 # Affinity number of processors AFF_NPROCS 5 # Affinity number of processors # Shared Memory Parameters LOCKS 50000 # Maximum number of locks#BUFFERS 50000 # Maximum number of shared buffers BUFFERS 65536 # Maximum number of shared buffers#NUMAIOVPS # Number of IO vps NUMAIOVPS 32 # Number of IO vps PHYSBUFF 32 # Physical log buffer size (Kbytes) LOGBUFF 32 # Logical log buffer size (Kbytes)LOGSMAX 20 # Maximum number of logical log files #CLEANERS 1 # Number of buffer cleaner processes CLEANERS 12 # Number of buffer cleaner processes SHMBASE 0xa000000 # Shared memory base address#SHMVIRTSIZE 8000 # initial virtual shared memory segment size SHMVIRTSIZE 24576 # initial virtual shared memory segment size SHMADD 8192 # Size of new shared memory segments (Kbytes) SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited CKPTINTVL 300 # Check point interval (in sec) LRUS 8 # Number of LRU queues LRU_MAX_DIRTY 1 # LRU percent dirty begin cleaning limit LRU_MIN_DIRTY 0 # LRU percent dirty end cleaning limit LTXHWM 50 # Long transaction high water mark percentage LTXEHWM 60 # Long transaction high water mark (exclusive) TXTIMEOUT 0x12c # Transaction timeout (in sec) STACKSIZE 32 # Stack size (Kbytes) # System Page Size # BUFFSIZE - OnLine no longer supports this configuration parameter. # To determine the page size used by OnLine on your platform # see the last line of output from the command, 'onstat -b'. # Recovery Variables # OFF_RECVRY_THREADS: # Number of parallel worker threads during fast recovery or an offline restore. # ON_RECVRY_THREADS: # Number of parallel worker threads during an online restore. OFF_RECVRY_THREADS 10 # Default number of offline worker threads ON_RECVRY_THREADS 1 # Default number of online worker threads # Data Replication Variables # DRAUTO: 0 manual, 1 retain type, 2 reverse type DRAUTO 0 # DR automatic switchover DRINTERVAL 30 # DR max time between DR buffer flushes (in sec) DRTIMEOUT 30 # DR network timeout (in sec)DRLOSTFOUND /home/informix/datareplication/corpsrv.lostfound # DR lost+found file path # CDR Variables CDR_LOGBUFFERS 2048 # size of log reading buffer poo

Sounds dumb but....... Next time turn affinity off. Looks like you truly have bound this process to one processor. Let the O/S spread the load out. I find that usually works better. ;-) Terry Hillick VP, CSCSi. Willem Roos wrote: > This is a multi-part message in MIME format. > --------------4CF41BB9DAD092C4AEBBE3C6 > Content-Type: text/plain; charset=us-ascii > Content-Transfer-Encoding: 7bit > > Hi everyone, > > It's been awhile, but here's my riddle for today: > > I'm running a create index statement and its been busy for the last 7 > hours. The table's got 55 million rows (spread across 3 locations by > expression). rtpm/top/... shows my one CPU (of six) at 100% > continuously, while the others are idle. I've got affinity and all that > jazz on. My onconfig follows. > > Any insights to help me explain the delay to my boss appreciated. > > Cheers > > -- > /* ----------------------------------------------------- * > * Willem Roos wroos@shoprite.co.za * > * roosj@mweb.co.za * > * 0(+27)21 980 4941 * > * 0(+27)21 919 0198 * > * ----------------------------------------------------- */ > --------------4CF41BB9DAD092C4AEBBE3C6 > Content-Type: text/plain; charset=us-ascii; name="onconfig.corpsrv" > Content-Transfer-Encoding: 7bit > Content-Disposition: inline; filename="onconfig.corpsrv" > > #************************************************************************** > # > # INFORMIX SOFTWARE, INC. > # > # Title: onconfig.corpsrv > # Description: INFORMIX-OnLine Configuration Parameters > # > #************************************************************************** > > # Root Dbspace Configuration > > ROOTNAME rootdbs # Root dbspace name> ROOTPATH /home/informix/dbspaces/corpsrv/rootdbs > # Path for device containing root dbspace > ROOTOFFSET 0 # Offset of root dbspace into device (Kbytes) > ROOTSIZE 65526 # Size of root dbspace (Kbytes)> > # Disk Mirroring Configuration Parameters > > MIRROR 0 # Mirroring flag (Yes = 1, No = 0) > MIRRORPATH # Path for device containing mirrored root > MIRROROFFSET 0 # Offset into mirrored device (Kbytes)> > # Physical Log Configuration > > PHYSDBS phylog # Location (dbspace) of physical log > PHYSFILE 260000 # Physical log file size (Kbytes)> > # Logical Log Configuration > > LOGFILES 20 # Number of logical log files > LOGSIZE 2048 # Logical log size (Kbytes)> > # Diagnostics > > MSGPATH /home/informix/logs/corpsrv.log # System message log file path > CONSOLE /home/informix/logs/corpsrv.console > # System console message path > ALARMPROGRAM /usr/informix/log_full.sh # Alarm program path > > # System Archive Tape Device > > #TAPEDEV chkic3:/dev/rmt/rtape1 # Tape device path > #TAPEDEV /dev/rmt/rtape1 # Tape device path > TAPEDEV /dev/rmt/rtape1 # Tape device path > #TAPEDEV /dev/null > TAPEBLK 16 # Tape block size (Kbytes) > TAPESIZE 5000000 # Maximum amount of data to put on tape (Kbytes)> > # Log Archive Tape Device > > LTAPEDEV /dev/null # Log tape device path > LTAPEBLK 16 # Log tape block size (Kbytes) > LTAPESIZE 10240 # Max amount of data to put on log tape (Kbytes)> > # Optical > > STAGEBLOB # INFORMIX-OnLine/Optical staging area > > # System Configuration > > SERVERNUM 1 # Unique id corresponding to a OnLine instance > DBSERVERNAME corpsrv_tli # Name of default database server > DBSERVERALIASES corpsrv # List of alternate dbservernames > DEADLOCK_TIMEOUT 60 # Max time to wait of lock in distributed env.> #RESIDENT 0 # Forced residency flag (Yes = 1, No = 0) > RESIDENT 1 # Forced residency flag (Yes = 1, No = 0)> > #MULTIPROCESSOR 0 # 0 for single-processor, 1 for multi-processor > MULTIPROCESSOR 1 # 0 for single-processor, 1 for multi-processor> #NUMCPUVPS 1 # Number of user (cpu) vps > NUMCPUVPS 5 # Number of user (cpu) vps > SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps to one> > #NOAGE 0 # Process aging > NOAGE 1 # Process aging> #AFF_SPROC 0 # Affinity start processor > AFF_SPROC 1 # Affinity start processor> #AFF_NPROCS 0 # Affinity number of processors > AFF_NPROCS 5 # Affinity number of processors> > # Shared Memory Parameters > > LOCKS 50000 # Maximum number of locks> #BUFFERS 50000 # Maximum number of shared buffers > BUFFERS 65536 # Maximum number of shared buffers> #NUMAIOVPS # Number of IO vps > NUMAIOVPS 32 # Number of IO vps > PHYSBUFF 32 # Physical log buffer size (Kbytes) > LOGBUFF 32 # Logical log buffer size (Kbytes)> LOGSMAX 20 # Maximum number of logical log files > #CLEANERS 1 # Number of buffer cleaner processes > CLEANERS 12 # Number of buffer cleaner processes > SHMBASE 0xa000000 # Shared memory base address> #SHMVIRTSIZE 8000 # initial virtual shared memory segment size > SHMVIRTSIZE 24576 # initial virtual shared memory segment size > SHMADD 8192 # Size of new shared memory segments (Kbytes) > SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited > CKPTINTVL 300 # Check point interval (in sec) > LRUS 8 # Number of LRU queues > LRU_MAX_DIRTY 1 # LRU percent dirty begin cleaning limit > LRU_MIN_DIRTY 0 # LRU percent dirty end cleaning limit > LTXHWM 50 # Long transaction high water mark percentage > LTXEHWM 60 # Long transaction high water mark (exclusive) > TXTIMEOUT 0x12c # Transaction timeout (in sec) > STACKSIZE 32 # Stack size (Kbytes)> > # System Page Size > # BUFFSIZE - OnLine no longer supports this configuration parameter. > # To determine the page size used by OnLine on your platform > # see the last line of output from the command, 'onstat -b'. > > # Recovery Variables > # OFF_RECVRY_THREADS: > # Number of parallel worker threads during fast recovery or an offline restore. > # ON_RECVRY_THREADS: > # Number of parallel worker threads during an online restore. > > OFF_RECVRY_THREADS 10