Re: [Spacewalk-list] CPU bound postgresql process ? Why and how to stop it?
Yes -- absolutely -- it would be very helpful since many are switching to PostgreSQL. I imagine many are like me not trained as DBAs and a bit of basic guidance would be much appreciated. I've been a DBA long ago, but not recently. So a handbook for basic tuning and backup/recovery operations for spacewalk/PostgreSQL would be good to have on the wiki. Robert -Original Message- From: spacewalk-list-boun...@redhat.com [mailto:spacewalk-list-boun...@redhat.com] On Behalf Of Paul Robert Marino Sent: Friday, December 13, 2013 5:53 PM To: spacewalk-list@redhat.com Subject: Re: [Spacewalk-list] CPU bound postgresql process ? Why and how to stop it? Ill compare your configs with some of my note when I get into the office on Sunday Im sure there is more that can be tweaked but I dont have all the notes I wrote up on to tune this stuff handy right now. Incidentally would it be helpful to nay one else if I created a Wiki page on the subject of tuning PostgreSQL for Spacewalk. ___ Spacewalk-list mailing list Spacewalk-list@redhat.com https://www.redhat.com/mailman/listinfo/spacewalk-list
Re: [Spacewalk-list] CPU bound postgresql process ? Why and how to stop it?
Up until today I thought I would be stuck with the cpu-bound processes until spacewalk 2.1 is released. However, yesterday I updated my spacewalk master server since rebooting performance is back to normal. Here is the list of updates that were applied to the server before the reboot: Dec 23 14:59:06 Updated: nspr-4.10.2-1.el6_5.x86_64 Dec 23 14:59:06 Updated: nss-util-3.15.3-1.el6_5.x86_64 Dec 23 14:59:06 Updated: nss-sysinit-3.15.3-3.el6_5.x86_64 Dec 23 14:59:07 Updated: nss-3.15.3-3.el6_5.x86_64 Dec 23 14:59:07 Updated: pixman-0.26.2-5.1.el6_5.x86_64 Dec 23 14:59:07 Updated: xorg-x11-server-common-1.13.0-23.1.el6_5.x86_64 Dec 23 14:59:10 Updated: samba-common-3.6.9-167.el6_5.x86_64 Dec 23 14:59:11 Updated: samba-winbind-clients-3.6.9-167.el6_5.x86_64 Dec 23 14:59:13 Updated: samba-winbind-3.6.9-167.el6_5.x86_64 Dec 23 14:59:13 Updated: libjpeg-turbo-1.2.1-3.el6_5.x86_64 Dec 23 14:59:18 Updated: kernel-firmware-2.6.32-431.1.2.el6.noarch Dec 23 14:59:27 Installed: kernel-2.6.32-431.1.2.el6.x86_64 Dec 23 14:59:27 Updated: systemtap-runtime-2.3-4.el6_5.x86_64 Dec 23 14:59:28 Installed: python-six-1.4.1-1.el6.noarch Dec 23 14:59:29 Updated: ca-certificates-2013.1.95-65.1.el6_5.noarch Dec 23 14:59:30 Updated: openssl-1.0.1e-16.el6_5.1.x86_64 Dec 23 14:59:30 Updated: postgresql92-libs-9.2.6-1PGDG.rhel6.x86_64 Dec 23 14:59:30 Updated: mesa-dri-filesystem-9.2-0.5.el6_5.2.x86_64 Dec 23 14:59:32 Updated: mesa-dri-drivers-9.2-0.5.el6_5.2.x86_64 Dec 23 14:59:32 Updated: mesa-libGL-9.2-0.5.el6_5.2.x86_64 Dec 23 14:59:32 Updated: mesa-libGLU-9.2-0.5.el6_5.2.x86_64 Dec 23 14:59:32 Updated: mesa-libgbm-9.2-0.5.el6_5.2.x86_64 Dec 23 14:59:32 Installed: python-chardet-2.0.1-1.el6.rf.noarch Dec 23 15:00:33 Installed: kernel-devel-2.6.32-431.1.2.el6.x86_64 Dec 23 15:00:34 Updated: systemtap-devel-2.3-4.el6_5.x86_64 Dec 23 15:00:39 Updated: systemtap-client-2.3-4.el6_5.x86_64 Dec 23 15:00:39 Updated: systemtap-2.3-4.el6_5.x86_64 Dec 23 15:00:39 Updated: python-debian-0.1.21-10.el6.noarch Dec 23 15:00:39 Updated: mesa-libEGL-9.2-0.5.el6_5.2.x86_64 Dec 23 15:00:39 Updated: glx-utils-9.2-0.5.el6_5.2.x86_64 Dec 23 15:00:39 Updated: postgresql84-libs-8.4.19-1PGDG.rhel6.x86_64 Dec 23 15:00:40 Updated: xorg-x11-server-Xorg-1.13.0-23.1.el6_5.x86_64 Dec 23 15:00:52 Installed: firefox-24.2.0-1.el6_5.x86_64 Dec 23 15:00:52 Updated: libsmbclient-3.6.9-167.el6_5.x86_64 Dec 23 15:00:53 Updated: nss-tools-3.15.3-3.el6_5.x86_64 Dec 23 15:00:54 Updated: perf-2.6.32-431.1.2.el6.x86_64 Dec 23 15:00:55 Updated: libcgroup-0.40.rc1-5.el6_5.1.x86_64 Dec 23 15:00:58 Updated: tzdata-java-2013i-1.el6.noarch Dec 23 15:01:00 Updated: samba4-libs-4.0.0-60.el6_5.rc4.x86_64 Dec 23 15:01:00 Updated: openjpeg-libs-1.3-10.el6_5.x86_64 Dec 23 15:01:02 Updated: kernel-headers-2.6.32-431.1.2.el6.x86_64 Dec 23 15:01:03 Updated: yum-3.2.29-43.el6_5.noarch Dec 23 15:01:04 Updated: ansible-1.4.1-1.el6.noarch Dec 23 15:01:07 Updated: tzdata-2013i-1.el6.noarch I am guessing that the kits most likely to have helped would be the postgresql84-libs and kernel related. Any thoughts about other kits that might be significant influencers of relieving the problem with the cpu bound query? In any case I'm elated and relieved to have the server back to normal cpu loading patterns. Perhaps with the 2.1 release performance will be even better than now! :) -Original Message- From: spacewalk-list-boun...@redhat.com [mailto:spacewalk-list-boun...@redhat.com] On Behalf Of Paul Robert Marino Sent: Friday, December 13, 2013 5:53 PM To: spacewalk-list@redhat.com Subject: Re: [Spacewalk-list] CPU bound postgresql process ? Why and how to stop it? Ill compare your configs with some of my note when I get into the office on Sunday Im sure there is more that can be tweaked but I dont have all the notes I wrote up on to tune this stuff handy right now. Incidentally would it be helpful to nay one else if I created a Wiki page on the subject of tuning PostgreSQL for Spacewalk. On Fri, Dec 13, 2013 at 5:45 PM, Paul Robert Marino wrote: > oh sorry I was reading it on my phone so the formatting was weird I misread > it. > > > > On Fri, Dec 13, 2013 at 5:37 PM, Boyd, Robert > wrote: >> That's a configuration setting - not an error message. >> >> >> >> From: spacewalk-list-boun...@redhat.com >> [mailto:spacewalk-list-boun...@redhat.com] On Behalf Of Paul Robert >> Marino >> Sent: Friday, December 13, 2013 4:27 PM >> To: spacewalk-list@redhat.com >> Subject: Re: [Spacewalk-list] CPU bound postgresql process ? Why and >> how to stop it? >> >> >> >> Well that message at the end of the output you posted "processing >> past damaged page headers." concerns me that means your database >> might have a very serious problem. The safest thing to do would be to >> back up the database via pgdump then drop and reload it.
Re: [Spacewalk-list] CPU bound postgresql process ? Why and how to stop it?
Ill compare your configs with some of my note when I get into the office on Sunday Im sure there is more that can be tweaked but I dont have all the notes I wrote up on to tune this stuff handy right now. Incidentally would it be helpful to nay one else if I created a Wiki page on the subject of tuning PostgreSQL for Spacewalk. On Fri, Dec 13, 2013 at 5:45 PM, Paul Robert Marino wrote: > oh sorry I was reading it on my phone so the formatting was weird I misread > it. > > > > On Fri, Dec 13, 2013 at 5:37 PM, Boyd, Robert > wrote: >> That’s a configuration setting – not an error message. >> >> >> >> From: spacewalk-list-boun...@redhat.com >> [mailto:spacewalk-list-boun...@redhat.com] On Behalf Of Paul Robert Marino >> Sent: Friday, December 13, 2013 4:27 PM >> To: spacewalk-list@redhat.com >> Subject: Re: [Spacewalk-list] CPU bound postgresql process ? Why and how to >> stop it? >> >> >> >> Well that message at the end of the output you posted "processing past >> damaged page headers." concerns me that means your database might have a >> very serious problem. The safest thing to do would be to back up the >> database via pgdump then drop and reload it. I would also do a fsck on the >> volume if I were you. >> >> >> -- Sent from my HP Pre3 >> >> >> >> >> >> On Dec 13, 2013 9:33, Boyd, Robert wrote: >> >> I made the constraint exclusion change in the config options. However I’m >> still seeing this query running 100% cpu: >> >> >> >> 16384 | spaceschema |6216 |16388 | spaceuser | SELECT DISTINCT >> snv.server_id AS server_id, S.name, S.release, SA.name as arch, >> >> | f | 2013-12-10 19:42:33.097029-05 | >> 2013-12-13 07:24:49.616359-05 | 2013-12-10 19:42:32.648538-05 | 127.0.0.1 >> | 36044 >> >> : urn.user_id >> >> : FROM ( >> >> : -- >> >> : select >> rhnChannelErrata.errata_id, rhnChannelErrata.channel_id, >> rhnServerChannel.server_id, rhnErrataPackage.package_id >> >> : from >> rhnChannelErrata, rhnErrataPackage, rhnChannelNewestPackage, rhnPackageEVR, >> >> : >> rhnServerChannel, rhnServerPackage, rhnPackageUpgradeArchCompat >> >> : where >> rhnChannelErrata.errata_id = rhnErrataPackage.errata_id >> >> : -- >> >> : and >> rhnChannelErrata.channel_id = rhnChannelNewestPackage.channel_id >> >> : and rhnErrataPackage.package_id = >> rhnChannelNewestPackage.package_id >> >> : -- >> >> : and >> rhnChannelErrata.channel_id = rhnServerChannel.channel_id >> >> : and >> rhnChannelNewestPackage.name_id = rhnServerPackage.name_id >> >> : and >> rhnServerChannel.server_id = rhnServerPackage.server_id >> >> : -- >> >> : and >> rhnChannelNewestPackage.evr_id = rhnPackageEVR.id >> >> : -- >> >> : and >> rhnServerPackage.package_arch_id = >> rhnPackageUpgradeArchCompat.package_arch_id >> >> : and >> rhnPackageUpgradeArchCompat.pack >> >> >> >> Here is a dump of my configuration settings: >> >> >> >> spaceschema=# show all; >> >> name |setting| >> description >> >> >> >> -+---+- >> >> -- >> >> add_missing_from
Re: [Spacewalk-list] CPU bound postgresql process ? Why and how to stop it?
oh sorry I was reading it on my phone so the formatting was weird I misread it. On Fri, Dec 13, 2013 at 5:37 PM, Boyd, Robert wrote: > That’s a configuration setting – not an error message. > > > > From: spacewalk-list-boun...@redhat.com > [mailto:spacewalk-list-boun...@redhat.com] On Behalf Of Paul Robert Marino > Sent: Friday, December 13, 2013 4:27 PM > To: spacewalk-list@redhat.com > Subject: Re: [Spacewalk-list] CPU bound postgresql process ? Why and how to > stop it? > > > > Well that message at the end of the output you posted "processing past > damaged page headers." concerns me that means your database might have a > very serious problem. The safest thing to do would be to back up the > database via pgdump then drop and reload it. I would also do a fsck on the > volume if I were you. > > > -- Sent from my HP Pre3 > > > > > > On Dec 13, 2013 9:33, Boyd, Robert wrote: > > I made the constraint exclusion change in the config options. However I’m > still seeing this query running 100% cpu: > > > > 16384 | spaceschema |6216 |16388 | spaceuser | SELECT DISTINCT > snv.server_id AS server_id, S.name, S.release, SA.name as arch, > > | f | 2013-12-10 19:42:33.097029-05 | > 2013-12-13 07:24:49.616359-05 | 2013-12-10 19:42:32.648538-05 | 127.0.0.1 > | 36044 > > : urn.user_id > > : FROM ( > > : -- > > : select > rhnChannelErrata.errata_id, rhnChannelErrata.channel_id, > rhnServerChannel.server_id, rhnErrataPackage.package_id > > : from > rhnChannelErrata, rhnErrataPackage, rhnChannelNewestPackage, rhnPackageEVR, > > : > rhnServerChannel, rhnServerPackage, rhnPackageUpgradeArchCompat > > : where > rhnChannelErrata.errata_id = rhnErrataPackage.errata_id > > : -- > > : and > rhnChannelErrata.channel_id = rhnChannelNewestPackage.channel_id > > : and rhnErrataPackage.package_id = > rhnChannelNewestPackage.package_id > > : -- > > : and > rhnChannelErrata.channel_id = rhnServerChannel.channel_id > > : and > rhnChannelNewestPackage.name_id = rhnServerPackage.name_id > > : and > rhnServerChannel.server_id = rhnServerPackage.server_id > > : -- > > : and > rhnChannelNewestPackage.evr_id = rhnPackageEVR.id > > : -- > > : and > rhnServerPackage.package_arch_id = > rhnPackageUpgradeArchCompat.package_arch_id > > : and > rhnPackageUpgradeArchCompat.pack > > > > Here is a dump of my configuration settings: > > > > spaceschema=# show all; > > name |setting| > description > > > > -+---+- > > -- > > add_missing_from| off | > Automatically adds missing table references to FROM clauses. > > allow_system_table_mods | off | > Allows modifications of the structure of system tables. > > archive_command | (disabled)| > Sets the shell command that will be called to archive a WAL file. > > archive_mode| off | > Allows archiving of WAL files using archive_command. > > archive_timeout | 0 | > Forces a switch to the next xlog file if a new file has not been started > within N seconds. > > array_nulls | on| > Enable input of NULL elements in arrays.
Re: [Spacewalk-list] CPU bound postgresql process ? Why and how to stop it?
Boyd, Robert wrote: % I have several hundred servers in spacewalk. How much time should I allow for a window to run this db maintenance? % % And how does this relate to the bugzilla that Michael mentioned? % % Michael, how long before the spacewalk-java update will move from nightly to production release? We hope we'll be able to release new Spacewalk version sometime in January. % Thank you both for your assistance! % % Robert Regards, -- Michael Mráka Satellite Engineering, Red Hat ___ Spacewalk-list mailing list Spacewalk-list@redhat.com https://www.redhat.com/mailman/listinfo/spacewalk-list
Re: [Spacewalk-list] CPU bound postgresql process ? Why and how to stop it?
Boyd, Robert wrote: % I see this and other spacewalk-java*2.1 rpms on the nightly: % http://spacewalk.redhat.com/yum/nightly/RHEL/6/x86_64/spacewalk-java-postgresql-2.1.90-1.el6.noarch.rpm % % I downloaded this one and it doesn't list any other 2.1 spacewalk % components as dependencies when I do a yum deplist on it. Does that % mean that I can safely update my spacewalk server with this before the % update to spacewalk 2.1 is released? Or do I need the spacewalk-java Unfortunately no. There were other changes e.g. in database schema which java packages depends on. % and spacewalk-java-config and spacewalk-java-lib updates as well? Or % do I just have to wait for 2.1? In this particular case you can either manually apply patches on top of spacewalk-java 2.0 packages and rebuild the package on your own or wait fo the Spacewalk 2.1 release. Regards, -- Michael Mráka Satellite Engineering, Red Hat ___ Spacewalk-list mailing list Spacewalk-list@redhat.com https://www.redhat.com/mailman/listinfo/spacewalk-list
Re: [Spacewalk-list] CPU bound postgresql process ? Why and how to stop it?
What about the config options I think the constraint exclusion would help you because it effectively allows the planner attempt to do the same thing as the patch.-- Sent from my HP Pre3On Dec 11, 2013 16:03, Boyd, Robert wrote: 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-boun...@redhat.com [mailto:spacewalk-list-boun...@redhat.com] On Behalf Of Paul Robert Marino Sent: Monday, December 09, 2013 4:25 PM To: spacewalk-list@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 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 ___ Spacewalk-list mailing list Spacewalk-list@redhat.com https://www.redhat.com/mailman/listinfo/spacewalk-list ___ Spacewalk-list mailing list Spacewalk-list@redhat.com https://www.redhat.com/mailman/listinfo/spacewalk-list
Re: [Spacewalk-list] CPU bound postgresql process ? Why and how to stop it?
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-boun...@redhat.com [mailto:spacewalk-list-boun...@redhat.com] On Behalf Of Paul Robert Marino Sent: Monday, December 09, 2013 4:25 PM To: spacewalk-list@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 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 ___ Spacewalk-list mailing list Spacewalk-list@redhat.com https://www.redhat.com/mailman/listinfo/spacewalk-list
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 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 > > -Original Message- > From: spacewalk-list-boun...@redhat.com > [mailto:spacewalk-list-boun...@redhat.com] On Behalf Of Paul Robert Marino > Sent: Tuesday, December 03, 2013 1:16 PM > To: spacewalk-list@redhat.com > Subject: Re: [Spacewalk-list] CPU bound postgresql process ? Why and how to > stop it? > > I think I know whats going on here. > The autovacuum process in PostgreSQL 8.x was new and wasn't quite perfected > yet. you need to stop spacewalk and do a full vacuum analyze on the database. > chances are your planner stats are out of date. It also may not hurt to do a > reindexdb as well. > I schedule a monthly window to do these procedures to absolutely ensure that > every thing is working optimally. > > here is what I run > " > #!/bin/bash > spacewalk-service stop > export PGUSER=postgres > export PGPASSWORD=password > vacuumdb -azvf > reindexdb -a > vacuumdb -a > spacewalk-service start > " > > > On Tue, Dec 3, 2013 at 11:52 AM, Boyd, Robert > wrote: >> Charles, Here's what I found associated with the CPU bound process: >> -- so, what is this being triggered by and what is it doing? Why is it >> taking so long? And is it ever going to finish? >> >> >> >> spaceschema=# select * from pg_stat_activity; >> >> datid | datname | procpid | usesysid | usename | >> current_query >> >> | waiting | xact_start | >> query_start | backend_start | client_addr | client >> >> _port >> >> >> >> 16384 | spaceschema | 15809 |16388 | spaceuser | SELECT DISTINCT >> snv.server_id AS server_id, S.name, S.release, SA.name as arch, >> >> | f
Re: [Spacewalk-list] CPU bound postgresql process ? Why and how to stop it?
Michael Mráka, I see this and other spacewalk-java*2.1 rpms on the nightly: http://spacewalk.redhat.com/yum/nightly/RHEL/6/x86_64/spacewalk-java-postgresql-2.1.90-1.el6.noarch.rpm I downloaded this one and it doesn't list any other 2.1 spacewalk components as dependencies when I do a yum deplist on it. Does that mean that I can safely update my spacewalk server with this before the update to spacewalk 2.1 is released? Or do I need the spacewalk-java and spacewalk-java-config and spacewalk-java-lib updates as well? Or do I just have to wait for 2.1? Thank you, Robert -Original Message- From: spacewalk-list-boun...@redhat.com [mailto:spacewalk-list-boun...@redhat.com] On Behalf Of Boyd, Robert Sent: Monday, December 09, 2013 2:27 PM To: spacewalk-list@redhat.com Subject: Re: [Spacewalk-list] CPU bound postgresql process ? Why and how to stop it? Michael, Do you have any idea when the fix will be escaping into the production updates? I presume from the bugzilla track that it's already in the nightly packages. Thanks! Robert -Original Message- From: spacewalk-list-boun...@redhat.com [mailto:spacewalk-list-boun...@redhat.com] On Behalf Of Michael Mraka Sent: Tuesday, December 03, 2013 1:54 PM To: spacewalk-list@redhat.com Subject: Re: [Spacewalk-list] CPU bound postgresql process ? Why and how to stop it? Boyd, Robert wrote: % Charles, Here's what I found associated with the CPU bound process: -- so, what is this being triggered by and what is it doing? Why is it taking so long? And is it ever going to finish? % Hello Robert, you've most likely hit https://bugzilla.redhat.com/show_bug.cgi?id=1022279. Regards, -- Michael Mráka Satellite Engineering, Red Hat ___ Spacewalk-list mailing list Spacewalk-list@redhat.com https://www.redhat.com/mailman/listinfo/spacewalk-list ___ Spacewalk-list mailing list Spacewalk-list@redhat.com https://www.redhat.com/mailman/listinfo/spacewalk-list ___ Spacewalk-list mailing list Spacewalk-list@redhat.com https://www.redhat.com/mailman/listinfo/spacewalk-list
Re: [Spacewalk-list] CPU bound postgresql process ? Why and how to stop it?
Michael, Do you have any idea when the fix will be escaping into the production updates? I presume from the bugzilla track that it's already in the nightly packages. Thanks! Robert -Original Message- From: spacewalk-list-boun...@redhat.com [mailto:spacewalk-list-boun...@redhat.com] On Behalf Of Michael Mraka Sent: Tuesday, December 03, 2013 1:54 PM To: spacewalk-list@redhat.com Subject: Re: [Spacewalk-list] CPU bound postgresql process ? Why and how to stop it? Boyd, Robert wrote: % Charles, Here's what I found associated with the CPU bound process: -- so, what is this being triggered by and what is it doing? Why is it taking so long? And is it ever going to finish? % Hello Robert, you've most likely hit https://bugzilla.redhat.com/show_bug.cgi?id=1022279. Regards, -- Michael Mráka Satellite Engineering, Red Hat ___ Spacewalk-list mailing list Spacewalk-list@redhat.com https://www.redhat.com/mailman/listinfo/spacewalk-list ___ Spacewalk-list mailing list Spacewalk-list@redhat.com https://www.redhat.com/mailman/listinfo/spacewalk-list
Re: [Spacewalk-list] CPU bound postgresql process ? Why and how to stop it?
I have several hundred servers in spacewalk. How much time should I allow for a window to run this db maintenance? And how does this relate to the bugzilla that Michael mentioned? Michael, how long before the spacewalk-java update will move from nightly to production release? Thank you both for your assistance! Robert -Original Message- From: spacewalk-list-boun...@redhat.com [mailto:spacewalk-list-boun...@redhat.com] On Behalf Of Paul Robert Marino Sent: Tuesday, December 03, 2013 1:16 PM To: spacewalk-list@redhat.com Subject: Re: [Spacewalk-list] CPU bound postgresql process ? Why and how to stop it? I think I know whats going on here. The autovacuum process in PostgreSQL 8.x was new and wasn't quite perfected yet. you need to stop spacewalk and do a full vacuum analyze on the database. chances are your planner stats are out of date. It also may not hurt to do a reindexdb as well. I schedule a monthly window to do these procedures to absolutely ensure that every thing is working optimally. here is what I run " #!/bin/bash spacewalk-service stop export PGUSER=postgres export PGPASSWORD=password vacuumdb -azvf reindexdb -a vacuumdb -a spacewalk-service start " On Tue, Dec 3, 2013 at 11:52 AM, Boyd, Robert wrote: > Charles, Here's what I found associated with the CPU bound process: > -- so, what is this being triggered by and what is it doing? Why is it > taking so long? And is it ever going to finish? > > > > spaceschema=# select * from pg_stat_activity; > > datid | datname | procpid | usesysid | usename | > current_query > > | waiting | xact_start | > query_start | backend_start | client_addr | client > > _port > > > > 16384 | spaceschema | 15809 |16388 | spaceuser | SELECT DISTINCT > snv.server_id AS server_id, S.name, S.release, SA.name as arch, > > | f | 2013-12-02 13:30:00.7034-05 | > 2013-12-03 11:45:02.564862-05 | 2013-12-02 12:43:22.424659-05 | > 127.0.0.1 > | > > 43711 > > : urn.user_id > > > > : FROM ( > > > > : -- > > > > : select > rhnChannelErrata.errata_id, rhnChannelErrata.channel_id, > rhnServerChannel.server_id, rhnE > > rrataPackage.package_id > > : from > rhnChannelErrata, rhnErrataPackage, rhnChannelNewestPackage, > rhnPackageEVR, > > > > : > rhnServerChannel, rhnServerPackage, rhnPackageUpgradeArchCompat > > > > : where > rhnChannelErrata.errata_id = rhnErrataPackage.errata_id > > > > : -- > > > > : and > rhnChannelErrata.channel_id = rhnChannelNewestPackage.channel_id > > > > : and > rhnErrataPackage.package_id = rhnChannelNewestPackage.package_id > > > > : -- > > > > : and > rhnChannelErrata.channel_id = rhnServerChannel.channel_id > > > > : and > rhnChannelNewestPackage.name_id = rhnServerPackage.name_id > > > > : and > rhnServerChannel.server_id = rhnServerPackage.server_id > > > > : -- > > > > : and > rhnChannelNewestPackage.evr_id = rhnPackageEVR.id > > > > : -- > > > > : and > rhnServerPackage.package_arch_id = > rhnPackageUpgradeArchCompat.package_arch_id > > > > : and > rhnPackageUpgradeArchCompat.pack > > > > From: spacewalk-list-boun...@redhat.com > [mailto:spacewalk-list-boun...@redhat.com] On Behalf Of Charles > Richards > Sent: Tuesday, December 03, 2013 11:18 AM > To: spacewalk-list@redhat.com > Subject: Re: [Spacewalk-list] CPU bound postgresql process ? Why and > how to stop it? > > > > You can check what postgresql is doing by
Re: [Spacewalk-list] CPU bound postgresql process ? Why and how to stop it?
Boyd, Robert wrote: % Charles, Here's what I found associated with the CPU bound process: -- so, what is this being triggered by and what is it doing? Why is it taking so long? And is it ever going to finish? % Hello Robert, you've most likely hit https://bugzilla.redhat.com/show_bug.cgi?id=1022279. Regards, -- Michael Mráka Satellite Engineering, Red Hat ___ Spacewalk-list mailing list Spacewalk-list@redhat.com https://www.redhat.com/mailman/listinfo/spacewalk-list
Re: [Spacewalk-list] CPU bound postgresql process ? Why and how to stop it?
I think I know whats going on here. The autovacuum process in PostgreSQL 8.x was new and wasn't quite perfected yet. you need to stop spacewalk and do a full vacuum analyze on the database. chances are your planner stats are out of date. It also may not hurt to do a reindexdb as well. I schedule a monthly window to do these procedures to absolutely ensure that every thing is working optimally. here is what I run " #!/bin/bash spacewalk-service stop export PGUSER=postgres export PGPASSWORD=password vacuumdb -azvf reindexdb -a vacuumdb -a spacewalk-service start " On Tue, Dec 3, 2013 at 11:52 AM, Boyd, Robert wrote: > Charles, Here’s what I found associated with the CPU bound process: -- so, > what is this being triggered by and what is it doing? Why is it taking so > long? And is it ever going to finish? > > > > spaceschema=# select * from pg_stat_activity; > > datid | datname | procpid | usesysid | usename | > current_query > > | waiting | xact_start | > query_start | backend_start | client_addr | client > > _port > > > > 16384 | spaceschema | 15809 |16388 | spaceuser | SELECT DISTINCT > snv.server_id AS server_id, S.name, S.release, SA.name as arch, > > | f | 2013-12-02 13:30:00.7034-05 | > 2013-12-03 11:45:02.564862-05 | 2013-12-02 12:43:22.424659-05 | 127.0.0.1 > | > > 43711 > > : urn.user_id > > > > : FROM ( > > > > : -- > > > > : select > rhnChannelErrata.errata_id, rhnChannelErrata.channel_id, > rhnServerChannel.server_id, rhnE > > rrataPackage.package_id > > : from > rhnChannelErrata, rhnErrataPackage, rhnChannelNewestPackage, rhnPackageEVR, > > > > : > rhnServerChannel, rhnServerPackage, rhnPackageUpgradeArchCompat > > > > : where > rhnChannelErrata.errata_id = rhnErrataPackage.errata_id > > > > : -- > > > > : and > rhnChannelErrata.channel_id = rhnChannelNewestPackage.channel_id > > > > : and > rhnErrataPackage.package_id = rhnChannelNewestPackage.package_id > > > > : -- > > > > : and > rhnChannelErrata.channel_id = rhnServerChannel.channel_id > > > > : and > rhnChannelNewestPackage.name_id = rhnServerPackage.name_id > > > > : and > rhnServerChannel.server_id = rhnServerPackage.server_id > > > > : -- > > > > : and > rhnChannelNewestPackage.evr_id = rhnPackageEVR.id > > > > : -- > > > > : and > rhnServerPackage.package_arch_id = > rhnPackageUpgradeArchCompat.package_arch_id > > > > : and > rhnPackageUpgradeArchCompat.pack > > > > From: spacewalk-list-boun...@redhat.com > [mailto:spacewalk-list-boun...@redhat.com] On Behalf Of Charles Richards > Sent: Tuesday, December 03, 2013 11:18 AM > To: spacewalk-list@redhat.com > Subject: Re: [Spacewalk-list] CPU bound postgresql process ? Why and how to > stop it? > > > > You can check what postgresql is doing by running the following query as the > postgres user: > > > > select * from pg_stat_activity; > > > > I've seen this behavior on my instance when the errata_cache task runs, but > have not found a resolution for it (running SW 1.9 / PG 8.4.13) > > > > > > On Dec 2, 2013, at 1:18 PM, Boyd, Robert > wrote: > > > > I’m running spacewalk 2.0 on RHEL 6.4 with database local postgresql 8.4 all > patched current. In recent days I’m seeing one or more very busy processes > like this one: > > > > 15809 postgres 20 0 1161m 256m 225m R 99.3 6.7 64:35.98 postgres: > spaceu
Re: [Spacewalk-list] CPU bound postgresql process ? Why and how to stop it?
You can check what postgresql is doing by running the following query as the
postgres user:
select * from pg_stat_activity;
I've seen this behavior on my instance when the errata_cache task runs, but
have not found a resolution for it (running SW 1.9 / PG 8.4.13)
On Dec 2, 2013, at 1:18 PM, Boyd, Robert wrote:
> I’m running spacewalk 2.0 on RHEL 6.4 with database local postgresql 8.4 all
> patched current. In recent days I’m seeing one or more very busy processes
> like this one:
>
> 15809 postgres 20 0 1161m 256m 225m R 99.3 6.7 64:35.98 postgres:
> spaceuser spaceschema 127.0.0.1(43711) SELECT
>
> The server has been CPU bound since about 11/21/2013. I’ve tried
> stopping/restarting spacewalk and postgresql, but as soon as both get fully
> started one postgresql process goes CPU bound.
>
> Here’s a CPU usage graph from a couple of days before through a couple of
> days after the problem began.
>
>
>
> How can I begin to find out what is going on with spacewalk and the database
> that is keeping it busy like this?
>
> I tried looking in the /var/log files for something at the system level but
> there just isn’t anything noteworthy in the time interval where this problem
> began.
>
> In the pgsql logs (e.g. /var/lib/pgsql/data/pg_log/postgresql-Sun.log) I see
> a lot of messages like these:
>
> LOG: unexpected EOF on client connection
> LOG: unexpected EOF on client connection
> LOG: automatic analyze of table "spaceschema.public.qrtz_triggers" system
> usage: CPU 0.00s/0.00u sec elapsed 0.18 sec
> LOG: unexpected EOF on client connection
> LOG: automatic vacuum of table "spaceschema.public.qrtz_triggers": index
> scans: 0
> pages: 0 removed, 1421 remain
> tuples: 0 removed, 30797 remain
> system usage: CPU 0.17s/0.03u sec elapsed 2.33 sec
> LOG: unexpected EOF on client connection
> LOG: automatic analyze of table "spaceschema.public.qrtz_cron_triggers"
> system usage: CPU 0.00s/0.00u sec elapsed 0.08 sec
> LOG: automatic vacuum of table "spaceschema.public.qrtz_cron_triggers":
> index scans: 0
> pages: 0 removed, 786 remain
> tuples: 0 removed, 30856 remain
> system usage: CPU 0.00s/0.00u sec elapsed 0.15 sec
> LOG: unexpected EOF on client connection
> LOG: unexpected EOF on client connection
> ERROR: -20243 : (package_arch_not_found) - Package architecture could not be
> found
> CONTEXT: SQL statement "SELECT
> rhn_exception.raise_exception('package_arch_not_found')"
> PL/pgSQL function "lookup_package_arch" line 14 at PERFORM
> STATEMENT:
> insert into rhnServerPackage
> (server_id, name_id, evr_id, package_arch_id, installtime)
> values (110465,
> LOOKUP_PACKAGE_NAME(E'keyutils-libs'), LOOKUP_EVR(NULL, E'1.0', E'2'),
> LOOKUP_PACKAGE_ARCH(E''), TO_TIMESTAMP(NULL,
> '-MM-DD HH24:MI:SS')
> )
>
> LOG: unexpected EOF on client connection
> LOG: unexpected EOF on client connection
> LOG: unexpected EOF on client connection
> LOG: unexpected EOF on client connection
> LOG: unexpected EOF on client connection
> LOG: unexpected EOF on client connection
> LOG: unexpected EOF on client connection
> LOG: unexpected EOF on client connection
> LOG: unexpected EOF on client connection
> LOG: unexpected EOF on client connection
> LOG: unexpected EOF on client connection
> LOG: unexpected EOF on client connection
> LOG: unexpected EOF on client connection
> LOG: unexpected EOF on client connection
>
> Robert Boyd
> Senior Systems Engineer | Peoplefluent
> p. 919-645-2972 | c. 919-306-4681
> e. robert.b...@peoplefluent.com
> Visit: www.peoplefluent.com | Read: Peoplefluent Blog
> Follow: @peoplefluent | Download: iPad App
>
>
>
> ___
> Spacewalk-list mailing list
> Spacewalk-list@redhat.com
> https://www.redhat.com/mailman/listinfo/spacewalk-list
___
Spacewalk-list mailing list
Spacewalk-list@redhat.com
https://www.redhat.com/mailman/listinfo/spacewalk-list
