MySQL Performance Schema (3)
This is #3 in a series of blog postings about MySQL Performance Schema.
Today when I list the tables in PERFORMANCE_SCHEMA I see:
mysql> select table_name from information_schema.tables where table_schema = 'performance_schema'; +----------------------------------------------+ | table_name | +----------------------------------------------+ | cond_instances | | events_waits_current | | events_waits_history | | events_waits_history_long | | events_waits_summary_by_event_name | | events_waits_summary_by_instance | | events_waits_summary_by_thread_by_event_name | | file_instances | | file_summary_by_event_name | | file_summary_by_instance | | mutex_instances | | performance_timers | | processlist | | rwlock_instances | | setup_actors | | setup_consumers | | setup_instruments | | setup_timers | +----------------------------------------------+ 18 rows in set (0.01 sec)
The table count fluctuates. As WL#2360 says, we’ll have a hundred ‘fore we’re through. The ones that I see today are a good representation since the rest will all be variations on the same theme. Besides the foundational table EVENTS_WAITS_CURRENT which I illustrated in my last post, there are four main types: *_history_*, *_instances, *_summary_*, *setup_*.
The *_history_* tables EVENTS_WAITS_HISTORY and EVENTS_WAITS_HISTORY_LONG.
These have the same structure as EVENTS_WAITS_CURRENT but more rows.
EVENTS_WAITS_CURRENT has “the last event for each thread”.
EVENTS_WAITS_HISTORY has “the last 30 events”.
EVENTS_WAITS_HISTORY_LONG has “the last 10000 events”.
Numbers such as ‘30′ and ‘10000′ are arbitrary and fixed at compile time; experience will tell us whether we need to adjust them.
Example selection:
mysql> SELECT DISTINCT thread_id, event_name FROM events_waits_history; +-----------+----------------------------------------------+ | thread_id | event_name | +-----------+----------------------------------------------+ | 2 | wait/synch/mutex/mysys/THR_LOCK_malloc | | 2 | wait/synch/mutex/maria/PAGECACHE::cache_lock | | 0 | wait/synch/mutex/mysys/THR_LOCK_malloc | | 0 | wait/synch/mutex/sql/LOCK_thread_count | | 1 | wait/synch/mutex/mysys/THR_LOCK_malloc | | 1 | wait/synch/mutex/sql/hash_filo::lock | | 1 | wait/io/file/sql/pid | | 1 | wait/synch/mutex/mysys/THR_LOCK_open | | 1 | wait/synch/mutex/sql/LOCK_thread_count | | 1 | wait/synch/mutex/mysys/LOCK_alarm | +-----------+----------------------------------------------+ 10 rows in set (0.28 sec)
The *_instances_* tables COND_INSTANCES FILE_INSTANCES MUTEX_INSTANCES RWLOCK_INSTANCES.
These are (relatively) static listings of event names and explanatory notes or statuses. They’re all tentative. I think we’ll present the information a different way eventually, but we’ll certainly have to present the information.
Example selection:
mysql> SELECT * FROM file_instances WHERE event_name LIKE '%maria%' LIMIT 5\G *************************** 1. row *************************** FILE_NAME: ./performance_schema/#sql2-18b4-1.MAD EVENT_NAME: wait/io/file/maria/data_file OPEN_COUNT: 0 *************************** 2. row *************************** FILE_NAME: /tmp/#sql_18b4_0.MAI EVENT_NAME: wait/io/file/maria/key_file OPEN_COUNT: 0 *************************** 3. row *************************** FILE_NAME: ./performance_schema/#sql-18b4_1.MAD EVENT_NAME: wait/io/file/maria/data_file OPEN_COUNT: 0 *************************** 4. row *************************** FILE_NAME: /usr/local/mysql/var/maria_log_control EVENT_NAME: wait/io/file/maria/Control OPEN_COUNT: 1 *************************** 5. row *************************** FILE_NAME: ./mysql/time_zone_name.MYD EVENT_NAME: wait/io/file/maria/data_file OPEN_COUNT: 1 5 rows in set (0.00 sec)
The *_summary_* tables EVENTS_WAITS_SUMMARY_BY_EVENT_NAME, EVENTS_WAITS_SUMMARY_BY_EVENT_NAME, EVENTS_WAITS_SUMMARY_BY_INSTANCE, EVENTS_WAITS_SUMMARY_BY_THREAD_BY_EVENT_NAME, FILE_SUMMARY_BY_EVENT_NAME, FILE_SUMMARY_BY_INSTANCE.
Imagine there was an INFINITE history of all events since the server start, and imagine it was easy to do groupings or aggregations on it:
SELECT SUM|AVG|COUNT|MIN|MAX(column_name) FROM EVENT_WAITS_CURRENT_HISTORY_INFINITE GROUP BY column_name(s);
Now come back to earth. An infinite history table would be bigger than the real database. We’ve got to limit the *_history_* tables to 30 or 10000 rows.
However, we can make summarized data before we throw details away, without bloating. The *_summary_* tables are all, in effect, predefined permanent SELECT agg(),… GROUP BY … tables derived from EVENTS_WAITS_CURRENT_HISTORY_INFINITE.
Example selection:
mysql> SELECT * FROM events_waits_summary_by_thread_by_event_name ORDER BY sum_timer_wait DESC LIMIT 3\G
*************************** 1. row ***************************
THREAD_ID: 2
EVENT_NAME: wait/synch/mutex/mysys/THR_LOCK_malloc
COUNT_WAIT: 110072
SUM_TIMER_WAIT: 71041916484
MIN_TIMER_WAIT: 425106
AVG_TIMER_WAIT: 645413
MAX_TIMER_WAIT: 2496981102
*************************** 2. row ***************************
THREAD_ID: 2
EVENT_NAME: wait/io/file/sql/FRM
COUNT_WAIT: 322
SUM_TIMER_WAIT: 33564274067
MIN_TIMER_WAIT: 598785
AVG_TIMER_WAIT: 104236875
MAX_TIMER_WAIT: 4181863653
*************************** 3. row ***************************
THREAD_ID: 2
EVENT_NAME: wait/synch/mutex/maria/PAGECACHE::cache_lock
COUNT_WAIT: 20197
SUM_TIMER_WAIT: 15792216396
MIN_TIMER_WAIT: 554895
AVG_TIMER_WAIT: 781909
MAX_TIMER_WAIT: 727091772
3 rows in set (0.01 sec)
The *setup_* tables SETUP_ACTORS, SETUP_CONSUMERS, SETUP_INSTRUMENTS, SETUP_TIMERS.
These are the tables that people will use to enable, disable, or adjust instrumentation. Unlike all the other PERFORMANCE_SCHEMA tables (which are strictly read-only), these tables have updatable columns which privileged users may change. Primarily they’re for “filtering in advance” so that specific actors or consumers or instruments are disabled, which eliminates clutter. The SETUP_TIMERS table is slightly different, it’s for choosing between the CYCLE, NANOSECOND, MICROSECOND, MILLISECOND, or TICK timers.
Example selection:
mysql> SELECT * FROM setup_instruments; +----------------------------------------------------------+---------+-------+ | NAME | ENABLED | TIMED | +----------------------------------------------------------+---------+-------+ | wait/synch/mutex/sql/LOCK_des_key_file | YES | YES | | wait/synch/mutex/sql/PAGE::lock | YES | YES | | wait/synch/mutex/sql/LOCK_sync | YES | YES | | wait/synch/mutex/sql/LOCK_active | YES | YES | | wait/synch/mutex/sql/LOCK_pool | YES | YES | ... | wait/io/file/maria/index_tmp | YES | YES | | wait/io/file/maria/iext | YES | YES | | wait/io/file/maria/LogDescriptorDirectory | YES | YES | | wait/io/file/maria/recovery_trace | YES | YES | +----------------------------------------------------------+---------+-------+ 239 rows in set (0.00 sec)
So by now it’s obvious what’s the benefit.
What’s the cost?
More in my next blog posting.
Leave a Reply