dw-tpain

Gordon KlokDemonware, Inc

A Demonware MySQL cluster

More info about the LSG see: “Erlang and First Person Shooters”

http://www.erlang-factory.com/upload/presentations/395/ErlangandFirst-PersonShooters.pdf

backup process

• innobackupex from Percona http://www.percona.com/doc/percona-xtrabackup/2.1/innobackupex/innobackupex_script.html

• Allows hot backup of database

• backups the underlying MySQL files replays a transaction log for changes occurring during the backup

• much faster to restore then SQL dumps.

Not good

Everything checks out on the database server

NFS Latency during backup window.

Paging...

So whats the problem?How is the slow performance of the

NAS affecting the client?

• /proc/sys/vm/dirty_background_ratio (default 10): Maximum percentage of active that can be filled with dirty pages before pdflush begins to write them

• /proc/sys/vm/dirty_expire_centiseconds (default 3000): In hundredths of a second, how long data can be in the page cache before it's considered expired and must be written at the next opportunity. Note that this default is very long: a full 30 seconds. That means that under normal circumstances, unless you write enough to trigger the other pdflush method, Linux won't actually commit anything you write until 30 seconds later.

• See “The Linux Page Cache and pdflush:Theory of Operation and Tuning for Write-Heavy Loads” http://www.westnet.com/~gsmith/content/linux-pdflush.htm

pd_fush

pd_flush continued

• Lots of memory in our database severs: not hitting the 10% threshold

• During the pd_flush interval about ~775MB of dirty pages were accumulating

• NFS write operations were taking about 0.6 sec per 65k block

• Meaning a11 second stall in all IO while waiting for dirty pages to be written out

pv(1) pv - monitor the progress of data through a pipe

-L RATE, --rate-limit RATE Limit the transfer to a maximum of RATE bytes per

second. A suffix of "k", "m", "g", or "t" can be added to denote kilobytes (*1024), megabytes, and so on.

Very useful feature:

Fixed limit problem

• If the pv rate limit is set to high we still interfere with MySQL by stealing IO to the database servers attached storage

• if the pv rate limit is set lower then the rate of incoming writes to mysql innobackupex will not finish until it fills the volume holding the xtrabackup logs.

• Need a dynamic rate limiter

-R PID, --remote PID If PID is an instance of pv that is already running, -R PID will cause that

instance to act as though it had been given this instance's command line instead. For example, if pv -L 123k is running with process ID 9876, then running pv -R 9876 -L 321k will cause it to start using a rate limit of 321k instead of 123k. Note that some options cannot be changed while running, such as -c, -l, -f, -E, and -S.

pv(1) pv - monitor the progress of data through a pipe

Making pv dynamic

Metric for IO Health

iostat -x -d -m 5 /dev/sdaDevice: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await svctm %util

sda 0.06 0.51 0.08 0.39 0.00 0.00 21.17 0.00 2.23 1.22 0.06

Through a bit of experimentation we found when avgqu-sz hovered around 1.0 for the device containing the mysql data files we achieved the most efficient backup with least

impact.

avgqu-sz - The average queue length of the requests that were issued to the device.

A PID Controller

Success!

Cool, where can I get tpain?

• Not open sourced yet, working on it! check https://github.com/Demonware

• Can’t wait? simple to create yourself (Python)

• PID controller: http://code.activestate.com/recipes/577231-discrete-pid-controller/

• Async read iostat output http://stefaanlippens.net/python-asynchronous-subprocess-pipe-reading

• PID Values P=0.060, I=0.0001, D=0.03, limits in KB/s use iostat -x -d 5 /dev, discard first sample