synapse
synapse copied to clipboard
element-hq
High database usage by `get_auth_chain_ids_chains` after no particular event
Description
The CPU usage of my server started to increase after no particular action on my end. After digging a little, I did find that the get_auth_chain_ids_chains transaction started to take a lot more time that previously.
Synapse exporter:
Postgres exporter:
Steps to reproduce
- Run synapse for a long time :sweat_smile:
Homeserver
club1.fr
Synapse Version
1.103.0
Installation Method
Debian packages from packages.matrix.org
Database
PostgreSQL single server, restored from a backup
Workers
Single process
Platform
- Debian 11 Bullseye
Linux club1.fr 5.10.0-31-amd64 #1 SMP Debian 5.10.221-1 (2024-07-14) x86_64 GNU/Linux- No VM, no Containers, Bare metal
Configuration
Definitely some unusual config:
# Message retention policy at the server level.
#
# Room admins and mods can define a retention period for their rooms using the
# 'm.room.retention' state event, and server admins can cap this period by setting
# the 'allowed_lifetime_min' and 'allowed_lifetime_max' config options.
#
# If this feature is enabled, Synapse will regularly look for and purge events
# which are older than the room's maximum retention period. Synapse will also
# filter events received over federation so that events that should have been
# purged are ignored and not stored again.
#
retention:
# The message retention policies feature is disabled by default. Uncomment the
# following line to enable it.
#
enabled: true
# Default retention policy. If set, Synapse will apply it to rooms that lack the
# 'm.room.retention' state event. Currently, the value of 'min_lifetime' doesn't
# matter much because Synapse doesn't take it into account yet.
#
default_policy:
min_lifetime: 1d
max_lifetime: 3y
media_retention:
local_media_lifetime: 2y
remote_media_lifetime: 90d
# When this option is enabled, the room "complexity" will be checked before a user
# joins a new remote room. If it is above the complexity limit, the server will
# disallow joining, or will instantly leave.
limit_remote_rooms:
enabled: true
complexity: 10
complexity_error: "Désolé, cette room est trop complèxe pour le serveur CLUB1."
admins_can_join: false
# disable presence completely
presence:
enabled: false
Relevant log output
https://static.club1.fr/nicolas/random-other/log-synapse-2024-07-19.log
Anything else that would be useful to know?
No response
I enabled DEBUG logging for synapse.storage.SQL, and I can sew quite a few blocks like this one:
2024-07-22 21:47:14,401 - synapse.storage.SQL - 493 - DEBUG - GET-3728057 - [SQL time] {get_auth_chain_ids_chains-c52929} 6.112671 sec
2024-07-22 21:47:14,420 - synapse.storage.SQL - 467 - DEBUG - GET-3728057 - [SQL] {get_auth_chain_ids_chains-c52929} WITH RECURSIVE links(chain_id) AS ( SELECT DISTINCT origin_chain_id FROM event_auth_chain_links WHERE origin_chain_id = ANY(?) UNION SELECT target_chain_id FROM event_auth_chain_links INNER JOIN links ON (chain_id = origin_chain_id) ) SELECT origin_chain_id, origin_sequence_number, target_chain_id, target_sequence_number FROM links INNER JOIN event_auth_chain_links ON (chain_id = origin_chain_id)
2024-07-22 21:47:14,421 - synapse.storage.SQL - 472 - DEBUG - GET-3728057 - [SQL values] {get_auth_chain_ids_chains-c52929} [[1733489, 448752, 1733506, 448754, 1602442, 1733521, 1602450, 1733538, 1602478, 454457, 448763, 454458, 448771, 1733609, 1733613, 448784, 1471551, 448794, 1602642, 448802, 1602710, 448812, 1602732, 1602750, 1733899, 1733903, 1602838, 1733921, 1602890, 448847, 448849, 448852, 448856, 1734028, 448861, 448864, 1734088, 1734090, 1734117, 1734171, 1734196, 448892, 1734206, 1734208, 1734209, 1603139, 1603142, 1734218, 1734224, 1603159, 1603169, 1603170, 1734291, 1603236, 1603266, 1734340, 448920, 1603271, 685786, 1734363, 1341158, 448931, 1341182, 1603335, 1734438, 1734446, 685879, 1341242, 1341244, 448944, 685889, 1734473, 448947, 1734479, 1603412, 1341269, 685909, 685920, 685921, 685923, 1603441, 448955, 685942, 685950, 685965, 1734545, 685986, 685988, 1734575, 686000, 686002, 686004, 1341378, 686022, 1603537, 1734638, 1734642, 448982, 1603596, 1734674, 1341467, 1603615, 1603623, 1603625, 448991, 1734700, 448994, 1341503, 1341504, 1603678, 1734763, 1734765, 449007, 1734784, 1603728, 449013, 449018, 1734837, 1341623, 1603777, 1734870, 1734878, 1341673, 1734893, 1734903, 1734906, 449033, 1603839, 1734915, 449036, 1734928, 1734930, 449038, 1603874, 1341753, 1341763, 1341767, 1603914, 1341775, 1734998, 1360858, 1341794, 1341800, 449057, 1735048, 1735052, 1735068, 1735069, 1604001, 1735077, 1735085, 1604044, 1604061, 1604078, 1341947, 1604113, 1735190, 1735211, 1604164, 449103, 1735268, 1735280, 1735293, 1342079, 1604230, 449112, 1735334, 449119, 1735369, 1735375, 1735377, 449128, 1735394, 1604328, 449131, 1342195, 1735418, 1604361, 1735444, 1735453, 1735480, 1735484, 1360953, 1735502, 1735516, 1735533, 1735534, 1106879, 1604485, 1604487, 1735559, 1735585, 1735594, 449171, 1735606, 1604542, 1342400, 1342405, 1604553, 1342412, 1604567, 1735646, 1735653, 1604583, 449183, 1342455, 1735687, 1342472, 1735711, 449195, 1342557, 1735784, 1361016, 1080445, 1735808, 454547, 1735824, 449216, 449217, 1735841, 1735843, 1604779, 1080498, 1735862, 1735867, 1342659, 1735876, 449227, 1735889, 1604819, 1735893, 1080548, 1735919, 1735933, 1604874, 1735951, 1735954, 1736001, 1736007, 1736010, 1736013, 1736032, 1736053, 1604989, 687504, 687511, 1605032, 1605038, 1605059, 687567, 1342937, 1361088, 687616, 1605134, 687631, 449293, 1605147, 1343025, 1605179, 1605183, 687685, 687694, 687711, 687726, 449310, 1605245, 687745, 1605261, 449318, 1343140, 1626844, 687792, 1343165, 1343176, 1361133, 687826, 1343188, 449336, 1605396, 1605404, 1605426, 1605428, 1343285, 687929, 1605438, 1343313, 1605459, 1605460, 687967, 687976, 687984, 449367, 1343379, 1343426, 1605598, 454583, 449399, 1474621, 1361209, 449411, 1605767, 1343662, 449425, 1605814, 1343702, 1343719, 1605902, 1343783, 1605941, 1343800, 1361264, 1343877, 449470, 449483, 1606133, 1343990, 1344038, 1606212, 449509, 1344114, 1606262, 1344120, 1606286, 1344156, 1344163, 449526, 1606320, 449534, 1606360, 1344239, 1606425, 1606438, 1344294, 1344300, 1606446, 1606455, 1100845, 449556, 449561, 1606534, 1344410, 1344415, 1344456, 449583, 1606613, 449586, 1361393, 1344520, 449595, 449596, 1344532, 1606679, 449599, 449603, 449605, 1606756, 1606759, 1344616, 1606770, 1344673, 1344686, 449628, 1344694, 1606849, 1606876, 1344761, 1606912, 449649, 1606938, 1606939, 1606943, 1606953, 1344814, 1606964, 1231202, 1344855, 449667, 1607033, 1607036, 449672, 1607056, 1607060, 449674, 1607067, 1213854, 1344940, 449679, 1213967, 449698, 1345056, 1345094, 1345105, 1345114, 449718, 1345143, 970756, 1345174, 1345182, 1361533, 454650, 1083080, 454653, 1345281, 449747, 1345300, 1345388, 1345392, 449769, 449789, 690171, 690174, 690176, 970829, 1345583, 690284, 690377, 690407, 454674, 690467, 449877, 1345963, 449882, 1345967, 1346004, 1346022, 1346030, 449895, 1346050, 1346079, 449905, 1346102, 1346124, 1346129, 1346131, 1346134, 454688, 1739369, 1346161, 1739393, 1346185, 1739410, 1346197, 449929, 455646, 1346223, 449939, 1346276, 454695, 1739565, 1739582, 1739599, 1739633, 1739642, 1739647, 1739661, 691090, 1346458, 1739684, 691108, 1215442, 1215443, 1215444, 1215445, 1739734, 1215447, 1215448, 1215446, 691180, 1608696, 691204, 971035, 1739794, 1608739, 1608745, 1739820, 450012, 691263, 1346629, 1346644, 1739860, 691285, 1215580, 1215585, 1215587, 691307, 1215597, 1739895, 1739896, 1608823, 450027, 691341, 1608865, 691375, 691390, 450041, 1740000, 1740003, 1346794, 1346803, 1608953, 1740047, 1740064, 1608999, 1740076, 450061, 1346876, 1478006, 450081, 454721, 1609152, 450090, 1347019, 1347041, 450099, 1347069, 1347087, 1609256, 1347132, 1609276, 1609282, 1216075, 450122, 450123, 691842, 691862, 1216154, 691869, 1347231, 691882, 1609390, 1085105, 1216236, 1609456, 1347327, 1609471, 1347347, 1347350, 1085230, 1622486, 1347393, 1609560, 1085297, 1347465, 1609616, 1609624, 1609627, 1347486, 1347489, 1216443, 1347517, 1347528, 1347546, 450202, 450206, 1347633, 1347661, 450222, 1347690, 450228, 1231783, 450239, 1347811, 450250, 450254, 1216770, 1347843, 1347885, 1347904, 1347913, 1347937, 450275, 450276, 1347968, 450280, 1347986, 1347987, 1348027, 1348042, 450314, 450315, 1348195, 450331, 1348237, 1217191, 1348287, 1348293, 1348295, 450348, 450354, 1757304, 1348357, 1362166, 1348449, 1217416, 450393, 454784, 450403, 1348600, 1217553, 450411, 1348664, 1348673, 971452, 450425, 1348698, 1217631, 1348718, 450458, 454796, 971497, 1348956, 450478, 1348966, 450480, 1217934, 1232037, 450493, 1349067, 450516, 450518, 450526, 1218150, 450548, 450549, 1218301, 1218302, 450560, 450567, 454818, 450571, 1218377, 450584, 450589, 1349543, 450593, 1218500, 450601, 1349593, 450609, 1349656, 1349660, 1218596, 1349691, 450623, 1232171, 1349717, 1349753, 454835, 1349855, 454838, 450677, 450678, 455676, 1350019, 1218955, 1350041, 1350096, 1219037, 1219049, 1350121, 1350180, 1350190, 1350196, 1350203, 1350222, 450730, 563814, 1350251, 1350279, 1219213, 563871, 563894, 1350337, 450751, 563922, 1350355, 563931, 563939, 563948, 563983, 563989, 1350430, 564008, 564012, 564066, 454861, 450784, 450785, 564078, 564092, 1350528, 1350529, 564101, 564104, 1350544, 1623115, 1350559, 1219494, 1219519, 564163, 564176, 564182, 564189, 564194, 564201, 564206, 564209, 455681, 564219, 1350656, 564231, 564253, 1350689, 1219640, 1219652, 564294, 564314, 1350751, 564322, 564329, 1219695, 564341, 1219703, 454872, 564393, 564414, 1350852, 564433, 1350875, 1219818, 1350894, 564464, 564478, 564479, 1219853, 564494, 450869, 1350942, 1613103, 450875, 564548, 564553, 1350989, 564558, 564576, 1351014, 564583, 450890, 564606, 564619, 450895, 564652, 454885, 1613254, 450905, 1351113, 450912, 1613297, 1613299, 1220096, 454888, 564762, 1351209, 1613372, 564812, 1220196, 1613423, 1220230, 1613469, 564917, 564930, 1232505, 564949, 1613526, 450960, 564962, 1351407, 1220343, 564988, 1613583, 565023, 565031, 1351466, 565034, 450976, 565042, 1351476, 1351493, 565070, 565081, 565085, 1220449, 454902, 1613692, 1613703, 1220508, 454905, 1613751, 1351638, 1613788, 1623332, 1351703, 1613862, 451026, 1351730, 451032, 1613920, 1351808, 1613984, 1613989, 565449, 565451, 1614042, 1351901, 1614045, 565473, 454917, 1614080, 1614081, 1614095, 565523, 565543, 565555, 1614134, 565570, 1220934, 565589, 451091, 565631, 565643, 565649, 1614238, 565680, 1614257, 451105, 451110, 455693, 1221081, 454928, 1221128, 565769, 565778, 451125, 565793, 565825, 565835, 565837, 565848, 1614428, 451138, 1614444, 451141, 565876, 565883, 1745538, 1614472, 451148, 565913, 1745564, 1745582, 1745619, 1221349, 1614569, 1623489, 1221393, 1614618, 1614649, 1614650, 1352514, 451191, 1745786, 1352590, 1745820, 451202, 1614760, 451204, 1221549, 1352627, 1745845, 1352633, 1614781, 1745853, 1614794, 1745871, 1614800, 1352664, 1614821, 1745912, 451220, 1745941, 1221696, 1352771, 1745997, 1221710, 1221733, 1352841, 1352844, 1746075, 1746076, 1221792, 1352889, 454956, 1746139, 1746140, 451265, 1746172, 451276, 1353000, 1221947, 1353033, 1232834, 1221969, 451291, 1222015, 1746314, 1353129, 1353142, 1353145, 451311, 1746391, 451317, 451323, 1746436, 451325, 1353227, 1746469, 1746476, 1353270, 1746494, 451338, 1757501, 1222266, 454974, 1353347, 1353362, 1746581, 1353369, 1746587, 451364, 1746643, 1353443, 1746668, 1353477, 1746714, 1222440, 451383, 1222447, 1353533, 1746755, 454983, 1353574, 960359, 451395, 1746795, 1353586, 1353601, 1353602, 451402, 451403, 960430, 451414, 1746897, 451417, 960474, 960480, 451419, 1746915, 451421, 1222651, 960511, 960544, 1747045, 451446, 451448, 451450, 1747080, 451453, 451455, 1222847, 454997, 451466, 1747167, 1747195, 451475, 451479, 1747217, 1747226, 451482, 1747232, 1623809, 1747263, 1747274, 1223002, 1747299, 1747319, 1747322, 451503, 451505, 451506, 1354147]]
Could this be a symptom of https://github.com/element-hq/synapse/security/advisories/GHSA-3h7q-rfh9-xm4v ?
EDIT: it does not seem so, I just updated to v1.111 and the issue persists.
As it seems the problem came from intensive recursive SQL queries, I tried to issue a reindex (verbose) database synapse; (07/23 13h) after the Synapse upgrade to v1.111, to make sure the indices were as optimized as possible, but it did not solve anything. Quite the opposite:
The get_auth_chain_difference_chains increase is probably due to the Synapse upgrade from v1.103 to v1.111, as it happened the 22 at 22:12:08.
EDIT: I just saw that this particular issue is tracked in https://github.com/element-hq/synapse/issues/17129
As my best clue was the logs shown in https://github.com/element-hq/synapse/issues/17470#issuecomment-2243726995, I decided to find what rooms were affected. So I used the following query where 444066 can be replaced by any of the value returned by the long query :
SELECT room_id
FROM event_auth_chains AS chains
INNER JOIN events ON (chains.event_id = events.event_id)
WHERE chain_id = 444066;
In my case, I had multiple long queries all linking to !lAoVmVifHHtoeOAmHO:grapheneos.org, so I decided to delete is using the admin API. And it seems everything went back to normal.
EDIT: hmm... not really, there are a lot of other rooms that show this behaviour EDIT2: after deleting the few rooms that showed up in long queries it seems the situation is now under control, but I am still waiting for a large room to be purged.
I still have this issue, here is the query / plan
https://explain.dalibo.com/plan/271d35b19fe84g32#plan
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Merge Join (cost=575263.44..695224.02 rows=802392 width=32) |
| Merge Cond: (event_auth_chain_links.origin_chain_id = links.chain_id) |
| CTE links |
| -> Recursive Union (cost=0.43..524411.88 rows=401196 width=8) |
| -> Unique (cost=0.43..4029.96 rows=1996 width=8) |
| -> Index Only Scan using event_auth_chain_links_origin_index on event_auth_chain_links event_auth_chain_links_1 (cost=0.43..4024.97 rows=1997 width=8) |
| Index Cond: (origin_chain_id = ANY ('{252142,252143,317683,252144,252145,252146,252147,252148,252149,252150,252151,252152,252153,252154,252155,252156,252157,317698,252158,252159,252160,252161,252162,317704,252163,252164,252165,317708,252166,317710,252167,317712,252168,252169,252170,252171,317717,252173,252174,252175,252176,252177,252178,252179,317725,317726,317727,252183,252184,252185,252186,252187,252188,252189,252190,252191,252192,252193,317739,252195,252196,252197,252198,252199,252200,252201,252202,252203,252204,317750,317751,252207,252208,317754,252210,252211,252212,252213,252214,252215,252216,252217,252218,252219,252220,252221,252222,252223,317769,317770,317777,317792,317797,317801,317804,317809,317821,317827,317833,317843,317849,317854,252324,252325,252326,252327,252328,252329,252330,252331,252332,252333,252334,252335,252336,252337,252338,252339,252340,252341,317878,317879,252342,252343,317882,317883,252344,317885,252345,252346,317888,252347,252348,317891,252349,317893,252350,252351,252352,252353,317898,252354,252355,252356,252357,252358,252359,317905,252361,252362,252363,252364,252365,317911,252367,252368,252369,252370,252371,252372,252373,252374,317920,252376,252377,252378,252379,252380,317926,252382,317928,317929,252385,252386,252387,252388,252389,252390,252391,252392,252393,252394,252395,252396,317942,252398,252399,252400,252401,252402,252403,252404,252405,317951,252407,252408,252409,252410,252411,252412,252413,252414,252415,252416,252417,317963,252419,252420,252421,252422,252423,317972,317977,317978,317981,317986,317991,317992,317993,318000,318004,318007,318015,318026,318039,318045,318052,318053,318071,318092,318109,318114,318125,318139,318142,318143,318145,318146,252611,252612,252613,252614,252615,252616,252617,252618,252619,252620,252621,252622,252623,252624,252625,252626,318163,252627,252628,252629,252630,252631,252632,252633,252634,252635,252636,252637,252638,252639,318177,252640,252641,252642,318181,252643,252644,252645,252646,252647,252648,252649,252650,318190,252651,252652,318193,252653,252654,252655,252656,252657,252658,252659,252660,252661,252662,252663,252664,252665,252666,252667,252668,252669,252670,252671,252672,252673,252674,252675,252676,252677,252678,252679,252680,252681,252682,252683,252684,252685,252686,252687,252688,252689,252690,252691,252692,252693,252694,252695,252696,252697,252698,252699,252700,252701,252702,252703,252704,252705,252706,252707,252708,252709,252710,318262,318269,318272,318278,318280,318287,318300,318311,318319,318328,318335,318336,318337,318346,252811,252812,252813,252814,252815,318352,252816,252817,252818,252819,252820,252821,252822,318360,252823,252824,252825,252826,252827,252828,252829,252830,318369,252831,252832,252833,252834,252835,252836,252837,252838,252839,252840,252841,318381,252842,252843,318384,252844,318386,252845,318388,252846,252847,318391,252848,252849,318394,252850,252851,252852,252853,252854,252855,252856,252857,252858,318404,252860,318406,252862,318408,318409,252865,252866,318412,252868,252869,252870,252871,252872,252873,252874,252875,252876,252877,252878,252879,318425,252881,252882,318428,252884,252885,252886,252887,252888,318434,252890,252891,252892,252893,252894,252895,252896,252897,252898,252899,252900,252901,252902,252903,318449,252905,252906,252907,252908,318454,252910,318456,318459,318463,318467,318487,318493,318494,318496,318498,318509,318514,318515,318539,318541,318552,318553,318559,318578,318580,318588,318593,318594,318608,318615,318619,318631,253111,253112,253113,253114,253115,253116,253117,253118,253119,253120,253121,253122,253123,253124,318661,318662,253125,318664,253126,318666,318667,318668,253127,253128,318671,253129,253130,253131,318675,253132,253133,253134,253135,318680,318681,318682,253138,253139,253140,318686,253142,253143,253144,253145,253146,253147,253148,253149,253150,253151,253152,253153,253154,253155,253156,253157,253158,253159,253160,253161,253162,318708,253164,318710,253166,253167,253168,253169,253170,253171,253172,253173,253174,318720,253176,253177,253178,253179,253180,253181,318727,318728,253184,253185,253186,253187,253188,253189,253190,253191,253192,253193,253194,253195,253196,253197,318743,318744,253200,253201,253202,253203,253204,253205,253206,253207,253208,253209,253210,318768,318813,318818,318821,318823,318827,318829,318831,318842,318844,253311,253312,253313,253314,318851,253315,318853,253316,253317,253318,253319,253320,253321,253322,253323,253324,253325,253326,253327,253328,253329,253330,253331,318870,318871,253332,253333,253334,318875,253335,318877,318878,253336,253337,253338,253339,253340,318884,253341,253342,253343,253344,253345,253346,253347,318892,253348,253349,253350,253351,253352,318898,253354,253355,253356,253357,253358,253359,318905,253361,253362,253363,253364,253365,253366,253367,253368,253369,253370,253371,253372,253373,253374,253375,253376,253377,253378,253379,253380,318926,253382,253383,253384,253385,253386,253387,253388,253389,318935,253391,253392,318938,253394,253395,253396,253397,253398,253399,253400,253401,253402,253403,253404,318950,253406,253407,253408,253409,253410,318956,318968,318971,318972,318973,318974,318975,318977,318983,318989,318990,318997,319008,319012,319024,319034,319035,319038,253511,319048,253512,319050,253513,253514,319053,253515,253516,253517,253518,253519,253520,253521,253522,253523,319063,253524,319065,253525,253526,253527,253528,253529,253530,253531,253532,319074,253533,253534,319077,319078,253535,253536,253537,319082,253538,253539,253540,319086,253542,253543,253544,253545,319091,253547,253548,253549,253550,253551,253552,253553,253554,253555,253556,319102,253558,253559,253560,253561,253562,319108,253564,253565,253566,253567,253568,253569,253570,253571,253572,253573,253574,253575,253576,253577,253578,253579,253580,253581,253582,253583,253584,253585,253586,253587,253588,253589,253590,253591,253592,253593,253594,253595,253596,253597,319143,253599,253600,253601,319147,253603,319149,253605,253606,253607,253608,253609,253610,319166,319173,319178,319182,319184,319231,319239,319240,319245,253711,253712,253713,319250,253714,253715,253716,253717,253718,253719,253720,253721,253722,253723,253724,253725,253726,253727,253728,253729,253730,253731,319269,253732,253733,253734,253735,253736,319275,253737,253738,253739,319279,253740,253741,253742,253743,319285,253744,253745,253746,253747,253748,253749,319292,253750,253751,253752,253753,253754,253755,253756,253757,253758,253759,319303,253760,253761,253762,253763,253764,253765,253766,319311,253767,253768,253769,253770,253771,253772,253773,253774,253775,253776,253777,253778,253779,253780,319326,319327,253783,253784,253785,253786,253787,253788,319334,253790,253791,253792,253793,253794,253795,319341,319342,253798,253799,253800,253801,253802,319348,253804,253805,319351,253807,319353,253809,253810,319356,319357,319361,319364,319370,319374,319379,319384,319396,319397,319406,319408,319411,319419,319421,319428,319435,319438,319440,319441,319450,319460,319461,319462,319463,319472,319482,319483,319490,319499,319539,319547,254011,254012,254013,254014}'::integer[])) |
| -> Nested Loop (cost=0.43..51235.80 rows=39920 width=8) |
| -> WorkTable Scan on links links_1 (cost=0.00..399.20 rows=19960 width=8) |
| -> Index Only Scan using event_auth_chain_links_idx on event_auth_chain_links event_auth_chain_links_2 (cost=0.43..2.53 rows=2 width=16) |
| Index Cond: (origin_chain_id = links_1.chain_id) |
| -> Index Scan using event_auth_chain_links_origin_index on event_auth_chain_links (cost=0.43..100443.18 rows=2591584 width=32) |
| -> Materialize (cost=50851.13..52857.11 rows=401196 width=8) |
| -> Sort (cost=50851.13..51854.12 rows=401196 width=8) |
| Sort Key: links.chain_id |
| -> CTE Scan on links (cost=0.00..8023.92 rows=401196 width=8) |
| JIT: |
| Functions: 14 |
| Options: Inlining true, Optimization true, Expressions true, Deforming true
explain WITH RECURSIVE links(chain_id) AS ( SELECT DISTINCT origin_chain_id FROM event_auth_chain_links WHERE origin_chain_id = ANY(ARRAY[252142,252143,317683,252144,252145,252146,252147,252148,252149,252150,252151,252152,252153,252154,252155,252156,252157,317698,252158,252159,252160,252161,252162,317704,252163,252164,252165,317708,252166,317710,252167,317712,252168,252169,252170,252171,317717,252173,252174,252175,252176,252177,252178,252179,317725,317726,317727,252183,252184,252185,252186,252187,252188,252189,252190,252191,252192,252193,317739,252195,252196,252197,252198,252199,252200,252201,252202,252203,252204,317750,317751,252207,252208,317754,252210,252211,252212,252213,252214,252215,252216,252217,252218,252219,252220,252221,252222,252223,317769,317770,317777,317792,317797,317801,317804,317809,317821,317827,317833,317843,317849,317854,252324,252325,252326,252327,252328,252329,252330,252331,252332,252333,252334,252335,252336,252337,252338,252339,252340,252341,317878,317879,252342,252343,317882,317883,252344,317885,252345,252346,317888,252347,252348,317891,252349,317893,252350,252351,252352,252353,317898,252354,252355,252356,252357,252358,252359,317905,252361,252362,252363,252364,252365,317911,252367,252368,252369,252370,252371,252372,252373,252374,317920,252376,252377,252378,252379,252380,317926,252382,317928,317929,252385,252386,252387,252388,252389,252390,252391,252392,252393,252394,252395,252396,317942,252398,252399,252400,252401,252402,252403,252404,252405,317951,252407,252408,252409,252410,252411,252412,252413,252414,252415,252416,252417,317963,252419,252420,252421,252422,252423,317972,317977,317978,317981,317986,317991,317992,317993,318000,318004,318007,318015,318026,318039,318045,318052,318053,318071,318092,318109,318114,318125,318139,318142,318143,318145,318146,252611,252612,252613,252614,252615,252616,252617,252618,252619,252620,252621,252622,252623,252624,252625,252626,318163,252627,252628,252629,252630,252631,252632,252633,252634,252635,252636,252637,252638,252639,318177,252640,252641,252642,318181,252643,252644,252645,252646,252647,252648,252649,252650,318190,252651,252652,318193,252653,252654,252655,252656,252657,252658,252659,252660,252661,252662,252663,252664,252665,252666,252667,252668,252669,252670,252671,252672,252673,252674,252675,252676,252677,252678,252679,252680,252681,252682,252683,252684,252685,252686,252687,252688,252689,252690,252691,252692,252693,252694,252695,252696,252697,252698,252699,252700,252701,252702,252703,252704,252705,252706,252707,252708,252709,252710,318262,318269,318272,318278,318280,318287,318300,318311,318319,318328,318335,318336,318337,318346,252811,252812,252813,252814,252815,318352,252816,252817,252818,252819,252820,252821,252822,318360,252823,252824,252825,252826,252827,252828,252829,252830,318369,252831,252832,252833,252834,252835,252836,252837,252838,252839,252840,252841,318381,252842,252843,318384,252844,318386,252845,318388,252846,252847,318391,252848,252849,318394,252850,252851,252852,252853,252854,252855,252856,252857,252858,318404,252860,318406,252862,318408,318409,252865,252866,318412,252868,252869,252870,252871,252872,252873,252874,252875,252876,252877,252878,252879,318425,252881,252882,318428,252884,252885,252886,252887,252888,318434,252890,252891,252892,252893,252894,252895,252896,252897,252898,252899,252900,252901,252902,252903,318449,252905,252906,252907,252908,318454,252910,318456,318459,318463,318467,318487,318493,318494,318496,318498,318509,318514,318515,318539,318541,318552,318553,318559,318578,318580,318588,318593,318594,318608,318615,318619,318631,253111,253112,253113,253114,253115,253116,253117,253118,253119,253120,253121,253122,253123,253124,318661,318662,253125,318664,253126,318666,318667,318668,253127,253128,318671,253129,253130,253131,318675,253132,253133,253134,253135,318680,318681,318682,253138,253139,253140,318686,253142,253143,253144,253145,253146,253147,253148,253149,253150,253151,253152,253153,253154,253155,253156,253157,253158,253159,253160,253161,253162,318708,253164,318710,253166,253167,253168,253169,253170,253171,253172,253173,253174,318720,253176,253177,253178,253179,253180,253181,318727,318728,253184,253185,253186,253187,253188,253189,253190,253191,253192,253193,253194,253195,253196,253197,318743,318744,253200,253201,253202,253203,253204,253205,253206,253207,253208,253209,253210,318768,318813,318818,318821,318823,318827,318829,318831,318842,318844,253311,253312,253313,253314,318851,253315,318853,253316,253317,253318,253319,253320,253321,253322,253323,253324,253325,253326,253327,253328,253329,253330,253331,318870,318871,253332,253333,253334,318875,253335,318877,318878,253336,253337,253338,253339,253340,318884,253341,253342,253343,253344,253345,253346,253347,318892,253348,253349,253350,253351,253352,318898,253354,253355,253356,253357,253358,253359,318905,253361,253362,253363,253364,253365,253366,253367,253368,253369,253370,253371,253372,253373,253374,253375,253376,253377,253378,253379,253380,318926,253382,253383,253384,253385,253386,253387,253388,253389,318935,253391,253392,318938,253394,253395,253396,253397,253398,253399,253400,253401,253402,253403,253404,318950,253406,253407,253408,253409,253410,318956,318968,318971,318972,318973,318974,318975,318977,318983,318989,318990,318997,319008,319012,319024,319034,319035,319038,253511,319048,253512,319050,253513,253514,319053,253515,253516,253517,253518,253519,253520,253521,253522,253523,319063,253524,319065,253525,253526,253527,253528,253529,253530,253531,253532,319074,253533,253534,319077,319078,253535,253536,253537,319082,253538,253539,253540,319086,253542,253543,253544,253545,319091,253547,253548,253549,253550,253551,253552,253553,253554,253555,253556,319102,253558,253559,253560,253561,253562,319108,253564,253565,253566,253567,253568,253569,253570,253571,253572,253573,253574,253575,253576,253577,253578,253579,253580,253581,253582,253583,253584,253585,253586,253587,253588,253589,253590,253591,253592,253593,253594,253595,253596,253597,319143,253599,253600,253601,319147,253603,319149,253605,253606,253607,253608,253609,253610,319166,319173,319178,319182,319184,319231,319239,319240,319245,253711,253712,253713,319250,253714,253715,253716,253717,253718,253719,253720,253721,253722,253723,253724,253725,253726,253727,253728,253729,253730,253731,319269,253732,253733,253734,253735,253736,319275,253737,253738,253739,319279,253740,253741,253742,253743,319285,253744,253745,253746,253747,253748,253749,319292,253750,253751,253752,253753,253754,253755,253756,253757,253758,253759,319303,253760,253761,253762,253763,253764,253765,253766,319311,253767,253768,253769,253770,253771,253772,253773,253774,253775,253776,253777,253778,253779,253780,319326,319327,253783,253784,253785,253786,253787,253788,319334,253790,253791,253792,253793,253794,253795,319341,319342,253798,253799,253800,253801,253802,319348,253804,253805,319351,253807,319353,253809,253810,319356,319357,319361,319364,319370,319374,319379,319384,319396,319397,319406,319408,319411,319419,319421,319428,319435,319438,319440,319441,319450,319460,319461,319462,319463,319472,319482,319483,319490,319499,319539,319547,254011,254012,254013,254014]) UNION SELECT target_chain_id FROM event_auth_chain_links INNER JOIN links ON (chain_id = origin_chain_id))SELECT origin_chain_id, origin_sequence_number,target_chain_id, target_sequence_number FROM links INNER JOIN event_auth_chain_links ON (chain_id = origin_chain_id);
I'm running 1.120.2 and randomly get the same issue. It just happened to me. @parisni Is it a coincidence that you just commented on this issue less than 20 minutes ago, or did you also experience this at the same time as I did ? (See the graph below, time is UTC+1)
It seems the RAM usage of my postgres container started to rise shorlty before the CPU did.
a coincidence that you just commented on this issue less than 20 minutes ago, or did you also experience this at the same time as I did
We definitely had the same issue at the same time. Not sure if it's independant. I have no idea what kind of action triggers this recursive query.
On December 10, 2024 5:06:01 PM GMT+01:00, Pierre Couy @.***> wrote:
I'm running 1.120.2 and randomly get the same issue. It just happened to me. @parisni Is it a coincidence that you just commented on this issue less than 20 minutes ago, or did you also experience this at the same time as I did ?
It seems the RAM usage of my postgres container started to rise shorlty before the CPU did.
We definitely had the same issue at the same time. Not sure if it's independant. I have no idea what kind of action triggers this recursive query.
This is a strong indicator that it was triggered by a cause external to our servers. I checked my synapse log but could not find any particularly weird line in all the noise. I'm not sure if this is caused by a single server acting funny (either old, misconfigured or malicious) or if the federation kind of collectively caused it.
Since we both had it happen at the same time, I would be very interested to hear from people to whom it did NOT happen at the same time. There is probably something to learn about what triggers it by understanding the differences between impacted and not-impacted servers.
(maybe we should create a room for server admins dedicated to tracking and sharing when this happens)
We are pretty sure, were able to trigger this behavior by letting Mjölnir change the room server ACLs (we always added a couple of them at a time and other than that, there wasn't going on anything really on the homeserver). After restarting synapse, CPU usage of Postgres drops again to normal until changing the ACLs once more.
pg_stat_activity Example Query
psql (17.4)
Type "help" for help.
synapse=# \x
synapse=# SELECT * FROM pg_stat_activity WHERE datname = 'synapse' AND state = 'active' AND usename = 'synapse'; -- might need a couple of tries
-[ RECORD 1 ]----+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
datid | 16386
datname | synapse
pid | 83559
leader_pid |
usesysid | 16385
usename | synapse
application_name |
client_addr | 172.29.0.5
client_hostname |
client_port | 37726
backend_start | 2025-05-15 10:59:35.568225+00
xact_start | 2025-05-19 12:02:43.574523+00
query_start | 2025-05-19 12:02:44.991597+00
state_change | 2025-05-19 12:02:44.991604+00
wait_event_type | IO
wait_event | BuffileWrite
state | active
backend_xid |
backend_xmin | 24350111
query_id |
query | +
| WITH RECURSIVE links(chain_id) AS ( +
| SELECT +
| DISTINCT origin_chain_id +
| FROM event_auth_chain_links WHERE origin_chain_id = ANY(ARRAY[262144,262145,262146,262147,262148,262149,262150,262151,262152,262153,262154,262155,262156,262157,262158,262159,262160,262161,262162,262163,262164,262165,262166,262167,262168,262169,262170,262171,262172,262173,262174,262175,262176,262177,262178,262179,262180,262181,262182,262183,262184,262185,262186,262187,262188,262189,262190,262191,262192,262193,262194,262195,262196,262197,262198,262199,262200,262201,262202,262203,262204,262205,262206,262207,262208,262209,262210,262211,262212,262213,262214,262215,262216,262217,262218,262219,262220,262221,262222,262223,262224,262225,262226,262227,262228,262229,262230,262231,262232,262233,262234,262235,262236,262237,262238,262239,262240,262241,262242,262243,262244,262245,262246,262247,262248,262249,262250,262251,262252,262253,262254,262255,262256,262257,262258,262259,262260,262261,26
backend_type | client backend
Synapse Dashboard - Database Row
(UTC+02:00)
Database Overview Rows
(UTC+02:00)
Additional Information
We were able to trigger this three times now on three different days. The messages mjolnir ban domain.tld foo (just using: msgtype and body) were created, each with a delay of 7 seconds, by a little bash script I wrote years ago. The number of ACL's being added ranges from 20-128 in a row. None of the servers added to the ACLs were (at the time) part of the protected rooms at the time. The number of current room members of the rooms the ACLs were changed ranges from 200 to 20000.
Protected rooms
- Python:
!iuyQXswfjgxQMZGrfQ:matrix.org - Community (Space):
!JnlWrgspTvEytPctFM:matrix.org - Offtopic:
!CGjuXMiLwtPOTXYexo:matrix.org - Meta:
!PyPOqShkhhfncUgWjN:matrix.org
Policy list room over time
(times in UTC+02:00; room:
!HhKjLjojOKSiiMFLkI:michaelsasser.org)
Synapse v1.129.0 Mjolnir v1.10.0 (and the version right before that)
