Mysql Tomcat c3p0 System Performance Tuning Personal Summary

System Information

Application logic is to query data with C3P0 to database and HTTP to return JSON data

1 Initial test results prior to tuning JMeter test result

No.	Type	Original	Data bigger
1	500Connection	query/s	63q/s 70q/s
2	Connections	255q/s	57q/s q/s

This data is the result of a database SELECT statement printed from the log of the program (which is discussed later).

2 after the IOD system prints the execution time of SQL query and the response time of Tomcat per request, the system bottleneck is identified 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 data less than the query time of about 100MS

Up to 1S of queries in 1000 video databases

Comment out this statement first, and try to use an optimized method to achieve this function.

Comment out the test data (or the database SELECT statement printed from the log of the program) after the SELECT statement Jmeter testresult

No.	Type	Original	Bigger
1	500Connection	query/s	CPU 100% 150q/s
2	Connections	255q/s	160q/s 160q/s

The problem now is that the Tomcat request 160q/s no more tuning, Tomcat's memory configuration 4G actually used less than 1 G, CPU 8 core utilization of only 10%.

3 found that the previous statistical system response performance problems, many times the SQL statement printed out, but did not complete, because the number of C3P0 connections only 15, are waiting for the database connection, and then change the statistical mode.

There is the working state of the connection pool that prints out the C3P0

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 ());//minimum number of connections            connectionbuffer.append (" getnumbusyconnections= "+ Ds.getnumbusyconnections ());/////Using connection number            connectionbuffer.append ("getnumidleconnections=" + Ds.getnumidleconnections ());///Idle connection number            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 (the default 100 maximum link), Tomcat (connection defaults) Tomcat memory configuration, C3P0 (maximum 15 links) Link Pool optimization.

Mysql: Configuring in System/programdata/mysql/my.ini

max_connections=1000

Tomcat: Configuring the number of connections

<connector port= "8080" protocol= "http/1.1"                                         minsparethreads=         "maxsparethreads=" Enablelookups= "false"                                  disableuploadtimeout= "true"                                  connectiontimeout= "20000"         acceptcount= "$"                                   Maxthreads= "minthreads=", "maxprocessors=", "                                  minprocessors="         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=256m-xx:+disableexplicitgc-xx:maxtenuringthreshold=31-xx:+useconcmarksweepgc-xx:+useparnewgc-xx:+ cmsparallelremarkenabled-xx:+usecmscompactatfullcollection-xx:largepagesizeinbytes=128m-xx:+ Usefastaccessormethods-xx:+usecmsinitiatingoccupancyonly-djava.awt.headless=true

To apply the C3P0 connection pool configuration:

<bean id= "DataSource" class= "Com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method= "Close" > &LT;PR Opertyname= "Driverclass" value= "Com.mysql.jdbc.Driver"/> <property name= "Jdbcurl" value= "jdbc:mysql://192.16 8.4.112:3306/iod1000?useunicode=true&characterencoding=utf-8 "/> <propertyname=" CheckoutTimeout "value= "60000"/> <propertyname= "idleconnectiontestperiod" value= "/> <propertyname=" initialPoolSize "value=" "/>" <property name= "maxpoolsize" value= "" "/> <property name=" minpoolsize "value=" the/> <p "                      Ropertyname= "maxstatements" value= "/> <propertyname=" "Properties" > <props> <propkey= "Preferredtestquery" >select 1</prop> <propkey= "C3p0.maxidletime" >25000</pro p> <propkey= "C3p0.testconnectiononcheckout" >true</prop> <propkey= "user" >roo              T</prop> <prop key= "password" >iptv4Um8</prop> </props> </property> </bean> 

Query configured MySQL maximum number of connections 1000, configure C3P0 Connection pool 800 Configure Tomcat link 800 test results:

CPU 8Core 12% usage

MEMMORY900M/2.4G 120q/s

The problem with this time is that neither CPU nor memory has reached the upper limit, but the performance of the query has not improved.

5 trial Jconsole View the thread state in Tomcat, a lot of threads are blocked on Java.util.logging.console the thread has been waiting for the write log to be blocked.

Comment out all the statements written in log. The OK system finally flew up.

can now reach 250q/s.

Places to check:

Cpu

Memory

MySQL Maximum number of connections

Tomcat Connection Number Configuration

Tomcat JVM configuration is important for memory size

Database Connection Pool configuration:

The bottleneck inside the program.

Mysql Tomcat c3p0 System Performance Tuning Personal Summary