No relevant resource is found in the selected language.

This site uses cookies. By continuing to browse the site you are agreeing to our use of cookies. Read our privacy policy>Search

Reminder

To have a better experience, please upgrade your IE browser.

upgrade

Query the Number of Visits for Each Table Based on Audit Logs

Publication Date:  2019-04-12 Views:  23 Downloads:  0
Issue Description

Use audit logs to query the number of visits for each table and find the table with the largest number of visits.

Solution

Run the following command to check whether the audit function is enabled:

show audit_enabled; (To run this command, you need to log in to the database.)

If the command output is on, the audit function is enabled.

Enable the audit function.

gs_guc reload -N all -Z coordinator -I all -c ' audit_enabled = on'

gs_guc reload -N all -Z datanode -I all -c ' audit_enabled = on'

Run the following command to check whether the audit query function is enabled:

Show audit_dml_state_select; (To run this command, you need to log in to the database.)

If the command output is 1, the audit query function is enabled.

Run the following command to check whether the audit query function is enabled:

gs_guc reload -N all -Z coordinator -I all -c'audit_dml_state_select = 1'

gs_guc reload -N all -Z datanode -I all -c 'audit_dml_state_select = 1'

View the audit result: SELECT * FROM pg_query_audit ('2018-05-04 14:50:00', '2018-05-04 15:10:00')

As shown in the preceding figure:

time indicates the audit time, type indicates the audit type, result indicates the execution result, username indicates the execution user, database indicates the execution database, client_conninfo indicates the client connection information, and object_name indicates the queried object (for example, T1 in the figure). detail_info indicates the specific statement, node_name indicates the execution node, thread_id indicates the thread number, local_port indicates the port number, and remote_port indicates the remote port number (remote port number is null in the preceding figure since the local GSQL connection is used). If you need to query the number of visits for each table and function, refer to the following solution:

SELECT object_name,count(object_name) FROM pg_query_audit('2018-05-04 14:50:00','2018-05-04 15:10:00')where type like 'dml_action_select' group by object_name;

For details about the audit items, see FusionInsight LibrA V100R002C71SPC100 Product Documentation 01 > LibrA Database Administrator Guide > Appendix > Appendix B GUC Parameter Description > Auditing.

END