Make sure write cache is enabled on your RAID cont

Using a write cache protected by battery (BBWC) is well known and one of the best practices in RDBMS world. But I have frequently seen situations that people do not set write cache properly. Sometimes they just forget to enable write cache. Sometimes write cache is disabled even though they say they set properly.

Make sure that BBWC is enabled. If not enabled, you will be able to easily get better performance by just enabling it. The following is a DBT-2 example.

Write cache is disabled:

# iostat -xm 10
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
        21.16    0.00    6.14   29.77    0.00   42.93

Device:         rrqm/s   wrqm/s   r/s   w/s    rMB/s
sdb               2.60   389.01 283.12 47.35     4.86
wMB/s avgrq-sz avgqu-sz   await  svctm  %util
2.19    43.67     4.89   14.76   3.02  99.83


Write cache is enabled:

# iostat -xm 10
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
        40.03    0.00   16.51   16.52    0.00   26.94

Device:         rrqm/s   wrqm/s   r/s   w/s    rMB/s
sdb               6.39   368.53 543.06 490.41     6.71
wMB/s avgrq-sz avgqu-sz   await  svctm  %util
3.90    21.02     3.29    3.20   0.90  92.66

Both run same applications(DBT-2), but the server activity was significantly different each other. I got 85% better result when write cache is enabled.


Checking write cache and battery status

BBWC is mostly equipped with H/W raid controller so operational command depends on products. Here is an example of "arcconf" command result.

# /usr/StorMan/arcconf GETCONFIG 1 AL
...
--------------------------------------------------------
Controller Battery Information
--------------------------------------------------------
Status                          : Optimal
Over temperature                : No
Capacity remaining              : 99 percent
Time remaining (at current draw): 3 days, 1 hours, 11 minutes

--------------------------------------------------------
Logical device information
--------------------------------------------------------
Logical device number 0
...
Read-cache mode    : Disabled
Write-cache mode   : Enabled (write-back)
Write-cache setting: Enabled (write-back) when
protected by battery
...

--------------------------------------------------------
Physical Device information
--------------------------------------------------------
Device #0
Device is a Hard drive
...
Size        : 140009 MB
Write Cache : Disabled (write-through)
...

Write cache should be enabled only when battery backup is working. In other words:
- Write cache on logical device (H/W raid controller) is enabled when protected by battery
- Write cache on physical device is disabled
- The battery has enough capacity and long enough remaining time

I recommend DBAs to monitor write cache status regularly (adding this to your monitoring scripts), including battery status checking. Long time ago I was asked for urgent help to fix a problem that application performance suddenly went down. I looked into problems then found that write cache unexpectedly turned off because a battery was expired. If you successfully detected that battery capacity was decreased before write cache was disabled, you would be able to take an action before server performance suddenly goes down (i.e. allocating scheduled down time in order to replace the battery).


Quick health check with mysql commands

If you are not familiar with H/W raid controller specific command but want to check write cache status quickly, using mysqlslap or stored procedure is easy.

mysqlslap:
$ mysql -e "set global innodb_flush_log_at_trx_commit=1"
$ mysqlslap --concurrency=1 --iterations=1 --engine=innodb \
--auto-generate-sql --auto-generate-sql-load-type=write \
--number-of-queries=100000


stored procedure:
create table t (c1 int) engine=innodb;
delimiter //
create procedure sp1(IN i INTEGER)
BEGIN
DECLARE done INTEGER DEFAULT 0;
WHILE i > 0 DO
insert into t values (1);
SET i = i - 1;
END WHILE;
END;
//
delimiter ;

set global innodb_flush_log_at_trx_commit=1;
call sp1(100000);

You will be able to insert thousands of records per second if write
cache is enabled. If disabled, only hundreds of inserts per second is
possible, so you can easily check.