Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Speaker Deck
PRO
Sign in
Sign up
for free
Ligthtning Talks at MySQL Casual Vol.3
Akinori Yoshida
April 19, 2012
Technology
3
7.2k
Ligthtning Talks at MySQL Casual Vol.3
Akinori Yoshida
April 19, 2012
Tweet
Share
More Decks by Akinori Yoshida
See All by Akinori Yoshida
marqs
1
8.2k
marqs
0
120
Other Decks in Technology
See All in Technology
s_uryu
0
180
koukyo1994
6
1.7k
110y
2
11k
mahito
0
240
dena_tech
0
220
pohjus
0
3.3k
miura55
0
470
dena_tech
0
180
benzookapi
1
450
kkosukeee
0
190
akitok_
2
790
will03
0
110
Featured
See All Featured
samlambert
237
10k
samanthasiow
56
6.4k
chriscoyier
683
180k
revolveconf
200
9.7k
michaelherold
225
8.5k
shpigford
165
19k
bkeepers
321
53k
schacon
145
6.6k
afnizarnur
176
14k
kneath
294
39k
lara
590
61k
eileencodes
113
25k
Transcript
MySQL Casual Talks Vol.3 Lightning Talks Akinori Yoshida @marqs
mitsubachiworks inc.
We love Maatkit
lots of Hatena Bookmarks
Ops guys at Pinterest use Maatkit http://speakerdeck.com/u/yashh/p/scaling-pinterest
Maatkit has become part of Percona Toolkit, and there will
be no further development or releases of Maatkit separately from Percona Toolkit. http://www.maatkit.org/
Maatkit Percona Toolkit
Percona Toolkit is derived from Maatkit and Aspersa, two of
the best- known toolkits for MySQL server administration. It is developed and supported by Percona Inc. http://www.percona.com/software/percona-toolkit/
some new tools.
pt-mext
Look at many samples of MySQL SHOW GLOBAL STATUS side-by-side.
http://www.percona.com/doc/percona-toolkit/2.1/pt-mext.html
server01:~$ pt-mext -r -- mysqladmin ext -i3 -c7 -uxxx -pxxx|grep
buffer Innodb_buffer_pool_pages_data 380 0 0 0 Innodb_buffer_pool_pages_dirty 9 2 2 -10 Innodb_buffer_pool_pages_flushed 2958 0 0 0 Innodb_buffer_pool_pages_free 131 0 0 0 Innodb_buffer_pool_pages_misc 1 0 0 0 Innodb_buffer_pool_pages_total 512 0 0 0 Innodb_buffer_pool_read_ahead_rnd 1 0 0 0 Innodb_buffer_pool_read_ahead_seq 0 0 0 0 Innodb_buffer_pool_read_requests 458289610 1801300 1720438 1144393 Innodb_buffer_pool_reads 12 0 0 0 Innodb_buffer_pool_wait_free 0 0 0 0 Innodb_buffer_pool_write_requests 386698 831 753 520
pt-online-schema-change
@kazeburo
pt-stalk
How do you solve intermittent MySQL problems when you can’t
observe them? That’s why pt-stalk exists. http://www.percona.com/doc/percona-toolkit/2.1/pt-stalk.html
define the trigger with the --function, --variable, and -- threshold
options http://www.percona.com/doc/percona-toolkit/2.1/pt-stalk.html
server01:~$ pt-stalk --cycles 2 --function processlist --variable State --match update
--threshold 10 --dest /var/tmp/ -- -uxxx -pxxx 2012_04_18_16_24_49 Starting /usr/bin/pt-stalk --function=processlist -- variable=State --threshold=10 --match=update --cycles=2 --interval=1 -- iterations= --run-time=30 --sleep=300 --dest=/var/tmp/ --prefix= --notify-by- email= --log=/var/log/pt-stalk.log --pid=/var/run/pt-stalk.pid 2012_04_18_16_24_50 Check results: State=6, matched=no, cycles_true=0 2012_04_18_16_24_55 Check results: State=78, matched=yes, cycles_true=1 2012_04_18_16_24_57 Check results: State=43, matched=yes, cycles_true=2 2012_04_18_16_24_58 Collect triggered 2012_04_18_16_25_01 Collector PID 14808 2012_04_18_16_25_01 Sleeping 300 seconds after collect pt-stalk
server01:/var/tmp$ ll total 2140 drwxrwxrwt 2 root root 4096 2012-04-18
16:27 ./ drwxr-xr-x 12 root root 4096 2012-02-22 05:54 ../ -rw-r--r-- 1 root root 10500 2012-04-18 16:26 2012_04_18_16_24_58-df -rw-r--r-- 1 root root 133 2012-04-18 16:26 2012_04_18_16_24_58-disk-space -rw-r--r-- 1 root root 33210 2012-04-18 16:26 2012_04_18_16_24_58-diskstats -rw-r--r-- 1 root root 17 2012-04-18 16:26 2012_04_18_16_24_58-hostname -rw-r--r-- 1 root root 34616 2012-04-18 16:25 2012_04_18_16_24_58- innodbstatus1 -rw-r--r-- 1 root root 34801 2012-04-18 16:26 2012_04_18_16_24_58- innodbstatus2 -rw-r--r-- 1 root root 31320 2012-04-18 16:27 2012_04_18_16_24_58-interrupts -rw-r--r-- 1 root root 7425 2012-04-18 16:25 2012_04_18_16_24_58-iostat -rw-r--r-- 1 root root 563 2012-04-18 16:25 2012_04_18_16_24_58-iostat- overall -rw-r--r-- 1 root root 4215 2012-04-18 16:25 2012_04_18_16_24_58-log_error -rw-r--r-- 1 root root 12620 2012-04-18 16:25 2012_04_18_16_24_58-lsof -rw-r--r-- 1 root root 36420 2012-04-18 16:26 2012_04_18_16_24_58-meminfo -rw-r--r-- 1 root root 8380 2012-04-18 16:25 2012_04_18_16_24_58-mpstat -rw-r--r-- 1 root root 608 2012-04-18 16:25 2012_04_18_16_24_58-mpstat- overall -rw-r--r-- 1 root root 73055 2012-04-18 16:25 2012_04_18_16_24_58-mutex- status1 -rw-r--r-- 1 root root 73061 2012-04-18 16:26 2012_04_18_16_24_58-mutex- status2 -rw-r--r-- 1 root root 460229 2012-04-18 16:25 2012_04_18_16_24_58-mysqladmin -rw-r--r-- 1 root root 20580 2012-04-18 16:26 2012_04_18_16_24_58-netstat
server01:/var/tmp$ pt-sift /var/tmp/ ======== server01 at 2012_04_18_16_24_58 DEFAULT (1 of
1) ======== --diskstats-- #ts device rd_s rd_avkb rd_mb_s rd_mrg rd_cnc rd_rt wr_s wr_avkb wr_mb_s wr_mrg wr_cnc wr_rt busy in_prg io_s qtime stime {29} xvda1 0.2 8.7 0.0 0% 0.0 4.7 31.7 7.0 0.2 31% 0.3 6.1 24% 0 31.9 0.8 5.2 xvda1 0% 35% 15% 30% 0% 65% 70% 65% . 50% 55% 60% 50% 60% . 50% 65% 55% 45% 65% 40% 60% 80% 5% 0% 15% 0% 25% 10% 0% --vmstat-- r b swpd free buff cache si so bi bo in cs us sy id wa 51 4 0 325532 97472 52648 0 0 6 36 15 328 2 1 84 2 16 1 0 320592 97552 53708 0 0 17 44 18 161 1 2 0 4 wa 0% . . . . . . . . . . . . . . 40% 0% . . . 10% 60% 50% 45% . . 50% 30% 45% 30% --innodb-- txns: 92xACTIVE (50s) 8xCOMMITTED (0s) 2xnot (0s) 8 queries inside InnoDB, 92 queries in queue Main thread: flushing log, pending reads 0, writes 1, flush 1 Log: lsn = 0, chkp = 0, chkp age = 0 Threads are waiting at: Threads are waiting on: --processlist-- State 91 Sending data 7 freeing items 3 1 update
very powerful if we can set appropriate trigger :)
one big drawback
Percona Toolkit does *not* have a tool correspond to mk-slave-move
http://www.percona.com/software/percona-toolkit/
thanks!!