Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

Describe PMM usage of PERFORMANCE_SCHEMA resources

Description

Please integrate this email thread from where he described in-depth the data we access for MySQL from PERFORMANCE_SCHEMA and how we use it.

  • "statements_digest" consumer needs to be enabled so we can get query samples for QAN

  • "events_statements_history" consumer needs to be enabled so we can get query examples by DIGEST

  • We then scrap data from two tables, "events_statements_summary_by_digest" delivers metrics and "events_statements_history" delivers queries.

    This is later joined by DIGEST so for each metric row from "events_statements_summary_by_digest" ** we try to match a query from "events_statements_history" ** by DIGEST column.

    The query:
    SELECT DIGEST, CURRENT_SCHEMA, SQL_TEXT FROM performance_schema.events_statements_history

  • Size of both tables, "events_statements_summary_by_digest" and "events_statements_history", is limited by variables

    p.s. If size of performance_schema_digests_size gets exceeded then performance_schema_digest_lost status variable is incremented

  • The events_statements_history table contains the most recent N statement events per thread. The value of N is autosized at server startup. To set the table size explicitly, set the performance_schema_events_statements_history_size system variable at server startup. Statement events are not added to the table until they have ended. As new events are added, older events are discarded if the table is full. 

  • There is also events_statements_history_long variable, that could be alternative to events_statements_history but we don't use it.

    The events_statements_history_long table contains the most recent N statement events. The value of N is autosized at server startup. To set the table size explicitly, set the performance_schema_events_statements_history_long_size system variable at server startup. Statement events are not added to the table until they have ended. As new events are added, older events are discarded if the table is full. When a thread ends, its rows are removed from the table.

    It's not clear to me why one is preferred over the other. Probably the best reason is that it "tend to" (I didn't verify that across different versions and vendors) be disabled by default, and the other one is enabled by default:

    This is from default install of MySQL 5.7

  • Defaults for variables tend to be different between MySQL versions and vendors (e.g. MariaDB disabled performance_schema by default).
    I don't know if defaults for consumers are consistent across different versions and vendors of MySQL. I see doc provided by Roma mentions ensuring that "statements_digest" consumer is turned on, but I don't see the same for "events_statements_history" for query_examples.
    https://www.percona.com/doc/percona-monitoring-and-management/conf-mysql.html#configuring-performance-schema

    If you are running a custom Performance Schema configuration, make sure that the statements_digest consumer is enabled

How to test

None

How to document

None

Attachments

1

Smart Checklist

Activity

Show:

Michael Coburn October 17, 2019 at 9:08 PM

Is what's written not adequate? I discussed this today with and he understands what is expected
> Please integrate this email thread from Kamil Dziedzic where he described in-depth the data we access for MySQL from PERFORMANCE_SCHEMA and how we use it.

Michael Coburn March 19, 2018 at 7:09 PM

Somehow this ticket was closed?

Here is the image as well:

Michael Coburn March 19, 2018 at 7:09 PM

Hi , the key components I would like to see addressed:

  • I am confused why we list PERFORMANCE_SCHEMA setup instructions in two places - one the link you pasted, and also at this one: https://www.percona.com/doc/percona-monitoring-and-management/using.html#perf-schema

  • I would like to see the justification of the tables we use become part of our documentation.  This is helpful for Users and for Perconians to understand what is captured from where and why.  Specifically I mean I would like to see us publicly document which tables we use and how we join them to build up our query analytics - Kamil shows this over a sequence of bullet points in the description.  I'd previously suggested this be shown in the FAQ with even a subject heading, do you need more clarification on this point?  Happy to help explain further if needed!

  • The query examples can be removed via the UI on the _PMM Query Analytics Settings dashboard, see "send real query examples":

  • a

Borys Belinsky March 19, 2018 at 5:53 PM

As far as I can judge the current contents of the `Configuring MySQL for Best Results' are good enough to cover the scope of this ticket. https://www.percona.com/doc/percona-monitoring-and-management/conf-mysql.html

Would you please take at look at this section and let me know if there is anything that the documentation should contain with respect to this ticket?

 

Michael Coburn February 20, 2018 at 6:26 PM

I suggest that the first six bullets are relevant to a FAQ with a title such as "How can I confirm in my running MySQL instance that I have properly configured PERFORMANCE_SCHEMA for QAN ? ".  Or it could be alongside the documentation of configuring my.cnf with P_S as the user's next question is going to be "ok how do I verify I have properly configured my instance?".  But I think this content belongs in our Documentation.

Won't Do

Details

Assignee

Reporter

Priority

Components

Labels

Needs QA

Needs Doc

Smart Checklist

Created October 5, 2017 at 12:23 PM
Updated March 27, 2024 at 2:58 PM
Resolved March 19, 2018 at 7:09 PM