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" > <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