Estimating Shared Memory Requirements

Each time a multi-user DBEnvironment session is started with either the START DBE statement or the first CONNECT statement, if the autostart option is set to on, a block of shared memory is reserved for this ALLBASE/SQL session. Until the DBEnvironment session is ended, all users and programs accessing the DBEnvironment share this allocated memory. This memory remains reserved until the DBEnvironment is stopped, at which point the memory is made available for re-use by the system. If AUTOSTART is ON, memory remains reserved until the last DBE session open against the DBEnvironment terminates. If AUTOSTART is OFF, memory remains reserved until the DBA issues the STOP DBE statement.

ALLBASE/SQL uses shared memory for three types of buffers:

Runtime control block buffer
Data buffer
Log buffer

You can specify how much shared memory is to be allocated to each buffer when you create the DBEnvironment with the START DBE NEW statement. The parameters you specify for ControlBlocks, DataBufferPages, and LogBufferPages are stored in the DBECon file.

A DBA can temporarily override several of the parameters that comprise shared memory by using the START DBE statement, or alter each of the shared memory parameters by using the SQLUtil ALTDBE command. Initial memory allocation and system configuration is performed before installation of the product.

Estimating Runtime Control Block Buffer Pages

Each type of DBCore service requires a specific number and size of control block buffer pages. The number and type needed at any one time depends on such factors as the number, duration, and type of concurrent transactions, the amount of row or page level locking, and the amount of update activity occurring. Whether or not a runtime control block buffer page is available for re-use by the system depends on the type of runtime control blocks being used. Transaction lock control block pages may not be available for re-use until after the transaction successfully terminates with either a COMMIT WORK or a ROLLBACK WORK statement.

Lock management is the single greatest user of shared memory. The greater the number of concurrent locks held, the greater the number of runtime control block buffer pages needed to manage these locks. Consequently, a program designed to have shorter transactions, coarser lock granularity, or more efficient concurrency practices is less likely to deplete the amount of shared memory available.

Effects of Page and Row Level Locking

Page level locking uses more runtime control blocks than table level locking, since each page must be locked. Row level locking uses even more runtime control blocks than page level locking, since each row must be locked individually. This can cause the allocation of a considerable amount of shared memory. The following indicates the maximum number of locks associated with table, page, and row level locking:

Table 3-5 Maximum Numbers of Locks Obtained at Different Granularities

Locking Level	Maximum Number of Locks
Table Level	1
Page Level	`n` + 1
Row Level	`m` + (`n` + 1)
	where `n` is the number of pages in the table and `m` is the number of rows in the table.

Table level locking requires a single lock. Page level locking requires up to n page level locks plus one intention lock at the table level. Row level locking requires up to m row level locks plus up to (n + 1) intention locks at the page and table levels. Because row level locking on a large table can consume a tremendous number of runtime control blocks, the use of the PUBLICROW table type on large tables is discouraged. Large tables for which maximum read/write concurrency is desired should generally be defined as PUBLIC. The PUBLICROW table type should generally be reserved for use on small tables.

As an illustration, consider a table that occupies 500 pages in a DBEFile. Assume that each page holds 20 rows. Suppose that 40% of all rows are affected by concurrent activity using index scans at the RR isolation level on this table--that is, at any one time about 40% of all rows are being read or updated. Further assume that these rows are spread out among 80% of the pages in the table.

With page level locking, the number of runtime control blocks used is as follows:

RCB = 500 * .8 = 400

Adding one for the table level intent lock, the total is as follows:

Total = 400 + 1 = 401

If each RCB occupies 90 bytes, the total memory required would be 36,090 bytes, or 9 runtime control block buffer pages.

With row level locking, the number of runtime control blocks used is as follows:

RCB = 500 * 20 * .4 = 4,000

In addition to row locks, you need to add the number of page locks (intent locks) from the earlier calculation. Finally, you should add one intent lock for the table.

The total is as follows:

Total = 4,000 + 400 + 1 = 4,401

Then, if each RCB occupies 90 bytes, the total memory required would be 396,090 bytes, or 97 runtime control block buffer pages.

In addition to the shared memory required for locks, row level locking also requires additional CPU time to fetch and release the locks.

Running out of Shared Memory

The DBCore allocates memory for runtime control blocks in 4 Kilobyte pages. More specifically, the DBCore can allocate up to 72 4-Kilobyte pages of memory for the control structures for the data buffer pages, the log buffer pages, and the runtime control block pages.

When the DBCore cannot obtain the required number of runtime control block buffer pages, the transaction requesting the additional shared memory is rolled back and ALLBASE/SQL returns the error code -4008.

