Netezza performance diagnosis when hardware issue is suspected

Answer

Performance Diagnosis When Hardware is Suspected

When performance has degraded and hardware is suspected, use this document to help diagnose and resolve the problem. The utility nz_check_disk_scan_speeds can be used to help determine if there is indeed a hardware related performance issue. This utility can be found in the Software Support Toolkit at /nz/support/bin.

The purpose of this utility is to measure disk read and write speeds. It will build a table (writes) and select against that same table (reads), measuring and reporting the timings. The table built in this test will be found in the SYSTEM database, named NZ_CHECK_DISK_SCAN_SPEEDS. The utility can be invoked to scan at different levels of granularity (SPA, SPU, enclosure, dataslice) to help isolate and validate. While this utility’s primary purpose is to identify slow disk, it has the side benefit of also surfacing non-disk related hardware bottlenecks.

Common options for nz_check_disk_scan_speeds utility:

-iterations <nn>

The number of times to scan NZ_CHECK_DISK_SCAN_SPEEDS. The default of 5 is recommended.

-write

This will cause the table NZ_CHECK_DISK_SCAN_SPEEDS table to be dropped (if exists) and rebuilt, which can take up to 10 minutes. This is recommended, but only for the initial run of this test.

-spa

Test the dataslices for each SPA as a group. It will run one scan against each SPA sequentially.

-spa <nn>

Test only one SPA. Use this to confirm the findings at the SPA level.

-spa <nn> -enc <nn>

This would be useful to confirm if the slowness is at a particular enclosure level.

-spa <nn> -spu <nn>

This would be useful to confirm if the slowness is at a particular SPU level.

-dsid [ … ]

Run against an individual or range of dataslices. Use this to validate that a suspect dataslice is slow.

-h

Help option to provide more information about the above mentioned options along with several additional options.

Please note that the data reported from the nz_check_disk_scan_speeds utility alone is not enough to diagnose performance issues. The utility nz_responders, also found in the Software Support Toolkit in /nz/support/bin, will assist by monitoring the progress of each dataslice (user data partition on the disk) and identifying those that are slow relative to the system’s community of dataslices. These two utilities running in conjunction is the recommended method.



Before running the performance tests:

Perform a health check using a current version (2.3.1.2 or above) of the System Health Check tool.

 /nz/kit/bin/adm/nzhealthcheck

See PureData Analytics Knowledge Center – System Health Check tool for usage information.


This will produce a report in /nz/kit/log/nzhealthcheck/. Review the report and address any alerts that could affect performance. Performance related issues could compromise the results of the nz_check_disk_scan_speeds test. Pay special attention to the following alerts:

DM01* + DM025 + DM026 + DM03* + DM04* - Various disk related alerts

Follow the Expert’s advice provided for the rule. If the Severity level is Medium or High, action should be taken. If a disk failover is warranted and executed, please note that this will initiate a disk regeneration that must be completed before performing the test.

SHC003 – Broken blade HBA connector or HBA

An HBA port is going unused for some reason, causing other HBA ports to be overloaded. This could be a hardware problem that IBM Support can help resolve. Open a PMR.

SHC027 – Failed hardware components

Component failures may or may not impact this performance test. The effect of any failed components should be assessed and addressed before performing the test.

SHC040[a|b|c] – Dataslice issue detected

If a dataslice is in the process of regenerating, it must complete before performing the test. If the dataslice is in a degraded state and no regeneration is active, regeneration must be started and completed before performing the test.

SHC041 – Topology issue of data slice detected

Check topology balance with nzhw rebalance –check -> Online activity
If this indicates a rebalance is necessary, then run nzhw rebalance -> System will Pause
However, if the “rebalance –check” indicates that there is no rebalance necessary, then powercycle the SPUs and enclosures. The following will cause an outage of 15-20 minutes:

$ nzstop
# /nzlocal/scripts/rpc/spapwr.sh –off all
# /nzlocal/scripts/rpc/spapwr.sh –on all
$ nzstart

If this does not resolve the topology issue, then open a PMR with IBM Support.

