Linux Admin Zone

I got stuck in a weird problem yesterday, where a newly installed MySQL Server was responsindg slow. I’ve fixed the issue after detecting a small spelling mistake in file which is entirely not related to MySQL at first instance. Here are some tips, if you’re also on the same boat where Server itself doesnt showing any error, but your user’s lodging complaints of being slow.

1. Check MySQL Configurations:

my.cnf: My Server machine has 2 GB of RAM, So, I’ve optimized my.cnf to make use of plenty of RAM. But that was not the issue because eventually no one was accessing this Server except some perl scripts residing on another Server in LAN. Its a good practice though, to replace your /etc/my.cnf with my-huge.cnf in case your machine have 2 or more GB of RAM and fast multi-core processor. You can find my-huge.cnf in /usr/share/doc/MySQL-server-community-5.0.51a/ directory if you have installed latest MySQL version (5.0.51a while writing this article).

DNS Resolution: I was accessing MySQL server from remote machine using its IP Address, so DNS should not be the issue. Check DNS stuff, if you are trying hostname instead of IP. For optimum response, always access db server with its IP. To do that, simply add “skip-name-resolve” keyword in mysqld section of your my.cnf.

Bind to IP: Its better to bind Server to a particular IP Address. you can use something like this bind-address=192.168.0.5 in your my.cnf

Turn on Logging: Turn on slow queries logging and find out queries running too slow. This was also not the issue at my side, but its better to have a look at it. You can use log-slow-queries = /var/log/mysql-slow-query.log statement in my.cnf under mysqld section to enable logging.

Note: You need to restart your MySQL Server everytime after updating config file. As we are investigating slow response issue, I assume you already have user’s and proper rights to access the Server from remote site.

2. System State: After checking the MySQL settings, you need to verify various parameters in the Server machine, some important points you should check:
Load: Check the current load by using top or cat /proc/loadavg commands.
Disk Space: Check disk space using df -h command and verify there’s some free space for proper working of Server. You can also check excessive I/O requests using iostate command.

3. Network: Time to check network settings. Check port 3306 (MySQL default port) is open. To check whether this port is open and accessible from remote, issue “telnet <mysql_server_IP> 3306″ command, it should get connected otherwise check your network/firewall settings.

So, you have checked everything and found that MySQL response is still slow even there’s no load, no disk space or network issue, accessing with IP Address so no DNS issue, etc. etc. and etc.

At the last resort, I checked hostname of my Server and amazed that by fixing hostname properly, My problem get resolved. How? check it:
# hostname
testservre.example.com
# cat /etc/hosts
127.0.0.1 testserver testserver.example.com localhost.localdomain localhost

You can see that there’s slight spelling mistake in hostname, displayed by hostname command and that written in hosts file. Just fix your hosts file and check it. You should note down that, even after using IP Address to access MySQL Server, its still important to set proper DNS Hostname for your Server as its was verfied when authenticating remote clients.

database hostname, hosts, linux, mysql, optimize, slow query