Results 1 to 9 of 9

Thread: Intra_parallel

  1. #1
    Join Date
    Nov 2010
    Posts
    4

    Unanswered: Intra_parallel

    Hi,

    We have a Datwarehouse (around 8TB) built on 2 AIX LPARs, each with 5 partitions

    AIX 5.3
    DB2 V9.5 FP9
    LPAR1 - 7CPU, 36GB (hosts DB2 + Appl, hence extra 3 cores)
    LPAR2 - 4CPU, 36GB

    For some reason, the INTRA_PARALLEL parameter is turned OFF and DFT_QUERY_DEGREE is set to 1 (this system was designed long ago and not much history about this server is available now. we just know that it runs without any issues).

    Would there be a benefit in turning on INTRA_PARALLEL parameter here?
    How much should the DFT_DEGREE be set to?
    Do we need to fine-tune FCM parameters if this change has been made?

    Appreciate inputs on this topic.

    Manuv

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Is there a problem that you are trying to solve, or are you just curious about the meaning of those parameters? If it's the former, then you probably should describe the problem. If the latter, just read the manual.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Since DPF uses inter-partition parallelism, many DPF installations have intra-parallel partitioning turned off. This is what IBM recommends in their Balanced Warehouse configurations (at least the one I looked at a few years ago). It all depends on the number of CPU cores you have for each DPF partition (not the number of CPU's, but number of CPU cores).

    If you have more than one CPU core per partition, then there may be some benefit to having intra-partition parallelism turned on. If you have 2 CPU cores per partition, then you don't want DFT_QUERY_DEGREE set higher than 2, etc.

    However, one reason why many don't use intra-partition parallelism for DPF is that, since you pay for DB2 licenses by the number of CPU cores (multiplied by PVU value of a particular CPU model), then it often makes more sense to increase the number of DPF partitions, rather than using intra-partition parallelism (assuming you have multiple CPU cores per partition). Increasing the number of DPF partitions will provide better and more linear parallelism than using intra-partition parallelism.

    The other thing to consider is whether you have queries that are likely to take advantage of intra-parallelism. Obviously, if you are using table partitioning, and a single query typically accesses data in more than one table partition, that would qualify.

    In your original post, you mention the number of CPU's and partitions per server. I don't know if those are cores or sockets. If you don't have at least one CPU core per DPF partition, then your configuration is sub-optimal (seriously flawed).

    If you have at least several CPU cores per DPF partition, and don't think they are being fully utilized now, and don't want to increase the number of DPF partitions (not a trivial task for an existing database), then turning on intra-partition parallelism might make more practical sense than increasing the number of DPF partitions. Another option (if you have an excessive number of CPU cores per partition) is to disable some of the CU cores at the OS level, and use those DB2 CPU licenses on another configuration.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Nov 2010
    Posts
    4

    Performance improvement using INTRA_PARALLEL

    Thanks gurus for the replies.

    We are trying to reduce the runtime of a batch job that currently takes 17-20 hours. App team read some link on the web which mentions that INTRA_PARALLEL would bump up their performance.

    What they are not really aware is how much CPU (and licenses) they are currently paying for. At present we have
    LPAR1 - 7 Virtual CPU - 5 partitions
    LPAR2 - 4 Virtual CPU - 5 partitions

    It doesnt really look like we could turn on INTRA_PARALLEL in this case.

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by manuv17 View Post
    LPAR2 - 4 Virtual CPU - 5 partitions
    This is a flaw in your configuration. You need at least one CPU core per partition.

    Also, I suspect the database design and implementation was done by people who don't understand DPF, and as a result there are a lot of cross partition joins.
    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
    Nov 2010
    Posts
    4

    CPU cores

    Hi Marcus,

    This system was designed in V8 and was migrated to V9.1 -> V9.5 FP9

    I had a chat with another expert from IBM and he suggested that the even though the number of CPU is less, if each CPU is dual core (or quad core) then we might benefit from turning on INTRA_PARALLEL.

    We are going to run baseline tests with and without PARALLELISM. Will post with the results

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by manuv17 View Post
    Hi Marcus,

    This system was designed in V8 and was migrated to V9.1 -> V9.5 FP9

    I had a chat with another expert from IBM and he suggested that the even though the number of CPU is less, if each CPU is dual core (or quad core) then we might benefit from turning on INTRA_PARALLEL.

    We are going to run baseline tests with and without PARALLELISM. Will post with the results
    I mentioned before that it is not clear whether you are talking about CPU sockets or CPU cores. CPU sockets (or "CPU's") are irrelevant, and I told you that you need to find out exactly how many CPU cores are available for each DPF partition. Ask a AIX system admin for this info. If you can't find out this information, then you will never be able to make an intelligent decision.

    As I said before, if you have more than 1-2 CPU cores per partition, it is better to add more DPF partitions instead of enabling intra-parallelism. But that is a lot of work to do on an existing database.

    Whether it is V8, V9, or V10 makes no difference.
    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
    Nov 2010
    Posts
    4
    Quote Originally Posted by Marcus_A View Post
    This is a flaw in your configuration. You need at least one CPU core per partition.

    Also, I suspect the database design and implementation was done by people who don't understand DPF, and as a result there are a lot of cross partition joins.
    Marcus,

    We had config changes made on our LPAR.
    We went from Capped mode to Uncapped mode on AIX LPAR in order to take advantage of CPU's from the Shared Pool.

    On Capped mode, the licensing is on Entitled Capacity but in Uncapped mode the licensing is on Online Virtual CPU (lparstat output).

    Not really sure whether we got any advantage of using shared pool CPU's (runtimes have increased)

  9. #9
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by manuv17 View Post
    Marcus,

    We had config changes made on our LPAR.
    We went from Capped mode to Uncapped mode on AIX LPAR in order to take advantage of CPU's from the Shared Pool.

    On Capped mode, the licensing is on Entitled Capacity but in Uncapped mode the licensing is on Online Virtual CPU (lparstat output).

    Not really sure whether we got any advantage of using shared pool CPU's (runtimes have increased)
    DB2 licensing is by CPU core, not by physical CPU. You need to find out how many cores.

    You might be able to find out if you rename the db2diag.log file, and then a few minutes later get the beginning of the new db2diag.log file which shows how many CPU cores that DB2 sees.

    However, you should be able to get a AIX admin to tell you how many CORES are allocated to each LPAR, either dedicated or shared.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Tags for this Thread

Posting Permissions

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