Hi,
Our dhis2 server handles load of more than 4000 daily users , reaching up to 4800 users.
This server sometimes, gets backlog, and delays due to excessive requests.
Some queries fetch data with delays, hence they create backlogs for new incoming requests.
Following query when run independantly, it takes more than 24 seconds to run. Hence if multiple times the same query is being executed, this would start creating extended waiting list, due to resource usage.
My point here is that, the core / development team to look into this query, and optimize this one please, OR provide us a quick way around.
There are similar examples, with different parameters of the same query present in slow logs.
Our OS : ubuntu 20.04
dhis2 version : 2.38.2.1
Build revision : e590bdc
java : 11.0.17
Query :
select ax.“dx”,ax.“uidlevel2”,ax.“daily”, sum(value) as value from (select ap.* from analytics_0 as ap union all select ap.* from analytics_2022 as ap ) as ax where ax.“dx” in (‘oT5HtJ9i34d’) and ax.“uidlevel2” in (‘oFjPz9oEhpJ’, ‘qdLaKMkjwH4’, ‘qN51eVO3Ckt’, ‘DdPi4GJytS7’, ‘n79fulcngts’, ‘FZ9rTtWYYS6’, ‘gKeVNIEbJQu’, ‘iwbXQ2UhIyQ’, ‘LehKUmLlkDt’, ‘rdsgPgiMji6’, ‘YYIjQaGacKp’, ‘jKxGC3hzsvf’, ‘UiWRcEumzgp’, ‘EYWvahs3a9W’, ‘CipzakslgpS’, ‘xp6Aor98DlM’, ‘tyJ7XyCgkKO’, ‘muX2EBVSnaW’, ‘PofX4HfxkIl’, ‘CTlNCVhAu9U’, ‘LHpq6zm22Ag’, ‘tFwuFLlVglg’, ‘lWdqPURCU5Y’, ‘GMBBhIbl88e’, ‘ltlrWhEOjKI’, ‘eb8ZX594XYw’, ‘AQ1jVBNU9Gl’, ‘lr0kK6Rl604’, ‘Netlg7mPGbq’, ‘gOCSelUnFTG’, ‘WFpkSLGawq1’, ‘oCkyatKfIRo’, ‘lkn5pn54NuL’, ‘XxdsAfe2hfT’, ‘G43eaQHGyCv’, ‘jHQON8QcZkf’, ‘HzctUPy7hmU’, ‘Xpu3jY7dosB’) and ax.“daily” in (‘20220901’, ‘20220902’, ‘20220903’, ‘20220904’, ‘20220905’, ‘20220906’, ‘20220907’, ‘20220908’, ‘20220909’, ‘20220910’, ‘20220911’, ‘20220912’, ‘20220913’, ‘20220914’, ‘20220915’, ‘20220916’, ‘20220917’, ‘20220918’, ‘20220919’, ‘20220920’, ‘20220921’, ‘20220922’, ‘20220923’, ‘20220924’, ‘20220925’, ‘20220926’, ‘20220927’, ‘20220928’, ‘20220929’, ‘20220930’, ‘20221001’, ‘20221002’, ‘20221003’, ‘20221004’, ‘20221005’, ‘20221006’, ‘20221007’, ‘20221008’, ‘20221009’, ‘20221010’, ‘20221011’, ‘20221012’, ‘20221013’, ‘20221014’, ‘20221015’, ‘20221016’, ‘20221017’, ‘20221018’, ‘20221019’, ‘20221020’, ‘20221021’, ‘20221022’, ‘20221023’, ‘20221024’, ‘20221025’, ‘20221026’, ‘20221027’, ‘20221028’, ‘20221029’, ‘20221030’, ‘20221031’, ‘20221101’, ‘20221102’, ‘20221103’, ‘20221104’, ‘20221105’, ‘20221106’, ‘20221107’, ‘20221108’, ‘20221109’, ‘20221110’, ‘20221111’, ‘20221112’, ‘20221113’, ‘20221114’, ‘20221115’, ‘20221116’, ‘20221117’, ‘20221118’, ‘20221119’, ‘20221120’, ‘20221121’, ‘20221122’, ‘20221123’, ‘20221124’, ‘20221125’, ‘20221126’, ‘20221127’, ‘20221128’, ‘20221129’, ‘20221130’, ‘20221201’, ‘20221202’, ‘20221203’, ‘20221204’, ‘20221205’, ‘20221206’, ‘20221207’, ‘20221208’, ‘20221209’) and ax.“year” in (0, 2022) group by ax.“dx”,ax.“uidlevel2”,ax.“daily”