Does IDS11 needs or uses more temp space
After upgrading from IDS 7.31 to 11.50 on AIX, temp dbspace use ballooned (huge HASHTEMP files, out-of-temp-space and -131 errors) for the same workloads. Suggestions included running UPDATE STATISTICS to avoid bad hash joins, but Art Kagel identified the likely cause: DBUPSPACE (default 15MB in 7.x) is a no-op from 10.00 onward, replaced by the onconfig parameter DS_NONPDQ_QUERYMEM, which defaults to only 128KB, pushing most sorts to disk. The poster agreed to raise it to 15-50MB (possibly also setting DS_TOTAL_MEMORY). Sort-to-disk activity can be checked via sysmaster sysprofile/syssesprof (numsorts/disksorts). No follow-up confirming results.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Since an upgrade to IDS11 from IDS7.x, the instance seems to consume mo=
re
temp space for the same tasks. Resulting in not enough temp space while=
building indexes on temp table and/or a -131 error code during some ot=
her
activities. There are about 17GB of temp space of about half a gig
each...in 7.31.UD8 everything worked fine with no issues with only 8GB =
of
tmp space. Now we are on IDS11.50.FC7X0 and AIX6.1
During some ops, I can see huge hashtemp table build, the below is noth=
ing
comparing to what I saw while monitoring the instance.
Number of Size in
DBS:Table Name Extents Mbytes
HASHTEMP:th_probe_ffffffffffffffff 1488 1514.73
Is there any onconfig params that one can enable/disable to override th=
is
effect or to limit temp space consumption/sorting or things that happen=
in
temp.
OPTCOMPIND is currently set to 2
Any insights would be greatly appreciated!
Regards,
jp=
How is the indexes and update statistics? If you don't have indexes the
optimizer will choose hash join.
Celso Cabral Coimbra
Administrador de Banco de Dados
ClearTech Ltda
"Trust at the heart of Communications"
Tel. (11) 3576-4509
-----Mensagem original-----
De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Em nome de
jpierrot@chubb.com
Enviada em: terça-feira, 25 de outubro de 2011 14:15
Para: ids@iiug.org
Assunto: Does IDS11 needs or uses more temp space [25272]
Since an upgrade to IDS11 from IDS7.x, the instance seems to consume mo=
re
temp space for the same tasks. Resulting in not enough temp space while=
building indexes on temp table and/or a -131 error code during some ot=
her
activities. There are about 17GB of temp space of about half a gig
each...in 7.31.UD8 everything worked fine with no issues with only 8GB =
of
tmp space. Now we are on IDS11.50.FC7X0 and AIX6.1
During some ops, I can see huge hashtemp table build, the below is noth=
ing
comparing to what I saw while monitoring the instance.
Number of Size in
DBS:Table Name Extents Mbytes
HASHTEMP:th_probe_ffffffffffffffff 1488 1514.73
Is there any onconfig params that one can enable/disable to override th=
is
effect or to limit temp space consumption/sorting or things that happen=
in
temp.
OPTCOMPIND is currently set to 2
Any insights would be greatly appreciated!
Regards,
jp=
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Run update statistics and I'm not joking.
Do you do it regularly? depending on your data model and usage, too frequent
and big hash* means too many/wrong hash joins.
Regards.
On Tue, Oct 25, 2011 at 5:14 PM, jpierrot@chubb.com <jpierrot@chubb.com>wrote:
> Since an upgrade to IDS11 from IDS7.x, the instance seems to consume mo=
> re
> temp space for the same tasks. Resulting in not enough temp space while=
>
> building indexes on temp table and/or a -131 error code during some ot=
> her
> activities. There are about 17GB of temp space of about half a gig
> each...in 7.31.UD8 everything worked fine with no issues with only 8GB =
> of
> tmp space. Now we are on IDS11.50.FC7X0 and AIX6.1
> During some ops, I can see huge hashtemp table build, the below is noth=
> ing
> comparing to what I saw while monitoring the instance.
>
> Number of Size in
> DBS:Table Name Extents Mbytes
>
> HASHTEMP:th_probe_ffffffffffffffff 1488 1514.73
>
> Is there any onconfig params that one can enable/disable to override th=
> is
> effect or to limit temp space consumption/sorting or things that happen=
> in
> temp.
>
> OPTCOMPIND is currently set to 2>
> Any insights would be greatly appreciated!
>
> Regards,
>
> jp=
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--001636426ab533dd5904b021fa39
I run update statistics once a week... but Auto Update Statistics is on=
by
default.
jp
=
From: "Fernando Nunes" <domusonline@gmail.com> =
=
To: ids@iiug.org =
=
Date: 10/25/2011 12:25 PM =
=
Subject: Re: Does IDS11 needs or uses more temp space [25274] =
=
Sent by: ids-bounces@iiug.org =
=
Run update statistics and I'm not joking.
Do you do it regularly? depending on your data model and usage, too
frequent
and big hash* means too many/wrong hash joins.
Regards.
On Tue, Oct 25, 2011 at 5:14 PM, jpierrot@chubb.com
<jpierrot@chubb.com>wrote:
> Since an upgrade to IDS11 from IDS7.x, the instance seems to consume =
mo=3D
> re
> temp space for the same tasks. Resulting in not enough temp space whi=
le=3D
>
> building indexes on temp table and/or a -131 error code during some o=
t=3D
> her
> activities. There are about 17GB of temp space of about half a gig
> each...in 7.31.UD8 everything worked fine with no issues with only 8G=
B =3D
> of
> tmp space. Now we are on IDS11.50.FC7X0 and AIX6.1
> During some ops, I can see huge hashtemp table build, the below is no=
th=3D
> ing
> comparing to what I saw while monitoring the instance.
>
> Number of Size in
> DBS:Table Name Extents Mbytes
>
> HASHTEMP:th_probe_ffffffffffffffff 1488 1514.73
>
> Is there any onconfig params that one can enable/disable to override =
th=3D
> is
> effect or to limit temp space consumption/sorting or things that happ=
en=3D
> in
> temp.
>
> OPTCOMPIND is currently set to 2>
> Any insights would be greatly appreciated!
>
> Regards,
>
> jp=3D
>
>
>
>
***********************************************************************=
********
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--001636426ab533dd5904b021fa39
***********************************************************************=
********
Forum Note: Use "Reply" to post a response in the discussion forum.
=
Most likely the significant portion of the difference is sort-work files.
Under 7.xx memory space used for non-PDQPRIORITY sorting was controlled by
the environment variable DBUPSPACE which defaulted to 15MB and had a range
of 15-50MB. Under 10.00 and later this variable is noop and non-PDQ sorting
memory space is controlled by the new ONCONFIG parameter DS_NONPDQ_QUERYMEM
which defaults to 128K if not set (and this is also the setting in the
onconfig.std sample file) and has a range from 128K to 25% of
DS_TOTAL_MEMORY. You need to increase DS_NONPDQ_QUERYMEM to whatever you
used to set DBUPSPACE to and at least 15MB to return to the performance
profile you had under 7.31. If you do not set DS_TOTAL_MEMORY currently you
may also have to set that to permit the setting of DS_NONPDQ_QUERYMEM you
want to set.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.
On Tue, Oct 25, 2011 at 12:14 PM, jpierrot@chubb.com <jpierrot@chubb.com>wrote:
> Since an upgrade to IDS11 from IDS7.x, the instance seems to consume mo=
> re
> temp space for the same tasks. Resulting in not enough temp space while=
>
> building indexes on temp table and/or a -131 error code during some ot=
> her
> activities. There are about 17GB of temp space of about half a gig
> each...in 7.31.UD8 everything worked fine with no issues with only 8GB =
> of
> tmp space. Now we are on IDS11.50.FC7X0 and AIX6.1
> During some ops, I can see huge hashtemp table build, the below is noth=
> ing
> comparing to what I saw while monitoring the instance.
>
> Number of Size in
> DBS:Table Name Extents Mbytes
>
> HASHTEMP:th_probe_ffffffffffffffff 1488 1514.73
>
> Is there any onconfig params that one can enable/disable to override th=
> is
> effect or to limit temp space consumption/sorting or things that happen=
> in
> temp.
>
> OPTCOMPIND is currently set to 2>
> Any insights would be greatly appreciated!
>
> Regards,
>
> jp=
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--90e6ba6e868e22448904b022a36c
In 7.x I did not set DBUPSPACE and DS_TOTAL_MEMORY so everything defau=
lted
and in 11.x I have not set up DS_TOTAL_MEMORY and DS_NONPDQ_QUERYMEM i=
s
set to default 128k. Doesn't that coincide to the profile that I had u=
nder
7.31. Right!
jp
=
From: "Art Kagel" <art.kagel@gmail.com> =
=
To: ids@iiug.org =
=
Date: 10/25/2011 01:12 PM =
=
Subject: Re: Does IDS11 needs or uses more temp space [25276] =
=
Sent by: ids-bounces@iiug.org =
=
Most likely the significant portion of the difference is sort-work file=
s.
Under 7.xx memory space used for non-PDQPRIORITY sorting was controlled=
by
the environment variable DBUPSPACE which defaulted to 15MB and had a ra=
nge
of 15-50MB. Under 10.00 and later this variable is noop and non-PDQ sor=
ting
memory space is controlled by the new ONCONFIG parameter DS_NONPDQ_QUER=
YMEM
which defaults to 128K if not set (and this is also the setting in the
onconfig.std sample file) and has a range from 128K to 25% of
DS_TOTAL_MEMORY. You need to increase DS_NONPDQ_QUERYMEM to whatever yo=
u
used to set DBUPSPACE to and at least 15MB to return to the performance=
profile you had under 7.31. If you do not set DS_TOTAL_MEMORY currently=
you
may also have to set that to permit the setting of DS_NONPDQ_QUERYMEM y=
ou
want to set.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinion=
s
and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any ot=
her
organization with which I am associated either explicitly, implicitly, =
or
by
inference. Neither do those opinions reflect those of other individuals=
affiliated with any entity with which I am affiliated nor those of the
entities themselves.
On Tue, Oct 25, 2011 at 12:14 PM, jpierrot@chubb.com
<jpierrot@chubb.com>wrote:
> Since an upgrade to IDS11 from IDS7.x, the instance seems to consume =
mo=3D
> re
> temp space for the same tasks. Resulting in not enough temp space whi=
le=3D
>
> building indexes on temp table and/or a -131 error code during some o=
t=3D
> her
> activities. There are about 17GB of temp space of about half a gig
> each...in 7.31.UD8 everything worked fine with no issues with only 8G=
B =3D
> of
> tmp space. Now we are on IDS11.50.FC7X0 and AIX6.1
> During some ops, I can see huge hashtemp table build, the below is no=
th=3D
> ing
> comparing to what I saw while monitoring the instance.
>
> Number of Size in
> DBS:Table Name Extents Mbytes
>
> HASHTEMP:th_probe_ffffffffffffffff 1488 1514.73
>
> Is there any onconfig params that one can enable/disable to override =
th=3D
> is
> effect or to limit temp space consumption/sorting or things that happ=
en=3D
> in
> temp.
>
> OPTCOMPIND is currently set to 2>
> Any insights would be greatly appreciated!
>
> Regards,
>
> jp=3D
>
>
>
>
***********************************************************************=
********
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--90e6ba6e868e22448904b022a36c
***********************************************************************=
********
Forum Note: Use "Reply" to post a response in the discussion forum.
=
NO! The default in 7.31 was 15MB of memory for in-memory sorting. In 11.xx
the default if you don't set anything is 128KB of memory for in-memory
sorting that means that where say 5% of your sorting was going to disk, now
80% of your sorts are now going to disk.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.
On Tue, Oct 25, 2011 at 2:15 PM, <jpierrot@chubb.com> wrote:
> In 7.x I did not set DBUPSPACE and DS_TOTAL_MEMORY so everything
> defaulted and in 11.x I have not set up DS_TOTAL_MEMORY and
DS_NONPDQ_QUERYMEM
> is set to default 128k. Doesn't that coincide to the profile that I had
> under 7.31. Right!
>
> jp
>
> [image: Inactive hide details for "Art Kagel" ---10/25/2011 01:12:38
> PM---Most likely the significant portion of the difference is sort]"Art
> Kagel" ---10/25/2011 01:12:38 PM---Most likely the significant portion of
> the difference is sort-work files. Under 7.xx memory space u
>
>
> From:
> "Art Kagel" <art.kagel@gmail.com>
> To:
> ids@iiug.org
> Date:
> 10/25/2011 01:12 PM
> Subject:
> Re: Does IDS11 needs or uses more temp space [25276]
> Sent by:
> ids-bounces@iiug.org
> ------------------------------
>
>
>
> Most likely the significant portion of the difference is sort-work files.
> Under 7.xx memory space used for non-PDQPRIORITY sorting was controlled by
> the environment variable DBUPSPACE which defaulted to 15MB and had a range
> of 15-50MB. Under 10.00 and later this variable is noop and non-PDQ sorting
>
> memory space is controlled by the new ONCONFIG parameter DS_NONPDQ_QUERYMEM
>
> which defaults to 128K if not set (and this is also the setting in the
> onconfig.std sample file) and has a range from 128K to 25% of
> DS_TOTAL_MEMORY. You need to increase DS_NONPDQ_QUERYMEM to whatever you
> used to set DBUPSPACE to and at least 15MB to return to the performance
> profile you had under 7.31. If you do not set DS_TOTAL_MEMORY currently you
>
> may also have to set that to permit the setting of DS_NONPDQ_QUERYMEM you
> want to set.
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> Blog: http://informix-myview.blogspot.com/
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and
> do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
> organization with which I am associated either explicitly, implicitly, or
> by
> inference. Neither do those opinions reflect those of other individuals
> affiliated with any entity with which I am affiliated nor those of the
> entities themselves.
>
> On Tue, Oct 25, 2011 at 12:14 PM, jpierrot@chubb.com
> <jpierrot@chubb.com>wrote:
>
> > Since an upgrade to IDS11 from IDS7.x, the instance seems to consume mo=
> > re
> > temp space for the same tasks. Resulting in not enough temp space while=
> >
> > building indexes on temp table and/or a -131 error code during some ot=
> > her
> > activities. There are about 17GB of temp space of about half a gig
> > each...in 7.31.UD8 everything worked fine with no issues with only 8GB =
> > of
> > tmp space. Now we are on IDS11.50.FC7X0 and AIX6.1
> > During some ops, I can see huge hashtemp table build, the below is noth=
> > ing
> > comparing to what I saw while monitoring the instance.
> >
> > Number of Size in
> > DBS:Table Name Extents Mbytes
> >
> > HASHTEMP:th_probe_ffffffffffffffff 1488 1514.73
> >
> > Is there any onconfig params that one can enable/disable to override th=
> > is
> > effect or to limit temp space consumption/sorting or things that happen=
> > in
> > temp.
> >
> > OPTCOMPIND is currently set to 2> >
> > Any insights would be greatly appreciated!
> >
> > Regards,
> >
> > jp=
> >
> >
> >
> >
>
*******************************************************************************
>
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --90e6ba6e868e22448904b022a36c
>
>
>
*******************************************************************************
>
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
--bcaec529a0a1d973c604b023917c
Thanks! I will adjust DS_NONPDQ_QUERYMEM to a value between 15 and 50M=
B.
jp
=
From: "Art Kagel" <art.kagel@gmail.com> =
=
To: ids@iiug.org =
=
Date: 10/25/2011 02:19 PM =
=
Subject: Re: Does IDS11 needs or uses more temp space [25278] =
=
Sent by: ids-bounces@iiug.org =
=
NO! The default in 7.31 was 15MB of memory for in-memory sorting. In 11=
.xx
the default if you don't set anything is 128KB of memory for in-memory
sorting that means that where say 5% of your sorting was going to disk,=
now
80% of your sorts are now going to disk.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinion=
s
and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any ot=
her
organization with which I am associated either explicitly, implicitly, =
or
by
inference. Neither do those opinions reflect those of other individuals=
affiliated with any entity with which I am affiliated nor those of the
entities themselves.
On Tue, Oct 25, 2011 at 2:15 PM, <jpierrot@chubb.com> wrote:
> In 7.x I did not set DBUPSPACE and DS_TOTAL_MEMORY so everything
> defaulted and in 11.x I have not set up DS_TOTAL_MEMORY and
DS_NONPDQ_QUERYMEM
> is set to default 128k. Doesn't that coincide to the profile that I h=
ad
> under 7.31. Right!
>
> jp
>
> [image: Inactive hide details for "Art Kagel" ---10/25/2011 01:12:38
> PM---Most likely the significant portion of the difference is sort]"A=
rt
> Kagel" ---10/25/2011 01:12:38 PM---Most likely the significant portio=
n of
> the difference is sort-work files. Under 7.xx memory space u
>
>
> From:
> "Art Kagel" <art.kagel@gmail.com>
> To:
> ids@iiug.org
> Date:
> 10/25/2011 01:12 PM
> Subject:
> Re: Does IDS11 needs or uses more temp space [25276]
> Sent by:
> ids-bounces@iiug.org
> ------------------------------
>
>
>
> Most likely the significant portion of the difference is sort-work fi=
les.
> Under 7.xx memory space used for non-PDQPRIORITY sorting was controll=
ed
by
> the environment variable DBUPSPACE which defaulted to 15MB and had a
range
> of 15-50MB. Under 10.00 and later this variable is noop and non-PDQ
sorting
>
> memory space is controlled by the new ONCONFIG parameter
DS_NONPDQ_QUERYMEM
>
> which defaults to 128K if not set (and this is also the setting in th=
e
> onconfig.std sample file) and has a range from 128K to 25% of
> DS_TOTAL_MEMORY. You need to increase DS_NONPDQ_QUERYMEM to whatever =
you
> used to set DBUPSPACE to and at least 15MB to return to the performan=
ce
> profile you had under 7.31. If you do not set DS_TOTAL_MEMORY current=
ly
you
>
> may also have to set that to permit the setting of DS_NONPDQ_QUERYMEM=
you
> want to set.
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> Blog: http://informix-myview.blogspot.com/
>
> Disclaimer: Please keep in mind that my own opinions are my own opini=
ons
> and
> do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other
> organization with which I am associated either explicitly, implicitly=
, or
> by
> inference. Neither do those opinions reflect those of other individua=
ls
> affiliated with any entity with which I am affiliated nor those of th=
e
> entities themselves.
>
> On Tue, Oct 25, 2011 at 12:14 PM, jpierrot@chubb.com
> <jpierrot@chubb.com>wrote:
>
> > Since an upgrade to IDS11 from IDS7.x, the instance seems to consum=
e
mo=3D
> > re
> > temp space for the same tasks. Resulting in not enough temp space
while=3D
> >
> > building indexes on temp table and/or a -131 error code during some=
ot=3D
> > her
> > activities. There are about 17GB of temp space of about half a gig
> > each...in 7.31.UD8 everything worked fine with no issues with only =
8GB
=3D
> > of
> > tmp space. Now we are on IDS11.50.FC7X0 and AIX6.1
> > During some ops, I can see huge hashtemp table build, the below is
noth=3D
> > ing
> > comparing to what I saw while monitoring the instance.
> >
> > Number of Size in
> > DBS:Table Name Extents Mbytes
> >
> > HASHTEMP:th_probe_ffffffffffffffff 1488 1514.73
> >
> > Is there any onconfig params that one can enable/disable to overrid=
e
th=3D
> > is
> > effect or to limit temp space consumption/sorting or things that
happen=3D
> > in
> > temp.
> >
> > OPTCOMPIND is currently set to 2> >
> > Any insights would be greatly appreciated!
> >
> > Regards,
> >
> > jp=3D
> >
> >
> >
> >
>
***********************************************************************=
********
>
> > Forum Note: Use "Reply" to post a response in the discussion forum.=
> >
> >
>
> --90e6ba6e868e22448904b022a36c
>
>
>
***********************************************************************=
********
>
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
--bcaec529a0a1d973c604b023917c
***********************************************************************=
********
Forum Note: Use "Reply" to post a response in the discussion forum.
=
Art,
Is there an onstat or series of onstats that will show how much of the
sorting is going go disk ...
Peter Logan
Senior Database Administrator
Phone: 616/878-8309
From: "jpierrot@chubb.com" <jpierrot@chubb.com>
To: ids@iiug.org
Date: 10/25/2011 02:29 PM
Subject: Re: Does IDS11 needs or uses more temp space [25280]
Sent by: ids-bounces@iiug.org
Thanks! I will adjust DS_NONPDQ_QUERYMEM to a value between 15 and 50M=
B.
jp
=
From: "Art Kagel" <art.kagel@gmail.com> =
=
To: ids@iiug.org =
=
Date: 10/25/2011 02:19 PM =
=
Subject: Re: Does IDS11 needs or uses more temp space [25278] =
=
Sent by: ids-bounces@iiug.org =
=
NO! The default in 7.31 was 15MB of memory for in-memory sorting. In 11=
..xx
the default if you don't set anything is 128KB of memory for in-memory
sorting that means that where say 5% of your sorting was going to disk,=
now
80% of your sorts are now going to disk.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinion=
s
and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any ot=
her
organization with which I am associated either explicitly, implicitly, =
or
by
inference. Neither do those opinions reflect those of other individuals=
affiliated with any entity with which I am affiliated nor those of the
entities themselves.
On Tue, Oct 25, 2011 at 2:15 PM, <jpierrot@chubb.com> wrote:
> In 7.x I did not set DBUPSPACE and DS_TOTAL_MEMORY so everything
> defaulted and in 11.x I have not set up DS_TOTAL_MEMORY and
DS_NONPDQ_QUERYMEM
> is set to default 128k. Doesn't that coincide to the profile that I h=
ad
> under 7.31. Right!
>
> jp
>
> [image: Inactive hide details for "Art Kagel" ---10/25/2011 01:12:38
> PM---Most likely the significant portion of the difference is sort]"A=
rt
> Kagel" ---10/25/2011 01:12:38 PM---Most likely the significant portio=
n of
> the difference is sort-work files. Under 7.xx memory space u
>
>
> From:
> "Art Kagel" <art.kagel@gmail.com>
> To:
> ids@iiug.org
> Date:
> 10/25/2011 01:12 PM
> Subject:
> Re: Does IDS11 needs or uses more temp space [25276]
> Sent by:
> ids-bounces@iiug.org
> ------------------------------
>
>
>
> Most likely the significant portion of the difference is sort-work fi=
les.
> Under 7.xx memory space used for non-PDQPRIORITY sorting was controll=
ed
by
> the environment variable DBUPSPACE which defaulted to 15MB and had a
range
> of 15-50MB. Under 10.00 and later this variable is noop and non-PDQ
sorting
>
> memory space is controlled by the new ONCONFIG parameter
DS_NONPDQ_QUERYMEM
>
> which defaults to 128K if not set (and this is also the setting in th=
e
> onconfig.std sample file) and has a range from 128K to 25% of
> DS_TOTAL_MEMORY. You need to increase DS_NONPDQ_QUERYMEM to whatever =
you
> used to set DBUPSPACE to and at least 15MB to return to the performan=
ce
> profile you had under 7.31. If you do not set DS_TOTAL_MEMORY current=
ly
you
>
> may also have to set that to permit the setting of DS_NONPDQ_QUERYMEM=
you
> want to set.
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> Blog: http://informix-myview.blogspot.com/
>
> Disclaimer: Please keep in mind that my own opinions are my own opini=
ons
> and
> do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other
> organization with which I am associated either explicitly, implicitly=
, or
> by
> inference. Neither do those opinions reflect those of other individua=
ls
> affiliated with any entity with which I am affiliated nor those of th=
e
> entities themselves.
>
> On Tue, Oct 25, 2011 at 12:14 PM, jpierrot@chubb.com
> <jpierrot@chubb.com>wrote:
>
> > Since an upgrade to IDS11 from IDS7.x, the instance seems to consum=
e
mo=3D
> > re
> > temp space for the same tasks. Resulting in not enough temp space
while=3D
> >
> > building indexes on temp table and/or a -131 error code during some=
ot=3D
> > her
> > activities. There are about 17GB of temp space of about half a gig
> > each...in 7.31.UD8 everything worked fine with no issues with only =
8GB
=3D
> > of
> > tmp space. Now we are on IDS11.50.FC7X0 and AIX6.1
> > During some ops, I can see huge hashtemp table build, the below is
noth=3D
> > ing
> > comparing to what I saw while monitoring the instance.
> >
> > Number of Size in
> > DBS:Table Name Extents Mbytes
> >
> > HASHTEMP:th_probe_ffffffffffffffff 1488 1514.73
> >
> > Is there any onconfig params that one can enable/disable to overrid=
e
th=3D
> > is
> > effect or to limit temp space consumption/sorting or things that
happen=3D
> > in
> > temp.
> >
> > OPTCOMPIND is currently set to 2> >
> > Any insights would be greatly appreciated!
> >
> > Regards,
> >
> > jp=3D
> >
> >
> >
> >
>
***********************************************************************=
********
>
> > Forum Note: Use "Reply" to post a response in the discussion forum.=
> >
> >
>
> --90e6ba6e868e22448904b022a36c
>
>
>
***********************************************************************=
********
>
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
--bcaec529a0a1d973c604b023917c
***********************************************************************=
********
Forum Note: Use "Reply" to post a response in the discussion forum.
=
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
sysmaster:sysprofile
sysmaster:syssesprof
These tables have numsorts and disksorts statistics for the server as a
whole and for each session. Obviously memory sorts are the difference.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.
On Tue, Oct 25, 2011 at 2:31 PM, Peter_Logan@spartanstores.com <
Peter_Logan@spartanstores.com> wrote:
> Art,
>
> Is there an onstat or series of onstats that will show how much of the
> sorting is going go disk ...
>
> Peter Logan
> Senior Database Administrator
> Phone: 616/878-8309
>
> From: "jpierrot@chubb.com" <jpierrot@chubb.com>
> To: ids@iiug.org
> Date: 10/25/2011 02:29 PM
> Subject: Re: Does IDS11 needs or uses more temp space [25280]
> Sent by: ids-bounces@iiug.org
>
> Thanks! I will adjust DS_NONPDQ_QUERYMEM to a value between 15 and 50M=
> B.
>
> jp
>
> =
>
> From: "Art Kagel" <art.kagel@gmail.com> =
>
> =
>
> To: ids@iiug.org =
>
> =
>
> Date: 10/25/2011 02:19 PM =
>
> =
>
> Subject: Re: Does IDS11 needs or uses more temp space [25278] =
>
> =
>
> Sent by: ids-bounces@iiug.org =
>
> =
>
> NO! The default in 7.31 was 15MB of memory for in-memory sorting. In 11=
> ...xx
> the default if you don't set anything is 128KB of memory for in-memory
> sorting that means that where say 5% of your sorting was going to disk,=
> now
>
> 80% of your sorts are now going to disk.
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> Blog: http://informix-myview.blogspot.com/
>
> Disclaimer: Please keep in mind that my own opinions are my own opinion=
> s
> and
> do not reflect on my employer, Advanced DataTools, the IIUG, nor any ot=
> her
> organization with which I am associated either explicitly, implicitly, =
> or
> by
> inference. Neither do those opinions reflect those of other individuals=
>
> affiliated with any entity with which I am affiliated nor those of the
> entities themselves.
>
> On Tue, Oct 25, 2011 at 2:15 PM, <jpierrot@chubb.com> wrote:
>
> > In 7.x I did not set DBUPSPACE and DS_TOTAL_MEMORY so everything
> > defaulted and in 11.x I have not set up DS_TOTAL_MEMORY and
> DS_NONPDQ_QUERYMEM
> > is set to default 128k. Doesn't that coincide to the profile that I h=
> ad
> > under 7.31. Right!
> >
> > jp
> >
> > [image: Inactive hide details for "Art Kagel" ---10/25/2011 01:12:38
> > PM---Most likely the significant portion of the difference is sort]"A=
> rt
> > Kagel" ---10/25/2011 01:12:38 PM---Most likely the significant portio=
> n of
>
> > the difference is sort-work files. Under 7.xx memory space u
> >
> >
> > From:
> > "Art Kagel" <art.kagel@gmail.com>
> > To:
> > ids@iiug.org
> > Date:
> > 10/25/2011 01:12 PM
> > Subject:
> > Re: Does IDS11 needs or uses more temp space [25276]
> > Sent by:
> > ids-bounces@iiug.org
> > ------------------------------
> >
> >
> >
> > Most likely the significant portion of the difference is sort-work fi=
> les.
>
> > Under 7.xx memory space used for non-PDQPRIORITY sorting was controll=
> ed
> by
> > the environment variable DBUPSPACE which defaulted to 15MB and had a
> range
> > of 15-50MB. Under 10.00 and later this variable is noop and non-PDQ
> sorting
> >
> > memory space is controlled by the new ONCONFIG parameter
> DS_NONPDQ_QUERYMEM
> >
> > which defaults to 128K if not set (and this is also the setting in th=
> e
> > onconfig.std sample file) and has a range from 128K to 25% of
> > DS_TOTAL_MEMORY. You need to increase DS_NONPDQ_QUERYMEM to whatever =
> you
> > used to set DBUPSPACE to and at least 15MB to return to the performan=
> ce
> > profile you had under 7.31. If you do not set DS_TOTAL_MEMORY current=
> ly
> you
> >
> > may also have to set that to permit the setting of DS_NONPDQ_QUERYMEM=
> you
>
> > want to set.
> >
> > Art
> >
> > Art S. Kagel
> > Advanced DataTools (www.advancedatatools.com)
> > Blog: http://informix-myview.blogspot.com/
> >
> > Disclaimer: Please keep in mind that my own opinions are my own opini=
> ons
> > and
> > do not reflect on my employer, Advanced DataTools, the IIUG, nor any
> other
> > organization with which I am associated either explicitly, implicitly=
> , or
>
> > by
> > inference. Neither do those opinions reflect those of other individua=
> ls
> > affiliated with any entity with which I am affiliated nor those of th=
> e
> > entities themselves.
> >
> > On Tue, Oct 25, 2011 at 12:14 PM, jpierrot@chubb.com
> > <jpierrot@chubb.com>wrote:
> >
> > > Since an upgrade to IDS11 from IDS7.x, the instance seems to consum=
> e
> mo=3D
> > > re
> > > temp space for the same tasks. Resulting in not enough temp space
> while=3D
> > >
> > > building indexes on temp table and/or a -131 error code during some=
> ot=3D
>
> > > her
> > > activities. There are about 17GB of temp space of about half a gig
> > > each...in 7.31.UD8 everything worked fine with no issues with only =
> 8GB
> =3D
> > > of
> > > tmp space. Now we are on IDS11.50.FC7X0 and AIX6.1
> > > During some ops, I can see huge hashtemp table build, the below is
> noth=3D
> > > ing
> > > comparing to what I saw while monitoring the instance.
> > >
> > > Number of Size in
> > > DBS:Table Name Extents Mbytes
> > >
> > > HASHTEMP:th_probe_ffffffffffffffff 1488 1514.73
> > >
> > > Is there any onconfig params that one can enable/disable to overrid=
> e
> th=3D
> > > is
> > > effect or to limit temp space consumption/sorting or things that
> happen=3D
> > > in
> > > temp.
> > >
> > > OPTCOMPIND is currently set to 2> > >
> > > Any insights would be greatly appreciated!
> > >
> > > Regards,
> > >
> > > jp=3D
> > >
> > >
> > >
> > >
> >
> ***********************************************************************=
> ********
>
> >
> > > Forum Note: Use "Reply" to post a response in the discussion forum.=
>
> > >
> > >
> >
> > --90e6ba6e868e22448904b022a36c
> >
> >
> >
> ***********************************************************************=
> ********
>
> >
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
> >
> >
>
> --bcaec529a0a1d973c604b023917c
>
> ***********************************************************************=
> ********
>
> Forum Note: Use @@D
Hmm, maybe the "numsorts" should be "totalsorts".
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.
On Tue, Oct 25, 2011 at 2:43 PM, Art Kagel <art.kagel@gmail.com> wrote:
> sysmaster:sysprofile
>
> sysmaster:syssesprof
>
> These tables have numsorts and disksorts statistics for the server as a
> whole and for each session. Obviously memory sorts are the difference.
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> Blog: http://informix-myview.blogspot.com/
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and
> do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
> organization with which I am associated either explicitly, implicitly, or
> by
> inference. Neither do those opinions reflect those of other individuals
> affiliated with any entity with which I am affiliated nor those of the
> entities themselves.
>
> On Tue, Oct 25, 2011 at 2:31 PM, Peter_Logan@spartanstores.com <
> Peter_Logan@spartanstores.com> wrote:
>
> > Art,
> >
> > Is there an onstat or series of onstats that will show how much of the
> > sorting is going go disk ...
> >
> > Peter Logan
> > Senior Database Administrator
> > Phone: 616/878-8309
> >
> > From: "jpierrot@chubb.com" <jpierrot@chubb.com>
> > To: ids@iiug.org
> > Date: 10/25/2011 02:29 PM
> > Subject: Re: Does IDS11 needs or uses more temp space [25280]
> > Sent by: ids-bounces@iiug.org
> >
> > Thanks! I will adjust DS_NONPDQ_QUERYMEM to a value between 15 and 50M=
> > B.
> >
> > jp
> >
> > =
> >
> > From: "Art Kagel" <art.kagel@gmail.com> =
> >
> > =
> >
> > To: ids@iiug.org =
> >
> > =
> >
> > Date: 10/25/2011 02:19 PM =
> >
> > =
> >
> > Subject: Re: Does IDS11 needs or uses more temp space [25278] =
> >
> > =
> >
> > Sent by: ids-bounces@iiug.org =
> >
> > =
> >
> > NO! The default in 7.31 was 15MB of memory for in-memory sorting. In 11=
> > ...xx
> > the default if you don't set anything is 128KB of memory for in-memory
> > sorting that means that where say 5% of your sorting was going to disk,=
> > now
> >
> > 80% of your sorts are now going to disk.
> >
> > Art
> >
> > Art S. Kagel
> > Advanced DataTools (www.advancedatatools.com)
> > Blog: http://informix-myview.blogspot.com/
> >
> > Disclaimer: Please keep in mind that my own opinions are my own opinion=
> > s
> > and
> > do not reflect on my employer, Advanced DataTools, the IIUG, nor any ot=
> > her
> > organization with which I am associated either explicitly, implicitly, =
> > or
> > by
> > inference. Neither do those opinions reflect those of other individuals=
> >
> > affiliated with any entity with which I am affiliated nor those of the
> > entities themselves.
> >
> > On Tue, Oct 25, 2011 at 2:15 PM, <jpierrot@chubb.com> wrote:
> >
> > > In 7.x I did not set DBUPSPACE and DS_TOTAL_MEMORY so everything
> > > defaulted and in 11.x I have not set up DS_TOTAL_MEMORY and
> > DS_NONPDQ_QUERYMEM
> > > is set to default 128k. Doesn't that coincide to the profile that I h=
> > ad
> > > under 7.31. Right!
> > >
> > > jp
> > >
> > > [image: Inactive hide details for "Art Kagel" ---10/25/2011 01:12:38
> > > PM---Most likely the significant portion of the difference is sort]"A=
> > rt
> > > Kagel" ---10/25/2011 01:12:38 PM---Most likely the significant portio=
> > n of
> >
> > > the difference is sort-work files. Under 7.xx memory space u
> > >
> > >
> > > From:
> > > "Art Kagel" <art.kagel@gmail.com>
> > > To:
> > > ids@iiug.org
> > > Date:
> > > 10/25/2011 01:12 PM
> > > Subject:
> > > Re: Does IDS11 needs or uses more temp space [25276]
> > > Sent by:
> > > ids-bounces@iiug.org
> > > ------------------------------
> > >
> > >
> > >
> > > Most likely the significant portion of the difference is sort-work fi=
> > les.
> >
> > > Under 7.xx memory space used for non-PDQPRIORITY sorting was controll=
> > ed
> > by
> > > the environment variable DBUPSPACE which defaulted to 15MB and had a
> > range
> > > of 15-50MB. Under 10.00 and later this variable is noop and non-PDQ
> > sorting
> > >
> > > memory space is controlled by the new ONCONFIG parameter
> > DS_NONPDQ_QUERYMEM
> > >
> > > which defaults to 128K if not set (and this is also the setting in th=
> > e
> > > onconfig.std sample file) and has a range from 128K to 25% of
> > > DS_TOTAL_MEMORY. You need to increase DS_NONPDQ_QUERYMEM to whatever =
> > you
> > > used to set DBUPSPACE to and at least 15MB to return to the performan=
> > ce
> > > profile you had under 7.31. If you do not set DS_TOTAL_MEMORY current=
> > ly
> > you
> > >
> > > may also have to set that to permit the setting of DS_NONPDQ_QUERYMEM=
> > you
> >
> > > want to set.
> > >
> > > Art
> > >
> > > Art S. Kagel
> > > Advanced DataTools (www.advancedatatools.com)
> > > Blog: http://informix-myview.blogspot.com/
> > >
> > > Disclaimer: Please keep in mind that my own opinions are my own opini=
> > ons
> > > and
> > > do not reflect on my employer, Advanced DataTools, the IIUG, nor any
> > other
> > > organization with which I am associated either explicitly, implicitly=
> > , or
> >
> > > by
> > > inference. Neither do those opinions reflect those of other individua=
> > ls
> > > affiliated with any entity with which I am affiliated nor those of th=
> > e
> > > entities themselves.
> > >
> > > On Tue, Oct 25, 2011 at 12:14 PM, jpierrot@chubb.com
> > > <jpierrot@chubb.com>wrote:
> > >
> > > > Since an upgrade to IDS11 from IDS7.x, the instance seems to consum=
> > e
> > mo=3D
> > > > re
> > > > temp space for the same tasks. Resulting in not enough temp space
> > while=3D
> > > >
> > > > building indexes on temp table and/or a -131 error code during some=
> > ot=3D
> >
> > > > her
> > > > activities. There are about 17GB of temp space of about half a gig
> > > > each...in 7.31.UD8 everything worked fine with no issues with only =
> > 8GB
> > =3D
> > > > of
> > > > tmp space. Now we are on IDS11.50.FC7X0 and AIX6.1
> > > > During some ops, I can see huge hashtemp table build, the below is
> > noth=3D
> > > > ing
> > > > comparing to what I saw while monitoring the instance.
> > > >
> > > > Number of Size in
> > > > DBS:Table Name Extents Mbytes
> > > >
> > > > HASHTEMP:th_probe_ffffffffffffffff 1488 1514.73
> > > >
> > > > Is there any onconfig params t