Blogs

Appliance-Based Analytics and Transactional Processing: Part 2

In organizations that maintain transactional operations, database development, administration, monitoring, and optimization are essential responsibilities. The first part in this series provides an overview of IBM® PureData® System appliances within the IBM PureSystems® family of pre-configured servers. This concluding part focuses on the tools available in PureData System for Transactions, an integrated, ready-to-run database platform that is designed specifically for transactional data workloads.

For database development and administration, IBM Data Studio provides a set of wizards and editors that enable developers and database administrators (DBAs) to develop, debug, tune, and deploy database applications, routines, and web services. It also offers basic administration for database object change, migration, and authorization management; scripting; and maintenance with job scheduling for automation.

Integrated management

A robust database performance manager can be used for database performance monitoring in a unified environment. At the system level, a summary of the health of all databases is available along with different types of configurable alerts (see Figure 2). These alerts show database health for data server status; storage space state and utilization; database key performance indicators (KPIs) from various categories such as buffer pool, memory, I/O, and cache hit ratio; and events such as deadlock, lock wait, and timeout. Detailed information for each alert includes recommendations for actions and resolution (see Figure 3).

Appliance-Based Analytics and Transactional Processing: Part 2 – Figure 2

Figure 2. System health summary within the database performance monitor

 
Appliance-Based Analytics and Transactional Processing: Part 2 – Figure 3

Figure 3. Alert list with comprehensive details for each alert

When drilling down to the database level, the at-a-glance performance overview helps DBAs identify problem areas quickly and shows them where deviations from the baseline are occurring. The diagnostic dashboards provide detailed metrics for particular areas, including locking, SQL statement executions, storage and memory use, and log performance. The extended insight analysis dashboard displays the list of top performance bottleneck SQL statements, with end-to-end performance data across the entire application transaction stack: application, client driver, application server, network, and database (see Figure 4).

Appliance-Based Analytics and Transactional Processing: Part 2 – Figure 4

Figure 4. Extended insight analysis dashboard for an at-a-glance view of application stack performance

From the list of bottleneck queries, query tuning can be used to tune individual queries or workloads and get advice for performance improvement along with the data definition languages (DDLs) to implement the tuning. The tuning categories include statistics, index, and table organization for IBM BLU Acceleration. This database monitoring and tuning integration provides the granularity of the database transactions so DBAs and developers can collaborate to identify, diagnose, and resolve performance problems quickly.

Predefined and customized reports can be generated for analysis of database configuration, connection, disk space consumption, performance, and workload categories. For example, DBAs can create a report comparing the performance of SQL executions during two periods.

Performance and resource optimization can be monitored using the database configuration manager, which offers a central tool for managing database configurations and connections. DBAs can use it to check if abnormal performance is occurring because of a database configuration change, or it can be used to dynamically reroute transactions to another database server while troubleshooting a problem. The database configuration manager also provides storage information and recommendations for compression or dynamically migrating data based on its temperature and access requirements. Access requirements include using fast, more expensive storage for frequently accessed, critical—hot—data and dynamically moving infrequently accessed—warm or cold—data to slower, less-expensive storage.

Infrastructure flexibility

For a growing range of organizations, particularly those that run data-intensive workloads, the PureData System infrastructure offers significant flexibility through its rich set of integrated tools for common database and analytics implementations. These tools help organizations get up and running quickly, enable them to utilize expertise, and can simplify the data management experience. In particular, PureData System for Transactions features centralized database development, administration, monitoring, and optimization to help keep data management and systems running efficiently and cost-effectively. The tools integrated into PureData System for Transactions help organizations efficiently develop, deploy, run, tune, and manage the database platform for data-intensive workloads.

Please share any thoughts or question in the comments.

Resources

[followbutton username='IBMdatamag' count='false' lang='en' theme='light']