How to enable Query logging in Ambari server for Postgres or Mysql Database

[#1]

Steps to enable Query logging in Ambari server with Postgres Database

1. If it is Postgres as DB then add following property in ambari.properties

server.jdbc.properties.loglevel=2

After restart you can see SQL query logging to Ambari-server.out file

2.If it is MySQL as DB then add/update following property in ambari.properties.

append below value in JDBC URL

?logger=com.mysql.jdbc.log.Slf4JLogger&profileSQL=true
Ex:
server.jdbc.rca.url=jdbc:mysql://apappudb.hdp.com:3306/ambariupgrade?logger=com.mysql.jdbc.log.Slf4JLogger&profileSQL=true
and
server.jdbc.url=jdbc:mysql://apappudb.hdp.com:3306/ambariupgrade?logger=com.mysql.jdbc.log.Slf4JLogger&profileSQL=true

After re-start you will find the logs in ambari-server.log

[#2]

?logger=com.mysql.jdbc.log.Slf4JLogger&profileSQL=true

?logger=com.mysql.jdbc.log.Slf4JLogger&profileSQL=true

[#3]

Just a caution note:-

When you are trying to set "server.jdbc.properties.loglevel=2", it adds additional database query logging and it should be enabled only when we troubleshoot performance issues.

Advise to remove this property once the troubleshooting is over. We have seen 2x to 3x Ambari performance degradation when it is enabled.