Thread: PHP memory usage

PHP memory usage

From
"A Palmblad"
Date:
We've been running into some issues with PHP and Postgres, and memory usage.
On a dual athlon 64, w/ 4 gigs of RAM, PHP 4.3.6, and Postgres 7.4.2 I've
seen our server start to crawl after some queries returning very large
result sets.  Although large result sets aren't exactly what we want to be
dealing with, I really want to avoid a situation where a user could disrupt
our server with certain options to some searches.

I did a quick commandline test, and it looks like PHP starts to eat memory
during its call to pg_query.  By eat memory, I mean use memory well in
excess of php.ini's memory_limit of 8 megs.  (I stop it around 90% of
available memory usage)  Is this acceptable behaviour, or should I report it
to PHP as a bug?  If not, are my only solutions to wrap my selects in
cursors and/or to use limit?

-Adam Palmblad


Re: PHP memory usage

From
Nick Barr
Date:
A Palmblad wrote:
> We've been running into some issues with PHP and Postgres, and memory usage.
> On a dual athlon 64, w/ 4 gigs of RAM, PHP 4.3.6, and Postgres 7.4.2 I've
> seen our server start to crawl after some queries returning very large
> result sets.  Although large result sets aren't exactly what we want to be
> dealing with, I really want to avoid a situation where a user could disrupt
> our server with certain options to some searches.
>
> I did a quick commandline test, and it looks like PHP starts to eat memory
> during its call to pg_query.  By eat memory, I mean use memory well in
> excess of php.ini's memory_limit of 8 megs.  (I stop it around 90% of
> available memory usage)  Is this acceptable behaviour, or should I report it
> to PHP as a bug?  If not, are my only solutions to wrap my selects in
> cursors and/or to use limit?
>
> -Adam Palmblad
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>
>
>
My guess is that PHP is pulling the entire record set into memory,
something which is not good as you can imagine. The only real solutions
to this are to reduce the size of the record set, or use cursors.

You could reduce the size of the record set by using paging, i.e. only
allowing the user to see say 20 records out of 5000. The way I have
implemented this in the past is to do a COUNT(*) on the entire record
set then use LIMIT 20 OFFSET 200. Alternatively you could use the cursor
to return the data you want to look at.

Hope that almost helps.


Nick


Re: PHP memory usage

From
Robby Russell
Date:
A Palmblad typed this on 05/31/2004 02:22 PM:
> We've been running into some issues with PHP and Postgres, and memory usage.
> On a dual athlon 64, w/ 4 gigs of RAM, PHP 4.3.6, and Postgres 7.4.2 I've
> seen our server start to crawl after some queries returning very large
> result sets.  Although large result sets aren't exactly what we want to be
> dealing with, I really want to avoid a situation where a user could disrupt
> our server with certain options to some searches.
>
> I did a quick commandline test, and it looks like PHP starts to eat memory
> during its call to pg_query.  By eat memory, I mean use memory well in
> excess of php.ini's memory_limit of 8 megs.  (I stop it around 90% of
> available memory usage)  Is this acceptable behaviour, or should I report it
> to PHP as a bug?  If not, are my only solutions to wrap my selects in
> cursors and/or to use limit?
>
> -Adam Palmblad
>

I am going to take a quick guess and suggest that test those queries in
psql itself. You might be running into some issues with your queries.


Test your queries with psql, EXPLAIN.
\h EXPLAIN

-Robby

--
Robby Russell,  |  Sr. Administrator / Lead Programmer
Command Prompt, Inc.   |  http://www.commandprompt.com
rrussell@commandprompt.com | Telephone: (503) 667.4564

Re: PHP memory usage

From
"A Palmblad"
Date:
I watch Postgres while its doing the query - and the postmaster is not the
culprit for memory usage.  It looks to me like PHP's postgres driver is
loading the entire result into memory when I do a query.  That is, the
postgres process stays within the memory its config tells it to use, while a
PHP script will eat as much as it can to load the result set.  Although I
would think best practices would dictate the use of a cursor or some limit
statements, I was also trying to get at whether or not a PHP extension
<i>should</i> be able to use as much RAM as it wants.

