Compress Repack Duration

Dear All, Had this same problem once, did not manage to find a solution. We are running IDS 11.70.FC4 on AIX 7.1 IBM Power 7 Machine. I have 16CPU (64 total SMT) and 160GB memory. Powerful disk subsystem IBM V7000 which goes up to >400MB/s read/write. I am trying to compress repack shrink 1 table which is ~ 25GB size and has 37M rows. Since "compress repack shrink" took me forever I've decided to split it i.e. first run compress than repack(don't know if it makes sense). The compress took around 11Hours and completed successfully(although there was quite a spare unused CPU capacity). Now I run repack and the situation is as following: > onstat -g dsk Rows Approx Approx Partnum OP Processed Cur Page Prog Duration Remaining Table 0x00200C8D REPACK 22781276 6306566 34% 05:20:06 10:01:44 --- 1st question, how come 22.7Mil out of 37M is only 34%? 2nd question, is it normal for such a strong machine to take > 16hours. Have few other tables with similar characteristics that have to compress but it takes quite long plus this is a production server and the things are getting slow during the working hours (since the whole operation takes more than 24h i have to run it sometimes) Here is my onconfig file: ROOTNAME rootdbs #ROOTPATH $INFORMIXDIR/tmp/demo_on.rootdbs #ROOTPATH /usr/informix/pb_dbs/rootdbs ROOTPATH /usr/informix/dbs/rootdbs ROOTOFFSET 100 ROOTSIZE 200000MIRROR 0 MIRRORPATH /usr/informix/tmp/demo_on.root_mirror MIRROROFFSET 0 #PHYSFILE 20970420 PHYSFILE 16776904 PLOG_OVERFLOW_PATH /usr/informix/tmp PHYSBUFF 128 LOGFILES 2684 LOGSIZE 10000 DYNAMIC_LOGS 2 LOGBUFF 64 LTXHWM 70 LTXEHWM 80 MSGPATH /usr/informix/tmp/online.log CONSOLE /usr/informix/tmp/online.con TBLTBLFIRST 0 TBLTBLNEXT 0 TBLSPACE_STATS 1 DBSPACETEMP tempdbs1:tempdbs2:tempdbs3:tempdbs4 SBSPACETEMP SBSPACENAME SYSSBSPACENAME ONDBSPACEDOWN 2 SERVERNUM 1 DBSERVERNAME xxx DBSERVERALIASES yyy FULL_DISK_INIT 0 NETTYPE ipcshm,4,100,CPU NETTYPE soctcp,2,256,NET LISTEN_TIMEOUT 60 MAX_INCOMPLETE_CONNECTIONS 1024 FASTPOLL 1 NUMFDSERVERS 8 NS_CACHE host=900,service=900,user=900,group=900 MULTIPROCESSOR 1 VPCLASS cpu,num=32,noage VP_MEMORY_CACHE_KB 0 SINGLE_CPU_VP 0 #VPCLASS aio,num=1 CLEANERS 8AUTO_AIOVPS 1 DIRECT_IO 0 LOCKS 1000000 DEF_TABLE_LOCKMODE row RESIDENT 0 SHMBASE 0x700000010000000 SHMVIRTSIZE 261248 SHMADD 8192 EXTSHMADD 8192 SHMVIRT_ALLOCSEG 0.000000 CKPTINTVL 100AUTO_CKPTS 1 RTO_SERVER_RESTART 0 BLOCKTIMEOUT 3600 CONVERSION_GUARD 2 RESTORE_POINT_DIR /usr/informix/tmp TXTIMEOUT 300 DEADLOCK_TIMEOUT 60 HETERO_COMMIT 0 TAPEDEV /dev/null TAPEBLK 32 TAPESIZE 0 LTAPEDEV /LLOG LTAPEBLK 32 LTAPESIZE 2097152 BAR_ACT_LOG /usr/informix/tmp/bar_act.log BAR_DEBUG_LOG /usr/informix/tmp/bar_dbug.log BAR_DEBUG 0 BAR_MAX_BACKUP 0 BAR_RETRY 1 BAR_NB_XPORT_COUNT 20 BAR_XFER_BUF_SIZE 31 RESTARTABLE_RESTORE on BAR_PROGRESS_FREQ 0 BAR_BSALIB_PATH BACKUP_FILTER /usr/bin/gzip RESTORE_FILTER /usr/bin/gunzip BAR_PERFORMANCE 0 BAR_CKPTSEC_TIMEOUT 15 ISM_DATA_POOL ISMData ISM_LOG_POOL ISMLogs DD_HASHSIZE 31 DD_HASHMAX 10 DS_HASHSIZE 31 DS_POOLSIZE 127 PC_HASHSIZE 31 PC_POOLSIZE 127 STMT_CACHE 0 STMT_CACHE_HITS 0 STMT_CACHE_SIZE 512 STMT_CACHE_NOLIMIT 0 STMT_CACHE_NUMPOOL 1 USEOSTIME 0 STACKSIZE 64 ALLOW_NEWLINE 0 USELASTCOMMITTED ALL FILLFACTOR 90 MAX_FILL_DATA_PAGES 0 BTSCANNER num=1,threshold=5000,rangesize=-1,alice=6,compression=default ONLIDX_MAXMEM 5120 MAX_PDQPRIORITY 100 DS_MAX_QUERIES 16 DS_TOTAL_MEMORY DS_MAX_SCANS 1048576 DS_NONPDQ_QUERY_MEM 128 DATASKIP off OPTCOMPIND 2 DIRECTIVES 1 EXT_DIRECTIVES 0 OPT_GOAL -1 IFX_FOLDVIEW 0AUTO_REPREPARE 1 AUTO_STAT_MODE 1 STATCHANGE 10 USTLOW_SAMPLE 0 BATCHEDREAD_TABLE 1 BATCHEDREAD_INDEX 1BATCHEDREAD_KEYONLY 0 AUTO_READAHEAD 1 EXPLAIN_STAT 1 IFX_EXTEND_ROLE 1 SECURITY_LOCALCONNECTION 0 UNSECURE_ONSTAT 0 ADMIN_USER_MODE_WITH_DBSA 0 ADMIN_MODE_USERS SSL_KEYSTORE_LABEL PLCY_POOLSIZE 127 PLCY_HASHSIZE 31 USRC_POOLSIZE 127 USRC_HASHSIZE 31 STAGEBLOB OPCACHEMAX 0 SEQ_CACHE_SIZE 10 ENCRYPT_HDR 0 ENCRYPT_SMX 0 ENCRYPT_CDR 0 ENCRYPT_CIPHERS ENCRYPT_MAC mediumENCRYPT_MACFILE builtin,builtin,builtin,builtin,builtin ENCRYPT_SWITCH 0,0 CDR_EVALTHREADS 1,2 CDR_DSLOCKWAIT 5 CDR_QUEUEMEM 4096 CDR_NIFCOMPRESS 0 CDR_SERIAL 0,0 CDR_DBSPACE CDR_QHDR_DBSPACE CDR_QDATA_SBSPACE CDR_SUPPRESS_ATSRISWARN CDR_LOG_LAG_ACTION ddrblock CDR_LOG_STAGING_MAXSIZE 0 CDR_MAX_DYNAMIC_LOGS 0 DRAUTO 0 DRINTERVAL 30 DRTIMEOUT 30 HA_ALIAS DRLOSTFOUND /usr/informix/etc/dr.lostfound DRIDXAUTO 0 LOG_INDEX_BUILDS 1 SDS_ENABLE 0 SDS_TIMEOUT 20 SDS_PAGING SDS_LOGCHECK 0 UPDATABLE_SECONDARY 0 FAILOVER_CALLBACK FAILOVER_TX_TIMEOUT 0 TEMPTAB_NOLOG 1 DELAY_APPLY 0 STOP_APPLY 0 LOG_STAGING_DIR RSS_FLOW_CONTROL 0 ENABLE_SNAPSHOT_COPY 0 SMX_COMPRESS 0 ON_RECVRY_THREADS 1 OFF_RECVRY_THREADS 10 DUMPDIR /usr/informix/tmp DUMPSHMEM 0 DUMPGCORE 0 DUMPCORE 0 DUMPCNT 1 ALARMPROGRAM /usr/informix/etc/alarmprogram.sh ALRM_ALL_EVENTS 0 STORAGE_FULL_ALARM 600,3 SYSALARMPROGRAM /usr/informix/etc/evidence.sh RAS_PLOG_SPEED 36250 RAS_LLOG_SPEED 2388 EILSEQ_COMPAT_MODE 0 QSTATS 0 WSTATS 0 USERMAPPING OFF SP_AUTOEXPAND 1 SP_THRESHOLD 0.000000 SP_WAITTIME 30 DEFAULTESCCHAR \\\\ BUFFERPOOL size=4K,buffers=23592960,lrus=512,lru_min_dirty=40.00,lru_max_dirty=50.00 AUTO_LRU_TUNING 1 SDS_LOGCHECK 0 SDS_LOGCHECK 0 SHMTOTAL 0 SHMNOACCESS SDS_LOGCHECK 0 SDS_LOGCHECK 0 SDS_LOGCHECK 0 Any advice will be quite helpful Thank you Aleksandar

