©  2021  Mullins Consulting, Inc.   All Rights Reserved   Privacy Policy   Contact Us

Database Administration and Storage Management

by Craig S. Mullins

Although storage management can be an afterthought for the DBA it really shouldn’t be. The cost of managing storage can be as much as 10 times higher than the initial cost of acquiring the storage. And the growth rate for disk storage was 37 percent between 1996 and 2007. So storage issues are vitally important and unless managed appropriately it can be very costly. Even so, it is common for storage-related issues to be relegated to the backburner by DBAs. But every database professional should understand modern storage basics.

A DBMS must be store data persistently in files or data sets of some sort. Depending on the DBMS, table spaces and index spaces each may require one, or possibly more, files to store the actual data. Some DBMS products combine multiple table spaces into a single O/S file. But there are many other storage-related objects you will encounter with database systems, including storage groups for interoperating with the storage subsystem, system-levels files and data sets (logs, system parameters, etc.), image copy backups (stored on disk or tape), and others such as library data sets and temporary data sets.

Storage-related information is available to DBAs from multiple sources including the System Catalog, statistics, and operating system utilities, but the details are scattered all over the place and it can be difficult to gain a complete, accurate, and up-to-date picture. Furthermore, any historical view into storage usage by the DBMS typically must be managed manually.

A responsible DBA with a comprehensive storage strategy will be able to ensure that all databases have sufficient allocation to satisfy business requirements. They will be able to answer questions like “Why is database storage growing when our business is not?”  Wasted storage will be minimized and a proactive approach to adding more storage when required will be adopted.

Today’s modern storage architecture uses disk arrays, or RAID (Redundant Array of Independent Disk). An array is the combination of two or more physical disk devices in a single logical device or multiple logical devices. The array is perceived by the system to be a single disk device.

And what about extents? When a file or data set reaches its allocated size the operating system can acquire an additional extent thereby increasing the size of the file. Many believe that modern storage devices render extent management obsolete, but that is not exactly true. For one thing, many of the latest extent management features work only with modern system managed storage and the most up-to-date version of the DBMS. For example, consider mainframe DB2. As of z/OS 1.7 system-managed data sets can have up to 123 extents on each of 59 volumes for a total of 7,257 extents. Otherwise the limit remains 255. Also, extent consolidation, introduced in z/OS 1.5, requires SMS-managed STOGROUPs. When a new extent is adjacent to old, they will be merged together automatically. This can result in some extents being larger than the PRIQTY or SECQTY specification.

Even if all database storage is system managed, extents can impact performance. Elapsed time can increase with multiple extents if there is heavy insert activity. For reads and updates the number of extents should not impact performance. Regardless, you no longer need to continuously monitor extents and clean them up immediately by reorganizing. It is still a good practice to periodically clean up extents, but there are other methods of reducing extents that are quicker and easier than REORG.

In terms of storage best practices, it is a good idea to perform regular and proactive monitoring. Examples of things you should be tracking include: 1) space used by your entire DBMS, individual databases, and of your table spaces and indexes; 2) monitoring the Storage Groups and the associated volumes of a database system or instance; 3) monitoring all underlying data sets and files for all table spaces and indexes including Used, Allocated, Primary and Secondary Quantity, Extents and the Volumes they are on; 4) alerts for Page Sets of table spaces and indexes that reach their maximum size and maximum number of data sets; 5) tracking of image copy backup data sets, including HSM migration; and 6) a way to delete Image copy backup datasets that are no longer needed because of they have been removed from the system catalog or backup management software.

Whenever possible, create alerts to automatically inform you of problems, shortages, and potential errors. Automate remediation tactics so that the alert tells you what happened, as well as what was done to correct the issue. Tools may be able to assist in automating reaction to shortages, potential errors, superfluous data sets, etc.

The better the database system works with the storage systems, the better your database applications will perform. And that is what it is all about, right?

From Database Trends and Applications, June 2013.

© 2013 Craig S. Mullins,  

June 2013