MySQL压力测试SHOW VARIABLES过多

Love The Way You Lie 2022-05-25 02:53 135阅读 0赞

系统做压力测试，数据库产生了性能瓶颈，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的语句。