What is all this I/O when doing a light append to
Andrew Ford saw heavy dbspace read I/O and physical-log writes when loading an external table into a RAW table in an unlogged database (IDS 12.10.FC3 on Linux), despite forcing a checkpoint first. Suggestions included checking space/extent size, onstat -g lap, onstat -g stk, opening a PMR, and docs claiming light appends aren't supported for RAW tables. John Miller noted onmode -c only requests a checkpoint; if it hasn't finished before the load opens, physical logging still occurs. Andrew couldn't reproduce it afterwards, and the thread ends with his unanswered question of whether the SQL Admin API waits for checkpoint completion, so no firm resolution is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
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 andphysical log write I/O occurs here before any rows are light appended to
table1
alter table table1 type(standard);
Thanks!
Andrew
I was goingo to suggest a checkpoint before but I see you're doing it...
Are you short on space in that dbspace? Extent size of the target table?....
On Jun 24, 2014 9:12 PM, "Andrew Ford" <andrew@informix-dba.com> wrote:
> 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.
>
>
--089e0118321aee722904fc99ee06
Plenty of space available in the dbspace and the first extent (3677728 KB)
is large enough to hold the entire dataset.
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Fernando Nunes
Sent: Tuesday, June 24, 2014 2:26 PM
To: ids@iiug.org
Subject: Re: What is all this I/O when doing a light ap.... [33269]
I was goingo to suggest a checkpoint before but I see you're doing it...
Are you short on space in that dbspace? Extent size of the target table?....
On Jun 24, 2014 9:12 PM, "Andrew Ford" <andrew@informix-dba.com> wrote:
> 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.
>
>
--089e0118321aee722904fc99ee06
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
Errr.... PMR?
On Jun 24, 2014 9:37 PM, "Andrew Ford" <andrew@informix-dba.com> wrote:
> Plenty of space available in the dbspace and the first extent (3677728 KB)
> is large enough to hold the entire dataset.
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Fernando Nunes
> Sent: Tuesday, June 24, 2014 2:26 PM
> To: ids@iiug.org
> Subject: Re: What is all this I/O when doing a light ap.... [33269]
>
> I was goingo to suggest a checkpoint before but I see you're doing it...
> Are you short on space in that dbspace? Extent size of the target
> table?....
>
> On Jun 24, 2014 9:12 PM, "Andrew Ford" <andrew@informix-dba.com> wrote:
>
> > 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.
> >
> >
>
> --089e0118321aee722904fc99ee06
>
>
> ****************************************************************************
> ***
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001a11c2d5ceba4ab504fc9a323d
Or test on another version...? 12.10.xC4 is out... not that I have any
indication it would make a difference...
On Jun 24, 2014 9:37 PM, "Andrew Ford" <andrew@informix-dba.com> wrote:
> Plenty of space available in the dbspace and the first extent (3677728 KB)
> is large enough to hold the entire dataset.
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Fernando Nunes
> Sent: Tuesday, June 24, 2014 2:26 PM
> To: ids@iiug.org
> Subject: Re: What is all this I/O when doing a light ap.... [33269]
>
> I was goingo to suggest a checkpoint before but I see you're doing it...
> Are you short on space in that dbspace? Extent size of the target
> table?....
>
> On Jun 24, 2014 9:12 PM, "Andrew Ford" <andrew@informix-dba.com> wrote:
>
> > 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.
> >
> >
>
> --089e0118321aee722904fc99ee06
>
>
> ****************************************************************************
> ***
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001a11c2f6442892b704fc9a3a05
Original post:
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 andphysical log write I/O occurs here before any rows are light appended to
table1
alter table table1 type(standard);
Thanks!
Andrew
Response:
If you can post an onstat -g stk output for the sqlexec thread doing the
insert/IO (while it's doing those things) I can try and see what/why it's
doing whatever it's doing.
Jacques Renaut
IBM Informix Advanced Support
APD Team
IBM
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
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.
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.
>
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.
Related threads
- Posting from the Informix-list
- Migrating from IDS 9.40.UC6 to 11.50.UC3
- Ip for a network session
- questions onstat -g