SHC105 – Disk path not optimal or some paths disabled

If alert is: “SPU does not use preferred path”
check and correct DM devices nzpush –a mpath dm -r -> Online activity
wait a few minutes
recheck using nzpush –a mpath –issues -> Online activity
If alert is: “SPU has 1 path(s) enabled” -> The following will cause an outage of 15-20 minutes:

$ nzstop
# /nzlocal/scripts/rpc/spapwr.sh –off all
# /nzlocal/scripts/rpc/spapwr.sh –on all
$ nzstart

SHC114 – Chassis component health issue detected

Follow Expert’s advice in the alert to find additional detail regarding the component health issue. Assess impact to performance and address if necessary before performing the test.

The nz_check_disk_scan_speeds utility must run during a quiet time (no concurrent query activity), in order to provide accurate and meaningful timings. To help minimize SQL activity, put the system in maintenance mode using nz_maintenance_mode -on. This will prevent non-ADMIN users from connecting to the databases, however ADMIN user can still connect. Be aware that any existing connections are not impacted by maintenance mode, and can continue to service queries. To remove all existing connections, use nz_abort –all . Remember, when complete with testing, take the system out of maintenance mode using nz_maintenance_mode -off.


Initial run of the performance test:

The first run will build a table named NZ_CHECK_DISK_SCAN_SPEEDS, measuring write speed, followed by a scan of all dataslices, measuring read speed.

1. Open two command windows on the active host
2. In window #1 start the utility nz_responders. The nz_responders utility will show dataslices that are busy. If there is a slow dataslice, it will show as busy for a period of time after others have completed. This will be used to identify slow disk drives.
• nz_responders –sleep 1 –stragglers 24
3. In window #2 start the utility nz_check_disk_scan_speeds with the –write option.
• nz_check_disk_scan_speeds –write
4. Monitor the nz_responders window. The number of busy dataslices will initially equal the total dataslice count. This represents the dataslices actively scanning. As individual dataslice scans complete, the count of busy dataslices will decrease. Once this count reaches 24, the busy dataslices ID’s will be itemized under the Dataslices column. Watch for any dataslices that continue to show up in this list for an additional 30% of time. For instance, if dataslices are itemized at 40 seconds, then make note of all dataslices still showing as busy at 52 seconds. Confirm that the same dataslices are consistently reported as straggling for all 5 iterations. Make note of these dataslices. This is the data that will be used for Next Steps.

When nz_check_disk_scan_speeds completes, a summary report will be displayed that will show both Read and Write speeds. Concurrent SQL activity during the test (shown in the report summary) or background tasks running during this test may cause speed variability from iteration to iteration. The speeds displayed for each iteration will represent the time to complete all dataslices, meaning it will equate to the slowest dataslice. It is recommended to consider the fastest iteration timing as that will likely have the fewest intrusions. The following chart shows suggested threshold for both Read and Write.

Model	Read Speed (Warning)	Read Speed (Alarm)	Write Speed (Warning)	Write Speed (Alarm)
TwinFin	84 MB/s	63 MB/s	8 MB/s	6 MB/s
Striper	100 MB/s	75 MB/s	12 MB/s	9 MB/s
Mako	111 MB/s	83 MB/s	20 MB/s	15 MB/s

Caution: factors may impact the reported speeds (fragmentation and location of table data, concurrent activity, model of disk, age of disk, etc.). Expect speed from system to system and test to test to show variability. It is most important to focus on disks that are speed outliers (stragglers) relative to the system’s community of disk rather than the absolute, overall disk speed.

Fragmentation of disk extents and track location of the table NZ_CHECK_DISK_SCAN_SPEEDS can affect speeds, but that should be normalized across the system’s community of dataslices. Measuring the fragmentation and location of the table is out of the scope of this document. See the utility nz_frag for more information.


Next Steps following the initial run:

