IDS Forum

Re: Insert into an indexed table is really SLOW!

Posted By: MIKE DUNHAM-WILKIE
Date: Friday, 10 July 2009, at 11:02 a.m.

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

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

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.