Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2003
    Posts
    23

    Question Unanswered: Cache Search Misses

    Hi

    The second time I run a query I would expect it to run quick because the table stays in memory. My sybase server appears to not be caching the query results or table. My server is doing physical reads from the disk every time it runs the query where it should only be doing logical reads. sp_sysmon "00:02:00" shows Cache Search Misses as 75% of total context switches. Does anybody have any idea why my server might not be caching correctly? (not using a named cache, default cache is around 3GB)

    -UNIX Solaris 8, 4cpu, 8GB RAM
    -Sybase 12.0.07
    -Raw Devices
    -Total memory - 3.5GB

  2. #2
    Join Date
    Sep 2004
    Location
    Stockholm, Sweden
    Posts
    28
    Not sure why your hot ratio is low but, remember that your defult cache is used for everything, table data, index pages, system tables etc.

    In some situations the optimizer use the MRU policy which means that the pages are not in cache the next time you run the query.

    Can you describe how your query look like and how the default cache is configured?

    /Mats

  3. #3
    Join Date
    Dec 2003
    Posts
    23
    Thanks for responding Matssve. Here is the cache breakdown:



    ------------ ------------
    Total 30.00 Mb 2901.61 Mb
    ================================================== ========================
    Cache: dbcc_cache, Status: Active, Type: Mixed
    Config Size: 30.00 Mb, Run Size: 30.00 Mb
    Config Replacement: strict LRU, Run Replacement: strict LRU
    Config Partition: 1, Run Partition: 1
    ================================================== ========================
    Cache: default data cache, Status: Active, Type: Default
    Config Size: 0.00 Mb, Run Size: 2871.61 Mb
    Config Replacement: strict LRU, Run Replacement: strict LRU
    Config Partition: 1, Run Partition: 1
    (2 rows affected)
    (2 rows affected)
    (3 rows affected)
    (return status = 0)


    Here is the query:

    select count(*) from ts_order where create_date > "10/25/04"
    go



    All the databases are set to use the default data cache except for dbccdb, which uses the dbcc_cache.

    -Tony

  4. #4
    Join Date
    Dec 2003
    Posts
    23
    Some more info....

    DDL of caches:


    sp_cacheconfig dbcc_cache, "30720K", "mixed"
    sp_poolconfig "dbcc_cache", "2K", "wash=614K"
    sp_poolconfig "dbcc_cache", "27648K", "16K", "2K"
    sp_poolconfig "dbcc_cache", "16K", "wash=5520K"
    sp_poolconfig "default data cache", "2K", "wash=61440K"
    sp_poolconfig "default data cache", "102400K", "4K", "2K"
    sp_poolconfig "default data cache", "4K", "wash=20480K"
    sp_poolconfig "default data cache", "102400K", "16K", "2K"
    sp_poolconfig "default data cache", "16K", "wash=20480K"

  5. #5
    Join Date
    Sep 2004
    Location
    Stockholm, Sweden
    Posts
    28
    Hi again,
    The Sybase cache policys (strict LRU and relaxed LRU (or clock)) works togehter with the MRU policy. Which one to use is decided by the optimizer, i.e. you chose strict or relaxed, but the MRU policy is always available.

    The (strict) LRU policy is implemented as a page chain with one MRU-end (Most Recently Used) and on LRU-end (Lest Recently Used). Somewhere near the LRU-end is a wash marker, where dirty pages are written to disk and/or the will soon leave the cache. When a page is taken into cache by the MRU policy, the page is placed at the wash marker.

    When you perform a range query, the MRU policy is chosen by the optimizer and the pages brought in to cache is very likely to be aged out from the cache. This means that they will not be in cache the next time you run the query.

    possible solutions:
    *) force the optimizer to use the LRU policy. Not sure if this is possible. If it is, it may result in lower cache hits for other queries.
    *) Make the wash section larger to prevent pages from aging out. This may be useless (or inefficient) if your table is large.
    *) Create a special table to hold the number of records and update it through triggers on your ts_order query.

    /Mats

  6. #6
    Join Date
    Sep 2004
    Location
    Stockholm, Sweden
    Posts
    28
    By the way, do you have an index on the attribute create_date ??
    If you have one, the query should not have to access the table data, only the index. You can then bind the index to a own named cache to make the use of it even more efficient.

    /Mats

  7. #7
    Join Date
    Dec 2003
    Posts
    23
    Thanks for the info matssve. I've been on a long vacation. Sucks to be back. Hehehe.

    When I removed the pools in the default data cache my problem went away. For some reason the 4k and 16k pools were causing a problem. My 16k pool was fairly small, so I'm thinking that could have contributed to the memory issue. I configured this server based on a production server that doesn't have this problem. That's why it seemed so strange at first.

    I eventually want to get the 16k pool back in there to cut down on reads, but I'm proceeding with caution.

    Thanks for your help.

    -Tony

Posting Permissions

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