Did a bit more digging in the database, wrote another a take2 script to review data diversity in the table so that I could further reduce the number of columns in the db / features to consider
cat check_data_diversity_take2.sh mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_1_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_2_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_3_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_4_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_5_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_7_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_8_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_9_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_10_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_11_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_12_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_13_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_15_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_16_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_17_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_18_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_22_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_23_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_24_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_160_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_161_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_163_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_164_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_165_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_166_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_167_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_168_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_169_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_170_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_171_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_172_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_173_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_174_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_175_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_176_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_177_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_178_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_179_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_180_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_181_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_182_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_183_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_184_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_187_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_188_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_189_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_190_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_191_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_192_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_193_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_194_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_195_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_196_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_197_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_198_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_199_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_200_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_201_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_202_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_206_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_210_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_218_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_220_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_222_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_223_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_224_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_225_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_226_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_230_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_231_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_232_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_233_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_234_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_235_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_240_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_241_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_242_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_244_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_245_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_246_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_247_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_248_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_250_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_251_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_252_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_254_raw)) from backblaze_ml.drive_stats;" mysql -e "select count(distinct(backblaze_ml.drive_stats.smart_255_raw)) from backblaze_ml.drive_stats;"
cat data_diversity_take2.txt count(distinct(backblaze_ml.drive_stats.smart_1_raw)) 10252982 count(distinct(backblaze_ml.drive_stats.smart_2_raw)) 173 count(distinct(backblaze_ml.drive_stats.smart_3_raw)) 2779 count(distinct(backblaze_ml.drive_stats.smart_4_raw)) 1488 count(distinct(backblaze_ml.drive_stats.smart_5_raw)) 13343 count(distinct(backblaze_ml.drive_stats.smart_7_raw)) 11558180 count(distinct(backblaze_ml.drive_stats.smart_8_raw)) 44 count(distinct(backblaze_ml.drive_stats.smart_9_raw)) 76070 count(distinct(backblaze_ml.drive_stats.smart_10_raw)) 34 count(distinct(backblaze_ml.drive_stats.smart_11_raw)) 7329 count(distinct(backblaze_ml.drive_stats.smart_12_raw)) 359 count(distinct(backblaze_ml.drive_stats.smart_13_raw)) 25 count(distinct(backblaze_ml.drive_stats.smart_15_raw)) 2 count(distinct(backblaze_ml.drive_stats.smart_16_raw)) 30 count(distinct(backblaze_ml.drive_stats.smart_17_raw)) 31 count(distinct(backblaze_ml.drive_stats.smart_18_raw)) 1 count(distinct(backblaze_ml.drive_stats.smart_22_raw)) 101 count(distinct(backblaze_ml.drive_stats.smart_23_raw)) 1 count(distinct(backblaze_ml.drive_stats.smart_24_raw)) 1 count(distinct(backblaze_ml.drive_stats.smart_160_raw)) 13 count(distinct(backblaze_ml.drive_stats.smart_161_raw)) 18 count(distinct(backblaze_ml.drive_stats.smart_163_raw)) 18 count(distinct(backblaze_ml.drive_stats.smart_164_raw)) 4767 count(distinct(backblaze_ml.drive_stats.smart_165_raw)) 2824 count(distinct(backblaze_ml.drive_stats.smart_166_raw)) 314 count(distinct(backblaze_ml.drive_stats.smart_167_raw)) 585 count(distinct(backblaze_ml.drive_stats.smart_168_raw)) 299 count(distinct(backblaze_ml.drive_stats.smart_169_raw)) 28 count(distinct(backblaze_ml.drive_stats.smart_170_raw)) 38 count(distinct(backblaze_ml.drive_stats.smart_171_raw)) 1 count(distinct(backblaze_ml.drive_stats.smart_172_raw)) 5 count(distinct(backblaze_ml.drive_stats.smart_173_raw)) 12021 count(distinct(backblaze_ml.drive_stats.smart_174_raw)) 29 count(distinct(backblaze_ml.drive_stats.smart_175_raw)) 68 count(distinct(backblaze_ml.drive_stats.smart_176_raw)) 1 count(distinct(backblaze_ml.drive_stats.smart_177_raw)) 15 count(distinct(backblaze_ml.drive_stats.smart_178_raw)) 18 count(distinct(backblaze_ml.drive_stats.smart_179_raw)) 1 count(distinct(backblaze_ml.drive_stats.smart_180_raw)) 10 count(distinct(backblaze_ml.drive_stats.smart_181_raw)) 1 count(distinct(backblaze_ml.drive_stats.smart_182_raw)) 1 count(distinct(backblaze_ml.drive_stats.smart_183_raw)) 1023 count(distinct(backblaze_ml.drive_stats.smart_184_raw)) 194 count(distinct(backblaze_ml.drive_stats.smart_187_raw)) 1837 count(distinct(backblaze_ml.drive_stats.smart_188_raw)) 2476 count(distinct(backblaze_ml.drive_stats.smart_189_raw)) 2024 count(distinct(backblaze_ml.drive_stats.smart_190_raw)) 64 count(distinct(backblaze_ml.drive_stats.smart_191_raw)) 190896 count(distinct(backblaze_ml.drive_stats.smart_192_raw)) 15320 count(distinct(backblaze_ml.drive_stats.smart_193_raw)) 229873 count(distinct(backblaze_ml.drive_stats.smart_194_raw)) 64 count(distinct(backblaze_ml.drive_stats.smart_195_raw)) 4619605 count(distinct(backblaze_ml.drive_stats.smart_196_raw)) 2414 count(distinct(backblaze_ml.drive_stats.smart_197_raw)) 3236 count(distinct(backblaze_ml.drive_stats.smart_198_raw)) 1757 count(distinct(backblaze_ml.drive_stats.smart_199_raw)) 1491 count(distinct(backblaze_ml.drive_stats.smart_200_raw)) 207196 count(distinct(backblaze_ml.drive_stats.smart_201_raw)) 5 count(distinct(backblaze_ml.drive_stats.smart_202_raw)) 222 count(distinct(backblaze_ml.drive_stats.smart_206_raw)) 1 count(distinct(backblaze_ml.drive_stats.smart_210_raw)) 1 count(distinct(backblaze_ml.drive_stats.smart_218_raw)) 2 count(distinct(backblaze_ml.drive_stats.smart_220_raw)) 5024 count(distinct(backblaze_ml.drive_stats.smart_222_raw)) 50154 count(distinct(backblaze_ml.drive_stats.smart_223_raw)) 7335 count(distinct(backblaze_ml.drive_stats.smart_224_raw)) 1 count(distinct(backblaze_ml.drive_stats.smart_225_raw)) 169663 count(distinct(backblaze_ml.drive_stats.smart_226_raw)) 280 count(distinct(backblaze_ml.drive_stats.smart_230_raw)) 548 count(distinct(backblaze_ml.drive_stats.smart_231_raw)) 155 count(distinct(backblaze_ml.drive_stats.smart_232_raw)) 69 count(distinct(backblaze_ml.drive_stats.smart_233_raw)) 25639 count(distinct(backblaze_ml.drive_stats.smart_234_raw)) 2122 count(distinct(backblaze_ml.drive_stats.smart_235_raw)) 24883 count(distinct(backblaze_ml.drive_stats.smart_240_raw)) 1450722 count(distinct(backblaze_ml.drive_stats.smart_241_raw)) 11636846 count(distinct(backblaze_ml.drive_stats.smart_242_raw)) 11642421 count(distinct(backblaze_ml.drive_stats.smart_244_raw)) 1 count(distinct(backblaze_ml.drive_stats.smart_245_raw)) 4741 count(distinct(backblaze_ml.drive_stats.smart_246_raw)) 1531 count(distinct(backblaze_ml.drive_stats.smart_247_raw)) 1804 count(distinct(backblaze_ml.drive_stats.smart_248_raw)) 1804 count(distinct(backblaze_ml.drive_stats.smart_250_raw)) 65276 count(distinct(backblaze_ml.drive_stats.smart_251_raw)) 65251 count(distinct(backblaze_ml.drive_stats.smart_252_raw)) 61592 count(distinct(backblaze_ml.drive_stats.smart_254_raw)) 1 count(distinct(backblaze_ml.drive_stats.smart_255_raw)) 2
Based on the output above the following can be removed (not enough diversity - for 255 we have two weird values which can be disregarded, for 244 all recorded values are either NULL or 0, same for 224, 210, 206, 218, 201 which is softreaderrorrate is interesting so keeping it for now , 181 and 182 like bofore, 179 is interesting - used reserved block count however not relevant for failure in general, 172 and 176 - erase fail count - are interesting however not relevant for failure in general, 171 is also interesting but same as 172and 176, 15, 18, 23, 24 - not relevant for failure, 201 despite being interesting should be discarded - see below): smart_255_raw, smart_254_raw, smart_244_raw, smart_224_raw, smart_218_raw, smart_210_raw, smart_206_raw, smart_182_raw, smart_181_raw, smart_179_raw, smart_176_raw, smart_172_raw, smart_171_raw, smart_15_raw, smart_18_raw, smart_23_raw, smart_24_raw, smart_201_raw
Sample analysis for 15
select smart_15_raw, count(serial_number) from drive_stats group by smart_15_raw; +----------------+----------------------+ | smart_15_raw | count(serial_number) | +----------------+----------------------+ | NULL | 15653249 | | 14475673534514 | 1 | | 14578752749618 | 1 | +----------------+----------------------+In the database containing failed drives there's only 2 entries reporting weird values for smart 15 (which is an unknown attribute ) - these might be indicators of failure for some special cases in the grand scheme should be ignored for now
another interesting attribute is 201 - SoftReadErrorRate
select smart_201_raw, count(serial_number) from drive_stats group by smart_201_raw; +---------------+----------------------+ | smart_201_raw | count(serial_number) | +---------------+----------------------+ | NULL | 15649883 | | 0 | 3330 | | 1 | 1 | | 12 | 16 | | 6 | 2 | | 184683530517 | 19 | +---------------+----------------------+which is interesting but again in the grand scheme should be ignored as there are better attributes that can be taken into account for predicting failures - read errors happen all the time for various reasons but they are not clear indicators of failure unless combined with other metrics (for example a drive reporting read errors which affect its performance will be removed from a storage pool to maintain performance to acceptable levels however that does not mean the drive has failed - it means it's underperforming - so perhaps a definition of what we mean by failure should be considered as a premise in this context) - given that we do not have any metrics for drive performance this attribute should not be considered for predicting failure
Attributes 1,2,3,4,7,8,9,10,11,12,13, 16 are more indicators of performance parameters than of failure so can be removed (see comments above ),
15, 17 can also be removed (not enough diversity, also undocumented) 22 is the state of Helium in HGST drives - can also be removed as it impacts performance 174 - unexpected power loss can be removed - power loss can be linked to drive failure on rare ocasions so can b eremoved, 180 - unused reserved block count can be removed as even if the drive runs out of reserved blocks it can still operate
Remaining output to review:
count(distinct(backblaze_ml.drive_stats.smart_5_raw)) 13343 count(distinct(backblaze_ml.drive_stats.smart_160_raw)) 13 count(distinct(backblaze_ml.drive_stats.smart_161_raw)) 18 count(distinct(backblaze_ml.drive_stats.smart_163_raw)) 18 count(distinct(backblaze_ml.drive_stats.smart_164_raw)) 4767 count(distinct(backblaze_ml.drive_stats.smart_165_raw)) 2824 count(distinct(backblaze_ml.drive_stats.smart_166_raw)) 314 count(distinct(backblaze_ml.drive_stats.smart_167_raw)) 585 count(distinct(backblaze_ml.drive_stats.smart_168_raw)) 299 count(distinct(backblaze_ml.drive_stats.smart_169_raw)) 28 count(distinct(backblaze_ml.drive_stats.smart_170_raw)) 38 count(distinct(backblaze_ml.drive_stats.smart_173_raw)) 12021 count(distinct(backblaze_ml.drive_stats.smart_175_raw)) 68 count(distinct(backblaze_ml.drive_stats.smart_177_raw)) 15 count(distinct(backblaze_ml.drive_stats.smart_178_raw)) 18 count(distinct(backblaze_ml.drive_stats.smart_183_raw)) 1023 count(distinct(backblaze_ml.drive_stats.smart_184_raw)) 194 count(distinct(backblaze_ml.drive_stats.smart_187_raw)) 1837 count(distinct(backblaze_ml.drive_stats.smart_188_raw)) 2476 count(distinct(backblaze_ml.drive_stats.smart_189_raw)) 2024 count(distinct(backblaze_ml.drive_stats.smart_190_raw)) 64 count(distinct(backblaze_ml.drive_stats.smart_191_raw)) 190896 count(distinct(backblaze_ml.drive_stats.smart_192_raw)) 15320 count(distinct(backblaze_ml.drive_stats.smart_193_raw)) 229873 count(distinct(backblaze_ml.drive_stats.smart_194_raw)) 64 count(distinct(backblaze_ml.drive_stats.smart_195_raw)) 4619605 count(distinct(backblaze_ml.drive_stats.smart_196_raw)) 2414 count(distinct(backblaze_ml.drive_stats.smart_197_raw)) 3236 count(distinct(backblaze_ml.drive_stats.smart_198_raw)) 1757 count(distinct(backblaze_ml.drive_stats.smart_199_raw)) 1491 count(distinct(backblaze_ml.drive_stats.smart_200_raw)) 207196 count(distinct(backblaze_ml.drive_stats.smart_202_raw)) 222 count(distinct(backblaze_ml.drive_stats.smart_220_raw)) 5024 count(distinct(backblaze_ml.drive_stats.smart_222_raw)) 50154 count(distinct(backblaze_ml.drive_stats.smart_223_raw)) 7335 count(distinct(backblaze_ml.drive_stats.smart_225_raw)) 169663 count(distinct(backblaze_ml.drive_stats.smart_226_raw)) 280 count(distinct(backblaze_ml.drive_stats.smart_230_raw)) 548 count(distinct(backblaze_ml.drive_stats.smart_231_raw)) 155 count(distinct(backblaze_ml.drive_stats.smart_232_raw)) 69 count(distinct(backblaze_ml.drive_stats.smart_233_raw)) 25639 count(distinct(backblaze_ml.drive_stats.smart_234_raw)) 2122 count(distinct(backblaze_ml.drive_stats.smart_235_raw)) 24883 count(distinct(backblaze_ml.drive_stats.smart_240_raw)) 1450722 count(distinct(backblaze_ml.drive_stats.smart_241_raw)) 11636846 count(distinct(backblaze_ml.drive_stats.smart_242_raw)) 11642421 count(distinct(backblaze_ml.drive_stats.smart_245_raw)) 4741 count(distinct(backblaze_ml.drive_stats.smart_246_raw)) 1531 count(distinct(backblaze_ml.drive_stats.smart_247_raw)) 1804 count(distinct(backblaze_ml.drive_stats.smart_248_raw)) 1804 count(distinct(backblaze_ml.drive_stats.smart_250_raw)) 65276 count(distinct(backblaze_ml.drive_stats.smart_251_raw)) 65251 count(distinct(backblaze_ml.drive_stats.smart_252_raw)) 61592
Not all attributes are reported by all vendors / disk models so we can further reduce the list for the generic model however we want to analyze them in context of vendors or specific models as potential indicators of failure
250, 251 and 252 are correlated so keeping the one which makes most sense -> 250 stays, 251 and 252 go 232 - not relevant (available reserved space) 233 - not relevant / not trusted - mead wearout indicator - as reported by smart - it is an indicator of failure from the pov of SMART however it is calculated based on values of the other attributes so can be removed 231 - temperature - will increase wear and will lead to failure however in this context there are only a few values recorded and they don't seem to point to failure here given that these disks live in a controlled environment - datacenter - so safe to remove 230 - gmr head amplitude - head motion counter - safe to ignore 226 - load in time - counter -safe to ignore 223 (load unload retry count), 225 (load unload cycle count) - counters -safe to ignore 222 (loaded hours) - counter - safe to ignore 220 (disk shift) - shock related - not relevant enough, also a counter - safe to ignore 202 data address mark errors or vendor specific - not enough data - safe to ignore 190 airflor temperature - will increase wear and will lead to failure however in this context there are only a few values recorded and they don't seem to point to failure here given that these disks live in a controlled environment - datacenter - so safe to remove 177 - wear leveling count and 178 - used reserved block couunt and 179 used reserved block count - counters that are more linked to performance / wear rather than failure 175 - program fail count - nr of times when rite to a flash memory failed - in context of hdds this is to do with its internal flash - firmware updates - when the internal flash fails completely the disk will stop working however this attribute records times flash writes failed - when flash becomes read only for example it has reached its end of life in the sense that it cannot be modified anymore however it does not mean the drive has failed ... only that one cannot update its flash - safe to ignore 173 - WearLevellerWorstCaseEraseCount - linked to above, similar explanation - safe to remove 170 - Reserved Block Count - safe to ignore (bad blocks do not necessarily mean disk failure) - safe to ignore
160, 161, 163,164, 165,166,167,168,169 - unknown/vendor specific - can be safely removed from the generic context however should be reviewed in vendor / model specific context
Remaining output to review:
count(distinct(backblaze_ml.drive_stats.smart_5_raw)) 13343 count(distinct(backblaze_ml.drive_stats.smart_160_raw)) 13 count(distinct(backblaze_ml.drive_stats.smart_161_raw)) 18 count(distinct(backblaze_ml.drive_stats.smart_163_raw)) 18 count(distinct(backblaze_ml.drive_stats.smart_164_raw)) 4767 count(distinct(backblaze_ml.drive_stats.smart_165_raw)) 2824 count(distinct(backblaze_ml.drive_stats.smart_166_raw)) 314 count(distinct(backblaze_ml.drive_stats.smart_167_raw)) 585 count(distinct(backblaze_ml.drive_stats.smart_168_raw)) 299 count(distinct(backblaze_ml.drive_stats.smart_169_raw)) 28 count(distinct(backblaze_ml.drive_stats.smart_178_raw)) 18 count(distinct(backblaze_ml.drive_stats.smart_183_raw)) 1023 count(distinct(backblaze_ml.drive_stats.smart_184_raw)) 194 count(distinct(backblaze_ml.drive_stats.smart_187_raw)) 1837 count(distinct(backblaze_ml.drive_stats.smart_188_raw)) 2476 count(distinct(backblaze_ml.drive_stats.smart_189_raw)) 2024 count(distinct(backblaze_ml.drive_stats.smart_191_raw)) 190896 count(distinct(backblaze_ml.drive_stats.smart_192_raw)) 15320 count(distinct(backblaze_ml.drive_stats.smart_193_raw)) 229873 count(distinct(backblaze_ml.drive_stats.smart_194_raw)) 64 count(distinct(backblaze_ml.drive_stats.smart_195_raw)) 4619605 count(distinct(backblaze_ml.drive_stats.smart_196_raw)) 2414 count(distinct(backblaze_ml.drive_stats.smart_197_raw)) 3236 count(distinct(backblaze_ml.drive_stats.smart_198_raw)) 1757 count(distinct(backblaze_ml.drive_stats.smart_199_raw)) 1491 count(distinct(backblaze_ml.drive_stats.smart_200_raw)) 207196 count(distinct(backblaze_ml.drive_stats.smart_225_raw)) 169663 count(distinct(backblaze_ml.drive_stats.smart_234_raw)) 2122 count(distinct(backblaze_ml.drive_stats.smart_235_raw)) 24883 count(distinct(backblaze_ml.drive_stats.smart_240_raw)) 1450722 count(distinct(backblaze_ml.drive_stats.smart_241_raw)) 11636846 count(distinct(backblaze_ml.drive_stats.smart_242_raw)) 11642421 count(distinct(backblaze_ml.drive_stats.smart_245_raw)) 4741 count(distinct(backblaze_ml.drive_stats.smart_246_raw)) 1531 count(distinct(backblaze_ml.drive_stats.smart_247_raw)) 1804 count(distinct(backblaze_ml.drive_stats.smart_248_raw)) 1804 count(distinct(backblaze_ml.drive_stats.smart_250_raw)) 65276
The "cleanup" query is below
alter table drive_stats drop column smart_255_raw, drop column smart_254_raw, drop column smart_244_raw, drop column smart_224_raw, drop column smart_218_raw, drop column smart_210_raw, drop column smart_206_raw, drop column smart_182_raw, drop column smart_181_raw, drop column smart_179_raw, drop column smart_176_raw, drop column smart_172_raw, drop column smart_171_raw, drop column smart_15_raw, drop column smart_18_raw, drop column smart_23_raw, drop column smart_24_raw, drop column smart_201_raw, drop column smart_1_raw, drop column smart_2_raw, drop column smart_3_raw, drop column smart_4_raw, drop column smart_7_raw, drop column smart_8_raw, drop column smart_9_raw, drop column smart_10_raw, drop column smart_11_raw, drop column smart_12_raw, drop column smart_13_raw, drop column smart_16_raw, drop column smart_17_raw, drop column smart_22_raw, drop column smart_174_raw, drop column smart_180_raw, drop column smart_251_raw, drop column smart_252_raw, drop column smart_232_raw, drop column smart_233_raw, drop column smart_231_raw, drop column smart_230_raw, drop column smart_226_raw, drop column smart_223_raw, drop column smart_225_raw, drop column smart_222_raw, drop column smart_220_raw, drop column smart_202_raw, drop column smart_190_raw, drop column smart_177_raw, drop column smart_178_raw, drop column smart_175_raw, drop column smart_173_raw, drop column smart_170_raw;
Output:
Query OK, 15653251 rows affected (11 min 30.60 sec) Records: 15653251 Duplicates: 0 Warnings: 0
Table structure after
mysql> describe drive_stats; +-----------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+-------------+------+-----+---------+-------+ | date | date | YES | MUL | NULL | | | serial_number | varchar(64) | YES | MUL | NULL | | | model | varchar(64) | YES | MUL | NULL | | | capacity_bytes | bigint | YES | MUL | NULL | | | days_to_failure | int | YES | | NULL | | | failure | int | YES | MUL | NULL | | | smart_5_raw | bigint | YES | | NULL | | | smart_160_raw | bigint | YES | | NULL | | | smart_161_raw | bigint | YES | | NULL | | | smart_163_raw | bigint | YES | | NULL | | | smart_164_raw | bigint | YES | | NULL | | | smart_165_raw | bigint | YES | | NULL | | | smart_166_raw | bigint | YES | | NULL | | | smart_167_raw | bigint | YES | | NULL | | | smart_168_raw | bigint | YES | | NULL | | | smart_169_raw | bigint | YES | | NULL | | | smart_183_raw | bigint | YES | | NULL | | | smart_184_raw | bigint | YES | | NULL | | | smart_187_raw | bigint | YES | | NULL | | | smart_188_raw | bigint | YES | | NULL | | | smart_189_raw | bigint | YES | | NULL | | | smart_191_raw | bigint | YES | | NULL | | | smart_192_raw | bigint | YES | | NULL | | | smart_193_raw | bigint | YES | | NULL | | | smart_194_raw | bigint | YES | | NULL | | | smart_195_raw | bigint | YES | | NULL | | | smart_196_raw | bigint | YES | | NULL | | | smart_197_raw | bigint | YES | | NULL | | | smart_198_raw | bigint | YES | | NULL | | | smart_199_raw | bigint | YES | | NULL | | | smart_200_raw | bigint | YES | | NULL | | | smart_234_raw | bigint | YES | | NULL | | | smart_235_raw | bigint | YES | | NULL | | | smart_240_raw | bigint | YES | | NULL | | | smart_241_raw | bigint | YES | | NULL | | | smart_242_raw | bigint | YES | | NULL | | | smart_245_raw | bigint | YES | | NULL | | | smart_246_raw | bigint | YES | | NULL | | | smart_247_raw | bigint | YES | | NULL | | | smart_248_raw | bigint | YES | | NULL | | | smart_250_raw | bigint | YES | | NULL | | +-----------------+-------------+------+-----+---------+-------+ 41 rows in set (0.02 sec)
Now we're in a much better place than we were initially however some analysis must be performed to identify how relevant these are in general or for specific vendors / models