Upgrade to Pro — share decks privately, control downloads, hide ads and more …

PerfectQueueはいかにパーフェクトか、あるいはRubyとMySQLでジョブキューを作る試みについて

 PerfectQueueはいかにパーフェクトか、あるいはRubyとMySQLでジョブキューを作る試みについて

9361878d459f1709feec780518946ee5?s=128

NARUSE, Yui

July 14, 2016
Tweet

More Decks by NARUSE, Yui

Other Decks in Programming

Transcript

  1. 1FSGFDU2VFVF͸͍͔ʹύʔϑΣΫτ͔ɺ
 ͋Δ͍͸3VCZͱ.Z42-Ͱ
 δϣϒΩϡʔΛ࡞ΔࢼΈʹ͍ͭͯ 5SFBTVSF%BUB OBSVTF How perfect PerfectQueue is,
 and

    about an experience of making
 a job queue with Ruby and MySQL
  2. 8IPJTOBSVTF w OLGϝϯςφ w ݹ୅ͷจࣈίʔυม׵ϓϩάϥϜ w 3VCZίϛολ w ΦϒδΣΫτࢦ޲εΫϦϓτݴޠ w

    5%Ͱ͸όοΫΤϯυͷ3VCZΞϓϦέʔγϣϯΛ͍ ͍ͬͯ͡·͢
  3. 8IBUJT1FSGFDU2VFVF w ύʔϑΣΫτͳ෼ࢄδϣϒΩϡʔ
 "1FSGFDU%JTUSJCVUFE+PC2VFVF  w 5%VTFT1FSGFDU2VFVFBTBCV⒎FSCFUXFFO UIFGSPOUFOEBOEUIFCBDLFOE

  4. None
  5. ͦ΋ͦ΋
 δϣϒΩϡʔͱ͸ $%1+PC0CTFSWFSύλʔϯ IUUQBXTDMPVEEFTJHOQBUUFSOPSHJOEFYQIQ$%1+PC@0CTFSWFS &&#'&#$&# w '*'0 w ϑϩϯτΤϯυͱόοΫΤ ϯυΛૄ݁߹Խ

    w εέʔϧΞ΢τ͕༰қʹ w ෛՙٸ্ঢ࣌ͷ࣌ؒՔ͗ w ଱ো֐ੑͷ޲্
  6. 1FSGFDU2VFVFͷಛ௃ w "UMFBTUPODFTFNBOUJDT w 1FSNBOFOU4UPSBHF w 3%#.4 .Z42-  w

    SFUSZ IFBSUCFBUUJNFPVU  w "UNPTUPODF  w *EFNQPUFOUUBTLTVCNJTTJPO w 1FSGFDU2VFVF`TVOJRVFLFZ w 0UIFSVOJRVFOFTTPO"QQMJDBUJPO-BZFS USBEFP⒎
  7. 5IFMJGFPG UBTLT w 4VCNJU w "DRVJSF w 'JOJTI w %FMFUF

    TVCNJU BDRVJSF EFMFUF GJOJTI SFMFBTF
  8. %BUB4USVDUVSF CREATE TABLE `queue` (
 /* unique key (" at

    most once) */
 id VARCHAR(255) NOT NULL,
 /* for FIFO’s timeline */
 timeout INT NOT NULL,
 /* opaque data */
 data LONGBLOB NOT NULL,
 /* alive or finished */
 created_at INT,
 PRIMARY KEY (id)
 )
  9. λεΫͷ౤ೖ INSERT INTO `queue`
 (id, timeout, data, created_at)
 VALUES (


    ‘hive.123456789.unique_key’, /* unique key */
 UNIX_TIMESTAMP(), /* now */
 ’{“some”: “json”}’, /* opaque data */
 NOW()); /* NOT NULL */ UJNFPVU now JOTFSU
  10. /* Run the following 2 queries exclusively: */ SELECT id

    FROM `queue`
 WHERE timeout < UNIX_TIMESTAMP()
 AND created_at IS NOT NULL /* ະ׬ྃͷλεΫ */
 ORDER BY timeout ASC LIMIT 5; UPDATE SET timeout=UNIX_TIMESTAMP()+300 /* alive_time */
 WHERE id IN (?,?,?,?,?); λεΫͷऔಘ UJNFPVU now BDRVJSF +300 waiting... running...
  11. UPDATE `queue`
 SET timeout = UNIX_TIMESTAMP()+300
 WHERE id = ?;

    λεΫͷ࣮ߦதʜʜ UJNFPVU now IFBSUCFBU +300 waiting... running... )FBSUCFBUEVSJOHSVOOJOHBUBTLʜ
  12. λεΫͷ׬ྃ UPDATE `queue`
 SET timeout = UNIX_TIMESTAMP() + 720,
 created_at

    = NULL UJNFPVU now pOJTI +720 ˢ SFUFOUJPO@UJNF running... +300 ˞SFUFOUJPO@UJNF
 ɹ׬ྃͯ͠΋ॏෳ࣮ߦΛ๷͙ͨΊɺ
 ɹҰఆ࣌ؒ෺ཧ࡟আͤͣʹอ͓࣋ͯ͘͠
  13. ׬ྃࡁΈλεΫͷ࡟আ DELETE FROM `queue`
 WHERE timeout <= UNIX_TIMESTAMP()
 AND created_at

    IS NULL; UJNFPVU now EFMFUF +720 to be deleted... લड़ͷSFUFOUJPO@UJNFΛա͗ͨΒ෺ཧ࡟আɻ ىಈ௚ޙͱҰఆ਺λεΫΛऔಘ͢Δ͝ͱʹ
 ্هΫΤϦΛ౤͛Δɻ
  14. ֤ૢ࡞ʹ͍ͭͯ w 4VCNJUجຊతʹ*/4&35͢Δ͚ͩ w "DRVJSFഉଞͳͷͰΉ͔͍ͭ͠ w ฒྻॲཧʹ͓͚Δഉଞॲཧͱ͍͏ຊ࣭తͳ໰୊ w 'JOJTIϓϥΠϚϦΩʔͰ61%"5&ͳͷͰ͔ΜͨΜ w

    %FMFUF%&-&5&͢Δ͚ͩʜͱΈͤͯҙ֎ͱͤ͘ऀ
  15. λεΫऔಘͲ͏ഉଞ͢Δ͔ w A'0361%"5&AJTEJ⒏DVMUUPVTF w #FDBVTFJUPGUFODBVTFEFBEMPDL JGZPV XSJUF42-XJUIPVUFNVMBUJOH.Z42-`TNJOE  ʙɹ'0361%"5&ɹʙ SELECT

    id FROM `queue` WHERE … LIMIT 5 FOR UPDATE; UPDATE SET … WHERE id IN (?,?,?,?,?);
  16. λεΫऔಘͲ͏ഉଞ͢Δ͔ w -0$,5"#-&4CMPDLTPUIFSQSPDFTTOPUPOMZ BDRVJSJOHUBTLTCVUBMTPTVCNJUUJOHUBTLT */4&35 BOEIFBSUCFBUT ʙɹ-0$,5"#-&4ɹʙ SET autocommit=0; LOCK

    TABLES `queue` WRITE; SELECT id FROM `queue` WHERE … LIMIT 5; UPDATE SET … WHERE id IN (?,?,?,?,?); COMMIT; UNLOCK TABLES;
  17. λεΫऔಘͲ͏ഉଞ͢Δ͔ w (&5@-0$, VTFSMPDL XPSLTpOF ʙɹ(&5@-0$,ɹʙ GET_LOCK(`queue`); SELECT id FROM

    `queue` WHERE … LIMIT 5; UPDATE SET … WHERE id IN (?,?,?,?,?); RELEASE_LOCK(`queue`);
  18. ʊਓਓਓਓਓਓਓਓਓਓਓʊ ʼɹಥવͷσουϩοΫɹʻ ʉ:?:?:?:?:?:?:?:?:?:ʉ

  19. $POqJDUXJUI%&-&5& ࡟আ଴ͪλεΫͱBDRVJSF଴ͪλεΫ͸ɺͱ΋ʹUJNFPVUʹ ΑΔJOEFY্ͷಉ͡ྖҬʹଘࡏ͢Δɻ ˠ஫ҙͯ͠ΫΤϦΛॻ͔ͳ͍ͱσουϩοΫ͢Δ͜ͱ͕͋Δ now EFMFUF +720 to be deleted...

    UJNFPVU BDRVJSF waiting... running...
  20. ׬ྃࡁΈλεΫͷ࡟আW GET_LOCK(`queue`); DELETE FROM `queue`
 WHERE timeout <= UNIX_TIMESTAMP()
 AND

    created_at IS NULL; RELEASE_LOCK(`queue`); UJNFPVU now EFMFUF +720 to be deleted... 3VOEFMFUFRVFSZBGUFSUIFMPDLOBNFEUIFUBCMF લड़ͷSFUFOUJPO@UJNFΛա͗ͨΒ෺ཧ࡟আɻ ىಈ௚ޙͱҰఆ਺λεΫΛऔಘ͢Δ͝ͱʹ
 ͜ͷΫΤϦΛ౤͛Δɻ
  21. ʊਓਓਓਓਓਓਓਓਓʊ ʼɹಥવͷੑೳ௿Լɹʻ ʉ:?:?:?:?:?:?:?:ʉ

  22. ׬ྃࡁΈλεΫͷ࡟আW GET_LOCK(`queue`); DELETE FROM `queue`
 WHERE timeout <= UNIX_TIMESTAMP()
 AND

    created_at IS NULL; RELEASE_LOCK(`queue`); UJNFPVU now EFMFUF +720 to be deleted... લड़ͷSFUFOUJPO@UJNFΛա͗ͨΒ෺ཧ࡟আɻ ىಈ௚ޙͱҰఆ਺λεΫΛऔಘ͢Δ͝ͱʹ
 ͜ͷΫΤϦΛ౤͛Δɻ શͯͷϫʔΧʔΛಉ࣌ʹىಈ͢Δͱɺ
 ͠͹Β͘ͷؒ࡟আΫΤϦ͕ඈ͹ͳ͍
  23. $POqJDUXJUI%&-&5& ࡟আ଴ͪλεΫͱऔಘ଴ͪλεΫ͸ɺUJNFPVUʹΑΔJOEFY্Ͱಉ͡ྖҬ ʹଘࡏ͢Δ ˠ࡟আ଴ͪλεΫ͕૿͑ΔͱɺλεΫऔಘͷεϧʔϓοτ͕௿Լ͢Δ
 ˠఆظతʹ%&-&5&ΫΤϦ͕ඈͿΑ͏ʹཚ਺Ͱ෼ࢄͤ͞Δ now EFMFUF +720 to be

    deleted... UJNFPVU BDRVJSF waiting... running...
  24. $POqJDUXJUI%&-&5& ࡟আ଴ͪλεΫͱऔಘ଴ͪλεΫ͸ɺUJNFPVUʹΑΔJOEFY্Ͱಉ͡ྖҬʹଘࡏ͢Δ ˠऔಘ଴ͪλεΫ͕૿͑Δͱɺ׬ྃࡁΈλεΫͷ࡟আʹ͕͔͔࣌ؒΔ ˠλεΫͷऔಘ͕ϒϩοΫ͞Εͯεϧʔϓοτ͕Լ͕Δ ɹʢόοΫΤϯυͷෆௐ࣌ʹ໰୊ΛѱԽͤ͞Δʣ
 ˠ޻෉͕ඞཁ now EFMFUF +720 to

    be deleted... UJNFPVU waiting... running...
  25. $POqJDUXJUI%&-&5& औಘ଴ͪλεΫͱ࡟আࡁΈλεΫͷଘࡏ͢ΔྖҬΛ෼͚ͨ w औಘ଴ͪλεΫ΍࡟আ଴ͪλεΫ͕ͨ·ͬͯ΋҆ఆͯ͠଎౓͕ग़Δ Α͏ʹͳͬͨ w ࡟আΫΤϦͷσουϩοΫରࡦ͕ෆཁʹͳͬͨ w λεΫͷऔಘͱ׬ྃλεΫͷ࡟আ͕ฒߦʹ૸Δ
 ˠεϧʔϓοτ޲্

    now EFMFUF waiting... running… now-10ԯඵ 13ԯඵ BDRVJSF to be deleted...
  26. ʊਓਓਓਓਓਓਓਓਓਓਓਓਓʊ ʼɹಥવͷωοτϫʔΫ஗Ԇɹʻ ʉ:?:?:?:?:?:?:?:?:?:?:?:ʉ

  27. /FUXPSLMBUFODZ w (&5@-0$, VTFSMPDL XPSLTpOF
 XIJMFOFUXPSLJTpOF w &$ͱ3%4ͷωοτϫʔΫ஗Ԇ͕ѱԽ͢Δͱɺ
 εϧʔϓοτ͕ഒѱԽ͢Δ ʙɹBDRVJSJOHUBTLTXJUI(&5@-0$,

    ɹʙ GET_LOCK(`queue`); SELECT id FROM `queue` WHERE … LIMIT 5; UPDATE SET … WHERE id IN (?,?,?,?,?); RELEASE_LOCK(`queue`); -0$,
 355
  28. %BUB4USVDUVSF CREATE TABLE `queue` (
 /* unique key (" at

    most once) */
 id VARCHAR(255) NOT NULL,
 /* for FIFO’s timeline */
 timeout INT NOT NULL,
 /* opaque data */
 data LONGBLOB NOT NULL,
 /* alive or finished */
 created_at INT,
 /* Who get this task */
 owner BIGINT(21) UNSIGNED NOT NULL DEFAULT 0,
 PRIMARY KEY (id)
 )
  29. UPDATE `queue`
 JOIN (
 SELECT id
 FROM `queue` FORCE INDEX

    (`index_queue_on_timeout`)
 WHERE 1300000000<timeout AND timeout<=UNIX_TIMESTAMP()
 ORDER BY timeout ASC
 LIMIT :max_acquire FOR UPDATE) AS t1 USING(id)
 SET timeout=:next_timeout, owner=CONNECTION_ID() SELECT id, timeout, data, created_at, resource
 FROM `#{@table}`
 WHERE timeout = ? AND owner=CONNECTION_ID() "DRVJSJOHUBTLTXJUIPVUUBCMFMPDLJOH 4FMFDU.BSL ˣ .BSL4FMFDU 28IBUJT$0//&$5*0/@*%  "1SPDFTT*%PGUIF.Z42-DPOOFDUJPO 28IZJUVTFT4&-&$5JO61%"5&  ".Z42-EPFTO`UPQUJNJ[F61%"5&RVFSZXJUI -*.*5DMBVTFʜ 2*TA'0361%"5&ATBGF  "4BGFCFDBVTF*XSPUFUIJTXJUIJNBHJOH.Z42-`T NJOE
  30. λεΫͷऔಘ UJNFPVU now ᶃ4&-&$5 waiting... read lock! XJUIPVUA'0361%"5&A

  31. λεΫͷऔಘ UJNFPVU now ᶄ4&-&$5 waiting... read lock! ᶃ61%"5& write lock!

    XJUIPVUA'0361%"5&A
  32. λεΫͷऔಘ UJNFPVU now ᶄ61%"5& waiting... write lock! ᶃ61%"5& write lock!

    DEADLOCK XJUIPVUA'0361%"5&A
  33. λεΫͷऔಘ UJNFPVU now ᶃ4&-&$5 waiting... write lock! XJUIA'0361%"5&A

  34. λεΫͷऔಘ UJNFPVU now ᶄ4&-&$5 waiting... write lock! ᶃ61%"5& write lock!

    XJUIA'0361%"5&A
  35. λεΫͷऔಘ UJNFPVU now ᶄ4&-&$5 waiting... write lock! XJUIA'0361%"5&A

  36. UPDATE `queue`
 JOIN (
 SELECT id
 FROM `queue` FORCE INDEX

    (`index_queue_on_timeout`)
 WHERE 1300000000<timeout AND timeout<=UNIX_TIMESTAMP()
 ORDER BY timeout ASC
 LIMIT :max_acquire FOR UPDATE) AS t1 USING(id)
 SET timeout=:next_timeout, owner=CONNECTION_ID() SELECT id, timeout, data, created_at, resource
 FROM `#{@table}`
 WHERE timeout = ? AND owner = CONNECTION_ID() "DRVJSJOHUBTLTXJUIPVUUBCMFMPDLJOH 4FMFDU.BSL ˣ .BSL4FMFDU #ZUIJTTUSBUFHZ JUBWPJETOFUXPSL MBUFODZJTTVF.PSFPWFSJUDBO BDIJFWFGBTUFSUBTLBDRVJSJOH BCPVUY
  37. ·ͱΊ w +PC2VFVFJTJNQPSUBOUCVUEJ⒏DVMU w 6TFTBOECPYUPUFTUCFODINBSLUIFDPEF w 8SJUF42-XJUIJNBHJOHIPX.Z42-XPSLT 1FSGFDU2VFVFCFDPNFQFSGFDUFS