Root db space fills up
An IDS 11.7 site saw rootdbs repeatedly fill up and then shrink during a query against nested views, even though four temp dbspaces existed and were not filling. Suggestions included checking whether logs were in rootdbs, mixing logged and unlogged temp dbspaces, setting TEMPTAB_NOLOG, and trying IFX_FOLDVIEW; adding a logged temp dbspace made no difference, as the engine never used it. After opening a case with IBM, the cause was identified as APAR IC84725 - optimizer overestimation of view temp table size forcing rootdbs to be used for temp space.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
We have experienced an unusual problem after a software delivery. The root db space in one of our instances gets full and then goes back to its normal state. This made us believe that it gets filled up by temporary tables used by querys for sorting etc but there is a catch since our temp db spaces aren´t getting filled before the root db space reaches its limit. The server is set up with all the databases in there own db space and there are four temp db spaces specified and seems to be working as they should be. Has anyone experienced a similar problem where the root db space suddenly seems to be used for storing temporary data instead of the temp db spaces?
Hello Are the physical log and logical logs using the rootdbs ? Thanks Sent from my iPhone > On 08 Dec 2014, at 10:24, RICKARD ESPING <rickard.esping@migrationsverket.se> wrote: > > We have experienced an unusual problem after a software delivery. > > The root db space in one of our instances gets full and then goes back to its > normal state. > This made us believe that it gets filled up by temporary tables used by querys > for sorting etc but there is a catch since our temp db spaces aren´t getting > filled before the root db space reaches its limit. > > The server is set up with all the databases in there own db space and there > are four temp db spaces specified and seems to be working as they should be. > > Has anyone experienced a similar problem where the root db space suddenly > seems to be used for storing temporary data instead of the temp db spaces? > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > Stefan Sammut Manager - Solutions & Innovation [PTL Ltd] Nineteen Twenty Three, Valletta Road Marsa, MRS 3000, MT T +35621445566 +356 99405444 stefan.sammut@ptl.com.mt | www.ptl.com.mt<http://www.ptl.com.mt>; [Facebook]<https://www.facebook.com/PTLMalta/app_349313058487732>; [LinkedIn] <https://www.linkedin.com/company/ptl-ltd?trk=tyah&trkInfo=tarId%3A1401716999276 %2Ctas%3APTL%2Cidx%3A2-3-8> [Twitter] <https://twitter.com/PTL_Malta>; [Youtube] <https://www.youtube.com/channel/UCuXrJBO_54kd9HttG8S89iQ/feed?view_as =public> [Google Plus] <https://plus.google.com/+PTLMalta>;
The logical and physical logs reside in there own db space.
Rickard
Do you have a mixture of logged and unlogged temp spaces ??
Unlogged temp spaces are used by the engine for sort/merge operations and
where a temp table is created specifically 'WITH NO LOG'. However if a temp
table does not have this specified it can only be created in a logged temp
space, or rootdbs if non-exist. I suspect this is your issue and that
somewhere in the delivery a logged temp table is being created. Run onstat
-d to see if any temp spaces do not have the T flag against them (to
indicate logged). See my extract below:
4 0x41001 4 1 N B informix ltempdbs1
5 0x42001 5 1 N TB informix ttempdbs1
6 0x41001 6 1 N B informix ltempdbs2
7 0x42001 7 1 N TB informix ttempdbs2
8 0x41001 8 1 N B informix ltempdbs3
9 0x42001 9 1 N TB informix ttempdbs3
All these are in the TEMPSPACE parameter in the onconfig file.
Keith
On 8 December 2014 at 09:23, RICKARD ESPING <
rickard.esping@migrationsverket.se> wrote:
> We have experienced an unusual problem after a software delivery.
>
> The root db space in one of our instances gets full and then goes back to
> its
> normal state.
> This made us believe that it gets filled up by temporary tables used by
> querys
> for sorting etc but there is a catch since our temp db spaces aren´t
> getting
> filled before the root db space reaches its limit.
>
> The server is set up with all the databases in there own db space and there
> are four temp db spaces specified and seems to be working as they should
> be.
>
> Has anyone experienced a similar problem where the root db space suddenly
> seems to be used for storing temporary data instead of the temp db spaces?
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--089e011833ea4cc67c0509b17a6e
All the temp db spaces are logged. I also examined the queries and there are no temporary tables specifically created in the queries.
Might want to check client env variables however you can only specify filesystem as temp db space . Also You might have some functions in sysutils which might be filling up a system database like sysmaster ? Sent from my iPhone > On 08 Dec 2014, at 11:28, RICKARD ESPING <rickard.esping@migrationsverket.se> wrote: > > All the temp db spaces are logged. > I also examined the queries and there are no temporary tables specifically > created in the queries. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > Stefan Sammut Manager - Solutions & Innovation [PTL Ltd] Nineteen Twenty Three, Valletta Road Marsa, MRS 3000, MT T +35621445566 +356 99405444 stefan.sammut@ptl.com.mt | www.ptl.com.mt<http://www.ptl.com.mt>; [Facebook]<https://www.facebook.com/PTLMalta/app_349313058487732>; [LinkedIn] <https://www.linkedin.com/company/ptl-ltd?trk=tyah&trkInfo=tarId%3A1401716999276 %2Ctas%3APTL%2Cidx%3A2-3-8> [Twitter] <https://twitter.com/PTL_Malta>; [Youtube] <https://www.youtube.com/channel/UCuXrJBO_54kd9HttG8S89iQ/feed?view_as =public> [Google Plus] <https://plus.google.com/+PTLMalta>;
have examined what happens in the root dbs and there is a large temporary table created during query execution and as soon as the query either finishes or is interrupted the root db space goes back to its normal size. I found this thread where Art Kagel had an answer to a similar problem (we are using IDS 11.7) but I´m not sure how to interpret his answer, it seems odd to create a non logged db space to be used på logged temp tables. http://www.iiug.org/forums/ids/index.cgi/read/15183 The query where we are experiencing the problem selects from a view that selects from an underlying view that uses a group by clause. The entire query is then sorted.
Your temp dbspaces should be marked as 'T' temp and be unlogged. You should also have TEMPTAB_NOLOG set to '1' to force all unspecified temp tables to be unlogged and so be written to unlogged temp dbspaces. If all of the dbspaces that are listed in DBSPACETEMP are logged then unlogged temp tables will, I believe, be written to the root dbspace which is what you are seeing. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.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 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 Mon, Dec 8, 2014 at 5:27 AM, RICKARD ESPING < rickard.esping@migrationsverket.se> wrote: > All the temp db spaces are logged. > I also examined the queries and there are no temporary tables specifically > created in the queries. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e0122930a82c8390509b27c2e
Rickard
In that case you need to create a couple of unlogged (or true temp)
dbspaces by setting 'Y' in the Temp space (or -t in the onspaces command).
Art's post just re-iterates my original response, you need logged and
unlogged temp spaces.
What does your onstta -d show ??
Keith
On 8 December 2014 at 11:03, RICKARD ESPING <
rickard.esping@migrationsverket.se> wrote:
> have examined what happens in the root dbs and there is a large temporary
> table created during query execution and as soon as the query either
> finishes
> or is interrupted the root db space goes back to its normal size.
>
> I found this thread where Art Kagel had an answer to a similar problem (we
> are
> using IDS 11.7) but I´m not sure how to interpret his answer, it seems odd
> to
> create a non logged db space to be used på logged temp tables.
> http://www.iiug.org/forums/ids/index.cgi/read/15183
>
> The query where we are experiencing the problem selects from a view that
> selects from an underlying view that uses a group by clause.
> The entire query is then sorted.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001a113f7e1c26b4b20509b2a8fe
as metioned above, maybe you need set TEMPTAB_NOLOG in you onconfig file
It seems that I misinterpreted your answer in the thread I referred to. We had four unlogged temp dbspaces as shown below and the TEMPTAB_NOLOG parameter was set to 1 address number flags fchunk nchunks pgsize flags owner name 4828e9c8 4 0x42001 4 1 2048 N TB informix tempdbs1 4828eb70 5 0x42001 5 1 2048 N TB informix tempdbs2 4828ed18 6 0x42001 6 1 2048 N TB informix tempdbs3 48290028 7 0x42001 7 1 2048 N TB informix tempdbs4 We then added a db space as seen below that is logged according to your answer in the refereed thread and edited the parameter DBSPACETEMP to include the new db space called tempdbs5. address number flags fchunk nchunks pgsize flags owner name 48291028 16 0x60001 34 1 2048 N B informix tempdbs5 But I still get that big temp table in the rootdbs when the query against the view is executed.
Rickard How large is the instansiated view going to be? How large is tempdbs5? Is it being filled by the view and then having to overflow into the rootdbs ? Keith On 11 December 2014 at 12:23, RICKARD ESPING < rickard.esping@migrationsverket.se> wrote: > It seems that I misinterpreted your answer in the thread I referred to. > > We had four unlogged temp dbspaces as shown below and the TEMPTAB_NOLOG > parameter was set to 1 > > address number flags fchunk nchunks pgsize flags owner name > 4828e9c8 4 0x42001 4 1 2048 N TB informix tempdbs1 > 4828eb70 5 0x42001 5 1 2048 N TB informix tempdbs2 > 4828ed18 6 0x42001 6 1 2048 N TB informix tempdbs3 > 48290028 7 0x42001 7 1 2048 N TB informix tempdbs4 > > We then added a db space as seen below that is logged according to your > answer > in the refereed thread and edited the parameter DBSPACETEMP to include the > new > db space called tempdbs5. > > address number flags fchunk nchunks pgsize flags owner name > 48291028 16 0x60001 34 1 2048 N B informix tempdbs5 > > But I still get that big temp table in the rootdbs when the query against > the > view is executed. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a113f7e1c4228f80509f0066d
What version are you on? We have customers on older systems that need over 500GB root dbspace to run some their views, not matter how you set temp space the view uses rootdbs. In their version, and according to IBM, this is the correct behaviour Cheers Paul Paul Watson Oninit www.oninit.com +1 913 387 7529 > On Dec 11, 2014, at 06:37, Keith Simmons <smiley73@gmail.com> wrote: > > Rickard > How large is the instansiated view going to be? How large is tempdbs5? Is > it being filled by the > view and then having to overflow into the rootdbs ? > > Keith > > On 11 December 2014 at 12:23, RICKARD ESPING < > rickard.esping@migrationsverket.se> wrote: > >> It seems that I misinterpreted your answer in the thread I referred to. >> >> We had four unlogged temp dbspaces as shown below and the TEMPTAB_NOLOG >> parameter was set to 1 >> >> address number flags fchunk nchunks pgsize flags owner name >> 4828e9c8 4 0x42001 4 1 2048 N TB informix tempdbs1 >> 4828eb70 5 0x42001 5 1 2048 N TB informix tempdbs2 >> 4828ed18 6 0x42001 6 1 2048 N TB informix tempdbs3 >> 48290028 7 0x42001 7 1 2048 N TB informix tempdbs4 >> >> We then added a db space as seen below that is logged according to your >> answer >> in the refereed thread and edited the parameter DBSPACETEMP to include the >> new >> db space called tempdbs5. >> >> address number flags fchunk nchunks pgsize flags owner name >> 48291028 16 0x60001 34 1 2048 N B informix tempdbs5 >> >> But I still get that big temp table in the rootdbs when the query against >> the >> view is executed. > ******************************************************************************* >> Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a113f7e1c4228f80509f0066d > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum.
The instantiated view is approximately 160 MB and the dbspace added is 2GB. It seems that the database doesn't even try to use the new dbspace. I've looked at what is in the different dbspaces during execution of the select query against the view and the temp table appears in the rootdbs every time but never in the new dbspace created.
Open a case with IBM. Regards, David. > On 11 December 2014 at 13:02 RICKARD ESPING > <rickard.esping@migrationsverket.se> wrote: > > > The instantiated view is approximately 160 MB and the dbspace added is 2GB. > It seems that the database doesn't even try to use the new dbspace. > I've looked at what is in the different dbspaces during execution of the > select query against the view and the temp table appears in the rootdbs every > time but never in the new dbspace created. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
Not sure if it will affect you and whether the behaviour of using rootdbs as
temporarily space is affected... But maybe you can try it out.
See below. We had used IFX_FOLDVIEW when having views with union and it did
improve performance.
"IFX_FOLDVIEW configuration parameter
Use the IFX_FOLDVIEW configuration parameter to enable or disable view
folding. For certain situations where a view is involved in a query, view
folding can significantly improve the performance of the query. In these
cases, views are folded into a parent query instead of the query results being
put into a temporary table.
onconfig.std value
IFX_FOLDVIEW 1values
0 or Off = Disables view folding.
1 or On = Default. Enables view folding.
takes effect
After you edit your onconfig file and restart the database server.
When you reset the value dynamically in your onconfig file by running the
onmode -wf command.When you reset the value in memory by running the onmode -wm command.
Usage
The following types of queries can take advantage of view folding:
Views that contain a UNION ALL clause and the parent query has a regular join,
an IBM(r) Informix(r) join, an ANSI join, or an ORDER BY clause
A temporary table is created and view folding is not performed for the
following types of queries that perform a UNION ALL operation involving a view:
The view has one of the following clauses: AGGREGATE, GROUP BY, ORDER BY,
UNION, DISTINCT, or OUTER JOIN (either Informix or ANSI type).
The parent query has a UNION or UNION ALL clause."
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
david@smooth1.co.uk
Sent: 12 December 2014 09:26
To: ids@iiug.org
Subject: Re: Root db space fills up [34342]
Open a case with IBM.
Regards,
David.
> On 11 December 2014 at 13:02 RICKARD ESPING
> <rickard.esping@migrationsverket.se> wrote:
>
>
> The instantiated view is approximately 160 MB and the dbspace added is 2GB.
> It seems that the database doesn't even try to use the new dbspace.
> I've looked at what is in the different dbspaces during execution of
> the select query against the view and the temp table appears in the
> rootdbs
every
> time but never in the new dbspace created.
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Stefan Sammut
Manager - Solutions & Innovation
[PTL Ltd]
Nineteen Twenty Three, Valletta Road
Marsa, MRS 3000, MT
T +35621445566 +356 99405444
stefan.sammut@ptl.com.mt | www.ptl.com.mt<http://www.ptl.com.mt>;
[Facebook]<https://www.facebook.com/PTLMalta/app_349313058487732>; [LinkedIn]
<https://www.linkedin.com/company/ptl-ltd?trk=tyah&trkInfo=tarId%3A1401716999276
%2Ctas%3APTL%2Cidx%3A2-3-8> [Twitter] <https://twitter.com/PTL_Malta>;
[Youtube] <https://www.youtube.com/channel/UCuXrJBO_54kd9HttG8S89iQ/feed?view_as
=public> [Google Plus] <https://plus.google.com/+PTLMalta>;
Case openend with IBM and we now have the answer, APAR IC84725 is causing this problem. IC84725, OPTIMIZER OVERESTIMATION FOR VIEW TEMP TABLES CAUSES ROOTDBS TO BE USED ALWAYS FOR TEMP SPACE. Regards Ulf