The effect of cache and connection pools in Java on access database performance

zhaozj2021-02-11  210

I. Admission Cache and Connection Pool

Imagine such a situation: You suddenly thirst, need a glass of water to relieve it, from the mood, the faster, the better. Typically, a glass of water is generated from a water source (well water, river water or river, or even seawater, etc.), which is reached in the container of your drinking water through pipe transfer and equipment purification. The above process is necessary, but not every cup of water must repeat the above process once. You can use a big container (such as a cylinder or can, etc.) to a lot of water, you can get into a small portion of the cup before drinking water, your price is just transferring the water from the cylinder; you can still be a lot When using water (eg bath or washing, etc.), just open the water valve without having to temporarily lay the pipeline to the water source and the equipment to purchase purified water. Because water is something that is indispensable, every moment is used in a large number, and the physical essence is also exactly the same, so the government will lay pipes and build water treatment stations, and complete the more difficult work, reach resource sharing Objective, and you can also use containers to have a particular purpose water with your needs. This article will have a lot of similarities with the cache and connecting pools you discussed with the above-mentioned specific containers and transport pipes. They have achieved the same purpose: to share resources as much as possible in the premise of meeting the user's will to increase the entire system. Performance. Cache and connection pools are important technologies in data access. In some cases, applications have great improvements to access the database, and they have received universal support in the database industry. The former is implemented by DBMS vendors for its own database, providing a scheme for user configuration; the latter is a standard interface for JDBC, providing specific implementations by applying J2EE technology, and your Java program code does not need to be changed. This article will briefly introduce you to the concept and mechanism of the cache and connect pool, and show you the application of cache as an example in the PointBase database, and one

A simple connection pool application scenario will describe the conditions for the application and improve performance.

II. Concept and mechanism of Cache (Cache) and Connection Pool

They are not a business technology, but they get universal support from the database industry and have many similar applications in other fields of data access and object multiplexing.

1.cache (cache)

As a personal computer, you must have heard of these nouns: cache (cache), Memory, Hard Disk. They are all data access units, but the access speed is very different, which is sequentially decreasing. For CPUs, it can access data from their nearest Cache, rather than accessing data from memory and hard drives at a low order of magnitude. The data stored in cache is often a CPU to repeatedly access data, there is a specific mechanism (or program) to ensure the hit rate of data within Cache (HIT RATE)

. Therefore, the speed of the CPU access data has been greatly improved after the cache is applied.

For databases, vendor's approach is often a statement that opens up the corresponding area to store data that may be accessed multiple times and may be executed multiple times so that they do not have to submit to DBMS again when they are next access. Requests and those statements do not have to compile again when executed next time.

Because the task written to the cache is being responsible by the Cache Manager, the content of the cache to the user is definitely read-only. There is very little job you do, there is no difference in the SQL statement in the program and the direct access to DBMS, and there is no difference. Database vendors tend to provide parameters related to C Ache in DB Server, by modifying them, can optimize Cache management for our application. The picture below is the interface for configuring the MS Access data source in Win2k, where you can set the page overtime and buffer size of the "Driver" section is the parameters related to cache. In the discussion later, I will show a more complex database to explain the Cache impact on access to database performance and how to find the optimal configuration. 2.connection pool (connection pool)

The pool is a very common concept, and there are places where the buffer storage has a mechanism, which has reduced the access from the link, but it pays more attention to the resource sharing. The following figure shows the VPN dialing of the establishment of "Modem Pool" to share the modem resource.

Program:

For access to the database, the cost of establishing a connection is expensive, so we need to establish a "connecting pool" to increase the performance of access. We can use the connection as an object or device, and there are many connected connections in the pool. Accessing the original connection to the database, it is changed to connect with the pool, and the pool temporary allocation connection is available, the result is returned, visit, visit Connect the connection.

The JDBC 1.0 standard and its extension are not defined in the connection pool, and the interface related to the connection pool is defined in the extension of the JDBC 2.0 standard. The class corresponding to the interface is implemented by the application server vendor, and you can adjust the parameters of a database connection pool during the management process of the server. The figure below is a short description of the operation mechanism of the connection pool:

Three .case

1. Cache parameter setting

In the configuration parameter list of PointBase Database DB Server, we can find these parameters: cache.ch

EckpointInterval, cache.size, sqlcaching.size, etc. The following table is a description of each parameter:

-------------------------------------------------- -

Parameter name parameter description

-------------------------------------------------- -

Cache.checkpointInterval checkpoint time interval

Cache.size's maximum number of pages of cache (when the number of performance, the performance is best)

Sqlcaching.size number of SQL statements in the cache

-------------------------------------------------- ---

For cache.checkpointInterval, and the page timeout in the previous Access interface is a concept,

Set the time interval for page content update, depending on your application's degree of application.

For cache.size, specify the number of pages, which should generally be set to meet the needs of your query results. As for why is the number of prime, I am also wondering, but still following the manufacturer's instructions.

For SqlCaching.size, specify the number of stored SQL statements, you can set it to 0 to cancel this option.

How much increase in performance after using cache? I open PointBase's console to drive access to the Server via JDBC

Ver, select the Timing Mode under the SQL menu to display the time consumption of each step. The execution statement is: select * from product_tbl

For the first time, a total consumption of 1082 milliseconds, while compiling 771 milliseconds.

The second visit is followed, only 160 milliseconds, while the compilation is 0.

Then then the third access, the total meter is only 91 milliseconds, and the compilation is also 0.

Turn off Console, wait for more than 30 seconds, re-open console, perform the same statement, total consumption of 210 milliseconds, compiling consumption 20 milliseconds.

