{"id":1064,"date":"2021-11-22T15:57:58","date_gmt":"2021-11-22T15:57:58","guid":{"rendered":"https:\/\/www.hammerdb.com\/blog\/?p=1064"},"modified":"2021-11-22T15:57:58","modified_gmt":"2021-11-22T15:57:58","slug":"hammerdb-v4-3-new-features-pt1-graphical-metrics-for-postgresql","status":"publish","type":"post","link":"https:\/\/www.hammerdb.com\/blog\/uncategorized\/hammerdb-v4-3-new-features-pt1-graphical-metrics-for-postgresql\/","title":{"rendered":"HammerDB v4.3 New Features Pt1: Graphical Metrics for PostgreSQL"},"content":{"rendered":"

Introducing the PostgreSQL performance metrics viewer<\/h2>\n

Prior to\u00a0 version 4.3, HammerDB included a graphical performance metrics view for the Oracle database only. At v4.3 HammerDB includes the same functionality for PostgreSQL enabling the user to drill down on database metrics in real time.\u00a0 Additionally, using the Active Session History functionality, it is possible to select a previous time period of statistics in the graph and view the PostgreSQL metrics for this earlier period of time.\u00a0 This enables the user to compare and contrast performance across different benchmark scenarios.<\/p>\n

\"\"<\/a>
PostgreSQL Graphical Metrics<\/figcaption><\/figure>\n

Install pg_stat_statements and pg_sentinel extensions<\/h2>\n

To use the PostgreSQL graphical metrics, it is necessary to install the pg_stat_statements and pg_sentinel extensions first in the database to be viewed.<\/p>\n

This example uses a PostgreSQL source based build with the pg_stat_statements extension found in the contrib directory.\u00a0 Running make and make install in this directory installs the extension.<\/p>\n

\/postgresql-14.1\/contrib\/pg_stat_statements$ make\r\n...\r\nmake[1]: Entering directory '\/opt\/postgresql-4.1\/src\/backend'\r\ngcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -O2 -fPIC -shared -o pg_stat_statements.so pg_stat_statements.o -L..\/..\/src\/port -L..\/..\/src\/common -Wl,--as-needed -Wl,-rpath,'\/opt\/postgresql-14.1\/lib',--enable-new-dtags -lm \r\n...\r\n.\/postgresql-14.1\/contrib\/pg_stat_statements$ make install\r\n...\r\n\/usr\/bin\/install -c -m 644 .\/pg_stat_statements--1.4.sql .\/pg_stat_statements--1.8--1.9.sql .\/pg_stat_statements--1.7--1.8.sql .\/pg_stat_statements--1.6--1.7.sql .\/pg_stat_statements--1.5--1.6.sql .\/pg_stat_statements--1.4--1.5.sql .\/pg_stat_statements--1.3--1.4.sql .\/pg_stat_statements--1.2--1.3.sql .\/pg_stat_statements--1.1--1.2.sql .\/pg_stat_statements--1.0--1.1.sql '\/opt\/postgresql-14.1\/share\/extension\/'\r\n...<\/pre>\n
To build pgsentinel it is necessary to download it from github and to run make and make install as we did previously for pg_stat_statements.<\/p>\n
git clone https:\/\/github.com\/pgsentinel\/pgsentinel.git\r\ncd pgsentinel\/src\r\nopt\/postgresql-14.1\/pgsentinel\/src$ make\r\ngcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -O2 -fPIC -I. -I.\/ -I\/opt\/postgresql-14.1\/include\/server -I\/opt\/postgresql-14.1\/include\/internal -D_GNU_SOURCE -c -o pgsentinel.o pgsentinel.c\r\n\/opt\/postgresql-4.1\/pgsentinel\/src$ make install\r\n\/bin\/mkdir -p '\/opt\/postgresql-14.1\/lib'\r\n...<\/pre>\n
Add the following example entries to the postgresql.conf<\/p>\n
shared_preload_libraries = 'pg_stat_statements,pgsentinel'\r\ntrack_activity_query_size=2048\r\npg_stat_statements.save=on\r\npg_stat_statements.track=all\r\npgsentinel_pgssh.enable = true\r\npgsentinel_ash.pull_frequency = 1\r\npgsentinel_ash.max_entries = 1000000<\/pre>\n
start the database and then login and create the extensions as follows<\/p>\n
postgres=# create extension pg_stat_statements;\r\nCREATE EXTENSION\r\npostgres=# create extension pgsentinel;\r\nCREATE EXTENSION<\/pre>\n
If the build went according to plan then the\u00a0 pg_active_session_history table will be found.<\/p>\n
postgres=# select * from pg_active_session_history;\r\nash_time | datid | datname | pid | leader_pid | usesysid | usename | applicatio\r\nn_name | client_addr | client_hostname | client_port | backend_start | xact_star\r\nt | query_start | state_change | wait_event_type | wait_event | state | backend_\r\nxid | backend_xmin | top_level_query | query | cmdtype | queryid | backend_type \r\n| blockers | blockerpid | blocker_state \r\n----------+-------+---------+-----+------------+----------+---------+-----------\r\n-------+-------------+-----------------+-------------+---------------+----------\r\n--+-------------+--------------+-----------------+------------+-------+---------\r\n----+--------------+-----------------+-------+---------+---------+--------------\r\n+----------+------------+---------------\r\n(0 rows)<\/pre>\n
Starting the PostgreSQL Metrics<\/h2>\n
With the pg_stat_statements and pg_sentinel extensions installed, it is now possible to start the HammerDB graphical metrics to view the Active Session History.<\/p>\n
Under the tree view there is the Metrics entry enabling the editing of options and to start the display.\u00a0 Note that if a workload is running and the treeview is locked, it is also possible to access the options from the Options menu and to start the metrics from the Metrics button.<\/p>\n
\"\"<\/a>
PostgreSQL Metrics treeview<\/figcaption><\/figure>\n
Clicking on options, it shows the login credentials for the PostgreSQL superuser.\u00a0 The Agent ID and Hostname are identical to the metrics option at earlier HammerDB versions, enabling the viewing of CPU performance by connecting to a running agent.<\/p>\n
\"\"<\/a>
PostgreSQL Metrics Options<\/figcaption><\/figure>\n
By clicking on the Metrics Display option, HammerDB will connect to the target database and start monitoring the performance, note that if the pg_active_session_history table is empty the viewer will report an error and refuse to start.<\/p>\n
\"\"<\/a>
No rows found<\/figcaption><\/figure>\n
If the pg_active_session_history table is populated (after running or having previously run a workload) the Metrics tab will become activated.<\/p>\n
\"\"<\/a>
Started Metrics<\/figcaption><\/figure>\n
Viewing PostgreSQL Metrics for a HammerDB workload<\/h2>\n
For full performance details, grab the Metrics tab and pull it out of the HammerDB window to expand.<\/p>\n
\"\"<\/a>
Drag out Metrics tab<\/figcaption><\/figure>\n
The example shows a TPROC-C workload running with 4 Active Virtual Users. The example has a configuration set to illustrate a number of wait events and has therefore not been configured for performance.<\/p>\n
\"\"<\/a>
Workload running<\/figcaption><\/figure>\n
The PostgreSQL performance metrics will automatically start to be populated in the viewer.\u00a0 The options enable the user to drill down on the SQL running in the database, the wait events and the user statistics.\u00a0 The example shows we have 4 users called tpcc running the SQL from the TPROC-C workload with key WALWrite and CPU events.\u00a0 The events are colour coded and indexed in the graph to the wait event groups.<\/p>\n
\"\"<\/a>
Metrics view for benchmark<\/figcaption><\/figure>\n
When a benchmark workload has completed, use the selection tool in the graph to select the metrics for a period of time of interest. The viewer will be populated with the metrics for that specific period of time.\u00a0 Clicking on the SQL, events or user will display the output relevant to that selection. The SQL entry gives further options of sql text, io and stats, the wait events show the SQL that caused that wait event and the users show a summary of statistics for that user. The following example shows the SQL view.<\/p>\n
\"\"<\/a>
Active Session History SQL view<\/figcaption><\/figure>\n
The following example shows the LWLock WalWrite event, illustrating the top SQL that caused that event.<\/p>\n
\"\"<\/a>
Active Session History event view<\/figcaption><\/figure>\n
Closing the Window will return it to the main GUI showing the performance graph summary.<\/p>\n
\"\"<\/a>
Summary display<\/figcaption><\/figure>\n
Pressing stop on the Metrics button will close the PostgreSQL metrics viewer.<\/p>\n
Summary<\/h2>\n
The HammerDB graphical metrics viewer in v4.3 adds the functionality to view the PostgreSQL active session history for benchmark workloads, enabling the user to find and diagnose bottlenecks in hardware and software configurations in a PostgreSQL environment.\u00a0 Further information on CPU and database metrics can be found in the documentation<\/a>.<\/p>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
Introducing the PostgreSQL performance metrics viewer Prior to\u00a0 version 4.3, HammerDB included a graphical performance metrics view for the Oracle database only. At v4.3 HammerDB includes the same functionality for PostgreSQL enabling the user to drill down on database metrics in real time.\u00a0 Additionally, using the Active Session History functionality, it is possible to select … <\/p>\n
Continue reading “HammerDB v4.3 New Features Pt1: Graphical Metrics for PostgreSQL”<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"ppma_author":[5],"class_list":["post-1064","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"authors":[{"term_id":5,"user_id":2,"is_guest":0,"slug":"hammerdb","display_name":"HammerDB","avatar_url":{"url":"https:\/\/www.hammerdb.com\/blog\/wp-content\/uploads\/2018\/10\/logo-white.png","url2x":"https:\/\/www.hammerdb.com\/blog\/wp-content\/uploads\/2018\/10\/logo-white.png"},"author_category":"","user_url":"http:\/\/www.hammerdb.com","last_name":"","first_name":"","job_title":"","description":""}],"_links":{"self":[{"href":"https:\/\/www.hammerdb.com\/blog\/wp-json\/wp\/v2\/posts\/1064","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.hammerdb.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.hammerdb.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.hammerdb.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.hammerdb.com\/blog\/wp-json\/wp\/v2\/comments?post=1064"}],"version-history":[{"count":12,"href":"https:\/\/www.hammerdb.com\/blog\/wp-json\/wp\/v2\/posts\/1064\/revisions"}],"predecessor-version":[{"id":1090,"href":"https:\/\/www.hammerdb.com\/blog\/wp-json\/wp\/v2\/posts\/1064\/revisions\/1090"}],"wp:attachment":[{"href":"https:\/\/www.hammerdb.com\/blog\/wp-json\/wp\/v2\/media?parent=1064"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.hammerdb.com\/blog\/wp-json\/wp\/v2\/categories?post=1064"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.hammerdb.com\/blog\/wp-json\/wp\/v2\/tags?post=1064"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.hammerdb.com\/blog\/wp-json\/wp\/v2\/ppma_author?post=1064"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}