[Spacewalk-list] CPU bound postgresql process ? Why and how to stop it?
Boyd, Robert
Robert.Boyd at peoplefluent.com
Wed Dec 4 16:21:53 UTC 2013
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-bounces at redhat.com [mailto:spacewalk-list-bounces at redhat.com] On Behalf Of Paul Robert Marino
Sent: Tuesday, December 03, 2013 1:16 PM
To: spacewalk-list at 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 <Robert.Boyd at peoplefluent.com> 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-bounces at redhat.com
> [mailto:spacewalk-list-bounces at redhat.com] On Behalf Of Charles
> Richards
> Sent: Tuesday, December 03, 2013 11:18 AM
> To: spacewalk-list at 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
> <Robert.Boyd at peoplefluent.com>
> 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.
>
>
>
> <image005.jpg>
>
>
>
> 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 (1000010465,
> LOOKUP_PACKAGE_NAME(E'keyutils-libs'), LOOKUP_EVR(NULL, E'1.0', E'2'),
>
> LOOKUP_PACKAGE_ARCH(E''), TO_TIMESTAMP(NULL,
> 'YYYY-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.Boyd at peoplefluent.com
>
> Visit: www.peoplefluent.com | Read: Peoplefluent Blog
>
> Follow: @peoplefluent | Download: iPad App
>
> <image001.png>
>
> <image002.png>
>
>
>
> _______________________________________________
> Spacewalk-list mailing list
> Spacewalk-list at redhat.com
> https://www.redhat.com/mailman/listinfo/spacewalk-list
>
>
>
>
> _______________________________________________
> Spacewalk-list mailing list
> Spacewalk-list at redhat.com
> https://www.redhat.com/mailman/listinfo/spacewalk-list
_______________________________________________
Spacewalk-list mailing list
Spacewalk-list at redhat.com
https://www.redhat.com/mailman/listinfo/spacewalk-list
More information about the Spacewalk-list
mailing list