Re: informix 9.3 to 9.4 issue
A user migrating a production instance from 9.30.UC7 to 9.40.UC8 on RHEL 4 (Dell PE1950, 4GB RAM) reported high CPU and the server seemingly not using all configured memory, and posted his ONCONFIG plus onstat -b/-m/profile and top output. Respondents asked for more detail (which sessions/threads burn CPU, sequential scans, btree scanner activity), suggested re-running UPDATE STATISTICS (low drop distributions, then the full suite and for procedures), setting RESIDENT 1 and NOAGE 1, raising NUMAIOVPS if not using KAIO, lowering LRU_MAX/MIN_DIRTY to about 10/5, using several real temp dbspaces, and running onstat -z then syssesprof to find busy sessions and check their query plans. The near-full memory in top was explained as normal Linux caching. No outcome or resolution is recorded in the thread.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
analog todd wrote:
> Hello,
> I'm having some serious issues with a production instance of Informix
> and I can't figure out why.
> I'd been running 9.30.UC7 for over a year solid, no complaints.
> Monday night, I installed 9.40.UC8 and its been awful. What am I
> missing? I've been tweaking ONCONFIG and I'm still not there. Today,
> the CPU is through the roof and Informix doesn't appear to using all
> the memory that I'm throwing at it. Hardware is a Dell PE 1950
> running Linux RHE 4.0 mirrored drives & 4GB of RAM
>
> here is a partial ONCONFIG:
>
> ROOTNAME rootdbs # Root dbspace name> ROOTPATH /informix/data/rootdbs # Path for device containing root
> dbspace
> ROOTOFFSET 0 # Offset of root dbspace into device (Kbytes)
> ROOTSIZE 32000 # Size of root dbspace (Kbytes)>
> # Physical Log Configuration
>
> PHYSDBS physdbs # Location (dbspace) of physical log
> PHYSFILE 32000 # Physical log file size (Kbytes)>
> # Logical Log Configuration
>
> LOGFILES 147 # Number of logical log files
> LOGSIZE 2000 # Logical log size (Kbytes)> LOG_BACKUP_MODE CONT # Logical log backup mode (MANUAL, CONT)
>
> # System Configuration
>
> SERVERNUM 0 # Unique id corresponding to a OnLine instance
> DBSERVERNAME infx010_ot # Name of default database server
> DBSERVERALIASES infx010_os # List of alternate dbservernames
> NETTYPE # Configure poll thread(s) for nettype
> DEADLOCK_TIMEOUT 60 # Max time to wait of lock in distributed env.
> RESIDENT 0 # Forced residency flag (Yes = 1, No = 0)
>
> MULTIPROCESSOR 1 # 0 for single-processor, 1 for multi-> processor
> NUMCPUVPS 4 # Number of user (cpu) vps
> SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps> to one
>
> NOAGE 0 # Process aging
> AFF_SPROC 0 # Affinity start processor
> AFF_NPROCS 0 # Affinity number of processors>
> # Shared Memory Parameters
>
> LOCKS 600000 # Maximum number of locks
> BUFFERS 400000 # Maximum number of shared buffers
> NUMAIOVPS 2 # Number of IO vps
> PHYSBUFF 128 # Physical log buffer size (Kbytes)
> LOGBUFF 128 # Logical log buffer size (Kbytes)
> CLEANERS 8 # Number of buffer cleaner processes
> SHMBASE 0x44000000L # Shared memory base address
> SHMVIRTSIZE 1024000 # initial virtual shared memory segment> size
> SHMADD 256000 # Size of new shared memory segments
> (Kbytes)
> EXTSHMADD 8192 # Size of new extension shared memory
> segments (Kbytes)
> SHMTOTAL 0 # Total shared memory (Kbytes).
> 0=>unlimited
> CKPTINTVL 180 # Check point interval (in sec)
> LRUS 32 # Number of LRU queues
> LRU_MAX_DIRTY 60 # LRU percent dirty begin cleaning limit
> LRU_MIN_DIRTY 50 # LRU percent dirty end cleaning limit
> TXTIMEOUT 300 # Transaction timeout (in sec)
> STACKSIZE 32 # Stack size (Kbytes)>
> # DBSPACETEMP:
>
> DBSPACETEMP tmpdbs # Default temp dbspaces>
It won't be possible to help you without more information...
Did you update statistics? What are the threads/sessions consuming more CPU?
What are they doing? Do you have unusual sequential scans? It may be worth
checking your btree scanner threads...
Some onstats could help...
By the, as a side note, you should have migrated to 10 or 11... The possibility
for pain is more or less the same, and you'd gain much more functionalities...
Regards.
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
On Feb 28, 12:12 pm, Fernando Nunes <s...@onlinedomus.net> wrote:
> analog todd wrote:
> > Hello,
> > I'm having some serious issues with a production instance of Informix
> > and I can't figure out why.
> > I'd been running 9.30.UC7 for over a year solid, no complaints.
> > Monday night, I installed 9.40.UC8 and its been awful. What am I
> > missing? I've been tweaking ONCONFIG and I'm still not there. Today,
> > the CPU is through the roof and Informix doesn't appear to using all
> > the memory that I'm throwing at it. Hardware is a Dell PE 1950
> > running Linux RHE 4.0 mirrored drives & 4GB of RAM
>
> > here is a partial ONCONFIG:
>
> > ROOTNAME rootdbs # Root dbspace name> > ROOTPATH /informix/data/rootdbs # Path for device containing root
> > dbspace
> > ROOTOFFSET 0 # Offset of root dbspace into device (Kbytes)
> > ROOTSIZE 32000 # Size of root dbspace (Kbytes)>
> > # Physical Log Configuration
>
> > PHYSDBS physdbs # Location (dbspace) of physical log
> > PHYSFILE 32000 # Physical log file size (Kbytes)>
> > # Logical Log Configuration
>
> > LOGFILES 147 # Number of logical log files
> > LOGSIZE 2000 # Logical log size (Kbytes)> > LOG_BACKUP_MODE CONT # Logical log backup mode (MANUAL, CONT)
>
> > # System Configuration
>
> > SERVERNUM 0 # Unique id corresponding to a OnLine instance
> > DBSERVERNAME infx010_ot # Name of default database server
> > DBSERVERALIASES infx010_os # List of alternate dbservernames
> > NETTYPE # Configure poll thread(s) for nettype
> > DEADLOCK_TIMEOUT 60 # Max time to wait of lock in distributed env.
> > RESIDENT 0 # Forced residency flag (Yes = 1, No = 0)
>
> > MULTIPROCESSOR 1 # 0 for single-processor, 1 for multi-> > processor
> > NUMCPUVPS 4 # Number of user (cpu) vps
> > SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps> > to one
>
> > NOAGE 0 # Process aging
> > AFF_SPROC 0 # Affinity start processor
> > AFF_NPROCS 0 # Affinity number of processors>
> > # Shared Memory Parameters
>
> > LOCKS 600000 # Maximum number of locks
> > BUFFERS 400000 # Maximum number of shared buffers
> > NUMAIOVPS 2 # Number of IO vps
> > PHYSBUFF 128 # Physical log buffer size (Kbytes)
> > LOGBUFF 128 # Logical log buffer size (Kbytes)
> > CLEANERS 8 # Number of buffer cleaner processes
> > SHMBASE 0x44000000L # Shared memory base address
> > SHMVIRTSIZE 1024000 # initial virtual shared memory segment> > size
> > SHMADD 256000 # Size of new shared memory segments
> > (Kbytes)
> > EXTSHMADD 8192 # Size of new extension shared memory
> > segments (Kbytes)
> > SHMTOTAL 0 # Total shared memory (Kbytes).
> > 0=>unlimited
> > CKPTINTVL 180 # Check point interval (in sec)
> > LRUS 32 # Number of LRU queues
> > LRU_MAX_DIRTY 60 # LRU percent dirty begin cleaning limit
> > LRU_MIN_DIRTY 50 # LRU percent dirty end cleaning limit
> > TXTIMEOUT 300 # Transaction timeout (in sec)
> > STACKSIZE 32 # Stack size (Kbytes)>
> > # DBSPACETEMP:
>
> > DBSPACETEMP tmpdbs # Default temp dbspaces>
> It won't be possible to help you without more information...
> Did you update statistics? What are the threads/sessions consuming more CPU?
> What are they doing? Do you have unusual sequential scans? It may be worth
> checking your btree scanner threads...
>
> Some onstats could help...
>
> By the, as a side note, you should have migrated to 10 or 11... The possibility
> for pain is more or less the same, and you'd gain much more functionalities...
>
> Regards.
>
> --
> Fernando Nunes
> Portugal
>
> http://informix-technology.blogspot.com
> My email works... but I don't check it frequently...- Hide quoted text -
>
> - Show quoted text -
I would have loved to have migrated to 11!! Unfortunately, I can't.
Stats have been run.
# onstat -b
IBM Informix Dynamic Server Version 9.40.UC8 -- On-Line -- Up
13:47:56 -- 1927900 Kbytes
Buffers
address userthread flgs pagenum memaddr nslots pgflgs xflgs
owner waitlist
779 modified, 400000 total, 524288 hash buckets, 2048 buffer size
# onstat -m
IBM Informix Dynamic Server Version 9.40.UC8 -- On-Line -- Up
13:48:03 -- 1927900 Kbytes
Message Log File: /informix/msg/online.log12:06:35 Fuzzy Checkpoint Completed: duration was 0 seconds, 306buffers not flushed.
12:06:35 Checkpoint loguniq 267, logpos 0x829308, timestamp:0x4665f203
12:06:35 Maximum server connections 144
12:09:35 Fuzzy Checkpoint Completed: duration was 0 seconds, 320buffers not flushed.
12:09:35 Checkpoint loguniq 267, logpos 0x846538, timestamp:0x46663e25
12:09:35 Maximum server connections 144
12:12:35 Fuzzy Checkpoint Completed: duration was 0 seconds, 333buffers not flushed.
12:12:35 Checkpoint loguniq 267, logpos 0x87c2d4, timestamp:0x4666a262
12:12:35 Maximum server connections 144
12:15:35 Fuzzy Checkpoint Completed: duration was 0 seconds, 339buffers not flushed.
12:15:35 Checkpoint loguniq 267, logpos 0x88a7a0, timestamp:0x4666e26b
12:15:35 Maximum server connections 144
12:18:36 Fuzzy Checkpoint Completed: duration was 0 seconds, 361buffers not flushed.
12:18:36 Checkpoint loguniq 267, logpos 0x8d07a0, timestamp:0x46675f0a
12:18:36 Maximum server connections 144
Profile
dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached
25171503 27587073 2122526749 98.82 141403 641010 1982131 94.57
isamtot open start read write rewrite delete
commit rollbk
2729829689 1813970 78889341 1708933881 1907781 140158 2979
13676 952
gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs
0 0 0 0 0 0 0
ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes
0 0 0 3682.17 568.50 45 90
bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress
seqscans
2384694 351 69987079 0 0 19 51151 653856
ixda-RA idx-RA da-RA RA-pgsused lchwaits
3769428 99756 1434608 5169892 104287
Here is a top:
top - 12:18:29 up 14:15, 4 users, load average: 1.30, 0.86, 0.76
Tasks: 122 total, 1 running, 120 sleeping, 1 stopped, 0 zombie
Cpu(s): 2.0% us, 0.8% sy, 0.0% ni, 73.8% id, 23.4% wa, 0.0% hi,
0.0% si
Mem: 4149104k total, 4125452k used, 23652k free, 6220k
buffers
Swap: 4095984k total, 208k used, 4095776k free, 3932260k
cached
PID USER PR NI %CPU TIME+ %MEM VIRT RES SHR S COMM
<snip
>>>RESIDENT 0 # Forced residency flag (Yes = 1, No = 0)
RESIDENT 1
>>>NOAGE 0 # Process aging
NOAGE 1
>>>NUMAIOVPS 2 # Number of IO vpsUsing KAIO?? or AIO??
If using AIO, may need to increase NUMAIOVPs to "number of relatively active chunks"
>>>LRU_MAX_DIRTY 60 # LRU percent dirty begin cleaning limitWill 800 Mb of BUFFERS, suggest 10
>>>LRU_MIN_DIRTY 50 # LRU percent dirty end cleaning limitand 5
>>>DBSPACETEMP tmpdbs # Default temp dbspacesAlways have 3 or more for temp dbspaces (REAL temp dbspaces with the -t flag)
>>
> Tasks: 122 total, 1 running, 120 sleeping, 1 stopped, 0 zombie
> Cpu(s): 2.0% us, 0.8% sy, 0.0% ni, 73.8% id, 23.4% wa, 0.0% hi,
> 0.0% si
> Mem: 4149104k total, 4125452k used, 23652k free, 6220k
> buffers
> Swap: 4095984k total, 208k used, 4095776k free, 3932260k
> cached
What is happening with the memory??
> Mem: 4149104k total, 4125452k used, 23652k free, 6220k buffers
4125452k Used?? Could be a Linux funny s'pose
Oh, and as Art says "update statistics low drop distributions" followed by the "suite of required update statistics" followed by
update statistics for procedure.
Ho hum, boiled bum
TBP (The Big Potato) wrote: > What is happening with the memory?? > > > Mem: 4149104k total, 4125452k used, 23652k free, 6220k buffers > > 4125452k Used?? Could be a Linux funny s'pose Linux's memory is always almost full as AFAIK. It uses every memory available for cache.. but this has a very low priority (meaning the cache will be disposed as soon as the system needs memory for anything more useful) -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...
On 29 Feb, 13:05, Fernando Nunes <s...@onlinedomus.net> wrote:
> TBP (The Big Potato) wrote:
>
> > What is happening with the memory??
>
> > > Mem: 4149104k total, 4125452k used, 23652k free, 6220k buffers
>
> > 4125452k Used?? Could be a Linux funny s'pose
>
> Linux's memory is always almost full as AFAIK.
> It uses every memory available for cache.. but this has a very low priority
> (meaning the cache will be disposed as soon as the system needs memory for
> anything more useful)
>
> --
> Fernando Nunes
> Portugal
>
> http://informix-technology.blogspot.com
> My email works... but I don't check it frequently...
Run onstat -z then 10 seconds later run select * from syssesprof in
sysmaster.
Look at the busy sessions and their query plans.