Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a MySQL backend, MS Aaccess 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?

share|improve this question
    
Updated my answer to discuss network latency issues that your can eliminate from your app !!! –  RolandoMySQLDBA May 28 '11 at 10:57
    
I wrote about this in my blog recently, for Oracle not MySQL, but the numbers clearly show that network latency is an absolute killer of DB performance –  Gaius Jun 14 '11 at 9:06
add comment

3 Answers

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.

share|improve this answer
    
"nice and responsive over a local network but becomes sluggish over a broadband connection" - surely general performance tuning is not the issue? –  Jack Douglas May 28 '11 at 4:59
1  
@JackDouglas Some good information that I'll probably use in the future. However I don't appear to have any slow queries and I have since discovered some inefficiencies from looking at the general log. I have noticed that the front-end, in a particular event that is repeated frequently, calls 4 different stored functions that could be replaced by a single stored procedure call using multiple out parameters. I'm guessing this has a big impact over a WAN with high latency! I'll see what difference it makes. –  David May 28 '11 at 14:32
    
@David - reducing the number of round trips like that is probably a very good idea. –  Jack Douglas May 29 '11 at 12:37
    
@DTest : I read this through. Deserves +1 !!! –  RolandoMySQLDBA Jul 13 '11 at 2:02
add comment

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.

share|improve this answer
    
I'm not using transactions. But are you saying that USING transactions will reduce performance or NOT USING transactions will reduce performance? –  David May 27 '11 at 13:55
    
Using 'long' running transaction will reduce performance. If you use transactions you can prevent this by 'Read Uncommitted' (Dirty Read etc.). But try also the --skip-name-resolve option it helps me to get better performance. –  edze May 27 '11 at 14:19
add comment

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

http://serverfault.com/questions/230551/mysql-innodb-innodb-file-per-table-cons/231400#231400

http://stackoverflow.com/questions/5174396/innodb-performance-tweaks/5348378#5348378 (MySQL 5.5 only)

http://drupal.stackexchange.com/questions/1715/what-would-the-optimal-mysql-configuration-for-a-drupal-7-site-be/2367#2367

share|improve this answer
    
"nice and responsive over a local network but becomes sluggish over a broadband connection" - surely general performance tuning is not the issue? Do you know any resources that specifically address latency? –  Jack Douglas May 28 '11 at 5:00
    
After looking at the general log it looks like I might be able to make a big difference by combining multiple stored function calls into a single stored procedure call. I'll see what difference it makes. –  David May 28 '11 at 14:38
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.