realtime-ranking.pdf

Transcript

1. ϦΞϧλΠϜɾϥϯΩϯάͷ࣮૷ྫΛRailsͰॻ͍ͯΈͨ miio mitani http://qiita.com/items/83cb8cbcc6c5fd3cf121 1

2. ࣗݾ঺հ Έ͓͌ (miio) Kawaz ( http://www.kawaz.org ) LOCAL ( http://www.local.or.jp

   ) Twitter ( @ayako119 ) ࡳຈͰιʔγϟϧήʔϜ࡞ͬͯ·͢ 2

3. ࠓ౓rubykaigi2013ߦ͖ͬͯ·͢ 3

4. ϦΞϧλΠϜϥϯΩϯάͷ࣮૷ํ๏ 4

5. 5

6. ਅͬઌʹࢥ͍͖ͭͦ͏ͳ࣮૷ํ๏ 6

7. SELECT * FROM user_high_scores ORDER BY score DESC LIMIT 10

   7

8. େن໛Ͱͳ͚Ε͹ਖ਼௚ ͜ΕͰ΋ྑ͍͔΋͠Εͳ͍ 8

9. ϦΞϧλΠϜ͕mustͰͳ͚Ε͹ όονॲཧͰ΋͍͍͔΋͠Εͳ͍ 9

10. ໰୊ ΋͠΋ϦΞϧλΠϜੑ͕ήʔϜੑʹ௚݁͢ΔͳΒόονͰ͸μϝ Կ΋ߟ͑ͳ͍ͱɺϨίʔυ͕૿͑ΔͱϩοΫࡇΓൃੜ GREE͞ΜʹΑΔͱ10ສ݅͘Β͍͔Βͱ͔ ࣮ࡍʹ͸2ສ݅Ͱ΋ࢀরɾߋ৽ස౓͕ߴ͍ͱεϩʔϩάʹग़·͢ 10

11. ϩοΫͬͯԿʁ 11

12. ϩοΫͬͯԿʁ σʔλߋ৽࣌ʹɺෳ਺ಉ࣌ʹߋ৽͞Εͳ͍Α͏ͳ࢓૊Έ ϩοΫத͸ଞͷߋ৽͕ϒϩοΫ͞ΕΔ ΑͬͯɺϩοΫͷ͗͢͠͸଎౓͕஗͘ͳΔ ͋·Γෛՙ͕ߴ͘ͳ͍ͷʹੑೳ͕Ͱͳ͘ͳΔ౳ ϩοΫ͸େ͖͘෼͚ͯ2छྨ(MySQLͷ৔߹) ಡΈࠐΈ͸ڐՄ͢Δ͚Ͳॻ͖ࠐΊͳ͍ڞ༗ϩοΫ ಡΈࠐΈ΋ͤ͞ͳ͍ഉଞϩοΫ 12

13. ϩοΫͬͯԿʁ MySQLͷInnoDBͩͱߦϩοΫ͕࢖͑Δ ςʔϒϧϩοΫ͸ςʔϒϧશମΛϩοΫ͢Δ ߦϩοΫͩͱɺಛఆͷߦ͚ͩϩοΫ͢Δ ϩοΫର৅͕ॏͳΒͳ͚Ε͹ผʑͷϩοΫΛ͔͚Δ͜ͱ͕Ͱ͖Δ MySQLͷߦϩοΫ͸ΠϯσοΫε͕ޮ͔ͳ͍ͱςʔϒϧϩοΫʹͳΔ᠘͕͋Δ ΠϯσοΫεͱ͸ɺຊͷ΋͘͡ͷΑ͏ͳ΋ͷͰ͢ 13

14. σουϩοΫ ϩοΫΛద੾ʹ͔͚ͯ͋͛ͳ͍ͱɺϩοΫಉ͕࢜Ϳ͔ͭΔ͜ͱ͕͋Δ σουϩοΫͨ͠Βॲཧͱͯ͠͸ҟৗऴྃͯ͠͠·͏ ద੾ͳϩοΫ΍ɺϦτϥΠʹΑͬͯରԠ͢Δ ࠓճͷ࣮૷த΋σουϩοΫ஍ࠈʹૺ۰͠·ͨ͠ 14

15. ϩοΫ΍ΠϯσοΫεͷ࿩͸ ෼ྔతʹॻ͖͖Ε·ͤΜ ͝ΊΜͳ͍͞orz 15

16. ྑͦ͞͏ͳࢀߟจݙݟ͚ͭΔ GREE͞Μ΍CyberAgant͞Μ͕ࣅͨΑ͏ͳࣄྫΛެ։ http://labs.gree.jp/blog/2010/07/456/ http://www.cyberagent.co.jp/technology/pdf/2011_10.pdf ͋ͷେखاۀ͕࣮ફͯ͠ΔͳΒࢼ͞ͳ͍ख͸ແ͍ ઌਓͷ஌ܙେࣄɺंྠͷ࠶։ൃ͸ۃྗ͠ͳ͍ 16

