[Spacewalk-list] CPU bound postgresql process ? Why and how to stop it?
Paul Robert Marino
prmarino1 at gmail.com
Tue Dec 3 18:15:56 UTC 2013
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
More information about the Spacewalk-list
mailing list