Обсуждение: Network permormance under windows
Hello, We used Postgresql 7.1 under Linux and recently we have changed it to Postgresql 8.1 under Windows XP. Our application uses ODBC and when we try to get some information from the server throw a TCP connection, it's very slow. We have also tried it using psql and pgAdmin III, and we get the same results. If we try it locally, it runs much faster. We have been searching the mailing lists, we have found many people with the same problem, but we haven't found any final solution. How can we solve this? Any help will be appreciated. Thanks in advance. Jordi.
> We used Postgresql 7.1 under Linux and recently we have changed it to > Postgresql 8.1 under Windows XP. Our application uses ODBC and when we > try to get some information from the server throw a TCP connection, it's > very slow. We have also tried it using psql and pgAdmin III, and we get > the same results. If we try it locally, it runs much faster. > > We have been searching the mailing lists, we have found many people with > the same problem, but we haven't found any final solution. > > How can we solve this? Any help will be appreciated. > > Thanks in advance. > by any chance are you working with large tuples/columns (long text, bytea, etc)? Also please define slow. Merlin
Dear Merlin,
For instance, we have this table (with 22900 tuples):
CREATE TABLE tbl_empresa
(
id_empresa int4 NOT NULL DEFAULT nextval(('seq_empresa'::text)::regclass),
ref_poblacio int4 NOT NULL,
nom varchar(50) NOT NULL,
nif varchar(12),
carrer varchar(50),
telefon varchar(13),
fax varchar(13),
email varchar(50),
lab_materials int2 DEFAULT 0,
web varchar(50),
ref_empresa int4,
ref_classificacio_empresa int4,
ref_sector_empresa int4,
control int2,
origen_volcat int2,
data_modificacio date,
plantilla int4,
tamany int2,
autoritzacio_email int2,
ref_estat_empresa int2,
CONSTRAINT tbl_clients_pkey PRIMARY KEY (id_empresa),
CONSTRAINT fk_tbl_empresa_ref_classificacio_emp FOREIGN KEY
(ref_classificacio_empresa)
REFERENCES tbl_classificacio_empresa (id_classificacio_empresa) MATCH
SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT fk_tbl_empresa_ref_empresa FOREIGN KEY (ref_empresa)
REFERENCES tbl_empresa (id_empresa) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT fk_tbl_empresa_ref_estat_emp FOREIGN KEY (ref_estat_empresa)
REFERENCES tbl_estat_empresa (id_estat_empresa) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT fk_tbl_empresa_ref_poblacio FOREIGN KEY (ref_poblacio)
REFERENCES tbl_poblacions (id_poblacio) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT fk_tbl_empresa_ref_sector_emp FOREIGN KEY (ref_sector_empresa)
REFERENCES tbl_sector_empresa (id_sector_empresa) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT
)
WITH OIDS;
When we select all data in local machine, we obtain results in 2-3
seconds aprox. In remote connections:
Postgresql 7.1 usign pgAdminII:
Network traffic generated with remote applications is about 77-80% in a
10Mb connection.
6 seconds aprox.
Postgresql 8.1 usign pgAdminIII:
Network traffic generated with remote applications is about 2-4% in a
10Mb connection.
12 seconds or more...
I feel that is a problem with TCP_NODELAY of socket options... but I
don't know.
Josep Maria
En/na Merlin Moncure ha escrit:
>>We used Postgresql 7.1 under Linux and recently we have changed it to
>>Postgresql 8.1 under Windows XP. Our application uses ODBC and when we
>>try to get some information from the server throw a TCP connection,
>>
>>
>it's
>
>
>>very slow. We have also tried it using psql and pgAdmin III, and we
>>
>>
>get
>
>
>>the same results. If we try it locally, it runs much faster.
>>
>>We have been searching the mailing lists, we have found many people
>>
>>
>with
>
>
>>the same problem, but we haven't found any final solution.
>>
>>How can we solve this? Any help will be appreciated.
>>
>>Thanks in advance.
>>
>>
>>
>by any chance are you working with large tuples/columns (long text,
>bytea, etc)?
>
>Also please define slow.
>
>Merlin
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: Don't 'kill -9' the postmaster
>
>
--
Josep Maria Pinyol i Fontseca
Responsable àrea de programació
ENDEPRO - Enginyeria de programari
Passeig Anselm Clavé, 19 Bx. 08263 Callús (Barcelona)
Tel. +34 936930018 - Mob. +34 600310755 - Fax. +34 938361994
jmpinyol@endepro.com - http://www.endepro.com
Aquest missatge i els documents en el seu cas adjunts,
es dirigeixen exclusivament al seu destinatari i poden contenir
informació reservada i/o CONFIDENCIAL, us del qual no està
autoritzat ni la divulgació del mateix, prohibit per la legislació
vigent (Llei 32/2002 SSI-CE). Si ha rebut aquest missatge per error,
li demanem que ens ho comuniqui immediatament per la mateixa via o
bé per telèfon (+34936930018) i procedeixi a la seva destrucció.
Aquest e-mail no podrà considerar-se SPAM.
Este mensaje, y los documentos en su caso anexos,
se dirigen exclusivamente a su destinatario y pueden contener
información reservada y/o CONFIDENCIAL cuyo uso no
autorizado o divulgación está prohibida por la legislación
vigente (Ley 32/2002 SSI-CE). Si ha recibido este mensaje por error,
le rogamos que nos lo comunique inmediatamente por esta misma vía o
por teléfono (+34936930018) y proceda a su destrucción.
Este e-mail no podrá considerarse SPAM.
This message and the enclosed documents are directed exclusively
to its receiver and can contain reserved and/or confidential
information, from which use isn’t allowed its divulgation, forbidden
by the current legislation (Law 32/2002 SSI-CE). If you have received
this message by mistake, we kindly ask you to communicate it to us
right away by the same way or by phone (+34936930018) and destruct it.
This e-mail can’t be considered as SPAM.
we experienced the same. had 2 win2003 servers - www and db connected to the
same router through 100mbit. the performance was quite bad. now we run the
db on the same machine as the web and everything runs smooth.
cheers,
thomas
----- Original Message -----
From: "Josep Maria Pinyol Fontseca" <jmpinyol@endepro.com>
Cc: <pgsql-performance@postgresql.org>; <info@teracat.com>
Sent: Friday, December 02, 2005 6:24 PM
Subject: Re: [PERFORM] Network permormance under windows
>
> Dear Merlin,
>
> For instance, we have this table (with 22900 tuples):
>
> CREATE TABLE tbl_empresa
> (
> id_empresa int4 NOT NULL DEFAULT nextval(('seq_empresa'::text)::regclass),
> ref_poblacio int4 NOT NULL,
> nom varchar(50) NOT NULL,
> nif varchar(12),
> carrer varchar(50),
> telefon varchar(13),
> fax varchar(13),
> email varchar(50),
> lab_materials int2 DEFAULT 0,
> web varchar(50),
> ref_empresa int4,
> ref_classificacio_empresa int4,
> ref_sector_empresa int4,
> control int2,
> origen_volcat int2,
> data_modificacio date,
> plantilla int4,
> tamany int2,
> autoritzacio_email int2,
> ref_estat_empresa int2,
> CONSTRAINT tbl_clients_pkey PRIMARY KEY (id_empresa),
> CONSTRAINT fk_tbl_empresa_ref_classificacio_emp FOREIGN KEY
> (ref_classificacio_empresa)
> REFERENCES tbl_classificacio_empresa (id_classificacio_empresa) MATCH
> SIMPLE
> ON UPDATE RESTRICT ON DELETE RESTRICT,
> CONSTRAINT fk_tbl_empresa_ref_empresa FOREIGN KEY (ref_empresa)
> REFERENCES tbl_empresa (id_empresa) MATCH SIMPLE
> ON UPDATE RESTRICT ON DELETE RESTRICT,
> CONSTRAINT fk_tbl_empresa_ref_estat_emp FOREIGN KEY (ref_estat_empresa)
> REFERENCES tbl_estat_empresa (id_estat_empresa) MATCH SIMPLE
> ON UPDATE RESTRICT ON DELETE RESTRICT,
> CONSTRAINT fk_tbl_empresa_ref_poblacio FOREIGN KEY (ref_poblacio)
> REFERENCES tbl_poblacions (id_poblacio) MATCH SIMPLE
> ON UPDATE RESTRICT ON DELETE RESTRICT,
> CONSTRAINT fk_tbl_empresa_ref_sector_emp FOREIGN KEY (ref_sector_empresa)
> REFERENCES tbl_sector_empresa (id_sector_empresa) MATCH SIMPLE
> ON UPDATE RESTRICT ON DELETE RESTRICT
> )
> WITH OIDS;
>
> When we select all data in local machine, we obtain results in 2-3 seconds
> aprox. In remote connections:
>
> Postgresql 7.1 usign pgAdminII:
> Network traffic generated with remote applications is about 77-80% in a
> 10Mb connection.
> 6 seconds aprox.
>
> Postgresql 8.1 usign pgAdminIII:
> Network traffic generated with remote applications is about 2-4% in a 10Mb
> connection.
> 12 seconds or more...
>
> I feel that is a problem with TCP_NODELAY of socket options... but I don't
> know.
>
> Josep Maria
>
>
> En/na Merlin Moncure ha escrit:
>
>>>We used Postgresql 7.1 under Linux and recently we have changed it to
>>>Postgresql 8.1 under Windows XP. Our application uses ODBC and when we
>>>try to get some information from the server throw a TCP connection,
>>>
>>it's
>>
>>>very slow. We have also tried it using psql and pgAdmin III, and we
>>>
>>get
>>
>>>the same results. If we try it locally, it runs much faster.
>>>
>>>We have been searching the mailing lists, we have found many people
>>>
>>with
>>
>>>the same problem, but we haven't found any final solution.
>>>
>>>How can we solve this? Any help will be appreciated.
>>>
>>>Thanks in advance.
>>>
>>>
>>by any chance are you working with large tuples/columns (long text,
>>bytea, etc)?
>>
>>Also please define slow.
>>
>>Merlin
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 2: Don't 'kill -9' the postmaster
>>
>
>
> --
>
> Josep Maria Pinyol i Fontseca
> Responsable àrea de programació
>
> ENDEPRO - Enginyeria de programari
> Passeig Anselm Clavé, 19 Bx. 08263 Callús (Barcelona)
> Tel. +34 936930018 - Mob. +34 600310755 - Fax. +34 938361994
> jmpinyol@endepro.com - http://www.endepro.com
>
>
> Aquest missatge i els documents en el seu cas adjunts, es dirigeixen
> exclusivament al seu destinatari i poden contenir informació reservada i/o
> CONFIDENCIAL, us del qual no està autoritzat ni la divulgació del mateix,
> prohibit per la legislació vigent (Llei 32/2002 SSI-CE). Si ha rebut
> aquest missatge per error, li demanem que ens ho comuniqui immediatament
> per la mateixa via o bé per telèfon (+34936930018) i procedeixi a la seva
> destrucció. Aquest e-mail no podrà considerar-se SPAM.
>
> Este mensaje, y los documentos en su caso anexos, se dirigen
> exclusivamente a su destinatario y pueden contener información reservada
> y/o CONFIDENCIAL cuyo uso no autorizado o divulgación está prohibida por
> la legislación vigente (Ley 32/2002 SSI-CE). Si ha recibido este mensaje
> por error, le rogamos que nos lo comunique inmediatamente por esta misma
> vía o por teléfono (+34936930018) y proceda a su destrucción. Este e-mail
> no podrá considerarse SPAM.
>
> This message and the enclosed documents are directed exclusively to its
> receiver and can contain reserved and/or confidential information, from
> which use isn’t allowed its divulgation, forbidden by the current
> legislation (Law 32/2002 SSI-CE). If you have received this message by
> mistake, we kindly ask you to communicate it to us right away by the same
> way or by phone (+34936930018) and destruct it. This e-mail can’t be
> considered as SPAM.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
"Josep Maria Pinyol Fontseca" <jmpinyol@endepro.com> wrote > > When we select all data in local machine, we obtain results in 2-3 seconds > aprox. In remote connections: > > Postgresql 7.1 usign pgAdminII: > Network traffic generated with remote applications is about 77-80% in a > 10Mb connection. > 6 seconds aprox. > > Postgresql 8.1 usign pgAdminIII: > Network traffic generated with remote applications is about 2-4% in a 10Mb > connection. > 12 seconds or more... > Have you tried to use psql? And how you "select all data" - by "select count(*)" or "select *"? Regards, Qingqing
Yes, with psql, pgAdminIII and our application with ODBC I experiment the same situation... the sentences that I execute are like "select * ..." or similar like this. Qingqing Zhou wrote: >"Josep Maria Pinyol Fontseca" <jmpinyol@endepro.com> wrote > > >>When we select all data in local machine, we obtain results in 2-3 seconds >>aprox. In remote connections: >> >>Postgresql 7.1 usign pgAdminII: >>Network traffic generated with remote applications is about 77-80% in a >>10Mb connection. >>6 seconds aprox. >> >>Postgresql 8.1 usign pgAdminIII: >>Network traffic generated with remote applications is about 2-4% in a 10Mb >>connection. >>12 seconds or more... >> >> >> > >Have you tried to use psql? And how you "select all data" - by "select >count(*)" or "select *"? > >Regards, >Qingqing > > > >---------------------------(end of broadcast)--------------------------- >TIP 2: Don't 'kill -9' the postmaster > > -- Josep Maria Pinyol i Fontseca Responsable àrea de programació ENDEPRO - Enginyeria de programari Passeig Anselm Clavé, 19 Bx. 08263 Callús (Barcelona) Tel. +34 936930018 - Mob. +34 600310755 - Fax. +34 938361994 jmpinyol@endepro.com - http://www.endepro.com Aquest missatge i els documents en el seu cas adjunts, es dirigeixen exclusivament al seu destinatari i poden contenir informació reservada i/o CONFIDENCIAL, us del qual no està autoritzat ni la divulgació del mateix, prohibit per la legislació vigent (Llei 32/2002 SSI-CE). Si ha rebut aquest missatge per error, li demanem que ens ho comuniqui immediatament per la mateixa via o bé per telèfon (+34936930018) i procedeixi a la seva destrucció. Aquest e-mail no podrà considerar-se SPAM. Este mensaje, y los documentos en su caso anexos, se dirigen exclusivamente a su destinatario y pueden contener información reservada y/o CONFIDENCIAL cuyo uso no autorizado o divulgación está prohibida por la legislación vigente (Ley 32/2002 SSI-CE). Si ha recibido este mensaje por error, le rogamos que nos lo comunique inmediatamente por esta misma vía o por teléfono (+34936930018) y proceda a su destrucción. Este e-mail no podrá considerarse SPAM. This message and the enclosed documents are directed exclusively to its receiver and can contain reserved and/or confidential information, from which use isn’t allowed its divulgation, forbidden by the current legislation (Law 32/2002 SSI-CE). If you have received this message by mistake, we kindly ask you to communicate it to us right away by the same way or by phone (+34936930018) and destruct it. This e-mail can’t be considered as SPAM.