Replay freezes, all queries stuck in ClientRead state. about pgreplay HOT 3 CLOSED

If the state of the sessions really is idle, that would be a pgreplay bug.
If the state is actually idle in transaction, you may be hitting the same problem as #12, which is also mentioned as a limitation in the README:

While pgreplay makes sure that commands are sent to the server in the
order in which they were originally executed, there is no way to guarantee
that they will be executed in the same order during replay: Network
delay, processor contention and other factors may cause a later command
to "overtake" an earlier one. While this does not matter if the
commands don't affect each other, it can lead to SQL statements hitting
locks unexpectedly, causing replay to deadlock and "hang".
This is particularly likely if many different sessions change the same data
repeatedly in short intervals.

You can work around this problem by canceling the waiting statement with
pg_cancel_backend. Replay should continue normally after that.

You could also consider setting lock_timeout to break such deadlocks.

Since you say that you have 2000 queries per second, it may well be that pgreplay is not the tool for you. It runs single-threaded, so beyond a certain point it will itself become the bottleneck.

from pgreplay.

State is in fact idle in transaction. I'll report if I find a way around it here.

from pgreplay.

It was in fact deadlock-related, and nothing was configured to kill the deadlocks so the whole thing would just freeze. Still not sure if the tool can support 2000 queries/second but it looks like it was actually keeping up at that pace! And after tracking down a few problems, we have it down to a lot less than 2000 queries/sec. Here is the query I used to find the deadlocks:

SELECT blocked_locks.pid     AS blocked_pid,
         blocked_activity.usename  AS blocked_user,
         blocking_locks.pid     AS blocking_pid,
         blocking_activity.usename AS blocking_user,
         blocked_activity.query    AS blocked_statement,
         blocking_activity.query   AS current_statement_in_blocking_process
   FROM  pg_catalog.pg_locks         blocked_locks
    JOIN pg_catalog.pg_stat_activity blocked_activity  ON blocked_activity.pid = blocked_locks.pid
    JOIN pg_catalog.pg_locks         blocking_locks
        ON blocking_locks.locktype = blocked_locks.locktype
        AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
        AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
        AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
        AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
        AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
        AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
        AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
        AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
        AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
        AND blocking_locks.pid != blocked_locks.pid
    JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
   WHERE NOT blocked_locks.GRANTED;

And then used pg_cancel_backend() to kill the offending query.

Thank you!

from pgreplay.