From: | Robert Treat <xzilla(at)users(dot)sourceforge(dot)net> |
---|---|
To: | pgsql-php(at)postgresql(dot)org |
Cc: | tv(at)fuzzy(dot)cz |
Subject: | Re: bytea columns and memory exhaustion |
Date: | 2008-05-01 17:30:44 |
Message-ID: | 200805011330.44371.xzilla@users.sourceforge.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-php |
On Wednesday 30 April 2008 12:50, tv(at)fuzzy(dot)cz wrote:
> 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 ...
>
Just wondering if using cursors might allow you to get around it?
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
From | Date | Subject | |
---|---|---|---|
Next Message | Tomas Vondra | 2008-05-01 17:50:36 | Re: bytea columns and memory exhaustion |
Previous Message | tv | 2008-04-30 16:50:10 | bytea columns and memory exhaustion |