[Spacewalk-list] Run away DELETE query eating my disk space

[Jon Miller]

Chris <dmagick at gmail.com> writes:

> On 04/05/14 10:29, Jon Miller wrote:
>> That's another fine suggestion though I still believe the issues were/are
>> still related to the actual SQL queries being ran. I completed my planned
>> routine last evening but just recreating the DB and reloading my backup
>> didn't change anything. I then changed the following PostgreSQL tunables:
>> - shared_buffers from (default?) 32MB => 2048MB
>> - work_mem from 1MB => 20MB
>> - maintenance_work_mem from 16MB => 300MB
>
> Did you `analyze` the database after reloading it? (If not, try it).
>
>> So, that's why I keep my attention on the DB. And now when I look for what
>> queries are running, I keep seeing only this one:
>
> Set log_min_duration to something (see 
> http://www.postgresql.org/docs/8.4/static/runtime-config-logging.html) 
> and reload the postgresql config (service postgresql reload).
>
> Is there a common table in all of the queries being logged? Check 
> indexes and so on.
>
>> Does PostgreSQL use pgsql_tmp space when queries can not be performed within
>> working memory?
>
> Yes.

Wanted to report back on the conclusion of my issue(s). 
- I thought about setting log_min_duration but I knew that would only end up
  constantly reporting same query that I could see running via a psql
  session.
- I did end up bumping up work_mem again to 40MB but still didn't help.

While researching the issue by browsing the source, I noticed that the
current query in my 2.0 instance[1] was pretty massive. I was hoping to gain
insight into what it was doing in order to further tune my DB but then
noticed that the same job was dramatically simplified[2] in the recent 2.1
Spacewalk release. I upgraded my test and production instances over the
weekend and has been stable since. No more wild disk space swings using
pgsql_tmp and actually reduced CPU overhead as well. 

Thanks for the 2.1 improvements. 

[1]: https://github.com/spacewalkproject/spacewalk/blob/SPACEWALK-2.0/java/code/src/com/redhat/rhn/common/db/datasource/xml/Task_queries.xml#L495
[2]: https://github.com/spacewalkproject/spacewalk/blob/SPACEWALK-2.1/java/code/src/com/redhat/rhn/common/db/datasource/xml/Task_queries.xml#L444

-- 
Jon Miller