Questions:
1. After the database was recreated and loaded did you run the
recommended suite of Update Statistics commands as described in the
Performance Guide manual (or as implemented by my dostats utility) or are
you depending on Auto Update Statistics (AUS)?
2. So, you mention using dbload to reload the data from the source
server. I assume that you manually recreated the database using a dbschema
output from online. Did you use -ss when you ran the dbschema? Did you
adjust the EXTENT SIZE and NEXT SIZE settings documented for the tables to
take account of the size of the tables at the point they were exported?
Requests:
Post the performance stats listed below and we'll see if we can help:
1. Post the output from:
onstat -d
onstat -p
onstat -g glo
onstat -g iov
onstat -g iof
2. Post the output from:
select first 50 dbsname, tabname, count(*), sum(size)
from sysextents
where dbsname = 'art'
group by 1, 2
order by 3 desc;
FYI: You cannot successfully attach anything to a post to the forum, so
you'll have to copy/paste the output into the body of the posting.
Art
Art S. Kagel, President and Principal Consultant
ASK Database Management
www.askdbmgt.com
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on the IIUG, nor any other organization with which I am
associated either explicitly, implicitly, or by inference. Neither do
those opinions reflect those of other individuals affiliated with any
entity with which I am affiliated nor those of the entities themselves.
On Fri, Oct 23, 2015 at 9:52 AM, <> 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.
>
>
--001a1142d85e4ce3d90522c6402b
