Working locally with remote database - Unusually slow page load

[sambadave]

Has anyone noticed abnormally slow page load times when working locally but connected to a remote database?

I work within a team and we all have local versions of our PW sites but are connected to the same remote database. This allows us all to work from the one ProcessWire admin area, which we prefer.

We have noticed our sites loading really slowly in this scenario and it is really limiting productivity.

Average load time comparisons across our websites

• Working locally, connected to remote development db
  ~30s to 1 minute (1minute for more complex pages)
• Hosted development site - connected to same development db
  ~5s - 10s
• Live site, optimised with ProCache etc and live db
  ~3s - 5s

Our template setup

Overall, the majority of our page templates aren't doing anything particulary fancy. Most templates allow sub pages to be rendered on the parent page as blocks using the $page->render approach. In some cases there may be a number of blocks to display. We recognise that rendering a lot of blocks on a page could slow things down a little but not to the degree we are encountering.

Steps taken to identify issues in our code

As a test we removed all the rendered blocks from the parent page template logic and went as far as just showing a single static <h1> heading. We still saw times of ~15s with an almost empty page.

It feels like this has something to do with the database connection, or maybe something within our MAMP setup on our Macs. It's strange as our hosted development and live sites are so much faster, so this feels like a problem with our local setup.

If anyone has any suggestions as to where we are going wrong, we'd be very grateful. It can be quite frustrating when we need to refresh the page regularly to see our changes but we know that we will face up to a minute wait just to see if something has worked. The live sites are really fast so this is definitely something with our setup I would think.

Thanks in advance for any words of wisdom ?

[rick]

The only thing I can see at this time is the word "remote". Any time you venture outside the loopback (127.0.0.1), all sorts of external factors come into play; dns lookup, hop latency, etc. I'm not suggesting that is where the problem lies, just that more areas are opened up to check this issue. Do you (or any of your team) experience this delay via SSH, SCP, SFTP, etc.? Is there noticeable improvement at different times of the day? Any firewall in place? Can you tail your network to see if it jives with Tracy's database request listing? I'm just thinkin' out loud.

[wbmnfktr]

Two thoughts here:

1. Just in case... ask your hosting company if there are any security measures in place that may slow down external connections on purpose. I know at least two companies that route external access to databases through such a small bottle neck that it becomes almost unusable.
2. Add the remote database server's IP to your local hosts file to remove at least DNS lookups and maybe even some routing issues. That helped me a while back when using DreamHost. Still slow-ish but at least usable.

 

[sambadave]

@wbmnfktr We've tried adding the remote database server's IP to our local hosts file. Unfortunately we're not seeing any noticeable improvements. We're asking our IT department  about the security measures they have in place and waiting for a rresponse.

@rick We've not noticed a delay via SFTP. I'm not too sure about your other questions yet but I've asked other people on the team and have also passed this thread link on to our IT team.

I can confirm that one member of our team wasn't using MAMP and still experienced the same issues, so that may rule out anything to do with MAMP setup.

I'll let you know if I find out any more information. Thank you both very much for your help so far.