Remote Postgresql - Extremely Slow

Question

I have a PostgreSQL setup for VPS that I own - the software that accesses the database is a program called PokerTracker.

PokerTracker records all your hands and statistics during online poker.

I wanted it to be accessible from several different computers, so I decided to install it on my VPS, and after several hiccups I managed to connect it without errors.

However, performance is terrible. I have done a lot of research on "remote post-congress", etc., and I have not found an answer yet, so I hope someone can help.

Notes:

The queries I'm trying to fulfill are very small. When connected locally to the VPS, the request is executed instantly.

When you run it remotely, it takes about 1 minute 30 seconds to complete the request.

VPS runs on 100MBPS, and then the computer I'm connecting to is on the 8MB line.

The network connection between them is almost instantaneous, I can connect remotely normally, without any restrictions, and I host several websites that run MSSQL, and all queries are launched instantly, regardless of whether they are connected remotely or locally, so they seem specific to PostgreSQL.

I use my latest software version and the latest compatible version of PostgreSQL with their software.

The database is a new database containing hardly any data, and I ran a vacuum / analysis, etc. all to no avail, I see no improvement.

I don’t understand how MSSQL can query almost instantly, but PostgreSQL is fighting so hard.

I can use telnet to connect to port 5432 on the VPS IP without problems, and, as I said, the request is completed, it takes a very long time.

What I notice is on the router at the start of the request, that hardly any bandwidth is used - but then I would not expect this to be a simple request, but I'm not sure if this is a problem. I tried to connect remotely on 3 different networks (including different routers), but the problem remains.

Connecting remotely through another machine via LAN is instantaneous.

I also edited the postgre conf file to allow for more memory / buffers, etc., but I don't think this is a problem. I ask him to do it very simply - it should not be intense at all.

Thanks Ricky

Edit: note that the client and server are running Windows.

Here is the information from the configuration files.

  pg_hba - currently allowing all traffic:

 # TYPE DATABASE USER CIDR-ADDRESS METHOD

 # IPv4 local connections:
 host all all 0.0.0.0/0 md5
 # IPv6 local connections:
 # host all all :: 1/128 md5

And postgresqlconf - I know that I gave some buffers / memory for this configuration, just to check if this problem was - show only without commenting:

  listen_addresses = '*'
 port = 5432
 max_connections = 100
 shared_buffers = 512MB
 work_mem = 64MB
 max_fsm_pages = 204800
 shared_preload_libraries = '$ libdir / plugins / plugin_debugger.dll'
 log_destination = 'stderr'
 logging_collector = on
 log_line_prefix = '% t'
 datestyle = 'iso, mdy'
 lc_messages = 'English_United States.1252'
 lc_monetary = 'English_United States.1252'
 lc_numeric = 'English_United States.1252'
 lc_time = 'English_United States.1252'
 default_text_search_config = 'pg_catalog.english'

Any other information is required, please let me know. Thank you for your help.

Answer 1

I turned on logging and sent logs to my software developers. Their answer was that the software was originally intended to work in a local or near local database, so working on a VPS would be slow - due to network latency.

Thanks for your help, but it seems like I have no ideas, and this is related to the software, not PostgreSQL on VPS.

Thanks Ricky

Answer 2

You can do explain analyze , which will tell you the time it took to complete the request on the server (without the network overhead of sending the result to the client).

If the server run time is very fast (compared to the time you see), this is a network problem. If the indicated time is very similar to what you observe on your side, this is a PostgreSQL problem (and then you need to publish the execution plan and possibly your PostgreSQL configuration).

Answer 3

Use network monitoring tools (I recommend wirehark because it can trace many protocols, including postgresql) to see if it is connected to the network normally. If the connection is poor, you will see dropped / retransmitted packets.

Answer 4

Perhaps Postgres is trying to authenticate you using ident , which is not working (for example, disconnected from the network) and must wait for a timeout before allowing the connection in other ways.

Try querying the remote server for select version() using psql - this should be instantaneous since it does not touch the disk.

If this is not instantaneous, send pg_hba.conf (uncommented lines) message.

Other possible causes:

• authentication using RevDNS;
• antivirus on the server or client;
• some other join blocks the table or row because it does not end clearly.

Answer 5

This is not an answer to the question why access to PPG is slow over VPN, but a possible solution / alternative could be to configure TeamPostgreSQL to access PG through a browser. This is an AJAX web application that includes some very convenient functions for navigating your data as well as managing a database.

This will also avoid the remote connections, which in my experience are common when working with pg via VPN.

There is also phpPgAdmin for network access, but I mention TeamPostgreSQL because it can be very useful for navigating and getting an overview of the data in the database.

Answer 6

They suffered from this problem for some time, and this question led me to an answer, so I thought that I would share what it helps.

The server had an additional network interface (eth1), which was configured as the default route. The client executing the requests was on the same subnet as eth0, so this should not cause any problems. But that was.

Disabling the default route made requests return to normal time frames. But the long solution was to change listen_addresses from '*' to the correct IP.