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