1

I need help regarding LOAD-command with BLOBs.

I notice very poor performance while loading a table which contains large BLOBs. While tracing the OS, I only have about 15MB/sec I/O-speed. But in other cases I have normal I/O-rates > 200MB/sec. (e.g. creating DB-backups, creating tablespaces, copy files etc.).

I already tried to use different extent-sizes, page-sizes and some LOAD-parameters like "DISK_PARALLELISM", "SORT BUFFER", "DATA BUFFER", "SAVECOUNT" etc. to improve LOAD-performance but I was not able to speed up the LOAD. The target table is empty and has no indices defined. Source and target are located on separate RAID-volumes with dedicated physical drives.

Any hints to improve LOAD performance or to further investigate the problem are very welcome.

Thank you in advance.

Ronny

My setup:
- SLES 11 SP2
- DB2 9.7 FP8
- Raid: 
    Source: 2 dedicated Disks, Raid-0 (Striping) with 64 KB stripe-size
    Target: 2 dedicated Disks, Raid-0 (Striping) with 64 KB stripe-size
- File-System: EXT3
- Tablespace-Definition:
    CREATE LARGE TABLESPACE LOB IN DATABASE PARTITION GROUP IBMDEFAULTGROUP PAGESIZE 4096 MANAGED BY DATABASE USING (FILE 'xxx' 20000000) PREFETCHSIZE AUTOMATIC BUFFERPOOL IBMDEFAULTBP DROPPED TABLE RECOVERY OFF;

- LOAD-Definition:
    LOAD FROM xxx OF IXF LOBS FROM xxx MODIFIED BY lobsinfile METHOD N (ID, CONTENT, SEGMENTGROUPID, SEGMENTNUMBER) SAVECOUNT 1 ROWCOUNT 250 MESSAGES /xxx/CONTENT.log REPLACE INTO CONTENT (ID, CONTENT, SEGMENTGROUPID, SEGMENTNUMBER) NONRECOVERABLE;
| improve this question | |
  • 1
    So, you have a single logical volume that you're both reading and writing during the load? May be you should consider a separate device for the input data. Consider also creating a separate tablespace for LOBS with filesystem caching and create the data tablespace without filesystem caching. – mustaccio Jun 30 '14 at 14:31
  • Thank you for your hints. Source and target data resides on separate RAID-volumes. I use separate tablespaces for DATA and LOB Data. I will check the filesystem-chahing-options. – Ronny Jun 30 '14 at 14:36
  • Separate RAID volumes still sharing the same physical drives, I presume? – mustaccio Jun 30 '14 at 15:36
  • No, I use 4 HDDs altogeter; each raid-Volume uses 2 dedicated physical discs. – Ronny Jun 30 '14 at 15:55
  • Then it makes sense, of course, to put a completely different configuration in your question. – mustaccio Jun 30 '14 at 16:22
1

As Chris already mentioned LOAD is a utility and therefore the UTIL_HEAP_SZ must be set high enough (but don't ask too much, always monitor the memory free space with top). You need a lot of memory for a fast load (see DATA BUFFER below).

Here are things that speed up the load:

  1. More containers for the tablespace where the table is (best on separate luns / disks)
  2. DATA BUFFER parameter (default 25% of UTIL_HEAP_SZ), make it bigger 400-800 (4K) per CPU core available.
  3. ANYORDER (allows LOAD to abandon the input order, bad if you load in clustering order) can give 30% boost
  4. FASTPARSE (use only if data is clean e.g. unloaded from other table) can give 15% boost
  5. SAVECOUNT - the higher the better I use #input records/10, so for 100M records the savecount is 10000000 - in your case 1 the LOAD has to update the restart data with every record = SLOW!

Note: always collect statistics with STATISTICS YES during load. It is faster than LOAD + RUNSTATS

Good luck and let us know your improvements!

| improve this answer | |
  • Thank You for your advices and the concrete expected effects - valuable information. But I already tried to improve performance using some of these parameters - without any countable effect in my scenario (lesser but much bigger entries due to BLOB columns). But in the meantime I discovered a solution, which speeds up the LOAD-performnce 8 times! I changed the pagesize of the affected LOB-tablespace to 32KB. I suppose, my problem is based in missing parallelism during loading BLOBs... For my scenario, the load performance is sufficient now. Thanks for all hints and information. – Ronny Jul 4 '14 at 10:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.