From: claudia.amorim@pcinformatica.com.br
To: claudia.amorim@pcinformatica.com.br
Cc: Heikki Linnakangas <heikki@enterprisedb.com>
Cc: pgsql-performance@postgresql.org
Cc: claudiacomput@hotmail.com
Subject: Re: Problems with PostGreSQL and Windows 2003
Date: Sun, 25 Nov 2007 02:57:01 -0200 (BRST)
Message-ID: <58995.189.5.149.224.1195966621.squirrel@smart> (raw)
In-Reply-To: <50662.189.5.149.224.1195905625.squirrel@smart>
References: <50326.189.5.149.224.1195860047.squirrel@smart>
<4747E684.2000509@enterprisedb.com>
<50662.189.5.149.224.1195905625.squirrel@smart>
Hello,
I forgot to say that I changed work_mem to 16 MB but I didn't have sucess.
I received the same
error message.
Thanks,
Cláudia Amorim.
> Hi,
>
> When I set shared buffers higher than 1GB, PostGreSQL doens't start.
>
> When my application crashes I receive a message "Out of memory" or
> "invalid sql statement". But the sql statement is ok - if I execute it
> in a table with less registers, it works and it is very simple. When I monitor
> the processes
> I can see that PostGreSQL allocs only 700 MB of memory, and my application 2GB.
> Total: 3GB.
> My program was made in Delphi 2006, and I use ADO via ODBC to connect to
> PostGreSQL.
>
> The structure of my table:
>
> CREATE TABLE "public"."fato_financeiro" (
> "CODCLI" VARCHAR(6),
> "PREST" VARCHAR(4) NOT NULL,
> "NUMTRANSVENDA" VARCHAR(10) NOT NULL,
> "RECNUM" VARCHAR(8) NOT NULL,
> "CODFORNEC" VARCHAR(8),
> "TIPO" VARCHAR(2),
> "NUMDOC" VARCHAR(10),
> "PREST_1" VARCHAR(4),
> "VALOR" DOUBLE PRECISION,
> "DTEMISSAO" TIMESTAMP WITH TIME ZONE,
> "DTVENC" TIMESTAMP WITH TIME ZONE,
> "DTPAG" TIMESTAMP WITH TIME ZONE,
> "VPAGO" DOUBLE PRECISION,
> "PAGO_PAG" VARCHAR(9),
> "ATRASADO" VARCHAR(3),
> CONSTRAINT "fato_financeiro_idx" PRIMARY KEY("PREST", "NUMTRANSVENDA",
> "RECNUM")
> ) WITHOUT OIDS;
>
>
> SQL statement:
>
> select
> fato_financeiro."TIPO",
> fato_financeiro."NUMDOC",
> fato_financeiro."PREST",
> fato_financeiro."NUMDOC",
> fato_financeiro."DTVENC",
> fato_financeiro."DTPAG",
> fato_financeiro."PAGO_PAG",
> fato_financeiro."ATRASADO",
> fato_financeiro."CODCLI",
> fato_financeiro."CODFORNEC",
> fato_financeiro."DTEMISSAO"
> from fato_financeiro
>
>
>
> Thanks,
> Cláudia.
>
>
>
>
>> claudia.amorim@pcinformatica.com.br wrote:
>>> I'm having serious peformance problems with PostGreSQL and Windows Server
>>> 2003
>>> Enterprise Edition. The PostgreSQL Server don't starts if I set the shared
>>> buffers high than
>>> 1GB. All my programs can use only 3 GB of RAM and I have 8GB of RAM.
>>> When I try to execute a query in a table about 4 milion registers, my
>>> application crashes with an error memory message.
>>
>> What error message do you get if setting shared_buffers higher than 1GB?
>> Exactly what error message do you get when the application crashes?
>>
>>> work_mem =512MB # min 64kB
>>
>> That's way too high for most applications. In a complex query, each sort
>> or hash node can will use up work_mem amount of memory. That means that
>> if you have a very complex query with several such nodes, it will run
>> out of memory. Try something like 16MB.
>>
>>
>> --
>> Heikki Linnakangas
>> EnterpriseDB http://www.enterprisedb.com
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 6: explain analyze is your friend
>>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: claudia.amorim@pcinformatica.com.br, heikki@enterprisedb.com, claudiacomput@hotmail.com
Subject: Re: Problems with PostGreSQL and Windows 2003
In-Reply-To: <58995.189.5.149.224.1195966621.squirrel@smart>
* 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