[Spacewalk-list] CPU bound postgresql process ? Why and how to stop it?

[Boyd, Robert]

Thank you for the suggestions.

I had previously done periodic pgtune passes.   I managed to do some tuning of config settings as you suggested for postgres and get a pass through vacuuming and reindexing. The performance of the server seems to be a bit crisper.  However it didn't eliminate the problem with a process going CPU bound. I'm still waiting to hear back on my question about when I might be able to apply the updates that Michael Mraka pointed to.

Robert

-----Original Message-----
From: spacewalk-list-bounces at redhat.com [mailto:spacewalk-list-bounces at redhat.com] On Behalf Of Paul Robert Marino
Sent: Monday, December 09, 2013 4:25 PM
To: spacewalk-list at redhat.com
Subject: Re: [Spacewalk-list] CPU bound postgresql process ? Why and how to stop it?

sorry for the delayed responce on this I thought I sent it but its been sitting in my drafts folder

On Wed, Dec 4, 2013 at 11:21 AM, Boyd, Robert <Robert.Boyd at peoplefluent.com> wrote:
> I have several hundred servers in spacewalk.   How much time should I allow for a window to run this db maintenance?
that depends on how fragmented the tables and free space in the tables are which is a direct correlation to how many changes have been made since autovacuume has cleaned it up.
my facilities are 24x7 365 mission critical but on the weekend no changes are allowed to any systems so I have a cron job that does it at midnight on the first Saturday of each month and I don't really time it.

if its the first time you are doing it I would schedule 4 hours.
1 hour if you skip the reindex and second vacuum analyze. the reindex is whats really time consuming and not really required.


>
> And how does this relate to the bugzilla that Michael mentioned?

Yes and no they are and are not related
Not doing the maintenance I mentioned would significantly exacerbate the issue in the bugzilla ticket.
this is because it seems to be a planner issue and the planer utilizes the statistics created by the analyze process to figure out how to handle complex queries like this.

additionally you could more quickly help it if you tune the planner for example in ~postgres/data/postgresql.conf adjusting the size of effective_cache_size can be extremely helpful.

Also turning on constraint_exclusion in the same file is helpfull with spacewalk! it means the planner takes longer but can automatically figure out things to exclude in subqueries and joins based on constraints in the other parts of the overall query so the resulting query can be faster.
by default constraint_exclusion is turned off in PostgreSQL because if you don't do a lot of joins and conditional sub queries it will hurt you performance but in the case of spacewalk its a significant help.

the query planner in PostgreSQL is a complex subject but the major things to look at for spacewalk tuning are

shared_buffers (increase a lot)
work_mem (increase a lot if possible)
effective_cache_size (definitely increase a lot) default_statistics_target (increase a little or a lot but the down size is it makes analyze take longer the more you increase it and there is a point of diminishing returns) constraint_exclusion (enable this) from_collapse_limit (increase slightly) join_collapse_limit (increase slightly)

Also you you want to speed up vacuum, autovacuum, and reindex, and analyze operations increasing the maintenance_work_mem is helpful for that but keep in mind that autovacuums may use that during normal database operation so be careful not to make it too high so for example on my production boxes I have it set to 1GB

>
> Michael, how long before the spacewalk-java update will move from nightly to production release?
>
> Thank you both for your assistance!
>
> Robert