search

rfcx / rfcx-api

Core, Media/Assets and MQTT APIs
https://api.rfcx.org/docs/
Apache License 2.0
0 stars 0 forks source link

Investigate the slowness of /summary/:classification endpoint #614

Closed Tooseriuz closed 5 months ago

Tooseriuz commented 5 months ago

reference log: https://grafana.rfcx.org/explore?orgId=1&left=%5B%221715758245000%22,%221715758315000%22,%22Loki%22,%7B%22expr%22:%22%7Bjob%3D%5C%22staging%2Fcore-api%5C%22%7D%22%7D%5D

log:

2024-05-15 14:30:51 |  
-- | --
  |   | 2024-05-15 14:30:51 | core query--------------------
  |   | 2024-05-15 14:30:51 | Executing (default): SELECT count(DISTINCT("classification_id")) AS "count" FROM "classifier_job_summaries" AS "ClassifierJobSummary" WHERE "ClassifierJobSummary"."classifier_job_id" = '134';
  |   | 2024-05-15 14:30:51 | ----------------------------------
  |   | 2024-05-15 14:30:51 |  
  |   | 2024-05-15 14:30:51 | core query--------------------
  |   | 2024-05-15 14:30:51 | Executing (default): SELECT "id", "name", "description", "is_public" AS "isPublic", "organization_id" AS "organizationId", "created_by_id" AS "createdById", "external_id" AS "externalId", "is_partner" AS "isPartner", "min_latitude" AS "minLatitude", "min_longitude" AS "minLongitude", "max_latitude" AS "maxLatitude", "max_longitude" AS "maxLongitude", "preferred_platform" AS "preferredPlatform", "created_at", "updated_at", "deleted_at" AS "deletedAt", "organization_id", "created_by_id" FROM "projects" AS "Project" WHERE "Project"."id" = '3dvrocmagfiw';
  |   | 2024-05-15 14:30:51 | ----------------------------------
  |   | 2024-05-15 14:30:51 |  
  |   | 2024-05-15 14:30:51 | core query--------------------
  |   | 2024-05-15 14:30:51 | Executing (default): SELECT count("Detection"."start") AS "count" FROM "detections" AS "Detection" INNER JOIN "classifications" AS "classification" ON "Detection"."classification_id" = "classification"."id" WHERE ("Detection"."start" >= '2018-01-01 00:00:00.000 +00:00' AND "Detection"."start" < '2024-05-10 23:59:59.000 +00:00') AND "classification"."value" IN ('sciurus_carolinensis_simple_call_1') AND "Detection"."classifier_id" IN ('3') AND "Detection"."classifier_job_id" IN ('134') AND "Detection"."confidence" >= '0.1' AND "Detection"."stream_id" IN ('fzcz82bb1596', 'qg0gg77cftv8', '80rp6vfx04hw', '6tg4c3gjph3r', '9mr75vs4tza1', 'k3hxji18xdx6', 'xsjnhc2yoa82', '5vjebuv4bkce', 'rnngdh0xne9b', 'emxmdz93rpsj', 'i2iccs7gw8cb', 'jfg2agt1wz6j', 'f98ql3belzdx', 'w0udo5g6ncq1', 'cxhb75lm310s', '79wdb4x59phs', 'ih8bqmp1s3um', 'xuerqvzym6jm', 'nal01fsi0dgk', '725rdgb8wfqm', '4sm6ecbrplmm', '1bqx53v34ohm', '97wdoaedeex8', 'c5sm5uxe5d9t', 'pt5wv50e28sj', 'gm5vy02d6uwl', '3xk9zojo58ev', 'melbe6oy4zo6', '2in2cd2zkd74', '4cjvmch8w44z', 'azzhqtvc9fhc', 'jclojxelxmfv', 'cj3y240lr9rp', '67n7qed81vfl', 'fr1cg0fbdp61', '0id8vsupwsj0', 'c7rdbbf818s1', '837vlhhoefre', 'whu99xt3stqf', 'rrko3k2d4a96', 'tbnnru39ip0r', '8fc5xsdjdkn3', 's04skckxm9n0', 'yesiwfxejtcn', 'ody7657rdhwv', '10ys01hfexvb', '29zd31uqx7k8', 'sy04lkmjmp6g', 'mltdixgmxt9m', '7nr146a8655b', 'hzci5gb3twak', 'ct5f1k86rpwv', 'c0r3llid9n8e', '8ikdli2wy596', 'f884iykvsp0g', 'cytk32tpkudj', '7dj4lusljlkw', 'oo96r2ipmiza', 'lo7a3rkrp5qp', 'm3l2hyb5m4c7', '695s5z9uftwj', 'ts6zgrbjqgp9', '52rb6h6ybrk6', 'cr86ejoj90al', '3b4aynw88ru3', 'jbx9no276g3a', 'xslm5yrpy8ny', 'cgtx2k8kqtaj', 'qxg0acgpr66c', 'sru51egwak5y', 'm580gdbfspvr', 'ib76jssr26ud', '2zw8omoi7xjd', 'fc01relgyn5y', 'wgpewfr2vs5n', 'kew23dazldra', '0gociad5dp12', '34wfc1t61j92', 'g3eofbsmfet6', 'slzvvolk7lgb', 'xnx7w5l4i5sn', 'j57tfznbx90a', 'thsq2t524wp3', 'cjjps2mtnubm', 'xmkk6fb5hcea', 'xa6vm2dfygy2', '3m0v8pfa0emh', 'njtp58cssx37', '1juykle7bprz', 'qvzxmcp5duid', '9y6gtapp0uaa', '2mnglrzic7sd', 'phjuxj1z0ur6', 'fa312cdht0ca', 'jaglvv85apnu', '7i3nsxaz0qfr', 'lqcrw1a6eizo', '0hu161ybg7ze', 'ik6byoa5uys3', 'd6r7zlm9tmip', '9wjj00us811q', 'se46f89uvyki', '0tf4flxzxc79', 'pq7e14stuoyv', 'bel3du3rzi77', 'gv68mtkh26rh', 'mm8uca730apw', '3x75daciv0n1', 'ysw86hi6icy3', 'doujse8rdbk1', 'k4o3smu2wgwm', 'irgson0sa2iw', '47hmfj57fy51', 'rlwza66dej2n', 't6z3mnecgzfc', 'tgzwneefodsr', '3dstqv1t6nlz', '3zu4ip5t08lk', '7rpri9llv5cd', 'q7fjsxy0gyfp', 'icliwv1s0apl', 'qkzfxlt6880s', 'qurokn0fr3a5', '73qip0rdd3k2', '1wj6y09fet27', 'e6f6voqggy8c', 'pinxc6131udv', 's5a4oh6iaeww', 'gl4zpjl9i6cj', 'hz2xoieh52w3', '4r40fo6yr466', 'vs9rf2x4aspv', '7kkk2bb1jhdx', 'co7h5flqxpn0', 'e9r3614qr8jp', '08qcrsrf0oth', 'lofb57c6hld5', 'm3efh06g59rb', '2q9881im7iz5', 'ncn65fay8odd', '83fggztc7j1u', 'wskrljoxfzy3', '8op1kjb5g6tu', 'yfxewf5bbwxx', 'is0mr1kxiho9', 'vjzmf4qewpcc', 'b67okqdx9ltu', 'y4oyhpmva3y1', '7520cfws4zwd', '1d8is4liijor', 'lpkajiaqrf3f', '8j10yr7qkgqb', 'ex03t6eia641', 'gbs90q92qosc', 'v9i2kdgm30hv', 'ac4a6k1ah66r', '14m2166uf3ng', 'iemocz34blf8', 'lzpt6u7ulbmh', '7reu447lacj8', '2oprneotbspg', '0v7cy0hppg8t', 'feme9fkksieg', 'fr6xgxe3e2gt', 'b1qe9c82s1ah', 'sp0alstmt8vd', 'j415ox09e8q8', 'ohiano1fr25j', 'l9dhnk3widde', '70gsqsdonrl6', 'is0irqe8vkmp', 'br5rk3b3ffqy', '3ae1fyejvztl', 'qz4lkqmn2d0p', 'jgly6va2zybj', '1n2yvk3eboxu', 'bp4m0pc2ltkn', 'pq7l1x48xtqr', '7qqfc7mqtzxi', 'mabvyv6clh79', '6pajpysjkrm0', 'zgjejh2tqaf1', 'dwyu0byx1lod', 'tcffnh2uq3zk', 'r5w6nfs45v5u', '3lgccclz6h3l', '4c66y8hnwnkk', 'o3h756whg4fp', 'vltu6xjyzhrw', 'n5aeu2eq2emf', 'ops61vlt0af4', 'bghq5ggn44x4', 'mcsyprfcqm98', 'f6k2b7aww101', 'hf86mil5tbdw', 'yei2cxytoynw', 'zqycasqqdkm2', 'jxybf8tiozv2', 'z2j7lye68tf6', 'df8q35otdb2c', 'pfzzyeqx7bl1', 'ebthwn8cprkp', 'eyocivlbthbh', 'egotezp2ri7w', 'gmupki5uw0qo', 'purwgznujinz', '4tg4t7b400ik', 'sv044fxcu5cs', 'kwelviz1ch6a', 'j3jmsn6fei8q', 'wog98sm4eqz6', 'u4l7z9d85r9y', 'ftbwky71qqv8', 'kakuagizr5dx', 'qc2h8w60laqq', 'z5384utmwc6n', '8lgxnq0e83ih', 'pavclb2vl1m3', 't8pj2vcjtmi5', 'trdcsw8q3nl7', 'jc9ipubcj36w', 'x1jfztkutr84', 'maih8h9h2pvz', '8hfp3pn7yt31', 'dbuua7l9u72u', 'kbnj8dwl7j3z', 'fyb1gpi5j2qz', '6wmksxnrtei1', 'fn6r31kvkebg', 'yugolhs36izb', 'e37z51w4wx3f', 'r5diq6hzisdf', 'rvkny3hlpyju', 'osw8kltwyalm', 'aswel3n6jp2v', 'n475gep6t2d3', 'wkx0a5alup7m', 'ddoz9n7leml2', '4942uyfd54lz', 'cvp9e0zxrrqq', 'x5lbzfvdict6', 'sapqqu3pfoe6', 'phwf7mqk31sq', '2z9mc3tvw0ww', 'ym2ohxdsss53', 'dsftr524qqb7', 'up2gv814tr7v', 'sam1ld31fu18', 't2lrmwx9ivhf', 'b25b06zrutgp', 'kmwqiizyvs1k', 'cvv0hfvsdqmt', '3sw1acjc6n5y', 'j6bwxwovzqtq', 'g068ki8n0ed9', 'hswc7scmaexh', 'wx6r704ii4hh', 'cadxisz4gnyu', 'ezettqt5ertg', 'ekrvf67e4kh8', 'm23jm8bjjhmo', 'vc4nl80pm7jm', 'h34g46c39sil', 'r9wrkxy9k15a', 'qu1ne30i1nnm', '79a2iirpq5li', '4g7069tsjj3v', 'ivw2ifdlhnh5', 'wiydr3u8p82v', 'td3f9n6yrcug', 'j1ftxfmtihdl', 'qvomrb1jz406', 'lgdjm8ysyxq3', '4sa4zbz9a2u2', 'cxdi3kzlpkgv', 'uoukd0lti9b1', '88sdl1face3a', '4tf4zwuzsfjw', '41iaoi589s2e', 'q8869oam4tzh', 'cstsrzcpg490', 'svq0jzyqhrdx', 'tfchco69w1sh', 'ppc661wcp6l1', 'p9ojoz9b6fte', '8r3oeiiav9ey', '3hk2kxn696vy', 'k0izq2r1amn2', 'hkz3d00lum1g', 'hj35mudfm2zz', 'vtu618898uc2', '9ipeyppm1lug', 'iheee7sxl063', '7g7o4ltgtbua', 'djrv4tn9pqm1', '1rp3fwc8kj30', '7kgk5ar9fn4a', '41y66cyajr8d', 'px0dvkh5ehs3', '4ess5oepu0bq', 'x9d2uplvaceq', 'sz1c3ulmveeu', 'dt9ut0ig9mqw', '0d3fxwos9ucd', '97izjh7hw8cn', 'uhva1zvt35is', 'yu4obk2ny267', '0y6h9silp6c6', 'zgybwzqa1k3b', 'gxydb5jdjjr6', 'vb1t4f0k8ce3', 'bzlo5qvieq15', 'b2qjgxy1bfzo', 'xqcth5uvwomx', '9pmqy4m9kxxc', 'kudnr5iydh64', '71f5e3oib0q1', 'cf12zyf5pyyy', '2hr9ws7kvli8', '6jdp8fyyw608', 'sk61me4ap1sx', 'iwtefwbb0jy1', 'd5vty1c8u91d', 'ipgl1k4rwkzp', 'la6ooupz3wvn', '2dss531hmizw', 'wa1gb2aplivh', 'qrt0j692blcg', '34r0kvczppk3', 'csjmlp5q2ndx', '1rgo4inukm6m', 'muc4waaq2nw0', 'aebbc0uiq819', 'hf6fa8whi181', '0qjvp6sbv2dm', 'a2nf1hu64jm7', '93oxz8f6mlih', 'dt2dp36n381y', 'dgfo7djvucuc', 'w5g08ofj1du9', 'qcsa68girsme', 'cpzdexzx20iq', 'yf82oml7npt6', 'xo9wtwh9bvqw', 'ahtjnofowsau', '6wdjn85x3ryt', 'ulbmdl5s0k61', 'twipwf3xfpl3', 'jf4cvnv5daw9', '944vg61ws8kt', '42np4xeck186', 'svl0c1vvj4p0', 'rqvwnhg0fdzq', 'q9b5r0gdgz7j', 'g2jqh43kw1kj', 'mxnzh57yzpjv', 'vwnxbg0x7ecz', 'y6ilpjrf4ltp', '6zdpz2fjfien', '8gprqsnaoudz', 'sk7vkzw39vsa', 'k7dyq3rmp8at', '4oei96q2t3ay', '949ycmkn2n1y', 'j58a5xhyfgec', 's0nogcjj6o5b', 'ybaoksoxbaoz', 'm2xj910ccpus', 'kzspf81lfjf7', 'aus7vrhv95d9', 'j4529owqqnm4', 'eeqwtcixmtse', 'valtzhj0ec4c', 'lrnyoa214j3n', 'b7gf0dywf7vp', 'j6eyodjwj0a6', 'yxxx2z8ygswq', 'f6mgq6nzqsra', '8b2wjykvm098', 've1rbzxrdkn8', 'fl6zbelr6cen', 'nv8t0kpmyfex', '6cpaf6j43bvr', 'jg8uvmbpi5ep', '8lcxrxbksozp', 'zu6wxx2p9e1g', 'ih9kohg2prh3', '9h2js5wpmw9z', 'cyvtb54vo1n6', 'xdsdptpxgrap', '38jekpgjhjp9', 'rhziid1nc6bx', '2bvd03mzbikt', 'wxzt657u39f4', 'x6fhnh9al666', '8xpoczs32xwt', 'a0qo84ybbs68', 'dkhjsoi2wuf4', 'ip9nukswfkar', '91iff26tn1aa', 'rsw08as7q44t', '3jsq9w6tb3v5', 'olqvxxopecvq', '6qdv6n9pgue7', '11noon0tywhr', 'bdr0x4dr3edk', 'xw45lj5xc0bx', '6qrlynzaq40b', 'yocsuq5iurw6', 'ytm7qhzs6qo9', 'wc4m1gc5n5lo', 'sj6eefe31qwi', 'ai9rt50jngvx', 'utsl3vg5v5u5', '8hlnl08amdht', 'e9p0jkvy35u6', '5ekf3v9n7fe6', 'r5xw54vvuck8', '3l31jxfqo7fr', 'l39vizt8sr2e', 'ahc7382kebyv', '2pxdri62rsbw', 'kjvrj02n5b1e', '7ctlw96kwpdz', '2o0in6juqwlw', 'kmf8hnb927om', 'wfc73msg77z5', 'oth1jgkkkwek', '7zehs1ud0pe3', '7noks481t3mx', '3b7j4bh810o4', 'vnj1i1h7dn4q', '8s1yhv5mk765', 'e49o8w7juvx0', 'ubhxxmopsrd0', '2nru5xof8niq', 'w08r9cgj0nwr', 'ytdakknyhy6h', '7kof3ytz4pcm', '39cplgx9wix9', 'n1xa65ltylm9', 'w5a47063fwqq', 'j2whobwz09k5', 'juw4ra2o7m8g', 'r3l0gvfss4rg', '727g5myx8gr3', 's8vl93albvpi', 'mg7dbo0nxk2j', 'lstvzi2ukhle', 'j22wqfypw6f8', 'dyw9gudgludd', 'ol37v9xd86r7', '1ldei1008tmi', 'ylvgehci3elh', 'km66hmu6652t', 'jbl33movmwih', 'x0ngjei5bj27', 'qu7nuteqel7k', 'ipsrbkjce57g', 's2qun0p5xst7', 'pomw3labmhmo', 'qwf0j1pp3zmr', 'q21pya3nk5dk', '17b1udlgcrsd', '3c0s9f2l5hdc', 'ewkyahk00h66', 'wmc6e490upov', '75fx5x48thb8', '5oztbnoyy7zj', 'x3jxd9oqmxbc', 'biqtjwkzqeg8', 'du1zarmoa865', 'j8szp6ic0k95', 'q0eusxics1jy', 'mcprikn1a2xt', '1hm668tav0tp', 'u84sntk2twe6', '7tiq5baklm1t', 'qpn6icv09u22', '5xjsy5frgszs', '1gxudu4tlaud', 'f1v74jglqgoa', 'sfj9otnk8xtz', 'gl7zb1su7h5e', 'n6bmk71b5g13', 'nx4uh1alkpj5', 'h2t1a1m82jwh', 'qdawvgrd0rn5', '69tp0niy0c2p', 'mnctdtafc8e2', 'aumjwv10xlbc', 'u3cm1ry3vk5d', '0cyn40ribk78', 'cbr8xg6otgho', 'erdhv1w2wzt8', 'kwscby67am6w', 'rlrvbhx968wk', 'gxss2vm75pca', 'zur6758ubwgk', 'igkxj9scro4p', 'usyywlk1isw6', '4119ovllnbmr', '9nejeaifoswj', 'givtiieoxmem', '9910gwe53xbo', '4y7gv65j83gy', 'v7krc3auw5ii', 'g2a4zc4e14om', 'aka1urf6ppyj', 'q4clyx60bfsi', 'tnxd63wg5sbb', 'b83n5b20wj2o', '5jedduoypwei', '972aov43qm2v', '5htkw26648bn', 'xn9q4xrfd0oh', 'kh3d65tmujc3', 'wzcaji5drafo', '53i7by1am5di', '9h3t5wqovduo', '9hbn80wwmsjw', 'f6b2mmgums0k', 'kv3frpmmb0in', 'n67ra1oji76u', 'ratdsx6gzfu2', 'rq0ih0h6y9a7', 'd6c6kh70z1uk', 'js99ay8t8k1c', 'tkzjwz7okrz3', 'f4pue8ficsb0', 'caasywbczuyh', 'o9d784ra9f0d', 'mbjqaisr2pg7', 'e01xcqncc2z2', 'roy7p4dne71l', 'drrjd0olch3a', '6dajfr0bvx4r');
  |   | 2024-05-15 14:30:51 | ----------------------------------
  |   | 2024-05-15 14:31:51 | Http handler: request 8a9f3be1-c632-45ff-bc4e-e3bc40a7ae0c, message "project with given id doesn't exist.", context null
  |   | 2024-05-15 14:31:51 | warn: GET 404 /classifier-jobs/134/summary/sciurus_carolinensis_simple_call_1 Response Time: 67365 Authorization: Bearer eyJhbGciOiJSUzI1NiIsInR5cCI6IkpXVCIsImtpZCI6IlF6UTROakpHTkRVd01UTTJSVGREUmpRMVFqazROa001UVVFMU1URTJSREl5T1VJME9VRkVOQSJ9.eyJhdXRoMF91c2VyX2lkIjoiYXV0aDB8NjQ1MWM2OWM5ZWEwZDc4YjczNDZlYTYxIiwiaHR0cHM6Ly9yZmN4Lm9yZy9hcHBfbWV0YWRhdGEiOnsiYXV0aG9yaXphdGlvbiI6eyJncm91cHMiOltdLCJyb2xlcyI6WyJhcHBVc2VyIl0sInBlcm1pc3Npb25zIjpbXX0sImd1aWQiOiJlZTAyM2I3OC03MTdmLTQ0NmUtOTUzNS1hOTFiZWJhZWRiZGIiLCJsb2dpbnNOdW1iZXIiOjEzMX0sInVzZXJfbWV0YWRhdGEiOnsiY29uc2VudEdpdmVuIjoidHJ1ZSIsImNvbnNlbnRHaXZlbkRhc2hib2FyZCI6InRydWUiLCJjb25zZW50R2l2ZW5BY291c3RpY3NFeHBsb3JlciI6InRydWUiLCJjb25zZW50R2l2ZW5SYW5nZXJBcHAiOiJ0cnVlIn0sImh0dHBzOi8vcmZjeC5vcmcvdXNlcl9tZXRhZGF0YSI6eyJjb25zZW50R2l2ZW4iOiJ0cnVlIiwiY29uc2VudEdpdmVuRGFzaGJvYXJkIjoidHJ1ZSIsImNvbnNlbnRHaXZlbkFjb3VzdGljc0V4cGxvcmVyIjoidHJ1ZSIsImNvbnNlbnRHaXZlblJhbmdlckFwcCI6InRydWUifSwiZ3VpZCI6ImVlMDIzYjc4LTcxN2YtNDQ2ZS05NTM1LWE5MWJlYmFlZGJkYiIsImxvZ2luc051bWJlciI6MTMxLCJnaXZlbl9uYW1lIjoiQmhhdHRhcmFwb25nIiwiZmFtaWx5X25hbWUiOiJTb213b25nIiwibmlja25hbWUiOiJhcnQiLCJuYW1lIjoiQmhhdHRhcmFwb25nIFNvbXdvbmciLCJwaWN0dXJlIjoiaHR0cHM6Ly9hcmJpbW9uLnMzLmFtYXpvbmF3cy5jb20vdXNlcnMvMTlmNDUxYmZiNzY1NGVjMWRhZWE3NGNkYTY0MjhhYTNlNzZkOTA5NjI0YWM2MThhMDRmNDJmZDAzMDYzOTU3MS9wcm9maWxlLWltYWdlLTYyNjEyM2MzLmpwZyIsInVwZGF0ZWRfYXQiOiIyMDI0LTA1LTE1VDA2OjM2OjM0LjIzM1oiLCJlbWFpbCI6ImFydEByZmN4Lm9yZyIsImVtYWlsX3ZlcmlmaWVkIjp0cnVlLCJpc3MiOiJodHRwczovL2F1dGgucmZjeC5vcmcvIiwiYXVkIjoiTGlvamR2TlVzZXJHbkNhTGo4Y2tjeGVHUEhPS2l0T2MiLCJpYXQiOjE3MTU3NTYzMzcsImV4cCI6MTcxODM0ODMzNywic3ViIjoiYXV0aDB8NjQ1MWM2OWM5ZWEwZDc4YjczNDZlYTYxIiwic2lkIjoiZEFxdUY4ZlFKRTd5dUhiQVRrY2o5SklwSDR2X2UzRmIiLCJub25jZSI6IlQyMW5ZMGRyU3pKRk1GTldTWE5MZFV0cWNscHRMakZ1UW1OeWIyWlhjRE54YW05bWEyWjZkRk0wVEE9PSJ9.St1TVG-7S5hXbkVCXuPP45wreq0I-1MnvANyrveerT3YYGgRusxZkVLHANIPKsKjGi2slVqnIJIKO9x5haNV3kWVchWcAIVrTPZUsgGd61x5ZicNOPL8LBFjtgdsbsDsE_pnwuaFn5n9b47Rs67ETXQGMYa6JQ9Upm2ipVe-akVLd-4ydYNZVm1sQpvLfzWowzLUK0BJw6li2GW6vpti0TfHkxuU2wZn5WI0OzTpNhtcMQaK_7P-CzS2fwZC4AQXZp9zzGVMEpBFi60jmL5xo-bShCpGx2Qz-iDfOxLKlRDSBYoxPKYHW7hgZkj1EmGlLb23Q5WGIhrXT16n1vzqkw Email: art@rfcx.org Body: {} {"meta":{}}
  |   | 2024-05-15 14:31:51 | httpErrorHandler ConnectionAcquireTimeoutError [SequelizeConnectionAcquireTimeoutError]: Operation timeout