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

  • 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 Jul 19 '16 at 20:05
  • Are those queries select queries only ? – Sahap Asci Jul 19 '16 at 22:12
2

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.

  • How about holding 'plain' locks? Those could also block other processes. – dezso Jul 20 '16 at 16:01
  • 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 Jul 20 '16 at 19:00

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.