Massive SQL load mail app + fix!

Eesger · January 26, 2022, 3:28pm

Nextcloud version: 22.2.3
Operating system and version : AlmaLinux 8
Apache : 2.4.37
PHP version: 7.4

The issue I’m facing:
After adding a new account with a large mailbox mysql nearly kills the server

Is this the first time you’ve seen this error? (Y/N): I think I have, now I’m sure

Steps to replicate it:

create group + user
add IMAP mail account with a large box (preferably most in one folder?)

Most likely , step 2 will suffice.

In the mysql proces list I saw several (about a dozen) of these queries (caught with ‘show fill processlist’)

I tried this query in PHPmyAdmin, and it indeed took quite some time… After a while, when these queries have been processed it seems they do not return?
Could the Nextcloud system “cut these queries up” in multiple smaller ones?

SELECT `m`.`id` FROM `oc_mail_messages` `m` LEFT JOIN `oc_mail_messages` `m2` ON m.mailbox_id = m2.mailbox_id and m.thread_root_id = m2.thread_root_id and m.sent_at < m2.sent_at WHERE (`m`.`mailbox_id` = 285) AND (`m`.`id` NOT IN (75980, 75981, 75982, 75983, 75984, 75985, 75986, 75987, 75988, 75989, 75990, 75991, 75992, 75993, 75994, 75995, 75996, 75997, 76198, 76197, 76196, 76195, 76194, 76193, 76192, 76191, 76190, 76189, 76188, 76187, 76186, 76185, 76184, 76183, 76182, 76181, 76180, 76179, 76178, 76177, 76176, 76175, 76174, 76173, 76172, 76171, 76170, 76169, 76168, 76167, 76166, 76165, 76164, 76163, 76162, 76161, 76160, 76159, 76158, 76157, 76156, 76155, 76154, 76153, 76152, 76151, 76150, 76147, 76149, 76148, 76146, 76145, 76144, 76143, 76142, 76141, 76140, 76139, 76138, 76137, 76136, 76135, 76134, 76133, 76132, 76131, 76130, 76129, 76128, 76127, 76126, 76125, 76124, 76123, 75964, 76122, 76121, 76120, 76119, 76118, 76117, 76116, 76115, 76114, 76113, 76112, 76111, 76110, 76109, 76108, 76107, 76106, 76105, 76104, 76103, 76102, 76101, 76100, 76099, 76098, 76097, 76096, 76095, 76094, 76093, 76092, 76091, 76090, 76089, 76086, 76085, 76084, 76083, 76082, 76081, 76080, 76079, 76078, 76077, 76076, 76075, 76074, 76073, 76072, 76071, 76070, 76069, 76068, 76067, 76066, 76065, 76063, 76064, 76062, 76061, 76088, 76060, 76059, 76087, 76058, 76057, 76056, 76055, 76054, 76053, 76052, 76051, 76050, 76049, 76048, 76047, 76046, 76045, 76044, 76042, 76041, 76040, 76043, 76039, 76038, 76037, 76036, 76035, 76034, 76033, 76032, 76031, 76030, 76029, 76028, 76027, 76026, 76025, 76024, 76023, 76022, 76021, 76020, 76019, 76018, 76017, 76016, 76015, 76014, 76013, 76012, 76011, 76010, 76009, 76008, 76007, 76006, 76005, 76004, 76003, 76002, 76001, 76000, 75999, 75998, 75862, 75861, 75860, 75859, 75858, 75857, 75856, 75855, 75854, 75853, 75852, 75851, 75850, 75849, 75848, 75847, 75846, 75845, 75844, 75843, 75842, 75841, 75840, 75839, 75838, 75837, 75836, 75835, 75834, 75833, 75832, 75831, 75830, 75829, 75828, 75827, 75826, 75825, 75824, 75823, 75822, 75821, 75820, 75819, 75818, 75817, 75816, 75815, 75814, 75813, 75812, 75811, 75810, 75809, 75808, 75807, 75806, 75805, 75804, 75803, 75802, 75801, 75800, 75799, 75798, 75797, 75796, 75795, 75794, 75793, 75792, 75791, 75790, 75789, 75788, 75787, 75786, 75785, 75784, 75783, 75782, 75781, 75780, 75779, 75778, 75777, 75776, 75770, 75769, 75768, 75767, 75766, 75765, 75764, 75763, 75762, 75761, 75760, 75759, 75758, 75757, 75756, 75755, 75754, 75753, 75752, 75751, 75750, 75749, 75748, 75387, 75388, 75393, 75394, 75395, 75396, 75397, 75398, 75399, 75400, 75401, 75402, 75403, 75404, 75405, 75406, 75407, 75408, 75409, 75410, 75411, 75412, 75413, 75414, 75415, 75416, 75417, 75418, 75419, 75420, 75421, 75422, 75423, 75424, 75425, 75426, 75427, 75428, 75429, 75430, 75431, 75432, 75433, 75434, 75435, 75436, 75437, 75438, 75439, 75440, 75441, 75442, 75443, 75444, 75446, 75447, 75448, 75449, 75450, 75451, 75452, 75453, 75454, 75455, 75456, 75457, 75458, 75459, 75460, 75461, 75462, 75463, 75464, 75465, 75466, 75467, 75468, 75469, 75470, 75471, 75472, 75473, 75474, 75475, 75476, 75477, 75478, 75479, 75480, 75481, 75482, 75483, 75484, 75485, 75486, 75487, 75488, 75489, 75490, 75491, 75492, 75493, 75494, 75495, 75496, 75497, 75498, 75499, 75500, 75746, 75745, 75744, 75743, 75742, 75741, 75740, 75739, 75738, 75737, 75736, 75735, 75734, 75733, 75732, 75731, 75730, 75729, 75728, 75727, 75726, 75725, 75724, 75723, 75722, 75721, 75720, 75719, 75718, 75717, 75716, 75715, 75714, 75713, 75712, 75711, 75710, 75709, 75708, 75707, 75706, 75705, 75704, 75703, 75702, 75701, 75700, 75699, 75698, 75697, 75696, 75695, 75694, 75693, 75692, 75691, 75690, 75689, 75688, 75687, 75686, 75685, 75684, 75683, 75682, 75681, 75680, 75679, 75678, 75677, 75676, 75675, 75674, 75673, 75672, 75671, 75670, 75669, 75668, 75667, 75666, 75665, 75664, 75663, 75662, 75661, 75660, 75659, 75658, 75657, 75656, 75655, 75654, 75653, 75652, 75651, 75650, 75649, 75648, 75647, 75646, 75645, 75644, 75643, 75642, 75641, 75640, 75639, 75638, 75637, 75636, 75635, 75634, 75633, 75632, 75631, 75630, 75629, 75628, 75627, 75626, 75625, 75624, 75623, 75622, 75621, 75620, 75619, 75618, 75617, 75445, 75616, 75615, 75612, 75611, 75610, 75609, 75608, 75607, 75606, 75605, 75604, 75603, 75602, 75601, 75600, 75599, 75598, 75597, 75596, 75595, 75594, 75593, 75592, 75591, 75590, 75589, 75588, 75587, 75586, 75585, 75584, 75583, 75582, 75581, 75580, 75579, 75578, 75577, 75576, 75575, 75574, 75573, 75572, 75571, 75570, 75569, 75568, 75567, 75566, 75565, 75564, 75614, 75563, 75562, 75561, 75560, 75559, 75558, 75557, 75556, 75555, 75554, 75553, 75552, 75551, 75550, 75549, 75548, 75547, 75546, 75545, 75613, 75544, 75543, 75542, 75541, 75540, 75539, 75538, 75537, 75536, 75535, 75534, 75533, 75532, 75531, 75530, 75528, 75527, 75526, 75529, 75525, 75524, 75522, 75521, 75520, 75519, 75518, 75523, 75517, 75516, 75515, 75514, 75513, 75512, 75511, 75510, 75509, 75507, 75508, 75506, 75505, 75504, 75503, 75502, 75501, 75349, 75350, 75351, 75352, 75353, 75354, 75355, 75356, 75357, 75358, 75359, 75360, 75361, 75362, 75363, 75364, 75365, 75366, 75367, 75368, 75369, 75370, 75371, 75372, 75373, 75386, 75385, 75383, 75384, 75382, 75381, 75380, 75379, 75378, 75377, 75376, 75375, 75374, 75323, 75324, 75325, 75326, 75327, 75328, 75329, 75330, 75331, 75332, 75333, 75334, 75335, 75336, 75337, 75338, 75339, 75340, 75348, 75347, 75346, 75345, 75344, 75343, 75342, 75341, 75305, 75306, 75307, 75308, 75309, 75310, 75311, 75312, 75313, 75314, 75315, 75316, 75317, 75318, 75319, 75320, 75321, 75322, 75304, 75303, 75302, 75301, 75300, 75299, 75298, 75297, 75296, 75295, 75294, 75293, 75292, 75291, 75290, 75289, 75288, 75287, 75286, 75285, 75284, 75283, 75282, 75281, 75280, 75279, 75278, 75277, 75276, 75275, 75274, 75273, 75272, 75271, 75270, 75269, 75268, 75267, 75266, 75265, 75264, 75263, 75262, 75261, 75260, 75259, 75258, 75257, 75256, 75255, 75254, 75253, 75252, 75251, 75250, 75249, 75248, 75247, 75246, 75245, 75244, 75243, 75242, 75241, 75240, 75239, 75238, 75237, 75236, 75235, 75234, 75233, 75232, 75231, 75230, 75229, 75228, 75227, 75226, 75225, 75224, 75223, 75222, 75221, 75220, 75219, 75218, 75217, 75216, 75215, 75214, 75213, 75212, 75211, 75210, 75209, 75208, 75207, 75206, 75205, 75204, 75203, 75202, 75201, 75200, 75199, 75198, 75197, 75196, 75195, 75194, 75193, 75191, 75190, 75189, 75188, 75187, 75186, 75185, 75184, 75183, 75182, 75181, 75180, 75179, 75178, 75177, 75176, 75175, 75174, 75173, 75172, 75170, 75171, 75169, 75168, 75167, 75166, 75165, 75164, 75163, 75162, 75192, 75161, 75160, 75159, 75158, 75157, 75156, 75155, 75154, 75153, 75152, 75151, 75150, 75149, 75148, 75147, 75146, 75145, 75144, 75143, 75142, 75141, 75140, 75139, 75138, 75137, 75136, 75135, 75134, 75133, 75132, 75131, 75130, 75129, 75128, 75127, 75126, 75125, 75124, 75122, 75121, 75120, 75123, 75119, 75118, 75117, 75116, 75115, 75112, 75114, 75113, 75111, 75110, 75109, 75108, 75107, 75106, 75105, 75104, 75103, 75102, 75101, 75100, 75099, 75098, 75097, 75096, 75094, 75093, 75092, 75095, 75091, 75090, 75089, 75088, 75087, 75086, 75085, 75084, 75083, 75082, 75081, 75080, 75079, 75078, 75077, 75076, 75075, 75074, 75073)) AND (`m2`.`id` IS NULL) AND (`m`.`sent_at` > (SELECT MIN(`sent_at`) FROM `oc_mail_messages` WHERE (`mailbox_id` = 285) AND (`id` IN (75980, 75981, 75982, 75983, 75984, 75985, 75986, 75987, 75988, 75989, 75990, 75991, 75992, 75993, 75994, 75995, 75996, 75997, 76198, 76197, 76196, 76195, 76194, 76193, 76192, 76191, 76190, 76189, 76188, 76187, 76186, 76185, 76184, 76183, 76182, 76181, 76180, 76179, 76178, 76177, 76176, 76175, 76174, 76173, 76172, 76171, 76170, 76169, 76168, 76167, 76166, 76165, 76164, 76163, 76162, 76161, 76160, 76159, 76158, 76157, 76156, 76155, 76154, 76153, 76152, 76151, 76150, 76147, 76149, 76148, 76146, 76145, 76144, 76143, 76142, 76141, 76140, 76139, 76138, 76137, 76136, 76135, 76134, 76133, 76132, 76131, 76130, 76129, 76128, 76127, 76126, 76125, 76124, 76123, 75964, 76122, 76121, 76120, 76119, 76118, 76117, 76116, 76115, 76114, 76113, 76112, 76111, 76110, 76109, 76108, 76107, 76106, 76105, 76104, 76103, 76102, 76101, 76100, 76099, 76098, 76097, 76096, 76095, 76094, 76093, 76092, 76091, 76090, 76089, 76086, 76085, 76084, 76083, 76082, 76081, 76080, 76079, 76078, 76077, 76076, 76075, 76074, 76073, 76072, 76071, 76070, 76069, 76068, 76067, 76066, 76065, 76063, 76064, 76062, 76061, 76088, 76060, 76059, 76087, 76058, 76057, 76056, 76055, 76054, 76053, 76052, 76051, 76050, 76049, 76048, 76047, 76046, 76045, 76044, 76042, 76041, 76040, 76043, 76039, 76038, 76037, 76036, 76035, 76034, 76033, 76032, 76031, 76030, 76029, 76028, 76027, 76026, 76025, 76024, 76023, 76022, 76021, 76020, 76019, 76018, 76017, 76016, 76015, 76014, 76013, 76012, 76011, 76010, 76009, 76008, 76007, 76006, 76005, 76004, 76003, 76002, 76001, 76000, 75999, 75998, 75862, 75861, 75860, 75859, 75858, 75857, 75856, 75855, 75854, 75853, 75852, 75851, 75850, 75849, 75848, 75847, 75846, 75845, 75844, 75843, 75842, 75841, 75840, 75839, 75838, 75837, 75836, 75835, 75834, 75833, 75832, 75831, 75830, 75829, 75828, 75827, 75826, 75825, 75824, 75823, 75822, 75821, 75820, 75819, 75818, 75817, 75816, 75815, 75814, 75813, 75812, 75811, 75810, 75809, 75808, 75807, 75806, 75805, 75804, 75803, 75802, 75801, 75800, 75799, 75798, 75797, 75796, 75795, 75794, 75793, 75792, 75791, 75790, 75789, 75788, 75787, 75786, 75785, 75784, 75783, 75782, 75781, 75780, 75779, 75778, 75777, 75776, 75770, 75769, 75768, 75767, 75766, 75765, 75764, 75763, 75762, 75761, 75760, 75759, 75758, 75757, 75756, 75755, 75754, 75753, 75752, 75751, 75750, 75749, 75748, 75387, 75388, 75393, 75394, 75395, 75396, 75397, 75398, 75399, 75400, 75401, 75402, 75403, 75404, 75405, 75406, 75407, 75408, 75409, 75410, 75411, 75412, 75413, 75414, 75415, 75416, 75417, 75418, 75419, 75420, 75421, 75422, 75423, 75424, 75425, 75426, 75427, 75428, 75429, 75430, 75431, 75432, 75433, 75434, 75435, 75436, 75437, 75438, 75439, 75440, 75441, 75442, 75443, 75444, 75446, 75447, 75448, 75449, 75450, 75451, 75452, 75453, 75454, 75455, 75456, 75457, 75458, 75459, 75460, 75461, 75462, 75463, 75464, 75465, 75466, 75467, 75468, 75469, 75470, 75471, 75472, 75473, 75474, 75475, 75476, 75477, 75478, 75479, 75480, 75481, 75482, 75483, 75484, 75485, 75486, 75487, 75488, 75489, 75490, 75491, 75492, 75493, 75494, 75495, 75496, 75497, 75498, 75499, 75500, 75746, 75745, 75744, 75743, 75742, 75741, 75740, 75739, 75738, 75737, 75736, 75735, 75734, 75733, 75732, 75731, 75730, 75729, 75728, 75727, 75726, 75725, 75724, 75723, 75722, 75721, 75720, 75719, 75718, 75717, 75716, 75715, 75714, 75713, 75712, 75711, 75710, 75709, 75708, 75707, 75706, 75705, 75704, 75703, 75702, 75701, 75700, 75699, 75698, 75697, 75696, 75695, 75694, 75693, 75692, 75691, 75690, 75689, 75688, 75687, 75686, 75685, 75684, 75683, 75682, 75681, 75680, 75679, 75678, 75677, 75676, 75675, 75674, 75673, 75672, 75671, 75670, 75669, 75668, 75667, 75666, 75665, 75664, 75663, 75662, 75661, 75660, 75659, 75658, 75657, 75656, 75655, 75654, 75653, 75652, 75651, 75650, 75649, 75648, 75647, 75646, 75645, 75644, 75643, 75642, 75641, 75640, 75639, 75638, 75637, 75636, 75635, 75634, 75633, 75632, 75631, 75630, 75629, 75628, 75627, 75626, 75625, 75624, 75623, 75622, 75621, 75620, 75619, 75618, 75617, 75445, 75616, 75615, 75612, 75611, 75610, 75609, 75608, 75607, 75606, 75605, 75604, 75603, 75602, 75601, 75600, 75599, 75598, 75597, 75596, 75595, 75594, 75593, 75592, 75591, 75590, 75589, 75588, 75587, 75586, 75585, 75584, 75583, 75582, 75581, 75580, 75579, 75578, 75577, 75576, 75575, 75574, 75573, 75572, 75571, 75570, 75569, 75568, 75567, 75566, 75565, 75564, 75614, 75563, 75562, 75561, 75560, 75559, 75558, 75557, 75556, 75555, 75554, 75553, 75552, 75551, 75550, 75549, 75548, 75547, 75546, 75545, 75613, 75544, 75543, 75542, 75541, 75540, 75539, 75538, 75537, 75536, 75535, 75534, 75533, 75532, 75531, 75530, 75528, 75527, 75526, 75529, 75525, 75524, 75522, 75521, 75520, 75519, 75518, 75523, 75517, 75516, 75515, 75514, 75513, 75512, 75511, 75510, 75509, 75507, 75508, 75506, 75505, 75504, 75503, 75502, 75501, 75349, 75350, 75351, 75352, 75353, 75354, 75355, 75356, 75357, 75358, 75359, 75360, 75361, 75362, 75363, 75364, 75365, 75366, 75367, 75368, 75369, 75370, 75371, 75372, 75373, 75386, 75385, 75383, 75384, 75382, 75381, 75380, 75379, 75378, 75377, 75376, 75375, 75374, 75323, 75324, 75325, 75326, 75327, 75328, 75329, 75330, 75331, 75332, 75333, 75334, 75335, 75336, 75337, 75338, 75339, 75340, 75348, 75347, 75346, 75345, 75344, 75343, 75342, 75341, 75305, 75306, 75307, 75308, 75309, 75310, 75311, 75312, 75313, 75314, 75315, 75316, 75317, 75318, 75319, 75320, 75321, 75322, 75304, 75303, 75302, 75301, 75300, 75299, 75298, 75297, 75296, 75295, 75294, 75293, 75292, 75291, 75290, 75289, 75288, 75287, 75286, 75285, 75284, 75283, 75282, 75281, 75280, 75279, 75278, 75277, 75276, 75275, 75274, 75273, 75272, 75271, 75270, 75269, 75268, 75267, 75266, 75265, 75264, 75263, 75262, 75261, 75260, 75259, 75258, 75257, 75256, 75255, 75254, 75253, 75252, 75251, 75250, 75249, 75248, 75247, 75246, 75245, 75244, 75243, 75242, 75241, 75240, 75239, 75238, 75237, 75236, 75235, 75234, 75233, 75232, 75231, 75230, 75229, 75228, 75227, 75226, 75225, 75224, 75223, 75222, 75221, 75220, 75219, 75218, 75217, 75216, 75215, 75214, 75213, 75212, 75211, 75210, 75209, 75208, 75207, 75206, 75205, 75204, 75203, 75202, 75201, 75200, 75199, 75198, 75197, 75196, 75195, 75194, 75193, 75191, 75190, 75189, 75188, 75187, 75186, 75185, 75184, 75183, 75182, 75181, 75180, 75179, 75178, 75177, 75176, 75175, 75174, 75173, 75172, 75170, 75171, 75169, 75168, 75167, 75166, 75165, 75164, 75163, 75162, 75192, 75161, 75160, 75159, 75158, 75157, 75156, 75155, 75154, 75153, 75152, 75151, 75150, 75149, 75148, 75147, 75146, 75145, 75144, 75143, 75142, 75141, 75140, 75139, 75138, 75137, 75136, 75135, 75134, 75133, 75132, 75131, 75130, 75129, 75128, 75127, 75126, 75125, 75124, 75122, 75121, 75120, 75123, 75119, 75118, 75117, 75116, 75115, 75112, 75114, 75113, 75111, 75110, 75109, 75108, 75107, 75106, 75105, 75104, 75103, 75102, 75101, 75100, 75099, 75098, 75097, 75096, 75094, 75093, 75092, 75095, 75091, 75090, 75089, 75088, 75087, 75086, 75085, 75084, 75083, 75082, 75081, 75080, 75079, 75078, 75077, 75076, 75075, 75074, 75073)))) ORDER BY `m`.`sent_at` desc```

Eesger · January 27, 2022, 12:19pm

I started a topic at Query overload · Issue #5985 · nextcloud/mail · GitHub

That gave me a direction. I have tested some stuff and “hacked” a (massive) improvement, adding a sql index for mailbox_id in oc_mail_messages (the testquery jumped from taking over 2 seconds to about 0.1 second!)

So for now I’m saved