|
|
|
|
|
|
|
|
ALLBASE/SQL Database Administration Guide: HP 9000 Computer Systems > Chapter 3 Physical Design
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:
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.
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. 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
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:
Adding one for the table level intent lock, the total is as follows:
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:
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:
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. 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.
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. 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.
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. 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:
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. This section discusses the system configurable parameters which directly affect the execution of ALLBASE/SQL. These parameters are part of the system configuration and can be modified using SAM. Please refer to the System Administration Tasks HP 9000 for more information. You may need to increase parameter values to meet your needs. When you use multiconnect functionality, your applications can use up HP-UX system resources quickly. Be sure to allocate a sufficient number of shared memory segments and semaphores for your system. The system parameters namely semmni, semmns, shmmni and shmseg and their uses by ALLBASE/SQL are explained in Table 3-6 “System Parameters Used by ALLBASE/SQL”. The formulas listed may help you determine the optimal numbers for your system. Table 3-6 System Parameters Used by ALLBASE/SQL
There are several other system parameters which are not directly affected by the execution of ALLBASE/SQL, but may be indirectly affected by an ALLBASE/SQL user's application. These include: nproc, nfile, and ninode. Table 3-7 Additional System Parameters
If you are running more than 32 users on your system, you may require additional swap space. You can use the formulas to calculate the heaviest expected use of your system, and then set the parameters accordingly. Refer to the System Administration Tasks HP 9000 for information on memory allocation and system reconfiguration. After a user makes the first connection to the DBEnvironment, you can enter the HP-UX command ipcs -sb to display the number of semaphore sets used. The result would be similar to this one:
Each line in the display represents a semaphore set, and the NSEMS field in each line represents the number of semaphores in the set. The first semaphore set contains one semaphore that is used for interprocess communication between the application and an hpsqlproc process. The owner of the semaphore is the user running the application. Each connection has one such semaphore set. The second semaphore set contains two semaphores and has the owner hpdb and the group hpsql. Each connection also has a semaphore set like this one. The third semaphore set belongs to an sqldaemon process, which monitors the DBEnvironment and cleans up shared memory and semaphores when necessary (for example, if a connection is lost). Now suppose a second connection is made to the DBEnvironment. The command ipcs -sb would display a list similar to this one:
Note that additional semaphores were added at the end of the list. However, there is still only one sqldaemon process for the DBEnvironment, the third semaphore set in the list. Therefore, there are only 3 additional semaphores, not 5. The sqldaemon will only clean up semaphores owned by hpdb. Ordinarily, when a connection is released, the hpsqlproc process releases the semaphores associated with its connection and with the application. If the hpsqlproc process terminates unexpectedly, the sqldaemon removes the semaphore associated with the interprocess communication. The sqldaemon checks each hpsqlproc process every 30 seconds. If both the sqldaemon and hpsqlproc processes are terminated, the semaphore associated with the connection is not released. In the display, you can see the semaphores associated with the connection the sqldaemon makes to the DBEnvironment, because the connection is maintained as long as the sqldaemon exists. If the sqldaemon cannot connect to the DBEnvironment because it cannot obtain a semaphore, it aborts, generating a DBCore error. In this case, the sqldaemon does not release the semaphores and shared memory that were used for the DBEnvironment. If you set semmap too low, the message
may appear on the console. Example. Assume a system in which there are 3 DBEnvironments and 5 users who would like to use the multiconnect feature. Each user is running one application with a maximum of 32 connections. The following system resources are recommended:
This is in addition to the shared memory segments, processes, and semaphores needed for other system uses. For more information on system reconfiguration, refer to "Changing Kernel Parameters" in the System Administration Task Manual for your HP-UX system. For more information on ALLBASE/SQL system parameters, refer to the section "Estimating Shared Memory Requirements" in this chapter. |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
||
|
|
|
|||||||||
|
|||||||||
