LIGHT_APPENDS=FORCE ???
The poster expected IDS to use light appends for ordinary INSERTs/dbload into unlogged or raw tables with no indexes, as he read in the Administrator's Guide, but testing on 9.4 and 10.0 showed light appends only with inserts into non-logged temp tables; he filed a defect with IBM. Respondents said light appends are only supported with HPL (onpload) and INSERT/SELECT into temp tables (raw-table light appends being an XPS thing), because much of HPL's gain comes from building pages client-side and bypassing the buffer pool, which plain inserts can't do; the docs were seen as unclear. Another user confirmed no light appends on 11.5. No fix — only suggestions to use HPL, insert cursors and FET_BUF_SIZE=32767, plus a feature request for something like LIGHT_APPENDS=FORCE.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
In addition to hyperloader I wish that Informix would do light appends
during ordinary INSERT's. IBM tells me (and I am paraphrasing) that
it will do light appends when I load data into non-logged tables with
no indexes. But my tests showed that is only true when I insert into
a temp table with no log. It is false when I insert into a raw table
or run dbload into a raw table.
Light Appends are used under the following conditions with HPL (high
performance loader):
non-logged table (can be non log db or raw table)
no indexes on table
If you meet those 2 conditions and your using the HPL, then, feel free
to file a defect.
On Feb 24, 3:25 pm, LIGHT SCANS <light_sc...@yahoo.com> wrote:
> In addition to hyperloader I wish that Informix would do light appends
> during ordinary INSERT's. IBM tells me (and I am paraphrasing) that
> it will do light appends when I load data into non-logged tables with
> no indexes. But my tests showed that is only true when I insert into
> a temp table with no log. It is false when I insert into a raw table
> or run dbload into a raw table.
On 24 Feb, 23:25, LIGHT SCANS <light_sc...@yahoo.com> wrote:
> In addition to hyperloader I wish that Informix would do light appends
> during ordinary INSERT's. IBM tells me (and I am paraphrasing) that
> it will do light appends when I load data into non-logged tables with
> no indexes. But my tests showed that is only true when I insert into
> a temp table with no log. It is false when I insert into a raw table
> or run dbload into a raw table.
Which Informix version?
The Informix manuals of versions 9, 10 and 11 say it should work as is (without Hyperloader). I did open a defect on this with IBM. I am using Informix versions 9.4 and 10.0. I ran the same tests on both and got the same results. I suspect that 11 and 11.5 will show the same results, too. -L.S.
Could you please point me to the manual (manual and page # would be helpful) that says light appends work with ordinary insert statements or insert cursors? I did miss 1 scenario from the list I mentioned earlier... a light append is used for insert/select statements where the insert goes into a temp table. As far as I know, light appends have never been supported beyond HPL and insert/select into temp for any version of the product including XPS. On Feb 25, 7:05 pm, LIGHT SCANS <light_sc...@yahoo.com> wrote: > The Informix manuals of versions 9, 10 and 11 say it should work as is > (without Hyperloader). I did open a defect on this with IBM. I am > using Informix versions 9.4 and 10.0. I ran the same tests on both > and got the same results. I suspect that 11 and 11.5 will show the > same results, too. > > -L.S.
On 26 Feb, 03:05, LIGHT SCANS <light_sc...@yahoo.com> wrote:
> The Informix manuals of versions 9, 10 and 11 say it should work as is
> (without Hyperloader). I did open a defect on this with IBM. I am
> using Informix versions 9.4 and 10.0. I ran the same tests on both
> and got the same results. I suspect that 11 and 11.5 will show the
> same results, too.
>
> -L.S.
How are you testing this?
What makes you think that light appends are not happening?
Is there free space in the dbspace for a new extent?
As per http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp?topic=/com.ibm.admin.doc/ids_admin_0597.htm
are you
dropping tables at the same time?
Also http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp?topic=/com.ibm.sqls.doc/ids_sqs_0510.htm
implies that
light appends only occur for raw tables on XPS, perhaps the
documentation needs to be clearer here!
http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp?topic=/com.ibm.sqls.doc/ids_sqs_0073.htm
also implies that
light appends on raw tables only happen in XPS not IDS.
Try using the High Performance Loader in express mode and see if that
uses light appends.
http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp?topic=/com.ibm.hpl.doc/sii15manage985348.htm
says that it will
"The onpload utility creates new extents and fills them with the new
rows. However, onpload does not update the database structures that
track extents."
I think he wants to have lap when inserting into a raw table without
HPL
so a nice feature request would be
make light appends available for loads not using HPL into raw tables
OR ... ????
Superboer.
On 26 feb, 21:53, "da...@smooth1.co.uk" <da...@smooth1.co.uk> wrote:
> On 26 Feb, 03:05, LIGHT SCANS <light_sc...@yahoo.com> wrote:
>
> > The Informix manuals of versions 9, 10 and 11 say it should work as is
> > (without Hyperloader). I did open a defect on this with IBM. I am
> > using Informix versions 9.4 and 10.0. I ran the same tests on both
> > and got the same results. I suspect that 11 and 11.5 will show the
> > same results, too.
>
> > -L.S.
>
> How are you testing this?
>
> What makes you think that light appends are not happening?
>
> Is there free space in the dbspace for a new extent?
>
> As perhttp://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp?topic...
> are you
> dropping tables at the same time?
>
> Alsohttp://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp?topic...
> implies that
> light appends only occur for raw tables on XPS, perhaps the
> documentation needs to be clearer here!
>
> http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp?topic...
> also implies that
> light appends on raw tables only happen in XPS not IDS.
>
> Try using the High Performance Loader in express mode and see if that
> uses light appends.
>
> http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp?topic...
> says that it will
> "The onpload utility creates new extents and fills them with the new
> rows. However, onpload does not update the database structures that
> track extents."
Y'all are mostly right. Most Informix manuals mention "light appends" in conjunction with either HPL or XPS. I only found 1 in my favor (in all versions 9 through 11.5). It is in the IBM Informix Dynamic Server Administrator's Guide. I just want Informix to be faster for those times when people are doing regular (non-HPL) inserts. Informix is a great product. Why not make it better? Why not do it automatically or give us a parameter like "LIGHT_APPENDS=FORCE"?
Without a specific manual and page #, I can't verify that. I've searched and find no reference to light appends working with normal inserts. The reason light appends don't work with normal inserts is because light appends are only part of the performance improvement you get when using HPL or insert/select. Much of the performance gain from HPL comes from the fact that the pages are constructed on the client side, in shared memory and then just a pointer is shipped to the server so that the page can be directly inserted on disk, bypassing the bufferpool. That is simply not possible with normal inserts. If you want to speed up loads and your not doing this yet, try the following.... export FET_BUF_SIZE=32767 for your client use insert cursors On Feb 27, 8:00 am, LIGHT SCANS <light_sc...@yahoo.com> wrote: > Y'all are mostly right. Most Informix manuals mention "light appends" > in conjunction with either HPL or XPS. I only found 1 in my favor (in > all versions 9 through 11.5). It is in the IBM Informix Dynamic > Server Administrator's Guide. I just want Informix to be faster for > those times when people are doing regular (non-HPL) inserts. Informix > is a great product. Why not make it better? Why not do it > automatically or give us a parameter like "LIGHT_APPENDS=FORCE"?
do not read the manual; just try it i did on V11.5 and there are no
laps
onstat -gr lap during an insert of nonlogged or raw table even whenthe table is locked exclusive
and there were no indexes on them.
Superboer.
On 28 feb, 17:55, pokeyma...@yahoo.com wrote:
> Without a specific manual and page #, I can't verify that. I've
> searched and find no reference to light appends working with normal
> inserts.
>
> The reason light appends don't work with normal inserts is because
> light appends are only part of the performance improvement you get
> when using HPL or insert/select. Much of the performance gain from HPL
> comes from the fact that the pages are constructed on the client side,
> in shared memory and then just a pointer is shipped to the server so
> that the page can be directly inserted on disk, bypassing the
> bufferpool. That is simply not possible with normal inserts.
>
> If you want to speed up loads and your not doing this yet, try the
> following....
> export FET_BUF_SIZE=32767 for your client
> use insert cursors
>
> On Feb 27, 8:00 am, LIGHT SCANS <light_sc...@yahoo.com> wrote:
>
> > Y'all are mostly right. Most Informix manuals mention "light appends"
> > in conjunction with either HPL or XPS. I only found 1 in my favor (in
> > all versions 9 through 11.5). It is in the IBM Informix Dynamic
> > Server Administrator's Guide. I just want Informix to be faster for
> > those times when people are doing regular (non-HPL) inserts. Informix
> > is a great product. Why not make it better? Why not do it
> > automatically or give us a parameter like "LIGHT_APPENDS=FORCE"?