How do we identify hive metastore performance and which tables/dbs causing more pressure on Hivemetastore and number of connections on HMS

[#1]

Hi Team,

How do we identify hive metastore performance and which tables/dbs causing more pressure on Hivemetastore and number of connections on HMS?

Regards,

naresh.B

[#2]

You can find number of connections to HMS using

  lsof -p <HMS PID> | grep -i "ESTABLICHSED" | wc -l

For checking number of db/table,you have to rely on hiveserver2 logs .

We don't have any STATS to check how many times a particular DB/Table is accessed.

[#3 · Accepted solution]

Hi @nareshbattula 

Q1: hive metastore performance
Based on the memory tuning the performance could vary. Please check the below link to see how to set the memory stuff

https://docs.cloudera.com/documentation/enterprise/5-7-x/topics/admin_hive_tuning.html

Q2: which tables/dbs causing more pressure on Hivemetastore
You may have to check in the HMS logs to see which query is taking a long time.

If you are using HDP you can find the current Memory pressure and Heap usage details via the below link
https://docs.cloudera.com/HDPDocuments/Ambari-2.7.5.0/using-ambari-core-services/content/amb_hive_hi...
https://docs.cloudera.com/HDPDocuments/Ambari-2.7.5.0/using-ambari-core-services/content/amb_hive_ho...

If you are using CM then you can see the details in below link
CM > Hive > Hivemetastore > Charts > JVM Heap Usage/JVM Pause Time/

Q3: number of connections on HMS?
You can run the below commands to see the established connection/number of connections to HMS

netstat -ntpla | grep 9083
lsof -p <hms pid> | grep "ESTABLISHED" -i

If you are using CM then you can see the details in the below link
CM > Hive > Hivemetastore > Charts > Open connections

If you are happy with the reply, mark it Accept as Solution

[#4]

@nareshbattula, Has any of the replies helped resolve your issue? If so, please mark the appropriate reply as the solution, as it will make it easier for others to find the answer in the future.