Last updated on January 10th, 2023 at 02:39 pm
Slow database is always a pain and investigating the root cause of the slowness is something that needs patience basically in figuring out if a query is responsible for slowness or is it the server resources bottleneck etc.,
When my application try to access one of the remote MySQL servers I noticed that when trying it takes ages to respond/connect to it.
After searching around the web, I have found what appears to be the solution.
It looks like MySQL server has a feature that lookups hostnames for authentication EVEN IF you are using for example: [email protected] so to speed up remote connections to your remote database servers try adding the following line
skip-name-resolve
To your MySQL configuration file (my.ini/my.cnf) and then be sure to reload (restart) the MySQL database server. The [mysqld] section of the my.cnf file now looks like this:
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
skip-name-resolve
From Microsoft Windows this can be done from the ‘Services’ window under ‘Administrative tools’ or in Debian/Ubuntu entering this command:
/etc/init.d/mysql restart
The above addition will ONLY work if you are using IP Address for the Database. It usually wont work with direct hostnames.
If this change doesn’t resolve your problem other areas to check will be
1] MySQL Server resource utilization : Memory / CPU usage along with Disk Read/Write – Install ATOP utility to figure out exactly what is going on if you are using Linux
2] Enable Slow Query : The slow query log consists of SQL statements that take more time to execute. The slow query log can be used to find queries that take a long time to execute and are therefore candidates for optimization.
3] In WordPress: If you are using WordPress disable all plugins and check whether that improves the page load time. If that is the case enable plugins one by one to find the culrprit.
admin