Re: [Spacewalk-list] Query Continuously Running
On Tue, Jan 24, 2012 at 09:18:16PM +, Wojtak, Greg (Superfly) wrote: > When I run an explain and analyze, I get: > spaceschema=# EXPLAIN DELETE FROM rhnPackageChangeLogData WHERE id NOT IN ( > SELECT changelog_data_id FROM rhnPackageChangeLogRec ); > QUERY PLAN > > --- > Seq Scan on rhnpackagechangelogdata (cost=126110.35..9410672444.95 > rows=113191 width=6) >Filter: (NOT (SubPlan 1)) >SubPlan 1 > -> Materialize (cost=126110.35..196580.21 rows=5067486 width=8) >-> Seq Scan on rhnpackagechangelogrec (cost=0.00..101247.86 > rows=5067486 width=8) > (5 rows) > > spaceschema=# ANALYZE rhnPackageChangeLogData; > ANALYZE > spaceschema=# ANALYZE rhnPackageChangeLogRec; > ANALYZE And what is the explain plan after these analyzes? -- Jan Pazdziora Principal Software Engineer, Satellite Engineering, Red Hat ___ Spacewalk-list mailing list Spacewalk-list@redhat.com https://www.redhat.com/mailman/listinfo/spacewalk-list
Re: [Spacewalk-list] Query Continuously Running
When I run an explain and analyze, I get: spaceschema=# EXPLAIN DELETE FROM rhnPackageChangeLogData WHERE id NOT IN ( SELECT changelog_data_id FROM rhnPackageChangeLogRec ); QUERY PLAN --- Seq Scan on rhnpackagechangelogdata (cost=126110.35..9410672444.95 rows=113191 width=6) Filter: (NOT (SubPlan 1)) SubPlan 1 -> Materialize (cost=126110.35..196580.21 rows=5067486 width=8) -> Seq Scan on rhnpackagechangelogrec (cost=0.00..101247.86 rows=5067486 width=8) (5 rows) spaceschema=# ANALYZE rhnPackageChangeLogData; ANALYZE spaceschema=# ANALYZE rhnPackageChangeLogRec; ANALYZE I noticed about a week ago that I haven't been seeing these except every once in a while after I deleted our Solaris systems, channels, patch clusters and disabled Solaris support. I'm wondering if that was related at all. -Original Message- From: spacewalk-list-boun...@redhat.com [mailto:spacewalk-list-boun...@redhat.com] On Behalf Of Jan Pazdziora Sent: Monday, January 16, 2012 2:17 PM To: spacewalk-list@redhat.com Subject: Re: [Spacewalk-list] Query Continuously Running On Thu, Jan 12, 2012 at 03:41:39PM +, Wojtak, Greg (Superfly) wrote: > Spacewalk 1.5 (PostgreSQL) on Cent 6 > > I've been seeing a process keep popping up that uses as much cpu as it can > get. It appears to be a delete transaction. I've run the following query in > psql: > > select current_query from pg_stat_activity where current_query <> '' > and current_query not like '%pg_stat_activity%'; > > It appears that the query that is hanging is: > > DELETE FROM rhnPackageChangeLogData > WHERE id NOT IN ( SELECT changelog_data_id FROM > rhnPackageChangeLogRec ) > > Any insight into this? What does EXPLAIN say about the query? Do things change if you ANALYZE the tables? -- Jan Pazdziora Principal Software Engineer, Satellite Engineering, Red Hat ___ Spacewalk-list mailing list Spacewalk-list@redhat.com https://www.redhat.com/mailman/listinfo/spacewalk-list ___ Spacewalk-list mailing list Spacewalk-list@redhat.com https://www.redhat.com/mailman/listinfo/spacewalk-list
Re: [Spacewalk-list] Query Continuously Running
On Thu, Jan 12, 2012 at 03:41:39PM +, Wojtak, Greg (Superfly) wrote: > Spacewalk 1.5 (PostgreSQL) on Cent 6 > > I've been seeing a process keep popping up that uses as much cpu as it can > get. It appears to be a delete transaction. I've run the following query in > psql: > > select current_query from pg_stat_activity where current_query <> '' > and current_query not like '%pg_stat_activity%'; > > It appears that the query that is hanging is: > > DELETE FROM rhnPackageChangeLogData > WHERE id NOT IN ( SELECT changelog_data_id FROM > rhnPackageChangeLogRec ) > > Any insight into this? What does EXPLAIN say about the query? Do things change if you ANALYZE the tables? -- Jan Pazdziora Principal Software Engineer, Satellite Engineering, Red Hat ___ Spacewalk-list mailing list Spacewalk-list@redhat.com https://www.redhat.com/mailman/listinfo/spacewalk-list
[Spacewalk-list] Query Continuously Running
Spacewalk 1.5 (PostgreSQL) on Cent 6 I've been seeing a process keep popping up that uses as much cpu as it can get. It appears to be a delete transaction. I've run the following query in psql: select current_query from pg_stat_activity where current_query <> '' and current_query not like '%pg_stat_activity%'; It appears that the query that is hanging is: DELETE FROM rhnPackageChangeLogData WHERE id NOT IN ( SELECT changelog_data_id FROM rhnPackageChangeLogRec ) Any insight into this? Thanks, Greg ___ Spacewalk-list mailing list Spacewalk-list@redhat.com https://www.redhat.com/mailman/listinfo/spacewalk-list
