PostgreSQL over internet

[belal hamed]

Hi All,

I think this is not new topic, I try to look for discussions about same
subject I find something like

http://postgresql.1045698.n5.nabble.com/libpq-or-postgresql-performance-td5723158.html
http://www.postgresql.org/message-id/***@wp.pl

I will talk to you about my experiment with same issue


I have table Materials created as:

postgre version: 9.1

create domain ID as integer;
^
create domain BIG_ID as bigint;
^
create domain SMALL_ID as smallint;
^
create domain GUID as varchar(32);
^
create domain GUIDSTRING as varchar(38);
^
create domain CURRENCY as numeric(18,4) default 0;
^
create domain AMOUNT as numeric(18,4) default 0;
^
create domain NAME as varchar(250);
^
create domain CODE as varchar(60);
^
create domain FOREIGNCODE as varchar(60);
^
create domain doc as varchar(40);
^
create domain NOTE as varchar;
^
create domain MEMO as varchar;
^
create domain BARCODE as varchar(40);
^
create domain IMAGE as bytea;
^
create domain OBJECT as varchar;
^
create domain CUR_PART_NAME as varchar(40);
^
create domain STRNAME as varchar(60);
^
create domain STRCODE as varchar(30);
^
create domain STRVALUE as varchar(250);

^
create domain SERIAL as varchar(60);

^
create domain D_DATE as DATE;

^
create domain D_TIME as TIME;

^
create domain D_DATETIME as timestamp;

^
create domain D_INTEGER as INTEGER;

^
create domain SHORT_BARCODE as varchar(20);

^
create domain XML as varchar;
^
create domain D_FLOAT as double precision default 0;

^
create domain D_DOUBLE as double precision default 0;

^
create domain KIND as smallint;

^
create domain D_LEVEL as smallint;

^
create domain D_SIGN as smallint;

^
create domain D_EXCHANGE as double precision;

^
create domain Rarefy as varchar(5);
^
create domain LONGNAME as varchar(250);
^
create table "Materials"
(
"MatID" serial,
"MatSite" SMALL_ID,
"MatChanged" BIG_ID,
"MatParent" D_INTEGER default 0 not null ,
"MatIsBook" BOOLEAN default 0 not null ,
"MatConsist" D_INTEGER,
"MatClass" D_INTEGER,
"MatName" Name default not null ,
"MatCode" Code default not null ,
"MatForeignCode" ForeignCode,
"MatBarcode" BARCODE,
"MatMaxLimit" AMOUNT,
"MatMinLimit" AMOUNT,
"MatAge" AMOUNT,
"MatPack" D_INTEGER,
"MatLevel" D_LEVEL default 0 not null ,
"MatUnity" D_INTEGER,
"MatDefUnity" D_INTEGER,
"MatPackUnity" D_INTEGER,
"MatPackSize" Amount,
"MatDefWeight" AMOUNT,
"MatApproxWeight" AMOUNT,
"MatDiscount" AMOUNT,
"MatNoDiscount" BOOLEAN,
"MatQntRebate" AMOUNT,
"MatIsQntRebate" BOOLEAN default 0 not null ,
"MatDefGroup" D_INTEGER,
"MatSpecification" Note,
"MatBonus" AMOUNT,
"MatBonusBase" AMOUNT,
"MatRarefy" Rarefy,
"MatDefQnt" AMOUNT,
"MatIsUnbounded" BOOLEAN,
"MatIsActive" BOOLEAN default 0 not null ,
"MatIsSerial" BOOLEAN default 0 not null ,
"MatIsPacked" BOOLEAN default 0 not null ,
"MatIsBatched" BOOLEAN default 0 not null ,
"MatIsWeb" BOOLEAN,
"MatIsAssist" BOOLEAN default 0 not null ,
"MatIsIgnored" BOOLEAN default 0 not null ,
"MatAccVendor" D_INTEGER,
"MatIsConsignment" BOOLEAN default 0 not null ,
"MatIsVariety" BOOLEAN default 0 not null ,
"MatIsMeal" BOOLEAN default 0 not null ,
"MatIsMaintain" BOOLEAN default 0 not null ,
"MatIsCategory" BOOLEAN default 0 not null ,
"MatAccCustomer" D_INTEGER,
"MatDepartment" ID,
"MatChain" D_INTEGER default 0 not null ,
"MatPhotoType" STRCODE default 0 not null ,
"MatPhoto" LONGNAME,
"MatCommission" AMOUNT,
"MatFactor" AMOUNT,
"MatTax" AMOUNT,
"MatFees" AMOUNT,
"MatExpiredThrough" D_INTEGER,
"MatExpiredBy" D_INTEGER,
"MatActivateDate" D_DATE default 'NOW' not null ,
"MatCreatedDate" D_DATE default 'NOW' not null ,
"MatModel" D_INTEGER,
"MatNote" Note,
"MatPoint" D_INTEGER,
"MatOriginal" D_INTEGER,
"MatRevision" BIG_ID default 0 not null
)
^
alter table "Materials" add constraint "pkMaterials" primary key ("MatID")
^
create index "IdxMatIsBook" on "Materials" ("MatIsBook" )
^
create index "IdxMatName" on "Materials" ("MatName" )
^
create unique index "IdxMatCode" on "Materials" ("MatCode" )
^
create unique index "IdxMatBarcode" on "Materials" ("MatBarcode" )
^
create index "IdxMatIsWeb" on "Materials" ("MatIsWeb" )
^
create index "IdxMatIsAssist" on "Materials" ("MatIsAssist" )
^
create index "IdxMatIsConsignment" on "Materials" ("MatIsConsignment" )
^

