SlideShare a Scribd company logo

Yapc asia 2011_zigorou

•
•
Toru Yamaguchi
Toru Yamaguchi

1. The document discusses various issues that can cause failures when building APIs to access database systems, such as deadlocks from concurrent updates and purging data inconsistencies between master and slave databases. 2. It proposes solutions to these issues like using queues to defer updates, disabling binary logging to bypass replication, and partitioning or sharding data. 3. When building a friend timeline API, challenges include efficiently querying the necessary data across multiple database tables to return a paginated result with the total count. Solutions presented include using temporary tables and iteration in batches to query the data.

1 of 42
Downloaded 17 times
The case file of Mobage Open Platform Mobage Toru Yamaguchi <zigorou@cpan.org> http://d.hatena.ne.jp/ZIGOROu/ DeNA co.,ltd. 2011/10/14 YAPC Asia 2011
!  !  !  !  ! 
!   Wikipedia !  !  !  !  (Cause Analysis) !  (Failure Prevention) !  (Knowledge Distribution)
( )
1 DeadLock !  API (T ) Dead Lock !  API Internal Server Error !  !  !   Internal Server Error
(1) !  API GET ! totalResults OpenSearch !  totalResults SQL_CALC_FOUND_ROWS !  SELECT !  totalResults !   INSERT, DELETE TRIGGER
(2) !  SHOW INNODB STATUS !   Dead Lock !  !  !  UPDATE !  UPDATE ! 
BEGIN; SELECT id FROM somedata_group WHERE name = ?; INSERT INTO somedata(id, group_id, data, published_on) VALUES(?, ?, ?, ?); UPDATE somedata_summary SET total_results = total_results + 1, updated_on = ? WHERE group_id = ?; COMMIT;
(3) !   Group !  !  !  !  UPDATE increment !  MySQL Dead Lock
(4) Transaction group_id = 1 Transaction group_id = 2 group_id = 3 Transaction group_id = 3 UPDATE group_id = 4 group_id = 5 Transaction
(1) !  !  UPDATE w !  SELECT totalResults !   INSERT incr/decr QUEUE UPDATE !  totalResults !  xaicron
(2) /* API */ BEGIN; INSERT INTO somedata(id, group_id, data, published_on) VALUES(?, ?, ?, ?); INSERT INTO somedata_summary_queue(id, group_id, affected_number, published_on); COMMIT;
(3) /* Batch Worker */ BEGIN; SELECT id, group_id, affected_number FROM somegroup_summary_queue ORDER BY published_on ASC LIMIT 100; /* group UPDATE */ UPDATE somedata_summary SET total_results = ?, updated_on = ? WHERE group_id = ?; … DELETE somegroup_summary_queue WHERE id IN (?, ?, …, ?); COMMIT;
(1) !  !  !  !  InnoDB QUEUE ( ) UPDATE ! 
(2) !  ! InnoDB QUEUE INDEX !   INSERT, DELETE enqueue !  queue WHERE id !  FOR UPDATE ! 
Yapc asia 2011_zigorou
2 INSERT vs DELETE !  !  !  API ( T ) !  1 INSERT !  API purge !  purge ( ) INSERT !  !  shard
( )
Yapc asia 2011_zigorou
(1) !  purge !  MASTER DELETE SLAVE ww !   DELETE !  redo SLAVE !  sleep wait DB wait !  wait !  wait !   SLAVE Seconds_Behind_Master wait !  wait
Loop::Sustainable (1) !  ! https://github.com/zigorou/p5-loop-sustainable !  !  !  !  wait !  !  Seconds_Behind_Master !  b ry
Loop::Sustainable (2) process process 2sec process process process 2sec process process 2sec process process 2sec Seconds_Behind_Master : 10 sec process 2sec
(2) !  w !   SET SESSION sql_log_bin = 0 !   MASTER SLAVE DELETE !  DELETE !  prefork DELETE xaicron ( )
(3) !  !   DELETE !   DELETE purge !  partitioning API !   STOP SLAVE DB Master !  ! 
(1) !  DELETE !  DELETE !   DELETE SELECT !  MySQL !  !  !  RDBMS !  !   SET SESSION sql_log_bin=0 !  DELETE
(2) !  DELETE INSERT !  Partitioning !  Sharding !  ! mysqldump –w WHERE
(1) !  !  !  !   PRIMARY KEY !  !  ALTER TABLE some_table ADD KEY !  !  M
(2) !  !  !  DML !  incr/decr !  ! 
Yapc asia 2011_zigorou
3 !  !  !  API Push friend timeline !  DB !  API
(1) !  !  !  !  !  count, startIndex (LIMIT/OFFSET ) ! 
SELECT friend_user_id FROM friends WHERE user_id = ?; /* */ SELECT user_id FROM user_app WHERE app_id = ? AND user_id IN (?, ?, …, ?); /* */ SELECT SQL_CALC_FOUND_ROWS user_id, nickname FROM users WHERE user_id IN(?, ?, …, ?) LIMIT 50 OFFSET 0; SELECT FOUND_ROWS();
LIMIT 50 valid users OFFSET 0 500 users installed users 750 users friend 1000 users
(2) !   friends n:m, user_app 1:n, users 1:1 !   friends ( 1000 ) user_app !   users SQL_CALC_FOUND_ROWS LIMIT, OFFSET !  friends !  Temporary Table INSERT users JOIN
(1) !  ! selectall_arrayref !  !   SQL_CALC_FOUND_ROWS !   LIMIT, OFFSET COUNT(*) !  !   Temporary Table !  !  !  user_id user_id LIMIT, OFFSET
(2) !   prepare, fetchall_arrayref($max_rows) !   DBI::st fetchall_arrayref($max_rows) $max_rows !  !  friends 1000 !  1000 users user_app !   1000 !  1000 users !  1000 ! 
installed users valid users friend installed users 750 users 750 users 1000 users 1000 users installed users valid users 750 users 750 users friend installed users 1000 users 1000 users 100 users installed users 750 users installed users 250 users friend 1000 users end iteration
(3) !  !  !   Iterator::GroupedRange ! http://search.cpan.org/dist/Iterator-GroupedRange/ !  List::MoreUtils natatime I::GR (fetchall_arrayref($max_rows) )
(4) !  !   friends DB 1000 memcached get/set ! user_app mapping memcached ( install ) !  iteration DB DB iteration set !  3 !  xaicron
(1) !  !  !  !  w !  kazuho
Yapc asia 2011_zigorou
!  !  !  !  !  !  xaicron !  !  ! 

