Virtual segment usage - now I'm confused.....
A session on IDS 9.30 on HP-UX blew out the virtual memory portion: onstat -g mem/-g stm showed ~10,700 copies of the same prepared SELECT, each with a 63KB heap (~675MB). Responders explained this is an application resource leak, not a server bug: the PHP/JDBC code prepares the statement (and/or opens cursors) repeatedly with new names without freeing result sets, statements or cursors. Fix is to free/close them in the application (e.g. ifx_free_result equivalent) rather than relying on destructors or program exit. Enabling the SQL statement cache was discussed but noted as possibly helping runtime, not memory usage. No follow-up confirming the fix was posted.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
OK we managed to catch the process that blew up our Virtual portion.
It's a remote PHP/javascript application that establishes a connection
via jdbc to the database (9.30HC5 on HP/UX 11i).
We managed to fire off an onstat -a and having used onstat -g mem to
get the sid, found the stack usage (same as onstat -g stm). Turns out
the renegade session showed 10,700 copies of EXACTLY the same query,
each with its own block address and using a 63k heap size causing
675Mb or so of allocation (it would have had more but I'd onmode -z'd
it). The query is reproduced below (as if it would be any help).
I'm assuming this means that some code's got into a tight loop and
resubmitted the same query, but does this further mean that the heap
is not released each time round?
The Admin guide's not a whole lot of good here, help me out guys, I
need to report to manglement (translation: I need to target the LART
elsewhere before it falls on me by default as DBA!)
The query: (yes I know there's loads of ORs, CASEs and function calls.
Do they listen to me?)
SELECT plan__id_code, plntypid_code, scmptpid_code, rule_code,
comp_1, comp_2, comp_3, comp_4, comp_5,
value_1, value_2, value_3, value_4, value_5,
to_char( stenddat_strtdat, '%d/%m/%Y' ) stenddat_strtdat,
to_char( stenddat_enddat, '%d/%m/%Y' ) stenddat_enddat,CASE WHEN plan__id_code = 'ALL' THEN 2
WHEN plan__id_code = 'ANY' THEN 3
ELSE 1 END pln_code,
CASE WHEN scmptpid_code = 'ALL' THEN 2
WHEN scmptpid_code = 'ANY' THEN 3
WHEN scmptpid_code = 'NONE' THEN 4
ELSE 1 END cmp_code
FROM quotes:plan_rules
WHERE ( plntypid_code = 'plntyp1' OR plntypid_code = 'ALL' )
AND ( plan__id_code = 'plntyp2' OR plan__id_code = 'ALL' OR
plan__id_code = 'ANY' ) AND rule_code = ?
AND ( scmptpid_code = 'ANY' OR scmptpid_code = 'ALL' OR
scmptpid_code = 'ANY'
OR scmptpid_code = 'NONE' )
AND status_code != 'cancelled'
AND stenddat_strtdat <= '31.10.2007'
AND ( stenddat_enddat >= '31.10.2007' OR stenddat_enddat IS NULL )
AND comp_1 = ? AND comp_2 = ? AND comp_3 = ? AND comp_4 = ? and comp_5
= ?
iiug@perrior.net wrote:
> OK we managed to catch the process that blew up our Virtual portion.
> It's a remote PHP/javascript application that establishes a connection
> via jdbc to the database (9.30HC5 on HP/UX 11i).
> We managed to fire off an onstat -a and having used onstat -g mem to
> get the sid, found the stack usage (same as onstat -g stm). Turns out
> the renegade session showed 10,700 copies of EXACTLY the same query,
> each with its own block address and using a 63k heap size causing
> 675Mb or so of allocation (it would have had more but I'd onmode -z'd
> it). The query is reproduced below (as if it would be any help).
> I'm assuming this means that some code's got into a tight loop and
> resubmitted the same query, but does this further mean that the heap
> is not released each time round?
> The Admin guide's not a whole lot of good here, help me out guys, I
> need to report to manglement (translation: I need to target the LART
> elsewhere before it falls on me by default as DBA!)
>
> The query: (yes I know there's loads of ORs, CASEs and function calls.
> Do they listen to me?)
>
> SELECT plan__id_code, plntypid_code, scmptpid_code, rule_code,
> comp_1, comp_2, comp_3, comp_4, comp_5,
> value_1, value_2, value_3, value_4, value_5,
> to_char( stenddat_strtdat, '%d/%m/%Y' ) stenddat_strtdat,
> to_char( stenddat_enddat, '%d/%m/%Y' ) stenddat_enddat,> CASE WHEN plan__id_code = 'ALL' THEN 2
> WHEN plan__id_code = 'ANY' THEN 3
> ELSE 1 END pln_code,
> CASE WHEN scmptpid_code = 'ALL' THEN 2
> WHEN scmptpid_code = 'ANY' THEN 3
> WHEN scmptpid_code = 'NONE' THEN 4
> ELSE 1 END cmp_code
> FROM quotes:plan_rules
> WHERE ( plntypid_code = 'plntyp1' OR plntypid_code = 'ALL' )
> AND ( plan__id_code = 'plntyp2' OR plan__id_code = 'ALL' OR
> plan__id_code = 'ANY' ) AND rule_code = ?
> AND ( scmptpid_code = 'ANY' OR scmptpid_code = 'ALL' OR
> scmptpid_code = 'ANY'
> OR scmptpid_code = 'NONE' )
> AND status_code != 'cancelled'
> AND stenddat_strtdat <= '31.10.2007'
> AND ( stenddat_enddat >= '31.10.2007' OR stenddat_enddat IS NULL )
> AND comp_1 = ? AND comp_2 = ? AND comp_3 = ? AND comp_4 = ? and comp_5
> = ?
>
You can get this effect with a simple PHP query/web page if you don't free the
result sets...
It may be different in recent PHP versions (with PDO), but I had exactly the
same problem recently. an ifx_free_result (I believe this is the function name,
but check the manual) solved it.
I've also seen this with JAVA applications...
Regards.
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
On Nov 1, 8:50 am, i...@perrior.net wrote:
> OK we managed to catch the process that blew up our Virtual portion.
> It's a remote PHP/javascript application that establishes a connection
> via jdbc to the database (9.30HC5 on HP/UX 11i).
> We managed to fire off an onstat -a and having used onstat -g mem to
> get the sid, found the stack usage (same as onstat -g stm). Turns out
> the renegade session showed 10,700 copies of EXACTLY the same query,
> each with its own block address and using a 63k heap size causing
> 675Mb or so of allocation (it would have had more but I'd onmode -z'd
> it). The query is reproduced below (as if it would be any help).
> I'm assuming this means that some code's got into a tight loop and
> resubmitted the same query, but does this further mean that the heap
> is not released each time round?
> The Admin guide's not a whole lot of good here, help me out guys, I
> need to report to manglement (translation: I need to target the LART
> elsewhere before it falls on me by default as DBA!)
>
> The query: (yes I know there's loads of ORs, CASEs and function calls.
> Do they listen to me?)
>
> SELECT plan__id_code, plntypid_code, scmptpid_code, rule_code,
> comp_1, comp_2, comp_3, comp_4, comp_5,
> value_1, value_2, value_3, value_4, value_5,
> to_char( stenddat_strtdat, '%d/%m/%Y' ) stenddat_strtdat,
> to_char( stenddat_enddat, '%d/%m/%Y' ) stenddat_enddat,> CASE WHEN plan__id_code = 'ALL' THEN 2
> WHEN plan__id_code = 'ANY' THEN 3
> ELSE 1 END pln_code,
> CASE WHEN scmptpid_code = 'ALL' THEN 2
> WHEN scmptpid_code = 'ANY' THEN 3
> WHEN scmptpid_code = 'NONE' THEN 4
> ELSE 1 END cmp_code
> FROM quotes:plan_rules
> WHERE ( plntypid_code = 'plntyp1' OR plntypid_code = 'ALL' )
> AND ( plan__id_code = 'plntyp2' OR plan__id_code = 'ALL' OR
> plan__id_code = 'ANY' ) AND rule_code = ?
> AND ( scmptpid_code = 'ANY' OR scmptpid_code = 'ALL' OR
> scmptpid_code = 'ANY'
> OR scmptpid_code = 'NONE' )
> AND status_code != 'cancelled'
> AND stenddat_strtdat <= '31.10.2007'
> AND ( stenddat_enddat >= '31.10.2007' OR stenddat_enddat IS NULL )
> AND comp_1 = ? AND comp_2 = ? AND comp_3 = ? AND comp_4 = ? and comp_5
> = ?
This is a prepared statement. If the PHP/Javascript is generating a
unique internal statement name for the statement and preparing it over
and over again without freeing it, and/or if each time it plugs in
different parameters and opens a cursor to fetch the results it
generates a unique cursor name and doesn't close and free the previous
one there is your culprit.
Art S. Kagel
On 1 Nov, 13:32, "Art S. Kagel" <art.ka...@gmail.com> wrote:
> On Nov 1, 8:50 am, i...@perrior.net wrote:
>
>
>
> > OK we managed to catch the process that blew up our Virtual portion.
> > It's a remote PHP/javascript application that establishes a connection
> > via jdbc to the database (9.30HC5 on HP/UX 11i).
> > We managed to fire off an onstat -a and having used onstat -g mem to
> > get the sid, found the stack usage (same as onstat -g stm). Turns out
> > the renegade session showed 10,700 copies of EXACTLY the same query,
> > each with its own block address and using a 63k heap size causing
> > 675Mb or so of allocation (it would have had more but I'd onmode -z'd
> > it). The query is reproduced below (as if it would be any help).
> > I'm assuming this means that some code's got into a tight loop and
> > resubmitted the same query, but does this further mean that the heap
> > is not released each time round?
> > The Admin guide's not a whole lot of good here, help me out guys, I
> > need to report to manglement (translation: I need to target the LART
> > elsewhere before it falls on me by default as DBA!)
>
> > The query: (yes I know there's loads of ORs, CASEs and function calls.
> > Do they listen to me?)
>
> > SELECT plan__id_code, plntypid_code, scmptpid_code, rule_code,
> > comp_1, comp_2, comp_3, comp_4, comp_5,
> > value_1, value_2, value_3, value_4, value_5,
> > to_char( stenddat_strtdat, '%d/%m/%Y' ) stenddat_strtdat,
> > to_char( stenddat_enddat, '%d/%m/%Y' ) stenddat_enddat,> > CASE WHEN plan__id_code = 'ALL' THEN 2
> > WHEN plan__id_code = 'ANY' THEN 3
> > ELSE 1 END pln_code,
> > CASE WHEN scmptpid_code = 'ALL' THEN 2
> > WHEN scmptpid_code = 'ANY' THEN 3
> > WHEN scmptpid_code = 'NONE' THEN 4
> > ELSE 1 END cmp_code
> > FROM quotes:plan_rules
> > WHERE ( plntypid_code = 'plntyp1' OR plntypid_code = 'ALL' )
> > AND ( plan__id_code = 'plntyp2' OR plan__id_code = 'ALL' OR
> > plan__id_code = 'ANY' ) AND rule_code = ?
> > AND ( scmptpid_code = 'ANY' OR scmptpid_code = 'ALL' OR
> > scmptpid_code = 'ANY'
> > OR scmptpid_code = 'NONE' )
> > AND status_code != 'cancelled'
> > AND stenddat_strtdat <= '31.10.2007'
> > AND ( stenddat_enddat >= '31.10.2007' OR stenddat_enddat IS NULL )
> > AND comp_1 = ? AND comp_2 = ? AND comp_3 = ? AND comp_4 = ? and comp_5
> > = ?
>
> This is a prepared statement. If the PHP/Javascript is generating a
> unique internal statement name for the statement and preparing it over
> and over again without freeing it, and/or if each time it plugs in
> different parameters and opens a cursor to fetch the results it
> generates a unique cursor name and doesn't close and free the previous
> one there is your culprit.
>
> Art S. Kagel
Thanks both of you, useful info I'll pass on.
We've got the SQL statement cache off at present, would it help to
have it set to ENABLED (mode 2 in onconfig) or would it not have
helped with the prepared statement here?
As we're DSS with (normally) rarely re-used queries we decided against
using it - worth enabling anyway?
iiug@perrior.net wrote:
> On 1 Nov, 13:32, "Art S. Kagel" <art.ka...@gmail.com> wrote:
>> On Nov 1, 8:50 am, i...@perrior.net wrote:
>>
>>
>>
>>> OK we managed to catch the process that blew up our Virtual portion.
>>> It's a remote PHP/javascript application that establishes a connection
>>> via jdbc to the database (9.30HC5 on HP/UX 11i).
>>> We managed to fire off an onstat -a and having used onstat -g mem to
>>> get the sid, found the stack usage (same as onstat -g stm). Turns out
>>> the renegade session showed 10,700 copies of EXACTLY the same query,
>>> each with its own block address and using a 63k heap size causing
>>> 675Mb or so of allocation (it would have had more but I'd onmode -z'd
>>> it). The query is reproduced below (as if it would be any help).
>>> I'm assuming this means that some code's got into a tight loop and
>>> resubmitted the same query, but does this further mean that the heap
>>> is not released each time round?
>>> The Admin guide's not a whole lot of good here, help me out guys, I
>>> need to report to manglement (translation: I need to target the LART
>>> elsewhere before it falls on me by default as DBA!)
>>> The query: (yes I know there's loads of ORs, CASEs and function calls.
>>> Do they listen to me?)
>>> SELECT plan__id_code, plntypid_code, scmptpid_code, rule_code,
>>> comp_1, comp_2, comp_3, comp_4, comp_5,
>>> value_1, value_2, value_3, value_4, value_5,
>>> to_char( stenddat_strtdat, '%d/%m/%Y' ) stenddat_strtdat,
>>> to_char( stenddat_enddat, '%d/%m/%Y' ) stenddat_enddat,>>> CASE WHEN plan__id_code = 'ALL' THEN 2
>>> WHEN plan__id_code = 'ANY' THEN 3
>>> ELSE 1 END pln_code,
>>> CASE WHEN scmptpid_code = 'ALL' THEN 2
>>> WHEN scmptpid_code = 'ANY' THEN 3
>>> WHEN scmptpid_code = 'NONE' THEN 4
>>> ELSE 1 END cmp_code
>>> FROM quotes:plan_rules
>>> WHERE ( plntypid_code = 'plntyp1' OR plntypid_code = 'ALL' )
>>> AND ( plan__id_code = 'plntyp2' OR plan__id_code = 'ALL' OR
>>> plan__id_code = 'ANY' ) AND rule_code = ?
>>> AND ( scmptpid_code = 'ANY' OR scmptpid_code = 'ALL' OR
>>> scmptpid_code = 'ANY'
>>> OR scmptpid_code = 'NONE' )
>>> AND status_code != 'cancelled'
>>> AND stenddat_strtdat <= '31.10.2007'
>>> AND ( stenddat_enddat >= '31.10.2007' OR stenddat_enddat IS NULL )
>>> AND comp_1 = ? AND comp_2 = ? AND comp_3 = ? AND comp_4 = ? and comp_5
>>> = ?
>> This is a prepared statement. If the PHP/Javascript is generating a
>> unique internal statement name for the statement and preparing it over
>> and over again without freeing it, and/or if each time it plugs in
>> different parameters and opens a cursor to fetch the results it
>> generates a unique cursor name and doesn't close and free the previous
>> one there is your culprit.
>>
>> Art S. Kagel
>
> Thanks both of you, useful info I'll pass on.
> We've got the SQL statement cache off at present, would it help to
> have it set to ENABLED (mode 2 in onconfig) or would it not have
> helped with the prepared statement here?
> As we're DSS with (normally) rarely re-used queries we decided against
> using it - worth enabling anyway?
>
I'd say it won't harm you to enable it, but it probably won't solve your issue.
Most of the parameters can be changes dinamically... so you can give it a try
and monitor it's use..
Regards
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
On Nov 1, 11:11 am, Fernando Nunes <s...@domus.online.pt> wrote:
> i...@perrior.net wrote:
> > On 1 Nov, 13:32, "Art S. Kagel" <art.ka...@gmail.com> wrote:
> >> On Nov 1, 8:50 am, i...@perrior.net wrote:
>
> >>> OK we managed to catch the process that blew up our Virtual portion.
> >>> It's a remote PHP/javascript application that establishes a connection
> >>> via jdbc to the database (9.30HC5 on HP/UX 11i).
> >>> We managed to fire off an onstat -a and having used onstat -g mem to
> >>> get the sid, found the stack usage (same as onstat -g stm). Turns out
> >>> the renegade session showed 10,700 copies of EXACTLY the same query,
> >>> each with its own block address and using a 63k heap size causing
> >>> 675Mb or so of allocation (it would have had more but I'd onmode -z'd
> >>> it). The query is reproduced below (as if it would be any help).
> >>> I'm assuming this means that some code's got into a tight loop and
> >>> resubmitted the same query, but does this further mean that the heap
> >>> is not released each time round?
> >>> The Admin guide's not a whole lot of good here, help me out guys, I
> >>> need to report to manglement (translation: I need to target the LART
> >>> elsewhere before it falls on me by default as DBA!)
> >>> The query: (yes I know there's loads of ORs, CASEs and function calls.
> >>> Do they listen to me?)
> >>> SELECT plan__id_code, plntypid_code, scmptpid_code, rule_code,
> >>> comp_1, comp_2, comp_3, comp_4, comp_5,
> >>> value_1, value_2, value_3, value_4, value_5,
> >>> to_char( stenddat_strtdat, '%d/%m/%Y' ) stenddat_strtdat,
> >>> to_char( stenddat_enddat, '%d/%m/%Y' ) stenddat_enddat,> >>> CASE WHEN plan__id_code = 'ALL' THEN 2
> >>> WHEN plan__id_code = 'ANY' THEN 3
> >>> ELSE 1 END pln_code,
> >>> CASE WHEN scmptpid_code = 'ALL' THEN 2
> >>> WHEN scmptpid_code = 'ANY' THEN 3
> >>> WHEN scmptpid_code = 'NONE' THEN 4
> >>> ELSE 1 END cmp_code
> >>> FROM quotes:plan_rules
> >>> WHERE ( plntypid_code = 'plntyp1' OR plntypid_code = 'ALL' )
> >>> AND ( plan__id_code = 'plntyp2' OR plan__id_code = 'ALL' OR
> >>> plan__id_code = 'ANY' ) AND rule_code = ?
> >>> AND ( scmptpid_code = 'ANY' OR scmptpid_code = 'ALL' OR
> >>> scmptpid_code = 'ANY'
> >>> OR scmptpid_code = 'NONE' )
> >>> AND status_code != 'cancelled'
> >>> AND stenddat_strtdat <= '31.10.2007'
> >>> AND ( stenddat_enddat >= '31.10.2007' OR stenddat_enddat IS NULL )
> >>> AND comp_1 = ? AND comp_2 = ? AND comp_3 = ? AND comp_4 = ? and comp_5
> >>> = ?
> >> This is a prepared statement. If the PHP/Javascript is generating a
> >> unique internal statement name for the statement and preparing it over
> >> and over again without freeing it, and/or if each time it plugs in
> >> different parameters and opens a cursor to fetch the results it
> >> generates a unique cursor name and doesn't close and free the previous
> >> one there is your culprit.
>
> >> Art S. Kagel
>
> > Thanks both of you, useful info I'll pass on.
> > We've got the SQL statement cache off at present, would it help to
> > have it set to ENABLED (mode 2 in onconfig) or would it not have
> > helped with the prepared statement here?
> > As we're DSS with (normally) rarely re-used queries we decided against
> > using it - worth enabling anyway?
>
> I'd say it won't harm you to enable it, but it probably won't solve your issue.
> Most of the parameters can be changes dinamically... so you can give it a try
> and monitor it's use..
But the statement cache doesn't care about parameters or even hard
coded values in the query. Since the final optimization step is
deferred until the parameters are supplied, the major optimization
decisions can be applied to any similar query and the cache applies
the same logic to similar queries with hard coded filters, it merely
marks them for a final optimization step as if they had relaceable
parameters for each filter value. So, it is possible that turning the
cache on will help runtime, however, it's not going to change the
memory usage!
Rewriting the objects so that they close and free statements and
cursors when they are no longer needed, instead of waiting until the
objects' destructors fire, or worse until the application exits, to
free resources is the only thing that will solve that problem.
Art S. Kagel