-Adam
----- Original Message -----
From: "Robby Russell" <rrussell@commandprompt.com>
To: "A Palmblad" <adampalmblad@yahoo.ca>
Cc: <pgsql-php@postgresql.org>
Sent: Wednesday, June 02, 2004 1:17 PM
Subject: Re: [PHP] PHP memory usage


> A Palmblad typed this on 05/31/2004 02:22 PM:
> > We've been running into some issues with PHP and Postgres, and memory
usage.
> > On a dual athlon 64, w/ 4 gigs of RAM, PHP 4.3.6, and Postgres 7.4.2
I've
> > seen our server start to crawl after some queries returning very large
> > result sets.  Although large result sets aren't exactly what we want to
be
> > dealing with, I really want to avoid a situation where a user could
disrupt
> > our server with certain options to some searches.
> >
> > I did a quick commandline test, and it looks like PHP starts to eat
memory
> > during its call to pg_query.  By eat memory, I mean use memory well in
> > excess of php.ini's memory_limit of 8 megs.  (I stop it around 90% of
> > available memory usage)  Is this acceptable behaviour, or should I
report it
> > to PHP as a bug?  If not, are my only solutions to wrap my selects in
> > cursors and/or to use limit?
> >
> > -Adam Palmblad
> >
>
> I am going to take a quick guess and suggest that test those queries in
> psql itself. You might be running into some issues with your queries.
>
>
> Test your queries with psql, EXPLAIN.
> \h EXPLAIN
>
> -Robby
>
> --
> Robby Russell,  |  Sr. Administrator / Lead Programmer
> Command Prompt, Inc.   |  http://www.commandprompt.com
> rrussell@commandprompt.com | Telephone: (503) 667.4564
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings


Re: PHP memory usage

From
Robert Treat
Date:
Interesting... I was just about to post a question regarding the differences
between the functions mysql_query and mysql_unbuffered_query in regards to
pg_query, but I guess this is the issue right here, and from what I can tell
there is no php based way to work around it.

Robert Treat

On Wednesday 02 June 2004 16:41, A Palmblad wrote:
> I watch Postgres while its doing the query - and the postmaster is not the
> culprit for memory usage.  It looks to me like PHP's postgres driver is
> loading the entire result into memory when I do a query.  That is, the
> postgres process stays within the memory its config tells it to use, while
> a PHP script will eat as much as it can to load the result set.  Although I
> would think best practices would dictate the use of a cursor or some limit
> statements, I was also trying to get at whether or not a PHP extension
> <i>should</i> be able to use as much RAM as it wants.
>
> -Adam
> ----- Original Message -----
> From: "Robby Russell" <rrussell@commandprompt.com>
> To: "A Palmblad" <adampalmblad@yahoo.ca>
> Cc: <pgsql-php@postgresql.org>
> Sent: Wednesday, June 02, 2004 1:17 PM
> Subject: Re: [PHP] PHP memory usage
>
> > A Palmblad typed this on 05/31/2004 02:22 PM:
> > > We've been running into some issues with PHP and Postgres, and memory
>
> usage.
>
> > > On a dual athlon 64, w/ 4 gigs of RAM, PHP 4.3.6, and Postgres 7.4.2
>
> I've
>
> > > seen our server start to crawl after some queries returning very large
> > > result sets.  Although large result sets aren't exactly what we want to
>
> be
>
> > > dealing with, I really want to avoid a situation where a user could
>
> disrupt
>
> > > our server with certain options to some searches.
> > >
> > > I did a quick commandline test, and it looks like PHP starts to eat
>
> memory
>
> > > during its call to pg_query.  By eat memory, I mean use memory well in
> > > excess of php.ini's memory_limit of 8 megs.  (I stop it around 90% of
> > > available memory usage)  Is this acceptable behaviour, or should I
>
> report it
>
> > > to PHP as a bug?  If not, are my only solutions to wrap my selects in
> > > cursors and/or to use limit?
> > >
> > > -Adam Palmblad
> >
> > I am going to take a quick guess and suggest that test those queries in
> > psql itself. You might be running into some issues with your queries.
> >
> >
> > Test your queries with psql, EXPLAIN.
> > \h EXPLAIN
> >
> > -Robby
> >
> > --
> > Robby Russell,  |  Sr. Administrator / Lead Programmer
> > Command Prompt, Inc.   |  http://www.commandprompt.com
> > rrussell@commandprompt.com | Telephone: (503) 667.4564
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 7: don't forget to increase your free space map settings
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings

