Environment Introduction:
os:rhel5.4
mysql:5.5.10
Exception Scenario:
database has been in the computer room intranet access and use, a day a foreign node needs to access the database, access rights, they feedback is very slow;
immediately on the fire wall, opened their own company IP access, there are similar problems!
Start Troubleshooting Reason:
1. Check that the network is stable? Get the answer, ping ttl value is stable, ssh is normal;
2. Check MySQL running status, no abnormal discovery;
3. Suddenly think of, MySQL use DNS to do parsing parameters may be in the planning, did not set!!!
Workaround:
1. Validation 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 library, modify the parameters inside the my.cnf
[Mysqld]
Skip_name_resolve
3. After restarting the database, verify that:
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 disables DNS resolution, and the connection speed is much faster. However, it is not possible to use the hostname in the MySQL authorization table and only use the IP address.
reference point of Knowledge collation:
mysqld parameter Finishing 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 Description: http://dev.mysql.com/doc/refman/5.5/en/host-cache.html
Another way: Add the host_cache_size definition in source code (default: 128) and recompile mysqld to improve performance {temporarily not tested}
MySQL remote access slow resolution and analysis
