Postgres Enterprise Manager aka PEM, now comes with an exciting new feature to diagnose your database cluster’s performance issues. This new feature is based on the extension edb_wait_states which comes bundled with Advanced Server. So how does it work:
>> The pre-requisite to configure this tool is, you need to configure edb_wait_states extension in the backend database of PEM as this new feature is based on that extension. This extension basically shows the wait events occurring in the database. To know more about this extension, you can review the following link:
Please make sure that the “share_preload_libraries” parameter is set to “$libdir/edb_wait_states” in postgresql.conf file and restart the Postgres cluster. After making the above change, please run the command “create extension edb_wait_states;” and you will be all set.
NOTE: Please make sure you create this extension in PEM backend database. To know which is your backend database, you can check the properties tab on PEM console or you can right click on the local pem server from the tree and click on properties.
>> By default the view of the chart is set to show the wait events occurred in the last 1 hour which you can alter to last 4, 12 or 24 hours. Also, you can select the date and time through which you want to have the data. Please see the below screen:
>> To show how the wait event is displayed, I have created a simple lock event by updating the sample row in a different transaction without mentioning commit or rollback. This will generate a lock event where the 2nd transaction is waiting for the 1st to commit or rollback to proceed. Please see the below screen:
>> Please note that under the “WAIT EVENT DETAILS”, there are three tabs which will show the SQL query which is waiting, the Users tab will show which users are involved, and the Waits tab will show the wait event type. Please see the below screens: