Re: libpq or postgresql performance

Hi Aryan

when responding, please include the list as recipient too, so others have a chance to help you.

W dniu 2012-09-08 08:00, Aryan Ariel Rodriguez Chalas pisze:
> I didn't understand this part:
>> so change connection mode when running the application _at remote_ site _locally_ to the server to
> tcp too. And then try with connection to localhost, as well as to the public IP of that server, the
> same which you connect to from your own desktop.
>
> Can you please be more specific, please?
>
> And about this part:
>> try to benchmark your elementary queries with psql run from your site (remotely to the server) and
> from remote site (locally to the server). Use `psql -h hostaddr` at remote site to force tcp connection.

I generally wanted to say: make sure you use the same connection mode in both scenarios, use TCP in
the case you use Unix domain socket now.

Also, what could confuse you, except my bad English, there seem to be some inconsistency in naming
convention about what is called "local" and what is called "remote", particulary, when you also
bring "remote desktop" into the mix.

Let's make that clear, and focus on just the connection between database client and server:

Local connection is the one which runs quickly in your case - the one when client runs on the same
machine the server runs.
Remote connection is when you connect from your site to the server over the internet - the one you
have problems with.

I will use this convention from now on.

> Well, I didn't mention it, but I've tested it before. For example I've done "psql -h hostaddr -d dbname" and it's
is hostaddr here a raw IP or domain name? Is it just equivalent to "servername.d2g.com" which you
used as an example to ssh connection?

> really slow over internet, but if I do "sshuser(at)servername(dot)d2g(dot)com" and then "su - postgres" and then "psql -d dbname",

use -h in local connection too. This forces TCP connection instead of Unix sockect. Try to use both
-h localhost (will resolve to 127.0.0.1) and -h servername.d2g.com.

> it's really fast in the same connection for both test. I insist that this issue is not about network connection, or network latency, or any network problem, because one test is slow and the other one is fast when using the same connection.

Some more ideas:

First of all, as you seem to prefer suspecting your libpq and not believe in network problems: setup
another server at your site and connect to it with the same libpq.

Using two terminal sessions you may trace server logs in one session
(ssh user(at)servername(dot)d2g(dot)com; su - postgres; tail -f /path/to/pg_lof/current_postresql_logfile)
and at the same time, in the other session, make remote connection with psql and run your queries.
You need to set log_statement to 'all' to see the queries in the log. This will not tell you exactly
what is wrong, but you may observe latency.

In the similar way you may also track network packets:
tcpdump port 5432
Run it at the server as root (can you?) and try to catch the moments when packets are received and
sent, delays between them and the moment you hit enter. With, say, -s 500 -X options you may also
see some packet contents (unless you use SSL) to have an idea. Make sure which interface you monitor
- use -i option. Your finger and eye are not a perfect measurement tool, and tcp output is not
perfectly immediate, but with the delays you say you have, and with some patience, you may come up
with some useful observations.

Someone here suggested DNS problems - try to see what happens when you use IP address of the server
instead of domain name (I am not sure what you use now). However, you seem to ssh to the server
without problems, so that's not the case.

Maybe the 5432 port (or whatever port your server runs on) has some problems on the connection
route. Stop the server and use the nc (or netcat) utility on the two machines talking to each other
on this port.

Just use the tools Linux gives you, understand what happens behind the scenes.

Hope this helps
Irek.
> Any other ideas?.
>
> Thanks in advance...
>
>
> ----- Mensaje original -----
> De: Ireneusz Pluta<ipluta(at)wp(dot)pl>
> Para: Aryan Ariel Rodriguez Chalas<wimogan(at)yahoo(dot)com>;"pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
> CC:
> Enviado: Viernes, 7 de septiembre, 2012 1:24 P.M.
> Asunto: Re: [PERFORM] libpq or postgresql performance
>
>
> W dniu 2012-09-07 17:43, Aryan Ariel Rodriguez Chalas pisze:
>> Thank you all for answering that quickly...
>>
>> A simple query could be "SELECT LOCALTIMESTAMP(0)" which could last even 5 seconds for returning data.
>>
>> The other queries that I would like to be faster, return less than 50 rows until now; and two of those queries (the ones the application uses the most), return only a record.
>>
>> I agree that a huge result over internet might take a long time, but until now my application is not returning that amount of rows...
>>
>> Just for the record, the server bandwidth is 2.0 mbps and the client is 1.89 mbps and other applications run really fast for my expectatives (such as opening a remote desktop and also my application through a remote desktop session runs very fast on this connection). But I'm still far of understanding why over a remote desktop session my application runs very fast on the same connection but when running it locally and connecting to the server, it's super slow. Could be because when running over a remote desktop session the application is connecting with "libpq" through "Unix Domain Socket", but when running locally; "libpq" works over "TCP Sockets"?.
> �� `翿 Ѻ ( 0� Ѻ ( �忿 �忿�濿?�(t濿��8翿�(,G!( h!(�濿5�(,G!( h!(�濿� (Eh!( � 9T(翿 h!(& �濿T(,G!( h!(Fh!(Տ (�뿿�Q翿��(翿 h!(& ����(�뿿lQ�뿿 (翿
l#!( 
  �� l����쿿
> so change connection mode when running the application _at remote_ site _locally_ to the server to
> tcp too. And then try with connection to localhost, as well as to the public IP of that server, the
> same which you connect to from your own desktop.
>> By the way I'm using Linux on both ends as Operating System, Lazarus 1.1 as IDE and Zeoslib for connecting to postgresql and I've noticed that when I compile the application for running on Windows as the client, it moves more or less acceptable; so it brings to me to another question: Why "libpq" is faster on Windows than Linux?.
> try to benchmark your elementary queries with psql run from your site (remotely to the server) and
> from remote site (locally to the server). Use `psql -h hostaddr` at remote site to force tcp connection.
>
>> Best regards...
>>
>> Ariel Rodriguez
>>
>> ----- Mensaje original -----
>> De: Ireneusz Pluta<ipluta(at)wp(dot)pl>
>> Para: Aryan Ariel Rodriguez Chalas<wimogan(at)yahoo(dot)com>
>> CC:"pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
>> Enviado: Viernes, 7 de septiembre, 2012 2:52 A.M.
>> Asunto: Re: [PERFORM] libpq or postgresql performance
>>
>> W dniu 2012-09-06 22:04, Aryan Ariel Rodriguez Chalas pisze:
>>> -Why if I connect to the remote server desktop (using RDP or any Remote Desktop Application) and run the application using the same internet connection, it runs really fast when making requests to postgresql; but if I run the application locally by connecting to the remote postgresql server through "libpq", it's really slow?.
>> It might look like the client side fetches too much data or sends too many queries over the connection to the database server and then further processes that data locally. Are you using some kind of ORM in your application?
>>
>> If that is the case, you might need to refactor your application to do as much as possible computation at server side and deal only with computation results over the connection, not the raw data.
>>
>> Try to see in server log what SQL statements are executed while you are running your application. You need to SET log_statement TO 'all' for that.
>>
>> With psql, try to see how much data (how many rows) are returned from that query you call "simple query". Even simple query may return a lot of rows. Server backend might execute it quickly, but returning a huge result over the Internet might take a long time.
>>
>>
>>
>>
>>
>>
>