Extra-long wait in merge phase

[Former Member]

When running INSERT INTO <table> <select statement> in 10 min intervals to aggregate data from temporary tables and insert it to main datastore. All goes perfectly, but after 4-5 hours we observe that insert runs 10-100x times slower. In iqmsg logs we see following:

for 'long' insert:

I. 05/20 07:50:05. 0000021624 Load phase completed

I. 05/20 07:50:05. 0000021624 Merge in progress: 45%

I. 05/20 07:50:05. 0000021624 Merge in progress: 67%

I. 05/20 07:50:05. 0000021624 Merge in progress: 90%

I. 05/20 08:46:11. 0000021624 Merge in progress: 100%

I. 05/20 08:46:11. 0000021624 Merge phase completed

I. 05/20 08:46:11. 0000021624 [20896]: Insert for 'natflow_20_05_2016' completed in 3422 seconds. 9024346 rows inserted.

for 'normal' insert:

I. 05/20 09:10:16. 0000000133 Load phase completed

I. 05/20 09:10:16. 0000000133 Merge in progress: 45%

I. 05/20 09:10:16. 0000000133 Merge in progress: 67%

I. 05/20 09:10:16. 0000000133 Merge in progress: 90%

I. 05/20 09:10:25. 0000000133 Merge in progress: 100%

I. 05/20 09:10:25. 0000000133 Merge phase completed

I. 05/20 09:10:25. 0000000133 [20896]: Insert for 'natflow_20_05_2016' completed in 23 seconds.  18966510 rows inserted.

no other activity in database, only inserts. After restarting SAP IQ all goes to normal until next 5 hours.

[c_baker · Accepted solution]

Questions:

What are your cache configurations (-iqlm, -iqtc, -iqmc)?

How many main and temp devices do you have?  RLV space?  Are they all on the same disk?
Are you using RLV and is it enabled for this table?  If you are using RLV, what is the -iqrlvmem cache size?  Do you really need RLV (i.e. is there some insert concurrency on this table that needs RLV?

In a 'normal' process, does the table start empty?  Does it have a primary key?

We need more details

Chris

[Former Member · Accepted solution]

server version:

' Version:','16.0.0.809/150721/P/sp08.35/MS/Windows 2003/64bit/2015-07-21 14:57:13'

server start string:

start_iq.exe -ch 20g -gd DBA -gl DBA -ti 5 -tl 3600 -iqmc 21000 -iqtc 26000 -iqlm 8000 -gc 60 -gm 50 -gn 75 -iqnumbercpus 12 -iqmsgsz 100 -iqmsgnum 10 -gb high D:\\DB\\flow

I have one main space and one temp dbspace on one device (RAID1) - it is relatively small: 500Gb

All data goes to separate user dpspace which is on DAS, RAID50, 50Tb.

I also use RAID1 SSD as cache, no any rlv configured and used (I've tried but it ends with stacktraces).

In a normal process table is empty on start of day (00:00:00) and on each 10min period it filled with 5-50m of rows. No any primary keys on table.

database options:

SET OPTION PUBLIC.DEFAULT_DBSPACE='USER_DBSPACE';

SET OPTION PUBLIC.FORCE_NO_SCROLL_CURSORS = 'ON';

SET OPTION PUBLIC.APPEND_LOAD = 'ON';

SET OPTION PUBLIC.MAX_QUERY_TIME = 120;

SET OPTION PUBLIC.INDEX_ADVISOR='ON';

a bit more details on setup:

on beginning of each run data is inserted into two temporary tables using load table #<table name>.

after that goes insert into which aggregates data from those to final table.

generated plans from all queries during load/aggregate/insert into attached

DBA_L_0__20160523-145203_5261.html - Google Drive

DBA_L_0__20160523-145315_5264.html - Google Drive

DBA_L_0__20160523-145315_5267.html - Google Drive

DBA_L_0__20160523-145316_5269.html - Google Drive

DBA_L_0__20160523-145336_5270.html - Google Drive

Here's plan from extra-long INSERT INTO. As wee see all time was spent in parallel combiner.

DBA_L_0__20160526-041954_9232.html - Google Drive

Any clues?

[tayeb_hadjou · Accepted solution]

Have you tested with option CORE_Options71 = 0 ?

[Former Member · Accepted solution]

Yes. Attached plans. Last one is mega extra-long.

DBA_L_0__20160527-170422_213.html - Google Drive

DBA_L_0__20160527-175126_225.html - Google Drive

Also attached CPU/disk graphs during parallel combiner.

CPU activity.png - Google Drive

disk activity.png - Google Drive

[Former Member · Accepted solution]

At the end problem was solved by removing hash partioning from destination table.

[tayeb_hadjou · Accepted solution]

Thank you for sharing your solution!

[tayeb_hadjou]

Hi,

What is the exact IQ version (select @@version)?

In next insert test, I would suggest try option below before executing the insert and see if it helps:

-- check current setting using sp_iqcheckoptions ;

-- Change option in current SQL user session

set TEMPORARY OPTION  CORE_Options71 = 0  ;

-- check new setting using sp_iqcheckoptions ;

-- execute the insert

Insert into ... ;

Regards,

Tayeb

[former_member333181]

Hi Oleg,

   Can you please check the iostat/vmstat during NORMAL and this SLOW period? Many times in my life as support architect, I have seen that this points to disks getting flooded with I/O requests and then they cannot handle those in timely manner. That is first area to check, we also need html plans form IQ during such insert into Queries to look at , set following options

set temporary option query_plan = 'ON';

set temporary option query_detail = 'ON';

set temporary option query_plan_as_html ='ON' ;

set temporary option query_plan_after_run='on';

set temporary option query_timing='on';

set temporary option DML_OPTIONS10='on';

set temporary option FORCE_NO_SCROLL_CURSORS ='on' ;

set temporary option index_advisor='on' ;