pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: tv@fuzzy.cz
To: pgsql-php@postgresql.org
Subject: bytea columns and memory exhaustion
Date: Wed, 30 Apr 2008 18:50:10 +0200 (CEST)
Message-ID: <18873.217.77.161.17.1209574210.squirrel@sq.gransy.com> (raw)

Hi all,

we have a quite nice web application written in PHP over PostgreSQL, with
all the data stored in a PostgreSQL database, including files stored in
BYTEA columns. It worked quite nice for a long time, but the users began
to insert large files (say 1 - 10 megabytes in size) and we began to
experience 'allowed memory exhausted' PHP fatal errors.

I believe the problem is caused by escaping the bytea data, which
multiplies the amount of required memory, so a 4MB file easily exhausts
the 20MB limit we've set when calling pg_fetch_array(). This is probably
made worse by usage of UTF-8, but not sure about it.

I guess I'm not the only one here who was hit by this problem, so I'd like
to hear your recommendations how to solve it. I've already thounght about
these solutions:

1) Increasing the PHP memory limit

I'm not a big fan of this, as we would hit the new limit sooner or later,
and we're not the only users of the server.

2) Using large objects and pg_lo_* functions instead of BYTEA columns.

I don't like this, as it would require a serious redesign of the database
schema and the PHP application itself. Moreover according to the user
comments in http://cz.php.net/manual/en/function.pg-lo-open.php it would
require a superuser access to the database, which is not possible in our
case.

3) Fetching the BYTEA piece by piece using substring()

This is the only reliable and 'elegant enough' solution I've found so far.
It lies in fetching the BYTEA column in 'small chunks' (say 10 - 100k in
size), unescaping each of them and either storing it or writing it to the
output before before fetching the next one. I still have to think about
upload / insert, though ...

Do you know about a better solution?

thanks for all your advices
Tomas




view thread (3+ messages)  latest in thread

Message-ID: <18873.217.77.161.17.1209574210.squirrel@sq.gransy.com>
Permalink:  ../18873.217.77.161.17.1209574210.squirrel@sq.gransy.com/
Also on:    postgresql.org/message-id/18873.217.77.161.17.1209574210.squirrel@sq.gransy.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-php@postgresql.org
  Cc: tv@fuzzy.cz
  Subject: Re: bytea columns and memory exhaustion
  In-Reply-To: <18873.217.77.161.17.1209574210.squirrel@sq.gransy.com>
  References: <18873.217.77.161.17.1209574210.squirrel@sq.gransy.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox