alter table with high VMEM usage

[lamb gong]

hi,

   I have a empty partition table, and i want alter one column type from varchar(16) --> varchar(64)

   And i get error as follow:

   ERROR:  Canceling query because of high VMEM usage. Used: 7375MB, available 817MB, red zone: 7372MB (runaway_cleaner.c:189)  (seg1 10.0.13.145:40000 pid=1992) (cdbdisp_query.c:542)

   I know that is beause gp_vmem_protect_limit is 8192, my confusion is the table is empty, why alter a column type would cost so much memory？

   By the way, my table have 247 partitions.

   And is there any other way to alter the column type without modify gp_vmem_protect_limit ?

   Thanks!

[Simon Gao]

are you using legacy planner or orca? 

--
You received this message because you are subscribed to the Google Groups "Greenplum Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to gpdb-users+...@greenplum.org.
To post to this group, send email to gpdb-...@greenplum.org.
Visit this group at https://groups.google.com/a/greenplum.org/group/gpdb-users/.
For more options, visit https://groups.google.com/a/greenplum.org/d/optout.

[Luis Macedo]

Hi!

Let me see if I understood correctly:

1-  You have a partitioned table with 247 partitions

2 - The table is empty

3 - You issued an alter table to augment the size of the varchar column

4 - It crashes due to out of memory (reached the vmem limit)

For now (this will be improved as we merge new PG versions) I recommend that you insert select in a new table, rename and drop old one.

Above been said, that should not happen. Can you share you GPDB version, table DDL and the alter table DDL? Maybe some of the guys can try to reproduce it and fix it. You may also open a PR.

Thanks,

Luis Macedo | Sr Platform Architect | Pivotal Inc 

Call Me @ +55 11 97616-6438

Take care of the customers and the rest takes care of itself

---

Pivotal POV Blog

Pivotal Mission: Help Developers Do What They Love

Big Data Problems? Go here!

Latency Issues? No more!

---

--

[lamb gong]

ORCA

postgres=# show optimizer;

 optimizer 

-----------

 on

(1 row)

在 2018年8月7日星期二 UTC+8下午10:36:23，SIMON GAO写道：

[lamb gong]

Thanks for the reply

You understanding is correct

1. the version is oss 5x_stable

2. table ddl is as attached : t_summer

3. alter ddl : alter table t_summer alter column channel type char(64)

在 2018年8月7日星期二 UTC+8下午10:36:54，Luis Macedo写道：

[lamb gong]

By the way, the pg version is 8.3.23

在 2018年8月7日星期二 UTC+8下午10:36:54，Luis Macedo写道：

Hi!

[Venkatesh Raghavan]

Hi All,

Alter table command should not care if it is Orca or Planner.

dump=# set optimizer = off;

SET

dump=#  alter table t_summer alter column channel type char(64)                                                                                                                                             ;

ERROR:  Canceling query because of high VMEM usage. Used: 7375MB, available 817MB, red zone: 7372MB (runaway_cleaner.c:189)  (seg0 192.168.1.24:25432 pid=20693) (cdbdisp_query.c:542)

dump=# set optimizer = on;

SET

dump=#  alter table t_summer alter column channel type char(64);                                                                                                                                            ERROR:  Canceling query because of high VMEM usage. Used: 7375MB, available 817MB, red zone: 7372MB (runaway_cleaner.c:189)  (seg2 192.168.1.24:25434 pid=20695) (cdbdisp_query.c:542)

dump=#

Rob/Jesse/Asim, can you please chime in on this thread.

Regards,

V

ᐧ

To unsubscribe from this group and stop receiving emails from it, send an email to gpdb-users+unsubscribe@greenplum.org.

[lamb gong]

hi,

   Can anybody give some suggestion here？

   
   The  high VMEM usage problem is as expectation or it is a bug?

在 2018年8月7日星期二 UTC+8下午9:29:42，lamb gong写道：

[Luis Macedo]

It's a bug or it's a very bad plan due to lack of stats.

My guess is that this is failing on the master . Try analysing the data dictionary tables. Look for a script on the link below, it will make things easier.

http://gpdb.docs.pivotal.io/5100/admin_guide/managing/maintain.html

Let us know if it works.

--- Sent from my Google Pixel

--

[lamb gong]

I run VACUUM ANALYZE， but it's the same error

postgres=# VACUUM ANALYZE t_summer;

VACUUM

postgres=# alter table t_summer alter column channel type char(64);

ERROR:  Canceling query because of high VMEM usage. Used: 7375MB, available 817MB, red zone: 7372MB (runaway_cleaner.c:189)  (seg1 10.0.13.145:40000 pid=968) (cdbdisp_query.c:542)

在 2018年8月9日星期四 UTC+8下午7:21:58，Luis Macedo写道：

[Luis Macedo]

Hi! 

That is not what I meant. I mean running analyse against the catalog tables. There is a script on the link that iterates over the catalog tables running vacuum analyse.

--- Sent from my Google Pixel

[yuwei...@gmail.com]

Looks like vmem issue here. so you have 800mb virtual memory in system and you setup 8gb vmem protect in greenplum.

runaway_cleaner detects this and kill your query to avoid oom kill. 

Yu-wei Sung

[Kushal Choubay]

Hi Lamb gong, 

Out of 247 child partitions you are using 1 MB  blocksize for 246.  I have changed it to 32KB (default) and ALTER COLUMN worked fine for "t_summer" .

Thanks

Kushal Choubay  |  Principal Customer Engineer  |  650-846-1628 

Support.Pivotal.io  |  Mon-Fri  8:30am to 5:00pm PST  |  1-877-477-2269

     

To unsubscribe from this group and stop receiving emails from it, send an email to gpdb-users+unsubscribe@greenplum.org.

To post to this group, send email to gpdb-...@greenplum.org.
Visit this group at https://groups.google.com/a/greenplum.org/group/gpdb-users/.
For more options, visit https://groups.google.com/a/greenplum.org/d/optout.

--
You received this message because you are subscribed to the Google Groups "Greenplum Users" group.

To unsubscribe from this group and stop receiving emails from it, send an email to gpdb-users+unsubscribe@greenplum.org.

To post to this group, send email to gpdb-...@greenplum.org.
Visit this group at https://groups.google.com/a/greenplum.org/group/gpdb-users/.
For more options, visit https://groups.google.com/a/greenplum.org/d/optout.

--

Yu-wei Sung

--

You received this message because you are subscribed to the Google Groups "Greenplum Users" group.

To unsubscribe from this group and stop receiving emails from it, send an email to gpdb-users+unsubscribe@greenplum.org.

[lamb gong]

I run 

analyzedb -s pg_catalog -d postgres  

Is that what you mean?

But it dons't work

在 2018年8月9日星期四 UTC+8下午10:13:28，Luis Macedo写道：

[Kushal Choubay]

Have you tried changing block size to default 32k ? 

--

Yu-wei Sung

--
You received this message because you are subscribed to the Google Groups "Greenplum Users" group.

To unsubscribe from this group and stop receiving emails from it, send an email to gpdb-users+...@greenplum.org.
To post to this group, send email to gpdb-...@greenplum.org.
Visit this group at https://groups.google.com/a/greenplum.org/group/gpdb-users/.
For more options, visit https://groups.google.com/a/greenplum.org/d/optout.

--

[lamb gong]

It work for me, thanks!

So the problem is setting blocksize too large？Is there any suggestion about how to set the blocksize  parameter?

Thanks!

在 2018年8月10日星期五 UTC+8上午6:24:40，Kushal Choubay写道：

--

Yu-wei Sung

--
You received this message because you are subscribed to the Google Groups "Greenplum Users" group.

[Kushal Choubay]

You can refer below link for more information on choosing storage types : 

http://gpdb.docs.pivotal.io/590/admin_guide/ddl/ddl-storage.html

Thanks

Kushal Choubay  |  Principal Customer Engineer  |  650-846-1628 

Support.Pivotal.io  |  Mon-Fri  8:30am to 5:00pm PST  |  1-877-477-2269

     

To unsubscribe from this group and stop receiving emails from it, send an email to gpdb-users+unsubscribe@greenplum.org.

[lamb gong]

Thanks!

在 2018年8月10日星期五 UTC+8下午2:19:18，Kushal Choubay写道：