Re: using a lot of maintenance_work_mem

Tom & Kevin,

thank you for your replies. Kevin, I had already employed all the
tricks you mention, except using temporary tables which would be hard
for me due to the structure of my application (but I could try using
something like pgbouncer or so), but thanks a lot for sharing the ideas.

Tom Lane wrote:
>> If I were to either (a) increase MaxAllocSize to, say, 48 GB instead of
>> 1 GB, or (b) hack tuplesort.c to ignore MaxAllocSize, just for my local
>> setup - would that likely be viable in my situation, or would I break
>> countless things?
>
> You would break countless things.

Indeed I did. I tried to raise the MaxAllocSize from 1 GB to a large
number, but immediately got strange memory allocation errors during the
regression test (something that looked like a wrapped integer in a
memory allocation request).

I reduced the number in steps, and found I could compile and run
PostgreSQL 8.3 with a MaxAllocSize of 4 GB, and PostgreSQL 9.0 with 2 GB
without breakage.

In a completely un-scientific test run, comprising 42 individual SQL
statements aimed at importing and indexing a large volume of data, I got
the following results:

pg8.3 with normal MaxAllocSize .................. 15284s
pg8.3 with MaxAllocSize increased to 4 GB ....... 14609s (-4.5%)
pg9.0 with normal MaxAllocSize .................. 12969s (-15.2%)
pg9.0 with MaxAllocSize increased to 2 GB ....... 13211s (-13.5%)

> I'd want to see some evidence that it's actually
> helpful for production situations. I'm a bit dubious that you're going
> to gain much here.

So, on the whole it seems you were right; the performance, at least with
that small memory increase I managed to build in without breaking
things, doesn't increase a lot, or not at all for PostgreSQL 9.0.

The single query that gained most from the increase in memory was an
ALTER TABLE statement to add a BIGINT primary key to a table with about
50 million records - this was 75% faster on the both 8.3 and 9.0 but
since it took only 120 seconds to begin with, didn't change the result a
lot.

The single query where pg9.0 beat pg8.3 by a country mile was a CREATE
INDEX statement on a BIGINT column to a table with about 500 million
records - this cost 2679 seconds on normal 8.3, 2443 seconds on
large-memory 8.3, and aroung 1650 seconds on 9.0, large memory or not.

The query that, on both 8.3 and 9.0, took about 10% longer with more
memory was a CREATE INDEX statement on a TEXT column.

All this, as I said, completely un-scientific - I did take care to flush
caches and not run anything in parallel, but that was about all I did so
it might come out differently when run often.

My result of all of this? Switch to 9.0 of course ;)

Bye
Frederik

--
Frederik Ramm ## eMail frederik(at)remote(dot)org ## N49°00'09" E008°23'33"