Tweet
Tweet

Slide 1

Slide 1 text

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

Slide 2

Slide 2 text

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

Slide 3

Slide 3 text

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

Slide 4

Slide 4 text

No content

Slide 5

Slide 5 text

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

Slide 6

Slide 6 text

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

Slide 7

Slide 7 text

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

Slide 8

Slide 8 text

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

Slide 9

Slide 9 text

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

Slide 10

Slide 10 text

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

Slide 11

Slide 11 text

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

Slide 12

Slide 12 text

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

Slide 13

Slide 13 text

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

Slide 14

Slide 14 text

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

Slide 15

Slide 15 text

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

Slide 16

Slide 16 text

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

Slide 17

Slide 17 text

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

Slide 18

Slide 18 text

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

Slide 19

Slide 19 text

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

Slide 20

Slide 20 text

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

Slide 21

Slide 21 text

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

Slide 22

Slide 22 text

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

Slide 23

Slide 23 text

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

Slide 24

Slide 24 text

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

Slide 25

Slide 25 text

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

Slide 26

Slide 26 text

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

Slide 27

Slide 27 text

/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

Slide 28

Slide 28 text

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

Slide 29

Slide 29 text

UPDATE `queue`
 JOIN (
 SELECT id
 FROM `queue` FORCE INDEX (`index_queue_on_timeout`)
 WHERE 1300000000

Slide 30

Slide 30 text

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

Slide 31

Slide 31 text

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

Slide 32

Slide 32 text

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

Slide 33

Slide 33 text

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

Slide 34

Slide 34 text

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

Slide 35

Slide 35 text

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

Slide 36

Slide 36 text

UPDATE `queue`
 JOIN (
 SELECT id
 FROM `queue` FORCE INDEX (`index_queue_on_timeout`)
 WHERE 1300000000

Slide 37

Slide 37 text

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