Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
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 '13 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. –  a_horse_with_no_name Feb 7 '13 at 22:38
    
@a_horse_with_no_name Well done, Holmes ;P –  Phil Feb 7 '13 at 22:39
    
@a_horse_with_no_name Awesome suggestion! Thank you! –  CamelBlues Feb 7 '13 at 23:31
    
@Phil Good point.... looks like I'm going to need to build a faster tunnel. –  CamelBlues Feb 7 '13 at 23:32

1 Answer 1

up vote 3 down vote accepted

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.

share|improve this answer
    
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 '13 at 15:58

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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