 |
IDS Forum
Informix Lockup (Wierd)
Posted By: Phillips, Rob
Date: Thursday, 30 January 2003, at 3:14 p.m.
Occasionally I am experiencing Informix locking up. It won't accept any db
connections, or process any SQL commands on connections that are currently
open. We are running on a dual processor box and durring this lockup I am
noticing that 1 cpu is maxed out with 100% usr processes. The other
processor is doing virtually nothing.
The informix was non responding for approximately 30 minutes. When it came
back online I checked some logs. The lockup started at approximately 2pm
(14:00 hrs) Here is what the logs said:
13:27:51 Checkpoint Completed: duration was 1 seconds.
13:35:32 Logical Log 51508 Complete.
13:35:35 Logical Log 51508 - Backup Started
13:35:36 Logical Log 51508 - Backup Completed
13:43:22 Logical Log 51509 Complete.
13:43:25 Logical Log 51509 - Backup Started
13:43:26 Logical Log 51509 - Backup Completed
13:51:04 Logical Log 51510 Complete.
13:51:07 Logical Log 51510 - Backup Started
13:51:07 Logical Log 51510 - Backup Completed
13:57:53 Checkpoint Completed: duration was 1 seconds.
13:59:13 Logical Log 51511 Complete.
13:59:16 Logical Log 51511 - Backup Started
13:59:16 Logical Log 51511 - Backup Completed
14:27:24 listener-thread: err = -25573: oserr = 72: errstr = : Network
driver cannot accept a connection on the port.
System error = 72.
14:27:24 listener-thread: err = -25587: oserr = 0: errstr = : Network
receive failed.
14:27:24 listener-thread: err = -25573: oserr = 72: errstr = : Network
driver cannot accept a connection on the port.
System error = 72.
14:27:24 listener-thread: err = -25573: oserr = 72: errstr = : Network
driver cannot accept a connection on the port.
System error = 72.
This last error "Network driver cannot accept a connection on the port."
repeated approximately 50 times until
14:27:55 listener-thread: err = -25573: oserr = 72: errstr = : Network
driver cannot accept a connection on the port.
System error = 72.
Another (possibly helpful) bit of information was that a updateStatistics
script is set to run every hour on the hour. Here is the
updatestatistics.sh that runs. I'm thinking that it was possibly this
script that caused the lockup becuase both the script and the lockup started
at the same time. However the lockup occurs very infrequently.. maybe once
every other month at most. I am no informix DBA but I'm all we have so if
anyone could look over this script or has experienced a similar/same problem
could you PLEASE HELP. Thank you very much.
#!/usr/bin/ksh
# This program runs update statistics for a database in parallel
# by invoking separate connections for each table
#
# USAGE : updatestat.sh <dbname> <run_options> <no_of_processes>
#
# AUTHOR : Varadharajan Kope mkv@infogain.com
#
# NOTE :-
# * Change the value of US_DIR and test before use. US_DIR is the
directory
# where the update statistics scripts and execution outputs are kept.
# A separate file with the name <tablename>.sql is created for each
table.
# * Run 'update statistics' on the database once before using this script
# for the first time for best results.
# * Distribution level selection is based on nrows. SMALL_TAB specifies
# the no. of rows for a small table and LARGE_TAB, for a large table.
# * To know information about the arguments, type prl_us.sh at the prompt
# * Do not run update statistics very often. Once a week would be fine.
#
# DISCLAIMER :-
# The author is not responsible for any damage this script could cause
# to your system performance. But, for any performance improvement, he
is.
# USE IT AT YOUR OWN RISK.
#--LOCAL MODIFICATIONS--------
# Carl M. Barnes, Data Basics International
# modified for CEA
# set informix vars
INFORMIXDIR=/informix
ONCONFIG=onconfig.ows
INFORMIXSERVER=ol_cea_shm
INFORMIXSQLHOSTS=/informix/etc/sqlhosts
TERM=vt100
TERMCAP=/informix/etc/termcap
PATH=.:/informix/bin:/informix/bin:.:/usr/ccs/bin:/usr/ccs/lib:/bin:/usr/bin
:/usr/sbin:/usr/etc:/usr/ucb
export INFORMIXDIR INFORMIXSERVER ONCONFIG INFORMIXSQLHOSTS PATH TERM
TERMCAP
##-cmbexport US_DIR=/dataconv/$DBNAME.stats
#-----------------------------
# ---------------------------------------------------------------------
# This function generates update statistics scripts for a table
# using the strategy suggested by Informix in the performance guide
# and the release notes which is
# 1. MEDIUM on all columns that are not part of an index as a single
statement
# with distributions only.
# 2. HIGH on all columns that are part of an index as separate statements.
# 3. For indexes that begin with the same subset of columns,
# run HIGH for the first column in each index that differs.
#
gen_us()
{
DBNAME=$1
TABNAME=$2
echo "set lock mode to wait;" > $TABNAME.sql
echo "set optimization all_rows;" >> $TABNAME.sql
# echo "set isolation to dirty read;" >> $TABNAME.sql
# for small tables run update statistics HIGH
eval dbaccess $DBNAME 2>/dev/null 1>&2 <<EOH
unload to "d.out" delimiter ';'
select 'update statistics high for table ' || tabname
from systables
where tabname = "$TABNAME"
and nrows < 1000 ;
EOH
if [ `cat d.out | wc -l` -ne 0 ]
then
cat d.out >>$TABNAME.sql
rm d.out
return
fi
# Identify columns that differ where indexes start with the same columns
TABID=`get_tabid $DBNAME $TABNAME | sed '1,4d'`
echo $TABID
eval dbaccess $DBNAME 2>/dev/null 1>&2 <<EOF
set optimization all_rows;
select tabid, idxname, abs(part1) col, 1 part from sysindexes
where part1 != 0 and tabid = $TABID
union
select tabid, idxname, abs(part2) col, 2 part from sysindexes
where part2 != 0 and tabid = $TABID
union
select tabid, idxname, abs(part3) col, 3 part from sysindexes
where part3 != 0 and tabid = $TABID
union
select tabid, idxname, abs(part4) col, 4 part from sysindexes
where part4 != 0 and tabid = $TABID
union
select tabid, idxname, abs(part5) col, 5 part from sysindexes
where part5 != 0 and tabid = $TABID
union
select tabid, idxname, abs(part6) col, 6 part from sysindexes
where part6 != 0 and tabid = $TABID
union
select tabid, idxname, abs(part7) col, 7 part from sysindexes
where part7 != 0 and tabid = $TABID
union
select tabid, idxname, abs(part8) col, 8 part from sysindexes
where part8 != 0 and tabid = $TABID
union
select tabid, idxname, abs(part9) col, 9 part from sysindexes
where part9 != 0 and tabid = $TABID
union
select tabid, idxname, abs(part10) col, 10 part from sysindexes
where part10 != 0 and tabid = $TABID
union
select tabid, idxname, abs(part11) col, 11 part from sysindexes
where part11 != 0 and tabid = $TABID
union
select tabid, idxname, abs(part12) col, 12 part from sysindexes
where part12 != 0 and tabid = $TABID
union
select tabid, idxname, abs(part13) col, 13 part from sysindexes
where part13 != 0 and tabid = $TABID
union
select tabid, idxname, abs(part14) col, 14 part from sysindexes
where part14 != 0 and tabid = $TABID
union
select tabid, idxname, abs(part15) col, 15 part from sysindexes
where part15 != 0 and tabid = $TABID
union
select tabid, idxname, abs(part16) col, 16 part from sysindexes
where part16 != 0 and tabid = $TABID
INTO TEMP mytmp ;
-- select m1.tabid, m1.idxname, m2.idxname idxname2, max(m1.part) part
-- from mytmp m1, mytmp m2
-- where
-- m1.part = m2.part
-- and m1.col = m2.col
-- and m1.idxname != m2.idxname
---- and m1.rowid != m2.rowid
-- group by 1, 2, 3
-- into temp mytmp2;
-- select * from mytmp2 ;
-- select col from mytmp
-- where part = 1
-- into temp high_col;
select unique col from mytmp
into temp high_col;
-- insert into high_col
-- select m1.col
-- from mytmp m1, mytmp2 m2
-- where m2.idxname = m1.idxname
-- and m1.part = ( m2.part + 1 )
-- and exists ( select * from mytmp
-- where mytmp.idxname = m2.idxname2
-- and mytmp.part = ( m2.part + 1) ) ;
select * from high_col ;
-- MEDIUM - all columns in a table which do not require high distribution
unload to "a.out" delimiter '|'
select distinct colname
from systables t, syscolumns c
where t.tabid = c.tabid
and t.tabid = $TABID
and c.colno not in ( select col from high_col );
-- HIGH distribution - columns that head an index, one US statement per
index
output to "b.out" without headings
select distinct tabname || '( ' || colname || ' ) ;'
from systables, high_col, syscolumns
where systables.tabid = $TABID
and syscolumns.tabid = $TABID
and syscolumns.colno = high_col.col;
-- generate statement to include fields that requires low distribution
-- for each multi column index, US low on all of its columns
-- unload to "c.out" delimiter '|'
-- select 'update statistics high for table ' || tabname || '(' ||
c1.colname,
-- c2.colname, c3.colname, c4.colname, c5.colname,
-- c6.colname, c7.colname, c8.colname, c9.colname, c10.colname,
-- c11.colname, c12.colname, c13.colname, c14.colname,
-- c15.colname, c16.colname
-- from systables t, sysindexes i,
-- syscolumns c1 , outer syscolumns c2 , outer syscolumns c3 ,
-- outer syscolumns c4, outer syscolumns c5 , outer syscolumns c6 ,
-- outer syscolumns c7 , outer syscolumns c8, outer syscolumns c9 ,
-- outer syscolumns c10, outer syscolumns c11, outer syscolumns c12,
-- outer syscolumns c13, outer syscolumns c14, outer syscolumns c15,
-- outer syscolumns c16
-- where t.tabid = i.tabid
-- and c1.tabid = i.tabid and abs(i.part1) = c1.colno
-- and c2.tabid = i.tabid and abs(i.part2) = c2.colno
-- and c3.tabid = i.tabid and abs(i.part3) = c3.colno
-- and c4.tabid = i.tabid and abs(i.part4) = c4.colno
-- and c5.tabid = i.tabid and abs(i.part5) = c5.colno
-- and c6.tabid = i.tabid and abs(i.part6) = c6.colno
-- and c7.tabid = i.tabid and abs(i.part7) = c7.colno
-- and c8.tabid = i.tabid and abs(i.part8) = c8.colno
-- and c9.tabid = i.tabid and abs(i.part9) = c9.colno
-- and c10.tabid = i.tabid and abs(i.part10) = c10.colno
-- and c11.tabid = i.tabid and abs(i.part11) = c11.colno
-- and c12.tabid = i.tabid and abs(i.part12) = c12.colno
-- and c13.tabid = i.tabid and abs(i.part13) = c13.colno
-- and c14.tabid = i.tabid and abs(i.part14) = c14.colno
-- and c15.tabid = i.tabid and abs(i.part15) = c15.colno
-- and c16.tabid = i.tabid and abs(i.part16) = c16.colno
-- and t.tabname = "$TABNAME"
-- and i.part2 is not null
-- and abs(i.part2) > 0 ;
EOF
if [ `cat a.out | wc -l` -ne 0 ]
then
( echo $TABNAME ; cat a.out ) |
sed '1,1s/^/update statistics medium for table /' |
sed '1,1s/$/(/' |
sed '2,$s/|/,/' | sed '$,$s/,$/ ) distributions only ;/' >>$TABNAME.sql
rm a.out
fi
if [ `cat b.out | wc -l` -ne 2 ]
then
# Write non-empty lines.
sed '
/./ {
p
d
}
:Empty
/^$/ {
N
s/.//
b Empty
}' b.out | sed '1,$s/^/update statistics high for table /'
>>$TABNAME.sql
rm b.out
fi
## if [ `cat c.out | wc -l` -ne 0 ]
## then
## sed '1,$s/||.*/);/' c.out | sed '1,$s/|/,/g' >>$TABNAME.sql
## rm c.out
## fi
}
# ---------------------------------------------------------------------
# This function generates the list of tables on which update statistics
# would be run. This function would not select system catalog tables.
gen_tab_list()
{
DBNAME=$1
# Read system catalog information to generate the list of tables
dbaccess $DBNAME 2>/dev/null <<EOQ1
unload to "tables.list" delimiter " "
select tabname
from systables
where tabtype = "T";
EOQ1
}
# ---------------------------------------------------------------------
# This function gets the tabid for a given table and database
get_tabid()
{
DBNAME=$1
TABNAME=$2
dbaccess $DBNAME 2>/dev/null <<EOF
select tabid from systables where tabname = "$TABNAME"
EOF
}
# ---------------------------------------------------------------------
# This function generate the update statistics script for all tables
gen_sql()
{
DBNAME=$1
echo "Update Statistics script files being generated for $DBNAME"
echo
gen_tab_list $DBNAME
# generate script for tables
for i in `cat tables.list`
do
echo Generating script for table $i
gen_us $DBNAME $i
done
echo
echo "Update Statistics script files generation completed for $DBNAME"
}
# ---------------------------------------------------------------------
# This function executes the scripts in parallel
run_sql()
{
echo
echo "Update Statistics scripts started running for $DBNAME"
DBNAME=$1
CURR=0
gen_tab_list $DBNAME
rm *.out
# run update statistics for all tables
for i in `cat tables.list`
do
# Generate SQL script file
if [ ! -f $US_DIR/$i.sql ]
then
gen_us $DBNAME $i
fi
echo Updating statistics for $i
# run the update statistics script for the current table
echo Started `date` >$US_DIR/$i.out
( dbaccess -e $DBNAME $US_DIR/$i.sql >>$US_DIR/$i.out 2>&1 ;
echo Completed `date` >>$US_DIR/$i.out ) &
# check to see if desired number of processes are running in parallel.
# wait for a process to complete if all are running.
CURR=`ps -lef | grep $US_DIR | grep -v grep | wc -l`
while [ $CURR -eq $COUNT ]
do
sleep 5
CURR=`ps -lef | grep $US_DIR | grep -v grep | wc -l`
done
done # loop - tables
wait
dbaccess $DBNAME <<EOS
update statistics for procedure ;
EOS
echo "Update Statistics run completed for $DBNAME"
exit
}
# ---------------------------------------------------------------------
# MAIN - Start of the program execution
if [ $# -lt 1 -o $# -gt 3 ]
then
echo "Usage : $0 <dbname> <run_options> <processes>"
echo
echo "dbname - database name, required"
echo "run_options - "
echo " BOTH - Generates SQL and RUN, the default"
echo " SQL - Generates SQL files only"
echo " RUN - Runs SQL files only"
echo " if SQL file does not exist for a table, generates
it"
echo "processes - No of parallel threads/processes "
echo " - applicable for RUN and BOTH, defaults to 1 "
echo
echo "NOTE :-"
echo
echo "Incorrect values for <run_options> and <processes> will use
defaults"
echo
exit
fi
DBNAME=$1
export US_DIR=/dataconv/$DBNAME.stats
export SMALL_TAB=1000
export LARGE_TAB=1000000
if [ -f $US_DIR ]
then
echo "Error : $US_DIR is a file, not a directory. STOP"
exit
fi
if [ ! -d $US_DIR ]
then
mkdir $US_DIR
if [ $? -gt 0 ]
then
echo "Error : Unable to create/use $US_DIR. STOP"
exit
fi
fi
cd $US_DIR
if [ $# -eq 2 ]
then
RUN_OPT=$2
COUNT=1
elif [ $# -eq 3 ]
then
RUN_OPT=$2
COUNT=$3
else
RUN_OPT="BOTH"
COUNT=1
fi
COUNT=`expr $COUNT + 0 2>/dev/null`
if [ $? -gt 0 ]
then
COUNT=1
fi
if [ "$RUN_OPT" = "SQL" ]
then
gen_sql $DBNAME
elif [ "$RUN_OPT" = "RUN" ]
then
run_sql $DBNAME $COUNT
else
gen_sql $DBNAME
run_sql $DBNAME $COUNT
fi
exit
Robert Phillips
Systems Analyst
Chamberlin Edmonds and Associates (www.ce-a.com)
404-634-5196 x1261
Messages In This Thread
- Informix Lockup (Wierd)
Phillips, Rob -- Thursday, 30 January 2003, at 3:14 p.m.
IDS Forum is maintained by Administrator with WebBBS 5.12.
|
|
