From: | claudia(dot)amorim(at)pcinformatica(dot)com(dot)br |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Problems with PostGreSQL and Windows 2003 |
Date: | 2007-11-25 04:53:03 |
Message-ID: | 58924.189.5.149.224.1195966383.squirrel@smart |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hello,
I'm having serious problems with PostGreSQL and Windows Server 2003
Enterprise Edition. The PostgreSQL Server doesn't start if I set the shared
buffers higher than 1GB. All my programs can use only 3 GB of RAM and I have 8GB
of RAM.
When I monitor the processes I can see that PostGreSQL allocs only 700 MB of
memory, and
my application 2GB. Total: 3GB.
When I try to execute a query in a table about 4 milion registers, my
application crashes with an error 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.
My program was made in Delphi 2006, and I use ADO via ODBC to connect to
PostGreSQL.
The configuration:
PostGreSQL 8.2.5
O.S: Windows Server 2003 Enterprise Edition
Service Pack 2
Computer:
dual quad core Intel(R) Xeon(R) CPU E5345 @ 2.33GHz
8GB of RAM
Physical Address Extension
3 HDs in RAID-5
My boot.ini:
[boot loader]
timeout=30
default=multi(0)disk(0)rdisk(0)partition(2)\WINDOWS
[operating systems]
multi(0)disk(0)rdisk(0)partition(2)\WINDOWS="Windows Server 2003, Enterprise"
/fastdetect /PAE /NoExecute=OptOut /3GB
PostGreSQL.conf:
shared_buffers = 1024MB # min 128kB or max_connections*16kB
# (change requires restart)
temp_buffers = 32MB # min 800kB
#max_prepared_transactions = 5 # can be 0 or more
# (change requires restart)
# Note: increasing max_prepared_transactions costs ~600 bytes of shared memory
# per transaction slot, plus lock space (see max_locks_per_transaction).
work_mem =16MB # min 64kB
maintenance_work_mem = 256MB # min 1MB
max_stack_depth = 2MB # min 100kB
# - Free Space Map -
max_fsm_pages = 409600 # min max_fsm_relations*16, 6 bytes each
# (change requires restart)
#max_fsm_relations = 1000 # min 100, ~70 bytes each
# (change requires restart)
#---------------------------------------------------------------------------
# WRITE AHEAD LOG
#---------------------------------------------------------------------------
# - Checkpoints -
checkpoint_segments = 128 # in logfile segments, min 1, 16MB each
checkpoint_timeout = 15min # range 30s-1h
checkpoint_warning = 30s # 0 is off
#---------------------------------------------------------------------------
# QUERY TUNING
#---------------------------------------------------------------------------
effective_cache_size = 5120MB
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 Amorim.
From | Date | Subject | |
---|---|---|---|
Next Message | Alex Turner | 2007-11-25 07:28:46 | Re: Disk arrangement in a cheap server |
Previous Message | Laurent CARON | 2007-11-24 23:35:46 | Re: Migrating from 32 to 64 bit |