TTL 機能を試す
PingCAPの関口さんがTTL機能がTiDB Serverlessでも使えるようになったと投稿されていましたので早速試してみました。
TTL 機能とは
ドキュメントには以下のように記載されています。
TTL は、オンラインの読み取りおよび書き込みワークロードに影響を与えることなく、ユーザーが不必要なデータを定期的かつタイムリーにクリーンアップできるように設計されています。TTL は、異なるジョブを異なる TiDB ノードに同時にディスパッチして、テーブル単位でデータを並行して削除します。TTL では、期限切れのデータがすぐに削除されることは保証されません。つまり、一部のデータが期限切れになった場合でも、そのデータがバックグラウンド TTL ジョブによって削除されるまで、クライアントは有効期限が切れてからしばらく経ってからそのデータを読み取る可能性があります。
バックグランドで別ノードで処理されるため稼働系インスタンスにパフォーマンスの影響が表れないのは非常にうれしい点です。その代わりとして処理に若干のラグが現れるようです。一般的なユースケースを考えるとTTLで削除をおこなうレコードは不要なレコードであり、そこまでミッションクリティカルなデータではないと思いますのでこのラグは運用上許容できるものであり特に問題にはならなそうです。
さっそくやってみる
まずはドキュメントの内容に従い作業していきます。
を参考にChat2Queryというクエリーエディタで作業をしていきます。use test;
CREATE TABLE t1 (
id int PRIMARY KEY,
created_at TIMESTAMP
) TTL = `created_at` + INTERVAL 30 second;
ドキュメントの記述では3か月にTTLをセットしていますが、テストするには長すぎるので30秒にしています。
次に作成されたテーブルに1000件ほどダミーのデータを入れてみます。Python環境をお持ちであれば以下を実行して下さい。
with open('insert_data.sql', 'w') as f:
f.write("INSERT INTO t1 (id, created_at) VALUES\n")
for i in range(1, 1001):
if i == 1000:
f.write(f"({i}, NOW() - INTERVAL {i-1} SECOND);\n")
else:
f.write(f"({i}, NOW() - INTERVAL {i-1} SECOND),\n")
insert_data.sql
とう1000件のデータをInsertするデータが生成されます。
面倒な場合は以下のSQLをコピーします。
INSERT INTO t1 (id, created_at) VALUES
(1, NOW() - INTERVAL 0 SECOND),
(2, NOW() - INTERVAL 1 SECOND),
(3, NOW() - INTERVAL 2 SECOND),
(4, NOW() - INTERVAL 3 SECOND),
(5, NOW() - INTERVAL 4 SECOND),
(6, NOW() - INTERVAL 5 SECOND),
(7, NOW() - INTERVAL 6 SECOND),
(8, NOW() - INTERVAL 7 SECOND),
(9, NOW() - INTERVAL 8 SECOND),
(10, NOW() - INTERVAL 9 SECOND),
(11, NOW() - INTERVAL 10 SECOND),
(12, NOW() - INTERVAL 11 SECOND),
(13, NOW() - INTERVAL 12 SECOND),
(14, NOW() - INTERVAL 13 SECOND),
(15, NOW() - INTERVAL 14 SECOND),
(16, NOW() - INTERVAL 15 SECOND),
(17, NOW() - INTERVAL 16 SECOND),
(18, NOW() - INTERVAL 17 SECOND),
(19, NOW() - INTERVAL 18 SECOND),
(20, NOW() - INTERVAL 19 SECOND),
(21, NOW() - INTERVAL 20 SECOND),
(22, NOW() - INTERVAL 21 SECOND),
(23, NOW() - INTERVAL 22 SECOND),
(24, NOW() - INTERVAL 23 SECOND),
(25, NOW() - INTERVAL 24 SECOND),
(26, NOW() - INTERVAL 25 SECOND),
(27, NOW() - INTERVAL 26 SECOND),
(28, NOW() - INTERVAL 27 SECOND),
(29, NOW() - INTERVAL 28 SECOND),
(30, NOW() - INTERVAL 29 SECOND),
(31, NOW() - INTERVAL 30 SECOND),
(32, NOW() - INTERVAL 31 SECOND),
(33, NOW() - INTERVAL 32 SECOND),
(34, NOW() - INTERVAL 33 SECOND),
(35, NOW() - INTERVAL 34 SECOND),
(36, NOW() - INTERVAL 35 SECOND),
(37, NOW() - INTERVAL 36 SECOND),
(38, NOW() - INTERVAL 37 SECOND),
(39, NOW() - INTERVAL 38 SECOND),
(40, NOW() - INTERVAL 39 SECOND),
(41, NOW() - INTERVAL 40 SECOND),
(42, NOW() - INTERVAL 41 SECOND),
(43, NOW() - INTERVAL 42 SECOND),
(44, NOW() - INTERVAL 43 SECOND),
(45, NOW() - INTERVAL 44 SECOND),
(46, NOW() - INTERVAL 45 SECOND),
(47, NOW() - INTERVAL 46 SECOND),
(48, NOW() - INTERVAL 47 SECOND),
(49, NOW() - INTERVAL 48 SECOND),
(50, NOW() - INTERVAL 49 SECOND),
(51, NOW() - INTERVAL 50 SECOND),
(52, NOW() - INTERVAL 51 SECOND),
(53, NOW() - INTERVAL 52 SECOND),
(54, NOW() - INTERVAL 53 SECOND),
(55, NOW() - INTERVAL 54 SECOND),
(56, NOW() - INTERVAL 55 SECOND),
(57, NOW() - INTERVAL 56 SECOND),
(58, NOW() - INTERVAL 57 SECOND),
(59, NOW() - INTERVAL 58 SECOND),
(60, NOW() - INTERVAL 59 SECOND),
(61, NOW() - INTERVAL 60 SECOND),
(62, NOW() - INTERVAL 61 SECOND),
(63, NOW() - INTERVAL 62 SECOND),
(64, NOW() - INTERVAL 63 SECOND),
(65, NOW() - INTERVAL 64 SECOND),
(66, NOW() - INTERVAL 65 SECOND),
(67, NOW() - INTERVAL 66 SECOND),
(68, NOW() - INTERVAL 67 SECOND),
(69, NOW() - INTERVAL 68 SECOND),
(70, NOW() - INTERVAL 69 SECOND),
(71, NOW() - INTERVAL 70 SECOND),
(72, NOW() - INTERVAL 71 SECOND),
(73, NOW() - INTERVAL 72 SECOND),
(74, NOW() - INTERVAL 73 SECOND),
(75, NOW() - INTERVAL 74 SECOND),
(76, NOW() - INTERVAL 75 SECOND),
(77, NOW() - INTERVAL 76 SECOND),
(78, NOW() - INTERVAL 77 SECOND),
(79, NOW() - INTERVAL 78 SECOND),
(80, NOW() - INTERVAL 79 SECOND),
(81, NOW() - INTERVAL 80 SECOND),
(82, NOW() - INTERVAL 81 SECOND),
(83, NOW() - INTERVAL 82 SECOND),
(84, NOW() - INTERVAL 83 SECOND),
(85, NOW() - INTERVAL 84 SECOND),
(86, NOW() - INTERVAL 85 SECOND),
(87, NOW() - INTERVAL 86 SECOND),
(88, NOW() - INTERVAL 87 SECOND),
(89, NOW() - INTERVAL 88 SECOND),
(90, NOW() - INTERVAL 89 SECOND),
(91, NOW() - INTERVAL 90 SECOND),
(92, NOW() - INTERVAL 91 SECOND),
(93, NOW() - INTERVAL 92 SECOND),
(94, NOW() - INTERVAL 93 SECOND),
(95, NOW() - INTERVAL 94 SECOND),
(96, NOW() - INTERVAL 95 SECOND),
(97, NOW() - INTERVAL 96 SECOND),
(98, NOW() - INTERVAL 97 SECOND),
(99, NOW() - INTERVAL 98 SECOND),
(100, NOW() - INTERVAL 99 SECOND),
(101, NOW() - INTERVAL 100 SECOND),
(102, NOW() - INTERVAL 101 SECOND),
(103, NOW() - INTERVAL 102 SECOND),
(104, NOW() - INTERVAL 103 SECOND),
(105, NOW() - INTERVAL 104 SECOND),
(106, NOW() - INTERVAL 105 SECOND),
(107, NOW() - INTERVAL 106 SECOND),
(108, NOW() - INTERVAL 107 SECOND),
(109, NOW() - INTERVAL 108 SECOND),
(110, NOW() - INTERVAL 109 SECOND),
(111, NOW() - INTERVAL 110 SECOND),
(112, NOW() - INTERVAL 111 SECOND),
(113, NOW() - INTERVAL 112 SECOND),
(114, NOW() - INTERVAL 113 SECOND),
(115, NOW() - INTERVAL 114 SECOND),
(116, NOW() - INTERVAL 115 SECOND),
(117, NOW() - INTERVAL 116 SECOND),
(118, NOW() - INTERVAL 117 SECOND),
(119, NOW() - INTERVAL 118 SECOND),
(120, NOW() - INTERVAL 119 SECOND),
(121, NOW() - INTERVAL 120 SECOND),
(122, NOW() - INTERVAL 121 SECOND),
(123, NOW() - INTERVAL 122 SECOND),
(124, NOW() - INTERVAL 123 SECOND),
(125, NOW() - INTERVAL 124 SECOND),
(126, NOW() - INTERVAL 125 SECOND),
(127, NOW() - INTERVAL 126 SECOND),
(128, NOW() - INTERVAL 127 SECOND),
(129, NOW() - INTERVAL 128 SECOND),
(130, NOW() - INTERVAL 129 SECOND),
(131, NOW() - INTERVAL 130 SECOND),
(132, NOW() - INTERVAL 131 SECOND),
(133, NOW() - INTERVAL 132 SECOND),
(134, NOW() - INTERVAL 133 SECOND),
(135, NOW() - INTERVAL 134 SECOND),
(136, NOW() - INTERVAL 135 SECOND),
(137, NOW() - INTERVAL 136 SECOND),
(138, NOW() - INTERVAL 137 SECOND),
(139, NOW() - INTERVAL 138 SECOND),
(140, NOW() - INTERVAL 139 SECOND),
(141, NOW() - INTERVAL 140 SECOND),
(142, NOW() - INTERVAL 141 SECOND),
(143, NOW() - INTERVAL 142 SECOND),
(144, NOW() - INTERVAL 143 SECOND),
(145, NOW() - INTERVAL 144 SECOND),
(146, NOW() - INTERVAL 145 SECOND),
(147, NOW() - INTERVAL 146 SECOND),
(148, NOW() - INTERVAL 147 SECOND),
(149, NOW() - INTERVAL 148 SECOND),
(150, NOW() - INTERVAL 149 SECOND),
(151, NOW() - INTERVAL 150 SECOND),
(152, NOW() - INTERVAL 151 SECOND),
(153, NOW() - INTERVAL 152 SECOND),
(154, NOW() - INTERVAL 153 SECOND),
(155, NOW() - INTERVAL 154 SECOND),
(156, NOW() - INTERVAL 155 SECOND),
(157, NOW() - INTERVAL 156 SECOND),
(158, NOW() - INTERVAL 157 SECOND),
(159, NOW() - INTERVAL 158 SECOND),
(160, NOW() - INTERVAL 159 SECOND),
(161, NOW() - INTERVAL 160 SECOND),
(162, NOW() - INTERVAL 161 SECOND),
(163, NOW() - INTERVAL 162 SECOND),
(164, NOW() - INTERVAL 163 SECOND),
(165, NOW() - INTERVAL 164 SECOND),
(166, NOW() - INTERVAL 165 SECOND),
(167, NOW() - INTERVAL 166 SECOND),
(168, NOW() - INTERVAL 167 SECOND),
(169, NOW() - INTERVAL 168 SECOND),
(170, NOW() - INTERVAL 169 SECOND),
(171, NOW() - INTERVAL 170 SECOND),
(172, NOW() - INTERVAL 171 SECOND),
(173, NOW() - INTERVAL 172 SECOND),
(174, NOW() - INTERVAL 173 SECOND),
(175, NOW() - INTERVAL 174 SECOND),
(176, NOW() - INTERVAL 175 SECOND),
(177, NOW() - INTERVAL 176 SECOND),
(178, NOW() - INTERVAL 177 SECOND),
(179, NOW() - INTERVAL 178 SECOND),
(180, NOW() - INTERVAL 179 SECOND),
(181, NOW() - INTERVAL 180 SECOND),
(182, NOW() - INTERVAL 181 SECOND),
(183, NOW() - INTERVAL 182 SECOND),
(184, NOW() - INTERVAL 183 SECOND),
(185, NOW() - INTERVAL 184 SECOND),
(186, NOW() - INTERVAL 185 SECOND),
(187, NOW() - INTERVAL 186 SECOND),
(188, NOW() - INTERVAL 187 SECOND),
(189, NOW() - INTERVAL 188 SECOND),
(190, NOW() - INTERVAL 189 SECOND),
(191, NOW() - INTERVAL 190 SECOND),
(192, NOW() - INTERVAL 191 SECOND),
(193, NOW() - INTERVAL 192 SECOND),
(194, NOW() - INTERVAL 193 SECOND),
(195, NOW() - INTERVAL 194 SECOND),
(196, NOW() - INTERVAL 195 SECOND),
(197, NOW() - INTERVAL 196 SECOND),
(198, NOW() - INTERVAL 197 SECOND),
(199, NOW() - INTERVAL 198 SECOND),
(200, NOW() - INTERVAL 199 SECOND),
(201, NOW() - INTERVAL 200 SECOND),
(202, NOW() - INTERVAL 201 SECOND),
(203, NOW() - INTERVAL 202 SECOND),
(204, NOW() - INTERVAL 203 SECOND),
(205, NOW() - INTERVAL 204 SECOND),
(206, NOW() - INTERVAL 205 SECOND),
(207, NOW() - INTERVAL 206 SECOND),
(208, NOW() - INTERVAL 207 SECOND),
(209, NOW() - INTERVAL 208 SECOND),
(210, NOW() - INTERVAL 209 SECOND),
(211, NOW() - INTERVAL 210 SECOND),
(212, NOW() - INTERVAL 211 SECOND),
(213, NOW() - INTERVAL 212 SECOND),
(214, NOW() - INTERVAL 213 SECOND),
(215, NOW() - INTERVAL 214 SECOND),
(216, NOW() - INTERVAL 215 SECOND),
(217, NOW() - INTERVAL 216 SECOND),
(218, NOW() - INTERVAL 217 SECOND),
(219, NOW() - INTERVAL 218 SECOND),
(220, NOW() - INTERVAL 219 SECOND),
(221, NOW() - INTERVAL 220 SECOND),
(222, NOW() - INTERVAL 221 SECOND),
(223, NOW() - INTERVAL 222 SECOND),
(224, NOW() - INTERVAL 223 SECOND),
(225, NOW() - INTERVAL 224 SECOND),
(226, NOW() - INTERVAL 225 SECOND),
(227, NOW() - INTERVAL 226 SECOND),
(228, NOW() - INTERVAL 227 SECOND),
(229, NOW() - INTERVAL 228 SECOND),
(230, NOW() - INTERVAL 229 SECOND),
(231, NOW() - INTERVAL 230 SECOND),
(232, NOW() - INTERVAL 231 SECOND),
(233, NOW() - INTERVAL 232 SECOND),
(234, NOW() - INTERVAL 233 SECOND),
(235, NOW() - INTERVAL 234 SECOND),
(236, NOW() - INTERVAL 235 SECOND),
(237, NOW() - INTERVAL 236 SECOND),
(238, NOW() - INTERVAL 237 SECOND),
(239, NOW() - INTERVAL 238 SECOND),
(240, NOW() - INTERVAL 239 SECOND),
(241, NOW() - INTERVAL 240 SECOND),
(242, NOW() - INTERVAL 241 SECOND),
(243, NOW() - INTERVAL 242 SECOND),
(244, NOW() - INTERVAL 243 SECOND),
(245, NOW() - INTERVAL 244 SECOND),
(246, NOW() - INTERVAL 245 SECOND),
(247, NOW() - INTERVAL 246 SECOND),
(248, NOW() - INTERVAL 247 SECOND),
(249, NOW() - INTERVAL 248 SECOND),
(250, NOW() - INTERVAL 249 SECOND),
(251, NOW() - INTERVAL 250 SECOND),
(252, NOW() - INTERVAL 251 SECOND),
(253, NOW() - INTERVAL 252 SECOND),
(254, NOW() - INTERVAL 253 SECOND),
(255, NOW() - INTERVAL 254 SECOND),
(256, NOW() - INTERVAL 255 SECOND),
(257, NOW() - INTERVAL 256 SECOND),
(258, NOW() - INTERVAL 257 SECOND),
(259, NOW() - INTERVAL 258 SECOND),
(260, NOW() - INTERVAL 259 SECOND),
(261, NOW() - INTERVAL 260 SECOND),
(262, NOW() - INTERVAL 261 SECOND),
(263, NOW() - INTERVAL 262 SECOND),
(264, NOW() - INTERVAL 263 SECOND),
(265, NOW() - INTERVAL 264 SECOND),
(266, NOW() - INTERVAL 265 SECOND),
(267, NOW() - INTERVAL 266 SECOND),
(268, NOW() - INTERVAL 267 SECOND),
(269, NOW() - INTERVAL 268 SECOND),
(270, NOW() - INTERVAL 269 SECOND),
(271, NOW() - INTERVAL 270 SECOND),
(272, NOW() - INTERVAL 271 SECOND),
(273, NOW() - INTERVAL 272 SECOND),
(274, NOW() - INTERVAL 273 SECOND),
(275, NOW() - INTERVAL 274 SECOND),
(276, NOW() - INTERVAL 275 SECOND),
(277, NOW() - INTERVAL 276 SECOND),
(278, NOW() - INTERVAL 277 SECOND),
(279, NOW() - INTERVAL 278 SECOND),
(280, NOW() - INTERVAL 279 SECOND),
(281, NOW() - INTERVAL 280 SECOND),
(282, NOW() - INTERVAL 281 SECOND),
(283, NOW() - INTERVAL 282 SECOND),
(284, NOW() - INTERVAL 283 SECOND),
(285, NOW() - INTERVAL 284 SECOND),
(286, NOW() - INTERVAL 285 SECOND),
(287, NOW() - INTERVAL 286 SECOND),
(288, NOW() - INTERVAL 287 SECOND),
(289, NOW() - INTERVAL 288 SECOND),
(290, NOW() - INTERVAL 289 SECOND),
(291, NOW() - INTERVAL 290 SECOND),
(292, NOW() - INTERVAL 291 SECOND),
(293, NOW() - INTERVAL 292 SECOND),
(294, NOW() - INTERVAL 293 SECOND),
(295, NOW() - INTERVAL 294 SECOND),
(296, NOW() - INTERVAL 295 SECOND),
(297, NOW() - INTERVAL 296 SECOND),
(298, NOW() - INTERVAL 297 SECOND),
(299, NOW() - INTERVAL 298 SECOND),
(300, NOW() - INTERVAL 299 SECOND),
(301, NOW() - INTERVAL 300 SECOND),
(302, NOW() - INTERVAL 301 SECOND),
(303, NOW() - INTERVAL 302 SECOND),
(304, NOW() - INTERVAL 303 SECOND),
(305, NOW() - INTERVAL 304 SECOND),
(306, NOW() - INTERVAL 305 SECOND),
(307, NOW() - INTERVAL 306 SECOND),
(308, NOW() - INTERVAL 307 SECOND),
(309, NOW() - INTERVAL 308 SECOND),
(310, NOW() - INTERVAL 309 SECOND),
(311, NOW() - INTERVAL 310 SECOND),
(312, NOW() - INTERVAL 311 SECOND),
(313, NOW() - INTERVAL 312 SECOND),
(314, NOW() - INTERVAL 313 SECOND),
(315, NOW() - INTERVAL 314 SECOND),
(316, NOW() - INTERVAL 315 SECOND),
(317, NOW() - INTERVAL 316 SECOND),
(318, NOW() - INTERVAL 317 SECOND),
(319, NOW() - INTERVAL 318 SECOND),
(320, NOW() - INTERVAL 319 SECOND),
(321, NOW() - INTERVAL 320 SECOND),
(322, NOW() - INTERVAL 321 SECOND),
(323, NOW() - INTERVAL 322 SECOND),
(324, NOW() - INTERVAL 323 SECOND),
(325, NOW() - INTERVAL 324 SECOND),
(326, NOW() - INTERVAL 325 SECOND),
(327, NOW() - INTERVAL 326 SECOND),
(328, NOW() - INTERVAL 327 SECOND),
(329, NOW() - INTERVAL 328 SECOND),
(330, NOW() - INTERVAL 329 SECOND),
(331, NOW() - INTERVAL 330 SECOND),
(332, NOW() - INTERVAL 331 SECOND),
(333, NOW() - INTERVAL 332 SECOND),
(334, NOW() - INTERVAL 333 SECOND),
(335, NOW() - INTERVAL 334 SECOND),
(336, NOW() - INTERVAL 335 SECOND),
(337, NOW() - INTERVAL 336 SECOND),
(338, NOW() - INTERVAL 337 SECOND),
(339, NOW() - INTERVAL 338 SECOND),
(340, NOW() - INTERVAL 339 SECOND),
(341, NOW() - INTERVAL 340 SECOND),
(342, NOW() - INTERVAL 341 SECOND),
(343, NOW() - INTERVAL 342 SECOND),
(344, NOW() - INTERVAL 343 SECOND),
(345, NOW() - INTERVAL 344 SECOND),
(346, NOW() - INTERVAL 345 SECOND),
(347, NOW() - INTERVAL 346 SECOND),
(348, NOW() - INTERVAL 347 SECOND),
(349, NOW() - INTERVAL 348 SECOND),
(350, NOW() - INTERVAL 349 SECOND),
(351, NOW() - INTERVAL 350 SECOND),
(352, NOW() - INTERVAL 351 SECOND),
(353, NOW() - INTERVAL 352 SECOND),
(354, NOW() - INTERVAL 353 SECOND),
(355, NOW() - INTERVAL 354 SECOND),
(356, NOW() - INTERVAL 355 SECOND),
(357, NOW() - INTERVAL 356 SECOND),
(358, NOW() - INTERVAL 357 SECOND),
(359, NOW() - INTERVAL 358 SECOND),
(360, NOW() - INTERVAL 359 SECOND),
(361, NOW() - INTERVAL 360 SECOND),
(362, NOW() - INTERVAL 361 SECOND),
(363, NOW() - INTERVAL 362 SECOND),
(364, NOW() - INTERVAL 363 SECOND),
(365, NOW() - INTERVAL 364 SECOND),
(366, NOW() - INTERVAL 365 SECOND),
(367, NOW() - INTERVAL 366 SECOND),
(368, NOW() - INTERVAL 367 SECOND),
(369, NOW() - INTERVAL 368 SECOND),
(370, NOW() - INTERVAL 369 SECOND),
(371, NOW() - INTERVAL 370 SECOND),
(372, NOW() - INTERVAL 371 SECOND),
(373, NOW() - INTERVAL 372 SECOND),
(374, NOW() - INTERVAL 373 SECOND),
(375, NOW() - INTERVAL 374 SECOND),
(376, NOW() - INTERVAL 375 SECOND),
(377, NOW() - INTERVAL 376 SECOND),
(378, NOW() - INTERVAL 377 SECOND),
(379, NOW() - INTERVAL 378 SECOND),
(380, NOW() - INTERVAL 379 SECOND),
(381, NOW() - INTERVAL 380 SECOND),
(382, NOW() - INTERVAL 381 SECOND),
(383, NOW() - INTERVAL 382 SECOND),
(384, NOW() - INTERVAL 383 SECOND),
(385, NOW() - INTERVAL 384 SECOND),
(386, NOW() - INTERVAL 385 SECOND),
(387, NOW() - INTERVAL 386 SECOND),
(388, NOW() - INTERVAL 387 SECOND),
(389, NOW() - INTERVAL 388 SECOND),
(390, NOW() - INTERVAL 389 SECOND),
(391, NOW() - INTERVAL 390 SECOND),
(392, NOW() - INTERVAL 391 SECOND),
(393, NOW() - INTERVAL 392 SECOND),
(394, NOW() - INTERVAL 393 SECOND),
(395, NOW() - INTERVAL 394 SECOND),
(396, NOW() - INTERVAL 395 SECOND),
(397, NOW() - INTERVAL 396 SECOND),
(398, NOW() - INTERVAL 397 SECOND),
(399, NOW() - INTERVAL 398 SECOND),
(400, NOW() - INTERVAL 399 SECOND),
(401, NOW() - INTERVAL 400 SECOND),
(402, NOW() - INTERVAL 401 SECOND),
(403, NOW() - INTERVAL 402 SECOND),
(404, NOW() - INTERVAL 403 SECOND),
(405, NOW() - INTERVAL 404 SECOND),
(406, NOW() - INTERVAL 405 SECOND),
(407, NOW() - INTERVAL 406 SECOND),
(408, NOW() - INTERVAL 407 SECOND),
(409, NOW() - INTERVAL 408 SECOND),
(410, NOW() - INTERVAL 409 SECOND),
(411, NOW() - INTERVAL 410 SECOND),
(412, NOW() - INTERVAL 411 SECOND),
(413, NOW() - INTERVAL 412 SECOND),
(414, NOW() - INTERVAL 413 SECOND),
(415, NOW() - INTERVAL 414 SECOND),
(416, NOW() - INTERVAL 415 SECOND),
(417, NOW() - INTERVAL 416 SECOND),
(418, NOW() - INTERVAL 417 SECOND),
(419, NOW() - INTERVAL 418 SECOND),
(420, NOW() - INTERVAL 419 SECOND),
(421, NOW() - INTERVAL 420 SECOND),
(422, NOW() - INTERVAL 421 SECOND),
(423, NOW() - INTERVAL 422 SECOND),
(424, NOW() - INTERVAL 423 SECOND),
(425, NOW() - INTERVAL 424 SECOND),
(426, NOW() - INTERVAL 425 SECOND),
(427, NOW() - INTERVAL 426 SECOND),
(428, NOW() - INTERVAL 427 SECOND),
(429, NOW() - INTERVAL 428 SECOND),
(430, NOW() - INTERVAL 429 SECOND),
(431, NOW() - INTERVAL 430 SECOND),
(432, NOW() - INTERVAL 431 SECOND),
(433, NOW() - INTERVAL 432 SECOND),
(434, NOW() - INTERVAL 433 SECOND),
(435, NOW() - INTERVAL 434 SECOND),
(436, NOW() - INTERVAL 435 SECOND),
(437, NOW() - INTERVAL 436 SECOND),
(438, NOW() - INTERVAL 437 SECOND),
(439, NOW() - INTERVAL 438 SECOND),
(440, NOW() - INTERVAL 439 SECOND),
(441, NOW() - INTERVAL 440 SECOND),
(442, NOW() - INTERVAL 441 SECOND),
(443, NOW() - INTERVAL 442 SECOND),
(444, NOW() - INTERVAL 443 SECOND),
(445, NOW() - INTERVAL 444 SECOND),
(446, NOW() - INTERVAL 445 SECOND),
(447, NOW() - INTERVAL 446 SECOND),
(448, NOW() - INTERVAL 447 SECOND),
(449, NOW() - INTERVAL 448 SECOND),
(450, NOW() - INTERVAL 449 SECOND),
(451, NOW() - INTERVAL 450 SECOND),
(452, NOW() - INTERVAL 451 SECOND),
(453, NOW() - INTERVAL 452 SECOND),
(454, NOW() - INTERVAL 453 SECOND),
(455, NOW() - INTERVAL 454 SECOND),
(456, NOW() - INTERVAL 455 SECOND),
(457, NOW() - INTERVAL 456 SECOND),
(458, NOW() - INTERVAL 457 SECOND),
(459, NOW() - INTERVAL 458 SECOND),
(460, NOW() - INTERVAL 459 SECOND),
(461, NOW() - INTERVAL 460 SECOND),
(462, NOW() - INTERVAL 461 SECOND),
(463, NOW() - INTERVAL 462 SECOND),
(464, NOW() - INTERVAL 463 SECOND),
(465, NOW() - INTERVAL 464 SECOND),
(466, NOW() - INTERVAL 465 SECOND),
(467, NOW() - INTERVAL 466 SECOND),
(468, NOW() - INTERVAL 467 SECOND),
(469, NOW() - INTERVAL 468 SECOND),
(470, NOW() - INTERVAL 469 SECOND),
(471, NOW() - INTERVAL 470 SECOND),
(472, NOW() - INTERVAL 471 SECOND),
(473, NOW() - INTERVAL 472 SECOND),
(474, NOW() - INTERVAL 473 SECOND),
(475, NOW() - INTERVAL 474 SECOND),
(476, NOW() - INTERVAL 475 SECOND),
(477, NOW() - INTERVAL 476 SECOND),
(478, NOW() - INTERVAL 477 SECOND),
(479, NOW() - INTERVAL 478 SECOND),
(480, NOW() - INTERVAL 479 SECOND),
(481, NOW() - INTERVAL 480 SECOND),
(482, NOW() - INTERVAL 481 SECOND),
(483, NOW() - INTERVAL 482 SECOND),
(484, NOW() - INTERVAL 483 SECOND),
(485, NOW() - INTERVAL 484 SECOND),
(486, NOW() - INTERVAL 485 SECOND),
(487, NOW() - INTERVAL 486 SECOND),
(488, NOW() - INTERVAL 487 SECOND),
(489, NOW() - INTERVAL 488 SECOND),
(490, NOW() - INTERVAL 489 SECOND),
(491, NOW() - INTERVAL 490 SECOND),
(492, NOW() - INTERVAL 491 SECOND),
(493, NOW() - INTERVAL 492 SECOND),
(494, NOW() - INTERVAL 493 SECOND),
(495, NOW() - INTERVAL 494 SECOND),
(496, NOW() - INTERVAL 495 SECOND),
(497, NOW() - INTERVAL 496 SECOND),
(498, NOW() - INTERVAL 497 SECOND),
(499, NOW() - INTERVAL 498 SECOND),
(500, NOW() - INTERVAL 499 SECOND),
(501, NOW() - INTERVAL 500 SECOND),
(502, NOW() - INTERVAL 501 SECOND),
(503, NOW() - INTERVAL 502 SECOND),
(504, NOW() - INTERVAL 503 SECOND),
(505, NOW() - INTERVAL 504 SECOND),
(506, NOW() - INTERVAL 505 SECOND),
(507, NOW() - INTERVAL 506 SECOND),
(508, NOW() - INTERVAL 507 SECOND),
(509, NOW() - INTERVAL 508 SECOND),
(510, NOW() - INTERVAL 509 SECOND),
(511, NOW() - INTERVAL 510 SECOND),
(512, NOW() - INTERVAL 511 SECOND),
(513, NOW() - INTERVAL 512 SECOND),
(514, NOW() - INTERVAL 513 SECOND),
(515, NOW() - INTERVAL 514 SECOND),
(516, NOW() - INTERVAL 515 SECOND),
(517, NOW() - INTERVAL 516 SECOND),
(518, NOW() - INTERVAL 517 SECOND),
(519, NOW() - INTERVAL 518 SECOND),
(520, NOW() - INTERVAL 519 SECOND),
(521, NOW() - INTERVAL 520 SECOND),
(522, NOW() - INTERVAL 521 SECOND),
(523, NOW() - INTERVAL 522 SECOND),
(524, NOW() - INTERVAL 523 SECOND),
(525, NOW() - INTERVAL 524 SECOND),
(526, NOW() - INTERVAL 525 SECOND),
(527, NOW() - INTERVAL 526 SECOND),
(528, NOW() - INTERVAL 527 SECOND),
(529, NOW() - INTERVAL 528 SECOND),
(530, NOW() - INTERVAL 529 SECOND),
(531, NOW() - INTERVAL 530 SECOND),
(532, NOW() - INTERVAL 531 SECOND),
(533, NOW() - INTERVAL 532 SECOND),
(534, NOW() - INTERVAL 533 SECOND),
(535, NOW() - INTERVAL 534 SECOND),
(536, NOW() - INTERVAL 535 SECOND),
(537, NOW() - INTERVAL 536 SECOND),
(538, NOW() - INTERVAL 537 SECOND),
(539, NOW() - INTERVAL 538 SECOND),
(540, NOW() - INTERVAL 539 SECOND),
(541, NOW() - INTERVAL 540 SECOND),
(542, NOW() - INTERVAL 541 SECOND),
(543, NOW() - INTERVAL 542 SECOND),
(544, NOW() - INTERVAL 543 SECOND),
(545, NOW() - INTERVAL 544 SECOND),
(546, NOW() - INTERVAL 545 SECOND),
(547, NOW() - INTERVAL 546 SECOND),
(548, NOW() - INTERVAL 547 SECOND),
(549, NOW() - INTERVAL 548 SECOND),
(550, NOW() - INTERVAL 549 SECOND),
(551, NOW() - INTERVAL 550 SECOND),
(552, NOW() - INTERVAL 551 SECOND),
(553, NOW() - INTERVAL 552 SECOND),
(554, NOW() - INTERVAL 553 SECOND),
(555, NOW() - INTERVAL 554 SECOND),
(556, NOW() - INTERVAL 555 SECOND),
(557, NOW() - INTERVAL 556 SECOND),
(558, NOW() - INTERVAL 557 SECOND),
(559, NOW() - INTERVAL 558 SECOND),
(560, NOW() - INTERVAL 559 SECOND),
(561, NOW() - INTERVAL 560 SECOND),
(562, NOW() - INTERVAL 561 SECOND),
(563, NOW() - INTERVAL 562 SECOND),
(564, NOW() - INTERVAL 563 SECOND),
(565, NOW() - INTERVAL 564 SECOND),
(566, NOW() - INTERVAL 565 SECOND),
(567, NOW() - INTERVAL 566 SECOND),
(568, NOW() - INTERVAL 567 SECOND),
(569, NOW() - INTERVAL 568 SECOND),
(570, NOW() - INTERVAL 569 SECOND),
(571, NOW() - INTERVAL 570 SECOND),
(572, NOW() - INTERVAL 571 SECOND),
(573, NOW() - INTERVAL 572 SECOND),
(574, NOW() - INTERVAL 573 SECOND),
(575, NOW() - INTERVAL 574 SECOND),
(576, NOW() - INTERVAL 575 SECOND),
(577, NOW() - INTERVAL 576 SECOND),
(578, NOW() - INTERVAL 577 SECOND),
(579, NOW() - INTERVAL 578 SECOND),
(580, NOW() - INTERVAL 579 SECOND),
(581, NOW() - INTERVAL 580 SECOND),
(582, NOW() - INTERVAL 581 SECOND),
(583, NOW() - INTERVAL 582 SECOND),
(584, NOW() - INTERVAL 583 SECOND),
(585, NOW() - INTERVAL 584 SECOND),
(586, NOW() - INTERVAL 585 SECOND),
(587, NOW() - INTERVAL 586 SECOND),
(588, NOW() - INTERVAL 587 SECOND),
(589, NOW() - INTERVAL 588 SECOND),
(590, NOW() - INTERVAL 589 SECOND),
(591, NOW() - INTERVAL 590 SECOND),
(592, NOW() - INTERVAL 591 SECOND),
(593, NOW() - INTERVAL 592 SECOND),
(594, NOW() - INTERVAL 593 SECOND),
(595, NOW() - INTERVAL 594 SECOND),
(596, NOW() - INTERVAL 595 SECOND),
(597, NOW() - INTERVAL 596 SECOND),
(598, NOW() - INTERVAL 597 SECOND),
(599, NOW() - INTERVAL 598 SECOND),
(600, NOW() - INTERVAL 599 SECOND),
(601, NOW() - INTERVAL 600 SECOND),
(602, NOW() - INTERVAL 601 SECOND),
(603, NOW() - INTERVAL 602 SECOND),
(604, NOW() - INTERVAL 603 SECOND),
(605, NOW() - INTERVAL 604 SECOND),
(606, NOW() - INTERVAL 605 SECOND),
(607, NOW() - INTERVAL 606 SECOND),
(608, NOW() - INTERVAL 607 SECOND),
(609, NOW() - INTERVAL 608 SECOND),
(610, NOW() - INTERVAL 609 SECOND),
(611, NOW() - INTERVAL 610 SECOND),
(612, NOW() - INTERVAL 611 SECOND),
(613, NOW() - INTERVAL 612 SECOND),
(614, NOW() - INTERVAL 613 SECOND),
(615, NOW() - INTERVAL 614 SECOND),
(616, NOW() - INTERVAL 615 SECOND),
(617, NOW() - INTERVAL 616 SECOND),
(618, NOW() - INTERVAL 617 SECOND),
(619, NOW() - INTERVAL 618 SECOND),
(620, NOW() - INTERVAL 619 SECOND),
(621, NOW() - INTERVAL 620 SECOND),
(622, NOW() - INTERVAL 621 SECOND),
(623, NOW() - INTERVAL 622 SECOND),
(624, NOW() - INTERVAL 623 SECOND),
(625, NOW() - INTERVAL 624 SECOND),
(626, NOW() - INTERVAL 625 SECOND),
(627, NOW() - INTERVAL 626 SECOND),
(628, NOW() - INTERVAL 627 SECOND),
(629, NOW() - INTERVAL 628 SECOND),
(630, NOW() - INTERVAL 629 SECOND),
(631, NOW() - INTERVAL 630 SECOND),
(632, NOW() - INTERVAL 631 SECOND),
(633, NOW() - INTERVAL 632 SECOND),
(634, NOW() - INTERVAL 633 SECOND),
(635, NOW() - INTERVAL 634 SECOND),
(636, NOW() - INTERVAL 635 SECOND),
(637, NOW() - INTERVAL 636 SECOND),
(638, NOW() - INTERVAL 637 SECOND),
(639, NOW() - INTERVAL 638 SECOND),
(640, NOW() - INTERVAL 639 SECOND),
(641, NOW() - INTERVAL 640 SECOND),
(642, NOW() - INTERVAL 641 SECOND),
(643, NOW() - INTERVAL 642 SECOND),
(644, NOW() - INTERVAL 643 SECOND),
(645, NOW() - INTERVAL 644 SECOND),
(646, NOW() - INTERVAL 645 SECOND),
(647, NOW() - INTERVAL 646 SECOND),
(648, NOW() - INTERVAL 647 SECOND),
(649, NOW() - INTERVAL 648 SECOND),
(650, NOW() - INTERVAL 649 SECOND),
(651, NOW() - INTERVAL 650 SECOND),
(652, NOW() - INTERVAL 651 SECOND),
(653, NOW() - INTERVAL 652 SECOND),
(654, NOW() - INTERVAL 653 SECOND),
(655, NOW() - INTERVAL 654 SECOND),
(656, NOW() - INTERVAL 655 SECOND),
(657, NOW() - INTERVAL 656 SECOND),
(658, NOW() - INTERVAL 657 SECOND),
(659, NOW() - INTERVAL 658 SECOND),
(660, NOW() - INTERVAL 659 SECOND),
(661, NOW() - INTERVAL 660 SECOND),
(662, NOW() - INTERVAL 661 SECOND),
(663, NOW() - INTERVAL 662 SECOND),
(664, NOW() - INTERVAL 663 SECOND),
(665, NOW() - INTERVAL 664 SECOND),
(666, NOW() - INTERVAL 665 SECOND),
(667, NOW() - INTERVAL 666 SECOND),
(668, NOW() - INTERVAL 667 SECOND),
(669, NOW() - INTERVAL 668 SECOND),
(670, NOW() - INTERVAL 669 SECOND),
(671, NOW() - INTERVAL 670 SECOND),
(672, NOW() - INTERVAL 671 SECOND),
(673, NOW() - INTERVAL 672 SECOND),
(674, NOW() - INTERVAL 673 SECOND),
(675, NOW() - INTERVAL 674 SECOND),
(676, NOW() - INTERVAL 675 SECOND),
(677, NOW() - INTERVAL 676 SECOND),
(678, NOW() - INTERVAL 677 SECOND),
(679, NOW() - INTERVAL 678 SECOND),
(680, NOW() - INTERVAL 679 SECOND),
(681, NOW() - INTERVAL 680 SECOND),
(682, NOW() - INTERVAL 681 SECOND),
(683, NOW() - INTERVAL 682 SECOND),
(684, NOW() - INTERVAL 683 SECOND),
(685, NOW() - INTERVAL 684 SECOND),
(686, NOW() - INTERVAL 685 SECOND),
(687, NOW() - INTERVAL 686 SECOND),
(688, NOW() - INTERVAL 687 SECOND),
(689, NOW() - INTERVAL 688 SECOND),
(690, NOW() - INTERVAL 689 SECOND),
(691, NOW() - INTERVAL 690 SECOND),
(692, NOW() - INTERVAL 691 SECOND),
(693, NOW() - INTERVAL 692 SECOND),
(694, NOW() - INTERVAL 693 SECOND),
(695, NOW() - INTERVAL 694 SECOND),
(696, NOW() - INTERVAL 695 SECOND),
(697, NOW() - INTERVAL 696 SECOND),
(698, NOW() - INTERVAL 697 SECOND),
(699, NOW() - INTERVAL 698 SECOND),
(700, NOW() - INTERVAL 699 SECOND),
(701, NOW() - INTERVAL 700 SECOND),
(702, NOW() - INTERVAL 701 SECOND),
(703, NOW() - INTERVAL 702 SECOND),
(704, NOW() - INTERVAL 703 SECOND),
(705, NOW() - INTERVAL 704 SECOND),
(706, NOW() - INTERVAL 705 SECOND),
(707, NOW() - INTERVAL 706 SECOND),
(708, NOW() - INTERVAL 707 SECOND),
(709, NOW() - INTERVAL 708 SECOND),
(710, NOW() - INTERVAL 709 SECOND),
(711, NOW() - INTERVAL 710 SECOND),
(712, NOW() - INTERVAL 711 SECOND),
(713, NOW() - INTERVAL 712 SECOND),
(714, NOW() - INTERVAL 713 SECOND),
(715, NOW() - INTERVAL 714 SECOND),
(716, NOW() - INTERVAL 715 SECOND),
(717, NOW() - INTERVAL 716 SECOND),
(718, NOW() - INTERVAL 717 SECOND),
(719, NOW() - INTERVAL 718 SECOND),
(720, NOW() - INTERVAL 719 SECOND),
(721, NOW() - INTERVAL 720 SECOND),
(722, NOW() - INTERVAL 721 SECOND),
(723, NOW() - INTERVAL 722 SECOND),
(724, NOW() - INTERVAL 723 SECOND),
(725, NOW() - INTERVAL 724 SECOND),
(726, NOW() - INTERVAL 725 SECOND),
(727, NOW() - INTERVAL 726 SECOND),
(728, NOW() - INTERVAL 727 SECOND),
(729, NOW() - INTERVAL 728 SECOND),
(730, NOW() - INTERVAL 729 SECOND),
(731, NOW() - INTERVAL 730 SECOND),
(732, NOW() - INTERVAL 731 SECOND),
(733, NOW() - INTERVAL 732 SECOND),
(734, NOW() - INTERVAL 733 SECOND),
(735, NOW() - INTERVAL 734 SECOND),
(736, NOW() - INTERVAL 735 SECOND),
(737, NOW() - INTERVAL 736 SECOND),
(738, NOW() - INTERVAL 737 SECOND),
(739, NOW() - INTERVAL 738 SECOND),
(740, NOW() - INTERVAL 739 SECOND),
(741, NOW() - INTERVAL 740 SECOND),
(742, NOW() - INTERVAL 741 SECOND),
(743, NOW() - INTERVAL 742 SECOND),
(744, NOW() - INTERVAL 743 SECOND),
(745, NOW() - INTERVAL 744 SECOND),
(746, NOW() - INTERVAL 745 SECOND),
(747, NOW() - INTERVAL 746 SECOND),
(748, NOW() - INTERVAL 747 SECOND),
(749, NOW() - INTERVAL 748 SECOND),
(750, NOW() - INTERVAL 749 SECOND),
(751, NOW() - INTERVAL 750 SECOND),
(752, NOW() - INTERVAL 751 SECOND),
(753, NOW() - INTERVAL 752 SECOND),
(754, NOW() - INTERVAL 753 SECOND),
(755, NOW() - INTERVAL 754 SECOND),
(756, NOW() - INTERVAL 755 SECOND),
(757, NOW() - INTERVAL 756 SECOND),
(758, NOW() - INTERVAL 757 SECOND),
(759, NOW() - INTERVAL 758 SECOND),
(760, NOW() - INTERVAL 759 SECOND),
(761, NOW() - INTERVAL 760 SECOND),
(762, NOW() - INTERVAL 761 SECOND),
(763, NOW() - INTERVAL 762 SECOND),
(764, NOW() - INTERVAL 763 SECOND),
(765, NOW() - INTERVAL 764 SECOND),
(766, NOW() - INTERVAL 765 SECOND),
(767, NOW() - INTERVAL 766 SECOND),
(768, NOW() - INTERVAL 767 SECOND),
(769, NOW() - INTERVAL 768 SECOND),
(770, NOW() - INTERVAL 769 SECOND),
(771, NOW() - INTERVAL 770 SECOND),
(772, NOW() - INTERVAL 771 SECOND),
(773, NOW() - INTERVAL 772 SECOND),
(774, NOW() - INTERVAL 773 SECOND),
(775, NOW() - INTERVAL 774 SECOND),
(776, NOW() - INTERVAL 775 SECOND),
(777, NOW() - INTERVAL 776 SECOND),
(778, NOW() - INTERVAL 777 SECOND),
(779, NOW() - INTERVAL 778 SECOND),
(780, NOW() - INTERVAL 779 SECOND),
(781, NOW() - INTERVAL 780 SECOND),
(782, NOW() - INTERVAL 781 SECOND),
(783, NOW() - INTERVAL 782 SECOND),
(784, NOW() - INTERVAL 783 SECOND),
(785, NOW() - INTERVAL 784 SECOND),
(786, NOW() - INTERVAL 785 SECOND),
(787, NOW() - INTERVAL 786 SECOND),
(788, NOW() - INTERVAL 787 SECOND),
(789, NOW() - INTERVAL 788 SECOND),
(790, NOW() - INTERVAL 789 SECOND),
(791, NOW() - INTERVAL 790 SECOND),
(792, NOW() - INTERVAL 791 SECOND),
(793, NOW() - INTERVAL 792 SECOND),
(794, NOW() - INTERVAL 793 SECOND),
(795, NOW() - INTERVAL 794 SECOND),
(796, NOW() - INTERVAL 795 SECOND),
(797, NOW() - INTERVAL 796 SECOND),
(798, NOW() - INTERVAL 797 SECOND),
(799, NOW() - INTERVAL 798 SECOND),
(800, NOW() - INTERVAL 799 SECOND),
(801, NOW() - INTERVAL 800 SECOND),
(802, NOW() - INTERVAL 801 SECOND),
(803, NOW() - INTERVAL 802 SECOND),
(804, NOW() - INTERVAL 803 SECOND),
(805, NOW() - INTERVAL 804 SECOND),
(806, NOW() - INTERVAL 805 SECOND),
(807, NOW() - INTERVAL 806 SECOND),
(808, NOW() - INTERVAL 807 SECOND),
(809, NOW() - INTERVAL 808 SECOND),
(810, NOW() - INTERVAL 809 SECOND),
(811, NOW() - INTERVAL 810 SECOND),
(812, NOW() - INTERVAL 811 SECOND),
(813, NOW() - INTERVAL 812 SECOND),
(814, NOW() - INTERVAL 813 SECOND),
(815, NOW() - INTERVAL 814 SECOND),
(816, NOW() - INTERVAL 815 SECOND),
(817, NOW() - INTERVAL 816 SECOND),
(818, NOW() - INTERVAL 817 SECOND),
(819, NOW() - INTERVAL 818 SECOND),
(820, NOW() - INTERVAL 819 SECOND),
(821, NOW() - INTERVAL 820 SECOND),
(822, NOW() - INTERVAL 821 SECOND),
(823, NOW() - INTERVAL 822 SECOND),
(824, NOW() - INTERVAL 823 SECOND),
(825, NOW() - INTERVAL 824 SECOND),
(826, NOW() - INTERVAL 825 SECOND),
(827, NOW() - INTERVAL 826 SECOND),
(828, NOW() - INTERVAL 827 SECOND),
(829, NOW() - INTERVAL 828 SECOND),
(830, NOW() - INTERVAL 829 SECOND),
(831, NOW() - INTERVAL 830 SECOND),
(832, NOW() - INTERVAL 831 SECOND),
(833, NOW() - INTERVAL 832 SECOND),
(834, NOW() - INTERVAL 833 SECOND),
(835, NOW() - INTERVAL 834 SECOND),
(836, NOW() - INTERVAL 835 SECOND),
(837, NOW() - INTERVAL 836 SECOND),
(838, NOW() - INTERVAL 837 SECOND),
(839, NOW() - INTERVAL 838 SECOND),
(840, NOW() - INTERVAL 839 SECOND),
(841, NOW() - INTERVAL 840 SECOND),
(842, NOW() - INTERVAL 841 SECOND),
(843, NOW() - INTERVAL 842 SECOND),
(844, NOW() - INTERVAL 843 SECOND),
(845, NOW() - INTERVAL 844 SECOND),
(846, NOW() - INTERVAL 845 SECOND),
(847, NOW() - INTERVAL 846 SECOND),
(848, NOW() - INTERVAL 847 SECOND),
(849, NOW() - INTERVAL 848 SECOND),
(850, NOW() - INTERVAL 849 SECOND),
(851, NOW() - INTERVAL 850 SECOND),
(852, NOW() - INTERVAL 851 SECOND),
(853, NOW() - INTERVAL 852 SECOND),
(854, NOW() - INTERVAL 853 SECOND),
(855, NOW() - INTERVAL 854 SECOND),
(856, NOW() - INTERVAL 855 SECOND),
(857, NOW() - INTERVAL 856 SECOND),
(858, NOW() - INTERVAL 857 SECOND),
(859, NOW() - INTERVAL 858 SECOND),
(860, NOW() - INTERVAL 859 SECOND),
(861, NOW() - INTERVAL 860 SECOND),
(862, NOW() - INTERVAL 861 SECOND),
(863, NOW() - INTERVAL 862 SECOND),
(864, NOW() - INTERVAL 863 SECOND),
(865, NOW() - INTERVAL 864 SECOND),
(866, NOW() - INTERVAL 865 SECOND),
(867, NOW() - INTERVAL 866 SECOND),
(868, NOW() - INTERVAL 867 SECOND),
(869, NOW() - INTERVAL 868 SECOND),
(870, NOW() - INTERVAL 869 SECOND),
(871, NOW() - INTERVAL 870 SECOND),
(872, NOW() - INTERVAL 871 SECOND),
(873, NOW() - INTERVAL 872 SECOND),
(874, NOW() - INTERVAL 873 SECOND),
(875, NOW() - INTERVAL 874 SECOND),
(876, NOW() - INTERVAL 875 SECOND),
(877, NOW() - INTERVAL 876 SECOND),
(878, NOW() - INTERVAL 877 SECOND),
(879, NOW() - INTERVAL 878 SECOND),
(880, NOW() - INTERVAL 879 SECOND),
(881, NOW() - INTERVAL 880 SECOND),
(882, NOW() - INTERVAL 881 SECOND),
(883, NOW() - INTERVAL 882 SECOND),
(884, NOW() - INTERVAL 883 SECOND),
(885, NOW() - INTERVAL 884 SECOND),
(886, NOW() - INTERVAL 885 SECOND),
(887, NOW() - INTERVAL 886 SECOND),
(888, NOW() - INTERVAL 887 SECOND),
(889, NOW() - INTERVAL 888 SECOND),
(890, NOW() - INTERVAL 889 SECOND),
(891, NOW() - INTERVAL 890 SECOND),
(892, NOW() - INTERVAL 891 SECOND),
(893, NOW() - INTERVAL 892 SECOND),
(894, NOW() - INTERVAL 893 SECOND),
(895, NOW() - INTERVAL 894 SECOND),
(896, NOW() - INTERVAL 895 SECOND),
(897, NOW() - INTERVAL 896 SECOND),
(898, NOW() - INTERVAL 897 SECOND),
(899, NOW() - INTERVAL 898 SECOND),
(900, NOW() - INTERVAL 899 SECOND),
(901, NOW() - INTERVAL 900 SECOND),
(902, NOW() - INTERVAL 901 SECOND),
(903, NOW() - INTERVAL 902 SECOND),
(904, NOW() - INTERVAL 903 SECOND),
(905, NOW() - INTERVAL 904 SECOND),
(906, NOW() - INTERVAL 905 SECOND),
(907, NOW() - INTERVAL 906 SECOND),
(908, NOW() - INTERVAL 907 SECOND),
(909, NOW() - INTERVAL 908 SECOND),
(910, NOW() - INTERVAL 909 SECOND),
(911, NOW() - INTERVAL 910 SECOND),
(912, NOW() - INTERVAL 911 SECOND),
(913, NOW() - INTERVAL 912 SECOND),
(914, NOW() - INTERVAL 913 SECOND),
(915, NOW() - INTERVAL 914 SECOND),
(916, NOW() - INTERVAL 915 SECOND),
(917, NOW() - INTERVAL 916 SECOND),
(918, NOW() - INTERVAL 917 SECOND),
(919, NOW() - INTERVAL 918 SECOND),
(920, NOW() - INTERVAL 919 SECOND),
(921, NOW() - INTERVAL 920 SECOND),
(922, NOW() - INTERVAL 921 SECOND),
(923, NOW() - INTERVAL 922 SECOND),
(924, NOW() - INTERVAL 923 SECOND),
(925, NOW() - INTERVAL 924 SECOND),
(926, NOW() - INTERVAL 925 SECOND),
(927, NOW() - INTERVAL 926 SECOND),
(928, NOW() - INTERVAL 927 SECOND),
(929, NOW() - INTERVAL 928 SECOND),
(930, NOW() - INTERVAL 929 SECOND),
(931, NOW() - INTERVAL 930 SECOND),
(932, NOW() - INTERVAL 931 SECOND),
(933, NOW() - INTERVAL 932 SECOND),
(934, NOW() - INTERVAL 933 SECOND),
(935, NOW() - INTERVAL 934 SECOND),
(936, NOW() - INTERVAL 935 SECOND),
(937, NOW() - INTERVAL 936 SECOND),
(938, NOW() - INTERVAL 937 SECOND),
(939, NOW() - INTERVAL 938 SECOND),
(940, NOW() - INTERVAL 939 SECOND),
(941, NOW() - INTERVAL 940 SECOND),
(942, NOW() - INTERVAL 941 SECOND),
(943, NOW() - INTERVAL 942 SECOND),
(944, NOW() - INTERVAL 943 SECOND),
(945, NOW() - INTERVAL 944 SECOND),
(946, NOW() - INTERVAL 945 SECOND),
(947, NOW() - INTERVAL 946 SECOND),
(948, NOW() - INTERVAL 947 SECOND),
(949, NOW() - INTERVAL 948 SECOND),
(950, NOW() - INTERVAL 949 SECOND),
(951, NOW() - INTERVAL 950 SECOND),
(952, NOW() - INTERVAL 951 SECOND),
(953, NOW() - INTERVAL 952 SECOND),
(954, NOW() - INTERVAL 953 SECOND),
(955, NOW() - INTERVAL 954 SECOND),
(956, NOW() - INTERVAL 955 SECOND),
(957, NOW() - INTERVAL 956 SECOND),
(958, NOW() - INTERVAL 957 SECOND),
(959, NOW() - INTERVAL 958 SECOND),
(960, NOW() - INTERVAL 959 SECOND),
(961, NOW() - INTERVAL 960 SECOND),
(962, NOW() - INTERVAL 961 SECOND),
(963, NOW() - INTERVAL 962 SECOND),
(964, NOW() - INTERVAL 963 SECOND),
(965, NOW() - INTERVAL 964 SECOND),
(966, NOW() - INTERVAL 965 SECOND),
(967, NOW() - INTERVAL 966 SECOND),
(968, NOW() - INTERVAL 967 SECOND),
(969, NOW() - INTERVAL 968 SECOND),
(970, NOW() - INTERVAL 969 SECOND),
(971, NOW() - INTERVAL 970 SECOND),
(972, NOW() - INTERVAL 971 SECOND),
(973, NOW() - INTERVAL 972 SECOND),
(974, NOW() - INTERVAL 973 SECOND),
(975, NOW() - INTERVAL 974 SECOND),
(976, NOW() - INTERVAL 975 SECOND),
(977, NOW() - INTERVAL 976 SECOND),
(978, NOW() - INTERVAL 977 SECOND),
(979, NOW() - INTERVAL 978 SECOND),
(980, NOW() - INTERVAL 979 SECOND),
(981, NOW() - INTERVAL 980 SECOND),
(982, NOW() - INTERVAL 981 SECOND),
(983, NOW() - INTERVAL 982 SECOND),
(984, NOW() - INTERVAL 983 SECOND),
(985, NOW() - INTERVAL 984 SECOND),
(986, NOW() - INTERVAL 985 SECOND),
(987, NOW() - INTERVAL 986 SECOND),
(988, NOW() - INTERVAL 987 SECOND),
(989, NOW() - INTERVAL 988 SECOND),
(990, NOW() - INTERVAL 989 SECOND),
(991, NOW() - INTERVAL 990 SECOND),
(992, NOW() - INTERVAL 991 SECOND),
(993, NOW() - INTERVAL 992 SECOND),
(994, NOW() - INTERVAL 993 SECOND),
(995, NOW() - INTERVAL 994 SECOND),
(996, NOW() - INTERVAL 995 SECOND),
(997, NOW() - INTERVAL 996 SECOND),
(998, NOW() - INTERVAL 997 SECOND),
(999, NOW() - INTERVAL 998 SECOND),
(1000, NOW() - INTERVAL 999 SECOND);
NOW() - INTERVAL 999 SECOND
の部分で1秒ずつレコード事に時間をずらしてあります。
select count(*) from test.t1;
を定期的に実行しながらいつレコードが削除されるか見ていきます。
SQL実行(1000件Insert):10時25分
全件削除完了:10時29分
と4分ほどかかりました。もう一度やってみます。
SQL実行(1000件Insert):10時30分
全件削除完了:10時44分
ある程度のタイムラグがあることがわかります。ドキュメントには以下として記載されています。
TTL_JOB_INTERVALは1hデフォルトに設定されています。
以下のコマンドで実行間隔を変更することが可能です。
ALTER TABLE orders TTL_JOB_INTERVAL = '24h';
なおjobの実行履歴は以下のコマンドで知ることができます。
SELECT * FROM mysql.tidb_ttl_table_status ORDER BY last_job_start_time DESC;
実行間隔な1時間より短くすることが出来ません。つまりIntervalにminute
より短い時間を指定する必然性はないようです。
クラスターへの負荷を見ると1000件Insert時以降は1分に1度実行されたCount
のみが表示されておりTTLによるDelete
は影響を及ぼしていないことがわかります。
SQLによるバッチ処理との違い
以上のことからSQLによるバッチ処理との違いは以下とまとめることが出来ます。
・バックグランド処理であるため処理時間が不定
TTLによる削除は結果整合性となり、最大1時間のラグが発生する可能性があります。データロックなどを掛けることが出来ないという点からも、ミッションクリティカルなデータの退避や削除などはSQLベースのバッチ処理を行った方がよさそうです。
・クラスターへの負荷は発生しない
ここは運用上大きな利点になります。Timestampを検索範囲のベースとしたSQLは一般的な主キーを用いたバイナリーサーチより大きなリソースを消費してしまいます。このリソースを気にしなくてよくなるのは大きな利点と言えます。
削除タイミングの変更
ALTER TABLE t1 TTL = `created_at` + INTERVAL 1 MONTH;
を実行するとテーブルにセットされているTTLの変更が可能です。
また以下のSQLでTTLの機能を切ることが可能です。
ALTER TABLE t1 TTL_ENABLE = 'OFF';
ALTER TABLE t1 REMOVE TTL;
またジョブの実行を時間帯で指定するコマンドも準備されているようです。
SET @@global.tidb_ttl_job_schedule_window_start_time = '01:00 +0000';
SET @@global.tidb_ttl_job_schedule_window_end_time = '05:00 +0000';
これによりジョブを実行可能な時間を制御可能ですがTiDB ServerlessはUTC時間で動作していることに注意してください。
SELECT NOW();
制限事項など
に制限事項がまとまっています。TiDB Serverless とDedicated型TiDBには機能差があるようですのでご注意ください。一番、お!と思ったのは以下の制約でしょうか。
TTL 属性を持つテーブルは、外部キー制約のプライマリ テーブルとして他のテーブルから参照されることをサポートしていません。
ここはテーブル設計上の留意点として重要なポイントになりそうです。
Discussion