Skip site navigation (1) Skip section navigation (2)

Re: Problems with PostGreSQL and Windows 2003

From: claudia(dot)amorim(at)pcinformatica(dot)com(dot)br
To: "Heikki Linnakangas" <heikki(at)enterprisedb(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org, claudiacomput(at)hotmail(dot)com
Subject: Re: Problems with PostGreSQL and Windows 2003
Date: 2007-11-24 12:00:25
Message-ID: 50662.189.5.149.224.1195905625.squirrel@smart (view raw or flat)
Thread:
Lists: pgsql-performance
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(dot)amorim(at)pcinformatica(dot)com(dot)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
>



In response to

Responses

pgsql-performance by date

Next:From: Dean RasheedDate: 2007-11-24 12:06:01
Subject: Re: Performance problem with UNION ALL view and domains
Previous:From: Heikki LinnakangasDate: 2007-11-24 08:53:24
Subject: Re: Problems with PostGreSQL and Windows 2003

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group