[ADMIN] long running delete

[Mark Steben]

Good morning,

We have been running a delete for nearly 24 hours now.  I would like to verify that it is either doing what it is supposed to do or 'spinning its wheels'.  

We are running postgres 9.2.12.

The delete statement is not waiting on any other transaction.

I have run straces on the pid and I see lots of 'reads, lseeks, and an occasional semop.  I have also looked in the base directory at the file matched by the oid of the table (as defined in pg_class) and have seen no change in size.

Is there somewhere else I can verify that work is / is not being done?  Perhaps looking for something else in strace?

Thanks for your time.

--

Mark Steben
 Database Administrator
@utoRevenue | Autobase 
  CRM division of Dominion Dealer Solutions 
95D Ashley Ave.
West Springfield, MA 01089
t: 413.327-3045
f: 413.383-9567

www.fb.com/DominionDealerSolutions
www.twitter.com/DominionDealer
 www.drivedominion.com

[Gilberto Castillo]

> Good morning,
>
> We have been running a delete for nearly 24 hours now. I would like to
> verify that it is either doing what it is supposed to do or 'spinning its
> wheels'.
>
> We are running postgres 9.2.12.
>
> The delete statement is not waiting on any other transaction.
>
> I have run straces on the pid and I see lots of 'reads, lseeks, and an
> occasional semop. I have also looked in the base directory at the file
> matched by the oid of the table (as defined in pg_class) and have seen no
> change in size.
>
> Is there somewhere else I can verify that work is / is not being done?
> Perhaps looking for something else in strace?

My recomendation,

El DELETE es prohibitivo en Cualquier gestor de BD para ello ponga a su
concideración dos formas de como mejorar su comportamiento en PostgreSQL

--Solución 1

DELETE FROM string s WHERE NOT EXISTS (SELECT 1 FROM data d WHERE
d.object_id = s.id OR d.property_id = s.id OR d.value_id = s.id);

--Solución 2

(1) CREATE TABLE copia AS SELECT (...) WHERE (...)
(2) TRUNCATE en la tabla original.
(3) INSERT (...) SELECT (...) --actualizar desde la copia la tabla original
(4) DROP TABLE copia.

>
> Thanks for your time.
>
> --

> *Mark Steben*
> Database Administrator
> @utoRevenue <http://www.autorevenue.com/> | Autobase
> <http://www.autobase.net/>

> CRM division of Dominion Dealer Solutions
> 95D Ashley Ave.
> West Springfield, MA 01089
> t: 413.327-3045
> f: 413.383-9567
>
> www.fb.com/DominionDealerSolutions
> www.twitter.com/DominionDealer

> www.drivedominion.com <http://www.autorevenue.com/>
>
> <http://autobasedigital.net/marketing/DD12_sig.jpg>
>


--
Saludos,
Gilberto Castillo
ETECSA, La Habana, Cuba



