Обсуждение: long running delete

Поиск

Список

Период

Сортировка

long running delete

От

Mark Steben

Дата:

28 июня 2016 г., 19:10:41

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

Re: [MASSMAIL]long running delete

От

"Gilberto Castillo"

Дата:

28 июня 2016 г., 19:15:26

> 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

Re: [MASSMAIL]long running delete

От

Mark Steben

Дата:

28 июня 2016 г., 19:31:39

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()

On Tue, Jun 28, 2016 at 1:15 PM, Gilberto Castillo <gilberto.castillo@etecsa.cu> wrote:

> 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

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

Re: [MASSMAIL]long running delete

От

"Gilberto Castillo"

Дата:

28 июня 2016 г., 19:53:00

> 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


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


>
> 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()
>
>
>
> On Tue, Jun 28, 2016 at 1:15 PM, Gilberto Castillo <
> gilberto.castillo@etecsa.cu> wrote:
>
>>
>> > 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
>>
>>
>
>
> --
> *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

Вход в личный кабинет

Восстановление пароля

Подтверждение аккаунта

Изменение пароля

Обсуждение: long running delete

long running delete

Re: [MASSMAIL]long running delete

Re: [MASSMAIL]long running delete

Re: [MASSMAIL]long running delete