IDS 9.40 UC5/linux update statistics crashes engine on large table
Breif background:
I am in the process of doing some migration testing from an hpux system
running 9.21 FC7 to a linux system (fedora core 1 with customer
mainline kernel) running 9.40 UC5.
I have gone through the install and everything seems normal. Took a
copy of one of our "smaller" databases about 12 tables all but 1 have
less then 5k rows in them. The largest table having 15 million rows.
Set up the database, loaded the data using load from insert into.. etc.
Used HPL to load the large table, built the indexes on the table. Not
a single issue (in fact I noticed that they fixed the gui for hpl
between 9.3 and 9.4 on linux)
Issue:
This is where the problem comes in. I went to our hpux machine and ran
dostats -h newserver -d newdb -f file.sql it connects and spits out an
sql file. looks like it always does, I have done this a million times
when rebuilding a table. sftp the file over to the new linux server
add some pdqpriority and set the lock mode, etc. run it and see some
of the statistics are updating in my output file and then the engine
crashes. I did this a number of times, at one point the crash
corrupted the dbspace this db was in and I had to rebuild from
scratch.. did that yesterday, rinse repeat and the same exact thing
happens. I found the line in the file that crashes the engine and its
the first set of sql statements for the largest table in the database.
UPDATE STATISTICS MEDIUM FOR TABLE big_table DISTRIBUTIONSONLY;
I have opened a case with informix, but its not looking good so far. I
have not been able to find much about this ids version.
Here is the exact error that I recieved the first few times:
17:41:38 Assert Failed: No Exception Handler
17:41:38 IBM Informix Dynamic Server Version 9.40.UC5
17:41:38 Who: Session(6, informix@repsvr01b_10.prod.networkip.net,
1288, (nil))
Thread(0, noname, 0, 0)
File: mtex.c Line: 431
17:41:38 Results: Exception Caught. Type: MT_EX_OS, Context: mem
17:41:38 Action: Please notify IBM Informix Technical Support.
17:41:38 stack trace for pid 1253 written to /tmp/af.3cbb1
17:41:38 Recursive Exception - Server exiting
17:41:38 Recursive Exception - Server exiting
17:41:38 Fatal error in ADM VP at mt.c:12351
17:41:38 Unexpected virtual processor termination, pid = 1253, exit =0x100
17:41:38 PANIC: Attempting to bring system down
Sometimes it just hangs and the engine will not respond to anything.
The load avg (cpu usage) on the server gets high but the memory usage
does not increase.
Art, if your out there.. I read something you wrote not long ago about
running ids 9.4 on linux.. are you using uc5 if so have you had any
problems like this with large tables? is there a newer version of
dostats or something different I should be doing now with 9.4?
I thought it had something to do with memory at first but the engine is
not even trying to add segments it just dies.
our system config is:
KERNEL VERSION : 2.4.28
GLIBC VERSION: glibc-2.3.2-101.4
IBM Informix Dynamic Server Version 9.40.UC5 -- On-Line -- Up
00:00:44 -- 241060 Kbytes
Configuration File: /opt/informix/etc/onconfig.repsvr01b
#**************************************************************************
## Licensed Material - Property Of IBM
#
# "Restricted Materials of IBM"
#
# IBM Informix Dynamic Server
# (c) Copyright IBM Corporation 1996, 2004 All rights reserved.
#
# Title: onconfig.std
# Description: IBM Informix Dynamic Server Configuration Parameters
#
#**************************************************************************
# Root Dbspace Configuration
ROOTNAME rootdbs # Root dbspace nameROOTPATH /dev/chunks/rootdbs # Path for device containing root
dbspace
ROOTOFFSET 0 # Offset of root dbspace into device
(Kbytes)
ROOTSIZE 393216 # Size of root dbspace (Kbytes)
# Disk Mirroring Configuration Parameters
MIRROR 0 # Mirroring flag (Yes = 1, No = 0)
MIRRORPATH # Path for device containing mirroredroot
MIRROROFFSET 0 # Offset into mirrored device (Kbytes)
# Physical Log Configuration
PHYSDBS physdbs # Location (dbspace) of physical log
PHYSFILE 393110 # Physical log file size (Kbytes)
# Logical Log Configuration
LOGFILES 43 # Number of logical log files
LOGSIZE 2000 # Logical log size (Kbytes)
# Diagnostics
MSGPATH /usr/informix/online.log # System message log file path
CONSOLE /dev/console # System console message path
# To automatically backup logical logs, edit alarmprogram.sh and set
# BACKUPLOGS=Y
ALARMPROGRAM /usr/informix/etc/alarmprogram.sh # Alarm program path
TBLSPACE_STATS 0 # Maintain tblspace statistics
# System Archive Tape Device
TAPEDEV /dev/null # Tape device path
TAPEBLK 32 # Tape block size (Kbytes)
TAPESIZE 10240 # Maximum amount of data to put on tape
(Kbytes)
# Log Archive Tape Device
LTAPEDEV /dev/null # Log tape device path
LTAPEBLK 32 # Log tape block size (Kbytes)
LTAPESIZE 10240 # Max amount of data to put on log tape
(Kbytes)
# Optical
STAGEBLOB # Informix Dynamic Server staging area
# System Configuration
SERVERNUM 0 # Unique id corresponding to a OnLineinstance
DBSERVERNAME repsvr01b # Name of default database server
DBSERVERALIASES repsvr01b_shm # List of alternate dbservernames
NETTYPE # Configure poll thread(s) for nettype
DEADLOCK_TIMEOUT 60 # Max time to wait of lock indistributed env.
RESIDENT 1 # Forced residency flag (Yes = 1, No =
0)
MULTIPROCESSOR 1 # 0 for single-processor, 1 formulti-processor
NUMCPUVPS 4 # Number of user (cpu) vps
SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vpsto one
NOAGE 1 # Process aging
AFF_SPROC 0 # Affinity start processor
AFF_NPROCS 0 # Affinity number of processors
# Shared Memory Parameters
LOCKS 200000 # Maximum number of locks
BUFFERS 10000 # Maximum number of shared buffers
NUMAIOVPS 24 # Number of IO vps
PHYSBUFF 32 # Physical log buffer size (Kbytes)
LOGBUFF 32 # Logical log buffer size (Kbytes)
CLEANERS 127 # Number of buffer cleaner processes
SHMBASE 0x10000000L # Shared memory base address
SHMVIRTSIZE 200000 # initial virtual shared memory segmentsize
SHMADD 100000 # Size of new shared memory segments
(Kbytes)
SHMTOTAL 0 # Total shared memory (Kbytes).
0=>unlimited
CKPTINTVL 300 # Check point interval (in sec)
LRUS 127 # Number of
I have an interesting update..
this is the original statement created by dostats which crashes the
engine:
UPDATE STATISTICS MEDIUM FOR TABLE big_table DISTRIBUTIONSONLY;
I played with the following changes and they all ran for a while
without error or engine crashing(there have been no other changes made,
as well all of these tests were run using the following prefix):
SET ISOLATION DIRTY READ;
SET LOCK MODE TO WAIT;
SET PDQPRIORITY 40;
UPDATE STATISTICS MEDIUM FOR TABLE big_table;
UPDATE STATISTICS HIGH FOR TABLE big_table DISTRIBUTIONS ONLY;
UPDATE STATISTICS HIGH FOR TABLE big_table DISTRIBUTIONS ONLY;
UPDATE STATISTICS LOW FOR TABLE rate_deck_entry;
UPDATE STATISTICS LOW FOR TABLE rate_deck_entry DISTRIBUTIONS ONLY;(returned error below.. makes sense.. )
# ^
# 859: "Distributions Only" is not meaningful in an update statistics
LOW request.
#
I then ran the original sql just for grins.. and the engine crashed.
so it happens only on update stats medium for table with distributions
only.
I was also monitoring the threads to see what was happening on each of
these test runs. on the modified queries the aio vp's changed
normally. The last run with the original query resulted in no change.
see below, the engine never moves from aio vp 0: (hope that makes
sense)
no query running..
IBM Informix Dynamic Server Version 9.40.UC5 -- On-Line -- Up
00:17:19 -- 241060 Kbytes
Running threads:
tid tcb rstcb prty status vp-class
name
30 130eee98 0 2 running 1cpu
soctcppoll
31 12ffb470 0 2 running 33shm
sm_poll
query begins..
IBM Informix Dynamic Server Version 9.40.UC5 -- On-Line -- Up
00:17:20 -- 241060 Kbytes
Running threads:
tid tcb rstcb prty status vp-class
name
4 12ec9140 0 2 running 8aio aio
vp 0
30 130eee98 0 2 running 1cpu
soctcppoll
31 12ffb470 0 2 running 33shm
sm_poll
IBM Informix Dynamic Server Version 9.40.UC5 -- On-Line -- Up
00:17:21 -- 241060 Kbytes
Running threads:
tid tcb rstcb prty status vp-class
name
4 12ec9140 0 2 running 8aio aio
vp 0
30 130eee98 0 2 running 1cpu
soctcppoll
31 12ffb470 0 2 running 33shm
sm_poll
IBM Informix Dynamic Server Version 9.40.UC5 -- On-Line -- Up
00:17:22 -- 241060 Kbytes
Running threads:
tid tcb rstcb prty status vp-class
name
4 12ec9140 0 2 running 8aio aio
vp 0
30 130eee98 0 2 running 1cpu
soctcppoll
31 12ffb470 0 2 running 33shm
sm_poll
IBM Informix Dynamic Server Version 9.40.UC5 -- On-Line -- Up
00:17:23 -- 241060 Kbytes
Running threads:
tid tcb rstcb prty status vp-class
name
4 12ec9140 0 2 running 8aio aio
vp 0
30 130eee98 0 2 running 1cpu
soctcppoll
31 12ffb470 0 2 running 33shm
sm_poll
IBM Informix Dynamic Server Version 9.40.UC5 -- On-Line -- Up
00:17:24 -- 241060 Kbytes
Running threads:
tid tcb rstcb prty status vp-class
name
4 12ec9140 0 2 running 8aio aio
vp 0
30 130eee98 0 2 running 1cpu
soctcppoll
31 12ffb470 0 2 running 33shm
sm_poll
IBM Informix Dynamic Server Version 9.40.UC5 -- On-Line -- Up
00:17:25 -- 241060 Kbytes
Running threads:
tid tcb rstcb prty status vp-class
name
4 12ec9140 0 2 running 8aio aio
vp 0
30 130eee98 0 2 running 1cpu
soctcppoll
31 12ffb470 0 2 running 33shm
sm_poll
IBM Informix Dynamic Server Version 9.40.UC5 -- On-Line -- Up
00:17:26 -- 241060 Kbytes
Running threads:
tid tcb rstcb prty status vp-class
name
4 12ec9140 0 2 running 8aio aio
vp 0
30 130eee98 0 2 running 1cpu
soctcppoll
31 12ffb470 0 2 running 33shm
sm_poll
IBM Informix Dynamic Server Version 9.40.UC5 -- On-Line -- Up
00:17:27 -- 241060 Kbytes
Running threads:
tid tcb rstcb prty status vp-class
name
4 12ec9140 0 2 running 8aio aio
vp 0
30 130eee98 0 2 running 1cpu
soctcppoll
31 12ffb470 0 2 running 33shm
sm_poll
IBM Informix Dynamic Server Version 9.40.UC5 -- On-Line -- Up
00:17:28 -- 241060 Kbytes
Running threads:
tid tcb rstcb prty status vp-class
name
4 12ec9140 0 2 running 8aio aio
vp 0
30 130eee98 0 2 running 1cpu
soctcppoll
31 12ffb470 0 2 running 33shm
sm_poll
IBM Informix Dynamic Server Version 9.40.UC5 -- On-Line -- Up
00:17:29 -- 241060 Kbytes
Running threads:
tid tcb rstcb prty status vp-class
name
4 12ec9140 0 2 running 8aio aio
vp 0
30 130eee98 0 2 running 1cpu
soctcppoll
31 12ffb470 0 2 running 33shm
sm_poll
IBM Informix Dynamic Server Version 9.40.UC5 -- On-Line -- Up
00:17:30 -- 241060 Kbytes
Running threads:
tid tcb rstcb prty status vp-class
name
4 12ec9140 0 2 running 8aio aio
vp 0
30 130eee98 0 2 running 1cpu
soctcppoll
31 12ffb470 0 2 running 33shm
sm_poll
IBM Informix Dynamic Server Version 9.40.UC5 -- On-Line -- Up
00:17:31 -- 241060 Kbytes
Running threads:
tid tcb rstcb prty status vp-class
name
4 12ec9140 0 2 running 8aio aio
vp 0
30 130eee98 0 2 running 1cpu
soctcppoll
31 12ffb470 0 2 running 33shm
sm_poll
IBM Informix Dynamic Server Version 9.40.UC5 -- On-Line -- Up
00:17:32 -- 241060 Kbytes
Running threads:
tid tcb rstcb prty status vp-class
name
4 12ec9140 0 2 running 8aio aio
vp 0
30 130eee98 0 2 running 1cpu
soctcppoll
31 12ffb470 0 2 running 33shm
sm_poll@@N
You could contact your support provider!
17:41:38 Results: Exception Caught. Type: MT_EX_OS, Context: mem
17:41:38 Action: Please notify IBM Informix Technical Support.
they'll probably ask you for the stack trace which should be in the af
file that got generated
loden wrote:
> I have an interesting update..
>
> this is the original statement created by dostats which crashes the
> engine:
> UPDATE STATISTICS MEDIUM FOR TABLE big_table DISTRIBUTIONS> ONLY;
>
> I played with the following changes and they all ran for a while
> without error or engine crashing(there have been no other changes
made,
> as well all of these tests were run using the following prefix):
> SET ISOLATION DIRTY READ;
> SET LOCK MODE TO WAIT;
> SET PDQPRIORITY 40;>
>
> UPDATE STATISTICS MEDIUM FOR TABLE big_table;
> UPDATE STATISTICS HIGH FOR TABLE big_table DISTRIBUTIONS ONLY;
> UPDATE STATISTICS HIGH FOR TABLE big_table DISTRIBUTIONS ONLY;
> UPDATE STATISTICS LOW FOR TABLE rate_deck_entry;
> UPDATE STATISTICS LOW FOR TABLE rate_deck_entry DISTRIBUTIONS ONLY;> (returned error below.. makes sense.. )
> # ^
> # 859: "Distributions Only" is not meaningful in an update
statistics
> LOW request.
> #
>
> I then ran the original sql just for grins.. and the engine crashed.
> so it happens only on update stats medium for table with
distributions
> only.
>
> I was also monitoring the threads to see what was happening on each
of
> these test runs. on the modified queries the aio vp's changed
> normally. The last run with the original query resulted in no
change.
> see below, the engine never moves from aio vp 0: (hope that makes
> sense)
>
> no query running..
> IBM Informix Dynamic Server Version 9.40.UC5 -- On-Line -- Up
> 00:17:19 -- 241060 Kbytes>
> Running threads:
> tid tcb rstcb prty status vp-class
> name
> 30 130eee98 0 2 running 1cpu
> soctcppoll
> 31 12ffb470 0 2 running 33shm
> sm_poll
>
>
> query begins..
> IBM Informix Dynamic Server Version 9.40.UC5 -- On-Line -- Up
> 00:17:20 -- 241060 Kbytes>
> Running threads:
> tid tcb rstcb prty status vp-class
> name
> 4 12ec9140 0 2 running 8aio
aio
> vp 0
> 30 130eee98 0 2 running 1cpu
> soctcppoll
> 31 12ffb470 0 2 running 33shm
> sm_poll
>
>
>
> IBM Informix Dynamic Server Version 9.40.UC5 -- On-Line -- Up
> 00:17:21 -- 241060 Kbytes>
> Running threads:
> tid tcb rstcb prty status vp-class
> name
> 4 12ec9140 0 2 running 8aio
aio
> vp 0
> 30 130eee98 0 2 running 1cpu
> soctcppoll
> 31 12ffb470 0 2 running 33shm
> sm_poll
>
>
>
> IBM Informix Dynamic Server Version 9.40.UC5 -- On-Line -- Up
> 00:17:22 -- 241060 Kbytes>
> Running threads:
> tid tcb rstcb prty status vp-class
> name
> 4 12ec9140 0 2 running 8aio
aio
> vp 0
> 30 130eee98 0 2 running 1cpu
> soctcppoll
> 31 12ffb470 0 2 running 33shm
> sm_poll
>
>
>
> IBM Informix Dynamic Server Version 9.40.UC5 -- On-Line -- Up
> 00:17:23 -- 241060 Kbytes>
> Running threads:
> tid tcb rstcb prty status vp-class
> name
> 4 12ec9140 0 2 running 8aio
aio
> vp 0
> 30 130eee98 0 2 running 1cpu
> soctcppoll
> 31 12ffb470 0 2 running 33shm
> sm_poll
>
>
>
> IBM Informix Dynamic Server Version 9.40.UC5 -- On-Line -- Up
> 00:17:24 -- 241060 Kbytes>
> Running threads:
> tid tcb rstcb prty status vp-class
> name
> 4 12ec9140 0 2 running 8aio
aio
> vp 0
> 30 130eee98 0 2 running 1cpu
> soctcppoll
> 31 12ffb470 0 2 running 33shm
> sm_poll
>
>
>
> IBM Informix Dynamic Server Version 9.40.UC5 -- On-Line -- Up
> 00:17:25 -- 241060 Kbytes>
> Running threads:
> tid tcb rstcb prty status vp-class
> name
> 4 12ec9140 0 2 running 8aio
aio
> vp 0
> 30 130eee98 0 2 running 1cpu
> soctcppoll
> 31 12ffb470 0 2 running 33shm
> sm_poll
>
>
>
> IBM Informix Dynamic Server Version 9.40.UC5 -- On-Line -- Up
> 00:17:26 -- 241060 Kbytes>
> Running threads:
> tid tcb rstcb prty status vp-class
> name
> 4 12ec9140 0 2 running 8aio
aio
> vp 0
> 30 130eee98 0 2 running 1cpu
> soctcppoll
> 31 12ffb470 0 2 running 33shm
> sm_poll
>
>
>
> IBM Informix Dynamic Server Version 9.40.UC5 -- On-Line -- Up
> 00:17:27 -- 241060 Kbytes>
> Running threads:
> tid tcb rstcb prty status vp-class
> name
> 4 12ec9140 0 2 running 8aio
aio
> vp 0
> 30 130eee98 0 2 running 1cpu
> soctcppoll
> 31 12ffb470 0 2 running 33shm
> sm_poll
>
>
>
> IBM Informix Dynamic Server Version 9.40.UC5 -- On-Line -- Up
> 00:17:28 -- 241060 Kbytes>
> Running threads:
> tid tcb rstcb prty status vp-class
> name
> 4 12ec9140 0 2 running 8aio
aio
> vp 0
> 30 130eee98 0 2 running 1cpu
> soctcppoll
> 31 12ffb470 0 2 running 33shm
> sm_poll
>
>
>
> IBM Informix Dynamic Server Version 9.40.UC5 -- On-Line -- Up
> 00:17:29 -- 241060 Kbytes>
> Running threads:
> tid tcb rstcb prty status vp-class
> name
> 4 12ec9140 0 2 running 8aio
aio
> vp 0
> 30 130eee98 0 2 running 1cpu
> soctcppoll
> 31 12ffb470 0 2 running 33shm
> sm_poll
>
>
>
> IBM Informix Dynamic Server Version 9.40.UC5 -- On-Line -- Up
> 00:17:30 -- 241060 Kbytes>
> Running threads:
> tid tcb rstcb prty status vp-class
> name
> 4 12ec9140 0 2 running 8aio
aio
> vp 0
> 30 130eee98 0 2 running 1cpu
> soctcppoll
> 31 12ffb470 0 2 running 33shm
> sm_poll
>
>
>
> IBM Informix Dynamic Server Version 9.40.UC5 -- O