Sybase reorg running too slow
Some slow performance with our reorg comamnd when it running, when I check the log it says
01server The 16K memory pool of named cache tempdbcache (cache id 1, cachelet id 2) is configured too small for current demands (state 1). Transaction progress may cease or response time may increase.
01server The 16K memory pool of named cache tempdbcache (cache id 1, cachelet id 4) is configured too small for current demands (state 1). Transaction progress may cease or response time may increase.
01server The 16K memory pool of named cache tempdbcache (cache id 1, cachelet id 1) is configured too small for current demands (state 1). Transaction progress may cease or response time may increase.
01server The 16K memory pool of named cache tempdbcache (cache id 1, cachelet id 3) is configured too small for current demands (state 1). Transaction progress may cease or response time may increase.
I ran sp_helpcache which tell me a results below
1> sp_helpcache
2>
Cache Name Config Size Run Size Overhead
------------------ ------------- ---------- ----------
default data cache 6000.00 Mb 6000.00 Mb 696.91 Mb
tempdbcache 500.00 Mb 500.00 Mb 56.83 Mb
(2 rows affected)
Memory Available For Memory Configured
Named Caches To Named Caches
-------------------- ----------------
6500.01 Mb 6500.00 Mb
****** Wil' the value above here is available cache allow to be use ?
------------------ Cache Binding Information: ------------------
Cache Name Entity Name Type Index Name Status
---------- ----------- ---- ---------- ------
tempdbcache tempdb database V
(return status = 0)
1> sp_cacheconfig 'tempdbcache'
2>
Cache Name Status Type Config Value Run Value
------------ -------- -------- -------------- ------------
tempdbcache Active Mixed 500.00 Mb 500.00 Mb
------------ ------------
Total 500.00 Mb 500.00 Mb
==========================
Cache: tempdbcache, Status: Active, Type: Mixed
Config Size: 500.00 Mb, Run Size: 500.00 Mb
Config Replacement: strict LRU, Run Replacement: strict LRU
Config Partition: 4, Run Partition: 4
IO Size Wash Size Config Size Run Size APF Percent
-------- ------------- ------------ ------------ -----------
2 Kb 90112 Kb 0.00 Mb 440.00 Mb 10
4 Kb 2048 Kb 10.00 Mb 10.00 Mb 10
16 Kb 10240 Kb 50.00 Mb 50.00 Mb 10
(return status = 0)
1>
Should I know increase this cache ??? will it help to speed up query and reorg performance in my sybase server ?
01server The 16K memory pool of named cache tempdbcache (cache id 1, cachelet id 2) is configured too small for current demands (state 1). Transaction progress may cease or response time may increase.
01server The 16K memory pool of named cache tempdbcache (cache id 1, cachelet id 4) is configured too small for current demands (state 1). Transaction progress may cease or response time may increase.
01server The 16K memory pool of named cache tempdbcache (cache id 1, cachelet id 1) is configured too small for current demands (state 1). Transaction progress may cease or response time may increase.
01server The 16K memory pool of named cache tempdbcache (cache id 1, cachelet id 3) is configured too small for current demands (state 1). Transaction progress may cease or response time may increase.
I ran sp_helpcache which tell me a results below
1> sp_helpcache
2>
Cache Name Config Size Run Size Overhead
------------------ ------------- ---------- ----------
default data cache 6000.00 Mb 6000.00 Mb 696.91 Mb
tempdbcache 500.00 Mb 500.00 Mb 56.83 Mb
(2 rows affected)
Memory Available For Memory Configured
Named Caches To Named Caches
-------------------- ----------------
6500.01 Mb 6500.00 Mb
****** Wil' the value above here is available cache allow to be use ?
------------------ Cache Binding Information: ------------------
Cache Name Entity Name Type Index Name Status
---------- ----------- ---- ---------- ------
tempdbcache tempdb database V
(return status = 0)
1> sp_cacheconfig 'tempdbcache'
2>
Cache Name Status Type Config Value Run Value
------------ -------- -------- -------------- ------------
tempdbcache Active Mixed 500.00 Mb 500.00 Mb
------------ ------------
Total 500.00 Mb 500.00 Mb
==========================
Cache: tempdbcache, Status: Active, Type: Mixed
Config Size: 500.00 Mb, Run Size: 500.00 Mb
Config Replacement: strict LRU, Run Replacement: strict LRU
Config Partition: 4, Run Partition: 4
IO Size Wash Size Config Size Run Size APF Percent
-------- ------------- ------------ ------------ -----------
2 Kb 90112 Kb 0.00 Mb 440.00 Mb 10
4 Kb 2048 Kb 10.00 Mb 10.00 Mb 10
16 Kb 10240 Kb 50.00 Mb 50.00 Mb 10
(return status = 0)
1>
Should I know increase this cache ??? will it help to speed up query and reorg performance in my sybase server ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I disagree with my learned colleague jlsilva01. Increasing parallelism will increase how much work reorg is trying to do at once... which will increase demands on cache. There is no point do this when there is already not enough space in this tempdb cache.
ASKER
Hi,
I have now increase the tempdbcache to 1000 and allocate 250MB for 16k pool, we will again run the same cronjob during the weekend and monitor it status.
I have now increase the tempdbcache to 1000 and allocate 250MB for 16k pool, we will again run the same cronjob during the weekend and monitor it status.
250 out of 1000 is probably not enough. We can't say exactly what the right number is without knowing how fragmented your table(s) is/are, but 400 or even 500 is probably about right.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
:)
ASKER
Thanks for the explanation I have tried with increasing Tempdbcache from 500MB to 1000MB but I notice overhead also now has increased, why the overhead get increased ?
1> sp_helpcache tempdbcache
2> Cache Name Config Size Run Size Overhead
------------ ------------- ---------- ----------
tempdbcache 1000.00 Mb 1000.00 Mb 113.56 Mb
(1 row affected)