IDS Forum

Compress Repack Duration

Posted By: ALEKSANDAR IVANOVSKI
Date: Thursday, 18 May 2017, at 8:28 a.m.

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 200000
MIRROR 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 8
AUTO_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 100
AUTO_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 0
AUTO_REPREPARE 1
AUTO_STAT_MODE 1
STATCHANGE 10
USTLOW_SAMPLE 0

BATCHEDREAD_TABLE 1
BATCHEDREAD_INDEX 1
BATCHEDREAD_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 medium
ENCRYPT_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

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.