10

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

PokerTracker logs all your hands and statistics whilst playing online poker.

I wanted this accessible from several different computers so decided to installed it on my VPS and after a few hiccups I managed to get it connecting without errors.

However, the performance is dreadful. I have done tons of research on 'remote postgresql slow' etc and am yet to find an answer so am hoping someone is able to help.

Things to note:

The query I am trying to execute is very small. Whilst connecting locally on the VPS, the query runs instantly.

While running it remotely, it takes about 1 minute and 30 seconds to run the query.

The VPS is running 100MBPS and then computer I'm connecting to it from is on an 8MB line.

The network communication between the two is almost instant, I am able to remotely connect fine with no lag whatsoever and am hosting several websites running MSSQL and all the queries run instantly, whether connected remotely or locally so it seems specific to PostgreSQL.

I'm running their newest version of the software and the newest compatible version of PostgreSQL with their software.

The database is a new database, containing hardly any data and I've ran vacuum/analyze etc all to no avail, I see no improvements.

I don't understand how MSSQL can query almost instantly yet PostgreSQL struggles so much.

I am able to telnet to the port 5432 on the VPS IP with no problems, and as I say the query does execute it just takes an extremely long time.

What I do notice is on the router when the query is running that hardly any bandwidth is being used - but then again I wouldn't expect it to for a simple query but am not sure if this is the issue. I've tried connecting remotely on 3 different networks now (including different routers) but the problem remains.

Connecting remotely via another machine via the LAN is instant.

I have also edited the postgre conf file to allow for more memory/buffers etc but I don't think this is the problem - what I am asking it to do is very simple - it shouldn't be intensive at all.

Thanks, Ricky

Edit: Please note the client and server are both running Windows.

Here is information from the config 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'm aware I've given some mammoth amount of buffers/memory to this config, just to test if it was the issue - showing uncommented lines only:

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 required, please let me know. Thanks for all your help.

Ricky
  • 7,785
  • 2
  • 34
  • 46
  • Please post the query, sizes of the tables involved and the size of the output. – Quassnoi Jan 11 '11 at 12:31
  • 1
    It sounds like a networking problem rather than a postgres problem. What happens if you do the query from the client machine using `psql -h server ...`? What happens if you do it over an ssh tunnel instead? – Paul Tomblin Jan 11 '11 at 12:37
  • I knew I'd miss out important information. I forgot to say both operating systems are running Windows. I will post the query if it is necessary, but it is a simple query, just looking at one table seems to cause the issue. I can't see how it can be due to the query when locally it is instant and yet there is a massive delay with running it remotely. – Ricky Jan 11 '11 at 13:44

6 Answers6

8

I enabled logging and sent the logs to the developers of their software. Their answer was that there software was originally intended to run on a local or near local database so running on a VPS would be expectedly slow - due to network latency.

Thanks for all your help, but it looks like I'm out of ideas and it's due to the software, rather than PostgreSQL on the VPS specifically.

Thanks, Ricky

Ricky
  • 7,785
  • 2
  • 34
  • 46
2

You can do an explain analyze which will tell you the execution time of the query on the server (without the network overhead of sending the result to the client).

If the server execution time is very quick (compared to the time you are seeing) than this is a network problem. If the reported time is very similar to what you observe on your side, it's a PostgreSQL problem (and then you need to post the execution plan and possibly your PostgreSQL configuration)

  • Hi, I tried running the select version() query remotely and locally and both times it was pretty much instant. Locally, the program 'PokerTracker' imports data instantly. Remotely, it takes much, much longer. I'm going to post my configs. – Ricky Jan 11 '11 at 18:57
  • `select version()` doesn't transfer any realistic amount of data between the server and the client. You should really run explain analyze on one of the slow queries to find the real culprit –  Jan 11 '11 at 19:02
  • The problem I have is it's a third party program running the query, so I'm not sure what the query actually is. Is there some other generic query I could run against it, something that requires more data transferred between the two? – Ricky Jan 11 '11 at 19:06
  • Pick some large table (> 1000 rows) and do a `SELECT * FROM the_table` –  Jan 11 '11 at 19:07
  • I ran explain analyze and selected everything from the pg_attribute table (as that had over 1000 rows) and the query took about 1 second, with the following results: QUERY PLAN -------------------------------------------------------------------------------- ------------------------------- Seq Scan on pg_attribute (cost=0.00..47.74 rows=1774 width=103) (actual time=0 .017..2.784 rows=1774 loops=1) Total runtime: 5.235 ms (2 rows) – Ricky Jan 11 '11 at 19:13
  • So that query runs in 1 second and takes 5ms on the server. Doesn't really sound like a network problem. Which SQL tool did you use to run that? You could turn on logging of slow statements (using the config parameter `log_min_duration_statement`) on the server to find out which statements that your application generates actually take that long. –  Jan 11 '11 at 19:17
  • I'm using psql - once logged in I run \i [sqlfile.sql] which contains the query, and the above outputs to the terminal. I ran a further test, selecting * from a different table with nearly 25,000 rows and that took about 10 seconds. Thanks for your help so far. Should I be using a different tool? As I mentioned, I'm not familiar with PostgreSQL so I'm finding all this out as I go along! – Ricky Jan 11 '11 at 19:22
  • No, psql is fine. My next step would be to turn on the slow statement logging on the server. That way you can pinpoint the application's query to find out what's wrong –  Jan 11 '11 at 19:57
  • I have enabled logging and will run some queries using the program later today. Thanks for your help - and I'll update hopefully tomorrow with some results. – Ricky Jan 11 '11 at 20:13
1

Have been plagued by this issue for awhile and this question lead me to the answer so thought I would share incase it helps.

The server had a secondary network interface (eth1) that was setup as the default route. The client performing the queries was within the same subnet as eth0, so this should not cause any issues.. but it was.

Disabling the default route made the queries return back within normal time frames. But the long term fix was to change the listen_addresses from '*' to the correct IP.

oden
  • 3,461
  • 1
  • 31
  • 33
0

Use network monitoring tools (I reccomend wireshark, because it can trace many protocols, including postgresql's) to see if network connection is ok. You will see dropped/retransmitted packets if the connection is bad.

cezio
  • 654
  • 3
  • 11
0

Maybe Postgres is trying to authenticate you using ident, which isn't working (for example firewalled out), and has to wait for timeout before allowing connection by other means.

Try to query remote server for select version() using psql - this should be instant, as it does not touch disk.

If it isn't instant please post your pg_hba.conf (uncommented lines).

Another possible causes:

  • authentication using RevDNS;
  • antivirus on server or client;
  • some other connection is blocking a table or row, because it didn't end clearly.
Tometzky
  • 22,573
  • 5
  • 59
  • 73
  • Hi. I tried select version() remotely and it was pretty much instant (maybe a few milliseconds) and displayed the version. On my VPS, there is no anti virus software. – Ricky Jan 11 '11 at 18:55
  • I wonder if this is the issue. The OS the VPS is installed on may be causing the issue. If something is timing out, I may not have control over it as I only have access to the VPS. Would this be logged anywhere, if it is timing out? Does PostgreSQL create error logs etc? – Ricky Jan 11 '11 at 19:08
0

This is not the answer to why pg access is slow over the VPN, but a possible solution/alternative could be setting up TeamPostgreSQL to access PG through a browser. It is an AJAX webapp that includes some very convenient features for navigating your data as well as managing the database.

This would also avoid dropped connections which in my experience is common when working with pg over a VPN.

There is also phpPgAdmin for web access but I mention TeamPostgreSQL because it can be very helpful for navigating and getting an overview over the data in the database.

johny
  • 41
  • 1