How do we identify hive metastore performance and which tables/dbs causing more pressure on Hivemetastore and number of connections on HMS?
Q1: hive metastore performanceBased on the memory tuning the performance could vary. Please check the below link to see how to set the memory stuff
Q2: which tables/dbs causing more pressure on HivemetastoreYou 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 linkhttps://docs.cloudera.com/HDPDocuments/Ambari-22.214.171.124/using-ambari-core-services/content/amb_hive_hi...https://docs.cloudera.com/HDPDocuments/Ambari-126.96.36.199/using-ambari-core-services/content/amb_hive_ho...
If you are using CM then you can see the details in below linkCM > 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 9083lsof -p <hms pid> | grep "ESTABLISHED" -i
If you are using CM then you can see the details in the below linkCM > Hive > Hivemetastore > Charts > Open connections
If you are happy with the reply, mark it Accept as Solution
View solution in original post
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.
@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.