IDS Forum

Re: Compress Repack Duration

Posted By: Andreas Legner
Date: Friday, 19 May 2017, at 10:15 a.m.

In Response To: Compress Repack Duration (ALEKSANDAR IVANOVSKI)

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=20
MIRROR 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=20
TBLSPACE=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=20
LISTEN=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=20
DEADLOCK=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=20
ONLIDX=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=20
EXT=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=20
UPDATABLE=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=5FFLOW=5FCONTROL 0=20
ENABLE=5FSNAPSHOT=5FCOPY 0=20
SMX=5FCOMPRESS 0=20

ON=5FRECVRY=5FTHREADS 1=20
OFF=5FRECVRY=5FTHREADS 10=20

DUMPDIR /usr/informix/tmp=20
DUMPSHMEM 0=20
DUMPGCORE 0=20
DUMPCORE 0=20
DUMPCNT 1=20

ALARMPROGRAM /usr/informix/etc/alarmprogram.sh=20
ALRM=5FALL=5FEVENTS 0=20
STORAGE=5FFULL=5FALARM 600,3=20
SYSALARMPROGRAM /usr/informix/etc/evidence.sh=20

RAS=5FPLOG=5FSPEED 36250=20
RAS=5FLLOG=5FSPEED 2388=20

EILSEQ=5FCOMPAT=5FMODE 0=20

QSTATS 0=20
WSTATS 0=20

USERMAPPING OFF=20

SP=5FAUTOEXPAND 1=20
SP=5FTHRESHOLD 0.000000=20
SP=5FWAITTIME 30=20

DEFAULTESCCHAR \=20

BUFFERPOOL=20
size=3D4K,buffers=3D23592960,lrus=3D512,lru=5Fmin=5Fdirty=3D40.00,lru=5Fmax=
=5Fdirty=3D50.00=20

AUTO=5FLRU=5FTUNING 1=20
SDS=5FLOGCHECK 0=20
SDS=5FLOGCHECK 0=20
SHMTOTAL 0=20
SHMNOACCESS=20
SDS=5FLOGCHECK 0=20
SDS=5FLOGCHECK 0=20
SDS=5FLOGCHECK 0=20

Any advice will be quite helpful=20
Thank you=20

Aleksandar=20

***************************************************************************=
****=20

Forum Note: Use "Reply" to post a response in the discussion forum.=20

Messages In This Thread

• Compress Repack Duration
  ALEKSANDAR IVANOVSKI -- Thursday, 18 May 2017, at 8:28 a.m.
  • Re: Compress Repack Duration
    Andreas Legner -- Friday, 19 May 2017, at 10:15 a.m.
    • Re: Compress Repack Duration
      ALEKSANDAR IVANOVSKI -- Saturday, 20 May 2017, at 3:36 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.