Ligthtning Talks at MySQL Casual Vol.3

Transcript

1. MySQL Casual
   Talks Vol.3
   Lightning Talks
   Akinori Yoshida
   @marqs
   

   View Slide

2. mitsubachiworks inc.
   

   View Slide

3. We love Maatkit
   

   View Slide

4. lots of Hatena Bookmarks
   

   View Slide

5. Ops guys at Pinterest use Maatkit
   http://speakerdeck.com/u/yashh/p/scaling-pinterest
   

   View Slide

6. 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/
   

   View Slide

7. Maatkit
   Percona Toolkit
   

   View Slide

8. 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/
   

   View Slide

9. some new tools.
   

   View Slide

10. pt-mext
    

    View Slide

11. Look at many samples of
    MySQL SHOW GLOBAL
    STATUS side-by-side.
    http://www.percona.com/doc/percona-toolkit/2.1/pt-mext.html
    

    View Slide

12. 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
    

    View Slide

13. pt-online-schema-change
    

    View Slide

14. @kazeburo
    

    View Slide

15. pt-stalk
    

    View Slide

16. 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
    

    View Slide

17. define the trigger with the
    --function, --variable, and --
    threshold options
    http://www.percona.com/doc/percona-toolkit/2.1/pt-stalk.html
    

    View Slide

18. 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
    

    View Slide

19. 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
    

    View Slide

20. 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
    

    View Slide

21. very powerful
    if we can set appropriate
    trigger :)
    

    View Slide

22. one big drawback
    

    View Slide

23. Percona Toolkit does *not*
    have a tool correspond to
    mk-slave-move
    

    View Slide

24. http://www.percona.com/software/percona-toolkit/
    

    View Slide

25. thanks!!
    

    View Slide