VERITAS Database Edition for DB2 and VERITAS Database Edition / HA for DB2 Version 1.0.2 for AIX
Product Evaluation
By Philip K. Gunning Principal Consultant Certified Advanced Technical Expert DB2 UDB V8.1
Gunning Technology Solutions, LLC IBM Business Partner October 23, 2003
1
Introduction In today’s Internet enabled, on-demand, e-Commerce environment, customers demand worldwide access to accounts, products, and services 24 hours a day, 7 days per week – via PC or hand-held device. This requires the underlying databases to be highly available and reliable, providing access to accounts, products or services anywhere at anytime, and meeting nearly instantaneous response time requirements. VERITAS Software Corporation has responded to this challenge by providing a suite of products for DB2 UDB environments that includes on-line storage management (volume manager, file system), high availability, clustering and database I/O performance-boosting technologies. Gunning Technology Solutions conducted a product evaluation of VERITAS Database Edition for DB2 and VERITAS Database Edition / HA for DB2 on the AIX platform. VERITAS Database Edition for DB2 UDB for AIX Version 1.0.2 The base Database Edition for DB2 product suite includes VERITAS Volume Manager, VERITAS File System, (known collectively as VERITAS Foundation Suite), along with Quick I/O, and Cached Quick I/O – VERITAS File System technologies that accelerate database performance and are unique to VERITAS Database Edition. High availability via clustering is provided with the Database Edition / HA for DB2 version which includes VERITAS Cluster Server™ (VCS) and the VERITAS Cluster Server™ Enterprise Agent for DB2. VERITAS Database Edition for DB2 – On-line Database Storage Management
VERITAS Database Edition for DB2™ provides several features over and above that of most OEM logical volume managers. For instance, VERITAS Volume Manager™ in conjunction with VERITAS File System™ , not only provides on-line creation and expansion of file systems, underlying volumes and containers to support rapidly growing DB2 databases, but actually enables a file system to be reduced in size without all the manual and time consuming steps required of logical volume managers that don’t support this function. VERITAS Volume Manager™ also provides, through the VERITAS Enterprise Manager graphical user interface (GUI) and command line interface, a mapping of logical volumes, file systems, and physical disks. This makes it easy for a
2
DBA to know how storage is configured for a particular database. Dynamic Multi-pathing (DMP) for data on disks with multiple host-to-device paths provides for higher availability when a path becomes temporarily unavailable. DMP dynamically routes the request over the available path. DMP also includes I/O load balancing across multiple paths to the disk device. By uniformly balancing I/O across multiple paths, improved performance may be obtained when using disk arrays such as IBM ESS and EMC Symmetrix. The use of these features can benefit DB2 databases by providing improved I/ O throughput. VERITAS Volume Manager™ can also implement concatenated volumes, RAID-1, RAID 0+1, RAID 1+0, and RAID-5. RAID levels can also be easily reconfigured on-line to support specific DB2 performance or availability requirements.
VERITAS Database Edition for DB2 - File System Manageability and Database Performance VERITAS Database Edition for DB2 provides several features that can significantly simplify and reduce the workload of DB2 database administrators and system administrators and significantly improve the performance and manageability of DB2 databases. The inclusion of VERITAS File System™ enables a system administrator to shrink a file system and offers both external and internal file defragmentation to improve performance and provide efficient use of storage. VERITAS File System™ uses extentbased allocation for improved performance and fast recovery. VERITAS Quick I/O™ Through the VERITAS Database Edition for DB2 Quick I/O feature, DMS file containers can be treated like they are raw devices. The Quick I/O feature is implemented through a character mode device driver and file container naming convention. Files designated for Quick I/O are recognized by the VERITAS File System™ and opened in raw mode. The Quick I/O driver then uses the same methods to access the table data as would be used with a raw device. DMS file containers converted to use Quick I/O can achieve the performance of raw devices; without the management and complexity involved with implementing actual raw devices. This removes the previous barriers that have prevented many a DB2 DBA from implementing raw devices in order to achieve high performance. VERITAS TPC-C like benchmarking has demonstrated that Quick I/O can achieve raw I/O device equivalent performance without the headaches of managing raw devices. DB2 DBAs can significantly increase the performance of DB2 databases using the Quick I/O feature. There are two ways to use Quick I/O:
3
• •
DBAs can create Quick I/O files first, then create DMS tablespaces using the Quick I/O file as FILE or DEVICE containers Existing DMS file containers can be converted by DBAs to use Quick I/O
The conversion of existing DMS tablespace containers to use Quick I/O is fairly straightforward. This can be done using VERITAS utilities, qio_getdbfile and qio_convertdbfile. The qio_getdbfiles command needs to be run which creates a file containing a list of tablespace container files. Using this list as input, the qio_convertdbfile command converts the file containers to use Quick I/O. The qio_convertdbfile utility is quite fast and runs in seconds. After the qio_convertdbfile command has been run, the database can be started. This type of activity can easily be done during a scheduled maintenance window or at initial database creation. To add a container to an existing Quick I/O enabled tablespace, the qio_convertdbfile command is run against the file container. The tablespace must be offline. After running qio_convertdbfile the DBA would then alter the tablespace to use the newly enabled Quick I/O container. Quick I/O is available for DB2 UDB V7.2 and DB2 UDB V8 ESE including the Database Partitioning Feature (DPF) in AIX environments. Quick I/O is supported on AIX 5.1 and above. VERITAS Cached Quick I/O Through the Cached Quick I/O feature, DB2 DBAs can take DB2 database I/O performance to an even higher level. Cached Quick I/O exploits main memory that is not used by the operating system or DB2 bufferpools. Many AIX servers running DB2 have 8, 16, or even 32GB of RAM. DB2 UDB EE V7.1 or 7.2 or even DB2 UDB ESE V8.1 in 32-bit mode can’t use more than 1.75GB of shared memory. The operating system uses some of this large main memory for file caching and OS kernel routines, but there is usually quite a bit left unused. Cached Quick I/O can take advantage of this unused memory and use it to cache DB2 data, essentially providing a second level of cache to DB2 bufferpools resulting in faster I/O and improved response times. Depending on the size of the bufferpool and storage configuration, performance improvements of up to 60%+ improvement over raw devices and JFS configured as RAID 1 have been observed, and more than 2x improvement over raw devices and a 4x improvement over JFS in JBOD have been observed using Cached Quick I/O. Cached Quick I/O is available for DB2 UDB V7.2 and DB2 UDB V8 ESE including the Database Partitioning Feature (DPF) on AIX. Cached Quick I/O is supported on AIX 5.1 and above.
VERITAS Database Edition / HA for DB2 – High Availability ™
4
DB2 databases can achieve high availability through the use of VERITAS Cluster Server™ , which is included with the Database Edition / HA product suite. VERITAS Cluster Server Enterprise Agent for DB2™ is an optional feature of VERITAS Database Edition /HA for DB2. High availability and clustering is provided by VERITAS Cluster Server Enterprise Agent for DB2™ and the VERITAS Database Edition / HA for DB2™ product. Up to 32 partitions are supported in a DB2 UDB V8 ESE DPF environment. Currently, only DPF on a single SMP machine is supported. A future release will support DPF on multiple physical machines. At the heart of VERITAS Cluster Server™ is a VERITAS developed protocol stack consisting of Low Latency Transport (LLT) and Group Membership and Atomic Broadcast (GAB) communication protocols. LLT does not use IP frames but accesses the network hardware directly through the Data Link Provider interface on all UNIX platforms. LLT is a highly reliable protocol that requires acknowledgements for all messages sent to cluster members. If an acknowledgment is not received, LLT notifies GAB, which will then analyze the state of all members in the cluster. This is accomplished by GAB sending atomic broadcast messages to all members in the cluster. If GAB detects a member failure, all members in the cluster are notified. Heartbeat detection is controlled via two time-related parameters called stable-timeout, at the GAB level and peerinact at the LLT level. The lower the value of these parameters the faster GAB is able to detect a failure and initiate a takeover. VERITAS fail over tests have demonstrated a fail over time as short as 7 seconds. Even very large databases with large numbers of file systems/ volumes can benefit from this fast fail over technology and the whole process is automatic. VERITAS Cluster Server can also be used to conduct manual fail overs when accomplishing OS maintenance or database upgrades. With the advent of utility computing, the continuous availability of mission-critical systems and databases has to be almost guaranteed due to the business’s relying on resources to be available in a 24 x 7 world. VERITAS Cluster Server™ goes a long way in providing this capability. VERITAS™ FlashSnap™ – Off-Host Processing The FlashSnap feature offers DB2 DBAs an alternative to replication or restoration of backups when creating databases for reporting or backup copies of the database. With FlashSnap, split-mirror snapshots can be created, moved to a secondary host, and then used with minimal impact to production systems. Split-mirror snapshots are full copies of production data. They can be accessed on the same server or used off-host on a different server. A split mirror is created by issuing the DB2 write suspend command and then taking the snapshot followed by the DB2 write resume command. The mirror can then be used in conjunction with the DB2 db2relocatedb and bringing up the mirror as a clone using the db2inidb as snapshot command. The snapshot database can then be used for
5
ad-hoc reporting purposes, report generation, database backup or testing purposes. The FlashSnap “FastResync” feature tracks changed blocks between the original mirror and the split-mirror snapshot. So, when an old snapshot is reattached to a server, it can be resynchronized very quickly as only the changes are resynchronized. The FlashSnap “ReverseResync” feature can be used to resynchronize the original database with the snapshot copy. A DBA can then create another split-mirror snapshot to use for subsequent off-host processing as previously discussed. FlashSnap is ideal for snapping off copies of OLTP databases to create an ad-hoc reporting database. This enables the production OLTP database to process transactions without contention caused by ad-hoc queries and report generation tools; instead they can be run against the copy created by FlashSnap. FlashSnap also helps eliminate database backup windows and frees DBAs and system administrators for other time consuming tasks. A future release of FlashSnap will completely automate the snapshot and associated database and OS commands.
Storage Checkpoint works by instantly creating an exact image of the database and the underlying VERITAS File Systems™ . VERITAS Storage Checkpoint™ is disk-and I/ O-efficient, providing a consistent, stable, point-in-time view of file systems via the copy-on-write technique. Because storage checkpoints offer a cost-effective mechanism to provide snapshots of database images, it is possible to take a virtually unlimited number of storage checkpoints and keep them available. Storage checkpoints are persistent; they will still be available after reboot. A storage checkpoint uses the same storage pool as the original file system. Combined with the online resizing capability of VERITAS File System™, storage requirements for storage checkpoints can be easily monitored and managed. A storage checkpoint of a “live” file system or the primary file system provides an instant, exact image of the file system. A storage checkpoint is created by freezing the primary file system, initializing the Storage Checkpoint’s block map and thawing the primary file system. Freezing the file system temporarily blocks all I/O operations to the file system; current or pending I/O operations to the file system are completed and the file system is synchronized to disk. After initializing the Storage Checkpoint’s block map, pointing to data blocks of the primary file system, the file system is then “thawed” to allow continued access. This operation is atomic, maintaining write ordering. When the storage checkpoint is first taken, the storage checkpoint has the block map and contains no data blocks, i.e., the storage checkpoint is empty, requiring storage only for the block map, which is about 1% of the primary file system. Since only the block map is needed and no data is copied, taking a Storage Checkpoint is very fast, typically a couple of seconds.
6
Summary In summary, I found that the Database Edition for DB2 (and the Database Edition /HA for DB2) ™ product contains all the features a DBA or system administrator would need to achieve high availability and superior performance for DB2 database and DB2 operating system platforms. FlashSnap™and VERITASs Cluster Server™ combine to form a very powerful high availability and backup/recovery solution. Storage Checkpoint™ enables a DBA or system administrator to make flexible use of existing storage and to easily support the various types of system development life cycle testing requirements while freeing-up time to perform other important DBA and system administrator tasks. The Quick I/O and Cached Quick I/O features of VERITAS Database Edition for DB2/HA enable DBAs to significantly enhance the performance of databases under their control. Since most databases spend significant time waiting for I/O, these two products can give your databases a serious performance boost by undoing the I/O bottleneck. The VERITAS Volume Manager™ and Veritas File System™ combine to offer DBAs and systems administrator a highly flexible solution for managing file systems, disks and general storage requirements, over and above what most other volume mangers provide. .
Gunning Technology Solutions LLC, is the world leader in DB2 UDB consulting.. Gunning Technology Solutions, specializes in Performance Reviews, Tuning Engagements, Database Administration, Quick Start to DB2 UDB course, Oracle conversions, customized education, and knowledge transfer. Philip K. Gunning, principle consultant, has many years of DB2 experience and recently authored the DB2 UDB V8 Handbook for Windows, UNIX, and LINUX, August 2003, IBM Press. He is also coauthor of the IBM Redbook, DB2 V8 Performance Guide for OLTP and BI, to be published in November 2003. Phil has also written articles for DB2 Magazine and DBAzine. Phil is a member of the IDUG North American Conference Planning Committee and Chief List Admin of the IDUG worldwide DB2-L listserv where he is an active participant. Phil also maintains the DB2ZONE at http://www.gunningts.com/ db2zone.htm where many tips on how to succeed with DB2 can be found.
7
8