pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Scott Marlowe <scott.marlowe@gmail.com>
To: Cláudia Macedo Amorim <claudia.amorim@pcinformatica.com.br>
Cc: pgsql-performance@postgresql.org
Subject: Re: Problems with + 1 million record table
Date: Fri, 5 Oct 2007 10:01:59 -0500
Message-ID: <dcc563d10710050801x1c652587k99c3b3e564831e3e@mail.gmail.com> (raw)
In-Reply-To: <002001c8075c$c9736df0$9fc8a8c0@pcserver.pcinformatica.com.br>
References: <002001c8075c$c9736df0$9fc8a8c0@pcserver.pcinformatica.com.br>

On 10/5/07, Cláudia Macedo Amorim <claudia.amorim@pcinformatica.com.br> wrote:
>
> I'm new in PostGreSQL and I need some help.
>
> I have a table with ~2 million records. Queries in this table are too slow
> and some are not completed.
> I think it must be a simple question to solve but, I'm trying without
> success. I'm worried because next week I will need to work with tables
> with ~100 million records.
>
> I'm using:
> O.S.: Windows XP;
> PostgreSQL 8.2;
> Index type: btree.
>
> I have 2 GB of RAM.
>
> POSTGRESQL XXX.LOG:
>
> <2007-10-05 09:01:42%SELECT> LOG:  could not send data to client: Unknown
> winsock error 10061
> <2007-10-05 09:03:03%idle> LOG:  could not receive data from client: Unknown
> winsock error 10061
> <2007-10-05 09:03:03%idle> LOG:  unexpected EOF on client connection

This looks like your client is dying on receiving too much data.  You
can either try to fix the client to handle more data, which isn't the
best way to proceed, or you can retrieve your data with a cursor a
chunk at a time.

> PSQLODBC.LOG:
> [13236.501]CONN ERROR: func=SC_execute, desc='(null)', errnum=109,
> errmsg='Out of memory while reading tuples.'

Assuming this is the client side error, yes, you're simply reading too
many rows at once.

> POSTGRESQL.CONF:
> shared_buffers = 512MB   # min 128kB or max_connections*16kB

Reasonable for  a machine with 2 G ram.

> work_mem = 256MB    # min 64kB

If and only if you have one or two users, this is ok.  Otherwise it's
a bit high.

Take a look at cursors, here's the declare ref page:

http://www.postgresql.org/docs/8.2/static/sql-declare.html



view thread (5+ messages)  latest in thread

Message-ID: <dcc563d10710050801x1c652587k99c3b3e564831e3e@mail.gmail.com>
Permalink:  ../dcc563d10710050801x1c652587k99c3b3e564831e3e@mail.gmail.com/
Also on:    postgresql.org/message-id/dcc563d10710050801x1c652587k99c3b3e564831e3e@mail.gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: scott.marlowe@gmail.com, claudia.amorim@pcinformatica.com.br
  Subject: Re: Problems with + 1 million record table
  In-Reply-To: <dcc563d10710050801x1c652587k99c3b3e564831e3e@mail.gmail.com>
  References: <002001c8075c$c9736df0$9fc8a8c0@pcserver.pcinformatica.com.br> <dcc563d10710050801x1c652587k99c3b3e564831e3e@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox