Re: PostgreSQL over internet

От: ktm@rice.edu
Тема: Re: PostgreSQL over internet
Дата: ,
Msg-id: 20130127024505.GA12423@aart.rice.edu
(см: обсуждение, исходный текст)
Ответ на: PostgreSQL over internet  (belal hamed)
Ответы: Re: PostgreSQL over internet  (Richard Neill)
Список: pgsql-performance

Скрыть дерево обсуждения

PostgreSQL over internet  (belal hamed, )
 Re: PostgreSQL over internet  ("", )
  Re: PostgreSQL over internet  (Richard Neill, )
   Re: PostgreSQL over internet  (belal hamed, )
    Re: PostgreSQL over internet  ("", )
 Re: PostgreSQL over internet  ("David Rowley", )
  Re: PostgreSQL over internet  (belal hamed, )

On Sun, Jan 27, 2013 at 03:15:45AM +0300, belal hamed wrote:
>
> I connect to my server through ADSL connection 4Mbps
>

Here is your "problem". You need to understand the performance
characteristics of your communication channel. ADSL is a VERY
asymmetric communications channel. Down is usually much faster
than up.

> I try this query
>
> select "MatID", "MatName", "MatCode"
> from "Materials"
> where "MatCode" ~* '^1101'
> order by "MatCode"
> limit 2
>
> by wireshark I monitor TCP packets I found total data transmit/received 400B
> I took about 2.5s to fetch results  why ??????
>
> after trying every solution mentioned in previous messages (DNS, tcpip,
> postgresql.conf, ...) not found any improve,
>
> I tried this one:
>
> using Zebedee(http://www.winton.org.uk/zebedee/)
> I build an IP tunnel between me and my data server (I used compression
> level 9)
>
> surprisingly same query now took about 600 ms, "very impressive"
>
> same thing with this query
> select "MatID", "MatName", "MatCode", "MatParent" from "Materials"
> from 48s down to  17s
>
> all these tests done on same connection with same devices so same dns,
> tcp-ip, ....
>
> now I am sure there is something wrong with libpq.

When you wrap the communication channel in an IP tunnel, you are
collapsing much of the syn-ack of the libpq protocol. You can see
the same effect trying to run any sort of X windows application.

Regards,
Ken



В списке pgsql-performance по дате сообщения:

От: Richard Neill
Дата:
Сообщение: Re: PostgreSQL over internet
От: belal hamed
Дата:
Сообщение: Re: PostgreSQL over internet