memory pool of named cache default data cache is configured too small for current demands

[former_member207908]

Dear Experts,

Issue: We have a system copy running and hence the following continous error in the ASE errorlog indicating slowness

Error received:

00:0003:00000:00045:2014/12/23 14:17:21.10 server  The 128K memory pool of named cache default data cache (cache id 0, cachelet id 3) is configured too small for current demands (state 1). Transaction progress may cease or response time may increase

ASE/OS: 15.7 SP122/AIX 7.1

Attachments: sp_sysmon for different times during system copy (Need more details?)

Let me know if I need to reconfigure the named cache size

Regards,

Rajesh

sp_sysmon_C30_2.txt.zip

C30_sp_sysmon.txt.zip

[former_member187136 · Accepted solution]

Rajesh,

Provide me sp_cacheconfig output please.

Regards

Kiran Kumar A

[former_member207908 · Accepted solution]

Dear Kiran

Thank you for your response. Please find the sp_cacheconfig output

Cache Name               Status       Type     Config Value       Run Value
------------------------------------------------------------------------ -------------------------------- -------------------------------- -------------------------------------------------------- ----------------------default data cache       Active       Default    8294.40 Mb       8294.40 Mb

log cache                   Active       Log Only  512.00 Mb         512.00 Mb

                                    ------------ ------------
                            Total    8806.40 Mb   8806.40 Mb
==========================================================================
Cache: default data cache,   Status: Active,   Type: Default
      Config Size: 8294.40 Mb,   Run Size: 8294.40 Mb
      Config Replacement: strict LRU,   Run Replacement: strict LRU
      Config Partition:            8,   Run Partition:            8
IO Size  Wash Size     Config Size  Run Size     APF Percent
-------- ------------- ------------ ------------ -----------
    16 Kb     663552 Kb   6634.50 Mb   6634.50 Mb     10
   128 Kb     165888 Kb   1658.88 Mb   1659.00 Mb     10
==========================================================================
Cache: log cache,   Status: Active,   Type: Log Only
      Config Size: 512.00 Mb,   Run Size: 512.00 Mb
      Config Replacement: strict LRU,   Run Replacement: strict LRU
      Config Partition:            1,   Run Partition:            1
IO Size  Wash Size     Config Size  Run Size     APF Percent
-------- ------------- ------------ ------------ -----------
    16 Kb       2448 Kb     12.00 Mb     12.00 Mb     10
    32 Kb      61440 Kb    500.00 Mb    500.00 Mb     10

As per the SAP,

Root cause of the error:

97% Prefetch data Size is bigger than 128k pool size, So it can't use the Large I/Os  pool.
This cause the message in errorlog  "server  The 128K memory pool of named cache default data cache (cache id 0, cachelet id 3) is configured too small for current demands (state 1). Transaction progress may cease or response time may increase."

Now the pool config of "default data cache" is
IO Size   Wash Size      Config Size   Run Size      APF Percent
--------  -------------  ------------  ------------  -----------
16 Kb     663552 Kb      6634.50 Mb    6634.50 Mb    10
128 Kb    165888 Kb      1658.88 Mb    1659.00 Mb    10

Please increase "128 Kb" pool, such as to 5Gb.

sp_poolconfig "default data cache", "5G", "128K"
go

I would like to know your valuable suggestions if you feel any.

Regards,

Rajesh

[former_member187136 · Accepted solution]

Rajesh,

Based on the messages we see on the errorlog,
"server  The 128K memory pool of named cache default data cache (cache id 0, cachelet id 3) is configured too small for current demands (state 1). Transaction progress may cease or response time may increase."

You need to increase the  size as the errorlog suggest and what your system can afford in term of max memory and total logical memory.

PS: Before configuring or increasing make sure you have enough memory under configured logical memory.

Regards

Kiran Kumar A

SAP

[former_member187136 · Accepted solution]

Rajesh,

Please use this infocenter writeup on cache configurations:

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc36273.1570/html/sprocs/X6...

Regards

Kiran Kumar A

SAP

[former_member207908 · Accepted solution]

Adding to that, I feel that sort buffer PCt_act value has reached 100% with out any reuse (swap)

 Name         Num_free  Num_active  Pct_act    Max_Used    Reuse_cnt   Instance_Name
 ------------------------- ----------- ----------- ------- ----------- ----------- ----------------------------------------------
 no of sort buffers      0       16200       100.00        16200           0                 NULL

"If a system workload involves very large sort operations, increasing this number can help improving sort performance"

Do you think sort buffers play any role here?

Correct me if im wrong

Regards,

Rajesh

[former_member187136 · Accepted solution]

Buddy,

You are thinking in the other way around. This parameter for number of sort buffers is only for creating parallel indexes and parallel sorting which will not match your "system copy" task

If you configure this to higher value, the large table may require more procedure cache

Parallel Sorts are used only for running index creation and running update statistics.

Let me give you the traditional definition of this parameter from infocenter in my next reply.

Regards

Kiran Kumar A

SAP

[former_member187136 · Accepted solution]

Rajesh,

I am correct . Pasted you the details from the infocenter

number of sort buffers specifies the amount of memory allocated for buffers used to hold pages read from input tables and perform index merges during sorts. number of sort buffers is used only for parallel sorting.

Parallel sorts are used when you:

  Run updates statistics

  Create indexes

The value you use for number of sort buffers depends on the page size of the server.

Sybase recommends that you leave this parameter set to the default except when you are creating indexes in parallel.

Setting the value too high can rob nonsorting processes of access to the buffer pool in caches being used to perform sorts.

If you configure a high number of sort buffers, a sort on a large table may require more procedure cache. The effect is more pronounced with tables that have smaller row sizes, because the number of rows per page is higher.

This equation estimates the amount of procedure cache required (in bytes):

(Number of sort buffers) X (rows per page) X 100

If you do not configure enough procedure cache for the number of sort buffers, the sort may fail with error message 701. If this occurs, reconfigure Adaptive Server with a lower number of sort buffers and retry the sort.

Regards

Kiran Kumar A

SAP

[former_member207908 · Accepted solution]

Dear Kiran,

Thank you for your wonderful response.

Now the question is how much to increase?

How can I decide to configure to a certain near perfect value?

Do you think trail and error method like increasing to 3 GB initially and increasing further if required works here?

Regards,

Rajesh

[former_member207908 · Accepted solution]

Do we need additional memory to reconfigure 128K memory pool (1.6GB)?

Cant we shift the memory allocated to 16K memory pool (6GB) to 128K memory pool?

Regards,

Rajesh