ソーシャルアプリ向けシステム監視運用の勘所
15 likes5,003 views
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.
![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](https://image.slidesharecdn.com/yapc2010-tatsuro-hisamori-101017094752-phpapp02/85/-11-320.jpg)
More Related Content
What's hot (20)
Viewers also liked (6)
Similar to ソーシャルアプリ向けシステム監視運用の勘所 (20)
Recently uploaded (20)
ソーシャルアプリ向けシステム監視運用の勘所
- 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