系统做压力测试,数据库产生了性能瓶颈,MySQL压力测试SHOW VARIABLES过多。
# Rank Query ID Response time Calls R/Call V/M Item
# ==== ================== ================ ===== ====== ===== ============# 1 0xC69B6ED2C47380A4 53851.5307 98.8% 71899 0.7490 0.04 SHOW VARIABLES
# MISC 0xMISC 637.4117 1.2% 888 0.7178 0.0 <2 ITEMS>
# Query 1: 239.66 QPS, 179.51x concurrency, ID 0xC69B6ED2C47380A4 at byte 2506206# This item is included in the report because it matches --limit.
# Scores: V/M = 0.04
# Time range: 2018-05-03 20:00:46 to 20:05:46
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 98 71899
# Exec time 98 53852s 500ms 2s 749ms 992ms 183ms 705ms
# Lock time 100 9s 50us 4ms 127us 260us 104us 98us
# Rows sent 99 1.17M 17 17 17 17 0 17
# Rows examine 100 1.17M 17 17 17 17 0 17
# Query size 99 62.81M 916 916 916 916 0 916
# String:
# Hosts XX.XX.XX.XX (7249/10%)... 13 more
# Users XXXXXX
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms ################################################################
# 1s ######
# 10s+
/* mysql-connector-java-5.1.29 ( Revision: alexander.soklakov@oracle.com-20140120140810-s44574olh90i6i4l ) */SHOW VARIABLES WHERE Variable_name ='language' OR Variable_name = 'net_write_timeout' OR Variable_name = 'interactive_timeout' OR Variable_name = 'wait_timeout' OR Variable_name = 'character_set_client' OR Variable_name = 'character_set_connection' OR Variable_name = 'character_set' OR Variable_name = 'character_set_server' OR Variable_name = 'tx_isolation' OR Variable_name = 'transaction_isolation' OR Variable_name = 'character_set_results' OR Variable_name = 'timezone' OR Variable_name = 'time_zone' OR Variable_name = 'system_time_zone' OR Variable_name = 'lower_case_table_names' OR Variable_name = 'max_allowed_packet' OR Variable_name = 'net_buffer_length' OR Variable_name = 'sql_mode' OR Variable_name = 'query_cache_type' OR Variable_name = 'query_cache_size' OR Variable_name = 'init_connect'\G
SHOW VARIABLES的是由于mysql-connector-java-5.1.29产生的,当新产生一个连接的时候就会生成一条这样的语句。我想本质上让JDBC少创建连接,要连接池的连接最好是始终保持。
原始:
<Resource auth="Container" driverClassName="com.mysql.jdbc.Driver"
maxActive="300" maxIdle="30" maxWait="10000" initialSize="30" removeAbandoned="true" logAbandoned="true" name="DataSource"
password="xx#88" type="javax.sql.DataSource" testWhileIdle="true"
timeBetweenEvictionRunsMillis="10000" minEvictableIdleTimeMillis="10000"
testOnBorrow="true" testOnReturn="false"
validationQuery="select 1"
url="jdbc:mysql://xx.xx.xx.xx:3306/xx?autoReconnect=true&autoReconnectForPools=true" username="xx" />
改进之后:
一般maxIdle与maxAcitve的值应尽量接近,可以取maxIdle=maxActive。这样可以避免高负荷系统频繁创建与销毁连接。
testOnBorrow指定连接被调用时是否经过校验,如果校验未通过,则该连接被连接池断掉,并由连接池尝试调用另一个连接 值为true
testOnReturn指定连接返回到池中是时是否经过校验值为true
maxWait连接池中连接用完时,新的请求的等待时间(即等待别的连接空闲),超时返回异常,毫秒
timeBetweenEvictionRunsMillis = "30000" 每30秒运行一次空闲连接回收器
minEvictableIdleTimeMillis = "1800000" 池中的连接空闲默认为半小时后被回收
<Resource auth="Container" driverClassName="com.mysql.jdbc.Driver"
maxActive="300" maxIdle="300" maxWait="10000" initialSize="100" removeAbandoned="true" logAbandoned="true" name="DataSource"
password="xx#88" type="javax.sql.DataSource" testWhileIdle="true"
timeBetweenEvictionRunsMillis="3600000" minEvictableIdleTimeMillis="3600000"
testOnBorrow="false" testOnReturn="false"
validationQuery="select 1"
url="jdbc:mysql://xx.xx.xx.xx:3306/xx?autoReconnect=true&autoReconnectForPools=true" username="xx" />
调整之后,就没有产生SHOW VARIABLES的语句。