Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Ligthtning Talks at MySQL Casual Vol.3

Ligthtning Talks at MySQL Casual Vol.3

Akinori Yoshida

April 19, 2012
Tweet

More Decks by Akinori Yoshida

Other Decks in Technology

Transcript

  1. 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/
  2. 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/
  3. Look at many samples of MySQL SHOW GLOBAL STATUS side-by-side.

    http://www.percona.com/doc/percona-toolkit/2.1/pt-mext.html
  4. 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
  5. 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
  6. define the trigger with the --function, --variable, and -- threshold

    options http://www.percona.com/doc/percona-toolkit/2.1/pt-stalk.html
  7. 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
  8. 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
  9. 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