Hi all,
I'm using 2nd Gen MySQL 5.7. I want to collect data from performance_schema tables, such as "events_statements_summary_by_digest". However, this table has a max size and I think it will require periodic truncation to insert new rows. But when I run truncate table or delete command on this table, I get permission denied error
mysql> truncate table performance_schema.events_statements_summary_by_digest ; ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operationWithout deleting old rows, we won't really be able to use perf schema as it'll fill all the rows quickly. Is there a way to delete rows from this table? Are folks able to use performance schema without periodic truncation?
If no row has the statement digest value for the statement that just completed, and the table is full, the statistics for the statement that just completed are added to a special “catch-all” row with DIGEST = NULL, which is created if necessary. If the row is created, the FIRST_SEEN and LAST_SEEN columns are initialized with the current time. Otherwise, the LAST_SEEN column is updated with the current time.
The row with DIGEST = NULL is maintained because Performance Schema tables have a maximum size due to memory constraints. The DIGEST = NULL row permits digests that do not match other rows to be counted even if the summary table is full, using a common “other” bucket. This row helps you estimate whether the digest summary is representative:
A DIGEST = NULL row that has a COUNT_STAR value that represents 5% of all digests shows that the digest summary table is very representative; the other rows cover 95% of the statements seen.
A DIGEST = NULL
row that has a COUNT_STAR value that
represents 50% of all digests shows that the digest
summary table is not very representative; the other rows
cover only half the statements seen. Most likely the DBA
should increase the maximum table size so that more of
the rows counted in the DIGEST =
NULL row would be counted using more
specific rows instead. To do this, set the
performance_schema_digests_size
system variable to a larger value at server startup.