There can be many reasons that cause slow execution of mysql command, possibly amount of memory, cpu, database size, database engine and many more. In this tutorial we are going to talk about the connection being slow for mysql, your queries are running faster from command line but your web page scripts are not able to make connection faster. Response time from mysql is taking longer than usual. The major cause can be the DNS lookup. So how to fix this ?
| Id | User | Host | db | Command | Time | State | Info |
------------------------------------------------------------------------------------------------
| 152 | unauthenticated user | localhost | database | Connect | 80 | | NULL |
| 153 | unauthenticated user | localhost | database | Connect | 80 | | NULL |
------------------------------------------------------------------------------------------------
2 rows in set (0.00 sec)
When you run this 'show processlist' command, the output will be some what like above. If you are seeing 'unauthenticated user' in user column, this means that DNS host name lookup was failed. Whenever your scripts try to create a new connection, mysql tries to resolve host name for that request. It take the IP address who generate the request and resolves into host name and again takes the host name and resolves it to IP address. It ensures that IP address was original or not by comparing the results of both. This is the reason why creating connection is taking long time. How to solve this ?
Run command show processlist from mysql console
mysql> show processlist;
------------------------------------------------------------------------------------------------| Id | User | Host | db | Command | Time | State | Info |
------------------------------------------------------------------------------------------------
| 152 | unauthenticated user | localhost | database | Connect | 80 | | NULL |
| 153 | unauthenticated user | localhost | database | Connect | 80 | | NULL |
------------------------------------------------------------------------------------------------
2 rows in set (0.00 sec)
When you run this 'show processlist' command, the output will be some what like above. If you are seeing 'unauthenticated user' in user column, this means that DNS host name lookup was failed. Whenever your scripts try to create a new connection, mysql tries to resolve host name for that request. It take the IP address who generate the request and resolves into host name and again takes the host name and resolves it to IP address. It ensures that IP address was original or not by comparing the results of both. This is the reason why creating connection is taking long time. How to solve this ?
To make mysql connection faster disable the DNS lookup
You can do this by running your mysqld with –skip-name-resolve option or add it to your my.cnf file. This file is nothing but just the configuration file for the mysql.
On linux
root@localhost #] mysql> vi /etc/my.cnf
#Add following command
No comments:
Post a Comment