MySQL very slow without a reason

Posted by 6bytes at 7, April, 2010

Category: MySQL

Tags: , , ,

Is your MySQL really slow without actually being too busy?
Does your scripts take very long time to connect to the database?
Do you have lots of RAM, strong CPU, just a little traffic and despite that a very long database response time?
Are you seeing connections with “unauthenticated user” while running show processlist in MySQL console?

mysql> show processlist;
+------+----------------------+-----------+----------+----------+------+-------+--------------+
| Id   | User                 | Host      | db      | Command   | Time | State | Info         |
+------+----------------------+-----------+----------+----------+------+-------+--------------+
| 2047 | unauthenticated user | localhost | myDB    | Connect   |   81 |       | NULL         |
| 2049 | unauthenticated user | localhost | myDB    | Connect   |   81 |       | NULL         |
| 2050 | unauthenticated user | localhost | myDB    | Connect   |   76 |       | NULL         |
...
+------+----------------------+-----------+----------+----------+------+-------+--------------+
131 rows in set (0.00 sec)

If you answered yes to any of the above questions your MySQL might have a problem with resolving connection’s host name.
When attempt is made for a new connection, MySQL tries to resolve the host name for that request. It takes the IP address and resolves it to a host name (using gethostbyaddr()). It then takes that host name and resolves it back to the IP address (using gethostbyname()) and compares to ensure it is the original IP address.
This might considerably increase connection time and slow down your whole application or produce show processlist result as above. You can easily solve this problem by disabling DNS host name lookups. In order to do this you need to run your mysqld with –skip-name-resolve option or add it to your my.cnf file like that:

[mysqld]
skip-name-resolve

After that running show processlist will result in “Host” column displaying only IP addresses instead of host names and the connection speed should be much faster.

Just keep in mind that also you have to change allowed hosts for your database users to proper IP addresses.
 

  • Nellai

    hi Kosinski,
    This is a nice post and it really helped me to solve a similar kind of issue what we faced in our prodcution system. Great work.
    thanks,
    Nellai…

  • Min Pavlovic

    Good website. I am going to require a bit of time to think over the info:D

  • I ran into this issue randomly. Editing /etc/my.cnf and adding “skip-name-resolve” did solve my problem. I still am not sure why it happened all of the sudden.

    • It was the same for me. One day just all of a sudden my MySQL slowed down considerably. Above was what fixed it for me.

  • Hi, this sounds as if it may by my problem BUT when I look in the event log I find that the MySQL user is attempting to connect to SQL Server Express.

    Login failed for user ‘datamon’. [CLIENT: ]

    datamon is the user on MySQL NOT SQL Server Express. Do both use localhost? Would it be possible to make this a named instance rather than localhost, or can I specify the port or something. (Sorry only my second day on MySQL)

  • Cesar Bazan

    You rock, man! Tnx for the advice!

  • Ali

    you saved my lif 🙂

    Thank you

  • Lucas Sandrini

    Perfect post! thanks!

  • Jo

    I had this problem also. Strange thing is that it was not always the case, probably depending on the load of the nameserver. Anyway my website is running fast as hell after applying the fix. (BTW setting the google nameservers instead of this tweak also fixed my site)

    • I’m glad my post was of help to you 🙂

      • Jo

        Yes, just found your site with google. Great post, thanks!

  • pongtohn

    thank you,
    That’s fix my problem.

  • armen

    Hey,

    It worked for me too.
    thanks

  • sergio

    It works!!
    Thank you very much!!!

  • koosha

    i was gonna buy a new laptop! developing when every page took about 40seconds to load was driving me crazy
    it’s 3seconds now 🙂
    tanx

  • Looking through my host with the LOCATE command and I can’t even find my my.cnf . Lots of example versions in lots of different folders but no live one from the looks of it. Is it possible that mysql can load up without this cnf file and just use some sort of defaults?

    • Hi

      You need to have my.cnf file. Try looking in one of those locations:
      /etc/my.cnf
      /etc/mysql/my.cnf
      $MYSQL_HOME/my.cnf
      $HOME/.my.cnf
      If still no luck try running this command
      $ sudo find / -name “*my*cnf”

      If all above fails you can try at least viewing your configuration from within MySQL console by running this command:
      SHOW VARIABLES;

  • Paulo

    Excelent post!
    It solved my problem quickly!

  • andres

    I just learned this: I was under the impression that using localhost or 127.0.0.1 as host address was the same thing… well, is not. using 127.0.0.1 made a huge difference in our scripts speed. I have no idea what was the fault in the dns configuration that resolving the domain localhost was taking that long, but when we switched to the ip, there was an amazing improvement in speed.

    • Good to hear I could help 🙂

  • Luke

    WOW!!!!!!!!!!!!!!!!!!!!! 2000 000% better!!!!!!!!!!

    Thanks so much!!!!!!!!!!!!!

  • Ehecatl

    Solved my problem.

    Thanks

  • Excellent. Thanks man! We are connecting with the IP to the MySQL but I suppose it was trying to do loopback search. Every site has been opening for more than 10 sec and you saved me this time but still… I need to find out where another half a sec get wasted. Maybe Apache? Hum… Once again thanks for the tip!

  • osay

    Resolve problem,
    Thank verry much!!!

  • chico

    Thanks sir .. it solve my problem.

  • This speeds up my cash register from 15 seconds to 7 seconds boot time on a i7…. thanks a lot for double speed!!!!!!!!!!!!!!!!!!!!!!!
    (Using Linux mint 15, Mysql 5.5.32

    Probably on Raspberry it will even be more.

  • Hi Wojciech,
    this tip gives you the award of “Hero of the day”. At least for me.

    Thank you for sharing!

  • George

    Thanks SOOO much for posting this, you saved our skin with our production system today. It has worked for 3 years with hardly a blip, then today it seriously dragging its heals so that its unusable. This solution worked perfectly and it was the cause of the problem.

    You rock!

  • cezar

    I tried it but it comes back with an error – could not connect to the database. Any advice?