I wrote a esql/c program, which read about 100,000 records from a
plain data file one bye one while inserted(or updated) them into a
local database.

When the progress go to about 20,000 records, the program seems halt(
ver very slow!), and the hard disk keeps flashing for about 2 hours.
The sar -u reports:

17:47:16 %usr %sys %wio %idle (-u)
17:47:17 0 2 98 0
17:47:18 0 0 100 0
17:47:20 0 3 97 0
17:47:23 0 1 99 0
17:47:24 0 2 65 33
17:47:25 1 3 64 32
17:47:26 0 1 42 57
17:47:27 0 6 94 0

sar -d reports:
17:49:06 device %busy avque r+w/s blks/s avwait
avserv (-d)
17:49:07 wd-0 95.05 1.08 830.69 6693.07 0.10
1.14
17:49:08 wd-0 97.03 1.08 837.62 6700.99 0.09
1.16
17:49:09 wd-0 99.01 1.00 790.10 6320.79 0.00
1.25
17:49:10 wd-0 100.00 1.25 280.20 2225.74 0.88
3.57
17:49:11 wd-0 98.02 1.01 343.56 2748.51 0.03
2.85

sar -r reports:
17:49:46 freemem freeswp availrmem availsmem (-r)
17:49:47 1245 224576 11548 23929
17:49:48 953 229240 11548 23929
17:49:49 1394 225128 11548 23929
17:49:50 1175 226896 11548 23929
17:49:51 1001 228248 11548 23929
17:49:52 840 229544 11548 23929
17:49:53 703 230664 11548 23929
17:49:54 1196 224192 11548 23929

My system is SCO OSR5 + Informix Online 7.x with 128M ram. The
database is created w/o loging.

Is any method here let me know what the Informix is doing?


Thans in advance.
--
steven (AT) lczmsoft (DOT) com

onstat -u - user sessions
onstat -g ses session_id - detailed session info
onstat -d - chunk info

For starters

----- Original Message -----
From: "steven" <steven (AT) steven4u (DOT) net>
To: <informix-list (AT) iiug (DOT) org>
Sent: Thursday, September 11, 2003 05:54
Subject: [HELP] What my Informix is doing?

steven (AT) steven4u (DOT) net (steven) wrote in message news:<1e7ef8d0.0309110154.29fa2925 (AT) posting (DOT) google.com>...
Google through c.d.i and look for words like performance, slow, and
tuning. Take particular notice of the onstat commands that are
requested. Run those commands on your engine and post the results
here. Post the hardware configuration and OS that the engine is
running on. You haven't posted enough information for anyone to help
you.

Dave Thacker

Well,

Since your wio percentage is so high, I suspect that your page flusher are
rather busy. Do you have LRU Min set to zero?


"steven" <steven (AT) steven4u (DOT) net> wrote

ok, I run some onstat and get the following:

--------------------------- cut
----------------------------------------------
$ onstat -u

INFORMIX-OnLine Version 7.22.UC2 -- On-Line -- Up 21:04:15 -- 805400
Kbytes

Userthreads
address flags sessid user tty wait tout locks nreads
nwrites
85c40014 ---P--D 1 informix - 0 0 0 27
5551
85c40448 ---P--F 0 informix - 0 0 0 0
34200
85c4087c ---P--B 9 informix - 0 0 0 0
0
85c410e4 ---P--- 14 informix ttyp0 0 0 0 0
0
85c41518 ---P--D 12 informix - 0 0 0 0
0
5 active, 128 total, 17 maximum concurrent

$ onstat -g ses 14

INFORMIX-OnLine Version 7.22.UC2 -- On-Line -- Up 21:01:06 -- 805400
Kbytes

session #RSAM total used
id user tty pid hostname threads memory memory
14 informix ttyp0 997 lczmdbsv 1 54476800 54413184

tid name rstcb flags curstk status
41 sqlexec 85c410e4 ---P--- 816 85c410e4 running

