Well I'm running PostgreSQL 9.x on all of my database servers so my plan looks way different than your for instance my servers don't need to do all of those sorts, so its not a viable comparison. that said the numbers are nothing like yours my servers chomp through that query reasonably quickly. Also Ive seen PostgreSQL 8.4 handle more complex operations on 1TB tables much more efficiently than how your box is handling things.
I suspect your issue is the setting for work_mem are too low to handle the dataset without resorting to using a temporary directory to swap the data in and out of memory. Additionally in PostgreSQL 9.x effective cache auto tunes to 1/3 of your ram which is fine for most users, but in 8.x you still need to statically set it in the config. Also load can be deceptive on modern systems I rarely use it as a benchmark any more. the intresting thing to know is what does sar say about your system during those queries.further more which scheduler are you using for the disks if you are using CFQ which is the default on Red Hat your IO performance can be somewhat against PostgreSQL in favor of other applications because PostgreSQL eats so much IO I always advise the deadline shceduler for physical servers and noop for virtual ones. the finally question is by any chance can it have been a really long time since that query was executed successfully, in which case its probably got a lot of cleanup to take care of. On Mon, Jun 8, 2015 at 1:33 PM, Gerald Vogt <[email protected]> wrote: > vacuum analyze is the first thing I do when I see problems on a > postgresql database... > > The database is running on the spacewalk server but it doesn't show too > much load at any time nor is it slow on the disks. > > The estimate of explain verbose don't look too promising to me. > > What do you get for > > explain verbose DELETE FROM rhnPackageChangeLogData WHERE id NOT IN ( > SELECT DISTINCT changelog_data_id FROM rhnPackageChangeLogRec ); > > ?? > > Thanks, > > Gerald > > On 08.06.15 19:05, Paul Robert Marino wrote: >> 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 >> > > _______________________________________________ > 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
