Optimize and fix Mysql Server, running slow without any load

by jagbir on July 14, 2008

I got stuck in a weird problem yesterday, where a newly installed MySQL Server was responding 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 verified when authenticating remote clients.

You may also like to read:
* Quick and simple php based script to check replication status of multiple MySQL Servers
* Setup multiple MySQL database servers in a single Linux host
* Optimize MySQL on a large Database Server
* Recover or reset root password of MySQL and PostgreSQL Servers
* How to setup MySQL Cluster 7.0 in Redhat based Linux
* How to find out the clients connecting to your MySQL server
* Quickly repair huge corrupted or crashed table in MySQL
* Install and configure PhpMyAdmin to manage multiple MySQL Servers

  • http://expert-seo-consultant.blogspot.com/ Learn SEO

    Nice work! Really you have done good job.

  • Nathan Goff

    Genius…I had some ugliness in my hosts file as well. Fixed the very slow MySQL issue.

    Thank you!

  • http://www.argentinawarez.com/ Snake

    Hello,

    Can you explain more the last step of the Hosts file?

    I’m having the same problem

    Thanks.

  • Tommie Delio

    90% off at hostgator BLACK FRIDAY SPECIAL tinyurl dot com hghosting101

  • Guest with crappy hostgator

    Yeah, mr spammer above me, hostgator may have discounts at times, but as a hostgator customer, I can tell you that they don’t know jack about the types of things mentioned in this blog. I have a dedicated server with them that has faced so many issues like this and their horrible support always tells me that there isn’t a way to tell why a server is slow. Really? So, yes, you may get a discount, but you don’t get much support for your website.

  • Michael Baldry

    Guest – If you have a dedicated server you shouldn’t expect them to support it. That’s a managed server.

  • Michael Baldry

    Oh, and I think the hosts thing may have fixed a long running issue I’ve been having! If so, I will be over the moon! Certainly seems faster right now!

  • Pingback: Rutweb Technology : Simple and efficient MongoDB Backup using script

  • Pingback: MySQL runs very slow after WAMP upgrade video

Previous post:

Next post: