Physical Log Size
User on IDS 11.50 (Windows 2008 64-bit) kept getting startup "Performance Advisory: physical log too small / automatic checkpoints disabled" messages recommending 28000 KB, even after repeatedly raising PHYSFILE in the ONCONFIG. Art Kagel and Mirav Kapadia explained that editing ONCONFIG alone doesn't resize the physical log — you must use onparams (or the alter plog admin API), or reinitialize. Setting PHYSFILE to 28000, running "onparams -p -s 28000" and taking a level-0 archive cleared the warnings. The thread then drifts into tuning advice (buffers, BR/BTR/RAU ratios, Art's ratios.shr_ak script on the IIUG ftp site).
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
IDS 11.50.FC4WE Windows Server 2008 64bit
I keep getting the message when I restart the Informix instance that the
physical log file might be too small. It recommends 28000.
I increased the PHYSFILE from 15000 all the way up to 800000, but I am still
getting this message in the log. (I did this in increments but am afraid to go
too far)
What am I missing?
Thanks
Log file
=====================================
Tue Jan 25 12:51:02 2011
12:51:02 Warning: ONCONFIG dump directory (DUMPDIR)
'D:\\\\PROGRA~1\\\\IBM\\\\IBMINF~1\\\\11.50\\\\tmp' has insecure permissions
12:51:02 Booting Language <c> from module <>
12:51:02 Loading Module <CNULL>
12:51:02 Booting Language <builtin> from module <>
12:51:02 Loading Module <BUILTINNULL>
12:51:02 Could not disable priority aging: errno = 0
12:51:02 Affinitied VP 1 to phys proc 1
12:51:03 Could not disable priority aging: errno = 0
12:51:03 Affinitied VP 3 to phys proc 2
12:51:03 Could not disable priority aging: errno = 0
12:51:03 Affinitied VP 4 to phys proc 3
12:51:04 Could not disable priority aging: errno = 0
12:51:04 Affinitied VP 5 to phys proc 4
12:51:07 DR: DRAUTO is 0 (Off)
12:51:07 DR: ENCRYPT_HDR is 0 (HDR encryption Disabled)
12:51:07 IBM Informix Dynamic Server Version 11.50.FC4WE Software Serial
Number AAA#B000000
12:51:08 IBM Informix Dynamic Server Initialized -- Shared Memory Initialized.
12:51:08 Started 1 B-tree scanners.
12:51:09 B-tree scanner threshold set at 5000.
12:51:09 B-tree scanner range scan size set to -1.
12:51:09 B-tree scanner ALICE mode set to 6.
12:51:09 B-tree scanner index compression level set to med.
12:51:09 Physical Recovery Started at Page (1:2127).
12:51:09 Physical Recovery Complete: 0 Pages Examined, 0 Pages Restored.
12:51:09 Logical Recovery Started.
12:51:09 10 recovery worker threads will be started.
12:51:10 Logical Recovery has reached the transaction cleanup phase.
12:51:10 Logical Recovery Complete.
0 Committed, 0 Rolled Back, 0 Open, 0 Bad Locks
12:51:11 Dataskip is now OFF for all dbspaces
12:51:12 Init operation complete - Mode Online
12:51:13 Checkpoint Completed: duration was 1 seconds.
12:51:14 Tue Jan 25 - loguniq 34022, logpos 0xaa018, timestamp: 0x2b38d818
Interval: 63728
12:51:14 Maximum server connections 0
12:51:14 Checkpoint Statistics - Avg. Txn Block Time 0.000, # Txns blocked 0,
Plog used 2, Llog used 1
12:51:14 On-Line Mode
12:51:14 SCHAPI: Started dbScheduler thread.
12:51:15 Booting Language <spl> from module <>
12:51:15 Loading Module <SPLNULL>
12:51:15 SCHAPI: Started 2 dbWorker threads.
12:51:17 Performance Advisory: Based on the current workload, the physical log
might be too small to
accommodate the time it takes to flush the buffer pool.
12:51:18 Results: The server might block transactions during checkpoints.
12:51:18 Action: If transactions are blocked during the checkpoint, increase
the size of the
physical log to at least 28000 KB.
12:51:18 Performance Advisory: The physical log is too small for automatic
checkpoints.
12:51:18 Results: Automatic checkpoints are disabled.
12:51:18 Action: To enable automatic checkpoints, increase the physical log to
at least 28000 KB.
=================================
ONCONFIG file
=================================
ROOTNAME rootdbs
ROOTPATH D:\\\\IFMXDATA\\\\ol_shustest\\\\rootdbs_dat.000 # Path for device containingroot dbspace
ROOTOFFSET 0
ROOTSIZE 204800 # Size of root dbspace (Kbytes)
MIRROR 0 # Mirroring flag (Yes = 1, No = 0)
MIRRORPATH # Path for device containing mirrored root
MIRROROFFSET 0
PHYSFILE 800000 #was 15000
PLOG_OVERFLOW_PATH
PHYSBUFF 128
LOGFILES 22
LOGSIZE 5000
DYNAMIC_LOGS 2
LOGBUFF 64
LTXHWM 70
LTXEHWM 80
MSGPATH D:\\\\PROGRA~1\\\\IBM\\\\IBMINF~1\\\\11.50\\\\ol_shustest.log # System message log
file path
CONSOLE D:\\\\PROGRA~1\\\\IBM\\\\IBMINF~1\\\\11.50\\\\conol_shustest.log # System console
message path
TBLTBLFIRST 0
TBLTBLNEXT 0
TBLSPACE_STATS 1
DBSPACETEMP
SBSPACETEMP
SBSPACENAME sbspace # Default sbspace
SYSSBSPACENAME sbspace # Default System sbspace
ONDBSPACEDOWN 2
SERVERNUM 0 # Unique id corresponding to a server instance
DBSERVERNAME ol_shustest # Name of default Dynamic Server
DBSERVERALIASES # List of alternate dbserver names
NETTYPE
LISTEN_TIMEOUT 60
MAX_INCOMPLETE_CONNECTIONS 1024
FASTPOLL 1
MULTIPROCESSOR 1
VPCLASS cpu,num=15,noage
VP_MEMORY_CACHE_KB 0
SINGLE_CPU_VP 0
#VPCLASS aio,num=1
CLEANERS 8AUTO_AIOVPS 1
DIRECT_IO 0
LOCKS 200000
DEF_TABLE_LOCKMODE page
RESIDENT 0
SHMBASE 0x80000000L
SHMVIRTSIZE 120704 #was 3017
SHMADD 120704
EXTSHMADD 8192
SHMTOTAL 0
SHMVIRT_ALLOCSEG 0,3#SHMNOACCESS 0x70000000-0x7FFFFFFF
CKPTINTVL 300AUTO_CKPTS 1
RTO_SERVER_RESTART 0
BLOCKTIMEOUT 3600
TXTIMEOUT 300
DEADLOCK_TIMEOUT 60
HETERO_COMMIT 0
TAPEDEV NUL # \\\\\\\\.\\\\TAPE0
TAPEBLK 16
TAPESIZE 0
LTAPEDEV NUL
LTAPEBLK 16
LTAPESIZE 0
BAR_ACT_LOG D:\\\\PROGRA~1\\\\IBM\\\\IBMINF~1\\\\11.50\\\\bar_ol_shustest.log #Path of log
file for onbar.exe
BAR_DEBUG_LOG D:\\\\PROGRA~1\\\\IBM\\\\IBMINF~1\\\\11.50\\\\bar_ol_shustest.log #Path of the
debug log for onbar.exe
BAR_DEBUG 0
BAR_MAX_BACKUP 0
BAR_RETRY 1
BAR_NB_XPORT_COUNT 20
BAR_XFER_BUF_SIZE 15
RESTARTABLE_RESTORE ON
BAR_PROGRESS_FREQ 0
BAR_BSALIB_PATH d:\\\\ISM\\\\2.20\\\\bin\\\\libbsa.dll # Location of ISM XBSA DLL
BACKUP_FILTER
RESTORE_FILTER
BAR_PERFORMANCE 0
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 NONE
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
DS_TOTAL_MEMORY
DS_MAX_SCANS 1048576
DS_NONPDQ_QUERY_MEM 128
DATASKIP
OPTCOMPIND 0
DIRECTIVES 1
EXT_DIRECTIVES 0
OPT_GOAL -1
IFX_FOLDVIEW 0AUTO_REPREPARE 1
RA_PAGES 128
RA_THRESHOLD 120
EXPLAIN_STAT 1
#SQLTRACE level=low,ntraces=1000,size=2,mode=global
#DBCREATE_PERMISSION informix
#DB_LIBRARY_PATH
IFX_EXTEND_ROLE 1
SECURITY_LOCALCONNECTION
UNSECURE_ONSTAT
ADMIN_USER_MODE_WITH_DBSA
ADMIN_MODE_USERS
SSL_KEYSTORE_LABEL
PLCY_POOLSIZE 127
PLCY_HASHSIZE 31
USRC_POOLSIZE 127
USRC_HASHSIZE 31
STAGEBLOB
OPCACHEMAX 0
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_MAX_DYNAMIC_LOGS 0
CDR_SUPPRESS_ATSRISWARN
DRAUTO 0
DRINTERVAL 30
DRTIMEOUT 30
HA_ALIAS
DRLOSTFOUND $INFO
How did you "increase" the size? You can only do that song onparams. You
cannot do that just by changing the Onconfig file
Art
On Jan 25, 2011 12:10 PM, "KENT GRUNDEN" <k.grunden@hoogwegtus.com> wrote:
> IDS 11.50.FC4WE Windows Server 2008 64bit
>
> I keep getting the message when I restart the Informix instance that the
> physical log file might be too small. It recommends 28000.
> I increased the PHYSFILE from 15000 all the way up to 800000, but I am
still
> getting this message in the log. (I did this in increments but am afraid
to go
> too far)
> What am I missing?
> Thanks
>
> Log file
> =====================================
> Tue Jan 25 12:51:02 2011
>
> 12:51:02 Warning: ONCONFIG dump directory (DUMPDIR)
> 'D:\\\\PROGRA~1\\\\IBM\\\\IBMINF~1\\\\11.50\\\\tmp' has insecure permissions
> 12:51:02 Booting Language <c> from module <>
> 12:51:02 Loading Module <CNULL>
> 12:51:02 Booting Language <builtin> from module <>
> 12:51:02 Loading Module <BUILTINNULL>
> 12:51:02 Could not disable priority aging: errno = 0
> 12:51:02 Affinitied VP 1 to phys proc 1
> 12:51:03 Could not disable priority aging: errno = 0
> 12:51:03 Affinitied VP 3 to phys proc 2
> 12:51:03 Could not disable priority aging: errno = 0
> 12:51:03 Affinitied VP 4 to phys proc 3
> 12:51:04 Could not disable priority aging: errno = 0
> 12:51:04 Affinitied VP 5 to phys proc 4
> 12:51:07 DR: DRAUTO is 0 (Off)
> 12:51:07 DR: ENCRYPT_HDR is 0 (HDR encryption Disabled)
> 12:51:07 IBM Informix Dynamic Server Version 11.50.FC4WE Software Serial
> Number AAA#B000000
> 12:51:08 IBM Informix Dynamic Server Initialized -- Shared Memory
Initialized.
>
> 12:51:08 Started 1 B-tree scanners.
> 12:51:09 B-tree scanner threshold set at 5000.
> 12:51:09 B-tree scanner range scan size set to -1.
> 12:51:09 B-tree scanner ALICE mode set to 6.
> 12:51:09 B-tree scanner index compression level set to med.
> 12:51:09 Physical Recovery Started at Page (1:2127).
> 12:51:09 Physical Recovery Complete: 0 Pages Examined, 0 Pages Restored.
> 12:51:09 Logical Recovery Started.
> 12:51:09 10 recovery worker threads will be started.
> 12:51:10 Logical Recovery has reached the transaction cleanup phase.
> 12:51:10 Logical Recovery Complete.
>
> 0 Committed, 0 Rolled Back, 0 Open, 0 Bad Locks
>
> 12:51:11 Dataskip is now OFF for all dbspaces
> 12:51:12 Init operation complete - Mode Online
> 12:51:13 Checkpoint Completed: duration was 1 seconds.
> 12:51:14 Tue Jan 25 - loguniq 34022, logpos 0xaa018, timestamp: 0x2b38d818
> Interval: 63728
>
> 12:51:14 Maximum server connections 0
> 12:51:14 Checkpoint Statistics - Avg. Txn Block Time 0.000, # Txns blocked
0,
> Plog used 2, Llog used 1
>
> 12:51:14 On-Line Mode
> 12:51:14 SCHAPI: Started dbScheduler thread.
> 12:51:15 Booting Language <spl> from module <>
> 12:51:15 Loading Module <SPLNULL>
> 12:51:15 SCHAPI: Started 2 dbWorker threads.
> 12:51:17 Performance Advisory: Based on the current workload, the physical
log
> might be too small to
> accommodate the time it takes to flush the buffer pool.
> 12:51:18 Results: The server might block transactions during checkpoints.
> 12:51:18 Action: If transactions are blocked during the checkpoint,
increase
> the size of the
> physical log to at least 28000 KB.
> 12:51:18 Performance Advisory: The physical log is too small for automatic
> checkpoints.
> 12:51:18 Results: Automatic checkpoints are disabled.
> 12:51:18 Action: To enable automatic checkpoints, increase the physical
log to
> at least 28000 KB.
> =================================
>
> ONCONFIG file
> =================================
>
> ROOTNAME rootdbs
> ROOTPATH D:\\\\IFMXDATA\\\\ol_shustest\\\\rootdbs_dat.000 # Path for devicecontaining
> root dbspace
> ROOTOFFSET 0
> ROOTSIZE 204800 # Size of root dbspace (Kbytes)
> MIRROR 0 # Mirroring flag (Yes = 1, No = 0)
> MIRRORPATH # Path for device containing mirrored root
> MIRROROFFSET 0
>
> PHYSFILE 800000 #was 15000
> PLOG_OVERFLOW_PATH
> PHYSBUFF 128
>
> LOGFILES 22
> LOGSIZE 5000
> DYNAMIC_LOGS 2
> LOGBUFF 64
>
> LTXHWM 70
> LTXEHWM 80>
> MSGPATH D:\\\\PROGRA~1\\\\IBM\\\\IBMINF~1\\\\11.50\\\\ol_shustest.log # System message
log
> file path
> CONSOLE D:\\\\PROGRA~1\\\\IBM\\\\IBMINF~1\\\\11.50\\\\conol_shustest.log # System console
> message path
>
> TBLTBLFIRST 0
> TBLTBLNEXT 0
> TBLSPACE_STATS 1
>
> DBSPACETEMP
> SBSPACETEMP
>
> SBSPACENAME sbspace # Default sbspace
> SYSSBSPACENAME sbspace # Default System sbspace
> ONDBSPACEDOWN 2
>
> SERVERNUM 0 # Unique id corresponding to a server instance
> DBSERVERNAME ol_shustest # Name of default Dynamic Server
> DBSERVERALIASES # List of alternate dbserver names
>
> NETTYPE
> LISTEN_TIMEOUT 60
> MAX_INCOMPLETE_CONNECTIONS 1024
> FASTPOLL 1
>
> MULTIPROCESSOR 1
> VPCLASS cpu,num=15,noage
> VP_MEMORY_CACHE_KB 0
> SINGLE_CPU_VP 0>
> #VPCLASS aio,num=1
> CLEANERS 8> AUTO_AIOVPS 1
> DIRECT_IO 0
>
> LOCKS 200000
> DEF_TABLE_LOCKMODE page
>
> RESIDENT 0
> SHMBASE 0x80000000L
> SHMVIRTSIZE 120704 #was 3017
> SHMADD 120704
> EXTSHMADD 8192
> SHMTOTAL 0
> SHMVIRT_ALLOCSEG 0,3> #SHMNOACCESS 0x70000000-0x7FFFFFFF
>
> CKPTINTVL 300> AUTO_CKPTS 1
> RTO_SERVER_RESTART 0
> BLOCKTIMEOUT 3600
>
> TXTIMEOUT 300
> DEADLOCK_TIMEOUT 60
> HETERO_COMMIT 0
>
> TAPEDEV NUL # \\\\\\\\.\\\\TAPE0
> TAPEBLK 16
> TAPESIZE 0
>
> LTAPEDEV NUL
> LTAPEBLK 16
> LTAPESIZE 0>
> BAR_ACT_LOG D:\\\\PROGRA~1\\\\IBM\\\\IBMINF~1\\\\11.50\\\\bar_ol_shustest.log #Path of
log
> file for onbar.exe
> BAR_DEBUG_LOG D:\\\\PROGRA~1\\\\IBM\\\\IBMINF~1\\\\11.50\\\\bar_ol_shustest.log #Path of
the
> debug log for onbar.exe
> BAR_DEBUG 0
> BAR_MAX_BACKUP 0
> BAR_RETRY 1
> BAR_NB_XPORT_COUNT 20
> BAR_XFER_BUF_SIZE 15
> RESTARTABLE_RESTORE ON
> BAR_PROGRESS_FREQ 0
> BAR_BSALIB_PATH d:\\\\ISM\\\\2.20\\\\bin\\\\libbsa.dll # Location of ISM XBSA DLL
> BACKUP_FILTER
> RESTORE_FILTER
> BAR_PERFORMANCE 0>
> 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 NONE
>
> 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
> DS_TOTAL_MEMORY
> DS_MAX_SCANS 1048576
> DS_NONPDQ_QUERY_MEM 128
> DATASKIP
>
> OPTCOMPIND 0
> DIRECTIVES 1
> EXT_DIRECTIVES 0
> OPT_GOAL -1
> IFX_FOLDVIEW 0> AUTO_REPREPARE 1
>
> RA_PAGES 128
> RA_THRESHOLD 120
>
> EXPLAIN_STAT 1
> #SQLTRACE level=low,ntraces=1000,size=2,mode=global>
> #DBCREATE_PERMISSION informix
> #DB_LIBRARY_PATH
> IFX
Since, you have updated the size in onconfig file, probably you are mis=
sing
complete disk initialization (-i).
Another option - you could change the physical log size/location in onl=
ine
mode with onparams/SQL Admin API "alter plog".
Regards,
-- Mirav
Mirav Kapadia (mirav@us.ibm.com)
Informix Embed, Virtualization & Cloud
Computing Team
Phone: +1 (913) 599-8456 | Tieline:
337-8456
From: "KENT GRUNDEN" <k.grunden@hoogwegtus.com>
To: ids@iiug.org
Date: 01/25/2011 01:12 PM
Subject: Physical Log Size [22556]
Sent by: ids-bounces@iiug.org
IDS 11.50.FC4WE Windows Server 2008 64bit
I keep getting the message when I restart the Informix instance that th=
e
physical log file might be too small. It recommends 28000.
I increased the PHYSFILE from 15000 all the way up to 800000, but I am
still
getting this message in the log. (I did this in increments but am afrai=
d to
go
too far)
What am I missing?
Thanks
Log file
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
Tue Jan 25 12:51:02 2011
12:51:02 Warning: ONCONFIG dump directory (DUMPDIR)
'D:\\\\PROGRA~1\\\\IBM\\\\IBMINF~1\\\\11.50\\\\tmp' has insecure permissions
12:51:02 Booting Language <c> from module <>
12:51:02 Loading Module <CNULL>
12:51:02 Booting Language <builtin> from module <>
12:51:02 Loading Module <BUILTINNULL>
12:51:02 Could not disable priority aging: errno =3D 0
12:51:02 Affinitied VP 1 to phys proc 1
12:51:03 Could not disable priority aging: errno =3D 0
12:51:03 Affinitied VP 3 to phys proc 2
12:51:03 Could not disable priority aging: errno =3D 0
12:51:03 Affinitied VP 4 to phys proc 3
12:51:04 Could not disable priority aging: errno =3D 0
12:51:04 Affinitied VP 5 to phys proc 4
12:51:07 DR: DRAUTO is 0 (Off)
12:51:07 DR: ENCRYPT_HDR is 0 (HDR encryption Disabled)
12:51:07 IBM Informix Dynamic Server Version 11.50.FC4WE Software Seria=
l
Number AAA#B000000
12:51:08 IBM Informix Dynamic Server Initialized -- Shared Memory
Initialized.
12:51:08 Started 1 B-tree scanners.
12:51:09 B-tree scanner threshold set at 5000.
12:51:09 B-tree scanner range scan size set to -1.
12:51:09 B-tree scanner ALICE mode set to 6.
12:51:09 B-tree scanner index compression level set to med.
12:51:09 Physical Recovery Started at Page (1:2127).
12:51:09 Physical Recovery Complete: 0 Pages Examined, 0 Pages Restored=
.
12:51:09 Logical Recovery Started.
12:51:09 10 recovery worker threads will be started.
12:51:10 Logical Recovery has reached the transaction cleanup phase.
12:51:10 Logical Recovery Complete.
0 Committed, 0 Rolled Back, 0 Open, 0 Bad Locks
12:51:11 Dataskip is now OFF for all dbspaces
12:51:12 Init operation complete - Mode Online
12:51:13 Checkpoint Completed: duration was 1 seconds.
12:51:14 Tue Jan 25 - loguniq 34022, logpos 0xaa018, timestamp: 0x2b38d=
818
Interval: 63728
12:51:14 Maximum server connections 0
12:51:14 Checkpoint Statistics - Avg. Txn Block Time 0.000, # Txns bloc=
ked
0,
Plog used 2, Llog used 1
12:51:14 On-Line Mode
12:51:14 SCHAPI: Started dbScheduler thread.
12:51:15 Booting Language <spl> from module <>
12:51:15 Loading Module <SPLNULL>
12:51:15 SCHAPI: Started 2 dbWorker threads.
12:51:17 Performance Advisory: Based on the current workload, the physi=
cal
log
might be too small to
accommodate the time it takes to flush the buffer pool.
12:51:18 Results: The server might block transactions during checkpoint=
s.
12:51:18 Action: If transactions are blocked during the checkpoint,
increase
the size of the
physical log to at least 28000 KB.
12:51:18 Performance Advisory: The physical log is too small for automa=
tic
checkpoints.
12:51:18 Results: Automatic checkpoints are disabled.
12:51:18 Action: To enable automatic checkpoints, increase the physical=
log
to
at least 28000 KB.
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D
ONCONFIG file
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D
ROOTNAME rootdbs
ROOTPATH D:\\\\IFMXDATA\\\\ol_shustest\\\\rootdbs_dat.000 # Path for devicecontaining
root dbspace
ROOTOFFSET 0
ROOTSIZE 204800 # Size of root dbspace (Kbytes)
MIRROR 0 # Mirroring flag (Yes =3D 1, No =3D 0)
MIRRORPATH # Path for device containing mirrored root
MIRROROFFSET 0
PHYSFILE 800000 #was 15000
PLOG_OVERFLOW_PATH
PHYSBUFF 128
LOGFILES 22
LOGSIZE 5000
DYNAMIC_LOGS 2
LOGBUFF 64
LTXHWM 70
LTXEHWM 80
MSGPATH D:\\\\PROGRA~1\\\\IBM\\\\IBMINF~1\\\\11.50\\\\ol_shustest.log # System message=
log
file path
CONSOLE D:\\\\PROGRA~1\\\\IBM\\\\IBMINF~1\\\\11.50\\\\conol_shustest.log # System cons=
ole
message path
TBLTBLFIRST 0
TBLTBLNEXT 0
TBLSPACE_STATS 1
DBSPACETEMP
SBSPACETEMP
SBSPACENAME sbspace # Default sbspace
SYSSBSPACENAME sbspace # Default System sbspace
ONDBSPACEDOWN 2
SERVERNUM 0 # Unique id corresponding to a server instance
DBSERVERNAME ol_shustest # Name of default Dynamic Server
DBSERVERALIASES # List of alternate dbserver names
NETTYPE
LISTEN_TIMEOUT 60
MAX_INCOMPLETE_CONNECTIONS 1024
FASTPOLL 1
MULTIPROCESSOR 1
VPCLASS cpu,num=3D15,noage
VP_MEMORY_CACHE_KB 0
SINGLE_CPU_VP 0
#VPCLASS aio,num=3D1
CLEANERS 8AUTO_AIOVPS 1
DIRECT_IO 0
LOCKS 200000
DEF_TABLE_LOCKMODE page
RESIDENT 0
SHMBASE 0x80000000L
SHMVIRTSIZE 120704 #was 3017
SHMADD 120704
EXTSHMADD 8192
SHMTOTAL 0
SHMVIRT_ALLOCSEG 0,3#SHMNOACCESS 0x70000000-0x7FFFFFFF
CKPTINTVL 300AUTO_CKPTS 1
RTO_SERVER_RESTART 0
BLOCKTIMEOUT 3600
TXTIMEOUT 300
DEADLOCK_TIMEOUT 60
HETERO_COMMIT 0
TAPEDEV NUL # \\\\\\\\.\\\\TAPE0
TAPEBLK 16
TAPESIZE 0
LTAPEDEV NUL
LTAPEBLK 16
LTAPESIZE 0
BAR_ACT_LOG D:\\\\PROGRA~1\\\\IBM\\\\IBMINF~1\\\\11.50\\\\bar_ol_shustest.log #Path of=
log
file for onbar.exe
BAR_DEBUG_LOG D:\\\\PROGRA~1\\\\IBM\\\\IBMINF~1\\\\11.50\\\\bar_ol_shustest.log #Path =
of
the
debug log for onbar.exe
BAR_DEBUG 0
BAR_MAX_BACKUP 0
BAR_RETRY 1
BAR_NB_XPORT_COUNT 20
BAR_XFER_BUF_SIZE 15
RESTARTABLE_RESTORE ON
BAR_PROGRESS_FREQ 0
BAR_BSALIB_PATH d:\\\\ISM\\\\2.20\\\\bin\\\\libbsa.dll # Location of ISM XBSA DLL
BACKUP_FILTER
RESTORE_FILTER
BAR_PERFORMANCE 0
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 NONE
FILLFACTOR 90
MAX_FILL_DATA_PAGES 0
BTSCANNER num=3D1,threshold=3D5000,rangesize=3D-1,alice=3D6,compression=
=3Ddefault
ONLIDX_MAXMEM 5120
MAX_PDQPRIORITY 100
DS_MAX_QUERIES
DS_TOTAL_MEMORY
DS_MAX_SCANS 1048576
DS_NO
Thanks Art, You are Fantastic! I had a feeling that it was something simple I was overlooking. I changed the PHYSFILE setting to 28000, ran ONPARAMS -p -s 28000 and did a level 0 archive. No warnings on restart. Informix has not been looked at for a long time and I feel it could run faster. Any suggested changes would be appreciated. Thanks again
Check your basic metrics - BR(bufwaits ratio), BTR (buffer turnover rate), RAU(readahead utilization). They will tell you about the three biggest effects on performance - buffer/lru contention, buffers, and readahead. Art On Jan 25, 2011 12:51 PM, "KENT GRUNDEN" <k.grunden@hoogwegtus.com> wrote: > Thanks Art, > You are Fantastic! > > I had a feeling that it was something simple I was overlooking. > I changed the PHYSFILE setting to 28000, ran ONPARAMS -p -s 28000 and > did a level 0 archive. > > No warnings on restart. > > Informix has not been looked at for a long time and I feel it could run > faster. > > Any suggested changes would be appreciated. > > Thanks again > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > --20cf3054a5b30cffb6049ab20db7
Art,
I doubled my SHMVIRTSIZE from 120704 to 241408,
Increased the BUFFERPOOL from 10000 to 50000
and increased the CLEANERS from 8 to 16
Here are my calculations for the metrics
BR = 5.009
BTR = 6.7479
RAU = 101.22 ??
I think they look OK and users have commented that the database is faster.
I still am a little concerned about my AIO and KIO readings
=================================================================
D:\\\\Program Files\\\\IBM\\\\IBM Informix Dynamic Server\\\\11.50>onstat -g iob
IBM Informix Dynamic Server Version 11.50.FC4WE -- On-Line -- Up 16:35:53 --483456 Kbytes
AIO big buffer usage summary:
class reads writes
pages ops pgs/op holes hl-ops hls/op pages ops pgs/op
drda_dbg 0 0 0.00 0 0 0.00 0 0 0.00
sqli_dbg 0 0 0.00 0 0 0.00 0 0 0.00
kio 6782521 2526233 2.68 720170 125095 5.76 22584 13825 1.63
adt 0 0 0.00 0 0 0.00 0 0 0.00
msc 0 0 0.00 0 0 0.00 0 0 0.00
aio 51280 3492 14.68 0 0 0.00 60288 4055 14.87
pio 0 0 0.00 0 0 0.00 0 0 0.00
lio 0 0 0.00 0 0 0.00 0 0 0.00
Do I need another aio thread??
=======================================================
D:\\\\Program Files\\\\IBM\\\\IBM Informix Dynamic Server\\\\11.50>onstat -g iov
IBM Informix Dynamic Server Version 11.50.FC4WE -- On-Line -- Up 16:40:14 --483456 Kbytes
AIO I/O vps:
class/vp s io/s totalops dskread dskwrite dskcopy wakeups io/wup errors tempops
kio 0 i 5.3 317058 315644 1414 0 83614965 0.0 0 0
kio 1 i 10.0 598030 591615 6415 0 341659363 0.0 0 0
kio 2 i 4.5 267545 263690 3855 0 72618941 0.0 0 0
kio 3 i 25.4 1522379 1520096 2283 0 557743124 0.0 0 0
msc 0 i 0.1 4410 0 0 0 4397 1.0 0 4410
aio 0 i 0.1 7974 3543 4063 0 7975 1.0 0 29
pio 0 i 0.0 0 0 0 0 1 0.0 0 0
lio 0 i 0.0 0 0 0 0 1 0.0 0 0
kio 3 seems to be doing much more work than the other kio's
=====================================================
D:\\\\Program Files\\\\IBM\\\\IBM Informix Dynamic Server\\\\11.50>onstat -g glo
IBM Informix Dynamic Server Version 11.50.FC4WE -- On-Line -- Up 16:42:03 --483456 Kbytes
MT global info:
sessions threads vps lngspins
141 177 11 0
sched calls thread switches yield 0 yield n yield forever
total: 0 0 9873401 1192617 1084139934
per sec: 0 0 0 0 0
Virtual processor summary:
class vps usercpu syscpu total
cpu 4 2589.60 829.07 3418.67
aio 1 0.02 1.17 1.18
lio 1 0.00 0.00 0.00
pio 1 0.00 0.00 0.00
adm 1 0.00 0.00 0.00
soc 2 0.00 0.00 0.00
msc 1 0.48 1.18 1.67
total 11 2590.10 831.42 3421.52
Individual virtual processors:
vp pid class usercpu syscpu total Thread Eff
1 2036 cpu 605.25 38.03 643.28 60126.65 1%
2 2352 adm 0.00 0.00 0.00 0.00 0%
3 2652 cpu 732.18 345.72 1077.90 60125.33 1%
4 2664 cpu 1090.40 396.98 1487.38 60125.29 2%
5 2672 cpu 161.77 48.33 210.10 60125.24 0%
6 2696 lio 0.00 0.00 0.00 0.01 0%
7 2732 pio 0.00 0.00 0.00 0.02 0%
8 2764 aio 0.02 1.17 1.18 60014.94 0%
9 2768 msc 0.48 1.18 1.67 60102.37 0%
10 2800 soc 0.00 0.00 0.00 NA NA
11 2804 soc 0.00 0.00 0.00 NA NA
tot 2590.10 831.42 3421.52
====================================================
D:\\\\Program Files\\\\IBM\\\\IBM Informix Dynamic Server\\\\11.50>onstat -g iob
IBM Informix Dynamic Server Version 11.50.FC4WE -- On-Line -- Up 16:42:13 --483456 Kbytes
AIO big buffer usage summary:
class reads writes
pages ops pgs/op holes hl-ops hls/op pages ops pgs/op
drda_dbg 0 0 0.00 0 0 0.00 0 0 0.00
sqli_dbg 0 0 0.00 0 0 0.00 0 0 0.00
kio 7359019 2787326 2.64 777719 134603 5.78 22922 13967 1.64
adt 0 0 0.00 0 0 0.00 0 0 0.00
msc 0 0 0.00 0 0 0.00 0 0 0.00
aio 51872 3529 14.70 0 0 0.00 60416 4063 14.87
pio 0 0 0.00 0 0 0.00 0 0 0.00
lio 0 0 0.00 0 0 0.00 0 0 0.00
Does this mean there are no ready threads??
=======================================================
D:\\\\Program Files\\\\IBM\\\\IBM Informix Dynamic Server\\\\11.50>onstat -g rea
IBM Informix Dynamic Server Version 11.50.FC4WE -- On-Line -- Up 16:42:22 --483456 Kbytes
Ready threads:
tid tcb rstcb prty status vp-class name
Thanks
Kent
Metrics look good. More buffers will probably help a bot more as s BTR over
6 is ok but bit high, so try increasing buffers again.
The RAU is over 100 because you got the formula from an old post where I did
it backwards. Divide the used by the sum of the other RA values.
Art
On Jan 26, 2011 9:40 AM, "KENT GRUNDEN" <k.grunden@hoogwegtus.com> wrote:
> Art,
> I doubled my SHMVIRTSIZE from 120704 to 241408,
> Increased the BUFFERPOOL from 10000 to 50000
> and increased the CLEANERS from 8 to 16
>
> Here are my calculations for the metrics
> BR = 5.009
> BTR = 6.7479
> RAU = 101.22 ??
>
> I think they look OK and users have commented that the database is faster.
>
> I still am a little concerned about my AIO and KIO readings
>
> =================================================================
> D:\\\\Program Files\\\\IBM\\\\IBM Informix Dynamic Server\\\\11.50>onstat -g iob
>
> IBM Informix Dynamic Server Version 11.50.FC4WE -- On-Line -- Up 16:35:53
--> 483456 Kbytes
>
> AIO big buffer usage summary:
> class reads writes
>
> pages ops pgs/op holes hl-ops hls/op pages ops pgs/op
> drda_dbg 0 0 0.00 0 0 0.00 0 0 0.00
> sqli_dbg 0 0 0.00 0 0 0.00 0 0 0.00
> kio 6782521 2526233 2.68 720170 125095 5.76 22584 13825 1.63
> adt 0 0 0.00 0 0 0.00 0 0 0.00
> msc 0 0 0.00 0 0 0.00 0 0 0.00
> aio 51280 3492 14.68 0 0 0.00 60288 4055 14.87
> pio 0 0 0.00 0 0 0.00 0 0 0.00
> lio 0 0 0.00 0 0 0.00 0 0 0.00
>
> Do I need another aio thread??
> =======================================================
> D:\\\\Program Files\\\\IBM\\\\IBM Informix Dynamic Server\\\\11.50>onstat -g iov
>
> IBM Informix Dynamic Server Version 11.50.FC4WE -- On-Line -- Up 16:40:14
--> 483456 Kbytes
>
> AIO I/O vps:
> class/vp s io/s totalops dskread dskwrite dskcopy wakeups io/wup errors
> tempops
> kio 0 i 5.3 317058 315644 1414 0 83614965 0.0 0 0
> kio 1 i 10.0 598030 591615 6415 0 341659363 0.0 0 0
> kio 2 i 4.5 267545 263690 3855 0 72618941 0.0 0 0
> kio 3 i 25.4 1522379 1520096 2283 0 557743124 0.0 0 0
> msc 0 i 0.1 4410 0 0 0 4397 1.0 0 4410
> aio 0 i 0.1 7974 3543 4063 0 7975 1.0 0 29
> pio 0 i 0.0 0 0 0 0 1 0.0 0 0
> lio 0 i 0.0 0 0 0 0 1 0.0 0 0
>
> kio 3 seems to be doing much more work than the other kio's
> =====================================================
> D:\\\\Program Files\\\\IBM\\\\IBM Informix Dynamic Server\\\\11.50>onstat -g glo
>
> IBM Informix Dynamic Server Version 11.50.FC4WE -- On-Line -- Up 16:42:03
--> 483456 Kbytes
>
> MT global info:
> sessions threads vps lngspins
> 141 177 11 0
>
> sched calls thread switches yield 0 yield n yield forever
> total: 0 0 9873401 1192617 1084139934
> per sec: 0 0 0 0 0
>
> Virtual processor summary:
> class vps usercpu syscpu total
> cpu 4 2589.60 829.07 3418.67
> aio 1 0.02 1.17 1.18
> lio 1 0.00 0.00 0.00
> pio 1 0.00 0.00 0.00
> adm 1 0.00 0.00 0.00
> soc 2 0.00 0.00 0.00
> msc 1 0.48 1.18 1.67
> total 11 2590.10 831.42 3421.52
>
> Individual virtual processors:
> vp pid class usercpu syscpu total Thread Eff
> 1 2036 cpu 605.25 38.03 643.28 60126.65 1%
> 2 2352 adm 0.00 0.00 0.00 0.00 0%
> 3 2652 cpu 732.18 345.72 1077.90 60125.33 1%
> 4 2664 cpu 1090.40 396.98 1487.38 60125.29 2%
> 5 2672 cpu 161.77 48.33 210.10 60125.24 0%
> 6 2696 lio 0.00 0.00 0.00 0.01 0%
> 7 2732 pio 0.00 0.00 0.00 0.02 0%
> 8 2764 aio 0.02 1.17 1.18 60014.94 0%
> 9 2768 msc 0.48 1.18 1.67 60102.37 0%
> 10 2800 soc 0.00 0.00 0.00 NA NA
> 11 2804 soc 0.00 0.00 0.00 NA NA
>
> tot 2590.10 831.42 3421.52
>
> ====================================================
> D:\\\\Program Files\\\\IBM\\\\IBM Informix Dynamic Server\\\\11.50>onstat -g iob
>
> IBM Informix Dynamic Server Version 11.50.FC4WE -- On-Line -- Up 16:42:13
--> 483456 Kbytes
>
> AIO big buffer usage summary:
> class reads writes
>
> pages ops pgs/op holes hl-ops hls/op pages ops pgs/op
> drda_dbg 0 0 0.00 0 0 0.00 0 0 0.00
> sqli_dbg 0 0 0.00 0 0 0.00 0 0 0.00
> kio 7359019 2787326 2.64 777719 134603 5.78 22922 13967 1.64
> adt 0 0 0.00 0 0 0.00 0 0 0.00
> msc 0 0 0.00 0 0 0.00 0 0 0.00
> aio 51872 3529 14.70 0 0 0.00 60416 4063 14.87
> pio 0 0 0.00 0 0 0.00 0 0 0.00
> lio 0 0 0.00 0 0 0.00 0 0 0.00
>
> Does this mean there are no ready threads??
> =======================================================
> D:\\\\Program Files\\\\IBM\\\\IBM Informix Dynamic Server\\\\11.50>onstat -g rea
>
> IBM Informix Dynamic Server Version 11.50.FC4WE -- On-Line -- Up 16:42:22
--> 483456 Kbytes
>
> Ready threads:
> tid tcb rstcb prty status vp-class name
>
> Thanks
> Kent
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
--20cf3054a547e1f8f0049ac2dcfb
Art, Would you please post the link or tell me where I can get to your document that has the formulas for the BR, BTR, RAU, etc. Thanks
I'll go you one better. Here's a link to my package ratios.shr_ak: ftp://ftp.iiug.org/pub/informix/pub/ratios.shr_ak.gz There is an SQL file, ratios.sql that installs a stored procedure, ratios, into sysmaster which calculates these metrics for each buffer cache pagesize and again for the total of all caches. There is also a ksh/bash script, newratios.ksh, that executes the procedure and formats the output. The readme file explains how to install it, but, you can also see the formulae in ratios.sql Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, 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 Tue, Feb 22, 2011 at 2:58 PM, TRI TRINH <tri_trinh@hotmail.com> wrote: > Art, > > Would you please post the link or tell me where I can get to your document > that has the formulas for the BR, BTR, RAU, etc. Thanks > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0015175ce1821c2931049ce723d1
Related threads
- Posting from the Informix-list
- Migrating from IDS 9.40.UC6 to 11.50.UC3
- Ip for a network session
- questions onstat -g