Hi,
I have a solairs box that has Sybase 12.5 installed. The Solaris 9 server has 32 GB of ram. However when i issue a prstat I dont see all CPU's using 2GB og memory to each CPU
PID USERNAME SIZE RSS STATE PRI NICE TIME CPU PROCESS/NLWP
9706 root 1985M 1969M cpu2 10 0 16:17:09 6.1% dataserver/81
12405 root 1985M 406M cpu6 32 0 13:44:14 4.7% dataserver/79
12402 root 1985M 398M sleep 59 0 12:04:52 1.2% dataserver/77
The sybase.cfg files looks like this:
##########################
#
# Configuration File for the Sybase SQL Server
#
# Please read the System Administration Guide (SAG)
# before changing any of the values in this file.
#
##########################
[Configuration Options]
[General Information]
[Backup/Recovery]
recovery interval in minutes = DEFAULT
print recovery information = DEFAULT
tape retention in days = DEFAULT
max concurrently recovered db = DEFAULT
number of checkpoint tasks = 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
extended cache size = DEFAULT
[Named Cache:default data cache]
cache size = DEFAULT
cache status = default data cache
cache replacement policy = DEFAULT
local cache partition number = DEFAULT
[16K I/O Buffer Pool]
pool size = DEFAULT
wash size = DEFAULT
local async prefetch limit = DEFAULT
[Meta-Data Caches]
number of open databases = DEFAULT
number of open objects = DEFAULT
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 = DEFAULT
disable disk mirroring = DEFAULT
allow sql server async i/o = DEFAULT
[Languages]
disable character set conversions = DEFAULT
[Unicode]
enable unicode normalization = DEFAULT
enable surrogate processing = DEFAULT
enable unicode conversions = DEFAULT
size of unilib cache = DEFAULT
[Network Communication]
default network packet size = DEFAULT
max network packet size = DEFAULT
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
send doneinproc tokens = 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 = DEFAULT
memory per worker process = DEFAULT
max parallel degree = DEFAULT
max scan parallel degree = DEFAULT
[Physical Resources]
[Physical Memory]
max memory = 1000000
additional network memory = DEFAULT
shared memory starting address = DEFAULT
allocate max shared memory = 1
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
statement cache size = DEFAULT
[SQL Server Administration]
procedure cache size = 100000
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 = DEFAULT
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 = DEFAULT
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
i/o batch size = DEFAULT
enable xml = DEFAULT
enable webservices = DEFAULT
enable job scheduler = DEFAULT
job scheduler tasks = DEFAULT
job scheduler interval = DEFAULT
percent database for history = DEFAULT
percent history free = DEFAULT
percent database for output = DEFAULT
percent output free = DEFAULT
maximum job output = DEFAULT
[User Environment]
number of user connections = 2000
stack size = DEFAULT
stack guard size = DEFAULT
permission cache entries = DEFAULT
user log cache size = DEFAULT
user log cache spinlock ratio = DEFAULT
max native threads per engine = DEFAULT
messaging memory = DEFAULT
enable real time messaging = DEFAULT
histogram tuning factor = DEFAULT
rtm thread idle wait period = DEFAULT
[Lock Manager]
number of locks = 200000
deadlock checking period = DEFAULT
lock spinlock ratio = DEFAULT
lock address spinlock ratio = DEFAULT
lock table spinlock ratio = DEFAULT
lock hashtable size = DEFAULT
lock scheme = datarows
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
enable pam user auth = DEFAULT
enable ldap user auth = DEFAULT
secure default login = DEFAULT
[Extended Stored Procedure]
esp unload dll = DEFAULT
esp execution priority = DEFAULT
esp execution stacksize = DEFAULT
xp_cmdshell context = DEFAULT
start mail session = DEFAULT
[Error Log]
event logging = DEFAULT
log audit logon success = DEFAULT
log audit logon failure = DEFAULT
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 dump threads = 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 = DEFAULT
performance monitoring option = DEFAULT
What do I need to do so each CPU is online with 2GB of memory allocated to 3 CPU's
Thanks
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
The size of large buffer pools should be set by considering the applications and the actual usage of pools. As a starting point you can set it 300MB or a bit larger. Then you should monitor large buffer pool usage and change the size accordingly. As I said it can be changed dynamically without bouncing ASE.
Cache sizing:
It's important not to directly edit the file while ASE is up. At best, nothing will happen. At worst you will break something. If you're editing the file do so while ASE is down. I don't recommend it though as you don't get any of the validations that happen when you do it interactively through the DBA stored procedures.
Best to resize data caches using
sp_cacheconfig "default data cache", "<size>"
Note size is a string and specifies the units, so something like "1G", say.
Solaris memory segments:
You're also being a bit misled by how Solaris is reporting Sybase memory. ASE uses one or more shared memory segments. ASE will request memory from Solaris, which will create a memory segment, and the dataserver engines (one per engine you have currently configured to be online) will all attach to the same shared memory. Basically you can't just add up what vmstat/prstat/top reports.
In fact this is a general comment about measuring O/S resources used by ASE - the O/S doesn't report them correctly. You can't look at CPU reported by the system and say that's how much CPU Sybase is using - ditto with memory and even I/Os to disk and network. You need to ask Sybase questions with Sybase tools. The way to ask how much memory ASE is using is to run
sp_configure "total physical memory"
The answer to this tells you how much memory has been allocated within ASE... and that's how much it will have asked for from the operating system.
Also to answer your specific question: "I will see all three cpu's use close too 2GB?"
No, probably not. :) This is correct and expected. The entire memory segment(s) will probably be recorded as being against engine 0, i.e. the first one to start, which grabs the memory.
Re. buffer pools within caches - that's a huge topic right there. Probably the best advice we can give you is to point you at the standard Sybase manual the Performance and Tuning Guide on the Sybase website which discusses this in detail.
> Hi alpmoon, so I understand, the reason the CPU's were not using 2 GB for each CPU was the fact that cache size was not set?
No. The reason Solaris is reporting this is because Solaris is not Sybase and doesn't understand how Sybase works. :) You may never see all engines being reported as using 2Gb - and this doesn't matter.
We've shown you how to answer the question "how much memory is my ASE using?". You cannot answer this correctly using anything but a Sybase tool to ask the question. If I have a single piece of advice for you it would be to stop looking at ASE using Solaris tools. :)
Hi Joe,
I agree that using sp_configure is a much better way to see the actual memory usage. But some of the things are obvious from config file and prstat.
richsark,
Actually, the other question is why using ASE 12.5 when it will not be supported next year. You should be using ASE 15 with more dynamic configuration options.
"It's important not to directly edit the file while ASE is up." It was another point I missed. Even though most of the changes are OK in this way, if you are going to bounce ASE after that, the preferred method should be using ASE stored procs to change the configuration. ASE may not start after editing the config file and you may need to edit it again to make parameters working together (by increasing "max memory" or decreasing "default data cache size").
Business Accounts
Answer for Membership
by: alpmoonPosted on 2009-08-24 at 17:23:12ID: 25173732
I think the first thing you need to do is to configure your data cache. Currently you don't use 1985M of memory, because you haven't yet allocated that memory. You can allocate memory for other purposes as well. But, the most critical one is data cache. Default size of "default data cache" is very small. If you configure "default data cache" size to let's say '1500M' (as shown below), you can see that the actual memory used will be close to 1985M. You can also change it by using sp_cacheconfig
cache size = 1500M
Unfortunately, you need to reboot ASE to change cache size. You should also adjust 16 K pool buffer size, even though you can do it dynamically.