Improve cas performance through connection pool and field Index

Cas is the central authentication of multiple systems. the authentication process is the process of pairing the user's login information with the information in the database. If a large number of users log on at a certain time point, the database needs to be read frequently, and the management of database connections is a problem.

The Single Sign-On problem was accidentally exposed during the test evaluation the day before yesterday. It is normal to log on to the management end through cas, while 200 people encounter exceptions when logging on to the student end through cas at the same time.

Org. springframework. jdbc. CannotGetJdbcConnectionException: cocould not get JDBC Connection; nested exception is java. SQL. SQLException: Listener refused the connection...

 

When implementing single-point logon, I focus on implementation without considering specific application scenarios. Therefore, the application must be put into practical use, otherwise, you will always stay in the ideal learning environment.

 

Solving problems is important, and more importantly, discovering problems. When the error message is displayed, it is obvious that the Connection resources are limited, so the following optimization is performed:

1. Add a Connection Pool for cas.
2. Configure the Connection Pool of Oracle. (The database uses Oracle, while Oracle does not enable the connection pool by default)
3. Create an index for frequently queried Fields

 

Add Connection Pool for cas

The basic idea of the database connection pool is to create a "buffer pool" for the database connection ". A certain number of connections are put in the buffer pool in advance. When you need to establish a database connection, you only need to extract one from the "buffer pool" and put it back after use.

 

Both DBCP and C3P0 have similar configurations, because the attributes of the connection pool are common. Taking C3P0 as an example, it is configured in deployerConfigContext. xml of cas.

Add the following content in tomcat/webapps/cas/WEB-INF/deployerConfigContext. xml:

<Span style = "font-size: 18px;"> <bean id = "casDataSource" class = "com. mchange. v2.c3p0. comboPooledDataSource "destroy-method =" close "> <property name =" driverClass "value =" $ {jdbc. driverClassName} "/> <property name =" jdbcUrl "value =" $ {jdbc. url} "/> <property name =" user "value =" $ {jdbc. username} "/> <property name =" password "value =" $ {jdbc. password} "/> <! -- Maximum connections --> <property name = "maxPoolSize" value = "$ {hibernate. c3p0. max_size}"/> <! -- Least connections --> <property name = "minPoolSize" value = "$ {hibernate. c3p0. min_size}"/> <! -- Initialize connections --> <property name = "initialPoolSize" value = "$ {hibernate. c3p0. initial_pool_size}"/> <! -- Maximum idle time. connections are discarded if they are not used within 120 seconds. If it is 0, it will never be discarded --> <property name = "maxIdleTime" value = "$ {hibernate. c3p0. max_idle_time}"/> <! -- When the connections in the connection pool are used up, C3P0 obtains the new connections --> <property name = "acquireIncrement" value = "$ {hibernate. c3p0. acquire_increment} "/> <! -- Maximum number of PreparedStatement --> <property name = "maxStatements" value = "$ {hibernate. c3p0. max_statements}"/> <! -- Check idle connections in the connection pool every 120 seconds --> <property name = "idleConnectionTestPeriod" value = "$ {hibernate. c3p0. idle_test_period}"/> <! -- If it is set to true, the connection validity will be verified when the connection is obtained. Default: false --> <property name = "testConnectionOnCheckin" value = "$ {hibernate. c3p0. test_connection_on_checkin}"/> <! -- Defines the number of repeated attempts after a new connection fails to be obtained from the database. Default: 30 --> <property name = "acquireRetryAttempts" value = "$ {hibernate. c3p0. acquire_retry_attempts}"/> <! -- When the connection is closed, all uncommitted operations are rolled back by default. Default: false --> <property name = "autoCommitOnClose" value = "$ {hibernate. c3p0. auto_commit_on_close}"/> <! -- Failed to obtain the connection will cause all threads waiting for the connection pool to obtain the connection to throw an exception. However, the data source is still valid, and the next call to getConnection () will continue to get the connection. If it is set to true, the data source will be declared disconnected and permanently closed after the connection fails to be obtained. Default: false --> <property name = "breakAfterAcquireFailure" value = "$ {hibernate. c3p0. break_after_acquire_failure}"/> <! -- C3p0 is asynchronous, and slow JDBC operations are completed by helping the process. These operations can effectively improve performance. Multiple operations can be executed simultaneously through multiple threads. Default: 3 --> <property name = "numHelperThreads" value = "$ {hibernate. c3p0. num_helper_threads}"/> <! -- When the connection pool is used up, the client calls getConnection () and waits for the time to obtain the new connection. After the timeout, SQLException will be thrown. If it is set to 0, the client waits indefinitely. Default: 0 --> <property name = "checkoutTimeout" value = "$ {hibernate. c3p0. checkout_timeout}"/> </bean> </span>




 

