Chris <[email protected]> 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 _______________________________________________ Spacewalk-list mailing list [email protected] https://www.redhat.com/mailman/listinfo/spacewalk-list
