In a DB2 for LUW database that makes use of the data partitioning feature available in the Infosphere Warehouse product, one of the new concerns a database administrator is faced with is skew among the partitions. Adding or removing partitions from a database introduces skew and requires a redistribution to ensure data is spread evenly across all partitions. Typically, it is desirable to reduce skew in order to balance the use of storage space and improve database system performance.
In some cases, however, an unequal distribution of data can be desirable. To use an example from the DB2 9.7 documentation, if some database partitions reside on a particularly powerful machine, then it may be beneficial for those database partitions to contain larger volumes of data than other partitions.
To determine data skew, the same documentation recommends using a query like the following on a representative table in a partition group:
SELECT DBPARTITIONNUM(column_name), COUNT(*) FROM table_name
GROUP BY DBPARTITIONNUM(column_name)
ORDER BY DBPARTITIONNUM(column_name) DESC
where column_name is the name of the distribution key for table table_name. Another option for determining data skew is the ANALYZE_LOG_SPACE procedure.
Another form of skew to examine is skew in your data access patterns. You can observe data access skew by looking at how the data actually gets used, that is, to see if more data is being read from one partition than the others by the queries being run by the users of the database. In today’s post, we will investigate the Skew screen of db2top and learn what it can tell you about data access skew in your database.
The Skew screen consists of a basic table with the following columns:
| Column Name | Definition |
|---|---|
| Node Nbr | Database partition where the subsections are executing (ss_node_number). |
| Exec Time | The time in seconds that it took a subsection to execute (sum(ss_exec_time), where sum() means summing across all subsections on the partition). |
| Cpu Time | The total CPU time used by the currently executing statement subsections on this partition (sum(ss_usr_cpu_time + ss_sys_cpu_time), where sum() means summing across all subsections on the partition). |
| Rows Second | The number of rows per second changed (inserted, deleted, or updated) or read by subsections on this partition and written to or read from table queues on this partition ((sum(rows_read + rows_written) + tq_rows_read + tq_rows_written) / ss_exec_time, where sum() means summing across all subsections on the partition). |
| Rows Read | The number of rows read by subsections on this partition (sum(rows_read), where sum() means summing across all subsections on the partition). |
| Rows Written | The number of rows changed (inserted, deleted, or updated) by subsections on this partition (sum(rows_written), where sum() means summing across all subsections on the partition). |
| TqRows Read | The number of rows read from table queues on this partition (tq_rows_read). |
| TqRows Written | The number of rows changed (inserted, deleted, or updated) on this partition (tq_rows_written). |
| Tq Spills | Total number of table queue buffers overflowed to a temporary table on this partition (tq_tot_send_spills). |
| FCM Sent | The total number of FCM buffers that have been sent to the node identified by the Node Nbr column (total_buffers_sent). |
| FCM Recvd | The total number of FCM buffers received the node identified by the Node Nbr column (total_buffers_rcvd). |
| Subs Exec | Count of the subsections on this partition whose current status (ss_status) is SQLM_SSEXEC. |
| Subs Comp | Count of the subsections on this partition whose current status (ss_status) is SQLM_SSCOMPLETED. |
| Subs Send | Count of the subsections on this partition whose current status (ss_status) is SQLM_SSTQ_WAIT_TO_SEND. |
| Subs Rec | Count of the subsections on this partition whose current status (ss_status) is SQLM_SSTQ_WAIT_TO_RCV. |
The Skew monitor screen is affected by holding the Shift key and pressing the ‘G’ key to switch between global and local snapshots. For a global snapshot, data for all partitions is shown as shown in the example below:
For a local snapshot, only the Node Nbr and Exec Time fields are shown for all partitions while all other data are shown for just the local partition as in the following example:
The Skew monitor is not affected by pressing the ‘i’ key to switch between showing active objects and showing all objects. It is not affected by pressing the ‘k’ key to switch between deltas and actuals. It is also not affected by holding the Shift key and pressing the ‘X’ key to enable and disable extended mode.
For other articles on db2top at this site, see the main db2top page.