Memory pools count 1
name class addr totalsize freesize #allocfrag #freefrag
14 V 86040014 54476800 61568 52661 105

name free used name free
used
overhead 0 108 scb 0 80
opentable 0 1084 filetable 0 384
log 0 2132 temprec 0 828
ralloc 0 52016432 gentcb 0
7636
ostcb 0 1968 sqscb 0
2381260
hashfiletab 0 276 osenv 0
2360
sqtcb 0 644 fragman 0 40


Sess SQL Current Iso Lock SQL ISAM F.E.
Id Stmt type Database Lvl Mode ERR ERR Vers
14 - - - Not Wait -349 0 7.22

Last parsed SQL statement :
insert into t_loan_retint (tran_date, host_seqno, clt_seqno,
tran_no,
loan_acc, clr_int_date, accu, rate, rate_type, int_recv_acc,
payint_acc,
holdint_acc, rb_int, crt_retint, rcpt_int, prt_num, flag) values
(?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);

$ onstat -g sql 14

INFORMIX-OnLine Version 7.22.UC2 -- On-Line -- Up 21:06:16 -- 805400
Kbytes

Sess SQL Current Iso Lock SQL ISAM F.E.
Id Stmt type Database Lvl Mode ERR ERR Vers
14 INSERT - - Not Wait -349 0 7.22

Current SQL statement :
insert into t_loan_retint (tran_date, host_seqno, clt_seqno,
tran_no,
loan_acc, clr_int_date, accu, rate, rate_type, int_recv_acc,
payint_acc,
holdint_acc, rb_int, crt_retint, rcpt_int, prt_num, flag) values
(?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);

Last parsed SQL statement :
insert into t_loan_retint (tran_date, host_seqno, clt_seqno,
tran_no,
loan_acc, clr_int_date, accu, rate, rate_type, int_recv_acc,
payint_acc,
holdint_acc, rb_int, crt_retint, rcpt_int, prt_num, flag) values
(?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);

$ onstat -d

INFORMIX-OnLine Version 7.22.UC2 -- On-Line -- Up 21:07:11 -- 805400
Kbytes

Dbspaces
address number flags fchunk nchunks flags owner name
85c3e0ec 1 1 1 1 N informix rootdbs
85c3e5b0 2 1 2 1 N informix dbcrm
85c3e61c 3 1 3 3 N informix
dboriginal
3 active, 2047 maximum

Chunks
address chk/dbs offset size free bpages flags pathname
85c3e158 1 1 0 977956 886579 PO-
/usr/informix/dbsps/rootdb
85c3e260 2 2 0 950000 488106 PO-
/usr/informix/dbsps/lczmdb1
85c3e334 3 3 0 950000 273565 PO-
/usr/informix/dbsps/lczmdb2
85c3e408 4 3 0 950000 61389 PO-
/usr/informix/dbsps/lczmdb3
85c3e4dc 5 3 0 950000 214960 PO-
/usr/informix/dbsps/lczmdb4
5 active, 2047 maximum
---------------------- cut
-------------------------------------------------

Any ideal?

$ grep LRU $ONCONFIG
LRUS 8 # Number of LRU queues
LRU_MAX_DIRTY 60 # LRU percent dirty begin cleaning limit
LRU_MIN_DIRTY 50 # LRU percent dirty end cleaning limit

so i think the min LRUS was set to 8.

"Madison Pruet" <mpruet (AT) comcast (DOT) net> wrote

Looks like you have a programatic error.

onstat -g ses 14 indicates:

The sql error is -349 which means "database not open". This error occurred
when the insert statement was processed.

I think you need to review your code.

Mark

sending to informix-list

"Mark Denham" <mkdenham (AT) comcast (DOT) net> wrote

Oh! thank you. The error is not in my code but in a config file, for
my code read the config file to know what database it should operate
on. After correct the db name spelling in the config, the program
performance well.

But it still keep confusing me that just a failed EXEC SQL DATABASE
command can pull the Informix so slow. :-(

-
steven (AT) lczmsoft (DOT) com