Caching and connection pooling impact of access to the database performance: Free Java evangelist from: Tsinghua acquaintance caching and connection pooling Imagine this scenario: you suddenly thirsty, need a glass of water to ease, speaking from the heart, Of course, the sooner 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 concepts and mechanisms of cache and connect pools, and to show you the cache app with the PointBase database, and a simple connection pool application scenario will describe the conditions for the application and improve performance. The concepts and mechanisms of Cache (Cache) and Connection Pool They are not generated by the database, but they get universal support from the database industry and have many similar applications in other data access and object multiplexing. Cache (Cache) As a daily user of 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 the data of the CPU to repeatedly access, there is a specific mechanism (or program) to ensure the hit rate of data within the Cache. 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 work that you do, there is no difference in the SQL statement in the program and the direct access to DBMS, and the result is not different. Database vendors tend to provide parameters related to cache in DB Server profiles, 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 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. The Connection Pool pool is a very common concept that has a relatively similar to the buffer storage, but it is more focused on the sharing of resources. The following figure shows the VPN dial scheme for establishing the Modem pool to share modem resources: For access to the database, the cost of establishing a connection is expensive, so we need to build a "connecting pool" to improve 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 manufacturer, and you can adjust the parameters of a database connection pool during the management process of the server. The figure below describes the running mechanism of the connection pool: the cache parameter settings in the configuration parameter list of the PointBase database DB Server, we can find these parameters: cache.checkpointInterval, cache.size, sqlcaching.size, etc. The following is a description of each parameter: Cache.CheckPointInterval checkpoints Cache.Size Cache.Size Cache's maximum page number (when the number of performance) SQLCACHING.SIZE The number of SQL statements in cache.checkpointInterval And the page timeout in the previous Access interface is a concept, which specifies the time interval for page content update, depending on your application's requirements for timeliness. 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 access Server via JDBC, 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, the total consumption is 1082 milliseconds, and the compilation is 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 are done according to their description, and you need to read the database's configuration documentation. Connecting the pool settings and Applications I Select IBM's Application Server Platform WebSphere to demonstrate the connection to the pool, so that you can face a very simple operating scenario. First, we entered the WebSphere's management console, this is a very beautiful web interface: followed by, I selected a data source: Session Persistence Datasource, you can see the properties of this data source configuration. Here, just lists the attributes related to the connection pool: the minimum number of connections held in the Minimum Pool Size pool; there is a new request, and when no activation is available, the number of connections in the pool will increase, to the maximum connection number 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 for the connection timeout when the number of connectivity reaches the maximum, and the activation connection is in use, The new request waiting time idle timeout connection can be idle in the pool; exceeding the release resource, to the minimum connection number, the ORPHAN TIMEOUT connection is idle when it is applied, and the time will be idle; more than the return pool you can To modify these values to meet your application needs. Next, we discuss the application of the connection pool. 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 / dbdriver). Tostring (); class.forname (dbdriver) .newinstance (); connection conn = null; conn = drivermanager.getConnection (" Java: comp / ENV) / Dburl "," userid "," password "); ... conn.close ();} ...} If the EntityBean is a shared component, then each customer request is requested to establish and release the connection with the database. This has become the main problem of performance.
EJB access database (Scene 2, using JDBC 2.0) import java.sql *;. Import javax.sql *;. // import here vendor specific JDBC driverspublic ProductPK ejbCreate () {try {// initialize JNDI lookup parameters Context ctx = new InitialContext (parms); ... ConnectionPoolDataSource cpds = (ConnectionPoolDataSource) ctx.lookup (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 ();} ...} EJB component utilizes JNDI The LOOKUP () method locates the connection pool resource of the database, using the getConnection () method to 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. The profound thinking performance of performance issues is not limited to the application of the database, but exists 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. Summary of the experience of "Methodology", hoping to throw brick, better solving 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 encountered such a problem "P166 64M machine running Win2K mysql JBOSS, can you run? "I only responded to the screen while answering the screen. 2. Try to use commercial software, and enjoy good after-sales technical support; if you don't have a hacking, please do not use free software. 3. Analyze your own questions, perhaps its nature and others; no key can be opened. 4. Determine the position of the bottleneck; solve the problem of bottlenecks, often solve the entire performance problem, don't grab the edge of the edge. 5. Separate the bottleneck link into multiple sequential processes, with an alternative method to test the core position of the bottleneck; the problem has a further understanding of you.
