I have a MySQL backend, MS Access front-end system that is nice and responsive over a local network but becomes sluggish over a broadband connection. I haven't done any performance tuning as of yet. What are the recommended tools and tuning methods that I should be using?
Recently, I discovered that MySQL keeps a 'General Query Log'. Maybe I can monitor the changes in this log file to see what requests are actually being made to the server. Is this a good method for fine tuning?
A good starting point is the MySQL Slow Query Log instead of the general query log. You can set the
You'll want to log queries that aren't using indexes
Update In your question, you state that the system is 'nice and responsive' over local network, but that you haven't done any performance tuning. The slow query log I pointed out will help you identify queries that are taking a long time to run (over 1 second, if configured that way). IMO, this is a great starting point. The longer a query takes, it is much worse when the response has to be transmitted over a WAN.
One tool I've recently discovered is mk-tcp-model that analyzes output from tcpdump to help measure how long a request takes to respond. You can see how many request/responses are coming in and how long each takes. The best tuning over a WAN is to reduce the amount of requests you need to make.
Are you using transactions?
Latency over the line affects the lock time of the transactions.
This will reduce your database performance drastically.
EDIT:
Try also to run MySQL with --skip-name-resolve
http://dev.mysql.com/doc/refman/5.0/en/dns.html
EDIT2:
I don't know your source code. If you use database operations in a foreach-style is also bad, especially if have a high latency network. In this case, try to get the required data at once, of course, only the required data.
I have a few good articles I wrote you that could use to tune MySQL from a variety of aspects. Have fun with them !!!
UPDATE 2011-05-28 06:43
Make sure you eliminate the use of "SELECT * FROM tbl ..." from your application. Replace them with "SELECT column1,column2,... FROM tbl ...". That way, you reduce the amount of data you transmit through your network.
Reduce the number of Stored Procedure calls (also tune the SQL commands within the Stored Procedure) you make, especially if your application relies on client-side processing of data. If you can move some of the BI aspects into the Stored Procedure, you will reduce network latency as well.
If you are using MySQL Replication, make sure the Master and Slave are in the same subnet. In fact, use crossover cables over 192.168.x.x. and have the slave use the 192.168.x.x. subnet of the master as the master host. In terms of replication between data centers, you should use thedistribution master topology to transmit only binary logs, which will offload that function from the master.
UPDATE 2011-06-13 17:10
You may want to look over the slow query log, not the general log. You may need to change the variable long_query_time to 1 second (default is 10) so as to see what queries take longer than 1 second. You could then run EXPLAIN on all SELECT queries and hopefully find what indexes each table could potentially need.
What are the main differences between InnoDB and MyISAM?
How to improve InnoDB DELETE performance?
How to safely change MySQL innodb variable 'innodb_log_file_size'?
Clarification on MySQL innodb_flush_method variable
https://serverfault.com/questions/230551/mysql-innodb-innodb-file-per-table-cons/231400#231400
https://stackoverflow.com/questions/5174396/innodb-performance-tweaks/5348378#5348378 (MySQL 5.5 only)
