1

I am running a Drupal site that is backed by Postgres. This is my first time running something with Postgres; I come from the MySQL world.

For doing dev work on the site, I connect to a VPN and tunnel my traffic through a machine that has an IP that is allowed in pg_hba.conf.

The queries are coming back very slow. My logs tell me that loading the homepage on the dev server:

Executed 86 queries in 84.26 ms

And on my local machine (with the remote connection):

Executed 87 queries in 22368.05 ms

Queries are 80% SELECT, with a few INSERT INTOs.

I have done some rudimentary tuning also on the db server, hoping to speed things up for the remote connection (the tuning did not, but it certainly made the dev server's queries come back faster!).

On the server's db config files, I have turned of ssl and only allowed connections from localhost and ips within the VPN. I am authenticating db users via md5.

Could the hold up be going through this VPN? Or could it have to do with the fact that the db is being accessed from two different places with the same username/password?

Improve this question
5
  • 5
    Round-trip times can make a huge difference. Have you pinged the server to see what the round-trip time is? That will probably answer your question instantly. (ie: 87 queries, 22 seconds, 250ms each, the network round trip (query sent, query has to come back) will be 125ms each way - problem accounted for). Ping the server.
    – Philᵀᴹ
    Feb 7, 2013 at 21:51
  • 2
    Run explain analyze on the select statements, that will report the time it took on the server. My guess is, that transferring the data is your bottleneck.
    – user1822
    Feb 7, 2013 at 22:38
  • @a_horse_with_no_name Well done, Holmes ;P
    – Philᵀᴹ
    Feb 7, 2013 at 22:39
  • @a_horse_with_no_name Awesome suggestion! Thank you!
    – CamelBlues
    Feb 7, 2013 at 23:31
  • @Phil Good point.... looks like I'm going to need to build a faster tunnel.
    – CamelBlues
    Feb 7, 2013 at 23:32

1 Answer 1

Reset to default
4

If the only difference is local vs remote, then the problem has to be your round trip times. The actual internal query execution is not aware of the type of connection used. However I also wonder if you are also running your dev system on a different database. In that case, caching could be very different and could also make a significant difference.

I see a few ways to solve this problem. The first is just to have a dev copy of the database for the site on your dev system. This data could then be sanitized so that if your dev system is compromised or lost (supposing it is a laptop for example) the data exposure is minimal. Then you can test performance on a system which can do caching specific to your dev work and will not have the round trip.

The second approach is to build a much faster tunnel, and to possibly throw a lot more RAM at the db server.

Improve this answer
1
  • Yes, I have done a similar approach to what you have suggested. I upped the RAM on the DB server, but that didn't really make a dent. So then I wrote a script that pulled down a copy of the database from the remote server and put it on my local dev machine and tied the script to run when I connected to the VPN.
    – CamelBlues
    Feb 15, 2013 at 15:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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