Because the conditions that caused the DBEnvironment to run out of shared memory may not exist if the transaction is simply restarted, the programmatic user can check for this error return code value and re-execute the program a finite number of times if it occurs. The ISQL user can simply re-execute the transaction.

If a DBEnvironment consistently runs out of shared memory, you can increase the number of runtime control block buffers by 20 percent and re-try the affected transactions. If you are using large PUBLICROW tables, you can use the ALTER TABLE statement to convert to PUBLIC mode. With large LOAD and INSERT operations, use the LOCK TABLE statement with the EXCLUSIVE option to avoid depleting shared memory.




	NOTE: The minimum number of runtime control block buffer pages is 17. The maximum is 2,000. The default is 37.

The number of 4096-byte pages in the runtime control block buffer is set using the START DBE NEW statement or the SQLUtil ALTDBE command. When you specify values for these parameters in START DBE and START DBE NEWLOG, you do not update the value stored in the DBECon file but change the value for the current DBEnvironment session only.

Estimating Data Buffer Pages

During query processing, pages from DBEFiles currently being accessed are held in the data buffer. The number of 4 Kilobyte pages to be allocated in the data buffer is specified in the BUFFER clause of the START DBE NEW statement. This number should be based on the number of concurrent users and the type of applications. You should start with a number of data buffer pages equal to slightly more than the maximum number of concurrent users on your system.

Each transaction may need from one to several buffer pages depending on the type of query being processed. The more complex a query the more buffer pages are needed. For a complex query, the required number of data buffer pages may be from 5 to 15 times the maximum number of concurrent transactions. Because some of the buffer pages are shared in a multiuser mode, the page requirement per user decreases as the number of users increases.




	NOTE: The minimum number of data buffer pages is 15. The maximum number is 50,000. The default is 100. You can now allocate enough data buffer pages to keep a whole DBEnvironment in memory, if necessary.

The number of pages in the data buffer can be temporarily overridden with the START DBE statement. The ALTDBE command in SQLUtil allows you to permanently change the number of buffer pages.

Setting the Memory Resident Buffer Flag

You can force data buffer pages to remain memory resident by setting the MemoryResidentEnabled flag in the DBECon file to YES. Setting the MemoryResidentEnabled flag to YES may improve database performance on machines with a small amount of memory. However, system virtual memory swapping overhead may increase for other applications on the system, since a part of the memory frame pool becomes unavailable.

Setting the MemoryResidentEnabled flag to YES is useful for database servers where very little is going on besides database work, and the database buffers occupy a small fraction of real memory in the system.

Use the SQLUtil ALTDBE command to change the setting of the Memory Resident Buffer Flag, as in the following example:



   >> altdbe



   DBEnvironment Name:  PartsDBE

   Maintenance Word: 

   AutoStart Mode (on/off) (opt):  

   User Mode (opt): 

   DDL Enabled (y/n) (opt):  

   No. of Runtime Control Block Pages (opt):

   No. of Data Buffer Pages (opt): 

   Data Buffer Pages Memory Resident (y/n) (opt): yes

   No. of Log Buffer Pages (opt):  

   Max. Transactions (opt):  

   Maximum Timeout (opt): 

   Default Timeout (opt): 

   Authorize Once per Session (on/off) (opt): 

   Alter DBEnvironment Startup Parameters (y/n)?  yes



   DBEnvironment startup parameters altered.

Estimating Log Buffer Pages

The log buffer holds before- and after-images of pages that are changed during a transaction. You specify the number of 512-byte pages for the log buffer in the BUFFER clause of the START DBE NEW statement.

In deciding the number of log buffer pages, you should consider the duration of a typical transaction, that is, the time between a BEGIN WORK statement and its corresponding COMMIT WORK statement. As log records are generated during a transaction, they are kept in the log buffer until any one of the following occurs:

A COMMIT WORK is performed.
A CHECKPOINT is performed.
All the log buffers are full.

When any one of the above occurs, the log buffer pages are written to the log file. Once transactions in the buffer are written to disk, the buffer pages can be used again. If transactions are short, the number of log buffer pages need not be very large, since the log records will be written to disk frequently. However, if there are lengthy transactions and few log buffer pages, transactions spend time forcing log records to disk.

A minimum of 24 log buffer pages is required; this is the default value supplied by ALLBASE/SQL. You can request up to 1024 log buffer pages. You can temporarily override the number of log buffer pages with the START DBE statement. The ALTDBE command in SQLUtil allows you to permanently change the number of buffer pages. For more detailed discussion of data buffer page size, refer to the ALLBASE/SQL Performance and Monitoring Guidelines.