informix 9.3 to 9.4 issue

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

analog todd - 28 Feb 2008 18:59 GMT

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

Reply to this Message

Fernando Nunes - 28 Feb 2008 19:12 GMT

> Hello,
> I'm having some serious issues with a production instance of Informix
[quoted text clipped - 71 lines]
>
> 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.

Signature

Fernando Nunes
Portugal

http://informix-technology.blogspot.com
My email works... but I don't check it frequently...

Reply to this Message

analog todd - 28 Feb 2008 19:25 GMT

> > Hello,
> > I'm having some serious issues with a production instance of Informix
[quoted text clipped - 92 lines]
>
> - 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.log
12:06:35 Fuzzy Checkpoint Completed: duration was 0 seconds, 306
buffers 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, 320
buffers 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, 333
buffers 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, 339
buffers 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, 361
buffers 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 COMMAND
6228 informix 15 0 5 70:47.73 28.0 1896m 1.1g 1.1g S oninit
6233 root 15 0 2 14:02.30 22.9 1896m 926m 926m D oninit
5579 root 15 0 2 3:46.02 0.4 37948 17m 6632 S X
6226 informix 15 0 1 48:42.82 29.5 1896m 1.2g 1.2g S oninit
6235 root 16 0 0 3:35.61 22.5 1896m 912m 911m D oninit
8796 root 16 0 0 0:17.08 0.3 22704 11m 8596 S gnome-
system-mo
1 root 16 0 0 0:01.03 0.0 3588 544 464 S init

Reply to this Message

TBP (The Big Potato) - 29 Feb 2008 10:06 GMT

<snip

>>>RESIDENT 0 # Forced residency flag (Yes = 1, No = 0)

RESIDENT 1

>>>NOAGE 0 # Process aging

NOAGE 1

>>>NUMAIOVPS 2 # Number of IO vps

Using 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 limit

Will 800 Mb of BUFFERS, suggest 10

>>>LRU_MIN_DIRTY 50 # LRU percent dirty end cleaning limit

and 5

>>>DBSPACETEMP tmpdbs # Default temp dbspaces

Always 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,
[quoted text clipped - 3 lines]
> 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

Reply to this Message

Fernando Nunes - 29 Feb 2008 13:05 GMT

> 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)

Signature

Fernando Nunes
Portugal

http://informix-technology.blogspot.com
My email works... but I don't check it frequently...

Reply to this Message

david@smooth1.co.uk - 29 Feb 2008 21:14 GMT

> > What is happening with the memory??
>
[quoted text clipped - 13 lines]
> 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.

Reply to this Message

Art S. Kagel (Oninit) - 28 Feb 2008 19:40 GMT

> 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
>

I presume that you did an in-place upgrade from 9.30 to 9.40. Did you
drop and recreate all of the data distributions after the upgrade?
There was a change in format of the stored distributions that makes them
incompatible. You have to force all of the distributions to drop with
UPDATE STATISTICS LOW.... DROP DISTRIBUTIONS before rerunning the stats.

Art S. Kagel
Oninit

> 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
[quoted text clipped - 80 lines]
>
>

===========================================================================================
Please access the attached hyperlink for an important electronic communications disclaimer:

http://www.oninit.com/home/disclaimer.php

===========================================================================================

Reply to this Message