Thread: BUG #1756: PQexec eats huge amounts of memory
The following bug has been logged online: Bug reference: 1756 Logged by: Denis Vlasenko Email address: vda@ilport.com.ua PostgreSQL version: 8.0.1 Operating system: Linux Description: PQexec eats huge amounts of memory Details: Verbatim from http://bugs.php.net/bug.php?id=33587: Description: ------------ Seen on php-4.3.4RC2. Since I was just testing how good PG fares compared to Oracle, and I am not feeling any real pain from this (IOW: not my itch to scratch), I do not research this in depth, apart from submitting bug report. Sorry. Symptom: even the simplest query $result = pg_query($db, "SELECT * FROM big_table"); eats enormous amounts of memory on server (proportional to table size). I think this is a problem with PostgreSQL client libs. php's source is included for easy reference. PHP_FUNCTION(pg_query) { ... pgsql_result = PQexec(pgsql, Z_STRVAL_PP(query)); if ((PGG(auto_reset_persistent) & 2) && PQstatus(pgsql) != CONNECTION_OK) { PQclear(pgsql_result); PQreset(pgsql); pgsql_result = PQexec(pgsql, Z_STRVAL_PP(query)); } if (pgsql_result) { status = PQresultStatus(pgsql_result); } else { status = (ExecStatusType) PQstatus(pgsql); } switch (status) { case PGRES_EMPTY_QUERY: case PGRES_BAD_RESPONSE: case PGRES_NONFATAL_ERROR: case PGRES_FATAL_ERROR: php_error_docref(NULL TSRMLS_CC, E_WARNING, "Query failed: %s.", PQerrorMessage(pgsql)); PQclear(pgsql_result); RETURN_FALSE; break; case PGRES_COMMAND_OK: /* successful command that did not return rows */ default: if (pgsql_result) { pg_result = (pgsql_result_handle *) emalloc(sizeof(pgsql_result_handle)); pg_result->conn = pgsql; pg_result->result = pgsql_result; pg_result->row = 0; ZEND_REGISTER_RESOURCE(return_value, pg_result, le_result); } else { PQclear(pgsql_result); RETURN_FALSE; } break; } }
Denis, $result =3D pg_query($db, "SELECT * FROM big_table"); >=20 you are reading a big result (as I suspect from big_table) into memory. It= =20 is perfectly normal that this uses large amounts of memory. [it would be rather suspicious if loading a big file / big resultset would= =20 not use big amounts of memory] Harald --=20 GHUM Harald Massa persuasion python postgresql Harald Armin Massa Reinsburgstra=DFe 202b 70197 Stuttgart 0173/9409607
Denis Vlasenko wrote: > Symptom: even the simplest query > $result = pg_query($db, "SELECT * FROM big_table"); > eats enormous amounts of memory on server > (proportional to table size). Right, which is exactly what you would expect. The entire result set is sent to the client and stored in local memory; if you only want to process part of the result set at a time, use a cursor. (And I'm a little suspicious that the performance of "SELECT * FROM big_table" will contribute to a meaningful comparison between database systems.) -Neil
Denis Vlasenko wrote: > The same php script but done against Oracle does not have this > behaviour. Perhaps; presumably Oracle is essentially creating a cursor for you behind the scenes. libpq does not attempt to do this automatically; if you need a cursor, you can create one by hand. -Neil
On Wednesday 06 July 2005 16:52, Harald Armin Massa wrote: > Denis, > > $result = pg_query($db, "SELECT * FROM big_table"); > > you are reading a big result (as I suspect from big_table) into memory. It > is perfectly normal that this uses large amounts of memory. No, I am not reading it into memory. I am executing query _on the server_, fetching result row-by-row and discarding rows as prey are processed (i.e. without accumulating all rows in _client's memory_) in the part of php script which you snipped off. Similar construct with Oracle, with 10x larger table, does not use Apache (php) memory significantly. php's pg_query() calls PQuery(), a Postgresql client library function, which is likely implemented so that it fetches all rows and stores them in client's RAM before completion. Oracle OCI8 does not work this way, it keeps result set on db server (in a form of a cursor or something like that). > [it would be rather suspicious if loading a big file / big resultset would > not use big amounts of memory] -- vda
On Thursday 07 July 2005 08:54, Neil Conway wrote: > Denis Vlasenko wrote: > > Symptom: even the simplest query > > $result = pg_query($db, "SELECT * FROM big_table"); > > eats enormous amounts of memory on server > > (proportional to table size). > > Right, which is exactly what you would expect. The entire result set is > sent to the client and stored in local memory; if you only want to > process part of the result set at a time, use a cursor. The same php script but done against Oracle does not have this behaviour. > (And I'm a little suspicious that the performance of "SELECT * FROM > big_table" will contribute to a meaningful comparison between database > systems.) I wanted to show colleagues which are Oracle admins that peak data fetch rate of PostgreSQL is way better than Oracle one. While it turned out to be true (Oracle+WinNT = 2kb TCP output buffer, ~1Mb/s over 100Mbit; PostgreSQL+Linux = 8kb buffer, ~2.6Mb/s), I was ridiculed instead when my php script failed miserably, crashing Apache with OOM condition, while alanogous script for Oracle ran to completion just fine. -- vda
On Thu, Jul 07, 2005 at 09:51:54AM +0300, Denis Vlasenko wrote: > I wanted to show colleagues which are Oracle admins that peak > data fetch rate of PostgreSQL is way better than Oracle one. > > While it turned out to be true (Oracle+WinNT = 2kb TCP output buffer, > ~1Mb/s over 100Mbit; PostgreSQL+Linux = 8kb buffer, ~2.6Mb/s), > I was ridiculed instead when my php script failed miserably, > crashing Apache with OOM condition, while alanogous script for Oracle > ran to completion just fine. You should have tested the script before showing off :-) You may want to convert it to manually use a cursor, at least the Postgres version. That would alleviate the memory problem. -- Alvaro Herrera (<alvherre[a]alvh.no-ip.org>) "Some men are heterosexual, and some are bisexual, and some men don't think about sex at all... they become lawyers" (Woody Allen)
Neil Conway wrote:
> Denis Vlasenko wrote:
>
>> The same php script but done against Oracle does not have this
>> behaviour.
>
>
> Perhaps; presumably Oracle is essentially creating a cursor for you
> behind the scenes. libpq does not attempt to do this automatically; if
> you need a cursor, you can create one by hand.
I do not understand how a cursor could be autocreated by a query like
$result = pg_query($db, "SELECT * FROM big_table");
php will expect $result to contain the entire table (yuck!).
On Thu, Jul 07, 2005 at 08:17:23AM -0700, John R Pierce wrote: > Neil Conway wrote: > >Denis Vlasenko wrote: > > > >>The same php script but done against Oracle does not have this > >>behaviour. > > > > > >Perhaps; presumably Oracle is essentially creating a cursor for you > >behind the scenes. libpq does not attempt to do this automatically; if > >you need a cursor, you can create one by hand. > > I do not understand how a cursor could be autocreated by a query like > > $result = pg_query($db, "SELECT * FROM big_table"); > > php will expect $result to contain the entire table (yuck!). Really? I thought what really happened is you had to get the results one at a time using the pg_fetch family of functions. If that is true, then it's possible to make the driver fake having the whole table by using a cursor. (Even if PHP doesn't do it, it's possible for OCI to do it behind the scenes.) -- Alvaro Herrera (<alvherre[a]alvh.no-ip.org>) Officer Krupke, what are we to do? Gee, officer Krupke, Krup you! (West Side Story, "Gee, Officer Krupke")
Hi, A similar topic has been discussed before on pgsql-sql mailing list: Subject: SELECT very slow Thomas Kellerer URL: http://archives.postgresql.org/pgsql-sql/2005-06/msg00118.php Regards. On 7/6/05, Denis Vlasenko <vda@ilport.com.ua> wrote: > Bug reference: 1756 > Logged by: Denis Vlasenko > Email address: vda@ilport.com.ua > PostgreSQL version: 8.0.1 > Operating system: Linux > Description: PQexec eats huge amounts of memory
On Sun, Jul 10, 2005 at 01:05:10PM +0300, Denis Vlasenko wrote: > On Thursday 07 July 2005 20:43, Alvaro Herrera wrote: > > Really? I thought what really happened is you had to get the results > > one at a time using the pg_fetch family of functions. If that is true, > > then it's possible to make the driver fake having the whole table by > > using a cursor. (Even if PHP doesn't do it, it's possible for OCI to do > > it behind the scenes.) > > Even without cursor, result can be read incrementally. > > I mean, query result is transferred over network, right? > We just can stop read()'ing before we reached the end of result set, > and continue at pg_fetch as needed. It's not that simple. libpq is designed to read whole result sets at a time; there's no support for reading incrementally from the server. Other problem is that neither libpq nor the server know how many tuples the query will return, until the whole query is executed. Thus, pg_numrows (for example) wouldn't work at all, which is a showstopper for many PHP scripts. In short, it can be made to work, but it's not as simple as you put it. -- Alvaro Herrera (<alvherre[a]alvh.no-ip.org>) "Industry suffers from the managerial dogma that for the sake of stability and continuity, the company should be independent of the competence of individual employees." (E. Dijkstra)
Alvaro Herrera wrote: > On Sun, Jul 10, 2005 at 01:05:10PM +0300, Denis Vlasenko wrote: > >>On Thursday 07 July 2005 20:43, Alvaro Herrera wrote: > > >>>Really? I thought what really happened is you had to get the results >>>one at a time using the pg_fetch family of functions. If that is true, >>>then it's possible to make the driver fake having the whole table by >>>using a cursor. (Even if PHP doesn't do it, it's possible for OCI to do >>>it behind the scenes.) >> >>Even without cursor, result can be read incrementally. >> >>I mean, query result is transferred over network, right? >>We just can stop read()'ing before we reached the end of result set, >>and continue at pg_fetch as needed. > > > It's not that simple. [...] It also requires that you assume there is only one set of query results outstanding at a time. I know that you can't assume that in JDBC, and by the sounds of it PHP's interface is similar in that you can have multiple query result objects active at the same time. -O
On Thursday 07 July 2005 20:43, Alvaro Herrera wrote: > On Thu, Jul 07, 2005 at 08:17:23AM -0700, John R Pierce wrote: > > Neil Conway wrote: > > >Denis Vlasenko wrote: > > > > > >>The same php script but done against Oracle does not have this > > >>behaviour. > > > > > > > > >Perhaps; presumably Oracle is essentially creating a cursor for you > > >behind the scenes. libpq does not attempt to do this automatically; if > > >you need a cursor, you can create one by hand. > > > > I do not understand how a cursor could be autocreated by a query like > > > > $result = pg_query($db, "SELECT * FROM big_table"); > > > > php will expect $result to contain the entire table (yuck!). > > Really? I thought what really happened is you had to get the results > one at a time using the pg_fetch family of functions. If that is true, > then it's possible to make the driver fake having the whole table by > using a cursor. (Even if PHP doesn't do it, it's possible for OCI to do > it behind the scenes.) Even without cursor, result can be read incrementally. I mean, query result is transferred over network, right? We just can stop read()'ing before we reached the end of result set, and continue at pg_fetch as needed. This way server does not need to do any of cursor creation/destruction work. Not a big win, but combined with reduced memory usage at client side, it is a win-win situation. -- vda
On Monday 11 July 2005 03:38, Alvaro Herrera wrote:
> On Sun, Jul 10, 2005 at 01:05:10PM +0300, Denis Vlasenko wrote:
> > On Thursday 07 July 2005 20:43, Alvaro Herrera wrote:
>
> > > Really? I thought what really happened is you had to get the results
> > > one at a time using the pg_fetch family of functions. If that is true,
> > > then it's possible to make the driver fake having the whole table by
> > > using a cursor. (Even if PHP doesn't do it, it's possible for OCI to do
> > > it behind the scenes.)
> >
> > Even without cursor, result can be read incrementally.
> >
> > I mean, query result is transferred over network, right?
> > We just can stop read()'ing before we reached the end of result set,
> > and continue at pg_fetch as needed.
>
> It's not that simple. libpq is designed to read whole result sets at a
> time; there's no support for reading incrementally from the server.
> Other problem is that neither libpq nor the server know how many tuples
> the query will return, until the whole query is executed. Thus,
> pg_numrows (for example) wouldn't work at all, which is a showstopper
> for many PHP scripts.
>
> In short, it can be made to work, but it's not as simple as you put it.
This sounds reasonable.
Consider my posts in this thread as user wish to
* libpq and network protocol to be changed to allow for incremental reads
of executed queries and for multiple outstanding result sets,
or, if above thing looks unsurmountable at the moment,
* libpq-only change as to allow incremental reads of single outstanding
result set. Attempt to use pg_numrows, etc, or attempt to execute
another query forces libpq to read and store all remaining rows
in client's memory (i.e. current behaviour).
--
vda
Denis Vlasenko <vda@ilport.com.ua> writes:
> Consider my posts in this thread as user wish to
> * libpq and network protocol to be changed to allow for incremental reads
> of executed queries and for multiple outstanding result sets,
> or, if above thing looks unsurmountable at the moment,
> * libpq-only change as to allow incremental reads of single outstanding
> result set. Attempt to use pg_numrows, etc, or attempt to execute
> another query forces libpq to read and store all remaining rows
> in client's memory (i.e. current behaviour).
This isn't going to happen because it would be a fundamental change in
libpq's behavior and would undoubtedly break a lot of applications.
The reason it cannot be done transparently is that you would lose the
guarantee that a query either succeeds or fails: it would be entirely
possible to return some rows to the application and only later get a
failure.
You can have this behavior today, though, as long as you are willing to
work a little harder at it --- just declare some cursors and then FETCH
in convenient chunks from the cursors.
regards, tom lane
On Wednesday 13 July 2005 17:43, Tom Lane wrote: > Denis Vlasenko <vda@ilport.com.ua> writes: > > Consider my posts in this thread as user wish to > > * libpq and network protocol to be changed to allow for incremental reads > > of executed queries and for multiple outstanding result sets, > > or, if above thing looks unsurmountable at the moment, > > * libpq-only change as to allow incremental reads of single outstanding > > result set. Attempt to use pg_numrows, etc, or attempt to execute > > another query forces libpq to read and store all remaining rows > > in client's memory (i.e. current behaviour). > > This isn't going to happen because it would be a fundamental change in > libpq's behavior and would undoubtedly break a lot of applications. > The reason it cannot be done transparently is that you would lose the > guarantee that a query either succeeds or fails: it would be entirely > possible to return some rows to the application and only later get a > failure. > > You can have this behavior today, though, as long as you are willing to > work a little harder at it --- just declare some cursors and then FETCH > in convenient chunks from the cursors. Thanks, I already tried that. It works. -- vda
Denis Vlasenko <vda@ilport.com.ua> writes:
> On Wednesday 13 July 2005 17:43, Tom Lane wrote:
>> The reason it cannot be done transparently is that you would lose the
>> guarantee that a query either succeeds or fails: it would be entirely
>> possible to return some rows to the application and only later get a
>> failure.
> What failures are likely?
Consider
select x, 1/x from foo;
where x is zero in the 10,000'th row ...
regards, tom lane
Are you saying that if I execute a statement like:
select * from "TableWithHundredsOfMillionsOfRows"
that the entire table will be copied to a result set before returning
the first row? Is this result set built in RAM on the server side?
I am interested primarily in the behavior under JDBC, although psql is
also significant. If streaming results to the client as they are
obtained is considered to be something which can only be done by a
cursor (an interpretation I haven't seen in other products), is there
any way to get a SELECT statement to be treated as a cursor without
explicitly issuing DECLARE CURSOR and FETCH statements? (For example,
some other products take an invocation of the JDBC
Statement.setCursorName method as a clue to use a cursor for a SELECT
using that Statement object, while this method is a no-op for
PostgreSQL's JDBC driver.)
I am fixing some portability flaws in software which has been using
Sybase so that is will also support PostgreSQL. Under Sybase, results
are always returned as soon as possible (and it is therefore entirely
possible to receive some rows and then receive an exception). Since our
software needs to be portable enough to support Sybase's optimistic
"streaming" technique, there is no particular benefit for us in the
strategy used by PostgreSQL in this regard, and it may cause problems
with a few of the larger results sets. I suspect that overall
performance for most queries will be about the same either way, as
PostgreSQL will reduce concurrency on a single request, but is likely to
make up for it by reducing task switching and blocking.
Thanks for any info.
-Kevin
>>> Tom Lane <tgl@sss.pgh.pa.us> 07/13/05 9:43 AM >>>
Denis Vlasenko <vda@ilport.com.ua> writes:
> Consider my posts in this thread as user wish to
> * libpq and network protocol to be changed to allow for incremental
reads
> of executed queries and for multiple outstanding result sets,
> or, if above thing looks unsurmountable at the moment,
> * libpq-only change as to allow incremental reads of single
outstanding
> result set. Attempt to use pg_numrows, etc, or attempt to execute
> another query forces libpq to read and store all remaining rows
> in client's memory (i.e. current behaviour).
This isn't going to happen because it would be a fundamental change in
libpq's behavior and would undoubtedly break a lot of applications.
The reason it cannot be done transparently is that you would lose the
guarantee that a query either succeeds or fails: it would be entirely
possible to return some rows to the application and only later get a
failure.
You can have this behavior today, though, as long as you are willing to
work a little harder at it --- just declare some cursors and then FETCH
in convenient chunks from the cursors.
regards, tom lane
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> Are you saying that if I execute a statement like:
> select * from "TableWithHundredsOfMillionsOfRows"
> that the entire table will be copied to a result set before returning
> the first row?
libpq does things that way, yes.
> Is this result set built in RAM on the server side?
No.
> I am interested primarily in the behavior under JDBC, although psql is
> also significant.
JDBC does things its own way; I believe it is possible to fetch a
resultset in a streaming fashion in JDBC, but you'd have to ask them.
The point of this discussion is simply that the current libpq API does
not permit streaming resultsets. This is not a limitation of the
server, nor of the protocol, nor even really of libpq itself if you
were willing to invent a suitable new set of API definitions for a
streaming-resultset interface. But we aren't going to change the
existing API to do streaming behind the client's back, because it won't
be transparent.
regards, tom lane
On Mon, 18 Jul 2005, Kevin Grittner wrote: > Are you saying that if I execute a statement like: > select * from "TableWithHundredsOfMillionsOfRows" > that the entire table will be copied to a result set before returning > the first row? Is this result set built in RAM on the server side? > > I am interested primarily in the behavior under JDBC, although psql is > also significant. http://jdbc.postgresql.org/documentation/80/query.html#query-with-cursor Kris Jurka