sap ase learning session 5 configuring memory
TRANSCRIPT
By R.N. Sangwan-www.rnsangwan.com
To Learn or Teach visit www.theskillpedia.com
Visit www.theskillpedia.com to learn or teach. By: www.rnsangwan.com 1
• The size of the wash area for the pool.
• As pages enter the wash area of the cache, they are written to disk.
• This column is blank on the line that shows the cache configuration.
APF Percent• The percentage of buffers in the pool that can hold buffers that have been
fetched by asynchronous prefetch, but have not been used.
Visit www.theskillpedia.com to learn or teach. By: www.rnsangwan.com 2
• Memory allocated to Sybase ASE
• The maximum memory parameter max memory should be set as high as possible takinginto consideration anything else which is running on the same host.
• You have to be aware of any other Adaptive Servers, Sybase products or other databasesystems that may be running on the box and taking memory.
• Note that having multiple engines configured for a server will not increase the memoryoverhead as the configured memory is shared among the engines.
3Visit www.theskillpedia.com to learn or teach. By: www.rnsangwan.com
Case study:
• one host and one ASE, plus the backup server and replication server.
• If we have a running a Sybase then leaving 600-800MB to the operating system plus the
replication server and the backup server should be sufficient.
• The max memory that the backup server will take is 144MB.
• It will be safe to give the replication server around 200MB. That leaves the rest to OS.
4Visit www.theskillpedia.com to learn or teach. By: www.rnsangwan.com
After taking the consideration for tempdb on tmpfs (assuming that you will be
creating tempdb on tmpfs) and if you have enough left over memory, you can
give a max memory of 4000MB to ASE itself.
5Visit www.theskillpedia.com to learn or teach. By: www.rnsangwan.com
• When you start up ASE, it will go and read the configuration file <SERVER_NAME>.cfg.
• The total memory allocated during start up is the sum of memory required for all the
configuration needs of ASE as specified in the configuration file.
• This value can be obtained from the read-only configuration parameter total logical memory.
• The configuration parameter max memory must be greater than or equal to total logical
memory, otherwise ASE will fail to start.
6Visit www.theskillpedia.com to learn or teach. By: www.rnsangwan.com
• Additionally, when you start up ASE, it will go and check whether there is
enough shared memory available as specified by max memory parameter.
• If the memory is not there or the equivalent swap space is not there, ASE
will fail to start as well.
• The important point to remember here is that checking for the availability of
memory does not mean that ASE will go and grab it!
• ASE will use as much memory as required.
7Visit www.theskillpedia.com to learn or teach. By: www.rnsangwan.com
1> sp_configure memory
2> go
Parameter Name Default Memory Used Config Value Run Value Unit Type
------------------------------ ----------- ----------- ------------ ----------- -------------------- ----------
additional network memory 0 740 757760 757760 bytes dynamic
allocate max shared memory 0 0 0 0 switch dynamic
compression memory size 0 76 0 0 memory pages(2k) dynamic
engine memory log size 0 2 0 0 memory pages(2k) dynamic
heap memory per user 4096 0 4096 4096 bytes dynamic
lock shared memory 0 0 0 0 switch static
max memory 33792 4096000 2048000 2048000 memory pages(2k) dynamic
memory alignment boundary 2048 0 2048 2048 bytes static
memory per worker process 1024 48 1024 1024 bytes dynamic
messaging memory 400 0 400 400 memory pages(2k) dynamic
shared memory starting address 0 0 0 0 not applicable static
total logical memory 33792 3914828 1957414 1957411 memory pages(2k) read-only
total physical memory 0 3914832 0 1957416 memory pages(2k) read-only
8Visit www.theskillpedia.com to learn or teach. By: www.rnsangwan.com
• Total physical memory run value is the one telling you how much memory
ASE is currently using.
• For example in this case ASE has 4GB of max memory but only using
1957416/512 = 3823MB in practice.
9Visit www.theskillpedia.com to learn or teach. By: www.rnsangwan.com
• additional network memory• This is the memory required for networks.
• allocate max shared memory• During start up ASE allocates memory based on the value of total logical memory.
• However, if the configuration parameter, allocate max shared memory has been set, then
the memory allocated will be based on the value of max memory.
• Unless you are competing with other resources, leave this parameter as default.
10Visit www.theskillpedia.com to learn or teach. By: www.rnsangwan.com
• lock shared memory• It disallows swapping of Adaptive Server pages to disk and allows the operating system
kernel to avoid the server's internal page locking code.• This can reduce disk reads, which are expensive.• Always ensure that there is enough RAM to cover for your max memory specification at
all times.
• memory per worker process• It specifies the amount of memory used by worker processes. Each worker process
requires memory for messaging during query processing.• For most needs the default value is perfectly adequate.
11Visit www.theskillpedia.com to learn or teach. By: www.rnsangwan.com
• After taking any memory needed by the Sybase kernel the rest is allocated to the defaultdata cache and the procedure cache.
• Default data cache• When ASE is created, it only has one cache, the default data cache. The raw default data
cache only has default buffer pools. The default buffer pool is the ASE’s page size.• So if you created ASE with 2K page size, then the default data cache will be made of 2K
buffer pools.• However, you can add additional buffer pools. In ASE, any table or index, which does not
have a binding or is not within a database bound to a named cache, will use the defaultdata cache.
• You cannot rename or delete the default data cache.
12Visit www.theskillpedia.com to learn or teach. By: www.rnsangwan.com
• You can adjust the size of default data cache dynamically.To allocate 900MB to default data cache you can use the following command:
sp_cacheconfig 'default data cache' , '900M‘
go
(return status = 0)
13Visit www.theskillpedia.com to learn or teach. By: www.rnsangwan.com
• ASE’s logical memory manager is capable of maintaining multiple caches.
• These user-defined caches are referred to as named cache.
• When you create named caches, you are effectively slicing up the shared memory
allocated to ASE into a number of separate data caches.
• Each named cache can be reserved for specific databases or database objects such as
tables and indexes.
• In ASE, this process is called “binding databases or objects to cache”.
14Visit www.theskillpedia.com to learn or teach. By: www.rnsangwan.com
• ASE binding enables the following:• Allows frequently accessed objects to be kept in memory by preventing other object’s
data pages to use the reserved pages
• Minimizes the effect of one application from another
• Stops the memory hungry applications from flushing out the useful data from the cache
• Can be configured with different cache pools
• Gives DBA a degree of control on using the available memory efficiently
• Both the default data cache and named caches can be configured dynamically. This
reduces or minimizes the downtime of the server.
15Visit www.theskillpedia.com to learn or teach. By: www.rnsangwan.com
Example : a private cache for dbccdb (database consistency check) database.
• You are advised to create a private cache for dbcc operations and bind dbccdb database to
this cache. This is because typically a dbcc operation has to read data for all tables
sequentially.
• This means caching as much data in a single IO as possible and obviously all the data
pages.
• Thus, the larger block size of 16K or more is ideal for dbcc operations and hence youshould create good size large buffer pools in dbccdb cache.
• You also do not want dbcc operation to flush out your valuable data out of memory.
16Visit www.theskillpedia.com to learn or teach. By: www.rnsangwan.com
• Large IO and Multiple Buffer Pools• When more than one buffer pool is available in a cache, the ASE optimizer determines
the optimal page size for each query and uses the appropriate buffer pool.
• ASE allows you to configure up to four larger page sizes. With 8K page size, buffer pools
of 8K, 16K, 32K and 64K can be configured.
• Example on the next slide
17Visit www.theskillpedia.com to learn or teach. By: www.rnsangwan.com
1> sp_cacheconfig 'default data cache'2> go
Cache Name Status Type Config Value Run Value
------------------ ------ ------- ------------ ------------
default data cache Active Default 1625.00 Mb 1625.00 Mb
------------ ------------
Total 1625.00 Mb 1625.00 Mb
===============================================================
Cache: default data cache, Status: Active, Type: Default
Config Size: 1625.00 Mb, Run Size: 1625.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
-------- --------- ------------ ------------ -----------
16 Kb 5120 Kb 25.00 Mb 25.00 Mb 10
32 Kb 20480 Kb 100.00 Mb 100.00 Mb 10
64 Kb 61440 Kb 300.00 Mb 300.00 Mb 10
8 Kb 61440 Kb 0.00 Mb 1200.00 Mb 10
(return status = 0)
18Visit www.theskillpedia.com to learn or teach. By: www.rnsangwan.com
Logical page size/KB Buffer pool sizes/KB
2 2, 4, 8, 16
4 4, 8, 16, 32
8 8, 16, 32,64
16 16, 32, 64, 128
19
Summary of logical page size and the corresponding
Buffer pool sizes.
Visit www.theskillpedia.com to learn or teach. By: www.rnsangwan.com
Example of creating a user defined cache and the associated pools
1> sp_cacheconfig tempdb_log_cache, '500M','logonly','relaxed', 'cache_partition=1'2> go
Since this is a log cache, it should be mainly made up of 4K buffer pools and some 16K buffer pools as well:
1> sp_poolconfig "tempdb_log_cache", "370M", "4K", "2K"2> go
1> sp_poolconfig "tempdb_log_cache", "125M", "16K", "2K"2> go
20Visit www.theskillpedia.com to learn or teach. By: www.rnsangwan.com
Display information about this cache1> sp_cacheconfig tempdb_log_cache
2> goCache Name Status Type Config Value Run Value
------------------------------ --------- -------- ------------ ------------
tempdb_log_cache Active Log Only 500.00 Mb 500.00 Mb
------------ ------------
Total 500.00 Mb 500.00 Mb
==========================================================================
Cache: tempdb_log_cache, Status: Active, Type: Log Only
Config Size: 500.00 Mb, Run Size: 500.00 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 1024 Kb 0.00 Mb 5.00 Mb 10
4 Kb 61440 Kb 370.00 Mb 370.00 Mb 10
16 Kb 25600 Kb 125.00 Mb 125.00 Mb 10
21Visit www.theskillpedia.com to learn or teach. By: www.rnsangwan.com
As a rule of thumb you can set this one to 30
1> sp_configure devices
2> goParameter Name Default Memory Used Config Value Run Value
------------------------------ ----------- ----------- ------------ -----------
number of devices 10 #17 30 30
22Visit www.theskillpedia.com to learn or teach. By: www.rnsangwan.com
A value of 100 should be sufficient for most non production servers1> sp_configure 'user connections'
2> go
Parameter Name Default Memory Used Config Value Run Value
------------------------------ ----------- ----------- ------------ -----------
number of user connections 25 17261 100 100
23Visit www.theskillpedia.com to learn or teach. By: www.rnsangwan.com
Visit www.theskillpedia.com to learn or teach. By:
www.rnsangwan.com 24