| |||||
![]() |
![]() |
![]() |
|||
|
Buildmaster Configuration Definitions in Sybase More DBA job interview questions and answers at
http://dba.fyicenter.com/Interview-Questions/
(Continued from previous question...) Buildmaster Configuration Definitions in Sybase The following is a list of configuration parameters and their effect on the ASE. Changes to these parameters can affect performance of the server. Sybase does not recommend modifying these parameters without first discussing the change with Sybase Tech Support. This list is provided for information only. These are categorized into two kinds:
Configurable variables:crecinterval: The recovery interval specified in minutes. ccatalogupdates: A flag to inform whether system catalogs can be updated or not. cusrconnections: This is the number of user connections allowed in SQL Server. This value + 3 (one for checkpoint, network and mirror handlers) make the number of pss configured in the server. cfgpss: Number of PSS configured in the server. This value will always be 3 more than cusrconnections. The reason is we need PSS for checkpoint, network and mirror handlers. THIS IS NOT CONFIGURABLE. cmemsize: The total memory configured for the Server in 2k units. This is the memory the server will use for both Server and Kernel Structures. For Stratus or any 4k pagesize implementation of ASE, certain values will change as appropriate. cdbnum: This is the number of databases that can be open in SQL Server at any given time. clocknum: Variable that defines and controls the number of logical locks configured in the system. cdesnum: This is the number of open objects that can be open at a given point of time. cpcacheprcnt: This is the percentage of cache that should be used for procedures to be cached in. cfillfactor: Fill factor for indexes. ctimeslice: This value is in units of milli-seconds. This value determines how much time a task is allowed to run before it yields. This value is internally converted to ticks. See below the explanations for cclkrate, ctimemax etc. ccrdatabasesize: The default size of the database when it is created. This value is Megabytes and the default is 2Meg. ctappreten: An outdated not used variable. crecoveryflags: A toggle flag which will display certain recovery information during database recoveries. cserialno: An informational variable that stores the serial number of the product. cnestedtriggers: Flag that controls whether nested triggers allowed or not. cnvdisks: Variable that controls the number of device structures that are allocated which affects the number of devices that can be opened during server boot up. If user defined 20 devices and this value is configured to be 10, during recovery only 10 devices will be opened and the rest will get errors. cfgsitebuf: This variable controls maximum number of site handler structures that will be allocated. This in turn controls the number of site handlers that can be active at a given instance. cfgrembufs: This variable controls the number of remote buffers that needs to send and receive from remote sites. Actually this value should be set to number of logical connections configured. (See below) cfglogconn: This is the number of logical connections that can be open at any instance. This value controls the number of resource structure allocated and hence it will affect the overall logical connection combined with different sites. THIS IS NOT PER SITE. cfgdatabuf: Maximum number of pre-read packets per logical connections. If logical connection is set to 10, and cfgdatabuf is set to 3 then the number of resources allocated will be 30. cfupgradeversion: Version number of last upgrade program ran on this server. csortord: Sort order of ASE. cold_sortdord: When sort orders are changed the old sort order is saved in this variable to be used during recovery of the database after the Server is rebooted with the sort order change. ccharset: Character Set used by ASE cold_charset: Same as cold_sortord except it stores the previous Character Set. cdflt_sortord: page # of sort order image definition. This should not be changed at any point. This is a server only variable. cdflt_charset: page # of character set image definition. This should not be changed at any point. This is a server only variable. cold_dflt_sortord: page # of previous sort order image definition. This should not be changed at any point. This is a server only variable. cold_dflt_charset: page # of previous chracter set image definition. This should not be changed at any point. This is a server only variable. cdeflang: Default language used by ASE. cmaxonline: Maximum number of engines that can be made online. This number should not be more than the # of cpus available on this system. On Single CPU system like RS6000 this value is always 1. cminonline: Minimum number of engines that should be online. This is 1 by default. cengadjinterval: A noop variable at this time. cfgstacksz: Stack size per task configured. This doesn't include the guard area of the stack space. The guard area can be altered through cguardsz. cguardsz: This is the size of the guard area. ASE will allocate stack space for each task by adding cfgstacksz (configurable through sp_configure) and cguardsz (default is 2K). This has to be a multiple of PAGESIZE which will be 2k or 4k depending on the implementation. behaviour: Size of fixed stack space allocated per task including the guard area. Non-configurable values : TIMESLICE, CTIMEMAX ETC: 1 millisecond = 1/1000th of a second. "cclkrate" :A value specified in microsecond units. Normally on systems where a fine grained timer is not available or if the Operating System cannot set sub-second alarms, this value is set to 1000000 milliseconds which is 1 second. In other words an alarm will go off every 1 second or you will get 1 tick per second. On Sun4 this is set to 100000 milliseconds which will result in an interrupt going at 1/10th of a second. You will get 6 ticks per second. "avetimeslice" :A value specified in millisecond units. This is the value given in "sp_configure",<timeslice value>. Otherwise the milliseconds are converted to milliseconds and finally to tick values. ticks = <avetimeslice> * 1000 / cclkrate. "timeslice" : The unit of this variable is in ticks. This value is derived from "avetimeslice". If "avetimeslice" is less than 1000 milliseconds then timeslice is set to 1 tick. "ctimemax" :The unit of this variable is in ticks. A task is considered in infinite loop if the consumed ticks for a particular task is greater than ctimemax value. This is when you get timeslice -201 or -1501 errors. "cschedspins" :For more information see Q1.3.2. This value alters the behavior of ASE scheduler. The scheduler will either run a qualified task or look for I/O completion or sleep for a while before it can do anything useful. The cschedspins value determines how often the scheduler will sleep and not how long it will sleep. A low value will be suited for a I/O bound ASE but a high value will be suited for CPU bound ASE. Since ASE will be used in a mixed mode, this value need to be fined tuned. Based on practical behavior in the field, a single engine ASE should have cschedspins set to 1 and a multi-engine server should have set to 2000. Now that we've defined the units of these variables what happens when we change cclkrate ? Assume we have a cclkrate=100000. A clock interrupt will occur every (100000/1000000) 1/10th milliseconds. Assuming a task started with 1 tick which can go up to "ctimemax=1500" ticks can potentially take 1/10us * (1500 + 1) ticks which will be 150 milliseconds or approx. .15 milliseconds per task. Now changing the cclkrate to 75000 A clock interrupt will occur every (75000/1000000) 1/7th milliseconds. Assuming a task started with 1 tick which can go up to ctimemax=1500 ticks can potentially take 1/7us * (1500 + 1) ticks which will be 112 milliseconds or approx. .11 milliseconds per task. Decreasing the cclkrate value will decrease the time spent on each task. If the task could not voluntarily yield within the time, the scheduler will kill the task. UNDER NO CIRCUMSTANCES the cclkrate value should be changed. The default ctimemax value should be set to 1500. This is an empirical value and this can be changed under special circumstances and strictly under the guidance of DSE. cfgdbname: Name of the master device is saved here. This is 64 bytes in length. cfgpss: This is a derived value from cusrconnections + 3. See cusrconnections above. cfgxdes: This value defines the number of transactions that can be done by a task at a given instance. Changing this value to be more than 32 will have no effect on the server. cfgsdes: This value defines the number of open tables per task. This will be typically for a query. This will be the number of tables specified in a query including subqueries. Sybase Advises not to change this value. There will be significant change in the size of per user resource in ASE. cfgbuf: This is a derived variable based on the total memory configured and subtracting different resource sizes for Databases, Objects, Locks and other Kernel memories. cfgdes: This is same as cdesnum. Other values will have no effect on it. cfgprocedure: This is a derived value. Based on cpcacheprcnt variable. cfglocks: This is same as clocknum. Other values will have no effect on it. cfgcprot: This is variable that defines the number of cache protectors per task. This is used internally by ASE. Sybase advise not to modify this value as a default of 15 will be more than sufficient. cnproc: This is a derived value based on cusrconnections + <extra> for Sybase internal tasks that are both visible and non-visible. cnmemmap: This is an internal variable that will keep track of ASE memory. Modifying this value will not have any effect. cnmbox: Number of mail box structures that need to be allocated. More used in VMS environment than UNIX environment. cnmsg: Used in tandem with cnmbox. cnmsgmax: Maximum number of messages that can be passed between mailboxes. cnblkio: Number of disk I/O request (async and direct) that can be processed at a given instance. This is a global value for all the engines and not per engine value. This value is directly depended on the number of I/O request that can be processed by the Operating System. It varies depending on the Operating System. cnblkmax: Maximum number of I/O request that can be processed at any given time. Normally cnblkio,cnblkmax and cnmaxaio_server should be the same. cnmaxaio_engine: Maximum number of I/O request that can be processed by one engine. Since engines are Operating System Process, if there is any limit imposed by the Operating System on a per process basis then this value should be set. Otherwise it is a noop. cnmaxaio_server: This is the total number of I/O request ASE can do. This value s directly depended on the number of I/O request that can be processed by the Operating System. It varies depending on the Operating System. csiocnt: not used. cnbytio: Similar to disk I/O request, this is for network I/O request. This includes disk/tape dumps also. This value is for the whole ASE including other engines. cnbytmax: Maximum number of network I/O request including disk/tape dumps. cnalarm: Maximum number of alarms including the alarms used by the system. This is typically used when users do "waitfor delay" commands. cfgmastmirror: Mirror device name for the master device. cfgmastmirror_stat: Status of mirror devices for the master device like serial/dynamic mirroring etc. cindextrips: This value determines the ageing of a index buffer before it is removed from the cache. coamtrips: This value determines the aging of a OAM buffer before it is removed from the cache. cpreallocext: This value determines the number of extents that will be allocated while doing BCP. cbufwashsize: This value determines when to flush buffers in the cache that are modified. (Continued on next question...)
Other Job Interview Questions
|
||||
![]() |
![]() |
![]() |
|||