In the evolving landscape of database management, optimizing performance is paramount. For enterprises relying on Here, we are installing version 4.6 of We need to update few parameters in To know the objects in To generate the statistical report , we need to know the list of servers in pg_profile.<\/p>\n\n\n\n As a default , there is a local server. In order the generate the report , we need to take samples at regular intervals. We can then generate a report in-between those samples.<\/p>\n\n\n\n Here , We have taken four samples . We can generate the report in between these samples. For example , between 1-2,1-3 ,1-4 ,2-3 ,2-4 & 3-4 .<\/p>\n\n\n\n On viewing the generated html report, we can find SQL query statistics ,schema object statistics ,user function statistics and vacuum related statistics.<\/p>\n\n\n\n To summarize – in the current landscape of database management , it is important to monitor and optimize the database performance. In this post, we have introduced you to pg_profile, a game-changing tool tailored specifically for PostgreSQL performance monitoring. We’ve delved into its features, installation process, and seamless integration with existing PostgreSQL infrastructures, setting the stage for an immersive exploration of its capabilities.<\/p>\n\n\n\n Thank you and Stay tuned for more…<\/p>\n","protected":false},"excerpt":{"rendered":" Introduction In the evolving landscape of database management, optimizing performance is paramount. For enterprises relying on PostgreSQL, a robust monitoring […]<\/p>\n","protected":false},"author":9,"featured_media":5701,"comment_status":"open","ping_status":"open","sticky":true,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[52,1,23,42,89],"tags":[],"class_list":["post-5694","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-customer-success","category-others","category-postgresql-14","category-postgresql-15","category-postgresql-16"],"yoast_head":"\nPostgreSQL<\/code>, a robust monitoring tool can make all the difference. Using the extension pg_profile<\/code> , can be a game changer in the realm of PostgreSQL<\/code> performance tuning. Every millisecond counts, and for PostgreSQL users, navigating the complexities of performance tuning can be daunting. Monitoring and fine-tuning of slow query execution can increase the performance. In this blog , we will delve into the installation, configuration and generating the statistical report using pg_profile<\/code> . <\/p>\n\n\n\npg_profile<\/h2>\n\n\n\n
pg_profile<\/code> is a extension designed specifically for monitoring and analyzing the performance of PostgreSQL databases. It helps database administrators identify and address performance bottlenecks, optimize queries, and fine-tune configurations to enhance the overall efficiency and responsiveness of PostgreSQL database systems. pg_profile is having some key features like query performance analysis, resource monitoring , index usage analysis and configuration parameter recommendations.<\/p>\n\n\n\nInstallation<\/h2>\n\n\n\n
pg_profile<\/code> and unzip into a folder.<\/p>\n\n\n\n#Installing using wget\n[root@localhost ~]# wget https:\/\/github.com\/zubkov-andrei\/pg_profile\/releases\/download\/4.6\/pg_profile--4.6.tar.gz\n\n#Unzip into a folder\n[root@localhost ~]# tar -xvf pg_profile--4.6.tar.gz --directory \/usr\/pgsql-16\/share\/extension\npg_profile--4.6.sql\npg_profile.control\npg_profile--4.5--4.6.sql\n[root@localhost ~]# cd \/usr\/pgsql-16\/share\/extension\n[root@localhost extension]# ls -ltr\ntotal 1680\n-rw-r--r--. 1 postgres postgres 193 Apr 27 20:10 pg_profile.control\n-rw-r--r--. 1 postgres postgres 1073167 Apr 27 20:10 pg_profile--4.6.sql\n-rw-r--r--. 1 postgres postgres 635910 Apr 27 20:10 pg_profile--4.5--4.6.sql<\/code><\/pre>\n\n\n\nCreate extension<\/h2>\n\n\n\n
pg_profile<\/code> extension depends on extensions plpgsql<\/code> and dblink<\/code>. The only mandatory requirement for server cluster is the ability to connect from pg_profile<\/code> database using provided server connection string. All other requirements are optional, but they can improve completeness of gathered statistics. To check the existence of this extensions , <\/p>\n\n\n\n[root@localhost extension]# yum install postgresql16-contrib\n\n#Login in to psql and create extensions\npostgres=# create extension dblink;\nCREATE EXTENSION\npostgres=# create extension pg_stat_statements;\nCREATE EXTENSION\npostgres=# create extension pg_profile;\nCREATE EXTENSION\npostgres=# \\dx\n List of installed extensions\n Name | Version | Schema | Description \n--------------------+---------+------------+------------------------------------------------------------------------\n dblink | 1.2 | public | connect to other PostgreSQL databases from within a database\n pg_profile | 4.6 | public | PostgreSQL load profile repository and report builder\n pg_stat_statements | 1.10 | public | track planning and execution statistics of all SQL statements executed\n plpgsql | 1.0 | pg_catalog | PL\/pgSQL procedural language<\/code><\/pre>\n\n\n\nConfiguration<\/h2>\n\n\n\n
postgresql.conf<\/code> file . <\/p>\n\n\n\nshared_preload_libraries = 'pg_stat_statements'\npg_stat_statements.max = 20000\npg_stat_statements.track = top\n\n#Restart the server<\/code><\/pre>\n\n\n\npg_profile<\/code> , use the command \\dx+ pg_profile;<\/code> . It would display all the objects in the extension, including functions, tables and views.<\/p>\n\n\n\nGenerating the statistical report<\/h2>\n\n\n\n
#To know the servers list in pg_profile \npostgres=# select * from show_servers() ;\n server_name | connstr | enabled | max_sample_age | description \n-------------+---------------------------+---------+----------------+-------------\n local | dbname=postgres port=5432 | t | | \n(1 row)<\/code><\/pre>\n\n\n\n# To take samples\n\n\npostgres=# select * from take_sample();\n server | result | elapsed \n--------+--------+-------------\n local | OK | 00:00:01.38\n(1 row)\n\n#To view samples taken\npostgres=# select * from show_samples();\n sample | sample_time | sizes_collected | dbstats_reset | clustats_reset | archstats_reset \n--------+---------------------------+-----------------+---------------+----------------+-----------------\n 1 | 2024-05-17 00:38:59+05:30 | t | | | \n 2 | 2024-05-17 00:40:01+05:30 | t | | | \n 3 | 2024-05-17 00:40:06+05:30 | t | | | \n 4 | 2024-05-17 00:43:51+05:30 | t | | | \n(4 rows)<\/code><\/pre>\n\n\n\n#To generate a report in html\n\n[postgres@localhost ~]$ psql -Aqtc \"SELECT get_report('local',1,2)\" -o Report.html\n[postgres@localhost ~]$ ls -ltr\ntotal 348\ndrwx------. 4 postgres postgres 51 May 15 07:25 16\n-rw-r--r--. 1 postgres postgres 350993 May 17 00:56 Report.html<\/code><\/pre>\n\n\n\nSummary <\/h2>\n\n\n\n