1618817 SYB: How to restore a Sybase ASE database server (UNIX ) 1611715 SYB: How to restore a Sybase ASE database server (Windows)
80
Syntax of the quiesce database command block write operations to databases:
'tag_name' is a user specified tag, which names the quiesce operation. It is used later to release the specific set of databases.
'hold' specifies to hold the database and create a manifest file that describes it.
'for external dump' specifies that the DBA will create a backup of the devices with a tool external to ASE. The backup serves as a replacement for the dump database command.
'to manifest file' specifies the path to the mainfest file to be created.
A manifest file is a binary file that describes the database(s) on a device / a set of devices. It is needed later when you want to mount the database(s) on the copy of the devices.
Syntax of the quiesce database command resume write operations :
It is NOT possible to mount system databases (master, sybsystemdb) and tempdb. You may, however, start ASE on a set of devices, you previously copied while the databases where quiesced.
82
84
Internally ASE is devided into a so called 'server' layer and a 'kernel' layer. The server layer is the user facing part of the ASE server. It deals with databases, queries, users, locks, logging and transactions.
The ASE kernel provides a generic run-time environment for the RDBMS engine, it does task management and scheduling, provides SMP support (engines and spinlocks), disk I/O and network I/O. The ASE kernel also provides memory management , signal handling , error log services , security Integration (SSL, LDAP, PAM, Kerberos) and the JVM host environment. In short, it is the 'operating system' of the upper layer , the RDBMS engine.
A running ASE server consists of one or more 'engines' . In ASE pre-15.7 an engine is one or several running dataserver process(es).
ASE engines are what is visible on OS level, all the tasks an RDBMS server has to do, are delivered by the engine(s).
Inside an engine different ASE internal 'tasks' (i.e. ASE internal threads) run and do fulfill different tasks.
ASE on Windows NT is an exception. On Windows engines are implemented as NT threads , running inside one sqlsrvr.exe process.
86
The architecture of ASE does not change fundamentally with the threaded mode. Engines are implemented as lightweight process (LWP) now instead as real OS processes. Further performance gains in disk and network IO handling and better handling of different workloads (I/O vs CPU bound) are expected from the change.
In the process model configuring for CPU bound workload often had negative impact on I/O bound workload and vice versa.
This schema represents two physical CPUs, on which two ASE engines run (OS light weight processes in 15.7).
The ASE engines currently execute TASK8 and TASK6 (i.e. the context of these tasks has been loaded into the engine process context.
In ASE shared memory another 6 runnable tasks are in the engines' and the global run queue, 4 tasks are placed in wait queues.
These will get moved to one of the run queues as soon as their wait event arrives.
Tasks
Each connection to the ASE server is represented by a task. Tasks are identified by a SPID (Server Process ID) , additionally all tasks have a KPID (Kernel Process ID)
The SPID is associated with structures that describes the sessions database, queries, locks, transaction, (server layer) , whereas the KPID is associated with structures that represent ASE kernel layer state of the task.
Some internal tasks in ASE do not have a SPID , only a KPID .
Tasks can migrate between engines, if load is distributed unevenly between engines.
88
ASE's internal threading model vs the competition
The multithreaded process architecture has been implemented by Sybase ASE and Informix. Each user connection is a task (thread) within ASE's engines (processes or OS threads). This architecture has a low overhead and scales extremly well on SMP machines using one multitasking process per CPU.
The 2nd Architecture has been implemented by Oracle and DB2 / UDB. Here each user connection has an agent process on the database server. The agent process (db2bp, oracle shadow process) parses and executes the SQL and sends results to the application process (since DB2 V9.5 the db2bp is implemented as OS thread , not a process any longer).
A user agent / shadow process is similar to a task in the multithreaded architecture. This has a higher overhead than multithreaded architecture.
TCP/IP connection is established between two sockets, Client and Server each own a socket. The ASE Server has one socket for each client connection. Each SPID (i.e. ASE internal tasks) knows its socket.
Network IO is one of the areas where the new threaded kernel mode has its advantage. Sockets are owned by the OS process. Hence, in ASE pre15.7 a task had to return to its original engine process before it could send results back to the application process. With the new threaded kernel, an engine is a light weight process inside the parent dataserver process. Network I/O can get initiated by a task at any time, there is no need to migrate back to the original engine.
Since 12.5 ASE has introduced dynamic listeners: Prior to 12.5.1 network listens ports were read from the interfaces file at boot time and were static after that. In 12.5.1 the SA has the ability add or remove listen ports at run time and can bind them to specific engines. The procedure is the interface to this functionality. This adds flexibility to network configuration.
90
As ASE uses a non-preemptive scheduling scheme, tasks have to determine themselves that they should release the CPU to some other runnable task.
Tasks are given a time slice, i.e. time, in milliseconds, a task can run without yielding. If a task cross a yield point it checks its after it has exceeded its timeslice counter. If it has exceeded its timeslice, it yields voluntarily.
Time slice is internally tracked in clock ticks, rounded up, the default is 100 ms
The configuration parameter cpu grace time specifies the number of clock ticks a task is allowed to run after it exceeds its time slice. It allows a longer running task to hit a yield point or go to sleep.
Tasks that exceed their grace time are killed by the scheduler (preemptive) . The default for cpu grace time is 500 ticks, i.e. 50 secs. (an eternity)
You would rather seldom change time slice configuration parameter, as it affects all threads Increasing CPU GRACE TIME maybe workaround in certain scenarios
.
An automatic yield of a task vs a voluntary (time slice driven) yield.
A task yields voluntarily when it reaches a yield point in the code and time slice counter is negative.
An automatic yield happens when a task determines it has to wait for a resource. That resource can be a lock or an I/O request.
The task places itself on the corresponding wait queue.
92
RECURRING TIME SLICE ERRORS SHOULD BE ADDRESSED Try to find the root cause instead of covering them up
Don t adjust the time slice parameter ineffective solution
will affect all tasks, not only those that exceed grace time cpu grace time should be a temporary solution
consider increasing grace time while the root cause is being investigated default value of 500 ticks = 50 seconds. This is an eternity!
increasing this will simply cause a longer hang in many cases Pursue the root cause!
Tasks may change their priority , thus help ASE run smoothly.
Tasks on lock or latch sleep change to priority 3 prior to sleeping on a lock or latch and revert to original priority upon wake-up. High priority helps waking task get scheduled, reduces contention.
The Housekeeper task may be accelerated to priority 5 if its queues reach > 80% full.
Login processing is done at priority 3, this helps newly spawning user process get CPU time to speed login
The priority reset after login completes
Priority may also be altered using the Logical Process Manager
94
Task can migrate between engines
Engines can run tasks from local run queues, global run queues, or may steal tasks from another engine s run queues. Local tasks preferred due to locality of reference, local tasks at a given priority always have preference. Global queues help important tasks on SMP systems. Gglobal queue is checked after local queue in priority order (local pri 0, global pri 0, local pri 1, global pri 1, etc.).
Waking priority 3 tasks are placed in the global queue, this ensures they are run by first available engine.
Tasks may be stolen from another engine, an idle engines may check priority 5 queue of other engines, this improves load balancing
In 15.7 checking for I/O has been offloaded from the scheduler to separate IO threads in pool 'syb_system_pool'. These system threads use highly efficient modern OS specific APIs (completion ports).
This reduces the number of CPU cycles used for polling , compared to the pre-15.7 architecture.
Network I/O
Configuration parameter 'number of network tasks'
All open sockets for a given network type (TLI, TCP, etc.) are checked Network types processed round-robin, one per I/O check
Disk I/O
Configuration parameter 'number of disk tasks' outstanding asynchronous I/O checked for completion
Completed disk I/Os are processed and sleeping tasks woken
ASE still uses polling to check for async IO completion. So, don't increase the number of disk or network IO tasks unless you verified you actually have to. Otherwise you might see wasted CPU cycles due to unnecessary polling.
Internal test on a Linux system showed 200.000 disk IOs were handled by one 'disk IO task' .
96
98
Although memory pages are always of 2k size , in a 16k page size server, pages from disk of course are read in to the data cache 16k blocks of course
100
ASE comes with a default data cache into which all data are loaded from disk. In SAP installations the initial size of the default data cache has been set to 400MB, which is probably too small for most scenarios.
You should determine how much physical memory is available on the machine which can get allocated to ASE. Then allocate a significant portion of that memory to the default data cache. What significant portion is optimal depends on your system of course, but maybe 55% and is a good starting point.
It is possible to create additional named caches and bind objects to these (any object that has not been bound to a particular named cache will get loaded into the default data cache). However, in most cases it is probably a god idea to have only the default data cache, which should be sized as large as possible (w/o causing swapping).
There is one exception to that rule. Often, it is probably useful to create a named cache for the temporary database and bind the tempdb to that cache. Optimally this cache is large enough to keep the whole tempdb in memory, however, one has to balance this with other memory requirements of ASE.
Use the stored procedure 'sp_cacheconfig' to configure data caches, or alternatively, configure the data cache in the server configuration file (<DBSID>.cfg ) and reboot ASE after you made the
Configuring large IO pools inside a cache
You may divide a cache into pools of different IO sizes. Possible are pools for 2, 4 and 8 times the server pages size.
When a large IO pool has been configured and ASE reads in data, it determines whether it better to read in single pages or larger blocks of consecutive pages (e.g. during a table scan). A large IO pool may provide performance improvements in such cases.
Configuring a pool does not change the size of the cache, it just allocates a certain amount of memory out of the cache for a pool.
As a starting point , configure 25% percent of your default data cache as an 128K pool .
The procedure sp_poolconfig is available to configure pools in existing data caches , this can be done while ASE is online.
Large I/O pages are treated as a unit in the pool, they age together and are written out to disk together.
Asynchronous prefetch limit specifies the percentage of the pool that can be used to hold pages that have been brought into the cache by asynchronous
prefetch, but have not yet been used by any queries. The configuration parameter 'global async prefetch limit' parameter sets the default value for the server.
102
If the pool size is less than 300MB, the default wash size is 20 percent of the buffers in the pool.
If the pool size is greater than 300MB, the default wash size is 20 percent of the number of buffers in 300MB.
The minimum wash size is 10 buffers. The maximum size of the wash area is 80 percent of the pool size.
You must specify the pool size and wash size for all pools larger than 2KB
A Data cache may get partitioned when spinlock contention is an issue. Per default a data cache has only a single spinlock.
This spinlock may become a hot spot when may tasks try to access the cache on a SMP machine.
To reduce this type of spinlock contention , a data cache may get partitioned into 2,4,8, 64 partitions (number of partitions must be a power of 2).
Creation of cache partitions is not dynamic, i.e. when you use sp_cacheconfig to alter an existing cache, the change takes effect only at next reboot of the ASE server.
If the number of partitions is not configured , ASE will set bit to a power of two , that is closest to the number of engines configured. In most cases there shouldn't be a need to change this.
104
An starting point for how to set 'procedure cache size' is perhaps some 15% of 'max memory' . Statement cache size is initally set 51200 (100MB) , which is probably not too bad for a starting point.
If you increase it, increase it cautiously. Cached statements are implemented as 'leightweight stored procedures' and as such require an object descriptor.
As object descriptors are limited and needed for other stuff as well, don't overconfigure statement cache size.
It may lead to object descriptors becoming a hot spot.
106
If some basic , important settings are correct , generally you may expect good performance for your SAP system on Sybase ASE. As has been pointed out already Sybase ASE is a RDBMS system designed for high performance and throughput and many mission critical real time applications run on Sybase ASE.
On the following slides we will discuss the basic settings that have to be correct in order to achieve good performance.
108
Migrated systems that were not compressed before the migration will occupy less disk space afterwards, how much space is saved depends on the source data of course.
If lots of data reside in R3 cluster tables, the disk space reduction will not be that large, as data in these tables is compressed by R3.
The number of devices depends on the size of your database, of course. Too many small devices are probably not convenient for administration .
Perhaps there is no unique formula to determine the correct number of devices, however, it seems that the closest whole number to the square root of the database size in GB has served quite well for other RDBMS vendors.
Of course it is recommended to have one or several separate filesystems on separate disks for the database devices.
Also, RAID10 or at least hardware mirroring should be considered, particularly for the log devices. (the latter more for data safety than performance reasons)
If on a SAN, ensure the SAN administrator does her/his job with respect to eliminating hot spots and mapping the the database devices FS to fast disks / disk areas.
spsysmon output - Example (excerpt)
For this system it is not necessary to increase the number of disk IO threads.
110
ASE should be running in threaded kernel mode (it is considered to provide better performance) Entry in Athe configuration file <SERVER>.cfg :
In threaded kernel mode , the number of engines is configured with the command 'alter thread pool' Example:
1>
This sets the number of threads in the thread pool 'syb_default_pool' to 6 , i.e. the number of ASE engines.
The parameter 'idle timeout' specifies the number of microseconds an engine will loop , looking for more work, before going to sleep. The lower the value the less idle CPU cycle will get wasted by ASE.
A value of '0' specifies that an ASE engine will go to sleep immediately, when there is not work left to do. A value of '-1' , however specifies the ASE engine will never sleep.
Consider decreasing the value of 'idle timeout' particularly when ASE does run on a host that is not a dedicated database host.
Example:
Determine how much of physical memory is needed for the operating system and other processes (is this a CI host as well ?)
Allocate as much memory to ASE as possible, w/o cuasing memory paging on the host. Set 'max memory' to that amount.
On a production system it is recommended to set ASE configuration parameter 'allocate max shared memory' to 1 , so that ASE will allocate 'max memory' h
Most important is the configuration of the data caches. The default data cache should perhaps take up 50 % 55 % of max memory. Additional named data caches should get configured only when you determined a need for it. For example , if you have one query that has to run repeatedly and due to the size of the result set thrashes the default data cache for many other queries, it might improve overall performance to have a separte cache for such queries. However, keep in mind, that named caches always reduce the memory that would otherwise be available for the default data cache. If a named cache is configured but used only rarely it may turn out to be a waste of memory and may have a negative impact on performance.
A seprate, named cache for the temporary database(s) is perhaps useful in most cases,
112
The procedure cache is an important part to configure in order to achieve good performance. The more users and the more varying workload the larger should be the procedure cache. The suggested value of 15 % of 'max memory' are a starting point.
The statement cache needs to be enabled and configured, but should not be over configured. Too many cached statements may
Example output of sp_monitorconfig:
(not information displayed)
In this example all open partitions descriptors are used and there is a high reuse count. The parameter should get increased, although it is impossible to say how much from this output. Even if the number of partition descriptors in this example was only off by 1 (i.e. 50001 partitions were constantly in use) it would lead to a very high reuse count.
114
Number of locks depends on the number of concurrent users and type of workload they generate. If migrating from another RDBMS vendor you may have to rething the setting of this parameter, if your DB used lock escalation.
ASE does provide some sort of lock escalation, however, for SAP systems it is generally recommended to not have lock escalations.
In order ot prevent ASE from attempting to escalate locks set row lock promotion hwm and row lock promotion lwm to some high values.
Number of connections should get high enough to prevent accidentally running out of connections (e.g. when temporarily an additional SAP instance is started) . If in doubt set it higher.
Number of connections should get high enough to prevent accidentally running out of connections (e.g. when temporarily an additional SAP instance is started) . If in doubt set it higher.