2

A colleague and I are both working on the same postgres database. We both started memory intensive queries which have now been running for several hours. The machine that hosts the database has limited RAM so both our processes are running extremely slowly. My colleague's query (the creation of a materialized view) typically takes 4 hours to complete but has now been running most of the day.

Since my colleague's query is more important than mine and needs to be completed by a certain time; is there a way I can pause my query thereby increasing the available RAM and allowing my colleague's process to complete?

My query is a large 'update' to a table and ideally I don't want to kill it a have to start all over again. Is there anyway I can reduce or temporarily suspend the resources consumed by my process?

Postgres 9.4/Redhat 6.8

Improve this question
2
  • Not sure that it possible using PostgreSQL, but you can try to play with process priority using OS tools (renice as I know, select pid, usename, query, * from pg_stat_activity; to find the process ID)
    – Abelisto
    Commented Jul 19, 2016 at 20:05
  • Are those queries select queries only ?
    – Sahap Asci
    Commented Jul 19, 2016 at 22:12

1 Answer 1

Reset to default
4

If you have access to the OS account under which postgres runs, you can identify the PID of your process (using top, or ps, or pg_stat_activity), and then do a kill -SIGSTOP <pid>. Later you would then do a kill -SIGCONT <pid> to get it to run again.

This is not a risk-free thing to do. If you stop the process while it is holding a spinlock (pretty unlikely, but certainly possible), you could freeze up the entire database, until eventually it would realize something was wrong and then crash itself (terminating your colleague's job). If you stop the process while it is holding a lightweight lock, it would also freeze the database (or parts of it) and it remain frozen until you continue the process and it releases the lock.

Also, the memory of the stopped process will not be released. The system will be able to swap that memory out without it constantly fighting to swap back in, and maybe that is good enough. But it would be better if it were actually released.

All in all, it is probably a better idea to cancel your update and repeat it later.

Improve this answer
3
  • How about holding 'plain' locks? Those could also block other processes.
    – András Váczi
    Commented Jul 20, 2016 at 16:01
  • 1
    Plain locks (heavy-weight locks) generally last for the duration of the transaction. So anything the process would be blocking on heavy-weight locks while it is paused, it would already be blocking while it is running (and running, and running, for hours) anyway. So there is no additional concern.
    – jjanes
    Commented Jul 20, 2016 at 19:00
  • Another option would be ionice -c3 for the process that can run slowly. Note that currently only CFQ disk scheduler supports scheduling class and priority so if you're using noop, deadline or some other lesser featured scheduler you're out of luck with this one.
    – Mikko Rantalainen
    Commented Mar 20, 2020 at 14:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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