Multiple user threads
Hello everybody,
we're using version 9.40.T7
Our max_pdqpriority is 100.
Right now, I see much this behaviour with onstat -u. Informix creates plenty
of user threads. If I set with the max_pdqpriority with onmode -D to 0.
Then I get a single user session which shows me sometimes 5 lock, sometimes
20 (while queries are running).
The weird thing is that I don't use set pdqpriority on client side. We're
finetuning our server and playing around with the settings.
I'm only afraid that those threads stay open (as I can see, I see lots of
cond waits(mc_1) etc). And that they can affect performance.
Is this a normal behaviour ? Do you need additional stats ? I also see
locks but I already received all my data.
Thanks,
Bjorn
onstat -u output.
3fa9e224 Y------ 84 informix NB070148 40a498b8 0 1 0 0
3fa9e838 Y--P--- 70 informix FSYS326 40f17380 0 1 33 0
3faa1eec Y------ 84 informix NB070148 410d0018 0 1 0 0
3faa373c Y------ 84 informix NB070148 40fa81a8 0 1 0 0
3faa4978 Y------ 84 informix NB070148 408dc480 0 1 0 0
3faa61c8 Y------ 84 informix NB070148 40892228 0 1 0 0
413f3018 Y------ 84 informix NB070148 41787c80 0 1 0 0
413f362c Y------ 84 informix NB070148 40a49650 0 1 0 0
413f4e7c Y------ 84 informix NB070148 40892228 0 1 0 0
413f5aa4 Y------ 84 informix NB070148 41c966e8 0 1 0 0
413f60b8 Y------ 84 informix NB070148 408dc480 0 1 0 0
413f66cc Y------ 84 informix NB070148 410d0018 0 1 0 0
413f6ce0 Y------ 84 informix NB070148 40a49650 0 1 0 0
413f7908 Y--P--- 83 informix NB070148 4163bc58 0 1 1514 0
413f7f1c Y------ 84 informix NB070148 410d0638 0 1 0 0
413f8530 Y------ 84 informix NB070148 3fd31ee0 0 1 0 0
413f8b44 Y--P--- 84 informix NB070148 41843920 0 1 3 70
413f9158 Y------ 84 informix NB070148 40efc4d8 0 1 0 0
413f976c Y------ 84 informix NB070148 40a496f8 0 1 0 0
413f9d80 Y------ 84 informix NB070148 40a496f8 0 1 0 0
413fafbc Y------ 84 informix NB070148 40a496f8 0 1 0 0
413fc1f8 Y------ 84 informix NB070148 3fd31f88 0 1 0 0
413fda48 Y------ 84 informix NB070148 40a496f8 0 1 0 0
413ffec0 Y------ 84 informix NB070148 419dc7e8 0 1 0 0
414010fc Y------ 84 informix NB070148 41787cd0 0 1 0 0
41401d24 Y------ 84 informix NB070148 40849f08 0 1 0 0
41402f60 Y------ 84 informix NB070148 408dc480 0 1 0 0
41403574 Y------ 84 informix NB070148 41136b58 0 1 0 0
414047b0 Y------ 84 informix NB070148 410d0638 0 1 0 0
414096b4 Y------ 84 informix NB070148 40a496f8 0 1 0 0
4140d37c Y------ 84 informix NB070148 40a496f8 0 1 0 0
4140dfa4 Y------ 84 informix NB070148 41136b58 0 1 0 0
On Wed, 14 Jul 2004 12:07:01 -0400, Bjorn De Waele wrote:
This mystery is usually caused by having compiled stored procedures with a
non-zero PDQPRIORITY in effect. This compiles in a run-time PDQPRIORITY for
the procedure which you likely do not want. Recompile all stored procedures
(UPDATE STATISTICS FOR PROCEDURE procname;) in a session with PDQPRIORITY set
to zero and this effect should disappear.
This is why my dostats utility has separate flags to set PDQPRIORITY for
updating stats for tables and for compiling stored procedures.
Art S. Kagel
> Hello everybody,
>
>
> we're using version 9.40.T7
> Our max_pdqpriority is 100.
>
> Right now, I see much this behaviour with onstat -u. Informix creates plenty
> of user threads. If I set with the max_pdqpriority with onmode -D to 0.
> Then I get a single user session which shows me sometimes 5 lock, sometimes
> 20 (while queries are running).
> The weird thing is that I don't use set pdqpriority on client side. We're
> finetuning our server and playing around with the settings. I'm only afraid
> that those threads stay open (as I can see, I see lots of cond waits(mc_1)
> etc). And that they can affect performance. Is this a normal behaviour ? Do
> you need additional stats ? I also see locks but I already received all my
> data.
>
> Thanks,
>
> Bjorn
>
> onstat -u output.>
>
> 3fa9e224 Y------ 84 informix NB070148 40a498b8 0 1 0 0
> 3fa9e838 Y--P--- 70 informix FSYS326 40f17380 0 1 33 0
> 3faa1eec Y------ 84 informix NB070148 410d0018 0 1 0 0
> 3faa373c Y------ 84 informix NB070148 40fa81a8 0 1 0 0
> 3faa4978 Y------ 84 informix NB070148 408dc480 0 1 0 0
> 3faa61c8 Y------ 84 informix NB070148 40892228 0 1 0 0
> 413f3018 Y------ 84 informix NB070148 41787c80 0 1 0 0
> 413f362c Y------ 84 informix NB070148 40a49650 0 1 0 0
> 413f4e7c Y------ 84 informix NB070148 40892228 0 1 0 0
> 413f5aa4 Y------ 84 informix NB070148 41c966e8 0 1 0 0
> 413f60b8 Y------ 84 informix NB070148 408dc480 0 1 0 0
> 413f66cc Y------ 84 informix NB070148 410d0018 0 1 0 0
> 413f6ce0 Y------ 84 informix NB070148 40a49650 0 1 0 0
> 413f7908 Y--P--- 83 informix NB070148 4163bc58 0 1 1514 0
> 413f7f1c Y------ 84 informix NB070148 410d0638 0 1 0 0
> 413f8530 Y------ 84 informix NB070148 3fd31ee0 0 1 0 0
> 413f8b44 Y--P--- 84 informix NB070148 41843920 0 1 3 70
> 413f9158 Y------ 84 informix NB070148 40efc4d8 0 1 0 0
> 413f976c Y------ 84 informix NB070148 40a496f8 0 1 0 0
> 413f9d80 Y------ 84 informix NB070148 40a496f8 0 1 0 0
> 413fafbc Y------ 84 informix NB070148 40a496f8 0 1 0 0
> 413fc1f8 Y------ 84 informix NB070148 3fd31f88 0 1 0 0
> 413fda48 Y------ 84 informix NB070148 40a496f8 0 1 0 0
> 413ffec0 Y------ 84 informix NB070148 419dc7e8 0 1 0 0
> 414010fc Y------ 84 informix NB070148 41787cd0 0 1 0 0
> 41401d24 Y------ 84 informix NB070148 40849f08 0 1 0 0
> 41402f60 Y------ 84 informix NB070148 408dc480 0 1 0 0
> 41403574 Y------ 84 informix NB070148 41136b58 0 1 0 0
> 414047b0 Y------ 84 informix NB070148 410d0638 0 1 0 0
> 414096b4 Y------ 84 informix NB070148 40a496f8 0 1 0 0
> 4140d37c Y------ 84 informix NB070148 40a496f8 0 1 0 0
> 4140dfa4 Y------ 84 informix NB070148 41136b58 0 1 0 0
Hello Art,
thank your for your answer. I'll check tomorrow if there are any stored
procedures.
I can't use my winsql tool right now. Do you know in which table I can see
my stored procs ?
Thanks,
Bjorn
"Art S. Kagel" <kagel@bloomberg.net> wrote in message
news:pan.2004.07.14.16.06.43.735485.1355@bloomberg.net...
> On Wed, 14 Jul 2004 12:07:01 -0400, Bjorn De Waele wrote:
>
> This mystery is usually caused by having compiled stored procedures with a
> non-zero PDQPRIORITY in effect. This compiles in a run-time PDQPRIORITY
for
> the procedure which you likely do not want. Recompile all stored
procedures
> (UPDATE STATISTICS FOR PROCEDURE procname;) in a session with PDQPRIORITY
set
> to zero and this effect should disappear.
>
> This is why my dostats utility has separate flags to set PDQPRIORITY for
> updating stats for tables and for compiling stored procedures.
>
> Art S. Kagel
>
> > Hello everybody,
> >
> >
> > we're using version 9.40.T7
> > Our max_pdqpriority is 100.
> >
> > Right now, I see much this behaviour with onstat -u. Informix creates
plenty
> > of user threads. If I set with the max_pdqpriority with onmode -D to 0.
> > Then I get a single user session which shows me sometimes 5 lock,
sometimes
> > 20 (while queries are running).
> > The weird thing is that I don't use set pdqpriority on client side.
We're
> > finetuning our server and playing around with the settings. I'm only
afraid
> > that those threads stay open (as I can see, I see lots of cond
waits(mc_1)
> > etc). And that they can affect performance. Is this a normal behaviour
? Do
> > you need additional stats ? I also see locks but I already received all
my
> > data.
> >
> > Thanks,
> >
> > Bjorn
> >
> > onstat -u output.> >
> >
> > 3fa9e224 Y------ 84 informix NB070148 40a498b8 0 1 0
0
> > 3fa9e838 Y--P--- 70 informix FSYS326 40f17380 0 1 33
0
> > 3faa1eec Y------ 84 informix NB070148 410d0018 0 1 0
0
> > 3faa373c Y------ 84 informix NB070148 40fa81a8 0 1 0
0
> > 3faa4978 Y------ 84 informix NB070148 408dc480 0 1 0
0
> > 3faa61c8 Y------ 84 informix NB070148 40892228 0 1 0
0
> > 413f3018 Y------ 84 informix NB070148 41787c80 0 1 0
0
> > 413f362c Y------ 84 informix NB070148 40a49650 0 1 0
0
> > 413f4e7c Y------ 84 informix NB070148 40892228 0 1 0
0
> > 413f5aa4 Y------ 84 informix NB070148 41c966e8 0 1 0
0
> > 413f60b8 Y------ 84 informix NB070148 408dc480 0 1 0
0
> > 413f66cc Y------ 84 informix NB070148 410d0018 0 1 0
0
> > 413f6ce0 Y------ 84 informix NB070148 40a49650 0 1 0
0
> > 413f7908 Y--P--- 83 informix NB070148 4163bc58 0 1 1514
0
> > 413f7f1c Y------ 84 informix NB070148 410d0638 0 1 0
0
> > 413f8530 Y------ 84 informix NB070148 3fd31ee0 0 1 0
0
> > 413f8b44 Y--P--- 84 informix NB070148 41843920 0 1 3
70
> > 413f9158 Y------ 84 informix NB070148 40efc4d8 0 1 0
0
> > 413f976c Y------ 84 informix NB070148 40a496f8 0 1 0
0
> > 413f9d80 Y------ 84 informix NB070148 40a496f8 0 1 0
0
> > 413fafbc Y------ 84 informix NB070148 40a496f8 0 1 0
0
> > 413fc1f8 Y------ 84 informix NB070148 3fd31f88 0 1 0
0
> > 413fda48 Y------ 84 informix NB070148 40a496f8 0 1 0
0
> > 413ffec0 Y------ 84 informix NB070148 419dc7e8 0 1 0
0
> > 414010fc Y------ 84 informix NB070148 41787cd0 0 1 0
0
> > 41401d24 Y------ 84 informix NB070148 40849f08 0 1 0
0
> > 41402f60 Y------ 84 informix NB070148 408dc480 0 1 0
0
> > 41403574 Y------ 84 informix NB070148 41136b58 0 1 0
0
> > 414047b0 Y------ 84 informix NB070148 410d0638 0 1 0
0
> > 414096b4 Y------ 84 informix NB070148 40a496f8 0 1 0
0
> > 4140d37c Y------ 84 informix NB070148 40a496f8 0 1 0
0
> > 4140dfa4 Y------ 84 informix NB070148 41136b58 0 1 0
0
On Wed, 14 Jul 2004 17:32:28 -0400, Bjorn De Waele wrote:
The system catalog's stored procedure header table is sysprocedures, the body
of the proc source, the compiled byte code, and the query plan byte code are
all stored in sysprocbody with different values in the datakey column.
However, you can just use:
dbschema -d mydatabase -f [all|<procname>]and similarly if you prefer using myschema:
myschema -d mydatabase -f [[all|*]|[<procspec>|<procname>]
(procspec uses MATCHES style wildcarding). FYI, there is no indication of
what the PDQPRIORITY was at compile time either in the dbschema/myschema
output or in the system catalog tables.
Art S. Kagel
> Hello Art,
>
> thank your for your answer. I'll check tomorrow if there are any stored
> procedures.
>
> I can't use my winsql tool right now. Do you know in which table I can see
> my stored procs ?
>
> Thanks,
>
> Bjorn
>
>
> "Art S. Kagel" <kagel@bloomberg.net> wrote in message
> news:pan.2004.07.14.16.06.43.735485.1355@bloomberg.net...
>> On Wed, 14 Jul 2004 12:07:01 -0400, Bjorn De Waele wrote:
>>
>> This mystery is usually caused by having compiled stored procedures with a
>> non-zero PDQPRIORITY in effect. This compiles in a run-time PDQPRIORITY
> for
>> the procedure which you likely do not want. Recompile all stored
> procedures
>> (UPDATE STATISTICS FOR PROCEDURE procname;) in a session with PDQPRIORITY
> set
>> to zero and this effect should disappear.
>>
>> This is why my dostats utility has separate flags to set PDQPRIORITY for
>> updating stats for tables and for compiling stored procedures.
>>
>> Art S. Kagel
>>
>> > Hello everybody,
>> >
>> >
>> > we're using version 9.40.T7
>> > Our max_pdqpriority is 100.
>> >
>> > Right now, I see much this behaviour with onstat -u. Informix creates
> plenty
>> > of user threads. If I set with the max_pdqpriority with onmode -D to 0.
>> > Then I get a single user session which shows me sometimes 5 lock,
> sometimes
>> > 20 (while queries are running).
>> > The weird thing is that I don't use set pdqpriority on client side.
> We're
>> > finetuning our server and playing around with the settings. I'm only
> afraid
>> > that those threads stay open (as I can see, I see lots of cond
> waits(mc_1)
>> > etc). And that they can affect performance. Is this a normal behaviour
> ? Do
>> > you need additional stats ? I also see locks but I already received all
> my
>> > data.
>> >
>> > Thanks,
>> >
>> > Bjorn
>> >
>> > onstat -u output.>> >
>> >
>> > 3fa9e224 Y------ 84 informix NB070148 40a498b8 0 1 0
> 0
>> > 3fa9e838 Y--P--- 70 informix FSYS326 40f17380 0 1 33
> 0
>> > 3faa1eec Y------ 84 informix NB070148 410d0018 0 1 0
> 0
>> > 3faa373c Y------ 84 informix NB070148 40fa81a8 0 1 0
> 0
>> > 3faa4978 Y------ 84 informix NB070148 408dc480 0 1 0
> 0
>> > 3faa61c8 Y------ 84 informix NB070148 40892228 0 1 0
> 0
>> > 413f3018 Y------ 84 informix NB070148 41787c80 0 1 0
> 0
>> > 413f362c Y------ 84 informix NB070148 40a49650 0 1 0
> 0
>> > 413f4e7c Y------ 84 informix NB070148 40892228 0 1 0
> 0
>> > 413f5aa4 Y------ 84 informix NB070148 41c966e8 0 1 0
> 0
>> > 413f60b8 Y------ 84 informix NB070148 408dc480 0 1 0
> 0
>> > 413f66cc Y------ 84 informix NB070148 410d0018 0 1 0
> 0
>> > 413f6ce0 Y------ 84 informix NB070148 40a49650 0 1 0
> 0
>> > 413f7908 Y--P--- 83 informix NB070148 4163bc58 0 1 1514
> 0
>> > 413f7f1c Y------ 84 informix NB070148 410d0638 0 1 0
> 0
>> > 413f8530 Y------ 84 informix NB070148 3fd31ee0 0 1 0
> 0
>> > 413f8b44 Y--P--- 84 informix NB070148 41843920 0 1 3
> 70
>> > 413f9158 Y------ 84 informix NB070148 40efc4d8 0 1 0
> 0
>> > 413f976c Y------ 84 informix NB070148 40a496f8 0 1 0
> 0
>> > 413f9d80 Y------ 84 informix NB070148 40a496f8 0 1 0
> 0
>> > 413fafbc Y------ 84 informix NB070148 40a496f8 0 1 0
> 0
>> > 413fc1f8 Y------ 84 informix NB070148 3fd31f88 0 1 0
> 0
>> > 413fda48 Y------ 84 informix NB070148 40a496f8 0 1 0
> 0
>> > 413ffec0 Y------ 84 informix NB070148 419dc7e8 0 1 0
> 0
>> > 414010fc Y------ 84 informix NB070148 41787cd0 0 1 0
> 0
>> > 41401d24 Y------ 84 informix NB070148 40849f08 0 1 0
> 0
>> > 41402f60 Y------ 84 informix NB070148 408dc480 0 1 0
> 0
>> > 41403574 Y------ 84 informix NB070148 41136b58 0 1 0
> 0
>> > 414047b0 Y------ 84 informix NB070148 410d0638 0 1 0
> 0
>> > 414096b4 Y------ 84 informix NB070148 40a496f8 0 1 0
> 0
>> > 4140d37c Y------ 84 informix NB070148 40a496f8 0 1 0
> 0
>> > 4140dfa4 Y------ 84 informix NB070148 41136b58 0 1 0
> 0
Hello Art,
thanks a lot for your answer yesterday.
Right now it works fine. We have running a ci process (to capture data from
terminals in production). This process does a lot of update, inserts and
selects.
If I enable pdqpriority 5 then I still have very much user threads from this
user. This isn't that annoying but the strange thing is (we're working in
our test environment) that all to threads stay open for a very long time
even after we don't send data to the database.
I'm just afraid that this will result in problems in future. If we do a
phew new queries, there are still many threads open but we receive and
update our data well towards the database.
Do you have an explanation for this behaviour ? I see also that many off the
threads are waiting for locks(onstat -k).
Thanks in advance,
Bjorn
"Art S. Kagel" <kagel@bloomberg.net> wrote in message
news:pan.2004.07.15.08.04.27.629024.1355@bloomberg.net...
> On Wed, 14 Jul 2004 17:32:28 -0400, Bjorn De Waele wrote:
>
> The system catalog's stored procedure header table is sysprocedures, the
body
> of the proc source, the compiled byte code, and the query plan byte code
are
> all stored in sysprocbody with different values in the datakey column.
> However, you can just use:
> dbschema -d mydatabase -f [all|<procname>]> and similarly if you prefer using myschema:
> myschema -d mydatabase -f [[all|*]|[<procspec>|<procname>]
> (procspec uses MATCHES style wildcarding). FYI, there is no indication of
> what the PDQPRIORITY was at compile time either in the dbschema/myschema
> output or in the system catalog tables.
>
> Art S. Kagel
>
> > Hello Art,
> >
> > thank your for your answer. I'll check tomorrow if there are any stored
> > procedures.
> >
> > I can't use my winsql tool right now. Do you know in which table I can
see
> > my stored procs ?
> >
> > Thanks,
> >
> > Bjorn
> >
> >
> > "Art S. Kagel" <kagel@bloomberg.net> wrote in message
> > news:pan.2004.07.14.16.06.43.735485.1355@bloomberg.net...
> >> On Wed, 14 Jul 2004 12:07:01 -0400, Bjorn De Waele wrote:
> >>
> >> This mystery is usually caused by having compiled stored procedures
with a
> >> non-zero PDQPRIORITY in effect. This compiles in a run-time
PDQPRIORITY
> > for
> >> the procedure which you likely do not want. Recompile all stored
> > procedures
> >> (UPDATE STATISTICS FOR PROCEDURE procname;) in a session with
PDQPRIORITY
> > set
> >> to zero and this effect should disappear.
> >>
> >> This is why my dostats utility has separate flags to set PDQPRIORITY
for
> >> updating stats for tables and for compiling stored procedures.
> >>
> >> Art S. Kagel
> >>
> >> > Hello everybody,
> >> >
> >> >
> >> > we're using version 9.40.T7
> >> > Our max_pdqpriority is 100.
> >> >
> >> > Right now, I see much this behaviour with onstat -u. Informix creates
> > plenty
> >> > of user threads. If I set with the max_pdqpriority with onmode -D to
0.
> >> > Then I get a single user session which shows me sometimes 5 lock,
> > sometimes
> >> > 20 (while queries are running).
> >> > The weird thing is that I don't use set pdqpriority on client side.
> > We're
> >> > finetuning our server and playing around with the settings. I'm only
> > afraid
> >> > that those threads stay open (as I can see, I see lots of cond
> > waits(mc_1)
> >> > etc). And that they can affect performance. Is this a normal
behaviour
> > ? Do
> >> > you need additional stats ? I also see locks but I already received
all
> > my
> >> > data.
> >> >
> >> > Thanks,
> >> >
> >> > Bjorn
> >> >
> >> > onstat -u output.> >> >
> >> >
> >> > 3fa9e224 Y------ 84 informix NB070148 40a498b8 0 1 0
> > 0
> >> > 3fa9e838 Y--P--- 70 informix FSYS326 40f17380 0 1 33
> > 0
> >> > 3faa1eec Y------ 84 informix NB070148 410d0018 0 1 0
> > 0
> >> > 3faa373c Y------ 84 informix NB070148 40fa81a8 0 1 0
> > 0
> >> > 3faa4978 Y------ 84 informix NB070148 408dc480 0 1 0
> > 0
> >> > 3faa61c8 Y------ 84 informix NB070148 40892228 0 1 0
> > 0
> >> > 413f3018 Y------ 84 informix NB070148 41787c80 0 1 0
> > 0
> >> > 413f362c Y------ 84 informix NB070148 40a49650 0 1 0
> > 0
> >> > 413f4e7c Y------ 84 informix NB070148 40892228 0 1 0
> > 0
> >> > 413f5aa4 Y------ 84 informix NB070148 41c966e8 0 1 0
> > 0
> >> > 413f60b8 Y------ 84 informix NB070148 408dc480 0 1 0
> > 0
> >> > 413f66cc Y------ 84 informix NB070148 410d0018 0 1 0
> > 0
> >> > 413f6ce0 Y------ 84 informix NB070148 40a49650 0 1 0
> > 0
> >> > 413f7908 Y--P--- 83 informix NB070148 4163bc58 0 1 1514
> > 0
> >> > 413f7f1c Y------ 84 informix NB070148 410d0638 0 1 0
> > 0
> >> > 413f8530 Y------ 84 informix NB070148 3fd31ee0 0 1 0
> > 0
> >> > 413f8b44 Y--P--- 84 informix NB070148 41843920 0 1 3
> > 70
> >> > 413f9158 Y------ 84 informix NB070148 40efc4d8 0 1 0
> > 0
> >> > 413f976c Y------ 84 informix NB070148 40a496f8 0 1 0
> > 0
> >> > 413f9d80 Y------ 84 informix NB070148 40a496f8 0 1 0
> > 0
> >> > 413fafbc Y------ 84 informix NB070148 40a496f8 0 1 0
> > 0
> >> > 413fc1f8 Y------ 84 informix NB070148 3fd31f88 0 1 0
> > 0
> >> > 413fda48 Y------ 84 informix NB070148 40a496f8 0 1 0
> > 0
> >> > 413ffec0 Y------ 84 informix NB070148 419dc7e8 0 1 0
> > 0
> >> > 414010fc Y------ 84 informix NB070148 41787cd0 0 1 0
> > 0
> >> > 41401d24 Y------ 84 informix NB070148 40849f08 0 1 0
> > 0
> >> > 41402f60 Y------ 84 informix NB070148 408dc480 0 1 0
> > 0
> >> > 41403574 Y------ 84 informix NB070148 41136b58 0 1 0
> > 0
> >> > 414047b0 Y------ 84 informix NB070148 410d0638 0 1 0
> > 0
> >> > 414096b4 Y------ 84 informix NB070148 40a496f8 0 1 0
> > 0
> >> > 4140d37c Y------ 84 informix NB070148 40a496f8 0 1 0
> > 0
> >> > 4140dfa4 Y------ 84 informix NB070148 41136b58 0 1 0
> > 0