This one is hard to make any statement, from the superficial problem=20 description, but I'd tend to agree that your duration is by far too long. One thing I noticed is: with your very many buffers (23mio) your=20 lru=5Fmin=5Fdirty/lru=5Fmax=5Fdirty values (40/50) would result in a lot of= pages=20 having to be flushed during a checkpoint. -> consider lowering these by at least 90%, probably better by 99% (->=20 0.4/0.5). Also have a look at checkpoint stats during such large operation, before=20 and after the re-config. You'd monitor the "StorageMGR" thread(s) performing these tasks, I'd=20 assume you'd see a lot of wait states, the question being what these are=20 waiting on. From: "ALEKSANDAR IVANOVSKI" <aleksandar.ivanovski@gmail.com> To: ids@iiug.org Date: 18.05.2017 14:29 Subject: Compress Repack Duration [39231] Sent by: ids-bounces@iiug.org Dear All,=20 Had this same problem once, did not manage to find a solution.=20 We are running IDS 11.70.FC4 on AIX 7.1 IBM Power 7 Machine.=20 I have 16CPU (64 total SMT) and 160GB memory. Powerful disk subsystem IBM=20 V7000 which goes up to >400MB/s read/write.=20 I am trying to compress repack shrink 1 table which is ~ 25GB size and has = 37M=20 rows. Since "compress repack shrink" took me forever I've decided to split = it=20 i.e. first run compress than repack(don't know if it makes sense).=20 The compress took around 11Hours and completed successfully(although there = was=20 quite a spare unused CPU capacity). Now I run repack and the situation is=20 as=20 following:=20 > onstat -g dsk=20 Rows Approx Approx=20 Partnum OP Processed Cur Page Prog Duration Remaining Table=20 0x00200C8D REPACK 22781276 6306566 34% 05:20:06 10:01:44 ---=20 1st question, how come 22.7Mil out of 37M is only 34%?=20 2nd question, is it normal for such a strong machine to take > 16hours.=20 Have few other tables with similar characteristics that have to compress=20 but=20 it takes quite long plus this is a production server and the things are=20 getting slow during the working hours (since the whole operation takes=20 more=20 than 24h i have to run it sometimes)=20 Here is my onconfig file:=20 ROOTNAME rootdbs=20 #ROOTPATH $INFORMIXDIR/tmp/demo=5Fon.rootdbs=20 #ROOTPATH /usr/informix/pb=5Fdbs/rootdbs=20 ROOTPATH /usr/informix/dbs/rootdbs=20 ROOTOFFSET 100=20 ROOTSIZE 200000=20MIRROR 0=20 MIRRORPATH /usr/informix/tmp/demo=5Fon.root=5Fmirror=20 MIRROROFFSET 0=20 #PHYSFILE 20970420=20 PHYSFILE 16776904=20 PLOG=5FOVERFLOW=5FPATH /usr/informix/tmp=20 PHYSBUFF 128=20 LOGFILES 2684=20 LOGSIZE 10000=20 DYNAMIC=5FLOGS 2=20 LOGBUFF 64=20 LTXHWM 70=20 LTXEHWM 80=20 MSGPATH /usr/informix/tmp/online.log=20 CONSOLE /usr/informix/tmp/online.con=20 TBLTBLFIRST 0=20 TBLTBLNEXT 0=20TBLSPACE=5FSTATS 1=20 DBSPACETEMP tempdbs1:tempdbs2:tempdbs3:tempdbs4=20 SBSPACETEMP=20 SBSPACENAME=20 SYSSBSPACENAME=20 ONDBSPACEDOWN 2=20 SERVERNUM 1=20 DBSERVERNAME xxx=20 DBSERVERALIASES yyy=20 FULL=5FDISK=5FINIT 0=20 NETTYPE ipcshm,4,100,CPU=20 NETTYPE soctcp,2,256,NET=20LISTEN=5FTIMEOUT 60=20 MAX=5FINCOMPLETE=5FCONNECTIONS 1024=20 FASTPOLL 1=20 NUMFDSERVERS 8=20 NS=5FCACHE host=3D900,service=3D900,user=3D900,group=3D900=20 MULTIPROCESSOR 1=20 VPCLASS cpu,num=3D32,noage=20 VP=5FMEMORY=5FCACHE=5FKB 0=20 SINGLE=5FCPU=5FVP 0=20 #VPCLASS aio,num=3D1=20 CLEANERS 8=20 AUTO=5FAIOVPS 1=20 DIRECT=5FIO 0=20 LOCKS 1000000=20 DEF=5FTABLE=5FLOCKMODE row=20 RESIDENT 0=20 SHMBASE 0x700000010000000=20 SHMVIRTSIZE 261248=20 SHMADD 8192=20 EXTSHMADD 8192=20 SHMVIRT=5FALLOCSEG 0.000000=20 CKPTINTVL 100=20 AUTO=5FCKPTS 1=20 RTO=5FSERVER=5FRESTART 0=20 BLOCKTIMEOUT 3600=20 CONVERSION=5FGUARD 2=20 RESTORE=5FPOINT=5FDIR /usr/informix/tmp=20 TXTIMEOUT 300=20DEADLOCK=5FTIMEOUT 60=20 HETERO=5FCOMMIT 0=20 TAPEDEV /dev/null=20 TAPEBLK 32=20 TAPESIZE 0=20 LTAPEDEV /LLOG=20 LTAPEBLK 32=20 LTAPESIZE 2097152=20 BAR=5FACT=5FLOG /usr/informix/tmp/bar=5Fact.log=20 BAR=5FDEBUG=5FLOG /usr/informix/tmp/bar=5Fdbug.log=20 BAR=5FDEBUG 0=20 BAR=5FMAX=5FBACKUP 0=20 BAR=5FRETRY 1=20 BAR=5FNB=5FXPORT=5FCOUNT 20=20 BAR=5FXFER=5FBUF=5FSIZE 31=20 RESTARTABLE=5FRESTORE on=20 BAR=5FPROGRESS=5FFREQ 0=20 BAR=5FBSALIB=5FPATH=20 BACKUP=5FFILTER /usr/bin/gzip=20 RESTORE=5FFILTER /usr/bin/gunzip=20 BAR=5FPERFORMANCE 0=20 BAR=5FCKPTSEC=5FTIMEOUT 15=20 ISM=5FDATA=5FPOOL ISMData=20 ISM=5FLOG=5FPOOL ISMLogs=20 DD=5FHASHSIZE 31=20 DD=5FHASHMAX 10=20 DS=5FHASHSIZE 31=20 DS=5FPOOLSIZE 127=20 PC=5FHASHSIZE 31=20 PC=5FPOOLSIZE 127=20 STMT=5FCACHE 0=20 STMT=5FCACHE=5FHITS 0=20 STMT=5FCACHE=5FSIZE 512=20 STMT=5FCACHE=5FNOLIMIT 0=20 STMT=5FCACHE=5FNUMPOOL 1=20 USEOSTIME 0=20 STACKSIZE 64=20 ALLOW=5FNEWLINE 0=20 USELASTCOMMITTED ALL=20 FILLFACTOR 90=20 MAX=5FFILL=5FDATA=5FPAGES 0=20 BTSCANNER num=3D1,threshold=3D5000,rangesize=3D-1,alice=3D6,compression=3Dd= efault=20ONLIDX=5FMAXMEM 5120=20 MAX=5FPDQPRIORITY 100=20 DS=5FMAX=5FQUERIES 16=20 DS=5FTOTAL=5FMEMORY=20 DS=5FMAX=5FSCANS 1048576=20 DS=5FNONPDQ=5FQUERY=5FMEM 128=20 DATASKIP off=20 OPTCOMPIND 2=20 DIRECTIVES 1=20EXT=5FDIRECTIVES 0=20 OPT=5FGOAL -1=20 IFX=5FFOLDVIEW 0=20 AUTO=5FREPREPARE 1=20 AUTO=5FSTAT=5FMODE 1=20 STATCHANGE 10=20 USTLOW=5FSAMPLE 0=20 BATCHEDREAD=5FTABLE 1=20 BATCHEDREAD=5FINDEX 1=20 BATCHEDREAD=5FKEYONLY 0=20 AUTO=5FREADAHEAD 1=20 EXPLAIN=5FSTAT 1=20 IFX=5FEXTEND=5FROLE 1=20 SECURITY=5FLOCALCONNECTION 0=20 UNSECURE=5FONSTAT 0=20 ADMIN=5FUSER=5FMODE=5FWITH=5FDBSA 0=20 ADMIN=5FMODE=5FUSERS=20 SSL=5FKEYSTORE=5FLABEL=20 PLCY=5FPOOLSIZE 127=20 PLCY=5FHASHSIZE 31=20 USRC=5FPOOLSIZE 127=20 USRC=5FHASHSIZE 31=20 STAGEBLOB=20 OPCACHEMAX 0=20 SEQ=5FCACHE=5FSIZE 10=20 ENCRYPT=5FHDR 0=20 ENCRYPT=5FSMX 0=20 ENCRYPT=5FCDR 0=20 ENCRYPT=5FCIPHERS=20 ENCRYPT=5FMAC medium=20 ENCRYPT=5FMACFILE builtin,builtin,builtin,builtin,builtin=20 ENCRYPT=5FSWITCH 0,0=20 CDR=5FEVALTHREADS 1,2=20 CDR=5FDSLOCKWAIT 5=20 CDR=5FQUEUEMEM 4096=20 CDR=5FNIFCOMPRESS 0=20 CDR=5FSERIAL 0,0=20 CDR=5FDBSPACE=20 CDR=5FQHDR=5FDBSPACE=20 CDR=5FQDATA=5FSBSPACE=20 CDR=5FSUPPRESS=5FATSRISWARN=20 CDR=5FLOG=5FLAG=5FACTION ddrblock=20 CDR=5FLOG=5FSTAGING=5FMAXSIZE 0=20 CDR=5FMAX=5FDYNAMIC=5FLOGS 0=20 DRAUTO 0=20 DRINTERVAL 30=20 DRTIMEOUT 30=20 HA=5FALIAS=20 DRLOSTFOUND /usr/informix/etc/dr.lostfound=20 DRIDXAUTO 0=20 LOG=5FINDEX=5FBUILDS 1=20 SDS=5FENABLE 0=20 SDS=5FTIMEOUT 20=20 SDS=5FPAGING=20 SDS=5FLOGCHECK 0=20UPDATABLE=5FSECONDARY 0=20 FAILOVER=5FCALLBACK=20 FAILOVER=5FTX=5FTIMEOUT 0=20 TEMPTAB=5FNOLOG 1=20 DELAY=5FAPPLY 0=20 STOP=5FAPPLY 0=20 LOG=5FSTAGING=5FDIR=20 RSS=

Thank you for the info. Checkpoints during the normal operations never take more than 1 sec (actually it is 0 most of the time). My buffer turnover ratio within an hour is around 2 which if I follow the recommendations is quite good (if it is less than 8) When i do compress/repack it may go up to 4-6seconds ... but afaik I am not able to change this settings without restarting the engine. A.