I had a SQL query that had to update various indexed unique fields in my database which went something like this:

UPDATE page SET path = REPLACE(path, 'junk', '') ON DUPLICATE KEY DELETE path

Obviously this isn’t a valid MySQL query. The work around is to use UPDATE IGNORE and then delete any rows after which contains the substring ‘junk’:

UPDATE IGNORE page SET path = REPLACE(path, 'junk', '');
DELETE FROM page WHERE path LIKE '%junk%'

Western Digital 2TB WD20EARS & High Load_Cycle_Count

After buying 2 new 2TB Western Digital Green hard drives and setting it up as part of a Linux software-based Raid 5, I noticed that the drives would ‘click’ every time a file is accessed after a period of inactivity. Running smartctl -a /dev/ would also trigger this ‘click’ to happen and would report the Load_Cycle_Count increment by one. What appears to be happening is the Western Digital drives are automatically parking the disk heads after a period of 8 seconds of inactivity. This will cause the head to ‘click’ every time it unparks when an IO request is made. Unfortunately, the constant park/unparking of the heads causes an unnecessary amount of strain on the disks, which I very much want to minimize on a new set of disks.

The SMART report showed the following:

Device Model:     WDC WD20EARS-00J99B0
Serial Number:    WD-WCAWZ0840640
Firmware Version: 80.00A80
User Capacity:    2,000,398,934,016 bytes
Device is:        Not in smartctl database [for details use: -P showall]
ATA Version is:   8
ATA Standard is:  Exact ATA specification draft version not indicated
Local Time is:    Fri Sep  2 23:26:17 2011 MDT
SMART support is: Available - device has SMART capability.
SMART support is: Enabled

SMART overall-health self-assessment test result: PASSED

General SMART Values:
Offline data collection status:  (0x80)Offline data collection activity
was never started.
Auto Offline Data Collection: Enabled.
Self-test execution status:      (   0)The previous self-test routine completed
without error or no self-test has ever 
been run.
Total time to complete Offline 
data collection:  (40200) seconds.
Offline data collection
capabilities:  (0x7b) SMART execute Offline immediate.
Auto Offline data collection on/off support.
Suspend Offline collection upon new
Offline surface scan supported.
Self-test supported.
Conveyance Self-test supported.
Selective Self-test supported.
SMART capabilities:            (0x0003)Saves SMART data before entering
power-saving mode.
Supports SMART auto save timer.
Error logging capability:        (0x01)Error logging supported.
General Purpose Logging supported.
Short self-test routine 
recommended polling time:  (   2) minutes.
Extended self-test routine
recommended polling time:  ( 255) minutes.
Conveyance self-test routine
recommended polling time:  (   5) minutes.
SCT capabilities:        (0x3035)SCT Status supported.
SCT Feature Control supported.
SCT Data Table supported.

SMART Attributes Data Structure revision number: 16
Vendor Specific SMART Attributes with Thresholds:
  1 Raw_Read_Error_Rate     0x002f   200   200   051    Pre-fail  Always       -       0
  3 Spin_Up_Time            0x0027   151   151   021    Pre-fail  Always       -       9441
  4 Start_Stop_Count        0x0032   100   100   000    Old_age   Always       -       16
  5 Reallocated_Sector_Ct   0x0033   200   200   140    Pre-fail  Always       -       0
  7 Seek_Error_Rate         0x002e   200   200   000    Old_age   Always       -       0
  9 Power_On_Hours          0x0032   100   100   000    Old_age   Always       -       72
 10 Spin_Retry_Count        0x0032   100   253   000    Old_age   Always       -       0
 11 Calibration_Retry_Count 0x0032   100   253   000    Old_age   Always       -       0
 12 Power_Cycle_Count       0x0032   100   100   000    Old_age   Always       -       12
192 Power-Off_Retract_Count 0x0032   200   200   000    Old_age   Always       -       8
193 Load_Cycle_Count        0x0032   200   200   000    Old_age   Always       -       121
194 Temperature_Celsius     0x0022   125   121   000    Old_age   Always       -       27
196 Reallocated_Event_Count 0x0032   200   200   000    Old_age   Always       -       0
197 Current_Pending_Sector  0x0032   200   200   000    Old_age   Always       -       0
198 Offline_Uncorrectable   0x0030   100   253   000    Old_age   Offline      -       0
199 UDMA_CRC_Error_Count    0x0032   200   200   000    Old_age   Always       -       0
200 Multi_Zone_Error_Rate   0x0008   100   253   000    Old_age   Offline      -       0

Although the Load_Cycle_count isn’t that high, the drive was mostly idle or busy rebuilding the raid array which doesn’t justify the ~1.7 cycle counts per hour.

The solution to this is to run Western Digital’s WDIdle3 utility which allows a custom timeout to be set instead of the default 8 seconds. Unfortunately, WDidle3 is a DOS only program and therefore the computer must be booted in DOS before the program can be executed.

If you’re like me and don’t have DOS readily available and have no CDRom or a floopy drive, the easiest way is to load up FreeDOS under PXE with the WDIdle3 utility as part of the FreeDOS image.
Step 1: Prepare the fdboot.img image with the WDidle3 utility

mount -o loop -t vfat fdboot.img /mnt/freedos
cp wdidle3.exe /mnt/freedos
umount /mnt/freedos

Step 2: Configure PXE to boot the fdboot.img image as a ramdisk

vi /tftpboot/pxelinux.cfg/default 

# Then add the following entry:
label FreeDOS
kernel memdisk
append initrd=fdboot.img

Step 3: Boot into PXE and boot into FreeDOS. A grub menu should appear:

Select #4 “FreeDOS Live CD with HIMEM only”

Step 4: Run the WDIdle3 utility with /S or /D to disable the timer

* Yes, these screenshots were done under vmware. Couldn’t take a picture of it in action.

After disabling the timer in my WD20EARS, the Load_Cycle_Count remains virtually static and the ‘click’s have disappeared.