[Spacewalk-list] cleanup-data-bunch takes forever

[Gerald Vogt]

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 <vogt at spamcop.net> 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
>>>> 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
>>>
>>
>> _______________________________________________
>> 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
>