pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedProblems with PostGreSQL and Windows 2003
9+ messages / 3 participants
[nested] [flat]
* Problems with PostGreSQL and Windows 2003
@ 2007-11-23 23:20 claudia.amorim@pcinformatica.com.br
2007-11-24 08:53 ` Re: Problems with PostGreSQL and Windows 2003 Heikki Linnakangas <heikki@enterprisedb.com>
0 siblings, 1 reply; 9+ messages in thread
From: claudia.amorim@pcinformatica.com.br @ 2007-11-23 23:20 UTC (permalink / raw)
To: pgsql-performance; +Cc: claudiacomput@hotmail.com
Hello,
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.
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 =512MB # 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
Thanks, Cláudia.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Problems with PostGreSQL and Windows 2003
2007-11-23 23:20 Problems with PostGreSQL and Windows 2003 claudia.amorim@pcinformatica.com.br
@ 2007-11-24 08:53 ` Heikki Linnakangas <heikki@enterprisedb.com>
2007-11-24 12:00 ` Re: Problems with PostGreSQL and Windows 2003 claudia.amorim@pcinformatica.com.br
0 siblings, 1 reply; 9+ messages in thread
From: Heikki Linnakangas @ 2007-11-24 08:53 UTC (permalink / raw)
To: claudia.amorim@pcinformatica.com.br; +Cc: pgsql-performance; claudiacomput@hotmail.com
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Problems with PostGreSQL and Windows 2003
2007-11-23 23:20 Problems with PostGreSQL and Windows 2003 claudia.amorim@pcinformatica.com.br
2007-11-24 08:53 ` Re: Problems with PostGreSQL and Windows 2003 Heikki Linnakangas <heikki@enterprisedb.com>
@ 2007-11-24 12:00 ` claudia.amorim@pcinformatica.com.br
2007-11-25 04:57 ` Re: Problems with PostGreSQL and Windows 2003 claudia.amorim@pcinformatica.com.br
0 siblings, 1 reply; 9+ messages in thread
From: claudia.amorim@pcinformatica.com.br @ 2007-11-24 12:00 UTC (permalink / raw)
To: Heikki Linnakangas <heikki@enterprisedb.com>; +Cc: pgsql-performance; claudiacomput@hotmail.com
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
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Problems with PostGreSQL and Windows 2003
2007-11-23 23:20 Problems with PostGreSQL and Windows 2003 claudia.amorim@pcinformatica.com.br
2007-11-24 08:53 ` Re: Problems with PostGreSQL and Windows 2003 Heikki Linnakangas <heikki@enterprisedb.com>
2007-11-24 12:00 ` Re: Problems with PostGreSQL and Windows 2003 claudia.amorim@pcinformatica.com.br
@ 2007-11-25 04:57 ` claudia.amorim@pcinformatica.com.br
2007-11-25 06:25 ` Re: Problems with PostGreSQL and Windows 2003 Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: claudia.amorim@pcinformatica.com.br @ 2007-11-25 04:57 UTC (permalink / raw)
To: claudia.amorim@pcinformatica.com.br; +Cc: Heikki Linnakangas <heikki@enterprisedb.com>; pgsql-performance; claudiacomput@hotmail.com
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
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Problems with PostGreSQL and Windows 2003
2007-11-23 23:20 Problems with PostGreSQL and Windows 2003 claudia.amorim@pcinformatica.com.br
2007-11-24 08:53 ` Re: Problems with PostGreSQL and Windows 2003 Heikki Linnakangas <heikki@enterprisedb.com>
2007-11-24 12:00 ` Re: Problems with PostGreSQL and Windows 2003 claudia.amorim@pcinformatica.com.br
2007-11-25 04:57 ` Re: Problems with PostGreSQL and Windows 2003 claudia.amorim@pcinformatica.com.br
@ 2007-11-25 06:25 ` Scott Marlowe <scott.marlowe@gmail.com>
2007-11-25 12:42 ` Re: Problems with PostGreSQL and Windows 2003 claudia.amorim@pcinformatica.com.br
0 siblings, 1 reply; 9+ messages in thread
From: Scott Marlowe @ 2007-11-25 06:25 UTC (permalink / raw)
To: claudia.amorim@pcinformatica.com.br; +Cc: Heikki Linnakangas <heikki@enterprisedb.com>; pgsql-performance; claudiacomput@hotmail.com
On Nov 24, 2007 10:57 PM, <claudia.amorim@pcinformatica.com.br> wrote:
> 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.
The error message you're getting is from your client because it's
getting too big of a result set back at once.
Try using a cursor
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Problems with PostGreSQL and Windows 2003
2007-11-23 23:20 Problems with PostGreSQL and Windows 2003 claudia.amorim@pcinformatica.com.br
2007-11-24 08:53 ` Re: Problems with PostGreSQL and Windows 2003 Heikki Linnakangas <heikki@enterprisedb.com>
2007-11-24 12:00 ` Re: Problems with PostGreSQL and Windows 2003 claudia.amorim@pcinformatica.com.br
2007-11-25 04:57 ` Re: Problems with PostGreSQL and Windows 2003 claudia.amorim@pcinformatica.com.br
2007-11-25 06:25 ` Re: Problems with PostGreSQL and Windows 2003 Scott Marlowe <scott.marlowe@gmail.com>
@ 2007-11-25 12:42 ` claudia.amorim@pcinformatica.com.br
2007-11-25 14:33 ` Re: Problems with PostGreSQL and Windows 2003 Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: claudia.amorim@pcinformatica.com.br @ 2007-11-25 12:42 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: claudia.amorim@pcinformatica.com.br, "Heikki Linnakangas" <heikki@enterprisedb.com>; pgsql-performance; claudiacomput@hotmail.com
I'm already using a cursor via ODBC.
> On Nov 24, 2007 10:57 PM, <claudia.amorim@pcinformatica.com.br> wrote:
>> 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.
>
> The error message you're getting is from your client because it's
> getting too big of a result set back at once.
>
> Try using a cursor
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Problems with PostGreSQL and Windows 2003
2007-11-23 23:20 Problems with PostGreSQL and Windows 2003 claudia.amorim@pcinformatica.com.br
2007-11-24 08:53 ` Re: Problems with PostGreSQL and Windows 2003 Heikki Linnakangas <heikki@enterprisedb.com>
2007-11-24 12:00 ` Re: Problems with PostGreSQL and Windows 2003 claudia.amorim@pcinformatica.com.br
2007-11-25 04:57 ` Re: Problems with PostGreSQL and Windows 2003 claudia.amorim@pcinformatica.com.br
2007-11-25 06:25 ` Re: Problems with PostGreSQL and Windows 2003 Scott Marlowe <scott.marlowe@gmail.com>
2007-11-25 12:42 ` Re: Problems with PostGreSQL and Windows 2003 claudia.amorim@pcinformatica.com.br
@ 2007-11-25 14:33 ` Scott Marlowe <scott.marlowe@gmail.com>
2007-11-25 16:19 ` Re: Problems with PostGreSQL and Windows 2003 claudia.amorim@pcinformatica.com.br
0 siblings, 1 reply; 9+ messages in thread
From: Scott Marlowe @ 2007-11-25 14:33 UTC (permalink / raw)
To: claudia.amorim@pcinformatica.com.br; +Cc: Heikki Linnakangas <heikki@enterprisedb.com>; pgsql-performance; claudiacomput@hotmail.com
Are you then trying to process the whole data set at once? I'm pretty
certain the issue is your app, not pgsql, running out of memory.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Problems with PostGreSQL and Windows 2003
2007-11-23 23:20 Problems with PostGreSQL and Windows 2003 claudia.amorim@pcinformatica.com.br
2007-11-24 08:53 ` Re: Problems with PostGreSQL and Windows 2003 Heikki Linnakangas <heikki@enterprisedb.com>
2007-11-24 12:00 ` Re: Problems with PostGreSQL and Windows 2003 claudia.amorim@pcinformatica.com.br
2007-11-25 04:57 ` Re: Problems with PostGreSQL and Windows 2003 claudia.amorim@pcinformatica.com.br
2007-11-25 06:25 ` Re: Problems with PostGreSQL and Windows 2003 Scott Marlowe <scott.marlowe@gmail.com>
2007-11-25 12:42 ` Re: Problems with PostGreSQL and Windows 2003 claudia.amorim@pcinformatica.com.br
2007-11-25 14:33 ` Re: Problems with PostGreSQL and Windows 2003 Scott Marlowe <scott.marlowe@gmail.com>
@ 2007-11-25 16:19 ` claudia.amorim@pcinformatica.com.br
2007-11-26 09:39 ` Re: Problems with PostGreSQL and Windows 2003 Heikki Linnakangas <heikki@enterprisedb.com>
0 siblings, 1 reply; 9+ messages in thread
From: claudia.amorim@pcinformatica.com.br @ 2007-11-25 16:19 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: claudia.amorim@pcinformatica.com.br, "Heikki Linnakangas" <heikki@enterprisedb.com>; pgsql-performance; claudiacomput@hotmail.com
Hi,
I'm using a cursor.
Here is the a piece of log file (psqlodbc):
[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.
> Are you then trying to process the whole data set at once? I'm pretty
> certain the issue is your app, not pgsql, running out of memory.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Problems with PostGreSQL and Windows 2003
2007-11-23 23:20 Problems with PostGreSQL and Windows 2003 claudia.amorim@pcinformatica.com.br
2007-11-24 08:53 ` Re: Problems with PostGreSQL and Windows 2003 Heikki Linnakangas <heikki@enterprisedb.com>
2007-11-24 12:00 ` Re: Problems with PostGreSQL and Windows 2003 claudia.amorim@pcinformatica.com.br
2007-11-25 04:57 ` Re: Problems with PostGreSQL and Windows 2003 claudia.amorim@pcinformatica.com.br
2007-11-25 06:25 ` Re: Problems with PostGreSQL and Windows 2003 Scott Marlowe <scott.marlowe@gmail.com>
2007-11-25 12:42 ` Re: Problems with PostGreSQL and Windows 2003 claudia.amorim@pcinformatica.com.br
2007-11-25 14:33 ` Re: Problems with PostGreSQL and Windows 2003 Scott Marlowe <scott.marlowe@gmail.com>
2007-11-25 16:19 ` Re: Problems with PostGreSQL and Windows 2003 claudia.amorim@pcinformatica.com.br
@ 2007-11-26 09:39 ` Heikki Linnakangas <heikki@enterprisedb.com>
0 siblings, 0 replies; 9+ messages in thread
From: Heikki Linnakangas @ 2007-11-26 09:39 UTC (permalink / raw)
To: claudia.amorim@pcinformatica.com.br; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; pgsql-performance; claudiacomput@hotmail.com
There is no mention of Out of Memory in that piece of log.
claudia.amorim@pcinformatica.com.br wrote:
> Hi,
>
> I'm using a cursor.
>
> Here is the a piece of log file (psqlodbc):
>
> [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.
>
>
>
>> Are you then trying to process the whole data set at once? I'm pretty
>> certain the issue is your app, not pgsql, running out of memory.
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 2: Don't 'kill -9' the postmaster
>>
>
>
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2007-11-26 09:39 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-11-23 23:20 Problems with PostGreSQL and Windows 2003 claudia.amorim@pcinformatica.com.br
2007-11-24 08:53 ` Heikki Linnakangas <heikki@enterprisedb.com>
2007-11-24 12:00 ` claudia.amorim@pcinformatica.com.br
2007-11-25 04:57 ` claudia.amorim@pcinformatica.com.br
2007-11-25 06:25 ` Scott Marlowe <scott.marlowe@gmail.com>
2007-11-25 12:42 ` claudia.amorim@pcinformatica.com.br
2007-11-25 14:33 ` Scott Marlowe <scott.marlowe@gmail.com>
2007-11-25 16:19 ` claudia.amorim@pcinformatica.com.br
2007-11-26 09:39 ` Heikki Linnakangas <heikki@enterprisedb.com>
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