IDS Forum

Re: Insert into an indexed table is really SLOW!

Posted By: Richard Kofler
Date: Friday, 10 July 2009, at 1:51 p.m.

In Response To: Re: Insert into an indexed table is really SLOW! (MIKE DUNHAM-WILKIE)

MIKE DUNHAM-WILKIE schrieb:
> Thank you all for the very helpful suggestions. I have made the recommending
> changes and want to report on the results. First, though, I'll explain why I
> can't load into an unindexed table and then create the index at the end. What
> I am doing is simulating a continuous load into a table, loading measurements
> from a continuously operating instrument. Specifically what I do is a 3-phase
> process: load some number N records into an uncompressed table, then compute
> compression statistics and compress the table (using 'execute function
> sysadmin:task("table compress repack shrink",...)'), then continue to load
> records (forever!) into the compressed table. For this experiment I picked N =
> 1000000 (probably higher than necessary), did my compression, then loaded
> another 1000000 records. (This is a smaller experiment than the 25,000,000 row
> load I reported in my original email, but it still illustrates the problem).
>
> Initial setup. FILLFACTOR 90, BUFFERS 1000, small initial and next extents.
> The timings for the three phases were 11+14+15 minutes. At the end my table
> had just 4 extents, but each of the three indexes had around 115 extents!
>
> Setup 1. Preallocate large initial extent, no other changes
> The timings went down to 9+13+12. Indexes are now 1 extent each
>
> Setup 2. Change FILLFACTOR to 50. Timings changed to 4+15+12
>
> Setup 3. Change BUFFERS to 10000. Timings were reduced to 5+6+6
>
> Setup 4. Change BUFFERS to 50000. Timings were reduced to 1+3+1
>
> Setup 5. Change BUFFERS to 100000. Timings were reduced to .5+2+1
>
> I also tried setting DIRECT_IO to 1, but that seemed to throw the compression
> phase into some sort of loop. I need to investigate that further.
>
> Looking at the log file during Setup 5 I spotted the following messages:
>
>
> -------------------------------------------------------------------------------
> 16:04:06 Logical Log 1818 Complete, timestamp: 0x857243ec.
> 16:04:09 Performance Advisory: Logical log file size might be too small for a
>
> checkpoint to complete.
> 16:04:09 Results: The size of individual logical log files is too small for
>
> the current workload, resulting in each log file filling very
>
> quickly. If log files fill in less than 30 seconds, the checkpoint
>
> might remain blocked because the last log file fills during the time
>
> needed to perform the checkpoint.
> 16:04:09 Action: Increase the size of the individual logical log files so
>
> that it takes at least 30 seconds to fill each one. Look at the
>
> online log to determine how quickly the log files are filling, and
>
> then increase the size of the files proportionately.
> 16:04:10 Logical Log 1819 Complete, timestamp: 0x85742388.
> 16:04:11 Performance Advisory: Based on the current workload, the physical log
> might be too small to
> accommodate the time it takes to flush the buffer pool.
> 16:04:11 Results: The server might block transactions during checkpoints.
> 16:04:11 Action: If transactions are blocked during the checkpoint, increase
> the size of the
> physical log to at least 154490 KB.
> 16:04:11 Performance Advisory: The physical log is too small for automatic
> checkpoints.
> 16:04:11 Results: Automatic checkpoints are disabled.
> 16:04:11 Action: To enable automatic checkpoints, increase the physical log to
> at least 154490 KB.
> 16:04:13 Logical Log 1820 Complete, timestamp: 0x857593b9.
> 16:04:17 Logical Log 1821 Complete, timestamp: 0x85774be2.
> 16:04:19 Performance Advisory: The physical log is running out of room during
> checkpoint processing.
> 16:04:19 Results: Transactions are being blocked until the checkpoint is
> complete.
> 16:04:19 Action: Increase the physical log size.
> 16:04:20 Checkpoint Completed: duration was 3 seconds.
> 16:04:20 Thu Jul 9 - loguniq 1822, logpos 0x6d415c, timestamp: 0x8578f312
> Interval: 12907
> ----------------------------------------------------------------------------
>
> I have logging turned off during the loading phases (1 and 3) but need it
> turned on for the compression phase (2).
>
> I will address the two advisories above and continue my testing.
>
> Thanks again for the advice,...
> Mike
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

Hi Mike,

when you want to create a dictionary with using a first
batch of rows, I guess you want to use this dictionary to
insert shortened rows with batch #2 ff.

Then I do not see a reason for FILLFACTOR < 100.
FILLFACTOR only leaves some room in index pages,
if not at 100. But it is valid for *all* indexes
of *all* databases in the IDS instance.
I consider it not a good idea to have indexes
active during load anyway. You must also carefully
think about BTSCANNER and the I/O you may create,
if not configured in an optimal way. Loading with
active index(es) always does a lot of index page splits.

Depending if you have VARCHAR datatype, and because
you are on IDS 11.50, you also might look in the AdminRef
docs pg 1-68: MAX_FILL_DATA_PAGES Configuration Parameter.
This can increase the number of rows fitting into 1 page.
It depends on the value of
maximum length defined in column having datatype VARCHAR
minus avg(length(column of datatype VARCHAR))

Maybe you should also consider if it might be an
advantage to use a bigger pagesize than default
and therefore also use another bufferpool instead
of the default bufferpool for this type of
processing. Else every load is a good candidate
to flood your buffers with data not used in OLTP,
but decreaing the cache rate of OLTP heavily.

As to the warnings you got in your online.log during
load: I already complained about this. Load is a
non-standard operation in a way, that it is normal
to switch llog pieces very fast.
This is the reason, why best way to load is w/o logging
This you can archive by staging: Bulk load into
a RAW table, then ALTER TABLE stagy_01 to TYPE ( standard )
and you will reduce write I/O to half and not flood
you llog. Of course YMMV and there are many reasons
to *not* switch of logging during load. This is
why I complained about the advisor, which really should
not throw such messages when a load opeartion is causing
the fast llog switches.
Maybe the clever advisor software could bring some
color into a grey DBA day using a message like:
'I see you are loading a buch of data and I see
that you switch your llog every 2 secs during this
load. Please don't do this while replicationg llogs to
a gazillion SDS servers and make sure, that you
logical log backup can cope with 6 GByte / sec.'
Or some such ;)
But then, my opinion about self healing and friends
is very well documented ....

