Issue 18232
Idle connection on Drupal MySQL DB
18232
Reporter: bko
Assignee: mblissett
Type: Bug
Summary: Idle connection on Drupal MySQL DB
Priority: Major
Resolution: Done
Status: Closed
Created: 2016-02-12 15:25:44.678
Updated: 2016-02-17 15:40:07.135
Resolved: 2016-02-17 15:40:07.053
Description: Tracing the source of the idle connections on MariaDB from User drupal_devel, it shows that these connections are established by java processes from various domain. These turn out have nothing to do with long queries run by cron jobs of Drupal modules.
By {{MariaDB> show full processlist;}} I was able to locate which process on these domains. For example, on boma this processlist shows {{devapps2-vh.gbif.org:56333}}, I then log on devapps2-vh and {{$netstat -ntp | grep :56633}}, which indicate the process ID 12110. Next, {{$ps waux | grep 12110}} shows this
{quote}
{{root 12110 17.8 2.3 5131964 782220 ? Sl 14:18 8:15 java -Xms256m -Xmx512m -XX:HeapDumpPath=/usr/local/gbif/services/registry-ws/jvm-dumps -XX:+HeapDumpOnOutOfMemoryError -XX:+UseConcMarkSweepGC -XX:CMSInitiatingOccupancyFraction=60 -Dfile.encoding=UTF-8 -jar registry-ws-2.47-20160212.131441-24.jar -conf /usr/local/gbif/services/registry-ws/2.47-SNAPSHOT/1455283100/application.properties -host apps2.gbif-dev.org -httpAdminPort 9007 -httpPort 9006 -externalAdminPort 9007 -externalPort 9006 -stopSecret stop -timestamp 1455283100 -zkHost c1n1.gbif.org:2181,c1n2.gbif.org:2181,c1n3.gbif.org:2181 -zkPath dev/services}}
{quote}
So this is on devapps2-vh.
On b14g6.gbif.org, we have
{quote}
{{tomcat 1535 0.1 6.1 11000516 618376 ? Sl 2015 259:46 /usr/lib/jvm/java/bin/java -Xms4g -Xmx8g -XX:MaxPermSize=1g -XX:+UseConcMarkSweepGC -XX:CMSInitiatingOccupancyFraction=60 -classpath :/usr/share/tomcat7/bin/bootstrap.jar:/usr/share/tomcat7/bin/tomcat-juli.jar:/usr/share/java/commons-daemon.jar -Dcatalina.base=/usr/share/tomcat7 -Dcatalina.home=/usr/share/tomcat7 -Djava.endorsed.dirs= -Djava.io.tmpdir=/var/cache/tomcat7/temp -Djava.util.logging.config.file=/usr/share/tomcat7/conf/logging.properties -Djava.util.logging.manager=org.apache.juli.ClassLoaderLogManager org.apache.catalina.startup.Bootstrap start}}
{quote}
And on devapps-vh.gbif.org, we have:
{quote}
{{tomcat 26814 0.1 57.6 3979424 2238328 ? Ssl 2015 215:37 /usr/lib/jvm/jre/bin/java -classpath /usr/share/tomcat/bin/bootstrap.jar:/usr/share/tomcat/bin/tomcat-juli.jar:/usr/share/java/commons-daemon.jar -Dcatalina.base=/usr/share/tomcat -Dcatalina.home=/usr/share/tomcat -Djava.endorsed.dirs= -Djava.io.tmpdir=/var/cache/tomcat/temp -Djava.util.logging.config.file=/usr/share/tomcat/conf/logging.properties -Djava.util.logging.manager=org.apache.juli.ClassLoaderLogManager org.apache.catalina.startup.Bootstrap start}}
{quote}
This worths follow-up to find out why the Java processes left the connection idle.
]]>
Author: mblissett
Comment: How many idle connections are there? I'd expect a few, but if it's too many we can reduce it.
Created: 2016-02-12 15:39:06.387
Updated: 2016-02-12 15:39:06.387
Author: bko@gbif.org
Comment: the 3 instances together generate about 35 ones, for example on UAT.
Created: 2016-02-12 15:44:54.032
Updated: 2016-02-12 15:44:54.032
Author: mblissett
Created: 2016-02-12 17:00:30.376
Updated: 2016-02-12 17:00:30.376
Devapps2-vh has a pool size of 4.
The others probably have 4 as well, and 4×3×3 = 36.
We could reduce it on Dev, and maybe the others, but unless it's causing a problem then this is working as expected.