Slow reposnse

7.31 FD5 on HP-UX 11.0 Users are complaining that their data loads are getting virtually no activity. The logs are not full, there are no untoward messages in online log, and glance/top shows the UNIX host be gently loaded Even trying to use dbaccess to get info about the table syssessions on database sysmaster is freezing. onstat -g ses shows as below. Can anyone interpret the flags for me? thanks Neil $ onstat -g ses 2259 Informix Dynamic Server Version 7.31.FD5 -- On-Line -- Up 05:15:23 -- 2993496 Kbytes session #RSAM total used id user tty pid hostname threads memory memory 2259 informix th 538 info01 4 131072 123232 tid name rstcb flags curstk status 189378 sqlexec c0000000a08b55e8 ---P--- 267584 sleeping(secs: 2) 189434 join_1.0 c0000000a08b4f00 Y------ 268416 cond wait(await_MC1) 189435 join_1.1 c00000009dc59e68 Y------ 268416 cond wait(await_MC1) 189436 scan_2.0 c0000000a43f2fb0 Y------ 268416 cond wait(await_MC2) Memory pools count 1 name class addr totalsize freesize #allocfrag #freefrag 2259 V c0000000a0975028 131072 7840 438 2 name free used name free used overhead 0 184 scb 0 144 opentable 0 6856 filetable 0 3512 log 0 8672 temprec 0 4360 keys 0 272 ralloc 0 46200 gentcb 0 15304 ostcb 0 3368 sqscb 0 10880 rdahead 0 864 xchg_desc 0 3624 xchg_port 0 2512 xchg_packet 0 1744 xchg_group 0 208 xchg_priv 0 536 hashfiletab 0 2208 osenv 0 3080 sqtcb 0 7104 fragman 0 584 shmblklist 0 1016 Sess SQL Current Iso Lock SQL ISAM F.E. Id Stmt type Database Lvl Mode ERR ERR Vers 2259 SELECT sysmaster CR Not Wait 0 0 7.31 Current statement name : colcur Current SQL statement : select colno, colname, coltype, collength from informix.syscolumns, informix.systables where informix.syscolumns.tabid = informix.systables.tabid and tabname = ? and owner = ? order by informix.syscolumns.colno; Last parsed SQL statement : select colno, colname, coltype, collength from informix.syscolumns, informix.systables where informix.syscolumns.tabid = informix.systables.tabid and tabname = ? and owner = ? order by informix.syscolumns.colno; $

"Neil Truby" <neil.truby@ardenta.com> wrote in message news:bvdt8c$qpn8p$1@ID-162943.news.uni-berlin.de... > 7.31 FD5 on HP-UX 11.0 In fact, more generally, any select. I've created a test table, inserted a single row (instantaneous), then tried to select it -... takes about 5 or 10 minutes: Very curious! regards Neil $ onstat -g sql 2285 Informix Dynamic Server Version 7.31.FD5 -- On-Line -- Up 05:40:40 -- 2993496 Kbytes Sess SQL Current Iso Lock SQL ISAM F.E. Id Stmt type Database Lvl Mode ERR ERR Vers 2285 SELECT con_pub_mart CR Not Wait 0 0 7.31 Current statement name : slctcur Current SQL statement : select * from ardenta_test Last parsed SQL statement : select * from ardenta_test $ onstat -g ses 2285 Informix Dynamic Server Version 7.31.FD5 -- On-Line -- Up 05:40:46 -- 2993496 Kbytes session #RSAM total used id user tty pid hostname threads memory memory 2285 informix ti 1240 info01 2 65536 60512 tid name rstcb flags curstk status 189847 sqlexec c0000000a43edcd0 ---P--- 267584 sleeping(secs: 1) 189848 scan_1.0 c00000009dc62888 Y------ 268416 cond wait(await_MC1) Memory pools count 1 name class addr totalsize freesize #allocfrag #freefrag 2285 V c0000000a77c3028 65536 5024 319 1 name free used name free used overhead 0 184 scb 0 144 opentable 0 3760 filetable 0 1680 log 0 4336 temprec 0 1776 keys 0 96 ralloc 0 8192 gentcb 0 14024 ostcb 0 3368 sqscb 0 10488 rdahead 0 864 xchg_desc 0 1200 xchg_port 0 1112 xchg_packet 0 408 xchg_group 0 104 xchg_priv 0 320 hashfiletab 0 1104 osenv 0 3080 sqtcb 0 3520 fragman 0 392 shmblklist 0 360 Sess SQL Current Iso Lock SQL ISAM F.E. Id Stmt type Database Lvl Mode ERR ERR Vers 2285 SELECT con_pub_mart CR Not Wait 0 0 7.31 Current statement name : slctcur Current SQL statement : select * from ardenta_test Last parsed SQL statement : select * from ardenta_test

