data cache is too small?

[babeo]

Hi,
I have a script to do a mass insertion data into database, althought data is inserted into the database at the end, but I get a message, I don't know how severe is the problem, but I want to improve the condition.
Could anyone help to explain for me, and how to fix it please?

"00:00000:00218:2003/07/23 10:24:47.22 server The 2K memory pool of named cache default data cache (cache id 0, cachelet id 1) is configured too small for current demands (state 2). Transaction progress may cease or response time may increase."

Thanks

 

[sybaseguru]

babeo,

which version of ASE are you using. Please do the following and send the output

select @@version
go
sp_configure 'memory',
go
sp_cacheconfig
go

[/color blue]
if you can send the <server_name>.cfg file as well.

 

[babeo]

Here they are:

1> sp_cacheconfig
2> go
Cache Name Status Type Config Value Run Value
------------------------------ --------- -------- ------------ ------------
default data cache Active Default 1861.08 Mb 1861.08 Mb
------------ ------------
Total 1861.08 Mb 1861.08 Mb
==========================================================================
Cache: default data cache, Status: Active, Type: Default
Config Size: 1861.08 Mb, Run Size: 1861.08 Mb
Config Replacement: relaxed LRU, Run Replacement: relaxed LRU
Config Partition: 1, Run Partition: 1
IO Size Wash Size Config Size Run Size APF Percent
-------- --------- ------------ ------------ -----------
2 Kb 61440 Kb 0.00 Mb 1265.08 Mb 10
4 Kb 13104 Kb 64.00 Mb 64.00 Mb 10
8 Kb 6552 Kb 32.00 Mb 32.00 Mb 10
16 Kb 61440 Kb 500.00 Mb 500.00 Mb 80
(return status = 0)

1> sp_configure 'memory'
2> go
Msg 17411, Level 16, State 1:
Server 'ABC', Procedure 'sp_configure', Line 195:
Configuration option is not unique.

Parameter Name Default Memory Used Config Value
Run Value Unit Type
------------------------------ ----------- ----------- ------------
----------- -------------------- ----------
additional network memory 0 2250 2304000
2304000 bytes dynamic
allocate max shared memory 0 0 0
0 switch dynamic
heap memory per user 4096 0 4096
4096 bytes dynamic
lock shared memory 0 0 0
0 switch static
max memory 33792 3448386 1724193
1724193 memory pages(2k) dynamic
memory alignment boundary 2048 0 2048
2048 bytes static
memory per worker process 1024 36 1024
1024 bytes dynamic
shared memory starting address 0 0 0
0 not applicable static
total logical memory 33792 2720846 1360423
1360423 memory pages(2k) read-only
total physical memory 0 2720848 0
1360424 memory pages(2k) read-only

(1 row affected)
An additional 727540 K bytes of memory is available for reconfiguration. This is the difference between 'max memory' and 'total logical memory'.
(return status = 1)
1> select @@version
2> go
Adaptive Server Enterprise/12.5.0.3/EBF 10688 IR/P/Sun_svr4/OS 5.8/rel12503/1915/32-bit/FBO/Thu Jan 23 11:04:31 2003

=====================
ABC.cfg file

[Configuration Options]

[General Information]

[Backup/Recovery]
recovery interval in minutes = DEFAULT
print recovery information = DEFAULT
tape retention in days = DEFAULT

[Cache Manager]
number of oam trips = DEFAULT
number of index trips = DEFAULT
memory alignment boundary = DEFAULT
global async prefetch limit = DEFAULT
global cache partition number = DEFAULT

[Named Cache:default data cache]
cache size = 1861.08M
cache status = default data cache
cache replacement policy = relaxed LRU replacement
local cache partition number = DEFAULT

[4K I/O Buffer Pool]
pool size = 64.0000M
wash size = DEFAULT
local async prefetch limit = DEFAULT

[8K I/O Buffer Pool]
pool size = 32.0000M
wash size = DEFAULT
local async prefetch limit = DEFAULT

[16K I/O Buffer Pool]
pool size = 500.0000M
wash size = DEFAULT
local async prefetch limit = 80

[Meta-Data Caches]
number of open databases = 25
number of open objects = 3000
open object spinlock ratio = DEFAULT
number of open indexes = DEFAULT
open index hash spinlock ratio = DEFAULT
open index spinlock ratio = DEFAULT
partition groups = DEFAULT
partition spinlock ratio = DEFAULT

[Disk I/O]
disk i/o structures = DEFAULT
number of large i/o buffers = DEFAULT
page utilization percent = DEFAULT
number of devices = 50
disable disk mirroring = 0
allow sql server async i/o = DEFAULT

[Languages]
disable character set conversions = DEFAULT

[Unicode]
enable unicode normalization = 0
enable surrogate processing = 0
enable unicode conversions = DEFAULT
size of unilib cache = DEFAULT

