psql client memory usage

Tim Kane wrote:

I have a fairly simple query, running on a particularly large table. For
illustration:

echo "select * from really_big_table;" | psql my_database > /dev/null

See psql's FETCH_COUNT. From the manpage:

FETCH_COUNT
If this variable is set to an integer value > 0, the results
of
SELECT queries are fetched and displayed in groups of that
many
rows, rather than the default behavior of collecting the
entire
result set before display. Therefore only a limited amount of
memory is used, regardless of the size of the result set.

Best regards,
--
Daniel
PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

(13/09/06 21:06), Tim Kane wrote:

Hi all,

I have a fairly simple query, running on a particularly large table. For
illustration:

echo "select * from really_big_table;" | psql my_database > /dev/null

When I monitor the memory usage of the psql session, it continually grows.
In fact, for this particularly large table � it grows to the point of
consuming all swap, before the OOM killer takes steps to resolve it.
Clearly, this isn't what I'd like to happen.

My settings are:
Postgresql 9.1.9
work_mem = 256MB
effective_cache_size = 12GB
shared_buffers = 6GB

I have 24GB physical ram to play with.

This is a client side problem (not server size).
See the description of FETCH_COUNT, please.
http://www.postgresql.org/docs/9.2/static/app-psql.html

echo "select * from really_big_table;" | psql --variable=FETCH_COUNT=100
my_database > /dev/null

Regards,

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Ahh. All these years (albeit sporadic), I never knew about FETCH_COUNT.
That makes sense. Thanks muchly.

On 06/09/2013 14:11, "Suzuki Hironobu" <hironobu@interdb.jp> wrote:

(13/09/06 21:06), Tim Kane wrote:

Hi all,

I have a fairly simple query, running on a particularly large table.
For
illustration:

echo "select * from really_big_table;" | psql my_database > /dev/null

When I monitor the memory usage of the psql session, it continually
grows.
In fact, for this particularly large table ­ it grows to the point of
consuming all swap, before the OOM killer takes steps to resolve it.
Clearly, this isn't what I'd like to happen.

My settings are:
Postgresql 9.1.9
work_mem = 256MB
effective_cache_size = 12GB
shared_buffers = 6GB

I have 24GB physical ram to play with.

This is a client side problem (not server size).
See the description of FETCH_COUNT, please.
http://www.postgresql.org/docs/9.2/static/app-psql.html

echo "select * from really_big_table;" | psql --variable=FETCH_COUNT=100
my_database > /dev/null

Regards,

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Fri, Sep 6, 2013 at 8:19 AM, Tim Kane <tim.kane@gmail.com> wrote:

Ahh. All these years (albeit sporadic), I never knew about FETCH_COUNT.
That makes sense. Thanks muchly.

Not your fault: FETCH_COUNT is a hack IMO. The real issue was that
libpq (until recently) forced the entire result into memory before it
was returned to the caller. We can now in libpq (thanks Marko) that
allows process rows as they come in. I expect soon psql will be
adjusted to utilize that new API (although exactly how is unclear);
runaway memory consumption in libpq/psql burns a *lot* of people.

I personally find cursors to be baroque and rarely use them except
internally inside pl/pgsql functions.

merlin

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Sep 6, 2013, at 6:56 AM, Merlin Moncure <mmoncure@gmail.com> wrote:

On Fri, Sep 6, 2013 at 8:19 AM, Tim Kane <tim.kane@gmail.com> wrote:

Ahh. All these years (albeit sporadic), I never knew about FETCH_COUNT.
That makes sense. Thanks muchly.

Not your fault: FETCH_COUNT is a hack IMO. The real issue was that
libpq (until recently) forced the entire result into memory before it
was returned to the caller. We can now in libpq (thanks Marko) that
allows process rows as they come in. I expect soon psql will be
adjusted to utilize that new API (although exactly how is unclear);

what version did/does this come into effect?

alan

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Mon, Sep 09/09/13, 2013 at 01:56:33PM -0700, Alan Nilsson wrote:

On Sep 6, 2013, at 6:56 AM, Merlin Moncure <mmoncure@gmail.com> wrote:

On Fri, Sep 6, 2013 at 8:19 AM, Tim Kane <tim.kane@gmail.com> wrote:

Ahh. All these years (albeit sporadic), I never knew about FETCH_COUNT.
That makes sense. Thanks muchly.

Not your fault: FETCH_COUNT is a hack IMO. The real issue was that
libpq (until recently) forced the entire result into memory before it
was returned to the caller. We can now in libpq (thanks Marko) that
allows process rows as they come in. I expect soon psql will be
adjusted to utilize that new API (although exactly how is unclear);

what version did/does this come into effect?

This is with PostgreSQL 9.2, see PQsetSingleRowMode:
http://www.postgresql.org/docs/9.2/static/libpq-single-row-mode.html

I do not expect psql will be adjusted to utilize the new API:
/messages/by-id/CAEYkp92z2w3VBs4uxWPwub7k4hGW-vEPW_WNSui9R5T+cGpLYw@mail.gmail.com

-Ryan Kelly

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general