dic_k
--
Richard Kofler
SOLID STATE EDV
Dienstleistungen GmbH
Vienna/Austria/Europe

Messages In This Thread

• Insert into an indexed table is really SLOW!
  MIKE DUNHAM-WILKIE -- Tuesday, 7 July 2009, at 1:06 p.m.
  • Re: Insert into an indexed table is really SLOW!
    Obnoxio The Clown -- Tuesday, 7 July 2009, at 1:32 p.m.
  • Re: Insert into an indexed table is really SLOW!
    Art Kagel -- Tuesday, 7 July 2009, at 1:42 p.m.
  • Re: Insert into an indexed table is really SLOW!
    RALPH GENTRY -- Tuesday, 7 July 2009, at 1:45 p.m.
  • Re: Insert into an indexed table is really SLOW!
    Art Kagel -- Tuesday, 7 July 2009, at 1:46 p.m.
    • Re: Insert into an indexed table is really SLOW!
      Richard Kofler -- Tuesday, 7 July 2009, at 6:44 p.m.
      • Re: Insert into an indexed table is really SLOW!
        Art Kagel -- Tuesday, 7 July 2009, at 7:09 p.m.
  • Re: Insert into an indexed table is really SLOW!
    MIKE DUNHAM-WILKIE -- Friday, 10 July 2009, at 11:02 a.m.
    • Re: Insert into an indexed table is really SLOW!
      Richard Kofler -- Friday, 10 July 2009, at 1:51 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.