--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

Re: PHP memory usage

From
Robby Russell
Date:
Robert Treat typed this on 06/02/2004 02:45 PM:
> Interesting... I was just about to post a question regarding the differences
> between the functions mysql_query and mysql_unbuffered_query in regards to
> pg_query, but I guess this is the issue right here, and from what I can tell
> there is no php based way to work around it.
>
> Robert Treat

Yeah, that's a good point. I doubt there is a difference as to how
PostgrSQL and MySQL work under these situations.

Let me know if you figure anything out in regards to this.

Cheers,

-Robby

--
Robby Russell,  |  Sr. Administrator / Lead Programmer
Command Prompt, Inc.   |  http://www.commandprompt.com
rrussell@commandprompt.com | Telephone: (503) 667.4564

Re: PHP memory usage

From
Christopher Kings-Lynne
Date:
The simple answer is the PHP's built-in memory checking cannot control
what a plugin .so does.  Use a cursor.

Chris

Robert Treat wrote:

> Interesting... I was just about to post a question regarding the differences
> between the functions mysql_query and mysql_unbuffered_query in regards to
> pg_query, but I guess this is the issue right here, and from what I can tell
> there is no php based way to work around it.
>
> Robert Treat
>
> On Wednesday 02 June 2004 16:41, A Palmblad wrote:
>
>>I watch Postgres while its doing the query - and the postmaster is not the
>>culprit for memory usage.  It looks to me like PHP's postgres driver is
>>loading the entire result into memory when I do a query.  That is, the
>>postgres process stays within the memory its config tells it to use, while
>>a PHP script will eat as much as it can to load the result set.  Although I
>>would think best practices would dictate the use of a cursor or some limit
>>statements, I was also trying to get at whether or not a PHP extension
>><i>should</i> be able to use as much RAM as it wants.
>>
>>-Adam
>>----- Original Message -----
>>From: "Robby Russell" <rrussell@commandprompt.com>
>>To: "A Palmblad" <adampalmblad@yahoo.ca>
>>Cc: <pgsql-php@postgresql.org>
>>Sent: Wednesday, June 02, 2004 1:17 PM
>>Subject: Re: [PHP] PHP memory usage
>>
>>
>>>A Palmblad typed this on 05/31/2004 02:22 PM:
>>>
>>>>We've been running into some issues with PHP and Postgres, and memory
>>
>>usage.
>>
>>
>>>>On a dual athlon 64, w/ 4 gigs of RAM, PHP 4.3.6, and Postgres 7.4.2
>>
>>I've
>>
>>
>>>>seen our server start to crawl after some queries returning very large
>>>>result sets.  Although large result sets aren't exactly what we want to
>>
>>be
>>
>>
>>>>dealing with, I really want to avoid a situation where a user could
>>
>>disrupt
>>
>>
>>>>our server with certain options to some searches.
>>>>
>>>>I did a quick commandline test, and it looks like PHP starts to eat
>>
>>memory
>>
>>
>>>>during its call to pg_query.  By eat memory, I mean use memory well in
>>>>excess of php.ini's memory_limit of 8 megs.  (I stop it around 90% of
>>>>available memory usage)  Is this acceptable behaviour, or should I
>>
>>report it
>>
>>
>>>>to PHP as a bug?  If not, are my only solutions to wrap my selects in
>>>>cursors and/or to use limit?
>>>>
>>>>-Adam Palmblad
>>>
>>>I am going to take a quick guess and suggest that test those queries in
>>>psql itself. You might be running into some issues with your queries.
>>>
>>>
>>>Test your queries with psql, EXPLAIN.
>>>\h EXPLAIN
>>>
>>>-Robby
>>>
>>>--
>>>Robby Russell,  |  Sr. Administrator / Lead Programmer
>>>Command Prompt, Inc.   |  http://www.commandprompt.com
>>>rrussell@commandprompt.com | Telephone: (503) 667.4564
>>>
>>>---------------------------(end of broadcast)---------------------------
>>>TIP 7: don't forget to increase your free space map settings
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 7: don't forget to increase your free space map settings
>
>

