Thread: PHP memory usage
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
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
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
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
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
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
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 > >
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