Mysql Tomcat C3p0 System Performance Tuning personal summary,

Source: Internet
Author: User

Mysql Tomcat C3p0 System Performance Tuning personal summary,

System Information

The application logic is to use c3p0 to query data in the database and return Json data over http.

 

1. the initial test result JMeter test result before optimization

No.

Type

Original

1000 data bigger

1

500 Connection

250 query/S

63q/S
70q/S

2

1000 connections

255q/S

57q/S

65 q/S

This data is the result of the select statement of the database printed from the program log (correct or not will be discussed later ).

2. After the IOD system prints the SQL query execution time and the response time of each request of tomcat, find out the System Bottleneck because a select statement uses in:

SELECT * FROM infoobject_table where category = 'advertisement 'and deleteflag = falseand (id in (select info_object_fk from timespan_table where vod_id =? And deleteflag = false) Order By Rand () Limit

In the original small database, the query time is less than 100 ms.

The query time in the 1000 video databases exceeds 1 S.

Comment out this statement and try to use optimization methods to implement this function.

 

Comment out the test data obtained after this select statement (or calculate the database select statement printed from the program log) Jmeter testresult

No.

Type

Original

1000 bigger

1

500 Connection

250 query/S

CPU 100%
150q/S

2

1000 connections

255q/S

160q/S

160q/S

The problem now is how to tune the tomcat request 160q/S and increase the number of times. The tomcat memory is configured with 4 GB and less than 1 GB. The CPU 8 core utilization rate is only 10%.

 

3. I found that the response performance of the previous statistical system was faulty. In many cases, the SQL statement was printed, but the execution was not completed because only 15 c3p0 connections were waiting for database connections, later, the statistical method was changed.

The other is to print the status of the c3p0 connection pool.

Private void printDataConnections () {ComboPooledDataSource ds = (ComboPooledDataSource) DBConn. getDataSource (); StringBuffer connectionBuffer = new StringBuffer (); try {connectionBuffer. append ("getMaxPoolSize =" + ds. getMaxPoolSize (); // maximum number of connections connectionBuffer. append ("getMinPoolSize =" + ds. getMinPoolSize (); // the minimum number of connections connectionBuffer. append ("getNumBusyConnections =" + ds. getNumBusyConnections (); // the number of connections in use connectionBuffer. append ("getNumIdleConnections =" + ds. getNumIdleConnections (); // Number of idle connections connectionBuffer. append ("getNumConnections =" + ds. getNumConnections (); // total number of connections logger.info ("connectionBuffer =" + connectionBuffer. toString ();} catch (SQLException e) {// TODO Auto-generated catch block e. printStackTrace ();}}


Later, mysql (100 Max connections by default), tomcat (default connections) tomcat memory configuration, and c3p0 (15 Max connections) connection pool were optimized.

Mysql: configured in system/programdata/mysql/my. ini

Max_connections = 1000

Tomcat: configure the number of connections

<Connector port="8080"protocol="HTTP/1.1"                                         minSpareThreads="25"                                  maxSpareThreads="75"         enableLookups="false"                                  disableUploadTimeout="true"                                  connectionTimeout="20000"         acceptCount="200"                                   maxThreads="800"                                  minThreads="600"                                  maxProcessors="1000"                                  minProcessors="1000"         useURIValidationHack="false"                  redirectPort="8443"/>


Configure tomcat to run jvm Configuration

Set JAVA_OPTS =-server-Xms4400M-Xmx4400M-Xss512k-XX: + AggressiveOpts-XX: + UseBiasedLocking-XX: PermSize = 128M-XX: MaxPermSize = 256 M-XX: + DisableExplicitGC-XX: maxTenuringThreshold = 31-XX: + UseConcMarkSweepGC-XX: + UseParNewGC-XX: + CMSParallelRemarkEnabled-XX: + bytes-XX: 128 m-XX: + UseFastAccessorMethods-XX: + UseCMSInitiatingOccupancyOnly-Djava.awt.headless = true

Application c3p0 connection pool Configuration:

<bean id="dataSource"        class="com.mchange.v2.c3p0.ComboPooledDataSource"destroy-method="close">         <propertyname="driverClass" value="com.mysql.jdbc.Driver" />         <property name="jdbcUrl"value="jdbc:mysql://192.168.4.112:3306/iod1000?useUnicode=true&characterEncoding=utf-8"/>       <propertyname="checkoutTimeout" value="60000"/>     <propertyname="idleConnectionTestPeriod" value="30"/>     <propertyname="initialPoolSize" value="50"/>     <property name="maxPoolSize"value="800"/>     <property name="minPoolSize"value="50"/>     <propertyname="maxStatements" value="100"/>    <propertyname="properties">         <props>                      <propkey="preferredTestQuery">SELECT 1</prop>               <propkey="c3p0.maxIdleTime">25000</prop>               <propkey="c3p0.testConnectionOnCheckout">true</prop>             <propkey="user">root</prop>               <prop key="password">iptv4Um8</prop>         </props>       </property> </bean>


Query the test results of configuring mysql Max connections 1000 and c3p0 connection pool 800 and tomcat link 800:

CPU 8 Core 12% usage

Memmory900M/2.4G 120q/S

At this time, the problem was that the cpu and memory did not reach the upper limit, but the query performance could not be improved.

 

5. Use jconsole to check the thread status in tomcat. The multi-thread mode is blocked on java. util. logging. The console is originally a thread that has been waiting for log writing to be blocked.

Comment out all the statements that write logs. The OK system is finally flying.

Up to 250q/S.

 

To check:

CPU

Memory

Mysql Max connections

Tomcat connection count Configuration

The memory size is important for tomcat JVM configuration.

Database Connection Pool Configuration:

Internal program bottleneck.

 

 

 

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.