Skip to content
This repository has been archived by the owner on Apr 26, 2024. It is now read-only.

Sync query taking upwards of 30 minutes #7772

Closed
hellcp opened this issue Jul 1, 2020 · 21 comments
Closed

Sync query taking upwards of 30 minutes #7772

hellcp opened this issue Jul 1, 2020 · 21 comments
Labels
A-Performance Performance, both client-facing and admin-facing

Comments

@hellcp
Copy link

hellcp commented Jul 1, 2020

Queries similar to the following (sent to postgres as a result of /_matrix/client/r0/sync) take a very long time to complete.

WITH RECURSIVE state(state_group) AS ( VALUES(33031::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC

The server is tuned, and works fine otherwise. I tried using state compression tool, but it did not improve the situation. The database isn't that big either, taking up around 800MB according to \l+
@richvdh
Copy link
Member

richvdh commented Jul 1, 2020

seems related to #5064/#7618. Could you look through those issues and see if you find any clues?

@babolivier babolivier added info-needed A-Performance Performance, both client-facing and admin-facing labels Jul 2, 2020
@hellcp
Copy link
Author

hellcp commented Jul 4, 2020

For some reason I didn't get an email about your response... Before submitting this issue I had a look at both, and they didn't really help, I tried setting higher statistics for pretty much everything mentioned in the query, I tried to see if there is anything extraordinary in EXPLAIN, but it doesn't seem like it.

matrix=# EXPLAIN WITH RECURSIVE state(state_group) AS ( VALUES(33031::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC ;
                                                         QUERY PLAN                                                         
----------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=74388.84..78867.42 rows=119429 width=88)
   CTE state
     ->  Recursive Union  (cost=0.00..523.68 rows=371 width=8)
           ->  Result  (cost=0.00..0.01 rows=1 width=8)
           ->  Nested Loop  (cost=0.42..51.63 rows=37 width=8)
                 ->  WorkTable Scan on state s  (cost=0.00..0.20 rows=10 width=8)
                 ->  Index Scan using state_group_edges_idx on state_group_edges e  (cost=0.42..5.10 rows=4 width=16)
                       Index Cond: (state_group = s.state_group)
   ->  Sort  (cost=73865.15..75358.01 rows=597144 width=88)
         Sort Key: state_groups_state.type, state_groups_state.state_key, state_groups_state.state_group DESC
         ->  Nested Loop  (cost=8.90..16575.99 rows=597144 width=88)
               ->  HashAggregate  (cost=8.35..10.35 rows=200 width=8)
                     Group Key: state.state_group
                     ->  CTE Scan on state  (cost=0.00..7.42 rows=371 width=8)
               ->  Index Scan using state_groups_state_type_idx on state_groups_state  (cost=0.55..78.79 rows=404 width=88)
                     Index Cond: (state_group = state.state_group)
(16 rows)

I had a look at statistics again, and it seems some of those queries NEVER finish, some of them have been hanging around in postgres for days at this point (basically since the last restart of postgres).

@hellcp
Copy link
Author

hellcp commented Jul 9, 2020

That issue causes most, if not all of the sync requests from client api to fail, so the homeserver is very much unusable in its current form.

@richvdh
Copy link
Member

richvdh commented Jul 9, 2020

my first question is how exactly you have established that that particular query is slow. if it's from postgres queries, please paste the results you see.

@hellcp
Copy link
Author

hellcp commented Jul 9, 2020

I run:

SELECT S.pid, age(clock_timestamp(), query_start), usename, query, L.mode, L.locktype, L.granted FROM pg_stat_activity S inner join pg_locks L on S.pid = L.pid  order by L.granted, L.pid DESC;

which results in:

  pid  |       age       | usename  |                                                                                                                                                                              query                                                                                                                                                                             |           mode           |   locktype    | granted 
-------+-----------------+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------+---------------+---------
  1094 | 00:16:34.708649 | matrix   | WITH RECURSIVE state(state_group) AS ( VALUES(33029::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC | AccessShareLock          | relation      | t
  1094 | 00:16:34.708651 | matrix   | WITH RECURSIVE state(state_group) AS ( VALUES(33029::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC | AccessShareLock          | relation      | t
  1094 | 00:16:34.708653 | matrix   | WITH RECURSIVE state(state_group) AS ( VALUES(33029::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC | ExclusiveLock            | virtualxid    | t
  1094 | 00:16:34.708663 | matrix   | WITH RECURSIVE state(state_group) AS ( VALUES(33029::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC | AccessShareLock          | relation      | t
  1094 | 00:16:34.708666 | matrix   | WITH RECURSIVE state(state_group) AS ( VALUES(33029::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC | AccessShareLock          | relation      | t
  1094 | 00:16:34.708668 | matrix   | WITH RECURSIVE state(state_group) AS ( VALUES(33029::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC | AccessShareLock          | relation      | t
  1093 | 00:16:34.884071 | matrix   | WITH RECURSIVE state(state_group) AS ( VALUES(33040::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC | AccessShareLock          | relation      | t
  1093 | 00:16:34.884073 | matrix   | WITH RECURSIVE state(state_group) AS ( VALUES(33040::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC | AccessShareLock          | relation      | t
  1093 | 00:16:34.884074 | matrix   | WITH RECURSIVE state(state_group) AS ( VALUES(33040::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC | AccessShareLock          | relation      | t
  1093 | 00:16:34.884076 | matrix   | WITH RECURSIVE state(state_group) AS ( VALUES(33040::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC | AccessShareLock          | relation      | t
  1093 | 00:16:34.884078 | matrix   | WITH RECURSIVE state(state_group) AS ( VALUES(33040::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC | AccessShareLock          | relation      | t
  1093 | 00:16:34.88408  | matrix   | WITH RECURSIVE state(state_group) AS ( VALUES(33040::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC | ExclusiveLock            | virtualxid    | t
  1092 | 00:16:34.779448 | matrix   | WITH RECURSIVE state(state_group) AS ( VALUES(33031::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC | AccessShareLock          | relation      | t
  1092 | 00:16:34.77945  | matrix   | WITH RECURSIVE state(state_group) AS ( VALUES(33031::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC | AccessShareLock          | relation      | t
  1092 | 00:16:34.779452 | matrix   | WITH RECURSIVE state(state_group) AS ( VALUES(33031::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC | ExclusiveLock            | virtualxid    | t
  1092 | 00:16:34.779454 | matrix   | WITH RECURSIVE state(state_group) AS ( VALUES(33031::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC | AccessShareLock          | relation      | t
  1092 | 00:16:34.779456 | matrix   | WITH RECURSIVE state(state_group) AS ( VALUES(33031::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC | AccessShareLock          | relation      | t
  1092 | 00:16:34.779458 | matrix   | WITH RECURSIVE state(state_group) AS ( VALUES(33031::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC | AccessShareLock          | relation      | t
  1090 | 00:16:35.153292 | matrix   | WITH RECURSIVE state(state_group) AS ( VALUES(33026::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC | AccessShareLock          | relation      | t
  1090 | 00:16:35.153294 | matrix   | WITH RECURSIVE state(state_group) AS ( VALUES(33026::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC | ExclusiveLock            | virtualxid    | t
  1090 | 00:16:35.153296 | matrix   | WITH RECURSIVE state(state_group) AS ( VALUES(33026::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC | AccessShareLock          | relation      | t
  1090 | 00:16:35.153298 | matrix   | WITH RECURSIVE state(state_group) AS ( VALUES(33026::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC | AccessShareLock          | relation      | t
  1090 | 00:16:35.153308 | matrix   | WITH RECURSIVE state(state_group) AS ( VALUES(33026::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC | AccessShareLock          | relation      | t
  1090 | 00:16:35.15331  | matrix   | WITH RECURSIVE state(state_group) AS ( VALUES(33026::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC | AccessShareLock          | relation      | t
  1089 | 00:16:34.797513 | matrix   | WITH RECURSIVE state(state_group) AS ( VALUES(33020::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC | AccessShareLock          | relation      | t
  1089 | 00:16:34.797515 | matrix   | WITH RECURSIVE state(state_group) AS ( VALUES(33020::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC | AccessShareLock          | relation      | t
  1089 | 00:16:34.797517 | matrix   | WITH RECURSIVE state(state_group) AS ( VALUES(33020::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC | AccessShareLock          | relation      | t
  1089 | 00:16:34.797519 | matrix   | WITH RECURSIVE state(state_group) AS ( VALUES(33020::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC | AccessShareLock          | relation      | t
  1089 | 00:16:34.797521 | matrix   | WITH RECURSIVE state(state_group) AS ( VALUES(33020::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC | AccessShareLock          | relation      | t
  1089 | 00:16:34.797524 | matrix   | WITH RECURSIVE state(state_group) AS ( VALUES(33020::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC | ExclusiveLock            | virtualxid    | t
  1088 | 00:16:34.662031 | matrix   | WITH RECURSIVE state(state_group) AS ( VALUES(33038::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC | ExclusiveLock            | virtualxid    | t
  1088 | 00:16:34.662034 | matrix   | WITH RECURSIVE state(state_group) AS ( VALUES(33038::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC | AccessShareLock          | relation      | t
  1088 | 00:16:34.662037 | matrix   | WITH RECURSIVE state(state_group) AS ( VALUES(33038::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC | AccessShareLock          | relation      | t
  1088 | 00:16:34.66204  | matrix   | WITH RECURSIVE state(state_group) AS ( VALUES(33038::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC | AccessShareLock          | relation      | t
  1088 | 00:16:34.662043 | matrix   | WITH RECURSIVE state(state_group) AS ( VALUES(33038::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC | AccessShareLock          | relation      | t
  1088 | 00:16:34.662046 | matrix   | WITH RECURSIVE state(state_group) AS ( VALUES(33038::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC | AccessShareLock          | relation      | t
  1087 | 00:16:34.905815 | matrix   | WITH RECURSIVE state(state_group) AS ( VALUES(33015::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC | AccessShareLock          | relation      | t
  1087 | 00:16:34.905818 | matrix   | WITH RECURSIVE state(state_group) AS ( VALUES(33015::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC | AccessShareLock          | relation      | t
  1087 | 00:16:34.90582  | matrix   | WITH RECURSIVE state(state_group) AS ( VALUES(33015::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC | AccessShareLock          | relation      | t
  1087 | 00:16:34.905823 | matrix   | WITH RECURSIVE state(state_group) AS ( VALUES(33015::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC | AccessShareLock          | relation      | t
  1087 | 00:16:34.905832 | matrix   | WITH RECURSIVE state(state_group) AS ( VALUES(33015::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC | ExclusiveLock            | virtualxid    | t
  1087 | 00:16:34.905835 | matrix   | WITH RECURSIVE state(state_group) AS ( VALUES(33015::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC | AccessShareLock          | relation      | t
  1085 | 00:16:34.597712 | matrix   | WITH RECURSIVE state(state_group) AS ( VALUES(33032::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC | AccessShareLock          | relation      | t
  1085 | 00:16:34.597715 | matrix   | WITH RECURSIVE state(state_group) AS ( VALUES(33032::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC | AccessShareLock          | relation      | t
  1085 | 00:16:34.597717 | matrix   | WITH RECURSIVE state(state_group) AS ( VALUES(33032::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC | AccessShareLock          | relation      | t
  1085 | 00:16:34.597719 | matrix   | WITH RECURSIVE state(state_group) AS ( VALUES(33032::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC | AccessShareLock          | relation      | t
  1085 | 00:16:34.597721 | matrix   | WITH RECURSIVE state(state_group) AS ( VALUES(33032::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC | AccessShareLock          | relation      | t
  1085 | 00:16:34.597723 | matrix   | WITH RECURSIVE state(state_group) AS ( VALUES(33032::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC | ExclusiveLock            | virtualxid    | t
  1084 | 00:16:34.856886 | matrix   | WITH RECURSIVE state(state_group) AS ( VALUES(33021::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC | AccessShareLock          | relation      | t
  1084 | 00:16:34.856888 | matrix   | WITH RECURSIVE state(state_group) AS ( VALUES(33021::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC | AccessShareLock          | relation      | t
  1084 | 00:16:34.85689  | matrix   | WITH RECURSIVE state(state_group) AS ( VALUES(33021::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC | ExclusiveLock            | virtualxid    | t
  1084 | 00:16:34.856892 | matrix   | WITH RECURSIVE state(state_group) AS ( VALUES(33021::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC | AccessShareLock          | relation      | t
  1084 | 00:16:34.856894 | matrix   | WITH RECURSIVE state(state_group) AS ( VALUES(33021::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC | AccessShareLock          | relation      | t
  1084 | 00:16:34.856896 | matrix   | WITH RECURSIVE state(state_group) AS ( VALUES(33021::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC | AccessShareLock          | relation      | t
  1083 | 00:16:34.787394 | matrix   | WITH RECURSIVE state(state_group) AS ( VALUES(33032::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC | AccessShareLock          | relation      | t
  1083 | 00:16:34.787397 | matrix   | WITH RECURSIVE state(state_group) AS ( VALUES(33032::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC | AccessShareLock          | relation      | t
  1083 | 00:16:34.787398 | matrix   | WITH RECURSIVE state(state_group) AS ( VALUES(33032::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC | AccessShareLock          | relation      | t
  1083 | 00:16:34.7874   | matrix   | WITH RECURSIVE state(state_group) AS ( VALUES(33032::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC | ExclusiveLock            | virtualxid    | t
  1083 | 00:16:34.787402 | matrix   | WITH RECURSIVE state(state_group) AS ( VALUES(33032::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC | AccessShareLock          | relation      | t
  1083 | 00:16:34.78741  | matrix   | WITH RECURSIVE state(state_group) AS ( VALUES(33032::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC | AccessShareLock          | relation      | t

I restarted pg fairly recently, so they aren't as old as they get

@richvdh
Copy link
Member

richvdh commented Jul 9, 2020

ok; i'm not sure the lock info is helping us much here, and it omits some useful information from the pg_stat_activity table. could you just share the output of:

select * from pg_stat_activity where state != 'idle';

Also, do you think it is limited to a small range of state groups (from the above, it looks like they are all in the range 33015 to 33040)?

@hellcp
Copy link
Author

hellcp commented Jul 9, 2020 

  datid   |   datname    |  pid  | usesysid | usename  | application_name |  client_addr   | client_hostname | client_port |         backend_start         |          xact_start           |          query_start          |         state_change          | wait_event_type |   wait_event   |        state        | backend_xid | backend_xmin |                                                                                                                                                                              query                                                                                                                                                                             |   backend_type    
----------+--------------+-------+----------+----------+------------------+----------------+-----------------+-------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-----------------+----------------+---------------------+-------------+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------
 17119706 | matrix       |  1083 | 14606064 | matrix   |                  | 192.168.47.102 |                 |       32866 | 2020-07-09 13:08:23.933162+00 | 2020-07-09 15:03:11.174432+00 | 2020-07-09 15:03:11.176873+00 | 2020-07-09 15:03:11.176874+00 |                 |                | active              |             |   1076414157 | WITH RECURSIVE state(state_group) AS ( VALUES(33032::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC | client backend
 17119706 | matrix       |  1084 | 14606064 | matrix   |                  | 192.168.47.102 |                 |       32868 | 2020-07-09 13:08:23.940776+00 | 2020-07-09 15:03:11.019251+00 | 2020-07-09 15:03:11.027344+00 | 2020-07-09 15:03:11.027346+00 |                 |                | active              |             |   1076414157 | WITH RECURSIVE state(state_group) AS ( VALUES(33021::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC | client backend
 17119706 | matrix       |  1085 | 14606064 | matrix   |                  | 192.168.47.102 |                 |       32870 | 2020-07-09 13:08:23.962906+00 | 2020-07-09 15:03:11.206928+00 | 2020-07-09 15:03:11.208824+00 | 2020-07-09 15:03:11.208826+00 |                 |                | active              |             |   1076414157 | WITH RECURSIVE state(state_group) AS ( VALUES(33032::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC | client backend
 17119706 | matrix       |  1087 | 14606064 | matrix   |                  | 192.168.47.102 |                 |       32874 | 2020-07-09 13:08:24.032267+00 | 2020-07-09 15:03:11.109529+00 | 2020-07-09 15:03:11.123694+00 | 2020-07-09 15:03:11.123695+00 |                 |                | active              |             |   1076414157 | WITH RECURSIVE state(state_group) AS ( VALUES(33015::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC | client backend
 17119706 | matrix       |  1088 | 14606064 | matrix   |                  | 192.168.47.102 |                 |       32908 | 2020-07-09 13:08:24.363384+00 | 2020-07-09 15:03:11.205454+00 | 2020-07-09 15:03:11.208424+00 | 2020-07-09 15:03:11.208425+00 |                 |                | active              |             |   1076414157 | WITH RECURSIVE state(state_group) AS ( VALUES(33038::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC | client backend
 17119706 | matrix       |  1089 | 14606064 | matrix   |                  | 192.168.47.102 |                 |       32914 | 2020-07-09 13:08:24.438294+00 | 2020-07-09 15:03:11.102769+00 | 2020-07-09 15:03:11.111513+00 | 2020-07-09 15:03:11.111514+00 |                 |                | active              |             |   1076414157 | WITH RECURSIVE state(state_group) AS ( VALUES(33020::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC | client backend
 17119706 | matrix       |  1090 | 14606064 | matrix   |                  | 192.168.47.102 |                 |       32924 | 2020-07-09 13:08:24.496462+00 | 2020-07-09 15:03:11.230489+00 | 2020-07-09 15:03:11.232307+00 | 2020-07-09 15:03:11.232309+00 | LWLock          | buffer_mapping | active              |             |   1076414157 | WITH RECURSIVE state(state_group) AS ( VALUES(33026::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC | client backend
 17119706 | matrix       |  1092 | 14606064 | matrix   |                  | 192.168.47.102 |                 |       33030 | 2020-07-09 13:08:25.471283+00 | 2020-07-09 15:03:11.442261+00 | 2020-07-09 15:03:11.44356+00  | 2020-07-09 15:03:11.443561+00 |                 |                | active              |             |   1076414157 | WITH RECURSIVE state(state_group) AS ( VALUES(33031::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC | client backend
 17119706 | matrix       |  1093 | 14606064 | matrix   |                  | 192.168.47.102 |                 |       33032 | 2020-07-09 13:08:25.483136+00 | 2020-07-09 15:03:11.079521+00 | 2020-07-09 15:03:11.081086+00 | 2020-07-09 15:03:11.081088+00 |                 |                | active              |             |   1076414157 | WITH RECURSIVE state(state_group) AS ( VALUES(33040::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC | client backend
 17119706 | matrix       |  1094 | 14606064 | matrix   |                  | 192.168.47.102 |                 |       33054 | 2020-07-09 13:08:25.746762+00 | 2020-07-09 15:03:11.176523+00 | 2020-07-09 15:03:11.180986+00 | 2020-07-09 15:03:11.180987+00 | LWLock          | buffer_mapping | active              |             |   1076414157 | WITH RECURSIVE state(state_group) AS ( VALUES(33029::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, state s WHERE s.state_group = e.state_group ) SELECT DISTINCT ON (type, state_key) type, state_key, event_id FROM state_groups_state WHERE state_group IN ( SELECT state_group FROM state ) ORDER BY type, state_key, state_group DESC | client backend

I have seen state groups like 28 before, so I think while it's mostly big numbers, it's not just big numbers

@richvdh
Copy link
Member

richvdh commented Jul 9, 2020

right, I think you might have a loop in the state_group_edges, presumably caused by your earlier problems with state_group_seq. Firstly, can you try:

WITH RECURSIVE sg(state_group) AS ( VALUES(33015::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, sg where e.state_group = sg.state_group) select * from sg;

this should return relatively quickly: if it hangs that confirms the theory, in which case can you also do:

select * from state_group_edges where prev_state_group>=state_group;

.... I don't suppose you have a backup of your database from before you, uh, broke it? Restoring that would be pretty handy right now....

@hellcp
Copy link
Author

hellcp commented Jul 9, 2020

right, I think you might have a loop in the state_group_edges, presumably caused by your earlier problems with state_group_seq. Firstly, can you try:

WITH RECURSIVE sg(state_group) AS ( VALUES(33015::bigint) UNION ALL SELECT prev_state_group FROM state_group_edges e, sg where e.state_group = sg.state_group) select * from sg;

this should return relatively quickly: if it hangs that confirms the theory,

It does hang

in which case can you also do:

select * from state_group_edges where prev_state_group>=state_group;

0 rows apparently

.... I don't suppose you have a backup of your database from before you, uh, broke it? Restoring that would be pretty handy right now....

I do have a backup of the database when I first had that issue at the very least, which might come in handy

@hellcp
Copy link
Author

hellcp commented Jul 9, 2020

After doing a backup of the current broken database, and restoring the older snapshot, it doesn't make much difference, and it posts the exact same queries as before, so it seems they are equally as broken. At the very least it's not getting more broken

@richvdh
Copy link
Member

richvdh commented Jul 9, 2020

It does hang

ok, can you do the same query with limit 200 or something.

I do have a backup of the database when I first had that issue at the very least, which might come in handy

right, but we believe it got broken when you transferred it to a different server and dropped the state_group_seq sequence? do you have a backup from before you did that?

@hellcp
Copy link
Author

hellcp commented Jul 9, 2020

It does hang

ok, can you do the same query with limit 200 or something.

That's instant

I do have a backup of the database when I first had that issue at the very least, which might come in handy

right, but we believe it got broken when you transferred it to a different server and dropped the state_group_seq sequence? do you have a backup from before you did that?

Nope

@richvdh
Copy link
Member

richvdh commented Jul 9, 2020

That's instant

yes, but what does it return

@hellcp
Copy link
Author

hellcp commented Jul 9, 2020 

 state_group 
-------------
       33032
       33011
       33011
       33006
       33006
       33006
       33006
       32978
       32978
       32978
       32978
       32978
       32978
       32978
       32978
       32947
       32947
       32947
       32947
       32947
       32947
       32947
       32947
[...]
       32947
       32947
       32925
       32925
       32925
       32925
       32925
       32925
       32925
       32925
       32925
       32925
       32925
       32925
       32925
       32925
       32925
       32925
       32925
       32925
       32925
       32925
       32925
       32925
       32925
       32925
       32925
       32925
       32925
       32925
       32925
       32925
       32925
       32925
       32915
       32915
       32915
       32915
[...]
       32915
       32915
       32915
       32915
       32915
       32915
       32915
       32915
       32915
       32915
       32915
       32915
       32915
       32915
       32915
       32915
       32915
       32915
       32905
       32905
       32905
       32905
       32905
       32905
       32905
       32905
       32905
       32905
       32905
       32905
[...]
       32905
       32905
       32905
       32905
       32905
       32905
       32905
       32905
       32905
       32905
       32905
       32905
       32905
       32905
       32905
       32905
       32905
       32905
       32905
       32905
       32905
       32905
       32905
       32905
       32905
       32905
       32905
       32905
       32905
       32905
       32905
       32905
       32905
       32905
       32905
       32905
       32905
       32905
       32905
       32905
       32905
       32905
       32905
       32905
       32905
       32905
       32905
       32905
       32905
       32905
       32905
       32905
(200 rows)

Abbreviated it a bit since copying output from terminal where a lot of lines are the exact same is hard ;)

@richvdh
Copy link
Member

richvdh commented Jul 9, 2020

ohhhh you've got duplicate rows.

select count(*) as c, state_group, prev_state_group from state_group_edges group by 2, 3 having count(*) > 1

?

@richvdh
Copy link
Member

richvdh commented Jul 9, 2020

in fact: did you import the backup twice, or something?

@hellcp
Copy link
Author

hellcp commented Jul 9, 2020

ohhhh you've got duplicate rows.

select count(*) as c, state_group, prev_state_group from state_group_edges group by 2, 3 having count(*) > 1

?

 c | state_group | prev_state_group 
---+-------------+------------------
 2 |       27555 |            27554
 2 |        9706 |             9681
 2 |       11757 |            11756
 2 |       28641 |            28640
 2 |       10073 |            10072
 2 |        5787 |             5785
 2 |        1405 |             1222
 2 |       28233 |            28232
 2 |       17263 |            17260
 2 |       25798 |            25797
 2 |       25614 |            25613
 2 |       17580 |            17579
 2 |        5295 |             5292
 2 |       19273 |            19272
 2 |       16237 |            16234
 2 |       20971 |            20967
 2 |       30869 |            30868
 2 |       27672 |            27671
 2 |        1900 |             1895
 2 |       28399 |            28398
 2 |       24600 |            24599
 2 |       31204 |            31162
 2 |       17111 |            17110
 2 |       13046 |            13043
 2 |       32985 |            32948
 2 |       18077 |            18068
 2 |       22746 |            22744
 2 |         203 |              190
 2 |        7082 |             7065
 2 |       15282 |            12014
 2 |       17337 |            17335
 2 |       30677 |            30676
 2 |       23203 |            23170
 2 |       24404 |            24403
 2 |       26928 |            26927
 2 |       23002 |            23001
 2 |       31048 |            31047
 2 |       19431 |            19426
 2 |       16716 |            16715
 2 |        3204 |             3203
 2 |        4060 |             4058
 2 |       27869 |            27868
 2 |       27135 |            27134
 2 |       13650 |            13647
 2 |       24668 |            24667
 2 |       22033 |            22032
 2 |       17252 |            17251
 2 |       28188 |            28187
 2 |       18448 |            18447
 2 |       10424 |            10423
 2 |       30241 |            30240
 2 |       27468 |            27467
 2 |       27468 |            27467
 2 |       30018 |            30017
 2 |       25339 |            25338
 2 |       23020 |            21599
 2 |       19251 |            19246
 2 |       27562 |            27561
 2 |        7968 |             7967
 2 |       18718 |            18708
 2 |       22388 |            22386
 2 |       20105 |            20100
 2 |       20461 |            20456
 2 |         925 |              924
 2 |       13693 |            13689
 2 |       29951 |            29950
 2 |        4254 |             4251
 2 |        7605 |             7602
 2 |        9925 |             9920
 2 |       31201 |            31178
 2 |       12812 |            12806
 2 |       24381 |            24380
 2 |       16534 |            16532
 2 |       21315 |            21311
 2 |        1799 |              826
 2 |       23892 |            23891
 2 |       30078 |            30077
 2 |        2667 |             2666
 2 |       26642 |            26641
 2 |       12881 |            12876
 2 |        4635 |             4634
 2 |       19182 |            19180
 2 |       12536 |            12534
 2 |       31043 |            31042
 2 |       23934 |            23588
 2 |        4909 |             4903
 2 |        5132 |             5129
 2 |       10103 |            10100
 2 |       30898 |            30897
 2 |       22300 |            22295
 2 |       29190 |            29189
 2 |        4199 |             4193
 2 |       28056 |            28055
 2 |       10228 |            10227
 2 |        2541 |             2538
 2 |       15598 |            15013
 2 |        3493 |             3490
 2 |       15781 |            15015
 2 |       30067 |            30066
 2 |       27386 |            27385
 2 |       13552 |            13551
 2 |       18019 |            18016
 2 |       28125 |            28124
 2 |       16971 |            16966
 2 |        6094 |             6092
 2 |       20229 |            20228
 2 |       20475 |            20472
 2 |       32632 |            32603
 2 |       20893 |            20892
 2 |        9335 |             9333
 2 |        8751 |             8750
 2 |       11850 |            11847
 2 |       18282 |            18281
 2 |       19826 |            19812
 2 |        1792 |             1787
 2 |       31623 |            31621
 2 |       21296 |            21293
 2 |        9195 |             9194
 2 |       26951 |            26950
 2 |       22878 |            22875
 2 |       23457 |            23455
 2 |       27481 |            27480
 2 |       21160 |            21157
 2 |        9896 |             9892
 2 |        4470 |             4466
 2 |       26668 |            26667
 2 |        1552 |             1551
 2 |       19287 |            19284
 2 |       17253 |            17252
 2 |       18946 |            18943
 2 |       25567 |            25566
 2 |       18311 |            18310
 2 |       16443 |            16441
 2 |        9144 |             9140
 2 |       23618 |            23617
 2 |       25067 |            25066
 2 |       18316 |            18314
 2 |       22355 |            22334
 2 |       13591 |            13588
 2 |       16634 |            16630
 2 |       28360 |            28359
 2 |        5014 |             5010
 2 |        5985 |             5983
 2 |       13093 |            13084
 2 |        9467 |             9466
 2 |       28308 |            28307
 2 |        2129 |             2127
 2 |       10517 |            10516
 2 |       21502 |            21501
 2 |       16423 |            16420
 2 |       27558 |            27557
 2 |       19063 |            19062
 2 |       13010 |            13005
 2 |        9070 |             9069
 2 |       24113 |            24110
 2 |       30602 |            30601
 2 |       29518 |            29516
 2 |       15016 |            15015
 2 |       27623 |            27622
 2 |         819 |              818
 2 |       20013 |            20012
 2 |       26652 |            26651
 2 |       21791 |            21790
 2 |        9057 |             9056
 2 |       17319 |            17318
 2 |        3121 |             3120
 2 |       29505 |            29504
 2 |        8852 |             8849
 2 |       32132 |            32122
 2 |       22204 |            22199
 2 |       27895 |            27894
 2 |        8803 |             8796
 2 |         244 |              243
 2 |       20139 |            20135
 2 |        8206 |             8205
 2 |        4487 |             4486
 2 |        3218 |             3217
 2 |        1208 |             1207
 2 |       30710 |            30697
 2 |       29817 |            29816
 2 |        7466 |             7465
 2 |       17353 |            17349
 2 |       17711 |            17709
 2 |       19679 |            19675
 2 |        8227 |             8194
 2 |       19104 |            19102
 2 |       22411 |            22410
 2 |       30689 |            30219
 2 |       19849 |            19848
 2 |       24965 |            24964
 2 |        1247 |             1246
 2 |       21933 |            21932
 2 |        8560 |             8559
 2 |       14273 |            14270
 2 |       22535 |            22533
 2 |       19767 |            19766
 2 |       23322 |             2825
 2 |       14827 |            14824
 2 |       30263 |            30262
 2 |       24083 |            17333
 2 |       32770 |            32742
 2 |       26189 |            26188
 2 |       14784 |            14781
 2 |       11699 |            11686
 2 |       24970 |            24969
 2 |       25843 |            25842
 2 |       19786 |            19785
 2 |       26580 |            26579
 2 |       12475 |            12473
 2 |       10792 |            10791
 2 |       26545 |            26544
 2 |       14263 |            14261
 2 |       19416 |            19413
 2 |       16200 |            16199
 2 |       32299 |            32275
 2 |       24991 |            24990
 2 |       11756 |            11755
 2 |       30806 |            30723
 2 |       25320 |            25319
 2 |        9002 |             9000
 2 |        6764 |             6756
 2 |       32162 |            32076
 2 |        8592 |             8589
 2 |       17078 |            17076
 2 |        8522 |             8495
 2 |        4479 |             4478
 2 |       11769 |            11764
 2 |       25077 |            25076
 2 |       29977 |            29976
 2 |       14819 |            14817
 2 |        1919 |             1918
 2 |       28697 |            28696
 2 |        2836 |             2835
 2 |       29362 |            29361
 2 |       27929 |            27928
 2 |       17259 |            17244
 2 |       19025 |            19021
 2 |        8405 |             8194
 2 |        4356 |             4351
 2 |        4107 |             4102
 2 |       18149 |            18144
 2 |       21414 |            21412
 2 |       10908 |            10896
 2 |       12459 |            12458
 2 |       26695 |            26694
 2 |       29751 |            29750
 2 |       24944 |            24943
 2 |        9008 |             9007
 2 |       11470 |            11442
 2 |       17317 |            17314
 2 |       33035 |            33003
 2 |       12814 |            12811
 2 |       24405 |            24404
 2 |       20436 |            20432
 2 |       29933 |            29932
 2 |       21761 |            21757
 2 |       31831 |            31827
 2 |       32652 |            32615
 2 |        6970 |             6965
 2 |        1349 |             1348
 2 |       17291 |            17287
 2 |       25226 |            25225
 2 |       19326 |            19320
 2 |       10581 |            10580
 2 |        3182 |             3181
 2 |       24952 |            24951
 2 |       13052 |            13048
 2 |       15660 |            15013
 2 |       11942 |            11938
 2 |        8252 |             8194
 2 |       24393 |            24392
 2 |        8801 |             8800
 2 |       32432 |            32431
 2 |       22510 |            22508
 2 |       28436 |            28435
 2 |       21541 |            21538
 2 |       32907 |            32858
 2 |       15294 |            12014
 2 |       21719 |            21716
 2 |       14179 |            14178
 2 |        4847 |             4845
 2 |        4852 |             4848
 2 |       27728 |            27727
 2 |       14233 |            14232
 2 |       28149 |            28148
 2 |        7141 |             7139
 2 |       32403 |            32402
 2 |       26169 |            26168
 2 |        4009 |             4008
 2 |       28791 |            28790
 2 |       27388 |            27386
 2 |       14001 |            13998
 2 |       31150 |            30840
 2 |       17772 |            17768
 2 |       31388 |            31319
 2 |       29256 |            29234
 2 |       20270 |            20269
 2 |       12558 |            12555
 2 |        4916 |             4913
 2 |       19581 |            19580
 2 |       17990 |            17976
 2 |        2878 |             2876
 2 |       12920 |            12907
 2 |       22458 |            22457
 2 |       16707 |            16703
 2 |       28116 |            28115
 2 |        5033 |             5032
 2 |       30419 |            30418
 2 |       19656 |            19640
 2 |       18625 |            18624
 2 |       11026 |            11023
 2 |       25356 |            25355
 2 |        2279 |             2254
 2 |       11478 |            11475
 2 |        2598 |             2597
 2 |       19388 |            19383
 2 |       13568 |            13567
 2 |       28006 |            28005
 2 |       26274 |            26273
 2 |       28227 |            28226
 2 |       23497 |            23496
 2 |       17978 |            17977
 2 |        4713 |             4712
 2 |       26559 |            26558
 2 |       19533 |            19532
 2 |        9647 |             9646
 2 |       23540 |            23539
 2 |       19378 |            19377
 2 |       11299 |            11296
 2 |       24139 |            24110
 2 |        1328 |             1326
 2 |        6028 |             6025
 2 |       11480 |            11477
 2 |       30706 |            29258
 2 |        4146 |             4143
 2 |        6571 |             6570
 2 |       10287 |            10285
 2 |       30280 |            30279
 2 |       28887 |            28886
 2 |       20113 |            20110
 2 |       20468 |            20464
 2 |       18141 |            18136
 2 |       27700 |            27699
 2 |        8420 |             8194
 2 |       27175 |            27174
 2 |       24468 |            24467
 2 |       13469 |            13466
 2 |       27457 |            27456
 2 |        5330 |             5329
 2 |        3407 |             3406
 2 |       22139 |            22138
 2 |       22531 |            22530
 2 |       15864 |            15863
 2 |        6588 |             6587
 2 |       22582 |            22581
 2 |       32354 |            32320
 2 |       15100 |            12014
 2 |       20124 |            20122
 2 |       22117 |            22116
 2 |        4380 |             4369
 2 |       32332 |            32314
 2 |        4288 |             4276
 2 |        6724 |             6722
 2 |        7231 |             7228
 2 |       15551 |            15013
 2 |        7868 |             7467
 2 |       31100 |            31099
 2 |       30391 |            30390
 2 |       30683 |            30682
 2 |       25098 |            25097
 2 |       28289 |            28288
 2 |       16737 |            16736
 2 |       15313 |            15007
 2 |       15795 |            15007
 2 |        3579 |             3577
 2 |         371 |              370
 2 |        2304 |             2297
 2 |       29136 |            29050
 2 |       10346 |            10345
 2 |        5870 |             5866
 2 |       12437 |            12436
 2 |       15908 |            15905
 2 |       13630 |            13629
 2 |       19565 |            19564
 2 |       27053 |            27052
 2 |       25817 |            25816
 2 |       18075 |            18074
 2 |        1668 |             1667
 2 |       11252 |            11248
 2 |       17275 |            17274
 2 |        1228 |             1227
 2 |       18691 |            18688
 2 |       26026 |            26025
 2 |       13206 |            13202
 2 |       27061 |            27060
 2 |       16646 |            16645
 2 |       12137 |            12136
 2 |       21614 |            21607
 2 |        7654 |             7653
 2 |       20952 |            20949
 2 |       19110 |            19107
 2 |         580 |              579
 2 |       28321 |            28320
 2 |        2247 |             2243
 2 |       21374 |            21373
 2 |       13167 |            13165
 2 |       13767 |            13763
 2 |       12273 |            12261
 2 |       27239 |            27238
 2 |       27740 |            27739
 2 |       10235 |            10234
 2 |        1631 |             1628
 2 |        3492 |             3489
 2 |       16342 |            16340
 2 |       13853 |            13852
 2 |        7983 |             7982
 2 |        9225 |             9224
 2 |         999 |              995
 2 |       23732 |            23729
 2 |        5978 |             5975
 2 |         951 |              937
 2 |       19694 |            19693
 2 |       27798 |            27797
 2 |       12462 |            12461
 2 |       32884 |            32829
 2 |       31986 |            31983
 2 |       23566 |            23565
 2 |       24558 |            24526
 2 |       23816 |            23815
 2 |       20945 |            20941
 2 |       31050 |            31048
 2 |       11588 |            11587
 2 |       17403 |            17402
 2 |       19318 |            19312
 2 |        9107 |             9104
 2 |       15872 |            15869
 2 |       21455 |            21448
 2 |       29914 |            29913
 2 |       23027 |            23026
 2 |       28347 |            28346
 2 |       12785 |            12783
 2 |       15491 |            15015
 2 |       13929 |            13926
 2 |        2573 |             2572
 2 |       15229 |            15013
 2 |       31768 |            31767
 2 |       29459 |            29458
 2 |       32362 |            32357
 2 |       13615 |            13614
 2 |       11572 |            11571
 2 |       18878 |            18875
 2 |       12511 |            12509
 2 |        6623 |             6609
 2 |       28229 |            28228
 2 |        8709 |             8705
 2 |       14074 |            14073
 2 |       18255 |            18254
 2 |       28177 |            28176
 2 |       22473 |            22472
 2 |       25048 |            25047
 2 |        3637 |             3636
 2 |       13378 |            13377
 2 |       19115 |            19113
 2 |       23707 |            23706
 2 |       10421 |            10420
 2 |       27426 |            27425
 2 |       10975 |            10963
 2 |       13606 |            13605
 2 |       16703 |            16701
 2 |       23492 |            23455
 2 |       11077 |            11054
 2 |       12973 |            12971
 2 |       32727 |            32698
 2 |       29813 |            29812
 2 |       15331 |            15015
 2 |        4711 |             4710
 2 |       14161 |            14158
 2 |       15410 |            15013
 2 |       31307 |            31281
 2 |       27696 |            27695
 2 |       11463 |            11462
 2 |       29868 |            29867
 2 |       23174 |            23173
 2 |       28676 |            28675
 2 |       13691 |            13687
 2 |       17916 |            17911
 2 |       23434 |            23433
 2 |       20241 |            20221
 2 |        7350 |             7349
 2 |       21706 |            21705
 2 |       21425 |            21423
 2 |       13928 |            13925
 2 |       16760 |            16759
 2 |        9917 |             9912
 2 |       32856 |            32845
 2 |        2885 |             2884
 2 |       23496 |            23495
 2 |        8729 |             8727
 2 |       13708 |            13707
 2 |       30234 |            30233
 2 |       23731 |            23730
 2 |        8051 |             8046
 2 |         842 |              828
 2 |       29449 |            29448
 2 |        6430 |             6418
 2 |       17931 |            17916
 2 |       12955 |            12951
 2 |        6175 |             6172
 2 |        3723 |             3722
 2 |       24825 |            24824
 2 |       25771 |            25770
 2 |       32484 |            32467
 2 |       12768 |            12767
 2 |        1762 |             1747
 2 |       19101 |            19097
 2 |       28489 |            28488
 2 |       23232 |            23231
 2 |        5147 |             5145
 2 |       19542 |            19541
 2 |       14350 |            14346
 2 |        9089 |             9088
 2 |        2507 |             2494
 2 |       21532 |            21517
 2 |        8577 |             8574
 2 |        5802 |             5800
 2 |       32315 |            32289
 2 |       27723 |            27722
 2 |       25592 |            25591
 2 |        4611 |             4588
 2 |         620 |              619
 2 |        8907 |             8903
 2 |       14738 |            14737
 2 |       25622 |            25621
 2 |       13587 |            13582
 2 |        7235 |             7231
 2 |         727 |              726
 2 |       20444 |            20443
 2 |        1311 |             1308
[...]
(31910 rows)

I kinda give up pasting this, because it has 32k lines

in fact: did you import the backup twice, or something?

I hope not, I would probably remember, but I cannot tell you if I didn't do it weeks ago, because that I wouldn't remember

@hellcp
Copy link
Author

hellcp commented Jul 9, 2020

I hope not, I would probably remember, but I cannot tell you if I didn't do it weeks ago, because that I wouldn't remember

Oh yeah, repeating the query for = 1 instead of > 1 reveals that only very high indexes don't repeat, so I must have imported it twice

@richvdh
Copy link
Member

richvdh commented Jul 9, 2020

well, sad times. You need to remove the duplicates. Perhaps you can come up with sql yourself for that, or find someone who can help.

I'm going to go ahead and close this, since it's very much a case of "I corrupted my database and now synapse runs slowly", which is somewhat out-of-scope for something we can fix...

@richvdh richvdh closed this as completed Jul 9, 2020
@richvdh
Copy link
Member

richvdh commented Jul 9, 2020

(honestly: if you've messed it up that badly, and don't have an uncorrupted backup ... you may be better off throwing it away and starting again. who knows what other tables are going to be even more corrupted.)

@hellcp
Copy link
Author

hellcp commented Jul 9, 2020

This was really helpful anyway, I got synapse going again, and I will take your advice if anything goes south :P

@richvdh richvdh mentioned this issue Jan 7, 2022
Open
@richvdh richvdh mentioned this issue Jan 20, 2022
Closed
@jffrancob jffrancob mentioned this issue Mar 3, 2022
Closed
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Assignees
No one assigned
Labels
A-Performance Performance, both client-facing and admin-facing
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
3 participants
@richvdh @babolivier @hellcp