Temp space filling up

[Pastore]

We are having an intermittent problem with the DBSPACETEMP space filling up.
Currently we have 4 chunks allocated to temporary space, each 2 gigabytes.
Every so often all four chunks fill up, we have to be quick to even see it.
The space fills up and the offending program and any other that wants or
needs temp space errors off. When the culprit errors off the temp space is
freed and everything goes back to normal with no trace, at least I know of
no way to trace it. We have set up a way of monitoring the space and will
send an alert to me when the condition gets bad ( over 80 % full ).

My question is, "What do I look for when I see the temp space filling
up?" Our system has between 1,000 to 3,000 concurrent users so looking at
the onstat -u output may not be realistic, not without knowing exactly what
to look for. So is there an onstat command that would tell me how much temp
space is being used by each currently running processes? If not, does
anyone have a suggestion as to what I should look for.

Thanks
Ray Pastore

[Andrew Hamm]

Pastore wrote in message ...

A process generating that much space would most likely have a very high
nwrites value in the onstat -u parameter.

Take their session id (for example, my quick test now shows a session id of
748 for a test session I've kicked off in dbaccess) and issue this command:

onstat -g sql 748

to confirm the SQL statement that session last executed. You'll either see
an explicit insertion into a temp table or (assuming you know your data)
you'll see an SQL which is clearly very greedy and requires some massive
internal temp table that the engine must use.

The following SQL's are all applied against sysmaster database

You can find info about temp tables (explicit or internal) with this:

select dbsname, tabname,
dbinfo('dbspace', ti_partnum) dbspace,
hex(ti_flags) hexflags,
ti_rowsize,
ti_nptotal
from systabinfo, outer systabnames
where systabinfo.ti_partnum = systabnames.partnum
and (bitval(ti_flags, '0x20') = 1 or
bitval(ti_flags, '0x40') = 1 or
bitval(ti_flags, '0x80') = 1)

don't try to combine the bitmasks in the where-clause. The bitval function
can only cope with one bit position at a time.

I'm trying to find a way to relate this temp table back to a specific
session id, but can't find a magic table. Two pages have fallen out of my
manual so maybe the info is in there ;-(

Fields in sysuserthreads table such as

us_sid (session id)
us_pagwrites
us_totsorts
us_dsksorts

might be useful evidence if the big temp table is a sort file.

syssqexplain table contains the cost estimate for currently executing
statements. Look for records with very large values for sqx_estcost or
sqx_estrows which should be roughly on the money if your table STATISTICS
are regularly UPDATEd.
--
Ehhhh, What's up, Doc?
R.I.P. Chuck Jones
*** Please stand for the 21 Anvil Salute ***

[Jack Parker]

onstat -d shows you how your disk space is doing. My guess would be a large
hash join, a large group, or a large order by. Find the culprit, determine
what he needs and then allocate him more memory (if its a hash join - see
pdqpriority), if it's an order by then consider an index on his sort
requirements to obviate the need for temp. If it's a group - give him more
temp.

cheers
j.

----- Original Message -----
From: "Pastore" <vpas...@erols.com>
To: <inform...@iiug.org>
Sent: Monday, March 18, 2002 5:52 PM
Subject: Temp space filling up

> We are having an intermittent problem with the DBSPACETEMP space filling
up.
> Currently we have 4 chunks allocated to temporary space, each 2 gigabytes.
> Every so often all four chunks fill up, we have to be quick to even see
it.
> The space fills up and the offending program and any other that wants or
> needs temp space errors off. When the culprit errors off the temp space
is
> freed and everything goes back to normal with no trace, at least I know of
> no way to trace it. We have set up a way of monitoring the space and will
> send an alert to me when the condition gets bad ( over 80 % full ).
>
> My question is, "What do I look for when I see the temp space filling
> up?" Our system has between 1,000 to 3,000 concurrent users so looking at
> the onstat -u output may not be realistic, not without knowing exactly
what
> to look for. So is there an onstat command that would tell me how much
temp
> space is being used by each currently running processes? If not, does
> anyone have a suggestion as to what I should look for.
>

> Thanks
> Ray Pastore
>
>
>
>

[Half Baked]

>
> You can find info about temp tables (explicit or internal) with this:
>

Hey, that's some good information to know. I can't imagine you picked this up
from an Informix manual - where'd you find that stuff out?

[Andrew Hamm]

Half Baked wrote in message
<89293D5935BB487D.F704CFEF...@lp.airnews.net>...

Ummm, from an Informix Manual?

Online Administrators Guide describes The Sysmaster Database in chapter 34,
although systabinfo is absent in my printed manual. Similar info is in the
"Upgrading to IFX ODS Training Manual" although that's the book with the 2
missing pages.

Probably similar guff is available in the performance tuning guides although
I'm not looking.

If you don't have printed copies of the manuals, they are all (except the
training books) available on the "Informix Answers Online" CD wot comes with
virtually all product deliveries. Last CD version I've seen is 3.2.

[Dirk Moolman]

I don't know if your system will be fast enough to run this query, but the
following query will show the space used per session in your temp dbspaces.
You can change it according to your needs (dbspace names, etc.)
This is also for a system with a 2k page size.

database sysmaster;

select dbinfo( "DBSPACE" , pe_partnum ) dbspace,
owner,
sum(pe_size)*2096/1024/1024 totsize
from sysptnext, outer systabnames
where dbinfo( "DBSPACE" , pe_partnum ) matches "tempdbs*"
and pe_partnum = partnum

group by 1,2
order by 3 desc

[Pastore Ray]

This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.

------_=_NextPart_001_01C1CF5F.7449F050
Content-Type: text/plain;
charset="iso-8859-1"

Thanks for all the help. I now have an arsenal of sql to help me fix the
problem when it occurs again. I'll keep you informed of the outcome.

Thanks again,
Ray Pastore

------_=_NextPart_001_01C1CF5F.7449F050
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Diso-8859-1">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
5.5.2653.12">
<TITLE>RE: Temp space filling up</TITLE>
</HEAD>
<BODY>

<P><FONT SIZE=3D2>Thanks for all the help.&nbsp; I now have an arsenal =
of sql to help me fix the problem when it occurs again.&nbsp; I'll keep =
you informed of the outcome.</FONT></P>

<P><FONT SIZE=3D2>Thanks again,</FONT>
<BR><FONT SIZE=3D2>Ray Pastore</FONT>
</P>

</BODY>
</HTML>
------_=_NextPart_001_01C1CF5F.7449F050--

[Andrew Hamm]

Pastore Ray wrote in message ...

>
>Thanks for all the help. I now have an arsenal of sql to help me fix the
>problem when it occurs again. I'll keep you informed of the outcome.
>

That would be great. We can all note your results down for experience.
Also, call back if you *don't* find the answer...

[Andy Kent]

Agree, except that I'd do everything possible to track down the
offending query and see if it can be made more efficient (probably by
better indexing) before capitulating to its inefficiency and giving it
more resources.

Andy

"Jack Parker" <jpa...@artentech.com> wrote in message news:<a763cd$b2v$1...@news.xmission.com>...

[Peter J Diaz de Leon]

I have found some strange behavior in 9.21 when dynamic hash joins are
created.
Or when your select has outer joins with very large tables involved.

For example:

select i.*, c.*
from invoice i, charge c
where i.invoice = c.invoice
into temp x1 with no log;

In my database a hash join is created but the select eventually fails
due to running out of temp space (I have 2 GB of temp dbspace.)

My solution was to change the "into temp" clause of the select
statement as follows:

create temp table x1
(
.
.
.
Column definitions
.
.
.
)with no log fragment by round robin in
tempdbs1,tempdbs2,tempdbs3,tempdbs4,tempdbs5,tempdbs6,tempdbs7;

insert into x1
select i.*, c.*
from invoice i, charge c
where i.invoice = c.invoice;

My problem went away. Note: all of my 7 temp dbspaces are equal in
size.
Therefore, with the first select statement Informix should have round
robin'd the data across the 7 temp dbspaces. It did not. I watched
as some of the dbspaces filled up while others were still more that
have empty.

So in the second select I explicitly created the temp tabled and
fragmented
and every thing worked fine.

FYI

-Peter

"Andrew Hamm" <ah...@sanderson.net.au> wrote in message news:<a78ldc$ikpns$1...@ID-79573.news.dfncis.de>...

[The Joker]

I had believed that a table whether temp or not, wouldn’t span dbspaces
unless you deliberately fragment across spaces.

If I am wrong it *will* be pointed out! :-(

TJ

P.S. Casting Sean Connery or Roger Moore (the younger versions!) would be
ideal; cross posting again! Although better than cross-dressing.

<clip..>

>Therefore, with the first select statement Informix should have round
>robin'd the data across the 7 temp dbspaces. It did not. I watched
>as some of the dbspaces filled up while others were still more that
>have empty.

<clip..>

_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp.