Determine which database/table is using most storage on a given dataslice

Troubleshooting

Problem

When a dataslice runs full, how do we determine which table(s) are taking the most space on the dataslice running full.

Symptom

When running queries you may observe errors like:

ERROR: 3 : Data partition is full

Cause

A dataslice may have no more space available or about to be full.

Environment

IBM PureData System for Analytics

Diagnosing The Problem

Use the following commands to determine which table(s) are taking the most space on the dataslice running full.

The 'nzds' command will show you how much storage space is used by each data slice. Look for data slices where the '% Used' is 100% or very close to it. Following is an example of 'nzds' command output:

[nz@system1 ~]$ nzds
Data Slice Status SPU Partition Size (GiB) % Used Supporting Disks
---------- ------- ---- --------- ---------- ------ ----------------
1 Healthy 1115 0 356 84.15 1066,1077
2 Healthy 1115 1 356 84.29 1066,1077
3 Healthy 1115 2 356 100.00 1067,1100
4 Healthy 1115 3 356 83.95 1067,1100
5 Healthy 1115 5 356 84.90 1068,1101
6 Healthy 1115 4 356 84.13 1068,1101
7 Healthy 1113 2 356 83.75 1069,1102
8 Healthy 1113 3 356 84.11 1069,1102
9 Healthy 1113 4 356 84.00 1070,1103
10 Healthy 1113 5 356 84.09 1070,1103
11 Healthy 1117 0 356 84.05 1071,1104
12 Healthy 1117 1 356 83.84 1071,1104
13 Healthy 1113 1 356 84.93 1072,1105
14 Healthy 1113 0 356 84.22 1072,1105
15 Healthy 1117 2 356 84.17 1073,1106
16 Healthy 1117 3 356 83.84 1073,1106
17 Healthy 1117 5 356 84.23 1074,1107
18 Healthy 1117 4 356 84.03 1074,1107
19 Healthy 1111 1 356 84.43 1075,1146
20 Healthy 1111 0 356 84.11 1075,1146
21 Healthy 1111 2 356 84.15 1076,1109
22 Healthy 1111 3 356 84.23 1076,1109
[nz@system1 ~]$

The 'nz_skew' command will show you how the data is distributed across data slices. Check which table has the highest 'Min-Max SKEW (MB)' value and focus on large tables only; in the terms of size. This will show any data skewing issues, which could be resolved by choosing a different distribution key for a given table. Following is an example of 'nz_skew' command output:

[nz@system1 ~]$ /nz/support/bin/nz_skew

SPU Disk Utilization
===========================
# Of DataSlices 22
Least Full DSlice # 3
Most Full DSlice # 13
Extents Per Dataslice 121,515
Storage Per DataSlice (GB) 356.000
Storage Used (GB)
Minimum 298.333
Average 302.387
Maximum 356.203
Storage Used (%)
Minimum 83.801
Average 84.940
Maximum 100.057
Total Storage
Available (TB) 7.648
Used (TB) 6.497
Used (%) 84.940
Remaining (TB) 1.151
Remaining (%) 15.060

Table Skew That Is > 100 MB
===========================
Database | Schema | Table | Total MB | Minimum | Average | Maximum | Min-Max SKEW (MB) | SKEW Ratio | DSlice
-----------------+--------+-----------------------------+------------------+--------------+--------------+--------------+-------------------+----------------+--------
DB1 | ADMIN | TABLE1 | 361 | 0 | 16 | 113 | 113 | .00 - 6.90 | 1
DB1 | ADMIN | TABLE2 | 377 | 0 | 17 | 362 | 362 | .01 - 21.16 | 5
DB2 | ADMIN | TABLE1 | 1,639,152 | 73,713 | 74,507 | 75,408 | 1,695 | .99 - 1.01 | 14
DB2 | ADMIN | TABLE2 | 3,405 | 138 | 155 | 244 | 107 | .89 - 1.58 | 5
DB2 | ADMIN | TABLE3 | 20,579 | 881 | 935 | 1,088 | 207 | .94 - 1.16 | 5
DB2 | ADMIN | TABLE4 | 74,275 | 0 | 3,376 | 58,879 | 58,879 | .00 - 17.44 | 3
DB3 | ADMIN | TABLE1 | 379,390 | 17,129 | 17,245 | 17,424 | 294 | .99 - 1.01 | 16
DB3 | ADMIN | TABLE1 | 6,716 | 253 | 305 | 394 | 141 | .83 - 1.29 | 10
DB3 | ADMIN | TABLE2 | 1,284 | 0 | 58 | 506 | 506 | .00 - 8.68 | 10
DB4 | ADMIN | TABLE1 | 255,815 | 11,481 | 11,628 | 11,736 | 255 | .99 - 1.01 | 5
DB4 | ADMIN | TABLE2 | 1,043 | 43 | 47 | 144 | 101 | .90 - 3.04 | 1
DB4 | ADMIN | TABLE3 | 64,693 | 2,836 | 2,941 | 3,080 | 244 | .96 - 1.05 | 1
DB4 | ADMIN | TABLE4 | 61,021 | 2,676 | 2,774 | 2,903 | 228 | .96 - 1.05 | 1
DB4 | ADMIN | TABLE5 | 914,540 | 41,401 | 41,570 | 41,730 | 328 | 1.00 - 1.00 | 19
DB4 | ADMIN | TABLE6 | 72,462 | 3,190 | 3,294 | 3,439 | 249 | .97 - 1.04 | 1
DB4 | ADMIN | TABLE7 | 5,533 | 118 | 252 | 553 | 435 | .47 - 2.20 | 5
DB4 | ADMIN | TABLE8 | 968 | 0 | 44 | 256 | 256 | .00 - 5.82 | 13
DB4 | ADMIN | TABLE9 | 2,484 | 21 | 113 | 275 | 254 | .19 - 2.44 | 8
DB4 | ADMIN | TABLE10 | 502 | 1 | 23 | 274 | 273 | .02 - 12.01 | 1
DB5 | ADMIN | TABLE2 | 522 | 1 | 24 | 427 | 426 | .03 - 18.01 | 5
DB5 | ADMIN | TABLE3 | 13,415 | 568 | 610 | 774 | 206 | .93 - 1.27 | 5
DB6 | ADMIN | TABLE1 | 657,930 | 29,707 | 29,906 | 30,045 | 338 | .99 - 1.00 | 2
DB6 | ADMIN | TABLE6 | 34,959 | 1,424 | 1,589 | 1,824 | 400 | .90 - 1.15 | 3
DB6 | ADMIN | TABLE1 | 7,593 | 185 | 345 | 452 | 267 | .54 - 1.31 | 9

