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 <[email protected]> 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: [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 _______________________________________________ Spacewalk-list mailing list [email protected] https://www.redhat.com/mailman/listinfo/spacewalk-list