17. ۩ମతͳ΍Γํ id user_id score partition id 1 1 600 1

    2 2 800 1 3 3 1020 2 id min max user count 1 0 999 2 2 1000 1999 1 UserHighScore HighScorePartition ύʔςΟγϣϯͰ۠੾Δͱ͍͏ߟ͑ํ 17

18. ͪΐͬͱ໰୊఺ ৽͍͠είΞͷύʔςΟγϣϯΛٻΊΔࡍͷ৚͕݅ࣜԼهͩͬͨ min <= ◦◦ AND ◦◦ <= max ͜Εͩͱɺ૝ఆ͍ͯ͠ͳ͍ߦʹ΋ϩοΫ͕͔͔ΔՄೳੑ͕͋Δ͜ͱ

    ͕෼͔ͬͨ(EXPLAINͯ͠ΈͨΒεΩϟϯର৅͕޿ൣғͩͬͨ) ͪͳΈʹΠϯσοΫε͸ min, maxͷෳ߹ΠϯσοΫεͰͨ͠ ϩοΫൣғ͸ڱ͍΄͏͕͍͍ʂ 18

19. ͪΐͬͱ೧ͬͯΈͨ 19

20. ύʔςΟγϣϯ৘ใΛίʔυ΁ 20

21. class HighScorePartitionMap PARTITIONS = { 0 => {id: 1, max:

    7999}, 8000 => {id: 2, max: 15999}, 16000 => {id: 3, max: 23999}, 24000 => {id: 4, max: 31999}, 32000 => {id: 5, max: 39999}, 40000 => {id: 6, max: 47999}, 48000 => {id: 7, max: 55999}, 56000 => {id: 8, max: 63999}, ~~தུ~~ 740000 => {id: 126, max: 747999}, 748000 => {id: 127, max: 755999}, 756000 => {id: 128, max: 763999} } end ࠷খ஋ΛΩʔͱͯ͠ɺidͱmaxͷϖΞΛ࣋ͨͤ·ͨ͠ 21

22. ϝϦοτ ύʔςΟγϣϯ৘ใม͑Δͱͨ͠Βϝϯςͷ࣌͘Β͍ ίʔυʹॻ͍ͨΒଟ෼Ұ൪ૣͦ͏ GREE͞Μ΍CA͞ΜͷྫͩͱߦϩοΫΛద੾ʹ͔͚Δͷ͕೉͔ͬͨ͠ ࣮ࡍʹϩοΫ͕ඞཁͳՕॴΛݮΒͤͨ ύʔςΟγϣϯͷ൑ఆͰϩοΫΛೖΕͳ͍ͱσουϩοΫ͢Δύλʔ ϯ͕͕͋ͬͨɺϩοΫͷλΠϛϯάΛޙΖʹճ͢͜ͱ͕ग़དྷͨ 22

23. σϝϦοτ ׂΓͱμα͍͔΋͠Εͳ͍ ϋογϡ؅ཧͳͷͰॻ͖׵͕͑গʑ໘౗ ίʔυδΣωϨʔλతͳ΋ͷ͕͋Δͱ͍͍͔΋ 23

24. before_save do |r| partition_map = HighScorePartitionMap::PARTITIONS.select {|k,v| k <= r.score

    and v[:max] >= r.score}.values.first # Not modify return true if r.high_score_partition_id == partition_map[:id] if r.high_score_partition_id partition_where = [r.high_score_partition_id, partition_map[:id]] else partition_where = partition_map[:id] end partition = HighScorePartition.where(id: partition_where).lock(true).all.select{|v| v.id == partition_map[:id]}.first # Not found partition if cheat? raise "Score not found range partition" unless partition # Remove user for old pertition HighScorePartition.decrement_counter(:user_count, r.high_score_partition_id) if r.high_score_partition_id # Add user for new partition HighScorePartition.increment_counter(:user_count, partition) # New partition self.high_score_partition = partition end 24

25. ॱҐͷٻΊํ ࣗ෼ͷείΞύʔςΟγϣϯΛಛఆ͢Δ ͦͷύʔςΟγϣϯΑΓ΋ଟ͍Ϣʔβ਺ΛΧ΢ϯτ͢Δ ࣗ෼ͷύʔςΟγϣϯʹଐ͍ͯ͠Δࣗ෼ΑΓείΞͷߴ͍ਓΛಛఆ͢ Δ 25

