Connecting to External databases

1. Where is the external database?
2. Have you tested the speed by connecting locally or somewhere else?
3. What is the ping to the other server?

Forge is just a deployment tool. It doesn't have anything to with latency between droplets you set up.

Have you contacted Digital Ocean's technical support? They'll more likely be able to offer a solution than anyone on here.

@topvillas If DO have nothing to do with the server that the external DB is on, I doubt it.

Yeah, well whoever hosts whatever. They'll have more knowledge about their infrastructure than anyone on here.

@topvillas Networking is networking. I wouldn't say "anyone on here" because some DO staff are on here and a lot of people who have worked in or with networking, including me.

I didn't come here for an argument @bashy.

If you can help the bloke out then go ahead. I'm just suggesting that he might get more joy by contacting the appropriate tech support people. Instead of a prolonged forum dialog where everybody just guesses at what the problem might be with the possibility of no resolution what-so-ever.

@topvillas No one is arguing here :P It wouldn't be DO's side unless they had something seriously wrong with the networking of that server. They only do support for hardware related stuff as well.

Well, like I said then. Maybe contacting the appropriate tech support people. Whoever that is.

Thanks for all your comments - just to help clarify the situation, Yes, I've been in touch with technical staff on both sides, with DO and the external host, neither of which at the moment have been able to offer any specific answers - both saying that services are running normally - they are still investigating but I thought I might try here as well in case someone else had encountered the same thing and there was something simple I was not doing.

I have tested the speed from other setups and the response of the MySQL queries is normal and quick. If I run Ping tests I get a quick response from the external server from the DO droplet so that is all ok, it is really only when trying to retrieve data that there is slow response. The external database is with another hosting company called Hostek.

I totally get that Forge is just the deployment tool and seemingly unlikely to be the cause of any of this - but, it's the only common factor at the moment. Setting servers up on DO and Linode - both result in the same very slow data retrieval from external servers, yet accessing data from the same MySQL server from at least 2 other hosts just using standard HTML/PHP ( not Laravel ) and all is good and fast again.

As mentioned before, if the databases are on the actual DO or Linode servers and therefore local to the application, then its all really quick - delays are only happening when connecting to the external server.

Anyways, it seems that I need to continue investigations with the DO tech team.

thanks

@Raider1967 Have you tested Laravel without Forge between two different hosts?

Hi Bashy, no not as yet, but that is going to be my next step today. I really don't think that Forge is the issue here but as mentioned it's the only common factor right now - when I'm running the same tests acorss other servers outside of the Forge setup everything appears to be normal and fast - so am going to set up a DO droplet today directly and test it that way.

@Bashy So after much investigation, I have elimited any issues with the DO servers or my remote SQL. I have realised that even testing in my local Dev environment, the application is performing slow when using a remote database connection.

However, testing this further, I stripped the code and page content right back to basics, eg, no calls to the database, literally outputting a single page with the single word 'Test' on it. If the Database connection is configured to use the local database, it's all quick and immediate page response. Yet, if the database connection is configured to use the remote database, even though there are no calls to this database for any data, the basic page takes 2 seconds to display.

I can understand some small delay when retrieving data from a remote server, yet what makes no sense is that Laravel itself just becomes so slow solely because the database is configured to use a remote server instead of the localhost server.

Any thoughts? What am I missing here?

I would add that, when running my full data load from a remote server, if I add in a "DD" command to trace out the results, this is displayed within around 2 seconds and shows all the records loaded, yet, if I allow the code to continue to the View, it takes 20- 30 seconds, even though the data is loaded in 2 seconds.

@Raider1967 I've not used an external DB (apart from vRack) so I can't say if it's a common thing but does sound like it could be an issue within Laravel driver (either as a core or how you've implemented it).

@Raider1967, please keep us updated on what you find. I'm having the exact same issue. I used to do my own hosting for my Laravel server so it was on the same network as the database that it needed to connect to. But recently I moved the Laravel server to Linode/Forge and it is having the same slow connection that you have referred to.

@niehman06 so after days of testing this I have finally discovered what is causing this issue for me.

I have an 'AppServiceProvider' class file that had the following code;

View::composer('*', function($view) { if( Auth::user() ) { $view-> **database call to load data for every view ** } });

Effectively this is making a database call for every view so that the data is available across all pages - in my case it is permission based data.

When this is run from a local database on the same server as the application it causes no problems and is super fast, yet when using a remote database, it has a huge impact on data loading speeds.

Oddly, the data itself is not that big and only takes less than 1 second to load when tested separately, and other data loaded for a particular page only took under 2 seconds to load, yet when combined in the application, it creates a page loading speed of around 30 seconds or so when its from the remote database. Not sure why it takes so much time when the overall data loading times would only be around 2 - 3 seconds. I'm guessing this is maybe causing some type of internal loop whilst processing or maybe multiple calls to the database.

When I remove the database call within the 'AppServiceProvider' and just use the remote database call for the data needed only on the specific page from within its own ServiceController, then it loads quickly with only what I would expect as a slight delay for a remote database connection.

Hope this helps.

Interesting. In my case, I'm definitely seeing a great slow down on my more complex pages that make several query calls to the database so that is inline with what you found. I'm able to re-think my code in some cases in order to make fewer queries but in some cases it can't be helped.

I wonder what is causing the delay though when multiple queries are performed. Why would a 1 second query followed by a 2 second query, when run separately, take 30 seconds when ran at the "same" time. And this only happens when querying a remote database and does not happen if ran on the same local network.

Is the fault on the database end or something on Laravel's end or maybe even a php driver issue? The remote database I'm connecting to is an MSSQL server and I'm not familiar enough with the settings on that to know if I just need to enable something so that it runs remote connections the same way it runs local connections.

@niehman06 It is rather odd to be honest. If i connect to the remote database from other non Laravel applications that are also set up to use the database in a remote way - then all works fine so I am sure it's not the database or the MySQL server.

As a test within my Laravel app, I was able to confirm that the second database call was loading the data quick enough under 2 seconds, but then seemingly hanging for around 20 seconds after that to render the page.

Oddly, I stripped the page back to literally nothing other than the word 'TEST' on the page, no CSS, no JS, no database call on the page itself - it was quicker but still around 2-3 seconds to show the page - so then I realised that the additional call to the database was being made via AppServiceProvider class.

It appears that the more data that is being loaded, then it increases the delay exponentially. I know that the AppServiceProvider data for the view takes around 2 seconds to load, on a blank HTML page, overall time is around 3 seconds, yet if I add in another database call for the page that is around 1 second, the overall time becomes 20 - 30 seconds. This is solely on remote connections - everything on a local database works super quick.

I did read another post somewhere that suggested that the AUTH element may be causing multiple connections to the database but have not been able to investigate that.

I've tested this on Digital Ocean and Linode servers and also my own local dev machine and it's always the same issues. Local database = fast, remote database = lots of delays depending how much additional data is loaded.

My solution at the moment is not to use the AppServiceProvider class to load any data - need to find another way to pre-load other data to store in memory for use across multiple pages.