IDS suddenly generates 4 times more logical logs
A DBA on IDS 11.50.FC5 (Solaris 10) saw logical log generation suddenly quadruple and, with hundreds of applications connecting, wanted a way to pinpoint which table/app was responsible, asking whether onlog output could be parsed. Advice from IBM's John Miller was to use the SQL Trace feature and check sysmaster:syssqltrace.sql_logspace, plus sysrstcb columns (upf_logspused, upf_logspmax, upf_lgrecs) for per-session log usage; a suggested setting was SQLTRACE level=low,ntraces=5000,size=2,mode=global. Clarification followed that sql_logspace is in bytes (Art Kagel said KB). A side question about finding a table's last access time was answered: that information isn't available. The thread ends without reporting what the actual culprit was.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
My customers database server (IDS 11.50.FC5 in Solaris 10 Sparc) suddenly
started to generate 4 times more logical logs in the middle of December. I
have tried to investigate what causes this without any success.
It's obvious that an application causes this but the customer have between 500
- 1.000 different applications that connects to the database. So my thought
was to figure out what table generates most logical log data. Is there a way
to get this kind of information by using the onlog command and then process
the output with Perl or similar script language ?
/Plexor
I have a different issue but the question remains very closely related. There
should be a way that we can see last access times for a table since Informix
was brought up last. I have been looking in sysmaster but so far no luck.
Maybe someone can help us out.
Thanx,
Dan
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
JIMMY.JONSSON@trnswrks.com
Sent: Tuesday, January 18, 2011 7:15 AM
To: ids@iiug.org
Subject: IDS suddenly generates 4 times more logical logs [22459]
My customers database server (IDS 11.50.FC5 in Solaris 10 Sparc) suddenly
started to generate 4 times more logical logs in the middle of December. I
have tried to investigate what causes this without any success.
It's obvious that an application causes this but the customer have between 500
- 1.000 different applications that connects to the database. So my thought
was to figure out what table generates most logical log data. Is there a way
to get this kind of information by using the onlog command and then process
the output with Perl or similar script language ?
/Plexor
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
It's not there.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art@iiug.org)
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 Tue, Jan 18, 2011 at 7:21 AM, Dan Mueller <Dan.Mueller@trnswrks.com>wrote:
> I have a different issue but the question remains very closely related.
> There
> should be a way that we can see last access times for a table since
> Informix
> was brought up last. I have been looking in sysmaster but so far no luck.
> Maybe someone can help us out.
>
> Thanx,
> Dan
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> JIMMY.JONSSON@trnswrks.com
> Sent: Tuesday, January 18, 2011 7:15 AM
> To: ids@iiug.org
> Subject: IDS suddenly generates 4 times more logical logs [22459]
>
> My customers database server (IDS 11.50.FC5 in Solaris 10 Sparc) suddenly
> started to generate 4 times more logical logs in the middle of December. I
> have tried to investigate what causes this without any success.
>
> It's obvious that an application causes this but the customer have between
> 500
> - 1.000 different applications that connects to the database. So my thought
> was to figure out what table generates most logical log data. Is there a
> way
> to get this kind of information by using the onlog command and then process
> the output with Perl or similar script language ?
>
> /Plexor
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001636c5b15bdea6b8049a1e23c8
Since you are on version 11.50 why not use the SQL Trace feature. This
will
tell you the amount of data each SQL statement generates. The specific
column
name is sysmaster:syssqltrace.sql_logspace. In addition, the maximum and
current log space consumed by session is stored in the
sysmaster.sysrstcb.upf_logspmax and sysmaster.sysrstcb.upf_logspused
You can also use onlog to see the log records generated.
John F. Miller III
STSM, Embedability Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)
ids-bounces@iiug.org wrote on 01/18/2011 04:15:11 AM:
> [image removed]
>
> IDS suddenly generates 4 times more logical logs [22459]
>
> JIMMY JONSSON
>
> to:
>
> ids
>
> 01/18/2011 04:15 AM
>
> Sent by:
>
> ids-bounces@iiug.org
>
> Please respond to ids
>
> My customers database server (IDS 11.50.FC5 in Solaris 10 Sparc) suddenly
> started to generate 4 times more logical logs in the middle of December.
I
> have tried to investigate what causes this without any success.
>
> It's obvious that an application causes this but the customer have
> between 500
> - 1.000 different applications that connects to the database. So my
thought
> was to figure out what table generates most logical log data. Is there a
way
> to get this kind of information by using the onlog command and then
process
> the output with Perl or similar script language ?
>
> /Plexor
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
I'm going to try it ;-) Any recommendation on how to set the SQLTRACE parameter ?
SQLTRACE level=3Dlow,ntraces=3D5000,size=3D2,mode=3Dglobal Low level tracing (fastest) Number of traces in the SQL trace buffer =3D 5000 Size of each trace buffer entry =3D 2Kb Global mode (trace for all users) This should give us some good starting values. HTH, -- Mirav From: JIMMY JONSSON <> To: ids@iiug.org Date: 01/19/2011 02:59 AM Subject: Re: IDS suddenly generates 4 times more logical lo [22483] Sent by: ids-bounces@iiug.org I'm going to try it ;-) Any recommendation on how to set the SQLTRACE parameter ? ***********************************************************************= ******** Forum Note: Use "Reply" to post a response in the discussion forum. =
Does sql_logspace column show the log space used i bytes or blocks ?
KB Art On Jan 23, 2011 6:46 AM, <> wrote: > Does sql_logspace column show the log space used i bytes or blocks ? > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > --20cf3054a5b3c23a19049a85e04c
sql_logspace is stored in bytes and is closely related to
sysmaster:sysrstcb.upf_logspused
column.
Here is some more information that might help you along with a demo program
that helps
explains the usage of some of the columns in the sysmaster:sysrstcb
1. upf_iscommit The number of implicit or explicit commit works
that session has done
2. upf_longtxs The number of long transactions generated by this
users
3. upf_logspuse The amount of log space consumed in bytes by the
current transaction
Reset to 0 after transaction completes.
4. upf_logspmax The largest transaction in byte consumed by this user
5 upf_lgrecs The number of logical log records generated by this
user
=======================PROGRAM==================================
database sysmaster;select "SYSMASTER"::char(12), upf_iscommit, upf_longtxs, upf_logspuse,
upf_logspmax, upf_lgrecs
from sysmaster:sysrstcb where sid = dbinfo('sessionid');
create database d1 with log;select "CREATE DBS"::char(12), upf_iscommit, upf_longtxs, upf_logspuse,
upf_logspmax, upf_lgrecs
from sysmaster:sysrstcb where sid = dbinfo('sessionid');
create table t1 (c1 serial, c2 char(250));select "CREATE TABLE"::char(12), upf_iscommit, upf_longtxs, upf_logspuse,
upf_logspmax, upf_lgrecs
from sysmaster:sysrstcb where sid = dbinfo('sessionid');
begin;
insert into t1 values (0,"JOHN");select "INSERT"::char(12),upf_iscommit, upf_longtxs, upf_logspuse,
upf_logspmax, upf_lgrecs
from sysmaster:sysrstcb where sid = dbinfo('sessionid');
commit;
select "COMMIT"::char(12),upf_iscommit, upf_longtxs, upf_logspuse,
upf_logspmax, upf_lgrecs
from sysmaster:sysrstcb where sid = dbinfo('sessionid');
begin;
delete from t1 where c1=1;select "DELETE"::char(12), upf_iscommit, upf_longtxs, upf_logspuse,
upf_logspmax, upf_lgrecs
from sysmaster:sysrstcb where sid = dbinfo('sessionid');
commit;
=======================OUTPUT==================================
(constant) upf_iscommit upf_longtxs upf_logspuse upf_logspmax upf_lgrecs
SYSMASTER 0 0 0 0 0
(constant) upf_iscommit upf_longtxs upf_logspuse upf_logspmax upf_lgrecs
CREATE DBS 1 0 0 2263472 15689
(constant) upf_iscommit upf_longtxs upf_logspuse upf_logspmax upf_lgrecs
CREATE TABLE 2 0 0 2263472 15718
(constant) upf_iscommit upf_longtxs upf_logspuse upf_logspmax upf_lgrecs
INSERT 2 0 424 2263472 15721
(constant) upf_iscommit upf_longtxs upf_logspuse upf_logspmax upf_lgrecs
COMMIT 3 0 0 2263472 15722
(constant) upf_iscommit upf_longtxs upf_logspuse upf_logspmax upf_lgrecs
DELETE 3 0 376 2263472 15724
John F. Miller III
STSM, Embedability Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)
ids-bounces@iiug.org wrote on 01/23/2011 05:46:40 AM:
> From:
>
> JIMMY JONSSON <>
>
> To:
>
> ids@iiug.org
>
> Date:
>
> 01/23/2011 05:47 AM
>
> Subject:
>
> Re: IDS suddenly generates 4 times more logical lo [22534]
>
> Sent by:
>
> ids-bounces@iiug.org
>
> Does sql_logspace column show the log space used i bytes or blocks ?
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>