Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I work in a small development team - PHP/MySQL app.

Each in-house developer has Apache/PHP installed and is accessing a Windows 7 box with MySQL 5.1 installed. Box is 32bit, Pentium D, 4GB ram, nothing tweaked with my.cnf. Network is on a gigabit switch, gigabit NICs.

I am curious why this setup is so obviously slow. Accessing an empty database on this server from my desktop is very, slow. 5 seconds or so just to list databases.

I don't expect it to be as fast as our production cluster @Softlayer, but 5 seconds to display an empty database seems a bit much.

Has anyone done specific things to speed up a office development setup utilizing a database server?

share|improve this question

4 Answers 4

This may sound a little odd, but check that the MySQL server is able to resolve domains quickly and correctly. Some query browsers send authentication with each command and if you have slow resolvers it kills the performance.

We had a very similar problem once when our internal DNS was down for maintenance.

share|improve this answer
2  
Delays of three or five seconds are often indicative of network problems of some sort. These are common timeout periods. Try nslookup for a developer's hostname on the MySQL server. It should return almost immediately. If not, then you may have authentication delays. –  BillThor Nov 17 '10 at 3:22
    
"You can disable DNS host name lookups by starting mysqld with the --skip-name-resolve option. However, in this case, you can use only IP addresses in the MySQL grant tables." - dev.mysql.com/doc/refman/5.0/en/dns.html –  danlefree Nov 17 '10 at 8:53

add in my.cnf:

skip_name_resolve in [mysqld_safe] and [mysqld] sections

share|improve this answer

It should not be so slow based on the configuration you have mentioned even without any optimization/tweaking. Most likely there is some other problem.

Try to use mysql clients on same Windows 7 machine and see if they are slow or not. At least this will help in deciding whether problem is network related or not.

If problem is network related then capturing packets using wireshark to see what is actually on wire is really helpful in troubleshooting delay problems.

If problem is host related check:

  1. How much hard disk space is free and is partition to much fragmented.
  2. Is some other process eating RAM
  3. Is some other process utilizing CPU
  4. Go through MySQL logs to see if MySQL has reported some problem.
share|improve this answer

Logs will most assuredly help here -

I would start with some basic MySQL monitoring - a few tools that may help here are:

  1. top (default in linux)
  2. mtop: http://mtop.sourceforge.net
  3. innotop: http://innotop.sourceforge.net

You can also resort to the MySQL monitoring tool from the command line (cli)

# mysqladmin processlist 
# mysqladmin proc

Sadly - many folks will use the default templates made available under /usr/share/mysql

I HIGHLY RECOMMEND SKIPPING THEM - they are old and not worth using for the following reasons:

  • Invalid settings for some OS or MySQL versions (e.g., thread_concurrency and skip-locking)
  • Adding huge array variables that you might not understand what they are
  • Performance from testing them against other settings are more than just questionable

After running those - and doing some tuning - let mysql run for at least 24-48 hours and then use the following diagnostic tools:

  1. MySQL tuner (just type wget mysqltuner.pl in linux to download then chmod755 mysqltuner.pl and run it by doing ./mysqltuner.pl ;-)

  2. mysqlidxchk

    (Install by wget hackmysql.com/scripts/mysqlidxchk-1.1 chmod 755 mysqlidxchk* mv mysqlidxchk* mysqlidxchk ) then run by doing something like this: ( ./mysqlidxchk --general /var/lib/mysql/ general.log )

If you need some help installing those as well please let me know

PS - most of these are linux only scripts - but will help a huge amount with the tuning and management of your mySQL server.

Finally - yse a good log parsing tool like mysqlsla and mysqlreport for status reporting

share|improve this answer
    
Using the good OS for the good task would help a lot too, see dev.mysql.com/doc/refman/5.1/en/limits-windows.html Anyway your post is a good description of the first steps to take to find a performance problem, thanks for mysqlidxchk I didn't know about it ! –  Shadok Feb 11 '11 at 17:00

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.