The following 'nzstats -type table' command with filtered on certain columns and sorted values shows the database and table with highest data slice disk space utilization.

[nz@system1 ~]$ nzstats -type table -cols 2,4,10,12,13,16 | sort -k5 -r -g | awk '$7==3 {print $0;}'

DB Name Table Name Disk Space Max Space per DS Max Space DS Id Space Skew
--------------- ----------------- ------------- ---------------- --------------- ----------
DB2 TABLE4 76057088 KB 60292352 KB 3 17.44
DB6 TABLE6 35797760 KB 1867264 KB 3 0.25
DB6 TABLE3 24913920 KB 1163648 KB 3 0.06
DB6 TABLE4 22935424 KB 1070976 KB 3 0.06
DB4 TABLE11 18871040 KB 872064 KB 3 0.03
DB4 TABLE12 16702592 KB 759680 KB 3 0.00
DB3 TABLE13 7411840 KB 337152 KB 3 0.00
DB4 TABLE14 5549568 KB 252544 KB 3 0.00
DB4 TABLE15 1927424 KB 88064 KB 3 0.01
DB5 TABLE16 640384 KB 30464 KB 3 0.10
DB2 TABLE17 581248 KB 28928 KB 3 0.20
DB5 TABLE18 547968 KB 27264 KB 3 0.21
DB2 TABLE19 528768 KB 26496 KB 3 0.22
DB2 TABLE20 487168 KB 24192 KB 3 0.20
DB2 TABLE21 485376 KB 23680 KB 3 0.16
DB1 TABLE22 424192 KB 23296 KB 3 0.40
DB1 TABLE23 487040 KB 22528 KB 3 0.03
DB1 TABLE24 476032 KB 22016 KB 3 0.04
DB4 TABLE25 362496 KB 17024 KB 3 0.06
DB5 TABLE26 298880 KB 13952 KB 3 0.08
DB2 TABLE27 270848 KB 12416 KB 3 0.01
DB3 TABLE28 107136 KB 6272 KB 3 0.53
DB6 TABLE29 96384 KB 5888 KB 3 0.64
DB3 TABLE30 36224 KB 2816 KB 3 1.09
DB3 TABLE31 42368 KB 2560 KB 3 0.66
DB3 TABLE32 19456 KB 2048 KB 3 1.59
DB4 TABLE33 39680 KB 1920 KB 3 0.07
DB2 TABLE34 33408 KB 1792 KB 3 0.34
DB6 TABLE35 22912 KB 1536 KB 3 0.86
DB6 TABLE36 23424 KB 1536 KB 3 0.72
DB6 TABLE37 23296 KB 1536 KB 3 0.85
DB6 TABLE38 17408 KB 1280 KB 3 0.97
DB3 TABLE39 19584 KB 1280 KB 3 0.86
DB6 TABLE40 17280 KB 1152 KB 3 0.81
DB7 TABLE41 22656 KB 1152 KB 3 0.12
DB3 TABLE42 18432 KB 1152 KB 3 0.76
DB3 TABLE43 18560 KB 1152 KB 3 0.76
DB6 TABLE44 13568 KB 1024 KB 3 1.04
DB6 TABLE45 17792 KB 1024 KB 3 0.47
DB3 TABLE46 14720 KB 896 KB 3 0.57
DB3 TABLE47 14592 KB 896 KB 3 0.58
DB3 TABLE48 10752 KB 896 KB 3 1.31
DB6 TABLE49 10624 KB 768 KB 3 0.80
DB3 TABLE50 9216 KB 640 KB 3 0.92
DB8 TABLE51 8704 KB 512 KB 3 0.32
DB3 TABLE52 7168 KB 512 KB 3 0.79
DB3 TABLE53 7424 KB 512 KB 3 1.14
DB3 TABLE54 6400 KB 512 KB 3 1.32
DB3 TABLE55 5888 KB 384 KB 3 0.48
DB3 TABLE56 5248 KB 384 KB 3 1.07
DB6 TABLE57 768 KB 256 KB 3 2.00
DB6 TABLE58 128 KB 128 KB 3 1.00
DB3 TABLE59 128 KB 128 KB 3 1.00
DB1 TABLE60 128 KB 128 KB 3 1.00
DB1 TABLE61 128 KB 128 KB 3 1.00
DB1 TABLE62 128 KB 128 KB 3 1.00
DB1 TABLE63 128 KB 128 KB 3 1.00

[{"Product":{"code":"SSULQD","label":"IBM PureData System"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"Storage","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"1.0.0","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Product Synonym

netezza pda

Was this topic helpful?

Document Information

Modified date:
17 October 2019

UID

swg21974212

Tips