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.