Jira (PDB-5027) Investigate factset update getting stuck in DataFileRead

[Zachary Kent (Jira)]

Title: Message Title

Zachary Kent created an issue   PuppetDB / PDB-5027 Investigate factset update getting stuck in DataFileRead Issue Type: Bug Assignee: Unassigned Created: 2021/02/16 1:37 PM Priority: Normal Reporter: Zachary Kent We recently had a FOSS person running PDB 6.2.0 report an issue that looked similar to one we've seen at a PE site recently (see restricted comment for link to internal slack message about PE issue). In this case PDB stopped processing commands (maybe only reports) and stopped logging messages until the disk filled up 4 hours later due to commands being enqueued. See FOSS slack thread for more context. Looking into this issue we found that there were multiple commands blocked waiting on locks behind an update factset command which was stuck waiting on wait_event=DataFileRead. This query appeared to be stuck for ~2 days at that point. We also noticed that there was quite a bit of bloat in the factsets and resource_events tables. In the similar PE case CS was able to resolve the issue by running pg_cancel_backend(pid) and running pg_repack on the bloated tables. state of bloat in the factset/resource_events tables: schemaname | relname | n_dead_tup | n_live_tup | last_auto vacuum ------------+-----------------------+------------+------------+----------------- public | factsets | 11067234 | 11266 | public | resource_events | 12623797 | 67360341 |   Poking around at the factset ingestion functions I noticed that we use a repeatable read transaction isolation level for factset submission and rely on serialization errors to cancel competing concurrent transactions. See here and the call site of that func for more detail. We should check if repeatable read causes transactions to grab stronger row level locks that may cause other queries to park behind an update factset query. We also need to check if there is anyway two concurrent updates for factsets could deadlock with each other in a way the Postgres deadlock detector wouldn't be able to see. See this thread in the Postgres slack channel for more related information. It looks like the FOSS person was able to find out that the Postgres pid for the stuck update factset query was doing a bunch of I/O in these calls: ginEntryInsert > ginInsertItemPointers > ginFindLeafPage. It may be possible that when a table is super bloated updating the gin index for the jsonb factsets might be slowing things up some. Overall I don't think anything other than a deadlock of some type can account for an update query being stuck open for two days though, but that still wouldn't explain the constant I/O that the query was doing. It would still be beneficial to look into the autovacuum settings on the factsets table and how gin indexes on jsonb columns get updated when working on this ticket. Check GIN fast update technique to determine if setting fastupdate would be useful in our case. Add Comment   This message was sent by Atlassian Jira (v8.5.2#805002-sha1:a66f935) -- You received this message because you are subscribed to the Google Groups "Puppet Bugs" group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/puppet-bugs/JIRA.387734.1613511425000.143212.1613511480309%40Atlassian.JIRA.