Neil Truby wrote: > "Neil Truby" <neil.truby@ardenta.com> wrote in message > news:bvdt8c$qpn8p$1@ID-162943.news.uni-berlin.de... > >>7.31 FD5 on HP-UX 11.0 > > > In fact, more generally, any select. > I've created a test table, inserted a single row (instantaneous), then tried > to select it -... takes about 5 or 10 minutes: > Very curious! > > regards > Neil > > $ onstat -g sql 2285> > Informix Dynamic Server Version 7.31.FD5 -- On-Line -- Up 05:40:40 -- > 2993496 Kbytes > > Sess SQL Current Iso Lock SQL ISAM F.E. > Id Stmt type Database Lvl Mode ERR ERR Vers > 2285 SELECT con_pub_mart CR Not Wait 0 0 7.31 > > Current statement name : slctcur > > Current SQL statement : > select * from ardenta_test> > Last parsed SQL statement : > select * from ardenta_test> > $ onstat -g ses 2285> > Informix Dynamic Server Version 7.31.FD5 -- On-Line -- Up 05:40:46 -- > 2993496 Kbytes > > session #RSAM total used > id user tty pid hostname threads memory memory > 2285 informix ti 1240 info01 2 65536 60512 > > tid name rstcb flags curstk status > 189847 sqlexec c0000000a43edcd0 ---P--- 267584 sleeping(secs: 1) > 189848 scan_1.0 c00000009dc62888 Y------ 268416 cond wait(await_MC1) > > Memory pools count 1 > name class addr totalsize freesize #allocfrag > #freefrag > 2285 V c0000000a77c3028 65536 5024 319 1 > > name free used name free used > overhead 0 184 scb 0 144 > opentable 0 3760 filetable 0 1680 > log 0 4336 temprec 0 1776 > keys 0 96 ralloc 0 8192 > gentcb 0 14024 ostcb 0 3368 > sqscb 0 10488 rdahead 0 864 > xchg_desc 0 1200 xchg_port 0 1112 > xchg_packet 0 408 xchg_group 0 104 > xchg_priv 0 320 hashfiletab 0 1104 > osenv 0 3080 sqtcb 0 3520 > fragman 0 392 shmblklist 0 360 > > Sess SQL Current Iso Lock SQL ISAM F.E. > Id Stmt type Database Lvl Mode ERR ERR Vers > 2285 SELECT con_pub_mart CR Not Wait 0 0 7.31 > > Current statement name : slctcur > > Current SQL statement : > select * from ardenta_test> > Last parsed SQL statement : > select * from ardenta_test> > Did you start the engine with PDQPRIORITY on? Apparently even simple queries have several threads... Are you issuing grant/revoke statements in big batches? Regards.

