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

1FSGFDU2VFVF͸͍͔ʹύʔϑΣΫτ͔ɺ  ͋Δ͍͸3VCZͱ.Z42-Ͱ  δϣϒΩϡʔΛ࡞ΔࢼΈʹ͍ͭͯ 5SFBTVSF%BUB OBSVTF How perfect PerfectQueue is,  and
about an experience of making  a job queue with Ruby and MySQL

8IPJTOBSVTF w OLGϝϯςφ w ݹ୅ͷจࣈίʔυม׵ϓϩάϥϜ w 3VCZίϛολ w ΦϒδΣΫτࢦ޲εΫϦϓτݴޠ w
5%Ͱ͸όοΫΤϯυͷ3VCZΞϓϦέʔγϣϯΛ͍ ͍ͬͯ͡·͢

8IBUJT1FSGFDU2VFVF w ύʔϑΣΫτͳ෼ࢄδϣϒΩϡʔ  "1FSGFDU%JTUSJCVUFE+PC2VFVF w 5%VTFT1FSGFDU2VFVFBTBCV⒎FSCFUXFFO UIFGSPOUFOEBOEUIFCBDLFOE

ͦ΋ͦ΋  δϣϒΩϡʔͱ͸ $%1+PC0CTFSWFSύλʔϯ IUUQBXTDMPVEEFTJHOQBUUFSOPSHJOEFYQIQ$%1+PC@0CTFSWFS &&#'&#$&# w '*'0 w ϑϩϯτΤϯυͱόοΫΤ ϯυΛૄ݁߹Խ
w εέʔϧΞ΢τ͕༰қʹ w ෛՙٸ্ঢ࣌ͷ࣌ؒՔ͗ w ଱ো֐ੑͷ޲্

1FSGFDU2VFVFͷಛ௃ w "UMFBTUPODFTFNBOUJDT w 1FSNBOFOU4UPSBHF w 3%#.4 .Z42- w
SFUSZ IFBSUCFBUUJNFPVU w "UNPTUPODF w *EFNQPUFOUUBTLTVCNJTTJPO w 1FSGFDU2VFVF`TVOJRVFLFZ w 0UIFSVOJRVFOFTTPO"QQMJDBUJPO-BZFS USBEFP⒎

5IFMJGFPG UBTLT w 4VCNJU w "DRVJSF w 'JOJTI w %FMFUF
TVCNJU BDRVJSF EFMFUF GJOJTI SFMFBTF

%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)  )

λεΫͷ౤ೖ 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

/* 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...

UPDATE `queue`  SET timeout = UNIX_TIMESTAMP()+300  WHERE id = ?;
λεΫͷ࣮ߦதʜʜ UJNFPVU now IFBSUCFBU +300 waiting... running... )FBSUCFBUEVSJOHSVOOJOHBUBTLʜ

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

׬ྃࡁΈλεΫͷ࡟আ DELETE FROM `queue`  WHERE timeout <= UNIX_TIMESTAMP()  AND created_at
IS NULL; UJNFPVU now EFMFUF +720 to be deleted... લड़ͷSFUFOUJPO@UJNFΛա͗ͨΒ෺ཧ࡟আɻ ىಈ௚ޙͱҰఆ਺λεΫΛऔಘ͢Δ͝ͱʹ  ্هΫΤϦΛ౤͛Δɻ

֤ૢ࡞ʹ͍ͭͯ w 4VCNJUجຊతʹ*/4&35͢Δ͚ͩ w "DRVJSFഉଞͳͷͰΉ͔͍ͭ͠ w ฒྻॲཧʹ͓͚Δഉଞॲཧͱ͍͏ຊ࣭తͳ໰୊ w 'JOJTIϓϥΠϚϦΩʔͰ61%"5&ͳͷͰ͔ΜͨΜ w
%FMFUF%&-&5&͢Δ͚ͩʜͱΈͤͯҙ֎ͱͤ͘ऀ

λεΫऔಘͲ͏ഉଞ͢Δ͔ 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 (?,?,?,?,?);

λεΫऔಘͲ͏ഉଞ͢Δ͔ 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;

λεΫऔಘͲ͏ഉଞ͢Δ͔ 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`);

ʊਓਓਓਓਓਓਓਓਓਓਓʊ ʼɹಥવͷσουϩοΫɹʻ ʉ:?:?:?:?:?:?:?:?:?:ʉ

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

׬ྃࡁΈλεΫͷ࡟আ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Λա͗ͨΒ෺ཧ࡟আɻ ىಈ௚ޙͱҰఆ਺λεΫΛऔಘ͢Δ͝ͱʹ  ͜ͷΫΤϦΛ౤͛Δɻ

ʊਓਓਓਓਓਓਓਓਓʊ ʼɹಥવͷੑೳ௿Լɹʻ ʉ:?:?:?:?:?:?:?:ʉ

׬ྃࡁΈλεΫͷ࡟আ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Λա͗ͨΒ෺ཧ࡟আɻ ىಈ௚ޙͱҰఆ਺λεΫΛऔಘ͢Δ͝ͱʹ  ͜ͷΫΤϦΛ౤͛Δɻ શͯͷϫʔΧʔΛಉ࣌ʹىಈ͢Δͱɺ  ͠͹Β͘ͷؒ࡟আΫΤϦ͕ඈ͹ͳ͍

$POqJDUXJUI%&-&5& ࡟আ଴ͪλεΫͱऔಘ଴ͪλεΫ͸ɺUJNFPVUʹΑΔJOEFY্Ͱಉ͡ྖҬ ʹଘࡏ͢Δ ˠ࡟আ଴ͪλεΫ͕૿͑ΔͱɺλεΫऔಘͷεϧʔϓοτ͕௿Լ͢Δ  ˠఆظతʹ%&-&5&ΫΤϦ͕ඈͿΑ͏ʹཚ਺Ͱ෼ࢄͤ͞Δ now EFMFUF +720 to be
deleted... UJNFPVU BDRVJSF waiting... running...

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

$POqJDUXJUI%&-&5& औಘ଴ͪλεΫͱ࡟আࡁΈλεΫͷଘࡏ͢ΔྖҬΛ෼͚ͨ w औಘ଴ͪλεΫ΍࡟আ଴ͪλεΫ͕ͨ·ͬͯ΋҆ఆͯ͠଎౓͕ग़Δ Α͏ʹͳͬͨ w ࡟আΫΤϦͷσουϩοΫରࡦ͕ෆཁʹͳͬͨ w λεΫͷऔಘͱ׬ྃλεΫͷ࡟আ͕ฒߦʹ૸Δ  ˠεϧʔϓοτ޲্
now EFMFUF waiting... running… now-10ԯඵ 13ԯඵ BDRVJSF to be deleted...

ʊਓਓਓਓਓਓਓਓਓਓਓਓਓʊ ʼɹಥવͷωοτϫʔΫ஗Ԇɹʻ ʉ:?:?:?:?:?:?:?:?:?:?:?:ʉ

/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

%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)  )

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

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

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

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

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

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

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

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

·ͱΊ w +PC2VFVFJTJNQPSUBOUCVUEJ⒏DVMU w 6TFTBOECPYUPUFTUCFODINBSLUIFDPEF w 8SJUF42-XJUIJNBHJOHIPX.Z42-XPSLT 1FSGFDU2VFVFCFDPNFQFSGFDUFS

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

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

Transcript

More Decks by NARUSE, Yui

Other Decks in Programming

Featured