IDS Forum

Re: tempdbs used by a ghost

Posted By: Alexandre Marini
Date: Wednesday, 14 September 2011, at 12:15 p.m.

In Response To: Re: tempdbs used by a ghost (Art Kagel)

Yes, that´s a nice try.
I had several issues here, coming from java clients doing XA
transactions that never freeds table locks.
It´s a little difficult to find, but nothing that a good "technical
connection configuration manual" doesn´t solve in a glance... kkkkkk

Em 14/09/2011 12:06, Art Kagel escreveu:
> One thing to try first: If rrosini ran the query from a PC based tool, try
> rebooting her PC first. That may release the network connection. PC apps
> are terrible about properly shutting down their network connections and
> sometimes that leaves Informix blocked waiting for a response from an app
> that is long gone.
>
> 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 Wed, Sep 14, 2011 at 10:21 AM, Alberto Romeu Pessonio Filho<
> arfilho@orizonbrasil.com.br> wrote:
>
>> Hi Art!
>>
>> I've talked to rrosini and she (Yes, a "dumb" girl...) told me that she got
>> the result yesterday. I think the query fetched data and finished. Also, I
>> saw
>> the query, and this kind of query when is triggered on the instance usually
>> brings rows in 3 minutes, at max.
>>
>> I think that I really need to bounce the engine.
>>
>> Thanks in advance,
>> Alberto.
>>
>> -----Mensagem original-----
>> De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Em nome de Art
>> Kagel
>> Enviada em: quarta-feira, 14 de setembro de 2011 11:14
>> Para: ids@iiug.org
>> Assunto: Re: tempdbs used by a ghost [24908]
>>
>> If the rrosini session is already gone then there isn't anything you can do
>> to get rid of the orphaned temp space short of bouncing the instance.
>> Usually it is still lying around because the sort is still going on (could
>> be a pathological query like a cartesian product sorting trillions of rows)
>> and the space will free once the sort completes and the engine tries to
>> tell
>> the session that data is finally available to fetch. Either that or it will
>> run out of temp dbspace and that will cause the query to rollback. Either
>> way at that point it will realize that the session is gone and release the
>> resources the session is holding. However, if the query that is sorting it
>> VERY large that may not happen for quite some time.
>>
>> 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 Wed, Sep 14, 2011 at 9:56 AM, Alberto Romeu Pessonio Filho<
>> arfilho@orizonbrasil.com.br> wrote:
>>
>>> Hi guys!!!
>>>
>>> Sorry to forget this:
>>>
>>> IDS 11.50 FC7W1GE
>>> Linux Redhat x86_64bits
>>>
>>> Art: My system wasn't in archive mode when the issue happened.
>>>
>>> I suspect that we are running almost out of memory on the Growth Edition
>>> License, and another session tried to get memory from the virtual
>> segment.
>>> Look at my Online.log:
>>>
>>> 09/13/11 14:43:08 WARNING: 1 mem pools not freed. (destroy_session)
>>> user:rrosini, prog:, ses id:44429442
>>> 09/13/11 14:43:08 Assert Failed: pool not freed. pool name:44429442_SO,
>>> address:0x0x2edf86040
>>> 09/13/11 14:43:08 IBM Informix Dynamic Server Version 11.50.FC7W1GE
>>> 09/13/11 14:43:08 Who: Session(44429442, rrosini@æx, -1, 0x2e8366a50)
>>>
>>> Thread(288624763, sqlexec, 3138a02e8, 1)
>>>
>>> File: session.c Line: 2062
>>> 09/13/11 14:43:08 Results: Internally corrected
>>> 09/13/11 14:43:08 stack trace for pid 17741 written to
>>> /informix/logs_informix/ifxorizon1/dump_af/af.1463962c
>>> 09/13/11 14:43:08 See Also:
>>> /informix/logs_informix/ifxorizon1/dump_af/af.1463962c
>>> 09/13/11 14:43:45 Releasing server from system block
>>> 09/13/11 14:43:45 Releasing server from system block
>>> 09/13/11 14:43:45 Releasing server from system block
>>> 09/13/11 14:48:44 VP Notify mechanism incomplete after 5 minutes. This
>> can
>>> be
>>> due to slow network file access. Will try 12 more times
>>> 09/13/11 14:49:43 pool not freed. pool name:44429442_SO,
>>> address:0x0x2edf86040
>>> 09/13/11 14:49:43 VP Notify mechanism completed
>>>
>>> This session (rrosini) was running a query from Aqua Data Studio Tool
>> near
>>> at
>>> noon, and 14:43 this error happened. this incident caused five minutes of
>>> system instability, but I had no action.
>>>
>>> The problem is: I've killed the session, but the engine didn't freed the
>>> temporary DBSpace, and I can't attach the space used on the temp DBSpace
>> to
>>> the user rrosini
>>>
>>> The query that Alexandre sent me helped a lot. Thank you very much!
>>> I executed the query using a filter for 'rrosini'. The output brought me
>>> 12.922 lines.
>>>
>>> SORTTEMP rrosini th_tmprun_0x2fb99f578 1 576 0
>>> SORTTEMP rrosini th_tmprun_0x2fb99f8e8 1 576 0
>>> SORTTEMP rrosini th_tmprun_0x2fcc7bde8 1 576 0
>>> SORTTEMP rrosini th_tmprun_0x31bda5078 1 576 0
>>> SORTTEMP rrosini th_tmprun_0x31bda5348 1 576 0
>>> SORTTEMP rrosini th_tmprun_0x31bda5618 1 576 0
>>> SORTTEMP rrosini th_tmprun_0x31bda5fc8 1 576 0
>>> SORTTEMP rrosini th_tmprun_0x2ee8cc578 1 576 0
>>> SORTTEMP rrosini th_tmprun_0x2ee8cc7a8 1 576 0
>>> SORTTEMP rrosini th_tmprun_0x386e8f9d8 1 576 0
>>> SORTTEMP rrosini th_tmprun_0x2efa6c078 1 576 0
>>> SORTTEMP rrosini th_tmprun_0x302ee77f8 1 576 0
>>> SORTTEMP rrosini th_tmprun_0x3ba928938 1 576 0
>>> SORTTEMP rrosini th_tmprun_0x3ba928ed8 1 576 0
>>> SORTTEMP rrosini th_tmprun_0x3030f0398 1 576 0
>>> SORTTEMP rrosini th_tmprun_0x30860d438 1 576 0
>>> SORTTEMP rrosini th_tmprun_0x30860db68 1 576 0
>>> SORTTEMP rrosini th_tmprun_0x30860dfc8 1 576 0
>>> SORTTEMP rrosini th_tmprun_0x2f815a1b8 1 576 0
>>>
>>> How can I freed this space to the engine without bounce it?
>>>
>>> Thanks,
>>> Alberto
>>>
>>> -----Mensagem original-----
>>> De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Em nome de Art
>>> Kagel
>>> Enviada em: quarta-feira, 14 de setembro de 2011 10:38
>>> Para: ids@iiug.org
>>> Assunto: Re: tempdbs used by a ghost [24905]
>>>
>>> It might be a running archive process. The archive copies physical log
>>> records for each dbspace into a temp table that is fragmented across the
>>> temp dbspaces. What version of Informix are you using and on what
>>> platform? That may shed some light.
>>>
>>> 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 Wed, Sep 14, 2011 at 9:22 AM, ALBERTO ROMEU PESSONIO FILHO<
>>> arfilho@orizonbrasil.com.br> wrote:
>>>
>>>> Hi folks!
>>>>
>>>> I Couldn't identify a session who is using the temporary DBSpace of my
>>>> company's system. The SNMP Monitoring Tool is pointing that the free
>>> space
>>>> of
>>>> 2 of 3 spaces has been used, and I can't find who is using.
>>>>
>>>> PS: I can't bounce the engine this week to solve this problem...
>>>>
>>>> Can anybody help me?
>>>> Thanks in advance,
>>>> Alberto.
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>
>>
> *******************************************************************************
>>>> Forum Note: Use "Reply" to post a response in the discussion forum.
>>>>
>>>>
>>> --20cf303ea5ce22409704ace6d721
>>>
>>>
>>>
>>>
>>
>>
> *******************************************************************************
>>> Forum Note: Use "Reply" to post a response in the discussion forum.
>>>
>>>
>>>
>>>
>>
>>
> *******************************************************************************
>>> Forum Note: Use "Reply" to post a response in the discussion forum.
>>>
>>>
>> --20cf303ea5ce60427604ace75f69
>>
>>
>>
>>
> *******************************************************************************
>> Forum Note: Use "Reply" to post a response in the discussion forum.
>>
>>
>>
>>
> *******************************************************************************
>> Forum Note: Use "Reply" to post a response in the discussion forum.
>>
>>
> --20cf303bfaae9056a304ace8f09f
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--