"Fernando Nunes" <spam@domus.online.pt> wrote in message news:bve3cb$qvajr$1@ID-161111.news.uni-berlin.de... > Did you start the engine with PDQPRIORITY on? > Apparently even simple queries have several threads... > > Are you issuing grant/revoke statements in big batches? Indeed, and many thanks to Paul Watson of ONINIT LIMITED, the UK's second-best IBM Data Management Business Partner, for pointing out the problem first. Which was that EVERY user session, irrespective of what it does, sources an Informix profile that sets PDQPRIORITY. So, the combination of MAX_PDQPRIORITY: 40 DS_MAX_QUERIES: 10 DS_MAX_SCANS: 1048576 DS_TOTAL_MEMORY: 92160 KB in the engine, and PDQPRIORITY=20 for every user is royally shafting everything by causing every query, no matter how trivial, to use the PDQ resource. Is this a bug, or working as specified? cheers Neil -- Neil Truby t:01932 724027 Director m:07798 811708 Ardenta Limited e:neil.truby@ardenta.com

> Indeed, and many thanks to Paul Watson of ONINIT LIMITED, the UK's > second-best IBM Data Management Business Partner, Cheek :-))) -- Paul Watson # Oninit Ltd # Growing old is mandatory Tel: +44 1436 672201 # Growing up is optional Fax: +44 1436 678693 # Mob: +44 7818 003457 # www.oninit.com #

Neil Truby wrote: > "Fernando Nunes" <spam@domus.online.pt> wrote in message > news:bve3cb$qvajr$1@ID-161111.news.uni-berlin.de... > >>Did you start the engine with PDQPRIORITY on? >>Apparently even simple queries have several threads... >> >>Are you issuing grant/revoke statements in big batches? > > > Indeed, and many thanks to Paul Watson of ONINIT LIMITED, the UK's > second-best IBM Data Management Business Partner, for pointing out the > problem first. > Which was that EVERY user session, irrespective of what it does, sources an > Informix profile that sets PDQPRIORITY. > > So, the combination of > > MAX_PDQPRIORITY: 40 > DS_MAX_QUERIES: 10 > DS_MAX_SCANS: 1048576 > DS_TOTAL_MEMORY: 92160 KB > > in the engine, and > > PDQPRIORITY=20 > > for every user is royally shafting everything by causing every query, no > matter how trivial, to use the PDQ resource. > > Is this a bug, or working as specified? Depends. If it is $INFORMIXDIR/etc/informixrc (or whatever the filename actually is - the one under $INFORMIXDIR, anyway), then that is the documented behaviour. And setting PDQPRIORITY in it is intended to work for everybody - so (if I'm interpreting your scenario correctly), the engine is behaving exactly as you told it to, however unwittingly, and however ill-advisedly :-) You can also have individual $HOME/.informixrc files. And you can really slow things up by having big files - it appears that connect time is awful, but actually the problem is the time it takes to read that file. I had a system that was starting sessions glacially - it wasn't until midday that I remembered I'd been doing some performance testing on these files and that I had a nasty big one left over. (And, incidentally, the place it looks is actually determined by the home directory specified in /etc/passwd or equivalent, not by the value of $HOME - for most people, most of the time, they're the same place, of course. I usually have $HOME set to a local file system, but ~jleffler (the value in /etc/passwd) is on an automounted file system, so I can spot the difference.) -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

"Jonathan Leffler" <jleffler@earthlink.net> wrote in message news:hUGSb.4278$F23.1269@newsread2.news.pas.earthlink.net... > Neil Truby wrote: > > > "Fernando Nunes" <spam@domus.online.pt> wrote in message > > news:bve3cb$qvajr$1@ID-161111.news.uni-berlin.de... > Depends. If it is $INFORMIXDIR/etc/informixrc (or whatever the > filename actually is - the one under $INFORMIXDIR, anyway), then that > is the documented behaviour. And setting PDQPRIORITY in it is > intended to work for everybody - so (if I'm interpreting your scenario > correctly), the engine is behaving exactly as you told it to, however > unwittingly, and however ill-advisedly :-) There's no need to spare my feelings, Jonathan, I was just trying to fix soneone else's system ...! > You can also have individual $HOME/.informixrc files. And you can > really slow things up by having big files - it appears that connect > time is awful, but actually the problem is the time it takes to read > that file Huh? Can you explain this further ....?