Skip site navigation (1) Skip section navigation (2)

ERROR: "invalid memory alloc request size" or "unexpected end of data" on large table

From: Stephane Bailliez <sbailliez(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: ERROR: "invalid memory alloc request size" or "unexpected end of data" on large table
Date: 2007-11-12 19:57:48
Message-ID: 4738B03C.8090901@gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
(posting on pgsql-perf as I'm questioning the pertinence of the 
settings, might not be the best place for the overall pb: apologies)

Postgresql 8.1.10
Linux Ubuntu: 2.6.17-12-server
4GB RAM, machine is only used for this I do have less than 30 tables, 4 
of them having between 10-40 million rows, size on disk is approximately 50G
Nothing spectacular on the install, it's mainly sandbox.

Relevant bits of the postgresql.conf
max_connections = 15
shared_buffers = 49152
work_mem = 16384
maintenance_work_mem = 32768
max_fsm_pages = 40000
effective_cache_size = 100000


I'm doing a rather 'simplistic' query, though heavy on hashing and 
aggregate:

For the records:
select count(*) from action where action_date between '2007-10-01' and 
'2007-10-31'
9647980


The query is:

select tspent, count(*) from (
select sum(time_spent)/60 as tspent from action
where action_date between '2007-10-01' and '2007-10-31'
group by action_date, user_id
) as a
group by tstpent
order by tspent asc;

I do receive a memory alloc error for a 1.5GB request size. So I may 
have oversized something significantly that is exploding (work_mem ?)
(I was running an explain analyze and had a pgsql_tmp dir reaching 2.9GB 
until it died with result similar error as with the query alone)

ERROR: invalid memory alloc request size 1664639562
SQL state: XX000

Sometimes I do get:

ERROR: unexpected end of data
SQL state: XX000


table is along the line of  (sorry cannot give you the full table):

CREATE TABLE action (
  id SERIAL,
  action_date  DATE NOT NULL,
  time_spent INT NOT NULL,
  user_id TEXT NOT NULL,  -- user id is a 38 character string
  ...
);

CREATE INDEX action_action_date_idx
  ON action USING btree(action_date);

Here is an explain analyze for just 1 day:

"HashAggregate  (cost=709112.04..709114.54 rows=200 width=8) (actual 
time=9900.994..9902.188 rows=631 loops=1)"
"  ->  HashAggregate  (cost=706890.66..708001.35 rows=74046 width=49) 
(actual time=9377.654..9687.964 rows=122644 loops=1)"
"        ->  Bitmap Heap Scan on action  (cost=6579.73..701337.25 
rows=740455 width=49) (actual time=2409.697..6756.027 rows=893351 loops=1)"
"              Recheck Cond: ((action_date >= '2007-10-01'::date) AND 
(action_date <= '2007-10-02'::date))"
"              ->  Bitmap Index Scan on action_action_date_idx  
(cost=0.00..6579.73 rows=740455 width=0) (actual time=2373.837..2373.837 
rows=893351 loops=1)"
"                    Index Cond: ((action_date >= '2007-10-01'::date) 
AND (action_date <= '2007-10-02'::date))"
"Total runtime: 9933.165 ms"



-- stephane

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2007-11-12 22:54:22
Subject: Re: ERROR: "invalid memory alloc request size" or "unexpected end of data" on large table
Previous:From: Dave CramerDate: 2007-11-12 18:32:34
Subject: Re: difference between a unique constraint and a unique index ???

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group