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
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