query작업시 union all 로 1400건정도를 붙혔더니 ERROR: insufficient memory reserved for statement (memquota.c:228)
에러가 나서 아래내용보고 변수 바꾸었더니 실행은 되는데. 쿼리가 150
set max_statement_mem='3GB';
set statement_mem='2GB';
ERROR: insufficient memory reserved for statement (memquota.c:228)
I 'm getting this error:
ERROR: insufficient memory reserved for statement (memquota.c:228)
when I run this query on my GPCE 4.1.1.1 instance on a table with 760 partitions (one for each day of a couple of years):
select count(1) from mytable;
The same query works on another table with the same data and no partitions.
I can work around the issue by issuing several queries over a limited range in the table and adding up the results. This seems to suggest that the number of partitions is related to the issue. Also, I have two tables with the same date range and number of partitions, one with only 15M rows, and the other with 350M rows. Both have the same issue with the above query - which also suggests that the problem is with the number of partitions. Of course, someone will suggest "reduce partitions". Even if I increase the size of the partitions, we'll eventually have this many partitions when we have more data.
I'm running on Red Hat Enterprise Linux 5.7 on a 6 CPU machine with 32GB physical memory. I haven't changed any params in the postgresql.conf file.
Any suggestions on how to get this query to run?
Thanks in advance,
Vick
Found info in doc: GPDB_4100_README.pdf - page 15 - Known Issues - 11734 Memory Quota
I found info relating to this issue in the document titled GPDB_4100_README.pdf, on page 15, in the Known Issues section - issue 11734 Memory Quota.
"When scanning a highly partitioned table with low reserved memory that was set by
memory quota, an insufficient memory reserved error can appear.
Work-around: Increase the reserved memory increasing statement_mem or
increasing the limits of a resource queue. Use statement_mem to set a memory
limit for a query. When gp_resqueue_memory_policy is enabled, statement_mem
replaces work_mem. The default is 125MB."
I ran these statements to get past the issue:
set max_statement_mem='3GB';
set statement_mem='2GB';
select count(1) from mytable;
This completed on the 750-partition table with 350M rows in 559 seconds.
Last edited by vfisher; 10-21-2011 at 01:22 PM.
Reply With Quote