More Related Content

Yapc asia 2011_zigorou

  • 1. The case file of Mobage Open Platform Mobage Toru Yamaguchi <[email protected]> http://d.hatena.ne.jp/ZIGOROu/ DeNA co.,ltd. 2011/10/14 YAPC Asia 2011
  • 2. !  !  !  !  ! 
  • 3. !   Wikipedia !  !  !  !  (Cause Analysis) !  (Failure Prevention) !  (Knowledge Distribution)
  • 4. ( )
  • 5. 1 DeadLock !  API (T ) Dead Lock !  API Internal Server Error !  !  !   Internal Server Error
  • 6. (1) !  API GET ! totalResults OpenSearch !  totalResults SQL_CALC_FOUND_ROWS !  SELECT !  totalResults !   INSERT, DELETE TRIGGER
  • 7. (2) !  SHOW INNODB STATUS !   Dead Lock !  !  !  UPDATE !  UPDATE ! 
  • 8. BEGIN; SELECT id FROM somedata_group WHERE name = ?; INSERT INTO somedata(id, group_id, data, published_on) VALUES(?, ?, ?, ?); UPDATE somedata_summary SET total_results = total_results + 1, updated_on = ? WHERE group_id = ?; COMMIT;
  • 9. (3) !   Group !  !  !  !  UPDATE increment !  MySQL Dead Lock
  • 10. (4) Transaction group_id = 1 Transaction group_id = 2 group_id = 3 Transaction group_id = 3 UPDATE group_id = 4 group_id = 5 Transaction
  • 11. (1) !  !  UPDATE w !  SELECT totalResults !   INSERT incr/decr QUEUE UPDATE !  totalResults !  xaicron
  • 12. (2) /* API */ BEGIN; INSERT INTO somedata(id, group_id, data, published_on) VALUES(?, ?, ?, ?); INSERT INTO somedata_summary_queue(id, group_id, affected_number, published_on); COMMIT;
  • 13. (3) /* Batch Worker */ BEGIN; SELECT id, group_id, affected_number FROM somegroup_summary_queue ORDER BY published_on ASC LIMIT 100; /* group UPDATE */ UPDATE somedata_summary SET total_results = ?, updated_on = ? WHERE group_id = ?; … DELETE somegroup_summary_queue WHERE id IN (?, ?, …, ?); COMMIT;
  • 14. (1) !  !  !  !  InnoDB QUEUE ( ) UPDATE ! 
  • 15. (2) !  ! InnoDB QUEUE INDEX !   INSERT, DELETE enqueue !  queue WHERE id !  FOR UPDATE ! 
  • 17. 2 INSERT vs DELETE !  !  !  API ( T ) !  1 INSERT !  API purge !  purge ( ) INSERT !  !  shard
  • 18. ( )
  • 20. (1) !  purge !  MASTER DELETE SLAVE ww !   DELETE !  redo SLAVE !  sleep wait DB wait !  wait !  wait !   SLAVE Seconds_Behind_Master wait !  wait
  • 21. Loop::Sustainable (1) !  ! https://github.com/zigorou/p5-loop-sustainable !  !  !  !  wait !  !  Seconds_Behind_Master !  b ry
  • 22. Loop::Sustainable (2) process process 2sec process process process 2sec process process 2sec process process 2sec Seconds_Behind_Master : 10 sec process 2sec
  • 23. (2) !  w !   SET SESSION sql_log_bin = 0 !   MASTER SLAVE DELETE !  DELETE !  prefork DELETE xaicron ( )
  • 24. (3) !  !   DELETE !   DELETE purge !  partitioning API !   STOP SLAVE DB Master !  ! 
  • 25. (1) !  DELETE !  DELETE !   DELETE SELECT !  MySQL !  !  !  RDBMS !  !   SET SESSION sql_log_bin=0 !  DELETE
  • 26. (2) !  DELETE INSERT !  Partitioning !  Sharding !  ! mysqldump –w WHERE
  • 27. (1) !  !  !  !   PRIMARY KEY !  !  ALTER TABLE some_table ADD KEY !  !  M
  • 28. (2) !  !  !  DML !  incr/decr !  ! 
  • 30. 3 !  !  !  API Push friend timeline !  DB !  API
  • 31. (1) !  !  !  !  !  count, startIndex (LIMIT/OFFSET ) ! 
  • 32. SELECT friend_user_id FROM friends WHERE user_id = ?; /* */ SELECT user_id FROM user_app WHERE app_id = ? AND user_id IN (?, ?, …, ?); /* */ SELECT SQL_CALC_FOUND_ROWS user_id, nickname FROM users WHERE user_id IN(?, ?, …, ?) LIMIT 50 OFFSET 0; SELECT FOUND_ROWS();
  • 33. LIMIT 50 valid users OFFSET 0 500 users installed users 750 users friend 1000 users
  • 34. (2) !   friends n:m, user_app 1:n, users 1:1 !   friends ( 1000 ) user_app !   users SQL_CALC_FOUND_ROWS LIMIT, OFFSET !  friends !  Temporary Table INSERT users JOIN
  • 35. (1) !  ! selectall_arrayref !  !   SQL_CALC_FOUND_ROWS !   LIMIT, OFFSET COUNT(*) !  !   Temporary Table !  !  !  user_id user_id LIMIT, OFFSET
  • 36. (2) !   prepare, fetchall_arrayref($max_rows) !   DBI::st fetchall_arrayref($max_rows) $max_rows !  !  friends 1000 !  1000 users user_app !   1000 !  1000 users !  1000 ! 
  • 37. installed users valid users friend installed users 750 users 750 users 1000 users 1000 users installed users valid users 750 users 750 users friend installed users 1000 users 1000 users 100 users installed users 750 users installed users 250 users friend 1000 users end iteration
  • 38. (3) !  !  !   Iterator::GroupedRange ! http://search.cpan.org/dist/Iterator-GroupedRange/ !  List::MoreUtils natatime I::GR (fetchall_arrayref($max_rows) )
  • 39. (4) !  !   friends DB 1000 memcached get/set ! user_app mapping memcached ( install ) !  iteration DB DB iteration set !  3 !  xaicron
  • 40. (1) !  !  !  !  w !  kazuho
  • 42. !  !  !  !  !  !  xaicron !  !  !