[Network Communication]
default network packet size = DEFAULT
max network packet size = 8192
remote server pre-read packets = DEFAULT
number of remote connections = DEFAULT
number of remote logins = DEFAULT
number of remote sites = DEFAULT
max number network listeners = DEFAULT
tcp no delay = DEFAULT
allow sendmsg = DEFAULT
syb_sendmsg port number = DEFAULT
allow remote access = DEFAULT

[O/S Resources]
max async i/os per engine = DEFAULT
max async i/os per server = DEFAULT

[Parallel Query]
number of worker processes = 30
memory per worker process = DEFAULT
max parallel degree = 2
max scan parallel degree = DEFAULT

[Physical Resources]

[Physical Memory]
max memory = 1724193
additional network memory = 2304000
shared memory starting address = DEFAULT
allocate max shared memory = DEFAULT
dynamic allocation on demand = DEFAULT
lock shared memory = DEFAULT
heap memory per user = DEFAULT

[Processors]
max online engines = 3
number of engines at startup = 3

[SQL Server Administration]
procedure cache size = 253666
default database size = DEFAULT
identity burning set factor = DEFAULT
allow nested triggers = DEFAULT
allow updates to system tables = DEFAULT
default fill factor percent = DEFAULT
default exp_row_size percent = DEFAULT
number of mailboxes = DEFAULT
number of messages = DEFAULT
number of alarms = DEFAULT
number of pre-allocated extents = DEFAULT
event buffers per engine = 2000
cpu accounting flush interval = DEFAULT
i/o accounting flush interval = DEFAULT
sql server clock tick length = DEFAULT
runnable process search count = DEFAULT
i/o polling process count = DEFAULT
time slice = DEFAULT
cpu grace time = DEFAULT
number of sort buffers = DEFAULT
size of auto identity column = DEFAULT
identity grab size = DEFAULT
housekeeper free write percent = DEFAULT
enable housekeeper GC = DEFAULT
allow resource limits = 1
number of aux scan descriptors = DEFAULT
SQL Perfmon Integration = DEFAULT
allow backward scans = DEFAULT
license information = DEFAULT
enable sort-merge join and JTC = DEFAULT
abstract plan load = DEFAULT
abstract plan dump = DEFAULT
abstract plan replace = DEFAULT
abstract plan cache = DEFAULT
text prefetch size = DEFAULT
enable HA = DEFAULT
number of histogram steps = DEFAULT
sampling percent = DEFAULT

[User Environment]
number of user connections = 250
stack size = DEFAULT
stack guard size = DEFAULT
permission cache entries = DEFAULT
user log cache size = DEFAULT
user log cache spinlock ratio = DEFAULT

[Lock Manager]
number of locks = 500000
deadlock checking period = 1000
lock spinlock ratio = DEFAULT
lock address spinlock ratio = DEFAULT
lock table spinlock ratio = DEFAULT
lock hashtable size = DEFAULT
lock scheme = DEFAULT
lock wait period = DEFAULT
read committed with lock = DEFAULT
print deadlock information = DEFAULT
deadlock retries = DEFAULT
page lock promotion HWM = DEFAULT
page lock promotion LWM = DEFAULT
page lock promotion PCT = DEFAULT
row lock promotion HWM = DEFAULT
row lock promotion LWM = DEFAULT
row lock promotion PCT = DEFAULT

[Security Related]
systemwide password expiration = DEFAULT
audit queue size = DEFAULT
curread change w/ open cursors = DEFAULT
allow procedure grouping = DEFAULT
select on syscomments.text = DEFAULT
auditing = DEFAULT
current audit table = DEFAULT
suspend audit when device full = DEFAULT
enable row level access = DEFAULT
check password for digit = DEFAULT
minimum password length = DEFAULT
maximum failed logins = DEFAULT
enable ssl = DEFAULT
unified login required = DEFAULT
use security services = DEFAULT
msg confidentiality reqd = DEFAULT
msg integrity reqd = DEFAULT
secure default login = DEFAULT

[Extended Stored Procedure]
esp unload dll = DEFAULT
esp execution priority = DEFAULT
esp execution stacksize = DEFAULT
xp_cmdshell context = 0
start mail session = DEFAULT

[Error Log]
event logging = DEFAULT
log audit logon success = 1
log audit logon failure = 1
event log computer name = DEFAULT

[Rep Agent Thread Administration]
enable rep agent threads = DEFAULT

[Component Integration Services]
enable cis = DEFAULT
cis connect timeout = DEFAULT
cis bulk insert batch size = DEFAULT
max cis remote connections = DEFAULT
cis packet size = DEFAULT
cis cursor rows = DEFAULT
enable snmp = DEFAULT
enable file access = DEFAULT
cis bulk insert array size = DEFAULT
enable full-text search = DEFAULT
cis rpc handling = DEFAULT