Re: PHP memory usage

From
Robert Treat
Date:
And yet somehow the my$ql folks get a php function to do this...

Robert Treat

On Wednesday 02 June 2004 21:37, Christopher Kings-Lynne wrote:
> The simple answer is the PHP's built-in memory checking cannot control
> what a plugin .so does.  Use a cursor.
>
> Chris
>
> Robert Treat wrote:
> > Interesting... I was just about to post a question regarding the
> > differences between the functions mysql_query and mysql_unbuffered_query
> > in regards to pg_query, but I guess this is the issue right here, and
> > from what I can tell there is no php based way to work around it.
> >
> > Robert Treat
> >
> > On Wednesday 02 June 2004 16:41, A Palmblad wrote:
> >>I watch Postgres while its doing the query - and the postmaster is not
> >> the culprit for memory usage.  It looks to me like PHP's postgres driver
> >> is loading the entire result into memory when I do a query.  That is,
> >> the postgres process stays within the memory its config tells it to use,
> >> while a PHP script will eat as much as it can to load the result set.
> >> Although I would think best practices would dictate the use of a cursor
> >> or some limit statements, I was also trying to get at whether or not a
> >> PHP extension <i>should</i> be able to use as much RAM as it wants.
> >>
> >>-Adam
> >>----- Original Message -----
> >>From: "Robby Russell" <rrussell@commandprompt.com>
> >>To: "A Palmblad" <adampalmblad@yahoo.ca>
> >>Cc: <pgsql-php@postgresql.org>
> >>Sent: Wednesday, June 02, 2004 1:17 PM
> >>Subject: Re: [PHP] PHP memory usage
> >>
> >>>A Palmblad typed this on 05/31/2004 02:22 PM:
> >>>>We've been running into some issues with PHP and Postgres, and memory
> >>
> >>usage.
> >>
> >>>>On a dual athlon 64, w/ 4 gigs of RAM, PHP 4.3.6, and Postgres 7.4.2
> >>
> >>I've
> >>
> >>>>seen our server start to crawl after some queries returning very large
> >>>>result sets.  Although large result sets aren't exactly what we want to
> >>
> >>be
> >>
> >>>>dealing with, I really want to avoid a situation where a user could
> >>
> >>disrupt
> >>
> >>>>our server with certain options to some searches.
> >>>>
> >>>>I did a quick commandline test, and it looks like PHP starts to eat
> >>
> >>memory
> >>
> >>>>during its call to pg_query.  By eat memory, I mean use memory well in
> >>>>excess of php.ini's memory_limit of 8 megs.  (I stop it around 90% of
> >>>>available memory usage)  Is this acceptable behaviour, or should I
> >>
> >>report it
> >>
> >>>>to PHP as a bug?  If not, are my only solutions to wrap my selects in
> >>>>cursors and/or to use limit?
> >>>>
> >>>>-Adam Palmblad
> >>>
> >>>I am going to take a quick guess and suggest that test those queries in
> >>>psql itself. You might be running into some issues with your queries.
> >>>
> >>>
> >>>Test your queries with psql, EXPLAIN.
> >>>\h EXPLAIN
> >>>
> >>>-Robby
> >>>
> >>>--
> >>>Robby Russell,  |  Sr. Administrator / Lead Programmer
> >>>Command Prompt, Inc.   |  http://www.commandprompt.com
> >>>rrussell@commandprompt.com | Telephone: (503) 667.4564
> >>>
> >>>---------------------------(end of broadcast)---------------------------
> >>>TIP 7: don't forget to increase your free space map settings
> >>
> >>---------------------------(end of broadcast)---------------------------
> >>TIP 7: don't forget to increase your free space map settings

--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL