DB2 - L

 View Only
  • 1.  SEGSIZE - impact on read performance

    Posted May 05, 2022 03:28 AM
    Hi,

    lately I read some articles about the SEGSIZE parameter for UTS tablespaces. Also that for example this parameter can have an impact on the INSERT performance. But now I'm wondering if SEGSIZE has also an impact on the read performance... Has it?

    Is there for example a restriction that the prefetch can only read SEGSIZE pages?
    Or are there other restrictions with regard to the read performance?

    ------------------------------
    WolfgangBeikircherRaiffeisen Information Service
    ------------------------------


  • 2.  RE: SEGSIZE - impact on read performance

    Posted May 06, 2022 06:24 AM
    Hi!

    Prefetch has nothing to do with SEGSIZE but everything to do with Bufferpool size... here's a little cut and paste from the performance Guide:

    Prefetch is a mechanism for reading a set of pages, usually 32, into the buffer pool with only one asynchronous I/O operation.

    size Number of buffers Pages Read by Sequential and LOB List Prefetch Pages Read by Dynamic and Non-LOB list Prefetch Pages Read by Utility sequential Prefetch
    4 KB VPSIZE < 224 8 8 16
    225 < VPSIZE <1,000 16 16 32
    1000 <= VPSIZE < 40,000
    or
    VPSIZE*VPSEQT < 40000 32 32 64
    40,000 <= VPSIZE*VPSEQT <
    80,000 64 32 64
    80,000 <= VPSIZE*VPSEQT 64 32 128
    Etc.
    Etc.

    I am pretty sure that listserv will mangle the above table but it is all documented there.

    Read performance is one of the reasons for segmenting in the first place but not so much these days with UTSs. Still good for mass deletes though!


    Roy Boxwell

    SOFTWARE ENGINEERING GmbH and SEGUS Inc.
    -Product Development-



    Vagedesstrasse 19
    40479 Dusseldorf/Germany
    Tel. +49 (0)211 96149-675
    Fax +49 (0)211 96149-32
    Email: R.Boxwell@seg.de
    Web http://www.seg.de
    Link zur Datenschutzerklärung

    Software Engineering GmbH
    Amtsgericht Düsseldorf, HRB 37894
    Geschäftsführung: Gerhard Schubert, Ulf Heinrich