26. ࣗ෼͔Βݟͯ10ҐΛग़͍ͨ͠ͱ͖ ύʔςΟγϣϯ಺Ͱࣗ෼ΑΓείΞͷߴ͍ਓΛ୳͢ ൣғͱͯ͠͸ ࣗ෼ͷείΞ ~ ͦͷύʔςΟγϣϯͷ࠷େ஋·Ͱ େ఍͸͜Ε͚ͩͰٻ·Δ ൣғ͕ڱ͍ͷͰϩοΫͷڝ߹Λආ͚ΕΔ 10ਓຬͨͳ͔ͬͨΒ࣍ͷύʔςΟγϣϯͰ୳ͯ͋͛͠Δ 26

27. def rank_range user_count = 0 users = self.class.upper_byrange(self.score).limit(10).all return users

    if users.length >= 10 user_count = users.count if users.count if self.current_rank >= 10 partitions = HighScorePartition.order('min DESC').all else partitions = HighScorePartition.upper(self.score).order('min DESC').all end partitions.each do |partition| users << partition.user_high_scores.limit(10).all user_count += partition.user_count return users if user_count >= 10 end end 27

28. ౾஌ࣝ 28

29. ਺஋ͷมಈ͸ઐ༻ϝιου࢖͓͏ Ճࢉͷincrement_counterϝιου(Model.increment_counter(:column, id)) ݮࢉͷdecrement_counterϝιου(Model.decrement_counter(:column, id)) ϓϩάϥϜ͔Β1Ճࢉͨ͠஋ΛೖΕΔΑΓ΋҆શ ສ͕ҰͷϩοΫ࿙Ε౳ʹΑͬͯݹ͍৘ใʹՃࢉͨ͠஋Ͱߋ৽ͯ͠͠·͏͜ͱΛ๷͙ SQLతʹ͜͏ͳΔ UPDATE `high_score_partitions`

    SET `user_count` = COALESCE(`user_count`, 0) - 1 WHERE `high_score_partitions`.`id` = 2 29

30. ϕϯνϚʔΫ 30

31. ϕϯνϚʔΫ ͘͞ΒVPS 2Gϓϥϯ 3ίΞCPU ϝϞϦ2GB σΟεΫ200GB Ubuntu 12.04 MySQL 5.5.29

    Ruby 1.9.3 31

32. ϕϯνϚʔΫ 1ʙ1000ͷಉ࣌ΞΫηεΛ૝ఆ ಉ࣌ΞΫηε͸RubyͷεϨουͰٖࣅతʹ࣮૷ ͜ͷͨΊɺDBͷίωΫγϣϯ͕ݶքʹ౸ୡͨ͠Γϓϩηεͷ্ݶ·Ͱಥഁͯ͠ ͠·ͬͨͷͰ1000Ҏ্ͷσʔλ͸͋Γ·ͤΜ... εϨουੜ੒࣌ؒ͸ϕϯνϚʔΫ͔Βআ֎ ϕϯνϚʔΫ։࢝࣌ʹͦΕͧΕͷςʔϒϧͷΠϯσοΫεΛ࠶ߏஙͤͯ͞·͢ ७ਮʹεϨουͷ࣮ߦ࣌ؒΛଌఆ͍ͯ͠·͢ 32

33. ϕϯνϚʔΫ ࢀর70ˋɺߋ৽30% είΞσʔλ͸20ສ݅ 33

34. ϕϯνϚʔΫ user system total real 1time legacy: 0.140000 0.010000 0.150000

    ( 0.295606) 1time new: 0.040000 0.010000 0.050000 ( 0.081668) 100time legacy: 0.480000 0.180000 0.660000 ( 1.161972) 100time new: 1.010000 0.330000 1.340000 ( 1.306994) 250time legacy: 1.140000 0.290000 1.430000 ( 3.732233) 250time new: 2.540000 0.710000 3.250000 ( 3.080671) 1000time legacy: 6.740000 1.210000 7.950000 ( 24.415076) 1000time new: 12.830000 3.220000 16.050000 ( 15.357371) ※ new͕ύʔςΟγϣϯํࣜͰ͢ 34

35. ϕϯνϚʔΫ ͍ͩͿ଎͘ͳͬͨ 1000εϨουͷྫͩͱ24ඵ→15ඵ 35

36. ྑ͍ࣄ͹͔Γ͡Όͳ͍ 2ສ݅ʹରͯ͠1000ճҰ੪ΞΫηεΛ૝ఆͯ͠Έͨ৔߹ εϩʔΫΤϦ͸͍ͬͺ͍ग़Δ Ͱ΋ɺύʔςΟγϣϯతͳߟ͑ํΑΓ΋࣮ߦ଎౓͸ૣ͍ ύʔςΟγϣϯతͳߟ͑ํͩͱΫΤϦͦͷ΋ͷ͸૿͑Δ 36

37. ࢀߟจݙ·ͱΊ http://labs.gree.jp/blog/2010/07/456/ http://www.cyberagent.co.jp/technology/pdf/2011_10.pdf http://www.slideshare.net/infinite_loop/phpmysql 37