Hi, Ive recently set up a versioned SS install on my local development machine. Im running XAMPP 1.7.3

Because multiple devs will be working on the project, I have set up a remote database on our MediaTemple dv server, allowing remote access as per this article - http://kb.mediatemple.net/questions/264/Setting+up+external+MySQL+connections

We currently dont have the domain name registered, so in SS _config.php I am accessing the host using xxx.xxx.xxx.xxx:3306

I have also used apaches virtual hosts to allow for access to this project using a "http://projectName.local" alias on my development machine.

This is all installed, SS is running, and I can view pages, login to admin etc... so, everything is sweet as for a smooth, multi-developer, versioned project... EXCEPT

For some reason, loading a page, or logging in to admin, or even loading a page to edit in the admin interface, takes FOREVER. At least 30 seconds per page refresh. Why on earth is this happening? Localhost SS sites that access a database on the same localhost server run fine, and if I open the mysql command line interface and do SELECT's on the external database its super responsive, but for some reason SS takes ridiculous amounts of time to connect to the database.

I have tried setting skip-name-resolve in the my.ini, didnt help. I hope I have provided enough information in this post for someone to recognise a potential source of the sluggishness.

Anyone have any ideas? Its really important I can get this up and running, I really want to stick to SS but if this wont work it my not be an option...

Remote connections to SQL from a local host (if you're on a normal internet connection, rather than a dedicated highspeed connection to the database server) will always be slow.

You local host is having to connect to the database server, ask for data, manipulate it, query some more, etc etc and this isnt a fast process given the (what we can assume is) large latency between your local host and the database server.

It's best to use your own dev databases, that's what we do.

Using localhost databases isnt an option as we have multiple developers and some will be working remotely at times.

I do understand that quite a lot of SQL queries get executed by SS in the process of rendering a page, however, the fact I can localhost connect to the remote DB using the command line and execute super fast SELECT/UPDATEs seems to indicate there is no real latency problem between my localhost and the remote server, but instead things are going wrong somewhere at the PHP/SS end.

What Im wondering is WHY there is such a huge latency between SS and the remote DB, and if there are any tips anyone has to improve remote DB performance.

"tips to improve database performance" is a whole massive topic!

Also DB performance & network performance are very hard to give remote advice on - apart from to say the further you distance the php executable from the database - the more of an issue you'll see. Running a select or update via the DOS prompt can be misleading - a raw select will start to show results before the query is finished - maybe the php executable needs to wait for the complete data to be transported before it can continue and then there will probably more joins and stuff in the ss SQL and there is going to be more overhead because each record will be wrapped in a new datastructure...

I don't see how you cannot each have your own database that you simply "refresh" when required. I've worked at some very high end database application development and I've hardly seen everyone "required" not to use a local db. More over I've NEVER seen people all using the same database as this causes insane clashes during development and testing. So if people have an individual db on the server or the localhost it doesn't make much difference... unless they are working remotely... in which case I'd use a local database because I wouldn't even bother connecting via the internet for this!

If the databases need to be synchronised - then get a sync tool...
If you are worried about privacy - write a script to remove personal data...

I'm afraid I'd have to agree with swaiba on this one...

Remote databases through the internet are always going to be slow (and moreso with complex queries) than they are with local databases, or databases on another server in the same network where the latency and data transfer speed is fast.

It is more efficient to have developers use their own local database server on their laptops or desktops, as performance will be much improved and work will get done faster. The downside is the content they use may get out of date, but they can download a MySQL dump and import it locally every so often to check working copy changes are fine with the "master" content.

It is quite unusual to have multiple developers working off the same remote server, and even the same database on the remote server for that matter. I have tried it before on a remote database server on the same network used by a few others, and the experience was not good, especially when multiple queries are performed at once.

Thanks,
Sean

Also SS runs a heap ton of queries on the database not just 3 or 4 but often up in the teens (throw a ?showqueries=1 if you want to see) so even a tiny bit of latency exponentially effects performance. If you look at your database statistics / logs you may also see some record or table locking going on for multiple queries which would be delaying the process as well.