After waiting for more than 30 seconds, the statement is executed, the total table is 101 milliseconds, and the compilation is 0.

It can be seen that the application of the cache greatly improves the performance of the access database, and the settings of its parameters should be done according to their description, and you need to read the database configuration documentation.

2. Connect the pool settings and applications

I chose IBM's application server platform WebSphere to give you a demonstration to connect to the pool setting, so that you face friendship

The web interface can experience very simple operating scenarios.

First, we entered the WebSphere management console, this is a very beautiful web interface:

Fmpted, I chose a data source: Session Persistence Datasource, you can see this data source

The property is configured. Here, just list the properties related to the connection pool:

Minimum's minimum number of connections in the MINIMUM POOL Size pool; there is a new request, and when the connection is activated, the number of connections in the pool will increase, and the maximum number of connections is

The maximum number of connections held in the Maximum Pool Size pool; when this number is reached, no activation is available, the new request will wait

Connection Timeout When the number of connections reaches the maximum, and the activation connection is in use, the new request waiting time

Idle Timeout connection can idle in the pool; exceeding the resource will be released, to the minimum connection

ORPHAN TIMEOUT connection is idle when it is applied; more than will return to the pool

You can modify these values ​​as needed to meet your application needs. Next, we discuss the application of the connection pool.

i) .EJB Access Database (Scene 1, using JDBC 1.0)

Import java.sql. *;

Import javax.sql. *;

...

Public class accountbean implements entitybean {

...

Public Collection EJBFindBYLASTNAME (String Lname) {

Try {

String dbdriver = new initialContext (). Lookup ("Java: Comp / ENV / DBD

River "). TOSTRING ();

Class.Forname (dbdriver) .newinstance ();

Connection conn = NULL;

CONN = DriverManager.getConnection ("Java: Comp / Env / Dburl", "Useri

D "," password ");

...

CONN.CLOSE ();

}

...

}

If EntityBean is a shared component, you have to establish and release connections to the database each time you request, which has become the main problem with performance.

2) .ejb Access Database (Scene 2, using JDBC 2.0)

Import java.sql. *;

Import javax.sql. *;

// Import Here Vendor Specific JDBC Drivers

Public productpk ejbcreate () {

Try {

// Initialize JNDI Lookup Parameters

Context CTX = New InitialContext (PARMS);

...

ConnectionPoolDataSource CPDS = (ConnectionPoolDataSource) CTX.lo

OKUP (cpsource);

...

// FOLLOWING PARMS COULD All Come from a JNDI Look-Up

CPDS.SetDatabaseName ("PTDB");

CPDS.SETUSERIF ("XYZ");

...

PooledConnection PC = cpds.getpooledConnection ();

Connection conn = pc.getConnection ();

...

// DO Business Logic

CONN.CLOSE ();

}

...

}

The EJB component uses JNDI's LOOKUP () method to locate the connection pool resources of the database, and use the getConnection () method.

Get the already opened database connection, and use close () to release the connection, put it back in the pool. Therefore, less than the scene 1 is less than the loss of physical connections with the database. For applications originally turned on and off, the performance will definitely be greatly improved by this method of establishing logical connections and multiplexing.

IV. Deep Thoughts of Performance

Performance issues are not limited to the application of the database, but exist in each software system. We hope that the software system has the smallest, and the maximum obtained, so it is not optimized. Through the "Analysis and Solution of the Java Program Access Database" and this article, I analyzed the performance issues of Java programs to access the database and provide an optimized Java program solution, I hope to help you. Maybe you will care about how to analyze and solve similar performance problems, I will find the problem of "Accessing the speed bottleneck" problem of "Accessing the Database", and the opinions of netizens and their own experiences. Methodology

"The experience summary, I hope to be able to throw the jade to better solve similar problems.

Several experiences to solve performance problems

1. Do not let the hardware's low configuration has become an obstacle to the software running, the latter has the needs of the former, please meet immediately; often encounter such a problem "P166 64M machine runs Win2K mysql jboss, can you run? "I am answering"

Can you only stay in the screen while it is.

2, try to use commercial software, and enjoy good after-sales technical support; if you don't have a hacking, please don't use free software.

3, analyze your own problems, maybe it is different from others; no key to open.

4, determine the position of the bottleneck; solve the bottleneck problem, often solve the entire performance problem, do not grasp the edge of the edge.

5. Separate the bottleneck link into multiple sequential processes, use a substitute for alternative to test the core position of the bottleneck; subtractive issues have a further understanding of you. 6. Do what you can do and what to do, always go to your own realistic issue; don't try the problem that should be solved by the manufacturer (for example, write a JDBC driver yourself).

7. The programs of others are only for their reference, solving their own thinking; however, the situation of your application is different, and the application solution will have to understand the programs of others.

8, observe new technology, apply new technologies; it often contains the idea to solve the problem, but not visible to you.

9. After the problem is resolved, immediately stop, and report the result; after the real problem is resolved, there is no need to spend energy on the non-core issue, maybe they will never be encountered, don't want to solve themselves. The above experience is an innocent summary, and there is no rigorous logic derivation, only representing the idea of ​​solving technical problems, for you to meet similar problems. You can tell you in advance, the next article I will transfer the theme to JDO introduction and discussion. This unfamiliar API has entered Final Draft, but it can give data access from what benefits, and what is associated with JDBC, "JDO (Java Data Object) development and introduction" will come to you .

转载请注明原文地址:https://www.9cbs.com/read-5222.html

New Post(0)