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: [email protected] [mailto:[email protected]] On Behalf Of Paul Robert Marino Sent: Tuesday, December 03, 2013 1:16 PM To: [email protected] 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 <[email protected]> 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: [email protected] > [mailto:[email protected]] On Behalf Of Charles > Richards > Sent: Tuesday, December 03, 2013 11:18 AM > To: [email protected] > 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 > <[email protected]> > 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. [email protected] > > Visit: www.peoplefluent.com | Read: Peoplefluent Blog > > Follow: @peoplefluent | Download: iPad App > > <image001.png> > > <image002.png> > > > > _______________________________________________ > Spacewalk-list mailing list > [email protected] > https://www.redhat.com/mailman/listinfo/spacewalk-list > > > > > _______________________________________________ > Spacewalk-list mailing list > [email protected] > https://www.redhat.com/mailman/listinfo/spacewalk-list _______________________________________________ Spacewalk-list mailing list [email protected] https://www.redhat.com/mailman/listinfo/spacewalk-list _______________________________________________ Spacewalk-list mailing list [email protected] https://www.redhat.com/mailman/listinfo/spacewalk-list
