Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2016
    Posts
    4

    Unanswered: db2 luw import issue: windows vs. linux

    Hi experts,

    I have exported a file from a table, and try to load it to another table using IMPORT utility on db2 luw 10.5 platform linux Centos 6.7.
    the average speed of import is approximately 20000 Row per Second (best result 17 million records in 13.5 minute).
    by changing the platform to windows total time reduce to less than 6 minute (approximately 55000 Row per Second)!!
    though my linux reside on a server: with 16 cores and 128G memory and 2 RAID-1 SSD disk drive (total 4 physical disks); But my Windows reside on my PC: with 8 core and 8G memory and 1 disk drive!
    I've tested same senario on linux SUSE and still poor performance exists!!
    I've checked all db & dbm parameters and there seems to be no diffrence between windows and linux platforms.
    my resources on linux (cpu, memory, and disk I/O) are not bottleneck (using monitoring tools: db2top, spotlight, ....).
    I've also set linux kernel parameters based on IBM recomandation.

    my question is why it behave like this and how can I find the bottleneck on my linux platform?

    thanks

  2. #2
    Join Date
    Apr 2012
    Posts
    1,143
    Provided Answers: 27
    Note that the IBM paid support channel for DB2 V10.5 and V11.1 won't support Centos yet.

    There are not enough facts in your post, no exact recreation scenario showing all the details of the I/O subsystem(s) and logging config, locales and DB2-registry settings.

    Are source and target on the same file-systems in both cases (i.e. same SSD/spinning-disk(s))?

    You might try measuring the time taken to copy the exported-file from its present location to the file-system(s) containing the tablespaces (e.g. just using time cp .... ), and comparing that to the equivalent timing for Windows. If those copy times are similar then it suggests the issue is with DB2. If the times are significantly different, the issue is not with DB2.

    Additionally, compare the same copy time if the copy-target is the file-system(s) containing the active-transaction-logs of DB2 on both operating-systems.

    Possible that DB2 on Linux is waiting for I/O, and/or waiting for logs, or doing code-page translation un-necessarily on the import.

  3. #3
    Join Date
    Dec 2016
    Posts
    4
    Quote Originally Posted by db2mor View Post
    Note that the IBM paid support channel for DB2 V10.5 and V11.1 won't support Centos yet. ....
    thanks
    I know that IBM won't support Centos, that's why I tried SUSE on the same server.
    in all scenarios the file which I import from exists on local machine.

    about the disk my server has 4*10K rpm disks (2 RAID 1), and in my PC I've got a single 7200 rpm disk.
    my file system in PC windows is NTFS
    my file system in linux is ext3; I've disabled journaling, increase file system page size to 8k. it got a little bit better but still far away from windows experience.

    to measure time spend I run theses commands from a file:
    select current timestamp from sysibm.sysdummy1;
    import from ....;
    select current timestamp from sysibm.sysdummy1 ;

    I've also tried not logged initailly option, but still poor performance exist.
    the only ambigious this is code-page translation.
    both databases are UTF-8 and territory US.
    how can I be sure about the un-necessarily code-page translation??

  4. #4
    Join Date
    Apr 2012
    Posts
    1,143
    Provided Answers: 27
    Have you measured both systems raw copy performance has already suggested, to eliminate DB2 ?
    Is the Linux virtualized, but the Windows is physical?

  5. #5
    Join Date
    Dec 2016
    Posts
    4
    Quote Originally Posted by db2mor View Post
    Have you measured both systems raw copy performance has already suggested, to eliminate DB2 ?
    Is the Linux virtualized, but the Windows is physical?
    yes, I've done that too, raw copy took about 2 minute; even load command in both linux and windows take same amount of time (about 2.5 minute).
    both linux and windows are physical.

  6. #6
    Join Date
    Dec 2016
    Posts
    4
    Quote Originally Posted by alireza_AB View Post
    yes, I've done that too, raw copy took about 2 minute; even load command in both linux and windows take same amount of time (about 2.5 minute).
    both linux and windows are physical.
    I've even test insert into:
    "create tbl2 like tbl1 in ts1....
    insert into tbl2 select * from tbl1"

    in this scenario, it inserted 17 million records in about 2.5 minute in both platforms linux and windows.

  7. #7
    Join Date
    Apr 2012
    Posts
    1,143
    Provided Answers: 27
    Verify you are comparing like with like, including concurrency aspects.

    Verify the database DDL is identical between Linux and Windows as regards table DDL, indexes, any enforced RI-constraints or other column check contstraints, any triggers, expression-based indexes, user defined functions, MQTs etc.

    You can investigate further with DB2 monitoring functionality, to compare metrics between Linux and Windows to reach an understanding of what's happening. There are many levels of detail, but monreport.db_summary might help. You can compare CPU-utilization, bufferpool-utilization , log-file-utilization, locking behaviour, and average write-times and many other aspects between the two operating systems during the import action.

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
  •