Solve and analyze the slow MySQL remote access speed and mysql remote access speed

Solve and analyze the slow MySQL remote access speed and mysql remote access speed
Environment Introduction:
OS: RHEL5.4
MySQL: 5.5.10
Exception scenarios:
The database has been accessed and used in the Intranet of the data center. A foreign node needs to access the database one day. After the access permission is activated, their feedback is very slow;
On the firewall, activate your company's IP address for access!
Start troubleshooting:
1. Check whether the network is stable? The answer is: The ping ttl value is stable, and ssh is also normal;
2. Check the MySQL running status without exception detection;
3. I suddenly remembered that MySQL's DNS-based resolution parameters may not be set during planning !!!
Solution:
1. Verify Parameters
Mysql> show variables like 'skip _ % ';
+ ----------------------- + ------- +
| Variable_name | Value |
+ ----------------------- + ------- +
| Skip_external_locking | ON |
| Skip_name_resolve | OFF |
| Skip_networking | OFF |
| Skip_show_database | OFF |
+ ----------------------- + ------- +
4 rows in set (0.00 sec)
Mysql> show variables like 'skip _ % ';
2. Stop the database and modify the parameters in my. cnf.
[Mysqld]
Skip_name_resolve
3. Verify after restarting the database:
Mysql> show variables like 'skip _ % ';
+ ----------------------- + ------- +
| Variable_name | Value |
+ ----------------------- + ------- +
| Skip_external_locking | ON |
| Skip_name_resolve | ON |
| Skip_networking | OFF |
| Skip_show_database | OFF |
+ ----------------------- + ------- +
4 rows in set (0.03 sec)
This option is used to disable DNS resolution, and the connection speed is much faster. However, you cannot use the host name in the MySQL authorization table but only the IP address.


Sort reference knowledge points:
Mysqld parameter sorting http://dev.mysql.com/doc/refman/5.5/en/mysqld-option-tables.html
Skip-name-resolve parameter Introduction 1: http://dev.mysql.com/doc/refman/5.5/en/server-options.html#option_mysqld_skip-name-resolve
Skip-name-resolve parameter Introduction 2: http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_skip_name_resolve
Host-cache parameter Introduction: http://dev.mysql.com/doc/refman/5.5/en/host-cache.html
Another method is to add the HOST_CACHE_SIZE definition (default value: 128) in the source code and re-compile MySQLd to Improve the Performance {not tested yet}