1

We are using postgres 11.4 and nodejs to run sql scripts. We use node-postgres module and its Pool feature, setting it to 10.

Database is running on a server with 4GB total memory. When the script is not running the total available memory is around 200MB. When the script is running it can go down to 160MB.

We have a script that most of the time is able to complete on smaller operations but gets stuck because of a query / queries that's being awaited and doesn't finish on larger operations.

The queries that don't finish have a state of 'active' when we do a select on pg_stat_activity. Other connections from the same pool (based on client_addr) have a state of 'idle'.

The node script running these queries does not appear to have become unresponsive when this happens, (we will verify this as it has a message client in it).

In the node script there is a query that fetches 1000 records and these records are iterated over with some logic executed on each one. The loop does a progress update after every 500 records processed and then fetches another 1000 records. This is done to process large numbers of records. Eg; 2 - 3 million of records.

The progress updates are done as async database calls that consist of one update and one select. The update query is asyc/awaited but the select is just async fire and forget, and then loop continues.

We are seeing that when we process a large batch of records that some of the two update/select queries for progress get stuck at the same time. A select query and an update query on the same table. The update is for updating one of the progress columns, and the select computes total progress that is then sent to the client.

Sometimes it happens on the maindata table while doing a select of the 1000 records to be processed but that doesn't happen often.

Sometimes when there is a query that gets stuck we also see autovacuum: VACUUM tablename in pg_stat_activity. And usually once that autovacuum disappears, one pair of select,update query gets to finish and the script it is running in runs to completion. BUT not all pair of select,update or other query not related to the table finishes.

Also, after a query(s) is stuck, we can still run select/update/insert queries on that same table from another node script without issue.

We have no idea what is causing this or how to identify/fix the issue. Any help would be much appreciated.

Thank your for your time.

Update:

Screenshot of pg_stat_activity showing stuck select statement and all rows in pg_locks. Couldn't get the select,update combo that get stucks; for now seems to get eventually unstuck.

pg_stat_activity

pg_locks


Select progress query:

select
    ((tn.progress1 + tn.progress2 + tn.progress3) * 100 / (3 * 100)) total_percent 
    ,concat(
            'Total Progress: ', ((tn.progress1 + tn.progress2 + tn.progress3) * 100 / (3 * 100)), E'\n',
            'Status: ', E'\n', tn.message, E'\n',
            E'\n\n'
    ) final_message
    ,(case coalesce(tn.state,0) <> [ERRORCODE] when TRUE then 0 else 1 end) error
from table tn
where 
    tn.id = [id] 
    and tn.guid1 = [guid1]
    and tn.guid2 = [guid2]
group by
    tn.progress1, tn.progress2, tn.progress3, tn.message, tn.state;
  • "select is just async fire and forget". What is the point of that? Does the select have side affects? – jjanes Aug 30 at 14:50
  • @jjanes it's just not await-ed. Process described above is only one of the steps to a job. After every step, the job's progress is updated then that total-progress-select-query is then run. – chrism Aug 31 at 3:46
  • Screenshot :^( Anyway, There are two queries with state = active and wait_event = ClientRead, which sound contradictory. What exactly are these queries? Can you share the text (not as screenshot)? – Laurenz Albe Sep 2 at 5:15
  • @LaurenzAlbe The queries are the fire-and-forget progress select queries that we send back as progress info to the client while the 1000 record iteration continues. – chrism Sep 2 at 11:54
  • Hmm. This is weird, but I cannot explain it. A query that is active should never wait for the client. – Laurenz Albe Sep 2 at 12:19
0

We are now looking at docker + logs as having caused the issue. It's not the database.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.