From adam@aers.ca Mon May 31 20:55:48 2004 X-Original-To: pgsql-php-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 339ADD1B181 for ; Mon, 31 May 2004 17:55:48 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 18948-04 for ; Mon, 31 May 2004 17:55:47 -0300 (ADT) Received: from ensim.creoe.com (unknown [207.44.172.93]) by svr1.postgresql.org (Postfix) with ESMTP id ACC02D1B18F for ; Mon, 31 May 2004 17:55:43 -0300 (ADT) Received: from AERS04 (linksys-router.idc.uvic.ca [142.104.12.41]) by ensim.creoe.com (8.11.6/8.11.6) with SMTP id i4VKgsQ20809 for ; Mon, 31 May 2004 13:42:54 -0700 Message-ID: <003101c44751$a5fef890$97019696@AERS04> From: "Adam Palmblad" To: Subject: PHP memory usage Date: Mon, 31 May 2004 13:55:46 -0700 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_002E_01C44716.F909FB10" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1158 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.5 tagged_above=0.0 required=5.0 tests=HTML_20_30, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200406/1 X-Sequence-Number: 2775 This is a multi-part message in MIME format. ------=_NextPart_000_002E_01C44716.F909FB10 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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'v= e seen our server start to crawl after some queries returning very large re= sult sets. Although large result sets aren't exactly what we want to be de= aling with, I really want to avoid a situation where a user could disrupt o= ur server with certain options to some searches.=20=20 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 exce= ss 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 ------=_NextPart_000_002E_01C44716.F909FB10 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
We've been running into some issues with P= HP and=20 Postgres, and memory usage.  On a dual athlon 64, w/ 4 gigs of RAM, PH= P=20 4.3.6, and Postgres 7.4.2 I've seen our server start to crawl after some qu= eries=20 returning very large result sets.  Although large result sets aren't= =20 exactly what we want to be dealing with, I really want to avoid a situation= =20 where a user could disrupt our server with certain options to some=20 searches. 
 
I did a quick commandline test, and it loo= ks like=20 PHP starts to eat memory during its call to pg_query.  By eat memory, = I=20 mean use memory well in excess of php.ini's memory_limit of 8 megs.&nb= sp;=20 (I stop it around 90% of available memory usage)  Is this acceptable= =20 behaviour, or should I report it to PHP as a bug?  If not, are my only= =20 solutions to wrap my selects in cursors and/or to use limit?
 
