I am writing to seek your guidance and suggestions on the following points regarding our Percona setup. We are looking for improvements in identifying and analyzing performance-related issues, particularly with queries, locking, and CPU utilization.
Below are the specific points we would like assistance with:
Improved Clarity in Query Issue Identification
We are looking for ways to gain more clarity in identifying issues related to running queries. Specifically, we would like to know how we can effectively find the “Top Ten Running Queries” in Percona. Could you please suggest best practices or tools available in Percona for this? Is there any way to enhance the process of identifying problematic queries, especially those causing delays?
Real-time Locking and CPU Utilization Analysis
Another area where we need further assistance is in analyzing locking queries in real-time, along with any CPU utilization issues. We would appreciate guidance on how to better monitor these aspects in real-time. Is there any feature in Percona that can assist us in identifying locking queries more efficiently as well as analyzing CPU consumption for queries that might be causing performance bottlenecks?
Query-Based Investigation Exploration
We would like to delve deeper into query-based investigations to understand query performance better. Could you suggest tools or methods within Percona to perform a detailed analysis on slow-running or resource-intensive queries? How can we leverage Percona’s capabilities to enhance our query-based investigations for optimization?
Your expertise and recommendations on these points will be greatly appreciated. We look forward to your guidance on whether these improvements can be achieved with our current setup or if there are any configurations or tools we should explore further.
I highly recommend you install Percona Monitoring and Management (PMM). The Query Analytics tool will show you the ‘top running queries’ across all databases. It will also give historical metrics for each individual query over time.
SELECT * FROM sys.innodb_lock_waits; This table shows you real-time locking information. If query A is blocking query B, it will show up here. You will need to poll this table on a regular basis. It is a live table, not a historical table.
Again, PMM is the tool you need for this. You can run live EXPLAIN plans directly from PMM.
Hmm. In the QAN, you should see all queries. Did you setup the pmm-client agent correctly? Please show us your commands. Is the performance_schema enabled?
Yes, in PMM go to ‘Nodes’ → ‘CPU Utilization’ and you can view all the CPU info.
I would also go to ‘MySQL Instance Summary’ dashboard and look for the ‘Handler’ graph. If the top result is ‘read-rnd-next’, then that means most of your queries are performing full table scans instead of using indexes.
Hi Matthewb,
I need above requirement for postgresql database, i think you gave me suggestions for MySQL database, can you please suggest for postgresql , please check below points how can set alerts and how can see on display below points .
1-High CPU utilisation issue
2-Top 10 Long running query
3-Explain plan for optimize query
4-Query locking and blocking -how can see this query for postgresql.
1, 2, and 3 are all the same for PGSQL. PMM provides all this. For #4, I’m not sure as I’m not a pgsql expert. I suggest you post in that forum category and let one of our pgsql experts chime in.
