Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2012
    Posts
    120

    Unanswered: performance when using LOBs

    Hi all,
    I'm using DB2 10.1 ESE on Windows.

    I'm having very poor performances with a select with a join on a table with a BLOB column.
    The column is defined as BLOB(2G) and contains text in binary string that is retrieved and parsed by a function.
    The select is

    select P.uid, CAST( fn_parse( 'any string', P.Data ) AS integer) AS outID
    from DATAT P, LOCATIONS V, SUPERSID S
    where P.uid = V.uid
    and CAST( fn_parse( 'any string', P.Data ) AS bigint) = S.buid
    and fn_parse( 'any string', P.Data ) IS NOT NULL

    In a unit test I created another table DATAT1 where the Data column is varchar(8000) for bit data and rewrote fn_parse to accept varchar for bit data instead of BLOB(2G). I used a subset of real data for the unit test and the result was a great performance improvement.

    I cannot use varchar for bit data in production since I have fields that can be very large and cannot be accomodated in a varchar column.

    Could you please suggest the best strategy to solve this issue?
    How can I optimize performance for a select on LOBs?

    Many thanks

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    If I understand you correctly, you are executing a UDF, probably even twice, that parses a BLOB value that can be up to 2 GB in size, for each row in the table DATAT. In this case your only options are optimizing the UDF and trying to improve the physical I/O performance of your storage.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by n_i View Post
    If I understand you correctly, you are executing a UDF, probably even twice, that parses a BLOB value that can be up to 2 GB in size, for each row in the table DATAT. In this case your only options are optimizing the UDF and trying to improve the physical I/O performance of your storage.
    In addition, the last predicate is redundant. Removing it might help.

  4. #4
    Join Date
    Mar 2012
    Posts
    120
    I'm using only one bufferpool (the default one) and one tablespace.
    Can I have performance improvements by creating one tablespace only for LOBs? In that case, how should I configure it?

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    LOBs should be in their own tablespace (specified by LARGE keyword when table is created). It should have FILE CACHING turned on (unlike other DMS tablespaces).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Mar 2012
    Posts
    120
    Thanks Marcus,
    so you suggest to create a DMS tablespace for LOBs right?

    Any other suggestion on tablespace characteristics?

    Do you think I should create a dedicated bufferpool also?

    Thanks!

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Yes, create a DMS tablespace for LOB's, but if you use automatic storage, it will automatically choose DMS (unless it is System Temporary Tablespace). Jus tmake sure it has FILE CACHING turned ON, or alter the tablespace to turn it on (only for LOB tablespaces).

    You should make the LOB tablespace 32K pagesize so you will need a 32K bufferpool. However, LOB columns don't use bufferpools, which is why they are so slow, and why you want file system caching turned on. Also look at INLINE specification for the column definition (see SQL Reference Vol II for description of this keyword).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  8. #8
    Join Date
    Apr 2012
    Posts
    1,034
    Provided Answers: 18
    Study the docs.

    Depending on your setup, you may gain a bit (but not a lot) by ensuring the LOB column(s) are in a dedicated tablespace, and if you configure that LOB tablespace to *USE* file-system-caching. The idea is to persuade Windows to cache the lobs (because DB2 will not put 2GB lobs into bufferpools).

    At the physical layer, ensure that the Windows filesystem on which you place the LOBS is on fastest storage the budget allows, and that sufficient RAM is left over (after DB2 has maxed its bufferpools) for Windows to cache that filesystem on which you put the lobs.

    An RDBMS is not always the correct tool for the job...

  9. #9
    Join Date
    Apr 2012
    Posts
    156
    Another thing to look at is using inline lobs. Rather than every lob being stored externally from the row, DB2 covnerts the LOB's below a certain threshold (you can set or allow DB2) to store them with the data row. This allows the inline lob to be compressed and and buffered like normal data. Of course if all of your lob data is over the threshold this will not help. What I have found on most of my systems is a large percentage of the LOB data is really only around 1 k or so. Look at the following for more info on this feature:
    IBM DB2 9.7 Information Center for Linux, UNIX, and Windows

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •