I'm having a problem with a medical records program that uses a Postgre SQL as a backend.

Several offices are connecting to a corporate site via Cisco VPNs. There is an ASA5055 at corporate and most sites use a PIX. I've determined that speed isn't an issue as I can send/receive files over a netshare through the VPN at around 500KB/s symmetrically.

I run a report in the software on a test account that shows a simple 1 page report, if it were a PDF file it would run be about 50KB total. If the report is ran on the LAN, it appears immediately. When ran remotely, even at a time when all other offices are closed and nothing else is going through the tunnel, it takes 40-50 seconds. Using that information, I gather that the report is no more than about 200KB in size. Pings are under 100ms.

During this time, I can watch the network interface through Task Manager and it appears that the report is being "streamed" to the client at 5-10KB/s.

The server is 2008R2 Enterprise, 2x 4 core Xeon, 56GB ram. System appears to be functioning as expected with no I/O errors.

What could be some causes or solutions to this mystery? Is there anything specifically that I should look at to further troubleshoot this?


You're probably running into latency issues. I've seen database performance over crappy links get downright evil. My next step would be to take a packet-trace of both local and remote connections (can be done on the db server, probably), and take a look at the relative time between packets. While slamming a single file in one stream may be fast in both cases, if the transaction requires some back-and-forth between the client and the server latency can kill perceived throughput.

| improve this answer | |
  • 1
    Yep. Specifically it's about round-trips, not just bandwidth. You're probably running lots of small queries where you query, wait for result, repeat. You probably need to get rid of the loops and do more work in fewer queries, or if you have really complex logic consider packaging it into a stored procedure. – Craig Ringer Aug 11 '12 at 6:44

As Erik commented, the issue could be latency.

You can try this experiment. In psql on the server, turn on \timing and run the related SQL query. Then run it again on the server, except time the full time to run psql:

time psql -c "SELECT ..."

My understanding is that the former will test the query time inside the server, while the latter will also include the connection overhead, anad time communicating back and forth with the client. Now repeat the test, but run psql on the other end of the VPN.

Did the \timing results vary much? How about the psql results? These answers should help confirm if the issue in PostgreSQL, and that it's due to WAN latency.

Besides other ways you might tune your network connection, you can consider a PostgreSQL replication solution, or consider caching alternatives on the client side if the severity of the situation merits further performance improvements.

| improve this answer | |

Latency is likely to be the cause of the problem only if the report is obtained with lots of small SQL queries. Each query implies at least one round-trip to the server and they can't run in parallel, at least not on the same connection.

On the other hand, if it's done with a few big SQL queries, latency is irrelevant, since there are few round-trips to the server. In this case, the speed wouldn't differ much as when downloading a file, bandwidth being the driving factor.

To check how many queries are run when the report is running, you could temporarily set log_statement="all" in PostgreSQL during the report. See the doc about log_statement.

Or watch it in real-time by querying repeatedly the pg_stat_activity system view.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.