Maintaining Local Databases
You can review and maintain local databases on the Databases page of the Management Portal (System Operations > Databases). From this page, you can view the following information:
-
Databases General Information — Overview information for all databases.
-
Databases Free Space Information — Free space information for all databases.
-
Database Details Page — Specific information for individual databases. You can also perform maintenance operations to increase free space from this page.
You also have the option to perform an integrity check using the buttons at the top of the page. For more information, see Verifying Structural Integrity.
Most of the information and operations described in this section can also be found and performed using the ^DATABASE command line utility.
Databases General Information
The Databases page (System Operation > Databases) contains a list of all local databases. For each local database, you see the following information:
Column Heading | Definition |
---|---|
Name | The database name; click this name to display more details. See Database Details Page. |
Directory | The system directory in which the database resides. |
Max Size (GB) | The maximum size allocated to which the database can grow, in gigabytes. |
Size (MB) | The current allocated size of the database, in megabytes.
Note:
This field measures the logical size of the database. As a result, the size reported here may be lower than the physical size of the database, in particular for the IRISTEMP database. |
Status |
The status of the database: mounted (including which permissions it has), unmounted, or dismounted.
|
Encrypted | Indicates whether or not the database is encrypted. |
Journal | Indicates whether globals in the database are journaled with a Y or an N. |
In addition, the page contains a filter bar that you can use to control the number of databases displayed. For example, to list only the system databases, you might enter IRIS* in the Filter: text box; and/or to list only five databases per page, enter 5 in the Page size: text box; and/or to limit the number of rows displayed to three, enter 3 in the Max rows: text box (a + sign displayed with the number in the Results field indicates there are additional databases that meet the specified criteria, but they are not displayed).
Database Free Space Information
Managing the free space (empty blocks) in a database is an important aspect of database maintenance. To see free space information, you can display the Free space view of the Management Portal or use the ^%FREECNT utility. Remember that the size and free space attributes of a database in normal operation change continuously, and that numbers reported by the Portal or the utility at a given point in time are approximations only.
If you determine that a database has more free space than is necessary, you may compact and truncate the database as described in Database Details Page.
Display Free Space Information Using the Management Portal
To display free space information, which shows information about the amount of free space on each local database, navigate to the Databases page (System Operation > Databases) and click the Free space view radio button. The following table describes the information displayed:
Column Heading | Definition |
---|---|
Name | The database name; click this name to display more details. See Database Details Page. |
Directory | The system directory in which the primary volume of the database resides. |
Max Size | The maximum allocated size to which the database can grow, in gigabytes. The default is unlimited when you create a database. |
Size | The current allocated size of the database, in megabytes.
Note:
This field measures the logical size of the database. As a result, the size reported here may be lower than the physical size of the database, in particular for the IRISTEMP database. |
Expansion Size | Size (in MB) by which to expand the database. The default and recommended setting is zero (0) when you create a database, which indicates the use of system defaults (12% of the current size or 10 MB, whichever is larger). Under this setting, the expansion size will not be greater than 1GB. |
Available | The amount of free space (in MB) available in the database. |
% Free | The percentage of free space available in the database. |
Disk Free Space | The amount of space free on the volume. |
Status | The status of the directory, which indicates if the database is mounted and with what permissions. |
For information about performing free space management operations from the Management Portal, see Database Details Page.
Display Free Space Information Using ^%FREECNT
InterSystems IRIS also provides the ^%FREECNT utility, which you run by entering do ^%FREECNT in the Terminal, to display the free space available in a database.
When using ^%FREECNT in the %SYS namespace, you can choose to display the free space of all databases by entering an asterisk (*) at the prompt, or enter one database directory name. For example:
%SYS>do ^%FREECNT
Database directory to show free space for (*=All)? *
Databases Selected
------------------
c:\MyIris\mgr\
c:\MyIris\mgr\irisaudit\
c:\MyIris\mgr\irislib\
c:\MyIris\mgr\irislocaldata\
c:\MyIris\mgr\iristemp\
c:\MyIris\mgr\user\
Device:
Right margin: 80 =>
Database Free Space
Feb 15 2012 7:25 PM
Database Max Size Size Available %Free Disk Free
c:\MyIris\mgr\ Unlimited 191MB 19MB 9.94 60.79GB
c:\MyIris\mgr\irisaudit\ Unlimited 1MB 0.43MB 43 60.79GB
c:\MyIris\mgr\irislib\ Unlimited 319MB 27MB 8.46 60.79GB
c:\MyIris\mgr\irislocaldata\ Unlimited 1MB 0.55MB 55 60.79GB
c:\MyIris\mgr\iristemp\ Unlimited 4MB 1.5MB 37.5 60.79GB
c:\MyIris\mgr\user\ Unlimited 1MB 0.43MB 43 60.79GB
In a namespace other than %SYS, the utility shows the free space of the databases in that namespace. For example:
USER>Do ^%FREECNT
Databases Selected
------------------
c:\MyIris\mgr\user\
Device:
Right margin: 80 =>
Database Free Space
Feb 15 2012 7:28 PM
Database Max Size Size Available %Free Disk Free
c:\MyIris\mgr\user\ Unlimited 1MB 0.52MB 52 42.72GB
A <- flag (in the %Free column) indicates that the percentage of free space in the specified database has dropped below 5%. Ensure that there is enough space on the file system to handle database expansion.
The ^DATABASE utility is another way to display free space information, as well as perform other database tasks. For example, you can compact globals in a database, or you can recreate a database, which lets you clear the data in an existing database without changing the database’s name or size. See ^DATABASE for information about the utility.
The data structures used by InterSystems IRIS are self-balancing and suffer no performance degradation over time. It is never necessary to take a database down to rebuild it nor to compress data or indexes to regain performance.
Database Details Page
The Database Details page displays detailed information about any database. To access this information from the Management Portal:
-
Display the Databases page (System Operation > Databases).
-
Click the name of the database for which you would like to see more details.
Along the top of the Database Details page is a row of buttons, which allow you to perform various database operations:
-
Mount / Dismount — Mount or dismount a database. The new status remains in effect until you explicitly change it or restart/stop InterSystems IRIS; to permanently dismount a database, you must remove it from the configuration.
-
Compact — Move free space distributed throughout a database to its end.
-
Truncate — Return free space at the end of a database to the underlying file system.
-
Defragment — Relocate global blocks so that the blocks representing a given global are in continuous sequence.
The page also includes information specific to the selected database, which is organized into a General Information table and a Database Size table:
Field | Definition |
---|---|
Directory | Name of directory where this database resides. |
Resource Name | Resource name assigned to this database. |
Mounted | Indicates whether this database is mounted. |
Read Only | Indicates whether this database is mounted as read-only. |
Read Only Reason | The reason the database is mounted as read-only. |
Encrypted | Indicates whether this database is encrypted. |
Encryption Key ID | Indicates encryption key ID. |
Field | Definition |
---|---|
Block Size | Block size (bytes) for this database. |
Blocks | Current number of blocks within this database. |
Max Size | Maximum size (MB) allowed for this database. |
Size | Current size (MB) of this database. |
Expansion Size: | Amount (MB) this database will expand by. |
Available Space | Available space within this database. |
% Free Space | % Free space within this database. |
Disk Free Space | Free space on disk containing this database. |
Last Expansion Time | Last time this database expanded. |
Full | Indicates that this database is full. |
Compacting a Database
Compacting a database moves free space distributed throughout the database to its end by relocating global blocks. You can then return the free space to the underlying file system by truncating the database. (You can also compact globals; see Compact Globals in a Database.)
When you compact a database, you specify the amount of the available free space to be positioned at its end, and the operation rearranges global blocks to ensure that at least that amount of free space is located at the end. (The operation cannot create more free space, so it can never place more at the end than the total available amount.)
For example, suppose the size of a database is 50 MB, with 15 MB of that being free space, and 5 MB of that free space already positioned at the end of the database. If you compact the database and specify more than 5 MB but less than 15MB, global blocks are moved from the end of the database to the beginning until the free space at the end equals the amount you specified; if you specify 15 MB, all possible global blocks are moved to the beginning.
To compact a database:
-
Navigate to the Databases page (System Operations > Databases).
-
Click the name of the database you want to compact. This takes you to its Database Details page.
-
Click Compact on the ribbon at the top of the page. This displays the Compact Database dialog box, which shows the name and location of the database, its current size, the total available free space, and the amount of free space currently at the end of the file.
-
Specify the amount of free space you want at the end of the file in the Target free space (in MB) at end of file text box. Your entry must be within the stated range. Once you have entered an amount, click OK. If all of the free space is already at the end, or there is no free space, the prompt does not appear and the OK button is disabled.
Note:For a number of reasons, the operation may move more free space than the amount you specify. Conversely, because the numbers reported are approximations, it is possible that not all of the free space displayed can actually be moved.
-
When the task is complete, a Background Task Info dialog box appears. Optionally, you can also view the task on the Background Tasks page (System Operation > Background Tasks).
-
Click Close to redisplay the Database Details page. The page should automatically refresh to show the new database information.
The compact database operation is designed to run concurrently with normal database activity. The operation does consume some system resources, however, and may not complete if the system is under extremely high load. For these reasons, InterSystems recommends running this and other database reorganization operations (including compacting and defragmenting globals) during off-peak hours, and running only one such operation on a system at a time.
Truncating a Database
Truncating a database returns free space from the end of the database to the underlying file system. A database is often truncated after being compacted, which moves free space to the end of the database.
When you truncate a database, you specify a target size for the database. If there is sufficient free space at the end of database, the operation removes enough to reduce the database to the target size; if not, it removes all that can be removed. (To find out how much of a database’s free space is positioned at the end, compact the database; you do not need to complete the operation to display current total available free space and the amount at the end.)
This feature is not applicable to databases with raw volumes.
To truncate a database:
-
Navigate to the Databases page (System Operations > Databases).
-
Click the name of the database you want to truncate. This takes you to its Database Details page.
-
Click Truncate on the ribbon at the top of the page. This displays the Truncate Database dialog box, which shows the name, location, and current size in megabytes (MB) of the selected database.
-
Enter the Target File Size (MB), which must be less than the current size, and click OK. Enter 0 to remove all possible free space from the end of the file.
Note:Free space for truncation purposes is different than free space for storing data. Certain control structures can be deleted during truncation if they are not being used for data storage. This can lead to a difference in reported free space.
Defragmenting a Database
Defragmenting a database rearranges global blocks within the database so that all of the blocks containing data for a given global are in consecutive sequence. The operation does not place big string blocks or pointer blocks from a global in sequence, but it does locate them in a contiguous area. As part of the process, the Defragment a database option compacts all globals in the same manner as the Compact globals in a database option, but with a target density of 70%. (If this is lower than the current global block density of the database, the size of the database does not increase.)
The IRISTEMP database cannot be defragmented.
In general, it is not necessary to run defragmentation on any regular basis. Some workloads, however, particularly those that read large portions of a database sequentially, can benefit from having global blocks organized sequentially.
The defragmentation process requires a certain amount of free space at the end of the database. For this reason, the following possibilities exist:
-
If there is enough free space at the end to perform the operation, it completes without any changes to the database beyond global defragmentation.
-
If there is not enough free space in the database, the database is expanded as necessary. When defragmentation is complete, you can truncate the database to remove the added free space.
-
If there is not enough free space in the database but there is significant free space that could be moved to the end, you are informed of this. In this case, compacting the database before you choose the Defragment a database option reduces the amount of expansion required to complete the defragmentation operation.
To defragment the globals in a database, use the following procedure:
-
Navigate to the Databases page (System Operations > Databases).
-
Click the name of the database you want to defragment. This takes you to its Database Details page.
-
Click Defragment on the ribbon at the top of the page. This displays the Defragment Database dialog box, which shows the name, location, and current size in megabytes (MB) of the selected database, as well as a message describing the free space needed to defragment.
-
Click OK.
-
When the task is complete, a Background Task Info dialog box appears. Optionally, you can also view the task on the Background Tasks page (System Operation > Background Tasks).
-
Click Close to redisplay the Database Details page. The page should automatically refresh to show the new database information.
The defragment operation temporarily relocates all of the data in the database, regardless of the degree of global fragmentation in the database prior to running the operation. Subsequent runs of the operation consume similar amounts of resources but do not provide any additional benefit.
The defragment operation is designed to run concurrently with normal database activity. The operation does consume some system resources, however, and may not complete if the system is under extremely high load. For these reasons, InterSystems recommends running this and other database reorganization operations (including compacting a database and compacting globals) during off-peak hours, and running only one such operation on a system at a time.
Global defragmentation can involve a temporary increase in the size of the database being compacted. If this causes the database to reach its configured maximum size (see Local Databases), or if expansion is not possible because there is insufficient space available on the storage volume, the operation is canceled.
Compacting Globals in a Database
Another way to manage database space is to compact the globals in a database using the ^DATABASE routine. Compacting globals consolidates global data into fewer blocks, increasing the amount of free space in a database.
When globals are created and updated, InterSystems IRIS typically allocates data in a manner that fills global blocks to about 70% of capacity. (Globals that have grown entirely in collation order may be allocated at closer to 90%.) In general, allowing InterSystems IRIS to manage global block density automatically is sufficient. However, some nonsequential patterns of data deletion may reduce average global block density considerably.
To see the current density of the global blocks in a database on a global by global basis, you can run an integrity check (as described in Verifying Structural Integrity) and examine the Data Level output for each global.
When you compact globals, you specify a desired global block density (90% by default) and the operation attempts to come as close to this as possible by consolidating data—for example, rearranging global data that is spread across three blocks into two. Typically (but not always), compacting globals yields a meaningful increase in available free space within a database. (If you specify a target density that is lower than the current global block density of the database, the size of the database does not increase.)
To compact the globals in a database, use the following procedure:
-
Open the Terminal and change to the %SYS namespace.
-
Enter do ^DATABASE, and select 7) Compact globals in a database from the menu.
-
Specify the directory of the database on which you want to run the operation. You can specify multiple databases by entering ? at the Database directories to compact? prompt and then entering a list of numbers.
-
Indicate that you want to compact all globals, or instead enter a list of individual globals to be compacted.
-
Specify the target average global block density, respond to the rest of the prompts, and confirm.
The compact globals operation is designed to run concurrently with normal database activity. The operation does consume some system resources, however, and may not complete if the system is under extremely high load. For these reasons, InterSystems recommends running this and other database reorganization operations (including compacting a database and defragmenting globals) during off-peak hours, and running only one such operation on a system at a time.
Global compaction can involve a temporary increase in the size of the database being compacted. If this causes the database to reach its configured maximum size (see Local Databases), or if expansion is not possible because there is insufficient space available on the storage volume, the operation is canceled.