Thread

I have an EE site I am working on locally using a remote MySQL database.  The connection is fast when using an application like Navicat to manage the database, but when accessing the site with EE, it is very slow. 

For example, If I load the main edit page in the admin from the live site, it loads very quick - Script executed in 0.2700 seconds   38 SQL queries used.  If I load the same page using my local EE version with the remote DB, it takes a lot longer - Script executed in 11.9361 seconds   38 SQL queries used.

I have full access to the server, so I can make any adjustments needed. Does anyone have any suggestions for speeding things up?

Start with your machine and opening the firewall for the port used.(3306 or something is the default).
To be honest speed is relative concept and depends on so many points…your ISP speed - dynamic or static IP, your machine/webserver, firewall ACLs rules, remote server and IP filtering etc. etc.
Why are you not using DB on local machine?

I don’t think it’s the port because the DB is very fast using other programs, such as Navicat or Sequel Pro.

I’m sure my ISP speed is good, and the latency between me and the server is normal.

I can’t use the DB on a local machine because I am making changes on a live site.  I am using Git to deploy the file changes when needed, but there are other changes being made to the database, so it would really be easy to merge a local and live version.

Also, I read that adding skip-name-resolve to the my.cnf might help. I did that but it didn’t change much, so I am wondering if there are any other settings or adjustments that might work.

the round trip latency might be the biggest factor here.  each query will have some network overhead… even if you’ve got only 100ms latency to the server that’s already 3.6 seconds for 36 queries.

might want to run a traceroute or ping to the server to see how long it takes for packets to go round trip.

for what it’s worth here’s what I get when I ping our production server from my desktop:

10 packets transmitted, 10 packets received, 0.0% packet loss
round-trip min/avg/max/stddev = 15.725/50.998/262.184/76.726 ms

The latency is about 70ms. It just doesn’t make sense to me that it’d be almost 12 seconds for 38 queries.

well you could always benchmark it… just need to add timers to the query method in the DB class:

system/db/db.mysql.php:

line 86, add:

var $total_time = 0;

top of query($sql) function (about line 237), add:

$mtime = microtime(); 
$mtime = explode(' ', $mtime); 
$mtime = $mtime[1] + $mtime[0]; 
$timer_start = $mtime;

bottom of query($sql) function (about line 414), just above ‘return $DBC’ add:

$mtime = microtime(); 
$mtime = explode(' ', $mtime); 
$mtime = $mtime[1] + $mtime[0]; 
$timer_end = $mtime;

$this->total_time += $timer_end - $timer_start;
print "queries took " . $this->total_time . " seconds so far
\n";

there are certainly more elegant ways of setting up a timer, but as a one-off test of whether this is your problem or not I’d say it’ll do the job.

To be honest speed is relative concept and depends on so many points…your ISP speed - dynamic or static IP, your machine/webserver, firewall ACLs rules, remote server and IP filtering etc. etc.