Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2013
    Posts
    20

    Red face Unanswered: During tablespace expansion, DB2 performance degrades

    Hello,


    I am using db2 9.7 FP8 on Linux. I have a 1.5TB database with multiple big (~100-200 GB) tablespaces, and whenever the tablespace fills up, it is automatically extended by DB2. However, during this expansion, database seems to be unresponsive. All connections hang, I cannot get into db2top until the tablespace containers are expanded. After the expansion, everything works ok, all connections are getting through etc.. To reduce the impact, I have changed the tablespace increase size from 5% to 1GB, so that tablespace containers are increased by a constant, relatively small amount rather than 5%, which can be 10+ GB.

    Is this normal for DB2 to freeze during tablespace expansion?
    I wish I could force db2 to expand the containers during a less intensive time, but since all the tablespaces are using Automatic Storage, it's not possible.. And this freeze is impacting applications that are working on the database.

    Should I think about opening PMR?


    Kind Regards,

    Tomas

  2. #2
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Let me guess: ext3?
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

  3. #3
    Join Date
    Jan 2013
    Posts
    20
    Hi dr_te_z,

    Yes, we are using ext3. I guess it wasn't a wild guess here?

    Regards,

    Tomas

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Extending tablespace containers is a synchronous operation. If you anticipate a period of growth you might as well preallocate enough space ahead of time during the period of low activity.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Jan 2013
    Posts
    20
    Hi n_i,


    I haven't found any way to do that when using Automatic Storage and I would not want to go back to DMS as it's said to be deprecated in later db2 releases.

    If you know how to pre-extend tablespace storage in a controlled way - please let me know.


    Thanks,

    Tomas

  6. #6
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by tomas. View Post
    Hi dr_te_z,

    Yes, we are using ext3. I guess it wasn't a wild guess here?
    No: http://www.dbforums.com/db2/1698962-...w-sp-only.html (There is another link in there in my post)

    I'd go for XFS or JFS for my tablespaces. But for you it has less impact to convert to ext4.
    Start by buying you linux-admin lunch.... you are going to need him/her

    Coming soon: http://www.internetnews.com/ent-news...ilesystem.html
    Last edited by dr_te_z; 01-28-14 at 09:07. Reason: added link to redhat7 with XFS
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

  7. #7
    Join Date
    Jan 2013
    Posts
    20
    Quote Originally Posted by dr_te_z View Post
    No: http://www.dbforums.com/db2/1698962-...w-sp-only.html (There is another link in there in my post)

    I'd go for XFS or JFS for my tablespaces. But for you it has less impact to convert to ext4.
    Start by buying you linux-admin lunch.... you are going to need him/her

    Coming soon: Red Hat Enterprise Linux 7 Beta Uses XFS as Default Filesystem - InternetNews.
    dr_te_z, many thanks for the information! The link to IBM technote was the answer I was looking for I will now have to take this information to our linux admins..

    Thanks and Regards,

    Tomas

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by tomas. View Post
    I haven't found any way to do that when using Automatic Storage and I would not want to go back to DMS as it's said to be deprecated in later db2 releases.
    You're right, I missed that part.
    ---
    "It does not work" is not a valid problem statement.

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
  •