Home / server / Questions / 202759
In Process
jaredsten
Asked: 2010-11-17 19:02:11 +0800 CST

Increase speed of small office MySQL server

  • 772

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?

mysql network-speed

4 Answers

  1. 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.

    • 5

  2. add in my.cnf:

    skip_name_resolve in [mysqld_safe] and [mysqld] sections

    • 2

  3. 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.
    • 1

  4. 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

    • 0