-Adam Palmblad
------=_NextPart_000_002E_01C44716.F909FB10-- From adampalmblad@yahoo.ca Mon May 31 21:22:18 2004 X-Original-To: pgsql-php-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 2B5B9D1B9A9 for ; Mon, 31 May 2004 18:22:18 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 29406-10 for ; Mon, 31 May 2004 18:22:18 -0300 (ADT) Received: from smtp011.mail.yahoo.com (smtp011.mail.yahoo.com [216.136.173.31]) by svr1.postgresql.org (Postfix) with SMTP id E3839D1B9A6 for ; Mon, 31 May 2004 18:22:14 -0300 (ADT) Received: from unknown (HELO AERS04) (adampalmblad@142.104.12.41 with login) by smtp011.mail.yahoo.com with SMTP; 31 May 2004 21:22:17 -0000 Message-ID: <005501c44755$5a851580$97019696@AERS04> From: "A Palmblad" To: Subject: PHP memory usage Date: Mon, 31 May 2004 14:22:18 -0700 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1158 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200405/54 X-Sequence-Number: 2773 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 From nicky@chuckie.co.uk Mon May 31 22:50:51 2004 X-Original-To: pgsql-php-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 17398D1B3AD for ; Mon, 31 May 2004 19:50:51 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 62550-09 for ; Mon, 31 May 2004 19:50:51 -0300 (ADT) Received: from obie.mrkdns.com (obie.mrkdns.com [69.93.72.82]) by svr1.postgresql.org (Postfix) with ESMTP id 02CC1D1B344 for ; Mon, 31 May 2004 19:50:47 -0300 (ADT) Received: from 82-33-0-42.cable.ubr13.newt.blueyonder.co.uk ([82.33.0.42] helo=[192.168.0.12]) by obie.mrkdns.com with asmtp (Exim 4.34) id 1BUvcI-0003M9-LJ; Mon, 31 May 2004 23:50:50 +0100 Message-ID: <40BBB6C3.8030507@chuckie.co.uk> Date: Mon, 31 May 2004 23:50:43 +0100 From: Nick Barr User-Agent: Mozilla Thunderbird 0.6 (Windows/20040502) X-Accept-Language: en-us, en MIME-Version: 1.0 To: A Palmblad Cc: pgsql-php@postgresql.org Subject: Re: PHP memory usage References: <005501c44755$5a851580$97019696@AERS04> In-Reply-To: <005501c44755$5a851580$97019696@AERS04> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - obie.mrkdns.com X-AntiAbuse: Original Domain - postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [0 0] / [47 12] X-AntiAbuse: Sender Address Domain - chuckie.co.uk X-Source: X-Source-Args: X-Source-Dir: X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.1 tagged_above=0.0 required=5.0 tests=RCVD_IN_SORBS X-Spam-Level: X-Archive-Number: 200405/55 X-Sequence-Number: 2774 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 From rrussell@commandprompt.com Wed Jun 2 20:18:01 2004 X-Original-To: pgsql-php-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id E7BB7D1CA4E for ; Wed, 2 Jun 2004 17:18:01 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 13495-01 for ; Wed, 2 Jun 2004 20:18:02 +0000 (GMT) Received: from hosting.commandprompt.com (128.commandprompt.com [207.173.200.128]) by svr1.postgresql.org (Postfix) with ESMTP id AE2E4D1B1AE for ; Wed, 2 Jun 2004 17:17:59 -0300 (ADT) Received: from [192.168.1.45] (dsl093-038-087.pdx1.dsl.speakeasy.net [66.93.38.87]) (authenticated) by hosting.commandprompt.com (8.11.6/8.11.6) with ESMTP id i52KHwK07840; Wed, 2 Jun 2004 13:17:58 -0700 Message-ID: <40BE35EF.8030003@commandprompt.com> Date: Wed, 02 Jun 2004 13:17:51 -0700 From: Robby Russell Organization: Command Prompt, Inc. User-Agent: Mozilla Thunderbird 0.6 (X11/20040502) X-Accept-Language: en-us, en MIME-Version: 1.0 To: A Palmblad Cc: pgsql-php@postgresql.org Subject: Re: PHP memory usage References: <005501c44755$5a851580$97019696@AERS04> In-Reply-To: <005501c44755$5a851580$97019696@AERS04> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Archive-Number: 200406/2 X-Sequence-Number: 2776 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 From adampalmblad@yahoo.ca Wed Jun 2 20:41:35 2004 X-Original-To: pgsql-php-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 44343D1B1AE for ; Wed, 2 Jun 2004 17:41:35 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 50893-03 for ; Wed, 2 Jun 2004 20:41:36 +0000 (GMT) Received: from smtp104.mail.sc5.yahoo.com (smtp104.mail.sc5.yahoo.com [66.163.169.223]) by svr1.postgresql.org (Postfix) with SMTP id 1FA35D1B196 for ; Wed, 2 Jun 2004 17:41:32 -0300 (ADT) Received: from unknown (HELO AERS04) (adampalmblad@142.104.12.41 with login) by smtp104.mail.sc5.yahoo.com with SMTP; 2 Jun 2004 20:41:33 -0000 Message-ID: <007a01c448e1$fde018a0$97019696@AERS04> From: "A Palmblad" To: "Robby Russell" Cc: References: <005501c44755$5a851580$97019696@AERS04> <40BE35EF.8030003@commandprompt.com> Subject: Re: PHP memory usage Date: Wed, 2 Jun 2004 13:41:33 -0700 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1158 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-Virus-Scanned: by amavisd-new at hub.org X-Archive-Number: 200406/3 X-Sequence-Number: 2777 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 should be able to use as much RAM as it wants. -Adam ----- Original Message ----- From: "Robby Russell" To: "A Palmblad" Cc: 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 From xzilla@users.sourceforge.net Wed Jun 2 21:45:53 2004 X-Original-To: pgsql-php-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 19712D1B341 for ; Wed, 2 Jun 2004 18:45:53 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 54682-07 for ; Wed, 2 Jun 2004 21:45:54 +0000 (GMT) Received: from lakermmtao10.cox.net (lakermmtao10.cox.net [68.230.240.29]) by svr1.postgresql.org (Postfix) with ESMTP id B4B81D1B28B for ; Wed, 2 Jun 2004 18:45:50 -0300 (ADT) Received: from [192.168.0.13] (really [68.226.6.208]) by lakermmtao10.cox.net (InterMail vM.6.01.03.02 201-2131-111-104-20040324) with ESMTP id <20040602214549.FRTE15473.lakermmtao10.cox.net@[192.168.0.13]>; Wed, 2 Jun 2004 17:45:49 -0400 From: Robert Treat To: "A Palmblad" , "Robby Russell" Subject: Re: PHP memory usage Date: Wed, 2 Jun 2004 17:45:59 -0400 User-Agent: KMail/1.5 Cc: References: <005501c44755$5a851580$97019696@AERS04> <40BE35EF.8030003@commandprompt.com> <007a01c448e1$fde018a0$97019696@AERS04> In-Reply-To: <007a01c448e1$fde018a0$97019696@AERS04> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200406021745.59563.xzilla@users.sourceforge.net> X-Virus-Scanned: by amavisd-new at hub.org X-Archive-Number: 200406/4 X-Sequence-Number: 2778 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 > should be able to use as much RAM as it wants. > > -Adam > ----- Original Message ----- > From: "Robby Russell" > To: "A Palmblad" > Cc: > 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 From rrussell@commandprompt.com Wed Jun 2 21:49:43 2004 X-Original-To: pgsql-php-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id C5AE6D1CC7F for ; Wed, 2 Jun 2004 18:49:43 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 56751-06 for ; Wed, 2 Jun 2004 21:49:45 +0000 (GMT) Received: from hosting.commandprompt.com (128.commandprompt.com [207.173.200.128]) by svr1.postgresql.org (Postfix) with ESMTP id E9A1DD1CAC0 for ; Wed, 2 Jun 2004 18:49:41 -0300 (ADT) Received: from [192.168.1.45] (dsl093-038-087.pdx1.dsl.speakeasy.net [66.93.38.87]) (authenticated) by hosting.commandprompt.com (8.11.6/8.11.6) with ESMTP id i52LnZK22734; Wed, 2 Jun 2004 14:49:35 -0700 Message-ID: <40BE4B69.4060705@commandprompt.com> Date: Wed, 02 Jun 2004 14:49:29 -0700 From: Robby Russell Organization: Command Prompt, Inc. User-Agent: Mozilla Thunderbird 0.6 (X11/20040502) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Robert Treat Cc: A Palmblad , pgsql-php@postgresql.org Subject: Re: PHP memory usage References: <005501c44755$5a851580$97019696@AERS04> <40BE35EF.8030003@commandprompt.com> <007a01c448e1$fde018a0$97019696@AERS04> <200406021745.59563.xzilla@users.sourceforge.net> In-Reply-To: <200406021745.59563.xzilla@users.sourceforge.net> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Archive-Number: 200406/5 X-Sequence-Number: 2779 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 From chriskl@familyhealth.com.au Thu Jun 3 01:28:01 2004 X-Original-To: pgsql-php-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id A8C57D1B196 for ; Wed, 2 Jun 2004 22:28:01 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 24648-08 for ; Thu, 3 Jun 2004 01:28:04 +0000 (GMT) Received: from houston.familyhealth.com.au (fhnet.arach.net.au [203.22.197.21]) by svr1.postgresql.org (Postfix) with ESMTP id 275CBD1B18C for ; Wed, 2 Jun 2004 22:27:58 -0300 (ADT) Received: from [192.168.0.40] (work-40.internal [192.168.0.40]) by houston.familyhealth.com.au (8.12.9p1/8.12.9) with ESMTP id i531RtWL092868; Thu, 3 Jun 2004 09:27:55 +0800 (WST) (envelope-from chriskl@familyhealth.com.au) Message-ID: <40BE80CD.4080308@familyhealth.com.au> Date: Thu, 03 Jun 2004 09:37:17 +0800 From: Christopher Kings-Lynne User-Agent: Mozilla Thunderbird 0.6 (Windows/20040502) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Robert Treat Cc: A Palmblad , Robby Russell , pgsql-php@postgresql.org Subject: Re: PHP memory usage References: <005501c44755$5a851580$97019696@AERS04> <40BE35EF.8030003@commandprompt.com> <007a01c448e1$fde018a0$97019696@AERS04> <200406021745.59563.xzilla@users.sourceforge.net> In-Reply-To: <200406021745.59563.xzilla@users.sourceforge.net> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Archive-Number: 200406/8 X-Sequence-Number: 2782 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 >>should be able to use as much RAM as it wants. >> >>-Adam >>----- Original Message ----- >>From: "Robby Russell" >>To: "A Palmblad" >>Cc: >>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 > > From xzilla@users.sourceforge.net Fri Jun 4 14:35:51 2004 X-Original-To: pgsql-php-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 1A260D1B188 for ; Fri, 4 Jun 2004 11:35:51 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 09830-10 for ; Fri, 4 Jun 2004 14:35:48 +0000 (GMT) Received: from lakermmtao08.cox.net (lakermmtao08.cox.net [68.230.240.31]) by svr1.postgresql.org (Postfix) with ESMTP id 62988D1B16F for ; Fri, 4 Jun 2004 11:35:47 -0300 (ADT) Received: from [192.168.0.13] (really [68.226.6.208]) by lakermmtao08.cox.net (InterMail vM.6.01.03.02 201-2131-111-104-20040324) with ESMTP id <20040604143545.TKXU5121.lakermmtao08.cox.net@[192.168.0.13]>; Fri, 4 Jun 2004 10:35:45 -0400 From: Robert Treat To: Christopher Kings-Lynne Subject: Re: PHP memory usage Date: Fri, 4 Jun 2004 10:35:53 -0400 User-Agent: KMail/1.5 Cc: A Palmblad , Robby Russell , pgsql-php@postgresql.org References: <005501c44755$5a851580$97019696@AERS04> <200406021745.59563.xzilla@users.sourceforge.net> <40BE80CD.4080308@familyhealth.com.au> In-Reply-To: <40BE80CD.4080308@familyhealth.com.au> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200406041035.53198.xzilla@users.sourceforge.net> X-Virus-Scanned: by amavisd-new at hub.org X-Archive-Number: 200406/16 X-Sequence-Number: 2790 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 should be able to use as much RAM as it wants. > >> > >>-Adam > >>----- Original Message ----- > >>From: "Robby Russell" > >>To: "A Palmblad" > >>Cc: > >>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