PostGreSQL 8.1.0 : out of memory during vacuum full analyze

On 12/12/05, DANTE ALEXANDRA <ALEXANDRA.DANTE@bull.net> wrote:

Hello,

This is my first posting in this mailing list and I am a PostGreSQL newbie.
I've posted this message in the "novice" mailing list last friday, but I
have any response.
I hope somenone could help me.

I realize a benchmarck on PostGreSQL on a 300Gb database. I used the
release 8.1.0.

I have created my database, which contains 8 tables, loaded each table
and created the appropriate indexes.
Then, on each table, I have launched the "VACUUM FULL ANALYZE" command
as a non-root user.

This command failed on the last table, the biggest, called "lineitem"
which contains 1799989091 rows (near 300 Gb of datas).
This description of this table is :
TPCH=# \d lineitem
Table "public.lineitem"
Column | Type | Modifiers
-----------------+-----------------------+-----------
l_orderkey | bigint | not null
l_partkey | bigint | not null
l_suppkey | bigint | not null
l_linenumber | bigint | not null
l_quantity | numeric |
l_extendedprice | numeric |
l_discount | numeric |
l_tax | numeric | not null
l_returnflag | character(1) |
l_linestatus | character(1) |
l_shipdate | date |
l_commitdate | date |
l_receiptdate | date |
l_shipinstruct | character(25) |
l_shipmode | character(10) |
l_comment | character varying(44) |
Indexes:
"i_l_orderkey" btree (l_orderkey), tablespace "tb_index"
Tablespace: "tb_lit"

Three hours after having launched the command, I've got this error :<>

<>TPCH=# VACUUM FULL VERBOSE ANALYZE lineitem;
<>INFO: vacuuming "public.lineitem"
<>*ERROR: out of memory*
DETAIL: Failed on request of size 67108864.

I do not try to launch again a vacuum full analyze on this table.

Can someone tell me why this error appears ?
Thank you for your help.

Regards,
Alexandra DANTE

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Thank you for your answer.
To give you more explanations, first, I have launched the ANALYZE
command on this big table, but the elasped time was very short. As my
queries launched on this table were very slow, I have tried to launch
the VACUUM FULL ANALYZE to update the statistics.

Is it correct ? Why ANALYZE has taken less than one minute ?

Thanks for your help.

Regards
Alexandra DANTE

Tom Lane a �crit :

DANTE ALEXANDRA <ALEXANDRA.DANTE@BULL.NET> writes:

Then, on each table, I have launched the "VACUUM FULL ANALYZE"
command as a non-root user.
This command failed on the last table, the biggest, called
"lineitem" which contains 1799989091 rows (near 300 Gb of datas).

VACUUM FULL requires workspace proportional to the number of blocks in
the table. You probably need to bump up the kernel's per-process memory
limit (see ulimit and so on) if you want to VACUUM FULL such a large
table.

My advice: you shouldn't be using VACUUM FULL anyway. Quite aside from
the memory issue, it's likely to take forever and a day.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings