SlideShare a Scribd company logo

ソーシャルアプリ向けシステム監視運用の勘所

•
•
Tatsuro Hisamori
Tatsuro Hisamori

Tatsuro Hisamori from DeNA Co., Ltd. discusses mbga OpenPlatform and summarizes techniques for optimizing database and application performance including using tcpdump, Wireshark, and mk-query-digest to analyze SQL queries and network traffic. He also provides examples of optimizing resources and response times by distributing applications across virtual machines and monitoring CPU usage, database connections, and I/O wait times.

1 of 35
Downloaded 16 times
Tatsuro Hisamori DeNA Co.,Ltd. 2010 10 17
• (Tatsuro Hisamori) • • mbga OpenPlatform • / • • etc 2010 10 17
mbga OpenPlatform • 16:00 id:ZIGOROu Inside mbga Platform (^0^v 2010 10 17
• Social Application / 2010 10 17
• • 2010 10 17
2010 10 17
• • • Gadget / API • • Platform • • tcpdump 2010 10 17
• • • DB • 2010 10 17
• • • DB • • • 2010 10 17
• request -> response • WAF • request finalize • Apache • CustomLog %D 2010 10 17
use Time::HiRes qw/gettimeofday tv_interval/; sub pre_request { my ( $c, $args ) = @_; $c->stash->{__start_time} = [gettimeofday]; } sub finalize { my ( $c, $args ) = @_; $c->log->debug( $process_time = tv_interval($c->stash->{__start_time}, [gettimeofday])); } 2010 10 17
• %D GadgetServer • %D request line response # protocol.c (httpd-2.2.16) static int read_request_line(request_rec *r, apr_bucket_brigade *bb) { r->request_time = apr_time_now(); } 2010 10 17
• GadgetServer • alarm() SIGALRM # eval { local $SIG{ALRM} = sub {die}; alarm($TIMEOUT); $response = $ua->request($URL); alarm(0); } ) 2010 10 17
• TCP • tcpdump + wireshark Casual (^0^v 2010 10 17
$ tcpdump -i bond0 -s 1500 -n –w tcp.dump 'host gadgetserver' • -w • wireshark • stream 2010 10 17
DB • DB • DBI::Profile • DBIx::ProfileManager • mk-query-digest(maatkit) 2010 10 17
DBI::Profile • DBI use DBI; use DBI::Profile; my $dbh = DBI->connect("dbi:mysql:db=test;”,"root", ””); $dbh->{Profile} = q|!Statement|; $dbh->selectall_arrayref("SELECT ?", undef, $_) for ( qw/1 2 3/ ); DBI::Profile: 0.000399s 91.38% (7 calls) test.pl @ 2010-10-12 21:08:03 'SELECT ?' => 0.000399s / 7 = 0.000057s avg (first 0.000177s, min 0.000002s, max 0.000177s) 2010 10 17
DBIx::ProfileManager • handle attibute use DBI; use DBIx::ProfileManager; my $dbh = DBI->connect(...); my $pm = DBIx::ProfileManager->new; $pm->profile_start; my $res = $dbh->selectall_arrayref(...); $pm->profile_stop; my @results = $pm->data_formatted; local $, = "n"; print @results; 2010 10 17
DBIx::ProfileManager • profile_start profile_stop • data_formatted format # sample $pm->data_formatted( q|%{statement} : %{max}, %{min}, %{avg}| ); • DeNA • http://engineer.dena.jp/2010/03/dbixprofilemanager-sql-profiling.html 2010 10 17
Catalyst # MyApp.pm use DBIx::ProfileManager; sub prepare_request { my ( $c, $args ) = @_; my $pm = DBIx::ProfileManager->new(%config); $pm->profile_start; $c->stash->{__dbix_profilemanager} = $pm; } sub finalize { my ( $c, $args ) = @_; my $pm = $c->stash->{__dbix_priflemanager}; $pm->profile_stop; # $pm->data_formatted } 2010 10 17
DB • slowlog mk-query-digest # Query 8: 44.67 QPS, 0.13x concurrency, ID 0x6516D7E6698E425A at byte 108467522 # Attribute pct total min max avg 95% stddev median # ========= ====== ======= ======= ======= ======= ======= ======= ======= # Count 2 317 # Exec time 2 944ms 0 205ms 3ms 2ms 22ms 108us # Hosts 181 10.5.9.20 (7/2%), 10.5.10.157 (6/1%)... 179 more # Databases 1 platform # Time range 2010-10-12 13:21:53.218711 to 2010-10-12 13:22:00.315719 # bytes 1 20.43k 66 68 66.01 65.89 0 65.89 # Errors 1 none # Rows affe 0 0 0 0 0 0 0 0 # Warning c 0 0 0 0 0 0 0 0 # Query_time distribution # 1us # 10us #################################################### # 100us ################################################################ # 1ms ######### # 10ms # # 100ms # # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `db` LIKE 'entry'G # SHOW CREATE TABLE `db`.`entry`G # EXPLAIN select id, type from entry where id = xxx G 2010 10 17
DB • # Query_time distribution # 1us # 10us #################################################### # 100us ###################################################### # 1ms ######### # 10ms # # 100ms # # 1s # 10s+ • • SQL explain 2010 10 17
• • • ↓ mk-query-digest tcpdump !! mysql dump OK 2010 10 17
DB • • • • • • 2010 10 17
DB • • $mysqladmin -uroot -p extended-STATUS | egrep '(Max|Threads_)' | Max_used_connections | 68 | | Threads_cached | 36 | | Threads_connected | 32 | | Threads_created | 68 | | Threads_running |5 | 2010 10 17
DB • • $ mysqladmin -uroot -p -hdbsrv status | awk '{print $22}' 9780.895 2010 10 17
DB • • $ sar 13:00:01 CPU %user %nice %system %iowait %idle 13:10:01 all 0.97 0.00 0.32 0.36 98.35 13:20:01 all 0.80 0.00 0.30 0.25 98.65 13:30:01 all 0.81 0.00 0.29 0.28 98.62 13:40:01 all 0.73 0.00 0.28 0.24 98.76 2010 10 17
ex. open Platform • • • • • 2010 10 17
• • from tcpdump & GadgetServer timeout • 100 200ms • ex. 2010 10 17
• • ping, traceroute etc • • 10ms ( ) • EC2 100ms 200ms 2010 10 17
• • platform <-> • <-> (Web <-> DB) • VM • I/O • 2010 10 17
• • ex. Platform @mbga OpenPlatform • TCO 2010 10 17
• • • DB • • • 2010 10 17
2010 10 17
Thanks! 2010 10 17

More Related Content

ソーシャルアプリ向けシステム監視運用の勘所

  • 1. Tatsuro Hisamori DeNA Co.,Ltd. 2010 10 17
  • 2. • (Tatsuro Hisamori) • • mbga OpenPlatform • / • • etc 2010 10 17
  • 3. mbga OpenPlatform • 16:00 id:ZIGOROu Inside mbga Platform (^0^v 2010 10 17
  • 4. • Social Application / 2010 10 17
  • 5. • • 2010 10 17
  • 6. 2010 10 17
  • 7. • • • Gadget / API • • Platform • • tcpdump 2010 10 17
  • 8. • • • DB • 2010 10 17
  • 9. • • • DB • • • 2010 10 17
  • 10. • request -> response • WAF • request finalize • Apache • CustomLog %D 2010 10 17
  • 11. use Time::HiRes qw/gettimeofday tv_interval/; sub pre_request { my ( $c, $args ) = @_; $c->stash->{__start_time} = [gettimeofday]; } sub finalize { my ( $c, $args ) = @_; $c->log->debug( $process_time = tv_interval($c->stash->{__start_time}, [gettimeofday])); } 2010 10 17
  • 12. • %D GadgetServer • %D request line response # protocol.c (httpd-2.2.16) static int read_request_line(request_rec *r, apr_bucket_brigade *bb) { r->request_time = apr_time_now(); } 2010 10 17
  • 13. • GadgetServer • alarm() SIGALRM # eval { local $SIG{ALRM} = sub {die}; alarm($TIMEOUT); $response = $ua->request($URL); alarm(0); } ) 2010 10 17
  • 14. • TCP • tcpdump + wireshark Casual (^0^v 2010 10 17
  • 15. $ tcpdump -i bond0 -s 1500 -n –w tcp.dump 'host gadgetserver' • -w • wireshark • stream 2010 10 17
  • 16. DB • DB • DBI::Profile • DBIx::ProfileManager • mk-query-digest(maatkit) 2010 10 17
  • 17. DBI::Profile • DBI use DBI; use DBI::Profile; my $dbh = DBI->connect("dbi:mysql:db=test;”,"root", ””); $dbh->{Profile} = q|!Statement|; $dbh->selectall_arrayref("SELECT ?", undef, $_) for ( qw/1 2 3/ ); DBI::Profile: 0.000399s 91.38% (7 calls) test.pl @ 2010-10-12 21:08:03 'SELECT ?' => 0.000399s / 7 = 0.000057s avg (first 0.000177s, min 0.000002s, max 0.000177s) 2010 10 17
  • 18. DBIx::ProfileManager • handle attibute use DBI; use DBIx::ProfileManager; my $dbh = DBI->connect(...); my $pm = DBIx::ProfileManager->new; $pm->profile_start; my $res = $dbh->selectall_arrayref(...); $pm->profile_stop; my @results = $pm->data_formatted; local $, = "n"; print @results; 2010 10 17
  • 19. DBIx::ProfileManager • profile_start profile_stop • data_formatted format # sample $pm->data_formatted( q|%{statement} : %{max}, %{min}, %{avg}| ); • DeNA • http://engineer.dena.jp/2010/03/dbixprofilemanager-sql-profiling.html 2010 10 17
  • 20. Catalyst # MyApp.pm use DBIx::ProfileManager; sub prepare_request { my ( $c, $args ) = @_; my $pm = DBIx::ProfileManager->new(%config); $pm->profile_start; $c->stash->{__dbix_profilemanager} = $pm; } sub finalize { my ( $c, $args ) = @_; my $pm = $c->stash->{__dbix_priflemanager}; $pm->profile_stop; # $pm->data_formatted } 2010 10 17
  • 21. DB • slowlog mk-query-digest # Query 8: 44.67 QPS, 0.13x concurrency, ID 0x6516D7E6698E425A at byte 108467522 # Attribute pct total min max avg 95% stddev median # ========= ====== ======= ======= ======= ======= ======= ======= ======= # Count 2 317 # Exec time 2 944ms 0 205ms 3ms 2ms 22ms 108us # Hosts 181 10.5.9.20 (7/2%), 10.5.10.157 (6/1%)... 179 more # Databases 1 platform # Time range 2010-10-12 13:21:53.218711 to 2010-10-12 13:22:00.315719 # bytes 1 20.43k 66 68 66.01 65.89 0 65.89 # Errors 1 none # Rows affe 0 0 0 0 0 0 0 0 # Warning c 0 0 0 0 0 0 0 0 # Query_time distribution # 1us # 10us #################################################### # 100us ################################################################ # 1ms ######### # 10ms # # 100ms # # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `db` LIKE 'entry'G # SHOW CREATE TABLE `db`.`entry`G # EXPLAIN select id, type from entry where id = xxx G 2010 10 17
  • 22. DB • # Query_time distribution # 1us # 10us #################################################### # 100us ###################################################### # 1ms ######### # 10ms # # 100ms # # 1s # 10s+ • • SQL explain 2010 10 17
  • 23. • • • ↓ mk-query-digest tcpdump !! mysql dump OK 2010 10 17
  • 24. DB • • • • • • 2010 10 17
  • 25. DB • • $mysqladmin -uroot -p extended-STATUS | egrep '(Max|Threads_)' | Max_used_connections | 68 | | Threads_cached | 36 | | Threads_connected | 32 | | Threads_created | 68 | | Threads_running |5 | 2010 10 17
  • 26. DB • • $ mysqladmin -uroot -p -hdbsrv status | awk '{print $22}' 9780.895 2010 10 17
  • 27. DB • • $ sar 13:00:01 CPU %user %nice %system %iowait %idle 13:10:01 all 0.97 0.00 0.32 0.36 98.35 13:20:01 all 0.80 0.00 0.30 0.25 98.65 13:30:01 all 0.81 0.00 0.29 0.28 98.62 13:40:01 all 0.73 0.00 0.28 0.24 98.76 2010 10 17
  • 28. ex. open Platform • • • • • 2010 10 17
  • 29. • • from tcpdump & GadgetServer timeout • 100 200ms • ex. 2010 10 17
  • 30. • • ping, traceroute etc • • 10ms ( ) • EC2 100ms 200ms 2010 10 17
  • 31. • • platform <-> • <-> (Web <-> DB) • VM • I/O • 2010 10 17
  • 32. • • ex. Platform @mbga OpenPlatform • TCO 2010 10 17
  • 33. • • • DB • • • 2010 10 17
  • 34. 2010 10 17
  • 35. Thanks! 2010 10 17