Mega Search
23.2 Million


Sign Up

Make a donation  
Level 0 onbar suddenly slow...  
News Group: comp.databases.informix

IDS9.31HC5, HP-UX11i (PA-RISC) on an HP system container running under Itanium

We're getting our level 0 archives slowing down by a factor 4 or so once the container has been running for a few days. We do an L0 every night which usually takes about 2 hours or so but it tends to go slow after a couple of days, taking 5 hours and maybe more.
What we're noticed is that the initial phase to collect the system info from sysmaster usually takes a few minutes but will start taking over an hour. This is the SQL it's running:
Informix Dynamic Server Version 9.30.HC5     -- On-Line -- Up 4 days 03:17:27 --
 382864 Kbytes                                                                  
                                                                                
Sess  SQL            Current            Iso Lock       SQL  ISAM F.E.           
Id    Stmt type      Database           Lvl Mode       ERR  ERR  Vers           
12791 SELECT         sysmaster          CR  Wait 300   0    0    9.03           
                                                                                
Current SQL statement :                                                         
   select ( sum ( chksize - nfree ) ) from sysdbspaces , syschunks where        
    sysdbspaces . dbsnum = syschunks . dbsnum and name = ?                      
                                                                                
Last parsed SQL statement :                                                     
   select ( sum ( chksize - nfree ) ) from sysdbspaces , syschunks where        
    sysdbspaces . dbsnum = syschunks . dbsnum and name = ?                     

We're up to 3.8 million reads on that session in the current L0. 
I've looked at whether it's arc_very_old_pages and none of the usual suspect indicators are there in onstat -g ath or onstat -g stk. We haven't got CCFLAGS=0x400000, should we?

We find that if we restart the container, it goes back to normal timings for a couple of days and then does it again. Any clues chaps?

Vote for best question.
Score: 0  # Vote:  0
Date Posted: 2-Oct-2014, at 9:11 AM EST
From: Malc P
 
Re: Level 0 onbar suddenly slow...  
News Group: comp.databases.informix
On Thursday, October 2, 2014 5:26:36 PM UTC-6, Malc P wrote:
> Mark, thanks.
> 
> A bit more info - we have some crons that run every hour or so checking how much space is free in the chunks, blobspaces and tempdbs so we can issue DBA alerts and these also slow down drastically - they are all variations of the form:
> 
> 
> 
> dbaccess sysmaster < /dev/null 2>&1                    
> 
> output to "${RESFILES}/dbspacefree.tmp" without headings     
> 
> select name[1,30], sum(nfree) from sysdbspaces d, syschunks c
> 
> where d.dbsnum = c.dbsnum                                    
> 
> and is_blobspace = 0                                         
> 
> and is_sbspace = 0                                           
> 
> group by 1 
> 
> EOF                    
> 
> 
> 
> I did another instance restart this evening and it definitely cleared the problem once again.
> 
> It would seem sysmaster is misbehaving but for the life of me I can't see why. As I recall, UPDATE STATISTICS is not useful or necessary on sysmaster as it's basically just views into shared memory?

Agreed on the idea of UPD STATS against sysmaster. Yes - sysmaster is predominantly views into shared memory structures. There are (or at least used to be) some "real tables" (arc this and that), but not many that I recall. 

Another possibility .... I have a current client that has over 230,000 partitions in a single engine. Many of my scripts take a very long time to run (sanity check scripts, etc.) simply due to this issue. But it's not "every so often" - it's every time and should be expected. Scanning sysptnprof takes a very long time due to the number of partitions. Haven't made a good connection on this for your situation but it came to mind. 

Thanks -
Mark Scranton
The Mark Scranton Group
"All Informix ... all the time. 
www.markscranton.com

Vote for best answer.
Score: 0  # Vote:  0
Date Posted: 6-Oct-2014, at 8:38 AM EST
From: Mark Scranton
 
Re: Level 0 onbar suddenly slow...  
News Group: comp.databases.informix
Mark, thanks.
A bit more info - we have some crons that run every hour or so checking how much space is free in the chunks, blobspaces and tempdbs so we can issue DBA alerts and these also slow down drastically - they are all variations of the form:

dbaccess sysmaster < /dev/null 2>&1                    
output to "${RESFILES}/dbspacefree.tmp" without headings     
select name[1,30], sum(nfree) from sysdbspaces d, syschunks c
where d.dbsnum = c.dbsnum                                    
and is_blobspace = 0                                         
and is_sbspace = 0                                           
group by 1 
EOF                    

I did another instance restart this evening and it definitely cleared the problem once again.
It would seem sysmaster is misbehaving but for the life of me I can't see why. As I recall, UPDATE STATISTICS is not useful or necessary on sysmaster as it's basically just views into shared memory?                              


Vote for best answer.
Score: 0  # Vote:  0
Date Posted: 2-Oct-2014, at 4:26 PM EST
From: Malc P
 
Re: Level 0 onbar suddenly slow...  
News Group: comp.databases.informix
On Thursday, October 2, 2014 10:11:09 AM UTC-6, Malc P wrote:
> IDS9.31HC5, HP-UX11i (PA-RISC) on an HP system container running under Itanium
> 
> 
> 
> We're getting our level 0 archives slowing down by a factor 4 or so once the container has been running for a few days. We do an L0 every night which usually takes about 2 hours or so but it tends to go slow after a couple of days, taking 5 hours and maybe more.
> 
> What we're noticed is that the initial phase to collect the system info from sysmaster usually takes a few minutes but will start taking over an hour. This is the SQL it's running:
> 
> Informix Dynamic Server Version 9.30.HC5     -- On-Line -- Up 4 days 03:17:27 --
> 
>  382864 Kbytes                                                                  
> 
>                                                                                 
> 
> Sess  SQL            Current            Iso Lock       SQL  ISAM F.E.           
> 
> Id    Stmt type      Database           Lvl Mode       ERR  ERR  Vers           
> 
> 12791 SELECT         sysmaster          CR  Wait 300   0    0    9.03           
> 
>                                                                                 
> 
> Current SQL statement :                                                         
> 
>    select ( sum ( chksize - nfree ) ) from sysdbspaces , syschunks where        
> 
>     sysdbspaces . dbsnum = syschunks . dbsnum and name = ?                      
> 
>                                                                                 
> 
> Last parsed SQL statement :                                                     
> 
>    select ( sum ( chksize - nfree ) ) from sysdbspaces , syschunks where        
> 
>     sysdbspaces . dbsnum = syschunks . dbsnum and name = ?                     
> 
> 
> 
> We're up to 3.8 million reads on that session in the current L0. 
> 
> I've looked at whether it's arc_very_old_pages and none of the usual suspect indicators are there in onstat -g ath or onstat -g stk. We haven't got CCFLAGS=0x400000, should we?
> 
> 
> 
> We find that if we restart the container, it goes back to normal timings for a couple of days and then does it again. Any clues chaps?

Malcom - 

At a recent client, when archives became slow(er), it was a TSM stalling issue not an onbar/engine issue. Archives during month-end processing are incredibly slow but it's consistent and we've concluded that it's just month-end causing the overhead. The "stalling" issue was a separate one that usually occurred when TSM was struggling with storage pool(s) config or TSM simply went out to lunch. 

It sounds like you're onto something with respect to the sysmaster look-up/reads, but the storage manager could be suspect? 

HTH - 
Mark 

Mark Scranton
The Mark Scranton Group
"All Informix ... all the time." 
www.markscranton.com

Vote for best answer.
Score: 0  # Vote:  0
Date Posted: 2-Oct-2014, at 10:17 AM EST
From: Mark Scranton