Mysql database remote connection very slow solution _mysql

Source: Internet
Author: User
Tags flush mongodb mysql host postgresql redis

One time colleagues suggested that the MySQL database connection used for development is slow because our MySQL development database is deployed on a separate machine, so we think it might be caused by a network connectivity problem.

After pinging and route, the network traffic is found to be normal, and the local connection on the MySQL machine is found to be very fast, so the network problem is basically excluded. I have encountered such a problem before, but then I do not know how suddenly good, this time again encountered such a problem, so want to see whether the MySQL configuration problem. After querying MySQL related documents and web searches, a configuration appears to solve the problem of adding the following configuration parameters to the MySQL configuration file:



In Linux, the profile is/ETC/MY.CNF, and the configuration file under Windows is the My.ini file under the MySQL installation directory. Note that the configuration is added under [mysqld], after you change the configuration and save, then restart MySQL and connect to the test remotely, and everything is back to the same. The official explanation information for this parameter is as follows:

 code as follows:

How MySQL uses DNS

When a new thread connects to Mysqld, Mysqld'll spawn a new thread to handle the request. This thread would be the hostname is in the hostname cache. If not the thread would call Gethostbyaddr_r () and Gethostbyname_r () to resolve the hostname.

If the operating system doesn ' t support the above Thread-safe calls, the thread would lock a mutex and call gethostbyaddr () and gethostbyname () instead. Note ' In ' This case no other thread can resolve the other hostnames so is isn't in the hostname cache until the D is ready.

You can disable DNS host lookup by starting mysqld With–skip-name-resolve. In this case you can however only use IP names in the MySQL privilege tables.

If you are have a very slow DNS and many hosts, you can have more performance by either disabling DNS Lookop Solve or by increasing the Host_cache_size define (default:128) and recompile mysqld.

You can disable the hostname cache with–skip-host-cache. You can clear the hostname cache with FLUSH HOSTS or mysqladmin flush-hosts.

If you don ' t want to allow connections over TCP/IP, you can do this by starting mysqld with–skip-networking.

According to the documentation, if your MySQL host query DNS slow or a lot of client host will cause the connection is slow, because our development machine is not able to connect the extranet, so DNS parsing is impossible to complete, thus also understand why the connection so slow. Also, note that after adding this configuration parameter, the Host field in the MySQL authorization table will not be able to use the domain name and only use the IP address, because this is the result of a domain name resolution that is forbidden.

ApsaraDB RDS for MySQL - A fully hosted online database service that supports MySQL 5.5, 5.6, 5.7, and 8.0.

Alibaba Cloud Hot Products

Elastic Compute Service (ECS) Dedicated Host (DDH) ApsaraDB RDS for MySQL (RDS) ApsaraDB for PolarDB(PolarDB) AnalyticDB for PostgreSQL (ADB for PG)
AnalyticDB for MySQL(ADB for MySQL) Data Transmission Service (DTS) Server Load Balancer (SLB) Global Accelerator (GA) Cloud Enterprise Network (CEN)
Object Storage Service (OSS) Content Delivery Network (CDN) Short Message Service (SMS) Container Service for Kubernetes (ACK) Data Lake Analytics (DLA)

ApsaraDB for Redis (Redis)

ApsaraDB for MongoDB (MongoDB) NAT Gateway VPN Gateway Cloud Firewall
Anti-DDoS Web Application Firewall (WAF) Log Service DataWorks MaxCompute
Elastic MapReduce (EMR) Elasticsearch

Alibaba Cloud Free Trail

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.