looks like your database isn't tuned correctly or you desperately need to run a vacuum analyze on it. check your disks too. This is most likely a problem with your database servers, configuration, maintenance, or hardware.
On Mon, Jun 8, 2015 at 11:07 AM, Gerald Vogt <[email protected]> wrote: > Does anyone have an idea what I could do about this? > > Does anyone see something similar? > > Thanks, > > Gerald > On 28.05.15 06:57, Gerald Vogt wrote: >> Eventually cleanup-data-bunch finished after 8:40 h. Definitively not >> suitable to run daily. >> >> I don't quite understand why the query runs with two sequential scans >> and does not use the indexes. >> >> Does anyone have an idea how to optimize that? Is that always taking so >> long? I am using CentOS 6.6, Spacewalk 2.3, PostgreSQL 8.4.20. >> >> For now, I'll schedule that task monthly... >> >> Thanks, >> >> Gerald >> >> On 27.05.15 13:39, Gerald Vogt wrote: >>> Hi! >>> >>> It seems cleanup-data-bunch takes a very long time. >>> >>> I have noticed that it was in state "INTERRUPTED" and did not run for a >>> couple of months. I have applied the changes as suggested in >>> >>> https://www.redhat.com/archives/spacewalk-list/2015-May/msg00091.html >>> >>> and now cleanup-data-bunch does run again. It's however very slow. >>> Currently, it is already running for 2:15 h and still goes on. >>> >>> I can see with pg_stat_activity that is running this query: >>> >>> DELETE FROM rhnPackageChangeLogData >>> WHERE id NOT IN ( SELECT DISTINCT changelog_data_id FROM >>> rhnPackageChangeLogRec ) >>> >>> which seems to keep the database busy for a while: >>> >>>> spacewalk=# explain verbose DELETE FROM rhnPackageChangeLogData WHERE id >>>> NOT IN ( SELECT DISTINCT changelog_data_id FROM rhnPackageChangeLogRec ); >>>> QUERY PLAN >>>> ----------------------------------------------------------------------------------------------------------- >>>> Seq Scan on rhnpackagechangelogdata (cost=803563.75..215621093.82 >>>> rows=201534 width=6) >>>> Output: >>>> Filter: (NOT (SubPlan 1)) >>>> SubPlan 1 >>>> -> Materialize (cost=803563.75..804467.22 rows=64947 width=8) >>>> Output: rhnpackagechangelogrec.changelog_data_id >>>> -> Unique (cost=781080.12..803244.80 rows=64947 width=8) >>>> Output: rhnpackagechangelogrec.changelog_data_id >>>> -> Sort (cost=781080.12..792162.46 rows=4432935 width=8) >>>> Output: rhnpackagechangelogrec.changelog_data_id >>>> Sort Key: rhnpackagechangelogrec.changelog_data_id >>>> -> Seq Scan on rhnpackagechangelogrec >>>> (cost=0.00..109859.35 rows=4432935 width=8) >>>> Output: >>>> rhnpackagechangelogrec.changelog_data_id >>>> (13 rows) >>>> >>>> spacewalk=# select count(*) from rhnpackagechangelogrec; >>>> count >>>> --------- >>>> 5112795 >>>> (1 row) >>>> >>>> spacewalk=# select count(*) from rhnpackagechangelogdata; >>>> count >>>> -------- >>>> 401000 >>>> (1 row) >>>> >>> >>> If it is taking this long I guess there is no point scheduling it daily. >>> >>> Any thoughts? >>> >>> Thanks, >>> >>> Gerald >>> >>> >>> _______________________________________________ >>> 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
