Shulou(Shulou.com)05/31 Report--

This article introduces the knowledge about "how to understand MySQL performance optimization: long connection, short connection, connection pool". In the actual case operation process, many people will encounter such difficulties. Next, let Xiaobian lead you to learn how to deal with these situations! I hope you can read carefully and learn something!

When the database server and client are on different hosts, a network connection needs to be established to communicate. The client must use a database connection to send commands and receive replies and data. By specifying the connection string via the driver provided to the client database, the client can establish a connection to the database. You can consult the program language manual to learn how to use short and long joins.

1. short connection

Short connection means that the program and database communication needs to establish a connection, after the operation, the connection is closed. Short connection is simply to open and close the database connection every time you operate the database. The basic steps are: connection → data transfer → close the connection.

With short connections on slow networks, the connection overhead is high; in busy systems, connections may also be limited by the number of ports in the system. If thousands of connections are established per second, the ports will not be recycled immediately after the connection is disconnected, and must go through a "FIN" phase until they can be recycled. This may lead to insufficient port resources. On Linux, you can adjust

The requested URL/proc/sys/net/ipv4/ip_local_port_range was not found on this server.

/proc/sys/net/ipv4/tcp_fin_timeout to reduce recycling delays (be careful if you want to adjust this parameter on the application server!).

Another option is for hosts to use multiple IP addresses. The limit on the number of ports is actually based on the same IP:PORT. If the host adds an IP, MySQL can listen to multiple IP addresses, and the client can also choose to connect to a certain IP:PORT, thus increasing port resources.

2. long connection

A long connection means that after the connection between programs is established, it is always open and reused by subsequent programs. The original intention of using long connections was to reduce connection overhead, although MySQL connections are much faster than other databases.

For example, when PHP receives a request for a persistent connection, PHP checks to see if there is already an identical persistent connection. If it exists, the connection is used directly; if it does not exist, a new connection is established. An "identical" connection is one that uses the same username and password to connect to the same host.

From the client's point of view, using long connections has the advantage of not having to create new connections every time, and permanent connections are more efficient if the client requests MySQL servers frequently. For high concurrency services, long connections or connection pools are recommended if connection impact is likely.

From the server's point of view, the situation is slightly different, which saves the overhead of creating connections, but maintaining connections is also memory-intensive. If you abuse long connections, you may use too many MySQL server connections. Modern operating systems can have thousands of MySQL connections, but it is likely that most of them are in sleep state, which is not efficient enough, and the connections take up memory and lead to memory waste.

For good scalability site, in fact, most of the access does not need to connect to the database. If users need to access the database frequently, performance problems may occur when traffic increases, and long and short connections cannot solve the problem, so reasonable design and optimization should be carried out to avoid performance problems.

If there is a connection pool or proxy between the client and MySQL database, short connections are generally recommended on the client side. For the use of long links must be careful, not abuse. Without hundreds or thousands of new connection requests per second, long connections aren't necessarily needed, and you can't get much benefit from them. In Java language, because there is a connection pool, if properly controlled, there will be no big impact on the database, but PHP's long connection may cause the number of database connections to exceed the limit, or take up too much memory.

In this regard, R & D engineers, system operation and maintenance engineers, DBAs need to maintain communication, determine a reasonable connection strategy, do not use long connections without thinking.

3. connection pool

Because some databases are expensive to create and destroy connections, or because connections consume too many resources relative to the specific data operations performed, you may need to add connection pools to improve performance.

Database connection pool is a feature implemented by some network proxy services or application servers, such as J2EE server, which implements a persistent connection "pool" that allows other programs and clients to connect. This connection pool will be shared by all connected clients. Connection pool can speed up connections, reduce database connections, and reduce the load on database servers.

4. Difference between persistent connections and connection pools

Long connections are a feature of some drivers, driver frameworks, and ORM tools. Drivers keep connection handles open so that subsequent database operations can reuse connections, thereby reducing database connection overhead. The connection pool is a component of the application server. It can configure the number of connections, connection detection, connection life cycle, etc. through parameters.

If the number of connections used by the connection pool or long connection is large, it may exceed the limit of the database instance, so you need to pay attention to the connection-related settings, such as the minimum and maximum number of connections in the connection pool, and the number of php-fpm processes, etc., otherwise the program will not be able to apply for new connections.

"How to understand MySQL performance optimization: long connection, short connection, connection pool" content is introduced here, thank you for reading. If you want to know more about industry-related knowledge, you can pay attention to the website. Xiaobian will output more high-quality practical articles for everyone!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.