with 31000 record

I connect to my server through ADSL connection 4Mbps

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.

[k***@rice.edu]

Post by belal hamed
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.

Post by belal hamed
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,
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

[Richard Neill]

Post by k***@rice.edu

Post by belal hamed
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'm not convinced that ADSL is your problem.

1. Try just SSH directly to the server, and run psql, and run a query
like this one:
SELECT 'This is a test message' AS status;

This should run in under 1ms; it also means that we don't have to worry
about the details of your database-schema for the purposes of this problem.

2. Try creating a simple SSH tunnel and using your application locally.
For example, if your server runs Postgresql on port 5432, run this SSH
command:
ssh -L 5432:localhost:5432 your_server_hostname
and then connect to your LOCAL (localhost) port 5432; SSH will handle
the port forwarding. [Explanation: "localhost" in the SSH command is in
the context of your_server_hostname]
How does it work now?

3. Try configuration you are currently using, but with the above query.

It should be possible to distinguish between:
- slowness caused by the database query itself
- slowness caused by the network fundamentally.
- slowness caused by the postgresql/libpq.

Hopefully, you'll be able to narrow it down a bit.

HTH,

Richard

[belal hamed]

Post by k***@rice.edu
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.

How it could be ADSL problem when it's the same in tow tests ?
beside data transferred when using tunnel is much bigger (about 10KB) than
direct connection (400B)
so it should be slower when using tunnel but the result shows it was faster
!!!!

Post by k***@rice.edu
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.

If that so, why these is not same option in Postgresql, Is it necessary to
use IP tunnel to do that and perform fast fetch?

Post by k***@rice.edu
Try creating a simple SSH tunnel

my server is windows 7

Post by k***@rice.edu
- slowness caused by the database query itself
- slowness caused by the network fundamentally.
- slowness caused by the postgresql/libpq.

I run the same query on same network connection so I eliminate the
slowness caused by the database query and network fundamentally,
nothing left but postgresql/libpq

not anyone consider there may be a bug when connection to a remote server
over internet in libpq
the only different when I used the tunnel is I connect to localhost
instead of server IP or domain name (I try both)

[k***@rice.edu]

Post by belal hamed

Post by k***@rice.edu
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.

How it could be ADSL problem when it's the same in tow tests ?
beside data transferred when using tunnel is much bigger (about 10KB) than
direct connection (400B)
so it should be slower when using tunnel but the result shows it was faster
!!!!

Due to the asymmetric communication, a bigger data output in a single
packet (the result of using compression on the tunnel) will get sent
without waiting. A smaller packet will delay a bit waiting for some
additional data, which in your case does not come. You may want to
check out this document describing some of what I believe is causing
your observed behavior:

http://www.faqs.org/docs/Linux-HOWTO/ADSL-Bandwidth-Management-HOWTO.html#BACKGROUND

Post by belal hamed

Post by k***@rice.edu
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.

If that so, why these is not same option in Postgresql, Is it necessary to
use IP tunnel to do that and perform fast fetch?

Post by k***@rice.edu
Try creating a simple SSH tunnel

my server is windows 7

Post by k***@rice.edu
- slowness caused by the database query itself
- slowness caused by the network fundamentally.
- slowness caused by the postgresql/libpq.

I run the same query on same network connection so I eliminate the
slowness caused by the database query and network fundamentally,
nothing left but postgresql/libpq
not anyone consider there may be a bug when connection to a remote server
over internet in libpq
the only different when I used the tunnel is I connect to localhost
instead of server IP or domain name (I try both)

You would find that if you log in to your DB server and use libpq
to it over a localhost connection that the performance is good which
points to your network as the problem.

Regards,
Ken

[David Rowley]

Sent: 27 January 2013 13:16
Subject: [PERFORM] PostgreSQL over internet
by wireshark I monitor TCP packets I found total data transmit/received 400B
I took about 2.5s to fetch results  why ??????

Are you sure there's not any QOS somewhere that is slowing down the packets
for port 5432 or whichever you're using for PostgreSQL?
Perhaps temporarily changing PostgreSQL's listening port to something else
might be a good test.

David

[zeljko]

Post by belal hamed
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"

adsl = asymmetric communication, bigger amount of data will be sent in a single
packet, while small have some kinda of waiting.
Using ssh tunnel you can have 2 benefits: no waiting on small amount of data and
ssh tunnel usually compress data (but it can be disabled by default) ,and that
is pretty nice when you dload eg 30000 records where each row is eg 1k - with
tunnel you'll get data cca 10x faster.

Post by belal hamed
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.

No, it's not wrong, it's just missing protocol compression like mysql have.

zeljko