MySQL Forums
Forum List  »  Performance

Re: performance_schema.events_statements_* not populating
Posted by: Ryan Streb
Date: March 15, 2017 01:38PM

Hello, and thanks for your reply.

It's a fresh install. There are no related errors in the error log.

I found this sys table from the manual, i suspect it may be related but not sure how I'd go about troubleshooting it:

https://dev.mysql.com/doc/refman/5.7/en/performance-schema-status-variables.html

".. information about instrumentation that could not be loaded or created due to memory constraints: "

mysql> select * from sys.ps_check_lost_instrumentation;
+-------------------------------------------+----------------+
| variable_name | variable_value |
+-------------------------------------------+----------------+
| Performance_schema_file_classes_lost | 4 |
| Performance_schema_rwlock_classes_lost | 7 |
| Performance_schema_statement_classes_lost | 25 |
+-------------------------------------------+----------------+

There is certainly plenty of freemem on the box.

One other thing I noticed is that there *are* many P_S tables populated:

mysql> SELECT CONCAT(table_schema, '.', table_name), table_rows rows, CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G') DATA, CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G') idx, CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') total_size, ROUND(index_length / data_length, 2) idxfrac FROM information_schema.TABLES where table_schema = 'performance_schema' ORDER BY rows desc LIMIT 15;
+-----------------------------------------------------------------------+--------+-------+-------+------------+---------+
| CONCAT(table_schema, '.', table_name) | rows | DATA | idx | total_size | idxfrac |
+-----------------------------------------------------------------------+--------+-------+-------+------------+---------+
| performance_schema.variables_by_thread | 134656 | 0.00G | 0.00G | 0.00G | NULL |
| performance_schema.session_connect_attrs | 131072 | 0.00G | 0.00G | 0.00G | NULL |
| performance_schema.session_account_connect_attrs | 131072 | 0.00G | 0.00G | 0.00G | NULL |
| performance_schema.events_waits_summary_by_thread_by_event_name | 95744 | 0.00G | 0.00G | 0.00G | NULL |
| performance_schema.memory_summary_by_thread_by_event_name | 81920 | 0.00G | 0.00G | 0.00G | NULL |
| performance_schema.events_waits_summary_by_user_by_event_name | 47872 | 0.00G | 0.00G | 0.00G | NULL |
| performance_schema.events_waits_summary_by_host_by_event_name | 47872 | 0.00G | 0.00G | 0.00G | NULL |
| performance_schema.events_waits_summary_by_account_by_event_name | 47872 | 0.00G | 0.00G | 0.00G | NULL |
| performance_schema.events_statements_summary_by_thread_by_event_name | 43008 | 0.00G | 0.00G | 0.00G | NULL |
| performance_schema.status_by_thread | 42240 | 0.00G | 0.00G | 0.00G | NULL |
| performance_schema.memory_summary_by_host_by_event_name | 40960 | 0.00G | 0.00G | 0.00G | NULL |
| performance_schema.memory_summary_by_account_by_event_name | 40960 | 0.00G | 0.00G | 0.00G | NULL |
| performance_schema.memory_summary_by_user_by_event_name | 40960 | 0.00G | 0.00G | 0.00G | NULL |
| performance_schema.events_stages_summary_by_thread_by_event_name | 38400 | 0.00G | 0.00G | 0.00G | NULL |
| performance_schema.events_statements_summary_by_account_by_event_name | 21504 | 0.00G | 0.00G | 0.00G | NULL |
+-----------------------------------------------------------------------+--------+-------+-------+------------+---------+
15 rows in set (0.00 sec)

eg:

mysql> select * from performance_schema.events_stages_history limit 2;
+-----------+----------+--------------+--------------------------------------------------+-------------------+-------------+-----------+------------+----------------+----------------+------------------+--------------------+
| THREAD_ID | EVENT_ID | END_EVENT_ID | EVENT_NAME | SOURCE | TIMER_START | TIMER_END | TIMER_WAIT | WORK_COMPLETED | WORK_ESTIMATED | NESTING_EVENT_ID | NESTING_EVENT_TYPE |
+-----------+----------+--------------+--------------------------------------------------+-------------------+-------------+-----------+------------+----------------+----------------+------------------+--------------------+
| 48 | 1 | 5 | stage/sql/Queueing master event to the relay log | rpl_slave.cc:5746 | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 48 | 6 | 6 | stage/sql/Waiting for master to send event | rpl_slave.cc:5697 | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
+-----------+----------+--------------+--------------------------------------------------+-------------------+-------------+-----------+------------+----------------+----------------+------------------+--------------------+
2 rows in set (0.00 sec)


so some things are indeed instrumented and recorded in P_S, but certain tables like the events_statements tables dont appear to be. This renders about 20+ tables between SYS and P_S unusable .

Any last ideas? Appreciate any help you can offer.
Thanks!

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: performance_schema.events_statements_* not populating
105
March 15, 2017 01:38PM


Sorry, only registered users may post in this forum.

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.