Connecting (connection) affects MySQL performance
I would like to ask you how to solve this, the interview questions of the other day
phenomenon : connecting (connection) is also an important aspect that affects the performance of MySQL. The connection between the MySQL client and the MySQL server is the result of repeated handshakes between the MySQL client and the MySQL server. Each "handshake" goes through authentication, authority verification and other links, and the handshake needs to take up certain network resources and MySQL server memory resources.
Please propose a solution to the above phenomenon.
use client connection pooling
connection pooling is common in the java technology stack.
such as
connection pool
https://en.wikipedia.org/wiki.
Database
https://en.wikipedia.org/wiki.
JDBC database connection pool
https://docs.oracle.com/cd/E1.
Common open source connection pooling implementations of java
https://java-source.net/open-.
Ali's druid is also used more recently.
but other languages are quite different. For example, PHP, doesn't seem to have heard of anything useful.
use connection pooling, that is, persistent connections, which can be recycled repeatedly by the same thread. Handshake verification is required only when the thread establishes the connection
< H2 > eg: execute update sql 100 times < / H2 >
sql is not performed using connection pooling
-- sql
-- : |<--------- t1 -------->|<------ t2 ----->|<------- t3 ------>
-- : tt1 = 100 *t1 + t2 + t3
execute sql using connection pooling
-- 5
-- 5 * t1
-- : tt2 = (5 * t1) + 100 * t2
-- :
obviously: tt2 < tt1
< H2 > so, uses connection pooling to solve the extra overhead caused by executing sql! < / H2 >
< H2 > Database connection pooling commonly used in Java < / H2 >
-
C3P0 is an open source JDBC connection pool that is released with Hibernate in the lib directory and includes DataSources objects that implement the Connection and Statement pools specified in the jdbc3 and jdbc2 extension specifications. (home page: http://sourceforge.net/projec.)
-
BoneCP is an open source fast JDBC connection pool. BoneCP is very small, just over 40K (runtime requires the support of log4j and Google Collections, which add up), while C3P0 costs more than 600K. In addition, I think the disadvantage of BoneCP is that the loading of JDBC drivers is outside the connection pool, so it is not flexible enough in the configuration of some application servers. Of course, small size is not the reason why BoneCP is excellent. What is the highlight of BoneCP? please take a look at the performance test report. (home page: http://jolbox.com/)
-
DBCP (Database Connection Pool) is a database connection pool that relies on the Jakarta commons-pool object pooling mechanism, and Tomcat's data source uses DBCP. There are currently two versions of DBCP: 1. 3 and 1. 4. Version 1.3 corresponds to JDK 1.4-1.5 and JDBC 3, while version 1.4 corresponds to JDK 1.6 and JDBC 4. So when choosing a version, you have to look at what JDK version you are using, but there is no difference in function. (home page: http://commons.apache.org/dbcp/)
-
Druid is a distributed, column-oriented, real-time analytics data store that is commonly used to power exploratory dashboards in multi-tenant environments. Druid excels as a data warehousing solution for fast aggregate queries on petabyte sized data sets. Druid supports a variety of flexible filters, exact calculations, approximate algorithms, and other useful calculations. Druid can load both streaming and batch data and integrates with Samza, Kafka, Storm, Spark, and Hadoop. ( http://druid.io/)
here in particular, Druid is Alibaba's open source connection pool, "Ma Dad" has recently been a bit gone with the Wind , Druid is obviously Chinese open source software, the official web page unexpectedly did not find the entry of Chinese documents, the official website is all English! Originally, I wanted to introduce Druid , but after thinking about it, I'd better forget it.
1. Use connection pooling
2. Use bulk insert / query
