Adding PostgreSQL Databases/Tables Size Dashboard to the Percona Monitoring and Management ...

栏目: IT技术 · 发布时间: 5年前

内容简介:It will use some custom queries in order to fetch such information, so we need to create a file with a custom query. Postgresql exporter is processing such files by default, so you should place your file into one of the paths referred to below, to the desi

Adding PostgreSQL Databases/Tables Size Dashboard to the Percona Monitoring and Management ... This blog post is about how you can gain detailed information in Percona Monitoring and Management (PMM2) of PostgreSQL disk usage. We take a look at using a catalog called pg_class , which catalogs tables and most everything else that has columns or is otherwise similar to a table. This includes indexes, sequences, views, materialized views, composite types, and toast tables.

It will use some custom queries in order to fetch such information, so we need to create a file with a custom query. Postgresql exporter is processing such files by default, so you should place your file into one of the paths referred to below, to the desired scraping interval and restart the exporter.

/usr/local/percona/pmm2/collectors/custom-queries/postgresql/low-resolution

/usr/local/percona/pmm2/collectors/custom-queries/postgresql/medium-resolution

/usr/local/percona/pmm2/collectors/custom-queries/postgresql/high-resolution

# /etc/init.d/pmm-agent restart
Stopping pmm-agent:                                        [  OK  ]
Starting pmm-agent:                                        [  OK  ]

FYI, the metrics scraping interval can be set on the tab “PMM Settings”:

Adding PostgreSQL Databases/Tables Size Dashboard to the Percona Monitoring and Management ...

Setting up Query Monitoring in PMM for Postgres

First of all, we have to enable the extension dblink . It’s a PostgreSQL contrib extension that allows users to connect to other databases and run arbitrary queries in them.

postgres=# CREATE EXTENSION dblink;
CREATE EXTENSION
 
postgres=# \dx
                                       List of installed extensions
        Name        | Version |   Schema   |                         Description                           
--------------------+---------+------------+--------------------------------------------------------------
 dblink             | 1.2     | public     | connect to other PostgreSQL databases from within a database
 pg_stat_statements | 1.6     | public     | track execution statistics of all SQL statements executed 
 plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
(3 rows)

The next step is to create a query for collecting data from tables. We propose using our bash script that generates a query and forms other fields for a custom query file. You have to specify the database name, or names, that will be monitored. You can store the result of the script in a separate file or can extend an existing file with queries.

$ ./postgres_query_size_generator.sh sbtest sbtest2 >> queries-postgres-2.yml

Now it’s time to restart exporter and check the newly collected metrics.

  • pg_class_rows
  • pg_class_total
  • pg_class_index
  • pg_class_toast

Also, you can upload our dashboard in order to checks queries through the Grafana interface. It has got all PMM2 elements such as variables, menu, annotations.

Here is a screenshot from our test installation.

Adding PostgreSQL Databases/Tables Size Dashboard to the Percona Monitoring and Management ...


以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持 码农网

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

商业模式新生代

商业模式新生代

亚历山大•奥斯特瓦德 (Alexander Osterwalder)、伊夫•皮尼厄 (Yves Pigneur) / 王帅、毛心宇、严威 / 机械工业出版社 / 2011-8-15 / 88.00元

中文官网:http://www.bizmodel.org 内容简介:当你愉快的看完第一章:商业模式画布,赫然发现这些构成要素全 都交织成一幅清晰的图像在脑海中呈现,它们如何互相影响、如何交互作用全都历历在目。利用商业模式画布分析瑞士银行、Google、Lego、Wii 、Apple等跨国企业,归纳出三种不同的产业 模式,也涵括新近的热门现象免费效应及长尾理论等。在这些有趣的例子中,我们不仅更......一起来看看 《商业模式新生代》 这本书的介绍吧!

JS 压缩/解压工具
JS 压缩/解压工具

在线压缩/解压 JS 代码

在线进制转换器
在线进制转换器

各进制数互转换器

HEX CMYK 转换工具
HEX CMYK 转换工具

HEX CMYK 互转工具