[Java Services]
enable java = DEFAULT
size of process object heap = DEFAULT
size of shared class heap = DEFAULT
size of global fixed heap = DEFAULT
number of java sockets = DEFAULT
enable enterprise java beans = DEFAULT

[DTM Administration]
enable DTM = DEFAULT
enable xact coordination = DEFAULT
xact coordination interval = DEFAULT
number of dtx participants = DEFAULT
strict dtm enforcement = DEFAULT
txn to pss ratio = DEFAULT
dtm lock timeout period = DEFAULT
dtm detach timeout period = DEFAULT

[Diagnostics]
dump on conditions = DEFAULT
maximum dump conditions = DEFAULT
number of ccbs = DEFAULT
caps per ccb = DEFAULT
average cap size = DEFAULT

[Monitoring]
enable monitoring = DEFAULT
sql text pipe active = DEFAULT
sql text pipe max messages = DEFAULT
plan text pipe active = DEFAULT
plan text pipe max messages = DEFAULT
statement pipe active = DEFAULT
statement pipe max messages = DEFAULT
errorlog pipe active = DEFAULT
errorlog pipe max messages = DEFAULT
deadlock pipe active = DEFAULT
deadlock pipe max messages = DEFAULT
wait event timing = DEFAULT
process wait events = DEFAULT
object lockwait timing = DEFAULT
SQL batch capture = DEFAULT
statement statistics active = DEFAULT
per object statistics active = DEFAULT
max SQL text monitored = 1024
performance monitoring option = DEFAULT

Thanks

 

[sybaseguru]

From your
> sp_cacheconfig
2> go
Cache Name Status Type Config Value Run Value
------------------------------ --------- -------- ------------ ------------
default data cache Active Default 1861.08 Mb 1861.08 Mb
------------ ------------
Total 1861.08 Mb 1861.08 Mb
==========================================================================
Cache: default data cache, Status: Active, Type: Default
Config Size: 1861.08 Mb, Run Size: 1861.08 Mb
Config Replacement: relaxed LRU, Run Replacement: relaxed LRU
Config Partition: 1, Run Partition: 1
IO Size Wash Size Config Size Run Size APF Percent
-------- --------- ------------ ------------ -----------
2 Kb 61440 Kb 0.00 Mb 1265.08 Mb 10
4 Kb 13104 Kb 64.00 Mb 64.00 Mb 10
8 Kb 6552 Kb 32.00 Mb 32.00 Mb 10
16 Kb 61440 Kb 500.00 Mb 500.00 Mb 80

Why do you need 500MB for 16K and I recommend APF should be left as default (you can change it in cfg file). Also I would have expected to have about 70% of your total physical memory allocated to default data cache. Yours is around 1860/3367 i.e. around 55%. can you increase the default data cache (you will need to reboot the server to around 70% i.e. 2356MB and let the 2K take it all and run your query. Also what H/W you are running on and what is your total RAM. I also see that you are running ASE 32-bit on 2.8. Any reason you are running 32-bit? Anyway try this first and good luck

 

[babeo]

Thanks,
I think the original reason why we have to use ASE-32 bits because it is suggested and required by Sun to accomodate the JVM, or there will be problem.

We did run ASE-16 bit before, and on this machine, we decide to change to new version of ASE. We may upgrade or change to Oracle in future. And that may be another reason.

We use sun4u SUNW,Ultra-80, SUN OS 5.8, RAM 4096MB

So, for the procedure to change, I will modify the .cfg file and reboot the machine? Do you mind to explain more detail of the configure file. I don't know what it tells me much.

 

[sybaseguru]

Babeo,

You can do through the configuration file. Make sure that you have a backup of cfg file before you change it. Modify the folowing

[Named Cache:default data cache]
    cache size = 1861.08M    increase this to 2356M
    cache status = default data cache
    cache replacement policy = relaxed LRU replacement
    local cache partition number = DEFAULT

[4K I/O Buffer Pool]
    pool size = 64.0000M
    wash size = DEFAULT
    local async prefetch limit = DEFAULT

[8K I/O Buffer Pool]
    pool size = 32.0000M
    wash size = DEFAULT
    local async prefetch limit = DEFAULT

[16K I/O Buffer Pool]
    pool size = 500.0000M
    wash size = DEFAULT
    local async prefetch limit = 80  change this to DEFAULT

shutdown and reboot.

You can configure Adaptive Server either interactively, by using sp_configure, or noninteractively, by instructing Adaptive Server to read values from an edited or restored version of the configuration file.
The benefits of using onfiguration files include:
·You can replicate a specific configuration across multiple servers by using the same configuration file.
·You can use a configuration file as a baseline for testing configuration values on your server.
·You can use a configuration file to do validation checking on parameter values before actually setting the values.
·You can create multiple configuration files and switch between them as your resource needs change.

Hope this helps babeo and good luck

 

[iuanee]

hi, does anyone know how to clean up the default cache?
the current config value of it is 32.0 Mb which prevents me from adding another cache