IDS Forum

RE: What is all this I/O when doing a light ap....

Posted By: Andrew Ford
Date: Wednesday, 25 June 2014, at 10:15 a.m.

In Response To: RE: What is all this I/O when doing a light ap.... (John Miller iii)

Will the SQL Admin API wait for the checkpoint to complete or does it have
the same behavior of onmode -c?

Thanks,

Andrew

-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of John
Miller iii
Sent: Tuesday, June 24, 2014 5:10 PM
To: ids@iiug.org
Subject: RE: What is all this I/O when doing a light ap.... [33276]

Just a quick note.

1. onmode -c only request a checkpoint. the checkpoint must be complete for
the physical log optimization to take place. So if blocking checkpoint takes
10 second to complete your load will have been opened and require the
physical logging to take place.

2. The rows from the light append are not available until the light append
completes.

John F. Miller III
STSM, Lead Architect
miller3@us.ibm.com
503-747-1366
IBM Informix Dynamic Server (IDS)

ids-bounces@iiug.org wrote on 06/24/2014 02:10:27 PM:

> From: "Andrew Ford" <andrew@informix-dba.com>
> To: ids@iiug.org,
> Date: 06/24/2014 02:11 PM
> Subject: RE: What is all this I/O when doing a light ap.... [33275]
> Sent by: ids-bounces@iiug.org
>
> I definitely see output for light appends in onstat -g lap when
> loading
from
> external tables to raw tables, so I know it can be done even if the
manuals
> say it can't.
>
> I'm not seeing the behavior at the moment which is making me wonder if
> my

> attempt to force a checkpoint before loading failed and I didn't
> notice
it
> or just wasn't done like I thought it was when I was seeing all of the
I/O.
> Informix would have to physically log a page that has changed since a
> checkpoint (correct?) and that could have been where the I/O was
> coming from.
>
> If I can recreate the problem I will grab the onstat -g stk output
> that Jacques requested and post it to the forum.
>
> Thanks everyone for the responses,
>
> Andrew
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Richard Kofler
> Sent: Tuesday, June 24, 2014 3:57 PM
> To: ids@iiug.org
> Subject: Re: What is all this I/O when doing a light ap.... [33274]
>
> Am 24.06.2014 21:11, schrieb Andrew Ford:
> > Linux 12.10.FC3
> >
> > When using an external table to insert data into a raw table that
> > lives in an unlogged database I'm seeing a lot of read I/O to the
> > dbspace that holds the raw table and a lot of write I/O to the
> > physical

> log.
> >
> > Anyone know what this I/O is for and how I can avoid it?
> >
> > execute function sysadmin:task("onmode", "c", "hard");
> >
> > create external table external_table1 sameas table1 using
> > (datafiles("pipe:/pipes/pipe.p"));
> >
> > alter table table1 type(raw);
> >
> > insert into table1 select * from external_table1; -- dbspace read
> > and physical log write I/O occurs here before any rows are light
> > appended to
> > table1
> >
> > alter table table1 type(standard);
> >
> > Thanks!
> >
> > Andrew
> >
> >
> >
>
****************************************************************************

> ***
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> Hi Andrew,
>
> what you describe looks like what I see from HPL when it is forced
> into using de-luxe mode loads.
>
> What does onstat -g lap report when you see write I/O to the physical
log?
>
> This is fron V12.10.xC3 docs, Admin Guide ( 8-26 IBM Informix
> Administrator's Guide )
>
> -----
> Light appends are not supported for loading RAW tables, except in
> High-Performance Loader (HPL) operations and in queries that specify
> INTO

> TEMP .... WITH NO LOG.
> -----
>
> Maybe this is the reason.
>
> Regards
> dic_k
>
>
****************************************************************************

> ***
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>

****************************************************************************
***

> Forum Note: Use "Reply" to post a response in the discussion forum.
>

****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.

Messages In This Thread

• What is all this I/O when doing a light append to
  Andrew Ford -- Tuesday, 24 June 2014, at 3:11 p.m.
  • Re: What is all this I/O when doing a light append
    Fernando Nunes -- Tuesday, 24 June 2014, at 3:26 p.m.
    • RE: What is all this I/O when doing a light ap....
      Andrew Ford -- Tuesday, 24 June 2014, at 3:36 p.m.
      • RE: What is all this I/O when doing a light ap....
        Fernando Nunes -- Tuesday, 24 June 2014, at 3:45 p.m.
      • RE: What is all this I/O when doing a light ap....
        Fernando Nunes -- Tuesday, 24 June 2014, at 3:47 p.m.
  • Re: What is all this I/O when doing a light append
    JACQUES RENAUT -- Tuesday, 24 June 2014, at 4:22 p.m.
  • Re: What is all this I/O when doing a light append
    Richard Kofler -- Tuesday, 24 June 2014, at 4:56 p.m.
    • RE: What is all this I/O when doing a light ap....
      Andrew Ford -- Tuesday, 24 June 2014, at 5:10 p.m.
      • RE: What is all this I/O when doing a light ap....
        John Miller iii -- Tuesday, 24 June 2014, at 6:10 p.m.
        • RE: What is all this I/O when doing a light ap....
          Andrew Ford -- Wednesday, 25 June 2014, at 10:15 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.