Ask about PMM Query Analytics features

Percona Monitoring and Management (PMM) PMM 2.x
1

Hi percona team. I’m doing research for tools that works similar like solarwinds/vividcortex in term of query profiling and monitoring. My need is monitoring tools for postgres and mysql databases

I saw in your documentation:

Query Analytics data retrieval is not instantaneous and can be delayed due to network conditions. In such situations no data is reported and a gap appears in the sparkline.

So i want to ask, can PMM Query Analytics show currently running queries on the db? For example, if there are high cpu on postgres db that caused by a vacuum running on the table. The vacuum is still on going. Can we see the vacuum activity that caused CPU high in the Query Analytics? or it will appear after the vacuum done?

Thanks in advance

2

Hi!
The simplest answer is that we gather data every 1 minute, but some data like percentiles has more granularity. But the data is pulled every 1 minute by default.

The real time trouble shooting scenario you describe for Vacuum monitoring is currently not possible for PMM Query Analytics as the data for that would not show until the process is done. This is though something we are currently investigating.

3

Ok jan. Thanks for the info.

I want to ask again about ### Examples Tab in PMM Query Analytics

Does this example only display 1 sample value randomly from the query form?

For example, there is a query like this:

select * from table where id = ?

in a certain time range, the query count is 1000 queries with value id 1 - 1000. will the example tab only display one sample data? or we can choose? for example, we want to display sample data queries that run at certain hours (eg 9.00)

My needs, for example there is a slow query that causes high cpu.
I can see which queries are slow. But I want to know exactly where what condition makes it slow? Is it when querying a specific id? Is this possible in PMM Query Analytics?

Really appreciate your answer. Thanks in advance

4

also on Details Page, there are some of metrics. one of them is lock_time : Time to acquire locks

In postgres, there are various types of locks. Can we find out which lock time refers to which lock?

I also try your demo
there is a metric called Number of Queries with errors. can we know what error that query got?
Screenshot 2023-07-26 at 10.53.59

thanks in advance

5

@Rizky_Nur_Zhafirah let me clarify some of the questions. (it looks like we need some doc changes)

  1. Currently, Metrics are able to “survive” network problems and data will be inserted with proper time stamps - Log in - Percona JIRA
    We also looking to add this for QAN in 2.41 release - [PMM-8655] Send unsent messages after connection problems - Percona JIRA

  2. PMM Already collects “example per data bucket”. So we have a random example for every query in a minute executed by different Hosts and on different Services and planing to present them on UI (Log in - Percona JIRA, no eta for now). If this is critical data - it can be already extracted from PMM database.
    Note: examples and real queries are stored if you use pg_stat_monitor plugin on the server. it provides more data than the default one.

  3. The timing for different tech are different. for PostgreSQL you can see the list of metrics exposed by the PG plugin at pg_stat_monitor view reference - pg_stat_monitor Documentation and if the metric impacting the time -we’ll present it.
    For example, for Mysql we have :lock_time, innodb_io_r_wait, innodb_queue_wait, innodb_rec_lock_wait