Create two files under the tomcat \ webapps \ cas \ WEB-INF directory: jdbc. properties and c3p0. properties. The content is as follows:

Jdbc. properties code

<span style="font-size:18px;">## MySQL    #jdbc.driverClassName=com.mysql.jdbc.Driver  #jdbc.url=jdbc:mysql://10.5.227.125:3306/dcsso?#useUnicode=true&characterEncoding=utf-8  #jdbc.username=root  #jdbc.password=root    ## Oracle    jdbc.driverClassName=oracle.jdbc.driver.OracleDriver  jdbc.url=jdbc:oracle:thin:@192.168.24.46:1521:orcl  jdbc.username=gxpt_jcjdbc.password=gxpt_jc</span>




C3p0. properties code

<span style="font-size:18px;">###########################  ### C3P0 Connection Pool###  ###########################  hibernate.c3p0.max_size 60  hibernate.c3p0.min_size 2  hibernate.c3p0.initial_pool_size 3  hibernate.c3p0.max_idle_time 120  hibernate.c3p0.acquire_increment 2  hibernate.c3p0.max_statements 100  hibernate.c3p0.idle_test_period 120  hibernate.c3p0.test_connection_on_checkin true  hibernate.c3p0.acquire_retry_attempts 30  hibernate.c3p0.auto_commit_on_close false  hibernate.c3p0.break_after_acquire_failure false  hibernate.c3p0.num_helper_threads 3  hibernate.c3p0.checkout_timeout 30000  hibernate.c3p0.validate true  </span>

 

C3p0 need jar package support, c3p0-0.9.1.jar, commons-collections-3.2.1.jar, commons-pool-1.5.4.jar

 

Find the propertyFileConfigurer. xml file in the \ webapps \ cas \ WEB-INF \ spring-configuration directory of tomcat, modify as follows:

Propertyfileconfigurer. xml Code

<span style="font-size:18px;"><?xml version="1.0" encoding="UTF-8"?>  <beans xmlns="http://www.springframework.org/schema/beans"         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"         xmlns:p="http://www.springframework.org/schema/p"         xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd">      <description>          This file lets CAS know where you've stored the cas.properties file which details some of the configuration options          that are specific to your environment.  You can specify the location of the file here.  You may wish to place the file outside          of the Servlet context if you have options that are specific to a tier (i.e. test vs. production) so that the WAR file           can be moved between tiers without modification.      </description>            <!--      <bean id="propertyPlaceholderConfigurer" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"          p:location="/WEB-INF/cas.properties" /> -->        <bean id="propertyPlaceholderConfigurer" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">          <property name="locations">              <list>                  <value>/WEB-INF/cas.properties</value>                  <value>/WEB-INF/jdbc.properties</value>                  <value>/WEB-INF/c3p0.properties</value>              </list>          </property>      </bean>  </beans>  </span>




Load the jdbc. properties and c3p0. properties files when the application starts.

 

Configure the Connection Pool of Oracle

Currently, Oracle only supports one connection pool. The default pool name is "SYS_DEFAULT_CONNECTION_POOL". The "DBMS_CONNECTION_POOL" package for managing the connection pool information means that the database must be operated by the DBA role.

 

Let's take a look at the description of DBMS_CONNECTION_POOL:

<span style="font-size:18px;">SQL> desc DBMS_CONNECTION_POOLElement          Type     ---------------- ---------ALTER_PARAM      PROCEDURECONFIGURE_POOL   PROCEDURERESTORE_DEFAULTS PROCEDURESTART_POOL       PROCEDURESTOP_POOL        PROCEDURE</span>




Contains five stored procedures.

SYS_DEFAULT_CONNECTION_POOL, but it is not opened. You need to display the enabled connection pool.

The first step is to enable the connection pool:

<span style="font-size:18px;">exec DBMS_CONNECTION_POOL.START_POOL('SYS_DEFAULT_CONNECTION_POOL');</span>




After the connection pool is opened, You can query it in the System View dba_cpool_info:

<span style="font-size:18px;">SQL> select connection_pool,status from dba_cpool_info; CONNECTION_POOL                                                                  STATUS-------------------------------------------------------------------------------- ----------------SYS_DEFAULT_CONNECTION_POOL                                                      ACTIVE</span>

