Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

CDH 6.2 JDBC connection leak

Highlighted

CDH 6.2 JDBC connection leak

New Contributor

I'm using CDH 6.2 stack with Hive on Ubuntu 18.04 LTS. All the libraries have "2.1.1-cdh6.2.0" version suffix.

We've tried using both MySQL and PostgreSQL while using the following guide: https://www.cloudera.com/documentation/enterprise/6/6.2/topics/cdh_ig_hive_metastore_configure.html

Problem is, the metastore seems to spawn more and more connections over time. E.g. while using MySQL, the "show processlist;" command shows a growing number of connections with every SELECT issued from beeline. Just connecting to hive from beeline and issuing one simple "SELECT COUNT(*) from testtable;" query increases the number of connections by 4.

All those connections are sleeping connections, e.g.

11 | root | localhost:59666 | metastore | Sleep | 933 | | NULL |
| 12 | root | localhost:59668 | metastore | Sleep | 933 | | NULL |
| 15 | root | localhost:59674 | metastore | Sleep | 933 | | NULL |
| 16 | root | localhost:59676 | metastore | Sleep | 933 | | NULL |
| 19 | root | localhost:59720 | metastore | Sleep | 862 | | NULL |
| 20 | root | localhost:59722 | metastore | Sleep | 862 | | NULL |
| 23 | root | localhost:59728 | metastore | Sleep | 862 | | NULL |
| 24 | root | localhost:59730 | metastore | Sleep | 862 | | NULL |
| 27 | root | localhost:59736 | metastore | Sleep | 862 | | NULL |


The problem is simple: MySQL or any underlying database will eventually run out of connections, be it due to default limits or due to ulimit. As mentioned before, it happens both with MySQL and with PostgreSQL.

Here is hive-site.xml


<configuration>

    <property>
        <name>javax.jdo.option.ConnectionURL</name>
        <value>jdbc:mysql://localhost/metastore</value>
        <description>the URL of the MySQL database</description>
    </property>

    <property>
        <name>javax.jdo.option.ConnectionDriverName</name>
        <value>com.mysql.jdbc.Driver</value>
    </property>

    <property>
        <name>javax.jdo.option.ConnectionUserName</name>
        <value>root</value>
    </property>

    <property>
        <name>javax.jdo.option.ConnectionPassword</name>
        <value>root</value>
    </property>

    <property>
        <name>datanucleus.autoCreateSchema</name>
        <value>false</value>
    </property>

    <property>
        <name>datanucleus.fixedDatastore</name>
        <value>true</value>
    </property>

    <property>
        <name>datanucleus.autoStartMechanism</name>
        <value>SchemaTable</value>
    </property>

    <property>
        <name>hive.security.authorization.createtable.owner.grants</name>
        <value>ALL</value>
        <description>The set of privileges automatically granted to the owner whenever a table gets created.</description>
    </property>

    <property>
        <name>hive.users.in.admin.role</name>
        <value>hdfs,hive</value>
    </property>
    <property>
        <name>hive.server2.enable.doAs</name>
        <value>false</value>
    </property>

    <property>
        <name>mapred.tez.java.opts</name>
        <value>-Xmx256m</value>
    </property>
    
    <property>
        <name>hive.tez.container.size</name>
        <value>256</value>
        <description></description>
    </property>

</configuration>


Thank you for any help.