I updated the database to PostgreSQL 8.2.6, but this does not appear to
make any difference.
I use the following script to create a test table. For
/tmp/oidfile.temp, I use "FAQ_farsi" from the PostgreSQL installation
(about 140kb).
create table gridvalue (gridoid oid);
--delete from gridvalue;
create or replace function load() RETURNS INTEGER AS
$body$
declare
i integer;
r oid;
begin
for i IN 1..150000
LOOP
r := lo_import('/tmp/oidfile.tmp');
insert into gridvalue values (r);
END LOOP;
RETURN i;
end;
$body$
language plpgsql;
select load();
select count(*) from gridvalue;
And the following script runs the .
create or replace function f() RETURNS setof bytea as
$body$
declare
r oid;
fd int;
ret bytea;
begin
for r in select gridoid FROM gridvalue LIMIT 150000
LOOP
fd := lo_open( r, 262144 ); -- 262144 = "INV_READ"
--PERFORM lo_lseek( fd, 120, 0 ); -- 0 = "SEEK_SET"
--ret := loread( fd, 4 );
PERFORM lo_close( fd );
--RETURN NEXT ret;
END LOOP;
end;
$body$
language plpgsql;
SELECT * FROM f();
On our 64bit Debian setup with 16 GB memory (2GB shared buffers),
running f() will rapidly eat up 1.4 GB of memory on the first run;
around 800-900 MB on subsequent runs. This seems a bit excessive,
considering that I am just opening the OID, without reading or writing
anything.
Adding more or less iterations seems to scale up (or down) the amount
of memory eaten up by the lo_open loop.
Some observations:
- With small blobs, the memory usage doesn't blow up in this way. The
problem seems to require "big" blobs (although 140kb isn't really that
much).
- Running the same query (with 50,000 iterations, due to hd and admin
limitations) on my 32bit laptop with Fedora5 doesn't show up the
problem, as it simply runs within the limits of the few MB it has
available on shared buffers. I suspect it would also gobble up memory,
if it was available, but I don't know.
Just to verify that there is not something within our database setup
that is affecting this, I'll try to run the tests again on a clean
installation of the database on the 64bit machine (just need to get some
disk space allocated first).
Regards,
Michael Akinde
Database Architect, met.no
begin:vcard
fn:Michael Akinde
n:Akinde;Michael
org:Meteorologisk Institutt, Norge;IT
adr;quoted-printable:;;Gaustadall=C3=A9en 30D;Oslo;;0313;Norge
email;internet:[EMAIL PROTECTED]
tel;work:22963379
tel;cell:45885379
x-mozilla-html:FALSE
url:http://www.met.no
version:2.1
end:vcard
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