If an individual dataslice is a consistent straggler, then do:
Rerun nz_check_disk_scan_speeds using the –dsid option. Include the straggling dataslice along with two unrelated, non-straggling dataslices for comparison. If the dataslice is truly slow, then it will be confirmed in this test. For example, let’s say that dataslice 45 has been the straggler, run the utility with these options (including randomly-chosen, non-straggling dataslices 5 and 105).

nz_check_disk_scan_speeds –dsid 5 45 105

If this confirms dataslice 45 to be an outlier with regards to speed (at least 30% slower than dataslices 5 and 105) then plan to fail the disk out. Find the disk that holds primary storage for this dataslice.

[nz@ bin]$ nzds -dsid 45 -detail
Data Slice Status  SPU  Partition Size (GiB) % Used Supporting Disks Primary Storage
---------- ------- ---- --------- ---------- ------ ----------------  ---------------
45         Healthy 1013 16        195        94.59  1187,1232        1232

Then, at an appropriate time, fail the disk. Before failing the disk, confirm that there are no other disks identified in the nzhealthcheck report at severity level Medium or High. Those must be addressed first.

nzhw failover –id 1232


If multiple dataslices are consistent stragglers, then do:
Find if and how the dataslices are related. Use the nz_show_topology command from the Software Support Toolkit to list your dataslices of interest along with their attributes. The following example will report on dataslices 3, 10, and 12. Note the sequence is “pipe, carrot, blank, asterisk, dataslice, blank” for each dataslice.

nz_show_topology |cut –d ‘|’ –f 1,6,8,10,13 |egrep “DSlice|^ *3 |^ *10 |^ *12 “

DSlice | Spa # | Spu # | Enc # | Disk ID
     3 |     1 |     3 |     1 |    1061
    10 |     1 |     3 |     2 |    1084
    12 |     1 |     3 |     2 |    1078

Some common relationships include:

Same SPA & SPU

• If the dataslices are same SPU, they could have the same HBA port. There are 4 HBA ports per SPU.
• Run nzds –topology. This will show a mapping of dataslices to the HBA ports. Locate in the output the dataslices identified as stragglers and note which HBA port they are assigned to.
• If dataslices are HBA port related this could be a topology or multipath issue… but, those should have been addressed in the “Before Running” section. Run the nzhealthcheck utility again. If all looks good, and issue persists, open a PMR.
• For validation, run nz_check_disk_scan_speeds –spa <nn> –spu <nn> referencing the suspect SPU and again, referencing an unrelated SPU for comparison.
• If the stragglers are not HBA port related, but are same SPU – various SPU related possibilities could be the cause. Open a PMR with IBM Support.

Same SPA & disk enclosure

• For validation, run  nz_check_disk_scan_speeds –spa <nn> –enc <nn> reerencing the suspect enclosure and again, referencing an unrelated enclosure for comparison.
• All stragglers in same enclosure – possible ESM issue. Open a PMR with IBM Support.

Same SPA only

• For validation, run nz_check_disk_scan_speeds –spa <nn> referencing the suspected SPA and again, referencing an unrelated SPA for comparison.
• All stragglers in same SPA – potential SAS Switch. Open a PMR with IBM Support.

No relationships found

• Each straggling dataslice could be slow of its own volition. For each straggling dataslice, follow the steps outlined above for the individual dataslice straggler.
• If that exercise does not provide resolution, then open a PMR with IBM Support.

Performance issues isolated to an individual disk can be addressed without engaging IBM Support. Performance issues isolated to multiple disk often involve an architectural layer above the storage itself and may require IBM Support. Isolating the problem using the methodologies described above will help IBM Support more quickly find root cause and provide resolution.

Before opening a PMR with IBM Support, confirm that the suspected dataslice(s) are consistently 30% slower than the community of dataslices and that all steps outlined in this document have been taken. Include all data collected during this process with as much detail as possible. This would include:

1. the nzhealtcheck report
2. output of nz_responders while nz_check_disk_scan_speeds was run
3. output of nz_check_disk_scan_speeds initial run and Next Step runs
4. the nz_show_topology complete report (no filtering)
5. the nzds –topology report
6. output of nz_migrate –loopback –status  this can isolate problems to the internal fabric.