Alexandre Marini

Tecnologia da Informação - DBA

SEFAZ-MS / SGI-UGSR / Sistemas IBM-Informix

<Cert-Info-Mgmt_color.jpg>

IBM Certified System Administrator - Informix Dynamic Server V10 / V11 /
V11.70

IBM Information Management Informix Technical Professional v3

Messages In This Thread

• tempdbs used by a ghost
  ALBERTO ROMEU PESSONIO FILHO -- Wednesday, 14 September 2011, at 9:22 a.m.
  • Re: tempdbs used by a ghost
    Alexandre Marini -- Wednesday, 14 September 2011, at 9:34 a.m.
  • Re: tempdbs used by a ghost
    Art Kagel -- Wednesday, 14 September 2011, at 9:37 a.m.
    • RES: tempdbs used by a ghost
      Alberto Romeu Pessonio Filho -- Wednesday, 14 September 2011, at 9:56 a.m.
      • Re: tempdbs used by a ghost
        Art Kagel -- Wednesday, 14 September 2011, at 10:14 a.m.
        • RES: tempdbs used by a ghost
          Alberto Romeu Pessonio Filho -- Wednesday, 14 September 2011, at 10:21 a.m.
          • Re: tempdbs used by a ghost
            Art Kagel -- Wednesday, 14 September 2011, at 12:06 p.m.
            • Re: tempdbs used by a ghost
              Alexandre Marini -- Wednesday, 14 September 2011, at 12:15 p.m.
            • RES: tempdbs used by a ghost
              Alberto Romeu Pessonio Filho -- Wednesday, 14 September 2011, at 1:58 p.m.
              • Re: tempdbs used by a ghost
                Art Kagel -- Wednesday, 14 September 2011, at 2:17 p.m.
                • RES: tempdbs used by a ghost
                  Alberto Romeu Pessonio Filho -- Wednesday, 14 September 2011, at 2:36 p.m.
                  • Re: tempdbs used by a ghost
                    Art Kagel -- Wednesday, 14 September 2011, at 2:53 p.m.
                    • RES: tempdbs used by a ghost
                      Alberto Romeu Pessonio Filho -- Wednesday, 14 September 2011, at 3:15 p.m.
      • Re: RES: tempdbs used by a ghost
        DAVE GRIFFEN -- Wednesday, 14 September 2011, at 10:46 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.