--
Sent via pgsql-admin mailing list (pgsql...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

[Mark Steben]

Thank you for your quick response Gilberto. Much appreciated.

I may very well follow your recommendation if I can determine if the current delete statement is not doing any work.

Here is the statement and an explain:

 DELETE FROM    contents USING    contents AS c   LEFT JOIN contents_social_posts csp ON csp.content_id = c.id   LEFT JOIN social_posts sp ON sp.id = csp.social_post_id WHERE    c.type = 'Content::Text::News'   AND (csp.id IS NULL OR sp.id IS NULL);

                                               QUERY PLAN                                               

--------------------------------------------------------------------------------------------------------

 Delete on contents  (cost=1150608.37..2295927.20 rows=1 width=24)

   ->  Nested Loop  (cost=1150608.37..2295927.20 rows=1 width=24)

         ->  Hash Right Join  (cost=1150608.37..1173423.64 rows=1 width=18)

               Hash Cond: (csp.content_id = c.id)

               Filter: ((csp.id IS NULL) OR (sp.id IS NULL))

               ->  Hash Left Join  (cost=6247.16..8286.32 rows=54372 width=24)

                     Hash Cond: (csp.social_post_id = sp.id)

                     ->  Seq Scan on contents_social_posts csp  (cost=0.00..951.72 rows=54372 width=18)

                     ->  Hash  (cost=4978.18..4978.18 rows=101518 width=10)

                           ->  Seq Scan on social_posts sp  (cost=0.00..4978.18 rows=101518 width=10)

               ->  Hash  (cost=1075343.73..1075343.73 rows=3970439 width=10)

                     ->  Seq Scan on contents c  (cost=0.00..1075343.73 rows=3970439 width=10)

                           Filter: ((type)::text = 'Content::Text::News'::text)

         ->  Seq Scan on contents  (cost=0.00..1059623.78 rows=6287978 width=6)

(14 rows)

As you can see, many sequential scans especially 2 on the table we are performing the delete on

Description of contents:

 \d contents

                                       Table "public.contents"

    Column     |            Type             |                       Modifiers                       

---------------+-----------------------------+-------------------------------------------------------

 id            | integer                     | not null default nextval('contents_id_seq'::regclass)

 raw_content   | text                        | 

 title         | text                        | 

 description   | text                        | 

 source_url    | text                        | 

 published_at  | timestamp without time zone | 

 guid          | text                        | 

 type          | character varying(255)      | 

 created_at    | timestamp without time zone | 

 updated_at    | timestamp without time zone | 

 image_url     | text                        | 

 original_id   | character varying(255)      | 

 refined_date  | date                        | 

 thumbnail_url | text                        | 

 user_id       | integer                     | 

 flagged_at    | timestamp without time zone | 

 flagged_by    | integer                     | 

 removed_at    | timestamp without time zone | 

 removed_by    | integer                     | 

 category_id   | integer                     | 

 parent_id     | integer                     | 

 parent_type   | character varying(255)      | 

 processing    | boolean                     | default false

 sharable_id   | integer                     | 

 sharable_type | character varying(255)      | 

 meta_data     | text                        | 

 medium_url    | text                        | 

 pinned_at     | date                        | 

 pinned_until  | date                        | 

 banner_url    | text                        | 

 deleted_at    | timestamp without time zone | 

Indexes:

    "primets_contents_pkey_id" PRIMARY KEY, btree (id), tablespace "prime2indexes"

    "primets_content_parent" btree (parent_id, parent_type), tablespace "prime2indexes"

    "primets_contents_category" btree (category_id), tablespace "prime2indexes"

    "primets_contents_desc_gin" gin (to_tsvector('english'::regconfig, description)), tablespace "prime2indexes"

    "primets_contents_guid" btree (guid), tablespace "prime2indexes"

    "primets_contents_pin_priority" btree ((GREATEST(refined_date::timestamp without time zone, pinned_until + '1 day'::interval)), id), tablespace "prime2indexes"

    "primets_contents_refined_date_id" btree (refined_date, id), tablespace "prime2indexes"

    "primets_contents_sharable_id_sharable_type" btree (sharable_id, sharable_type), tablespace "prime2indexes"

    "primets_contents_source_gin" gin (to_tsvector('english'::regconfig, source_url)), tablespace "prime2indexes"

    "primets_contents_title_gin" gin (to_tsvector('english'::regconfig, title)), tablespace "prime2indexes"

    "primets_contents_type" btree (type), tablespace "prime2indexes"

    "primets_contents_user_id" btree (user_id), tablespace "prime2indexes"

Triggers:

    _replication_logtrigger AFTER INSERT OR DELETE OR UPDATE ON contents FOR EACH ROW EXECUTE PROCEDURE _replication.logtrigger('_replication', '26', 'k')

    _replication_truncatetrigger BEFORE TRUNCATE ON contents FOR EACH STATEMENT EXECUTE PROCEDURE _replication.log_truncate('26')

Disabled triggers:

    _replication_denyaccess BEFORE INSERT OR DELETE OR UPDATE ON contents FOR EACH ROW EXECUTE PROCEDURE _replication.denyaccess('_replication')

    _replication_truncatedeny BEFORE TRUNCATE ON contents FOR EACH STATEMENT EXECUTE PROCEDURE _replication.deny_truncate()

Mark Steben
 Database Administrator
@utoRevenue | Autobase 

  CRM division of Dominion Dealer Solutions 
95D Ashley Ave.
West Springfield, MA 01089
t: 413.327-3045
f: 413.383-9567

www.fb.com/DominionDealerSolutions
www.twitter.com/DominionDealer
 www.drivedominion.com

[Gilberto Castillo]

----Much index, about the table if general one problem