Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2004
    Posts
    60

    Question Unanswered: One of many tables suffers during checkpoint

    IDS 7.31 UD4 for AIX, HDR.

    We have two databases, one with about 120 tables and one with 2 tables. The second database containing two tables has one table which on average gets 10 rows inserted into it per second.

    Our problem is performance of these inserts is occasionally poor during checkpoints just for this table. Other tables in the first database can be hit with the same number of inserts, and a lot of reads, and our debug shows they are performing just fine (we are logging insert times for all table hits).

    Ironically the poorly performing table has 3 extents of 1Gb a piece, the tables performing well have fragmentation and in one case over 70 extents.

    I am thinking of trying to attack the checkpoints by reducing BUFFERS and playing with the LRU queues/CLEANERS - checkpoints are currently running at about 10 seconds - but if checkpoints are the problem why does the fragemented table out perform the 'contiguous' table?

  2. #2
    Join Date
    May 2004
    Location
    New York
    Posts
    248
    If you can try fragmenting the tabler across multipule disks (just a suggestion)

  3. #3
    Join Date
    May 2004
    Posts
    45
    At checkpoint time dirty pages in the lru queues are written back to the chunk they belong to, each chunk is given a single page cleaner to do the writing. Without doing a re-org of your dbspace/chunks, reducing the LRU max/min values to clean between checkpoints (where chunk number assignments are not relevant) would be a good strategy.

    If you are using later versions of 9.x you could also try 'fuzzy checkpoints'. Otherwise a disk re-org to chop up the 1 gig chunks into something smaller so more page cleaners can participate in the checkpointing. You won't lose anything in making multiple chunks as long as they are contigious to each other.
    Keith Brownlow
    ServerMetrics DB Monitoring (www.servermetrics.com)

Posting Permissions

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