IDS Forum

Re: Performance issue after migrate to IDS from On

Posted By: Madison Pruet
Date: Friday, 23 October 2015, at 10:07 a.m.

In Response To: Performance issue after migrate to IDS from Online (ANTONIO DIAZ)

blockquote, div.yahoo_quoted { margin-left: 0 !important; border-left:1px #715FFA solid !important; padding-left:1ex !important; background-color:white !important; } I suspect repeatable reads.

Sent from Yahoo Mail for iPad

On Friday, October 23, 2015, 8:53 AM, ANTONIO DIAZ <> wrote:

(first of all: I post here because IDS has changed but 4GL no.)

Hi All,

After migration, the new virtual server shows a no smooth behavior(I don't
know how to express it in English) when running 4GL database processes.
When we run certain processes, other terminals get stalled for seconds.
Also, a process (we'll call it "malo") that took about 30 seconds, now takes
about 1 minute.

The problem is that all has changed. Nevertheless, before posting here we have
checked that (almost) all is perfect in the platform.

After all kind of optimizations, we reached about a 50sec time.

In a nutshell, the "malo" process iterates through a big cursor, accesing
other tables and doing several for loops.
SET EXPLAIN reports no sequential access at all.

We have (now I don't know if) enough experience tunning Unix/Linux/informix.
All benchmarks show that the new platform is in the worst case, 8 times faster
than old one.
Intense I/O O.S. processes such as copying or managing big files are very fast
and don't cause latency problems to other terminals.
When migrating database, DBLOAD tasks were incredibly fast.

We have replicated the "malo" 4GL program into a stored procedure and it took
4 seconds!

If you just suspect something, please tell me.

Thanks

Antonio

Technical info follows:

--------------------------------------------------------------------------------
Previous platform:
Server: Fujitsu-Siemens RX300S4 8GB RAM 2 sockets 4cores/each
CPUid:1067A@2667MHz
Storage: Fujitsu-Siemens SX80. Two Controllers 1GB cache/each FC4Gb/s
Hypervisor: RHEL5 Xen 3
guest OS: RHEL4 (32bit)
Database: Informix Online 5.20
Tools: 4GL 7.50UC1

--------------------------------------------------------------------------------
New platform:
Server: Fujitsu RX300S8 32GB RAM 2 sockets 6cores/each
CPUid:0306E4@2100MHz(Xeon E5-2620v2)
Storage: Fujitsu DX200S3. Two Controllers 8GB cache/each FC8Gb/s
Hypervisor: VMware ESXi 6.0
guest OS: Centos 6.7 (64bit)
Database: IDS 12.10.FC5WE
Tools: 4GL 7.50.FC6

--------------------------------------------------------------------------------
ONCONFIG: (we have tried hundreds of changes whithout appreciable changes)
--------

ROOTNAME rootdbs
ROOTPATH /apl/dev/trozos/rootdbs
ROOTOFFSET 0
ROOTSIZE 2097152 # 2gb
MIRROR 0
MIRRORPATH $INFORMIXDIR/tmp/demo_on.root_mirror
MIRROROFFSET 0
PHYSFILE 99894 # 100MB
PLOG_OVERFLOW_PATH $INFORMIXDIR/tmp
PHYSBUFF 512
LOGFILES 100
LOGSIZE 10240
DYNAMIC_LOGS 2
LOGBUFF 256
LTXHWM 50
LTXEHWM 60
MSGPATH /apl/informix/turlo.log # linkak a turbo.log!!
CONSOLE /apl/informix/turbo.con
TBLTBLFIRST 0
TBLTBLNEXT 0
TBLSPACE_STATS 1
SBSPACETEMP
SBSPACENAME
SYSSBSPACENAME
ONDBSPACEDOWN 1
SERVERNUM 0
DBSERVERNAME idspina
DBSERVERALIASES lo_informix1210
FULL_DISK_INIT 0
NETTYPE ipcshm,5,50,CPU
NETTYPE soctcp,5,10,NET
LISTEN_TIMEOUT 60
MAX_INCOMPLETE_CONNECTIONS 1024
FASTPOLL 1
NUMFDSERVERS 4
NS_CACHE host=900,service=900,user=900,group=900
MULTIPROCESSOR 1
VPCLASS cpu,num=6,noage
VP_MEMORY_CACHE_KB 0
SINGLE_CPU_VP 0
AUTO_TUNE 1
AUTO_CKPTS 0
CLEANERS 21
DIRECT_IO 1
LOCKS 100000
DEF_TABLE_LOCKMODE row
RESIDENT -1
SHMBASE 0x44000000L
SHMVIRTSIZE 201376
SHMADD 32768
EXTSHMADD 20480
SHMTOTAL 6291456
SHMVIRT_ALLOCSEG 0,3
SHMNOACCESS
CKPTINTVL 300
RTO_SERVER_RESTART 0
BLOCKTIMEOUT 3600
CONVERSION_GUARD 0
RESTORE_POINT_DIR $INFORMIXDIR/tmp
TXTIMEOUT 300
DEADLOCK_TIMEOUT 60
HETERO_COMMIT 0
TAPEDEV /DBLOAD/TRASPASO-DB/backup/c0
TAPEBLK 1024
TAPESIZE 0
LTAPEDEV /apl/dev/cbfolder
LTAPEBLK 1024
LTAPESIZE 0
BAR_ACT_LOG $INFORMIXDIR/tmp/bar_act.log
BAR_DEBUG_LOG $INFORMIXDIR/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 $INFORMIXDIR/lib/libbsapsm.so
BACKUP_FILTER
RESTORE_FILTER
BAR_PERFORMANCE 0
BAR_CKPTSEC_TIMEOUT 15
PSM_DBS_POOL DBSPOOL
PSM_LOG_POOL LOGPOOL
DD_HASHSIZE 31
DD_HASHMAX 10
DS_HASHSIZE 31
DS_POOLSIZE 127
PC_HASHSIZE 31
PC_POOLSIZE 127
PRELOAD_DLL_FILE
STMT_CACHE 2
STMT_CACHE_HITS 2
STMT_CACHE_SIZE 512
STMT_CACHE_NOLIMIT 0
STMT_CACHE_NUMPOOL 1
USEOSTIME 0
STACKSIZE 64
ALLOW_NEWLINE 0
USELASTCOMMITTED "NONE"
FILLFACTOR 90
MAX_FILL_DATA_PAGES 0
BTSCANNER num=1,threshold=5000,rangesize=-1,alice=6,compression=default
ONLIDX_MAXMEM 5242880
MAX_PDQPRIORITY 0
DS_MAX_QUERIES 5
DS_TOTAL_MEMORY 26214400
DS_MAX_SCANS 5
DS_NONPDQ_QUERY_MEM 1310720
DATASKIP
OPTCOMPIND 0
DIRECTIVES 1
EXT_DIRECTIVES 2
OPT_GOAL -1
IFX_FOLDVIEW 1
STATCHANGE 0
USTLOW_SAMPLE 1
BATCHEDREAD_TABLE 1
BATCHEDREAD_INDEX 1
EXPLAIN_STAT 1
IFX_EXTEND_ROLE 1
SECURITY_LOCALCONNECTION
UNSECURE_ONSTAT
ADMIN_USER_MODE_WITH_DBSA
ADMIN_MODE_USERS
SSL_KEYSTORE_LABEL
TLS_VERSION
PLCY_POOLSIZE 127
PLCY_HASHSIZE 31
USRC_POOLSIZE 127
USRC_HASHSIZE 31
SQL_LOGICAL_CHAR OFF
SEQ_CACHE_SIZE 10
ENCRYPT_HDR
ENCRYPT_SMX
ENCRYPT_CDR 0
ENCRYPT_CIPHERS
ENCRYPT_MAC
ENCRYPT_MACFILE
ENCRYPT_SWITCH
CDR_EVALTHREADS 1,2
CDR_DSLOCKWAIT 5
CDR_QUEUEMEM 4096
CDR_NIFCOMPRESS 0
CDR_SERIAL 0
CDR_DBSPACE
CDR_QHDR_DBSPACE
CDR_QDATA_SBSPACE
CDR_SUPPRESS_ATSRISWARN
CDR_DELAY_PURGE_DTC 0
CDR_LOG_LAG_ACTION ddrblock
CDR_LOG_STAGING_MAXSIZE 0
CDR_MAX_DYNAMIC_LOGS 0
GRIDCOPY_DIR $INFORMIXDIR
CDR_TSINSTANCEID 0
CDR_MAX_FLUSH_SIZE 50
CDR_AUTO_DISCOVER 0
CDR_MEM 0
DRAUTO 0
DRINTERVAL 0
HDR_TXN_SCOPE NEAR_SYNC
DRTIMEOUT 30
HA_ALIAS
HA_FOC_ORDER SDS,HDR,RSS
DRLOSTFOUND $INFORMIXDIR/etc/dr.lostfound
DRIDXAUTO 0
LOG_INDEX_BUILDS
SDS_ENABLE
SDS_TIMEOUT 20
SDS_TEMPDBS
SDS_PAGING
SDS_LOGCHECK 10
SDS_ALTERNATE NONE
SDS_FLOW_CONTROL 0
UPDATABLE_SECONDARY 0
FAILOVER_CALLBACK
FAILOVER_TX_TIMEOUT 0
TEMPTAB_NOLOG 0
DELAY_APPLY 0
STOP_APPLY 0
LOG_STAGING_DIR
RSS_FLOW_CONTROL 0
SMX_NUMPIPES 1
ENABLE_SNAPSHOT_COPY 0
SMX_COMPRESS 0
SMX_PING_INTERVAL 10
SMX_PING_RETRY 6
CLUSTER_TXN_SCOPE SERVER
ON_RECVRY_THREADS 2
OFF_RECVRY_THREADS 5
DUMPDIR $INFORMIXDIR/tmp
DUMPSHMEM 1
DUMPGCORE 0
DUMPCORE 0
DUMPCNT 1
ALARMPROGRAM $INFORMIXDIR/etc/alarmprogram.sh
ALRM_ALL_EVENTS 0
STORAGE_FULL_ALARM 600,3
SYSALARMPROGRAM $INFORMIXDIR/etc/evidence.sh
RAS_PLOG_SPEED 132717
RAS_LLOG_SPEED 5333
EILSEQ_COMPAT_MODE 0
QSTATS 0
WSTATS 0
USERMAPPING OFF
SP_AUTOEXPAND 1
SP_THRESHOLD 0
SP_WAITTIME 30
AUTOLOCATE 0
DEFAULTESCCHAR \
MQSERVER
MQCHLLIB
MQCHLTAB
REMOTE_SERVER_CFG
REMOTE_USERS_CFG
S6_USE_REMOTE_SERVER_CFG 0
LOW_MEMORY_RESERVE 0
LOW_MEMORY_MGR 0
GSKIT_VERSION
INFORMIXCONTIME 60
INFORMIXCONRETRY 1
JVPPROPFILE $INFORMIXDIR/extend/krakatoa/.jvpprops
JVPLOGFILE $INFORMIXDIR/tmp/jvp.log
JVPARGS -Dcom.ibm.tools.attach.enable=no
JVPCLASSPATH INFORMIXDIR/extend/krakatoa/jdbc.jar
BUFFERPOOL default,buffers=10000,lrus=10,lru_min_dirty=20,lru_max_dirty=30
BUFFERPOOL size=2k,buffers=1048576,lrus=21,lru_min_dirty=20,lru_max_dirty=30
AUTO_READAHEAD 1,16

--------------------------------------------------------------------------------
Instance LOG:
------------
13:17:13 Maximum server connections 3
13:17:13 Checkpoint Statistics - Avg. Txn Block Time 0.000, # Txns blocked 0,
Plog used 43, Llog used 48

13:18:54 Parameter's user-configured value was adjusted. (DS_MAX_SCANS)
13:18:54 Parameter's user-configured value was adjusted. (ONLIDX_MAXMEM)
13:18:54 Parameter's user-configured value was adjusted. (DS_TOTAL_MEMORY)
13:18:54 IBM Informix Dynamic Server Started.
13:18:54 Requested shared memory segment size rounded from 16788KB to 18432KB
13:18:54 Shared memory segment will use huge pages.
13:18:54 Segment locked: addr=0x44000000, size=18874368
13:18:54 Requested shared memory segment size rounded from 201376KB to
202752KB
13:18:54 Shared memory segment will use huge pages.
13:18:54 Segment locked: addr=0x45200000, size=207618048

Fri Oct 23 13:18:56 2015

13:18:56 Requested shared memory segment size rounded from 2302208KB to
2304000KB
13:18:56 Shared memory segment will use huge pages.
13:18:56 Segment locked: addr=0x51800000, size=2359296000
13:18:57 Successfully added a bufferpool of page size 2K.

13:18:57 Event alarms enabled. ALARMPROG = '/apl/informix/etc/alarmprogram.sh'
13:18:58 Booting Language <c> from module <>
13:18:58 Loading Module <CNULL>
13:18:58 Booting Language <builtin> from module <>
13:18:58 Loading Module <BUILTINNULL>
13:19:03 CCFLAGS2 value set to 0x200
13:19:03 SQL_FEAT_CTRL value set to 0x8008
13:19:03 SQL_DEF_CTRL value set to 0x4b0
13:19:03 DR: DRAUTO is 0 (Off)
13:19:03 DR: ENCRYPT_HDR is 0 (HDR encryption Disabled)
13:19:03 Event notification facility epoll enabled.
13:19:04 IBM Informix Dynamic Server Version 12.10.FC5WE Software Serial
Number AAA#B000000
13:19:05 IBM Informix Dynamic Server Initialized -- Shared Memory Initialized.

13:19:05 Started 1 B-tree scanners.
13:19:05 B-tree scanner threshold set at 5000.
13:19:05 B-tree scanner range scan size set to -1.
13:19:05 B-tree scanner ALICE mode set to 6.
13:19:05 B-tree scanner index compression level set to med.
13:19:05 Physical Recovery Started at Page (47:165379).
13:19:05 Physical Recovery Complete: 7 Pages Examined, 7 Pages Restored.
13:19:05 Logical Recovery Started.
13:19:05 12 recovery worker threads will be started.
13:19:06 Logical Recovery has reached the transaction cleanup phase.
13:19:06 Logical Recovery Complete.

0 Committed, 0 Rolled Back, 0 Open, 0 Bad Locks

13:19:08 Dataskip is now OFF for all dbspaces
13:19:09 Dropping temporary TBLspace 0x100224, recovering 8 pages.
13:19:10 Checkpoint Completed: duration was 1 seconds.
13:19:10 Fri Oct 23 - loguniq 7651, logpos 0x901190, timestamp: 0x470c4d17
Interval: 8491

13:19:10 Maximum server connections 0
13:19:10 Checkpoint Statistics - Avg. Txn Block Time 0.000, # Txns blocked 0,
Plog used 19, Llog used 1

13:19:10 On-Line Mode
13:19:11 SCHAPI: Started dbScheduler thread.
13:19:11 Booting Language <spl> from module <>
13:19:11 Loading Module <SPLNULL>
13:19:11 Auto Registration is synced
13:19:11 SCHAPI: Started 2 dbWorker threads.
13:19:13 Defragmenter cleaner thread now running
13:19:13 Defragmenter cleaner thread cleaned:0 partitions
13:24:09 Checkpoint Completed: duration was 0 seconds.
13:24:09 Fri Oct 23 - loguniq 7651, logpos 0x92a258, timestamp: 0x4717669d
Interval: 8492

13:24:09 Maximum server connections 3
13:24:09 Checkpoint Statistics - Avg. Txn Block Time 0.000, # Txns blocked 0,
Plog used 55, Llog used 41

13:29:09 Checkpoint Completed: duration was 0 seconds.
13:29:09 Fri Oct 23 - loguniq 7651, logpos 0x92d398, timestamp: 0x4728e592
Interval: 8493

13:29:09 Maximum server connections 3
13:29:09 Checkpoint Statistics - Avg. Txn Block Time 0.000, # Txns blocked 0,
Plog used 20, Llog used 3

13:39:09 Checkpoint Completed: duration was 0 seconds.
13:39:09 Fri Oct 23 - loguniq 7651, logpos 0x92f018, timestamp: 0x476dcb0f
Interval: 8494

13:39:09 Maximum server connections 3
13:39:09 Checkpoint Statistics - Avg. Txn Block Time 0.000, # Txns blocked 0,
Plog used 6, Llog used 2

13:54:09 Checkpoint Completed: duration was 0 seconds.
13:54:09 Fri Oct 23 - loguniq 7651, logpos 0x931018, timestamp: 0x47c8589f
Interval: 8495

13:54:09 Maximum server connections 3
13:54:09 Checkpoint Statistics - Avg. Txn Block Time 0.000, # Txns blocked 0,
Plog used 3, Llog used 2

14:04:09 Checkpoint Completed: duration was 0 seconds.

--------------------------------------------------------------------------------
Virtual Machine:
1 vsocket 6vcores w/hyperthreading => 12vCPU
16GB RAM
O.S. Disk: 2GB vmdk
Data Disk: 167GB MRL
SCSI Controller: paravirtual

--------------------------------------------------------------------------------

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

Messages In This Thread

• Performance issue after migrate to IDS from Online
  ANTONIO DIAZ -- Friday, 23 October 2015, at 9:52 a.m.
  • Re: Performance issue after migrate to IDS from On
    Madison Pruet -- Friday, 23 October 2015, at 10:07 a.m.
    • Re: Performance issue after migrate to IDS from On
      ANTONIO DIAZ -- Friday, 23 October 2015, at 3:18 p.m.
  • Re: Performance issue after migrate to IDS from On
    Art Kagel -- Friday, 23 October 2015, at 10:16 a.m.
    • Re: Performance issue after migrate to IDS from On
      ANTONIO DIAZ -- Friday, 23 October 2015, at 4:08 p.m.
      • Re: Performance issue after migrate to IDS from On
        ANTONIO DIAZ -- Saturday, 24 October 2015, at 4:32 a.m.
        • Re: Performance issue after migrate to IDS from On
          BENJAMIN THOMPSON -- Tuesday, 27 October 2015, at 8:10 a.m.
          • Re: Performance issue after migrate to IDS from On
            ANTONIO DIAZ -- Tuesday, 27 October 2015, at 4:53 p.m.
            • Re: Performance issue after migrate to IDS from On
              Art Kagel -- Tuesday, 27 October 2015, at 5:19 p.m.
            • Re: Performance issue after migrate to IDS from On
              BENJAMIN THOMPSON -- Wednesday, 28 October 2015, at 9:28 a.m.
              • Re: Performance issue after migrate to IDS from On
                ANTONIO DIAZ -- Wednesday, 28 October 2015, at 4:29 p.m.
                • Re: Performance issue after migrate to IDS from On
                  ANTONIO DIAZ -- Thursday, 29 October 2015, at 4:49 a.m.
          • Re: Performance issue after migrate to IDS from On
            ANTONIO DIAZ -- Tuesday, 27 October 2015, at 5:12 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.