After the connection pool is started, you can view the configuration items of the connection pool through DBMS_CONNECTION_POOL.CONFIGURE_POOL.

<span style="font-size:18px;">SQL> desc DBMS_CONNECTION_POOL.CONFIGURE_POOLParameter              Type           Mode Default?---------------------- -------------- ---- --------POOL_NAME              VARCHAR2       IN   Y       MINSIZE                BINARY_INTEGER IN   Y       MAXSIZE                BINARY_INTEGER IN   Y       INCRSIZE               BINARY_INTEGER IN   Y       SESSION_CACHED_CURSORS BINARY_INTEGER IN   Y       INACTIVITY_TIMEOUT     BINARY_INTEGER IN   Y       MAX_THINK_TIME         BINARY_INTEGER IN   Y       MAX_USE_SESSION        BINARY_INTEGER IN   Y       MAX_LIFETIME_SESSION   BINARY_INTEGER IN   Y       </span>

Parameter description:

Parameters	Description
MINSIZE	The minimum number of pooled servers in the pool. The default value is 4.
MAXSIZE	The maximum number of pooled servers in the pool. The default value is 40.
INCRSIZE	This parameter increases the number of pooled servers in each pool when a client application needs to be connected and pooled servers is unavailable.
SESSION_CACHED_CURSORS	Number of session cursors cached on each pooled servers. The default value is 20.
INACTIVITY_TIMEOUT	The maximum time (in seconds) for the pooled server to be In the idle state. After this time, the server will be stopped. 300 by default.
MAX_THINK_TIME	After a client obtains a pooled server from the pool, if the database call is not submitted within MAX_THINK_TIME, the pooled server will be released and the client connection will be stopped. The default value is 30, in seconds.
MAX_USE_SESSION	The number of times the pooled server can be taken and released on the pool. The default value is 5000.
MAX_LIFETIME_SESSION	The time, in seconds, to live for a pooled server in the pool. The default value is 3600. The life of a pooled server in the pool.

 

You can use DBMS_CONNECTION_POOL.CONFIGURE_POOL or DBMS_CONNECTION_POOL.ALTER_PARAM to modify the connection pool settings.

Let's take a look at the parameter information:

<span style="font-size:18px;">SQL>  desc DBMS_CONNECTION_POOL.ALTER_PARAMParameter   Type     Mode Default?----------- -------- ---- --------POOL_NAME   VARCHAR2 IN   Y       PARAM_NAME  VARCHAR2 IN           PARAM_VALUE VARCHAR2 IN           SQL> exec DBMS_CONNECTION_POOL.ALTER_PARAM ('','minsize','10'); PL/SQL procedure successfully completed SQL> exec DBMS_CONNECTION_POOL.ALTER_PARAM ('','maxsize','100'); PL/SQL procedure successfully completed</span>




Because there is only one connection pool, the value of the first parameter can be omitted.

Several system views are useful in the system:

The DBA_CPOOL_INFO view contains the status of the connection pool.

The V $ CPOOL_STATS view contains the statistics of the connection pool.

V $ CPOOL_CC_STATS this view contains the pool connection type level statistics

 

After the modification is successful, you can query the connection pool information:

<span style="font-size:18px;">SQL> select CONNECTION_POOL, STATUS,MINSIZE,MAXSIZE from DBA_CPOOL_INFO; CONNECTION_POOL                                                                  STATUS              MINSIZE    MAXSIZE-------------------------------------------------------------------------------- ---------------- ---------- ----------SYS_DEFAULT_CONNECTION_POOL                                                      ACTIVE                   10        100</span>





At this point, the connection pool settings and related modifications have been completed.

 

Create an index for a query field

First, check the existing indexes.

<span style="font-size:18px;">select * from user_indexes where table_name = ‘TB_STUDENT’; </span>

We can see that only the system automatically creates an index for the primary key: SYS_C55001

 

Index frequently queried fields (nearly 30 thousand data entries in the student table ).

<span style="font-size:18px;">create index student_code_idx on tb_student(student_code）;  </span>

 

The non-clustered index created here, if clustered index is used, the logical order of the key values determines the physical order of the corresponding rows in the table, and the query speed is indeed faster, however, the addition, deletion, and modification operations are slow. Therefore, be cautious when creating clustered indexes.

The index is described by the data structure of the binary tree. We can understand the clustering index as follows: the leaf node of the index is the data node. The leaf node without clustering index is still an index node, but there is a pointer pointing to the corresponding data block.