Make sure write cache is enabled on your RAID cont

cenalulu

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.
               
               
               

本文来自ChinaUnix博客，如果查看原文请点：http://blog.chinaunix.net/u3/90603/showart_1966333.html