Thread: Problems with PostGreSQL and Windows 2003
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.
On Sun, Nov 25, 2007 at 02:53:03AM -0200, claudia.amorim@pcinformatica.com.br wrote: > 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. You do realise that the "shared buffers" only controls the amount of memory that is shared. Each backend however can access additional memory seperately from all the other processes. So your calculation should be 2GB + 700MB *per connection* which can be much higher. > 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 guess is you gave so much memory to shared buffers you did not leave enough for normal work. Try reducing your shared buffers to something more reasonable, like 256MB or 128MB. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Those who make peaceful revolution impossible will make violent revolution inevitable. > -- John F Kennedy
Attachment
Hello, I changeded shared buffers to 256 MB. But I still receiving the error message. So I changed to 128 MB, and I received the error again. I'm using a cursor via ODBC. Thanks, Cláudia. > On Sun, Nov 25, 2007 at 02:53:03AM -0200, claudia.amorim@pcinformatica.com.br > wrote: >> 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. > > You do realise that the "shared buffers" only controls the amount of > memory that is shared. Each backend however can access additional > memory seperately from all the other processes. So your calculation > should be 2GB + 700MB *per connection* which can be much higher. > >> 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 guess is you gave so much memory to shared buffers you did not leave > enough for normal work. Try reducing your shared buffers to something > more reasonable, like 256MB or 128MB. > > Have a nice day, > -- > Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ >> Those who make peaceful revolution impossible will make violent revolution >> inevitable. >> -- John F Kennedy >
On Sun, Nov 25, 2007 at 10:38:31AM -0200, claudia.amorim@pcinformatica.com.br wrote: > Hello, > > I changeded shared buffers to 256 MB. But I still receiving the error message. > So I changed to 128 MB, and I received the error again. > I'm using a cursor via ODBC. Are you using a cursor, because it certainly doesn't look like you're using a cursor, just a plain select. Which probably means youre client side is running out of memory. So, check you're using a cursor. Or just limit the output to however many rows you actually want. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Those who make peaceful revolution impossible will make violent revolution inevitable. > -- John F Kennedy
Attachment
Hi, Here is a piece of psqlodbc log file: [0.000]conn=02DE3A70, PGAPI_DriverConnect( in)='DSN=BI;UID=biuser;PWD=xxxxxxxxx;', fDriverCompletion=0 [0.000]DSN info: DSN='BI',server='localhost',port='5432',dbase='BI',user='biuser',passwd='xxxxx' [0.000] onlyread='0',protocol='7.4',showoid='0',fakeoidindex='0',showsystable='0' [0.000] conn_settings='',conn_encoding='(null)' [0.000] translation_dll='',translation_option='' [0.000]Driver Version='08.02.0400,200704270001' linking static Multithread library [0.000]Global Options: fetch=100, socket=4096, unknown_sizes=0, max_varchar_size=255, max_longvarchar_size=8190 [0.000] disable_optimizer=0, ksqo=1, unique_index=1, use_declarefetch=1 [0.000] text_as_longvarchar=1, unknowns_as_longvarchar=0, bools_as_char=1 NAMEDATALEN=64 [0.000] extra_systable_prefixes='dd_;', conn_settings='' conn_encoding='' [0.046] [ PostgreSQL version string = '8.2.5' ] [0.046] [ PostgreSQL version number = '8.2' ] [0.046]conn=02DE3A70, query='select oid, typbasetype from pg_type where typname = 'lo'' [0.046]NOTICE from backend during send_query: 'SLOG' [0.046]NOTICE from backend during send_query: 'C00000' [0.046]NOTICE from backend during send_query: 'Mstatement: select oid, typbasetype from pg_type where typname = 'lo'' [0.046]NOTICE from backend during send_query: 'Fpostgres.c' [0.046]NOTICE from backend during send_query: 'L811' [0.046]NOTICE from backend during send_query: 'Rexec_simple_query' [0.046] [ fetched 1 rows ] [0.046] [ Large Object oid = 17288 ] [0.046] [ Client encoding = 'LATIN9' (code = 16) ] [0.046]conn=02DE3A70, PGAPI_DriverConnect(out)='DSN=BI;DATABASE=BI;SERVER=localhost;PORT=5432;UID=biuser;PWD=xxxxxxxxx;SSLmode=disable;ReadOnly=0;Protocol=7.4-1;FakeOidIndex=0;ShowOidColumn=0;RowVersioning=0;ShowSystemTables=0;ConnSettings=;Fetch=100;Socket=4096;UnknownSizes=0;MaxVarcharSize=255;MaxLongVarcharSize=8190;Debug=0;CommLog=1;Optimizer=0;Ksqo=1;UseDeclareFetch=1;TextAsLongVarchar=1;UnknownsAsLongVarchar=0;BoolsAsChar=1;Parse=0;CancelAsFreeStmt=0;ExtraSysTablePrefixes=dd_;;LFConversion=1;UpdatableCursors=1;DisallowPremature=0;TrueIsMinus1=0;BI=0;ByteaAsLongVarBinary=0;UseServerSidePrepare=0;LowerCaseIdentifier=0;XaOpt=1' [0.062]STATEMENT ERROR: func=set_statement_option, desc='', errnum=30, errmsg='The option may be for MS SQL Server(Set)' [0.062] ------------------------------------------------------------ [0.062] hdbc=02DE3A70, stmt=02DE85C8, result=00000000 [0.062] prepare=0, internal=0 [0.062] bindings=00000000, bindings_allocated=0 [0.062] parameters=02DE8F48, parameters_allocated=1 [0.062] statement_type=-2, statement='(NULL)' [0.062] stmt_with_params='(NULL)' [0.062] data_at_exec=-1, current_exec_param=-1, put_data=0 [0.062] currTuple=-1, current_col=-1, lobj_fd=-1 [0.062] maxRows=0, rowset_size=1, keyset_size=0, cursor_type=0, scroll_concurrency=1 [0.062] cursor_name='' [0.062] ----------------QResult Info ------------------------------- [0.062]CONN ERROR: func=set_statement_option, desc='', errnum=0, errmsg='(NULL)' [0.062] Thanks, Cláudia. > On Sun, Nov 25, 2007 at 10:38:31AM -0200, claudia.amorim@pcinformatica.com.br > wrote: >> Hello, >> >> I changeded shared buffers to 256 MB. But I still receiving the error message. >> So I changed to 128 MB, and I received the error again. >> I'm using a cursor via ODBC. > > Are you using a cursor, because it certainly doesn't look like you're > using a cursor, just a plain select. Which probably means youre client > side is running out of memory. > > So, check you're using a cursor. Or just limit the output to however > many rows you actually want. > > Have a nice day, > -- > Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ >> Those who make peaceful revolution impossible will make violent revolution >> inevitable. >> -- John F Kennedy >