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?
Best Answer
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.