Re: problem with large maintenance_work_mem settings and

Stefan Kaltenbrunner wrote:
> Hi all!
> 
> while playing on a new box i noticed that postgresql does not seem to be
> able to cope with very large settings for maintenance_work_mem.
> 
> For a test I created a single table with 5 integer columns containing
> about 1,8B rows 8(about 300M distinct values in the column I want to index):
> 
> 
> foo=# select count(*) from testtable;
>    count
>  ------------
>   1800201755
>  (1 row)
> 
> 
> I tried to create an index on one of the columns:
> 
> foo=# SET maintenance_work_mem to 4000000;
> SET
> foo=# CREATE INDEX a_idx ON testtable(a);
> ERROR:  invalid memory alloc request size 1073741824
> 
> foo=# SET maintenance_work_mem to 3000000;
> SET
> foo=# CREATE INDEX a_idx ON testtable(a);
> ERROR:  invalid memory alloc request size 1073741824
> 
> the error is generated pretty fast (a few seconds into the create index)
> 
> however:
> 
> foo=# SET maintenance_work_mem to 2000000;
> SET
> foo=# CREATE INDEX a_idx ON testtable(a);
> 
> is running now for about 10 hours with nearly no IO but pegging the
> CPU-core it is running on at a constent 100%.
> 
> watching the process while this happens seems to indicate that the above
> error occures after the backend exceeds about 3,1GB in resident size.
> 
> The box in question is a Dual Opteron 275 (4 cores @2,2Ghz) with 16GB of
>  RAM and 24GB of swap. OS is Debian Sarge/AMD64 with a pure 64bit userland.

forgot to mention that this is 8.1.3 compiled from source. Further
testing shows that not only CREATE INDEX has some issue with large
maintenance_work_mem settings :


foo=# set maintenance_work_mem to 2000000;
SET
foo=# VACUUM ANALYZE verbose;
INFO:  vacuuming "information_schema.sql_features"
ERROR:  invalid memory alloc request size 2047999998


Stefan