Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Feb 2008
    Posts
    25

    Unanswered: How much main memory is allocated for a query

    Hi!
    As the thread title already says: how much RAM is normally allocated for a query?
    Futhermore it would be interessting to know, how much of this allocated RAM is used for a external sorting operator?

    Could you eventually give me perhaps a reference to the manual, where this point is described?

    Where can you change theses parameters in DB2?

    Thanks a lot for the effort to answer my question!

    Regards,
    Martin

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Have a look at snapshots. Those provide a wealth of information.

    To be more specific on your question: the answer is "it depends". The memory consumption depends on the amount of available memory. If you have less memory, you may earlier have to use an external sort. Also, with less memory, DB2 may choose a different access plan of different locking strategies.

    The real question is back to you: what exactly do you want to achieve with this information? I am asking because there are systems available where it is virtually impossible to track system resource usage at a query level because, for example, multiple queries may shared some resources and dividing things up will only give you wrong results whichever way you look at it.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Feb 2008
    Posts
    25
    Hi Stolze,
    I'm working on my diploma thesis at the moment. There, we want to compare the performance of two different database systems (DB2 and an own "laboratory development"). The tested queries all use an sorting operation at the end of the plan, and are not too complex (using a table scan / index scan, some selections, join, and case satements for the calculation of specific values).

    For a "good" comparsion of the plans, it is very important for me to know, how much memory is allocated for the sort operations of DB2. For our own database system, we can set a fix value for these parameters (used cache and used main memory for sorting). It the database systems use different values, I cannnot say something real specific about the performance.

    Is it possible to set a fix value in DB2, too?

    Regards,
    Martin

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    So you are only talking about the sort memory and not the whole main memory consumption for a query? In that case, you can configure the amount of sort heap memory that is available to DB2. Have a look at the SHEAPTHRSH (I'm not completely sure about the name) and related configuration parameters. The usage of the sort heap is explained there as well - it is not straight forward like "threashold = X -> 1 query can use up to X pages"; but the manual explains the details you need to know.

    If in doubt, just come back here ;-)
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Feb 2008
    Posts
    25
    Thanks for the reply!
    Your answer sounds pretty good :-)
    I'm going to have look at it in detail!
    Otherwise - as you wrote - I just come back

    Martin

  6. #6
    Join Date
    Feb 2008
    Posts
    25
    Hi!
    I just read through the manual!
    For me, it seems, that two parameters are really necessery for me: sortheap and sheapthres_shr.
    The parameter sortheap sets the maximum of pages used for sorting operations. sheapthres_shr sets the maximum for all operators using somekind of sorting (e.g. hashing, using of indices).

    If I get it right, i have just to manipulate the value of sortheap, to increase / decrease the maximum number of used pages using the sort-operator; is this right

    Regards,
    Martin

  7. #7
    Join Date
    Feb 2008
    Posts
    25
    Hi!
    Now, i've got a next problem!
    I started to change the parameters sortheap and sheapthres_shr to the minimum of their possible values (250 pages and 16 pages), and compared the performance of a plan, sorting 1.000.000 tuples, with the default values of sortheap and sheapthres_shr (something at 10000 pages).
    Both had the same performance, which is a little confusing, because the performance test with the small values should be slower!

    Setting the values in the configuration dialogue, I've got the following problem: after setting the value, I try do deactivate the field "update dynamically" (I hope the name of the field is in the english version something like that). But eacht time, I open the dialogue window again, the field is activated, which probably means, that DB2 changes the set value on a dynamical way to a much higher value, because a lot more heap space is free!


    Why can't i simple deactivate this field? Is that the source of my problem?

    Regards,
    Martin

  8. #8
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You are using Control Center? I would recommend that you stick with the command line tools and the UPDATE DB CFG command. Besides being less troublesome, it has the advantage that you can easily script your things and, thus, make them reproducable.

    Besides that, I usually recommend the English version of DB2 because at least the German translation uses some terms that no one knowns what they mean (example: "DB2 Exemplar" vs. "DB2 Instanz").

    p.s: I guess the "update dynamically" refers to DB2's autonomic features. You may want to make sure that STMM (automatic memory management) is turned off.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  9. #9
    Join Date
    Feb 2008
    Posts
    25
    Hi!
    Thanks for the answer! Although deactivating the parameter self_tuning_mem_off (and working with the command line tool) the problem still exists . Furthermore, I had again a look at the condfiguration dialogue. There, all my parameters are still all marked as "dynamic"....

    Eventually it is the best, if I show you, what I have done:
    After starting the cmd it wrote followin:
    Code:
    update db cfg for test using self_tuning_mem off
    update db cfg for test using sheapthres_shr manual
    update db cfg for test using sheapthres_shr 250
    update db cfg for test using sortheap manual
    update db cfg for test using sortheap 16
    after that i stopped and startet the test database again, and startet my testquery:
    SELECT * FROM TEST.xxx order by a;
    on tables with 300.000, 600.000 and 1.000.000 tuples with the following results:
    300000 Tuples
    * Zeit (arithmetisches Mittel): 0,853129 Sekunden
    * Zeit (geometrisches Mittel): 0,853123 Sekunden

    600000 Tuples
    * Zeit (arithmetisches Mittel): 1,714625 Sekunden
    * Zeit (geometrisches Mittel): 1,713980 Sekunden

    1000000 Tuples
    * Zeit (arithmetisches Mittel): 3,036803 Sekunden
    * Zeit (geometrisches Mittel): 3,035570 Sekunden


    After that, I did the same again, but with following values:
    Code:
    update db cfg for test using self_tuning_mem off
    update db cfg for test using sheapthres_shr manual
    update db cfg for test using sheapthres_shr 250
    update db cfg for test using sortheap manual
    update db cfg for test using sortheap 16
    Running the tests again resulted in following times:
    300000 Tuples
    * Zeit (arithmetisches Mittel): 0,894321 Sekunden
    * Zeit (geometrisches Mittel): 0,888789 Sekunden

    600000 Tuples
    * Zeit (arithmetisches Mittel): 1,871847 Sekunden
    * Zeit (geometrisches Mittel): 1,861351 Sekunden

    1000000 Tuples
    * Zeit (arithmetisches Mittel): 3,112691 Sekunden
    * Zeit (geometrisches Mittel): 3,096004 Sekunden


    As you can see on the results, nothing has changed....but normally, it should have, because I'm sure, that 1.000.000 tuples don't fit in 16 allocated pages in main memory, and therefor have to be stored on the external hard disk.

    Hope you / someone can help me!

    Regards,
    Martin
    Last edited by SilencerandLois; 06-03-08 at 10:49.

  10. #10
    Join Date
    Feb 2008
    Posts
    25
    Perhaps this is also important (but I don't think so...).
    How am I doing my tests?
    I've got a file, where my queries are included:
    Code:
    -- Test: 61 repetitions 
     --#SET ROWS_OUT 0
    
    SELECT * FROM TEST.X1EQ order by a;
    
    SELECT * FROM TEST.X1EQ order by a;
    
    SELECT * FROM TEST.X1EQ order by a;
    .
    .
    .
    And I start the file with
    Code:
    db2batch -d test -f Sorting.db2batch > Sorting.txt

  11. #11
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by SilencerandLois

    As you can see on the results, nothing has changed....but normally, it should have, because I'm sure, that 1.000.000 tuples don't fit in 16 allocated pages in main memory, and therefor have to be stored on the external hard disk.
    Looks like you are forgetting about the DB2 bufferpools and the OS disk cache.
    ---
    "It does not work" is not a valid problem statement.

  12. #12
    Join Date
    Feb 2008
    Posts
    25
    Hi!
    Hm... can be.
    I thought, that, when setting the parameters as I've done, sorting isn't done anymore in main memory, but on the external storage device.
    That's what I want DB2 to do

    Any hints...?
    So far as I know, i can the OS disk cache in the system settings of windows xp, right?
    But what about the DB2 bufferpools?

    Regards,
    Martin
    Last edited by SilencerandLois; 06-03-08 at 15:05.

  13. #13
    Join Date
    Feb 2008
    Posts
    25
    Hi!
    Now, I changed the bufferpool ibmdefaultbp with the effect I wanted to have!
    Thanks a lot for the advices!!

    But I have still one littke question: Do sheapthres_shr and sortheap share the pages of the bufferpool, or are they all independent?

    Regards,
    Martin

  14. #14
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Why don't you read the manual - it's all in there in great detail.
    ---
    "It does not work" is not a valid problem statement.

  15. #15
    Join Date
    Feb 2008
    Posts
    25
    Well, so far as I understand it, is the bufferpool separate to sortheap sheapthres_shr. However sheapthres_shr and sortheap are optimized together and belong together.

    Sorting works like that ( as I imgainge): e.g. sortheap has value of 100. That means, you have 100 pages for sorting in main memory. If more pages are needed, they have to be stored somewhere. First of all, they are stored in the bufferpool, if there is enough space left. Otherwise, on the external storage device...

    But I am / was not quite sure about that; therefore the question So, please correct me, if I am wrong.

    Regards,
    Martin
    Last edited by SilencerandLois; 06-04-08 at 13:13.

Posting Permissions

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