Re: MySQL v3.0 - Query Analyzer problem
Posted by: Matthew Lord
Date: October 07, 2013 10:35AM

Hi Mahesh,

I'm assuming that you're using MySQL 5.6 Performance Schema as the MEM Query Analyzer source here?

If that's the case, then there is a hard limit on the number of "canonical" or "anonymized" queries that are held within Performance Schema at any one time. That limit is set by the following MySQL server variable:
http://dev.mysql.com/doc/refman/5.6/en/performance-schema-system-variables.html#sysvar_performance_schema_digests_size

That variable defaults to 10,000. You can increase that as much as needed. Just keep in mind that it will incur a slight memory overhead as you increase the size, because the performance_schema tables are stored in memory.

We don't currently flush data (TRUNCATE TABLE performance_schema.<table name>) from that table within MEM, as we don't know what other tools may be using the Performance Schema data on that MySQL server. Instead, when the limit is reached, there is a row with a NULL digest and any statement(s) that get lost get aggregated into the NULL digest. We will likely provide an option in the future that would automatically flush the old data out when the digest table becomes full, but there is not option currently.

If you simply wanted to know if any queries were lost/skipped, then you could try and determine that this way on the given MySQL server that you're pulling Query Analyzer data from:

1) mysql> show global status like "Performance_schema_digest_lost";
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| Performance_schema_digest_lost | 0 |
+--------------------------------+-------+
1 row in set (0.00 sec)

2) mysql> select * from information_schema.global_status where variable_name="Performance_schema_digest_lost";
+--------------------------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+--------------------------------+----------------+
| PERFORMANCE_SCHEMA_DIGEST_LOST | 0 |
+--------------------------------+----------------+
1 row in set (0.00 sec)

3) mysql> select count_star from performance_schema.events_statements_summary_by_digest where digest IS NULL;
Empty set (0.00 sec)


Thanks for your interest in MEM 3.0!

Best Regards,

Matt

Options: ReplyQuote


Subject
Views
Written By
Posted
3417
October 07, 2013 05:01AM
Re: MySQL v3.0 - Query Analyzer problem
2265
October 07, 2013 10:35AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.