Just MySQL Performance Schema

After I installed MySQL. I find "Performance Schema". MySQL Performance Schema is a feature for monitoring MySQL Server execution at a low level. Performance Schema is available as of MySQL 5.5.3

mysql> select version();
+--------------+
| version() |
+--------------+
| 5.5.6-rc-log |
+--------------+

Check MySQL Performance Schema.

mysql> SHOW VARIABLES LIKE 'performance_schema';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| performance_schema | OFF |
+--------------------+-------+

It's not ON... then
# /usr/local/MySQL-5.5/bin/mysqladmin shutdown

# /usr/local/MySQL-5.5/bin/mysqld_safe --user=mysql --performance_schema &

mysql> SHOW VARIABLES LIKE 'performance_schema';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| performance_schema | ON |
+--------------------+-------+

Or modify /etc/my.cnf
[mysqld]
performance_schema

# /usr/local/MySQL-5.5/bin/mysqld_safe --user=mysql &

mysql> SHOW VARIABLES LIKE 'performance_schema';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| performance_schema | ON |
+--------------------+-------+

we can check variables with LIKE.

mysql> SHOW VARIABLES LIKE 'perf%';
+---------------------------------------------------+--------+
| Variable_name | Value |
+---------------------------------------------------+--------+
| performance_schema | ON |
| performance_schema_events_waits_history_long_size | 10000 |
| performance_schema_events_waits_history_size | 10 |
| performance_schema_max_cond_classes | 80 |
| performance_schema_max_cond_instances | 1000 |
| performance_schema_max_file_classes | 50 |
| performance_schema_max_file_handles | 32768 |
| performance_schema_max_file_instances | 10000 |
| performance_schema_max_mutex_classes | 200 |
| performance_schema_max_mutex_instances | 1000 |
| performance_schema_max_rwlock_classes | 20 |
| performance_schema_max_rwlock_instances | 1000 |
| performance_schema_max_table_handles | 100000 |
| performance_schema_max_table_instances | 50000 |
| performance_schema_max_thread_classes | 50 |
| performance_schema_max_thread_instances | 1000 |
+---------------------------------------------------+--------+

then tested it.

mysql> SELECT * FROM EVENTS_WAITS_CURRENT WHERE THREAD_ID = 22 \G
*************************** 1. row ***************************
THREAD_ID: 22
EVENT_ID: 1267
EVENT_NAME: wait/synch/mutex/sql/THD::LOCK_thd_data
SOURCE: sql_class.cc:3348
TIMER_START: 869047580523600
TIMER_END: 869047580764800
TIMER_WAIT: 241200
SPINS: NULL
OBJECT_SCHEMA: NULL
OBJECT_NAME: NULL
OBJECT_TYPE: NULL
OBJECT_INSTANCE_BEGIN: 41856000
NESTING_EVENT_ID: NULL
OPERATION: lock
NUMBER_OF_BYTES: NULL
FLAGS: 0

mysql> SELECT EVENT_ID, EVENT_NAME, TIMER_WAIT FROM EVENTS_WAITS_HISTORY WHERE THREAD_ID = 22 ORDER BY EVENT_ID;
+----------+-----------------------------------------+------------+
| EVENT_ID | EVENT_NAME | TIMER_WAIT |
+----------+-----------------------------------------+------------+
| 1258 | wait/synch/mutex/sql/LOCK_open | 45225 |
| 1259 | wait/synch/rwlock/sql/MDL_lock::rwlock | 472350 |
| 1260 | wait/synch/mutex/sql/MDL_map::mutex | 190950 |
| 1261 | wait/synch/rwlock/sql/MDL_lock::rwlock | 216075 |
| 1262 | wait/synch/mutex/sql/MDL_map::mutex | 80400 |
| 1263 | wait/synch/rwlock/sql/MDL_lock::rwlock | 80400 |
| 1264 | wait/synch/mutex/sql/MDL_map::mutex | 45225 |
| 1265 | wait/synch/rwlock/sql/MDL_lock::rwlock | 60300 |
| 1266 | wait/synch/mutex/sql/MDL_map::mutex | 45225 |
| 1267 | wait/synch/mutex/sql/THD::LOCK_thd_data | 241200 |
+----------+-----------------------------------------+------------+

mysql> SELECT * FROM EVENTS_WAITS_CURRENT WHERE THREAD_ID = 22 \G
*************************** 1. row ***************************
THREAD_ID: 22
EVENT_ID: 1493
EVENT_NAME: wait/synch/mutex/sql/THD::LOCK_thd_data
SOURCE: sql_class.cc:3348
TIMER_START: 884476565663700
TIMER_END: 884476565784300
TIMER_WAIT: 120600
SPINS: NULL
OBJECT_SCHEMA: NULL
OBJECT_NAME: NULL
OBJECT_TYPE: NULL
OBJECT_INSTANCE_BEGIN: 41856000
NESTING_EVENT_ID: NULL
OPERATION: lock
NUMBER_OF_BYTES: NULL
FLAGS: 0

mysql> SELECT EVENT_ID, EVENT_NAME, TIMER_WAIT FROM EVENTS_WAITS_HISTORY WHERE THREAD_ID = 22 ORDER BY EVENT_ID;
+----------+---------------------------------------------------------+------------+
| EVENT_ID | EVENT_NAME | TIMER_WAIT |
+----------+---------------------------------------------------------+------------+
| 1484 | wait/synch/mutex/sql/Query_cache::structure_guard_mutex | 150750 |
| 1485 | wait/synch/mutex/sql/LOG::LOCK_log | 180900 |
| 1486 | wait/synch/mutex/sql/LOCK_global_read_lock | 201000 |
| 1487 | wait/synch/rwlock/sql/MDL_lock::rwlock | 386925 |
| 1488 | wait/synch/mutex/sql/MDL_map::mutex | 206025 |
| 1489 | wait/synch/rwlock/sql/MDL_lock::rwlock | 160800 |
| 1490 | wait/synch/mutex/sql/MDL_map::mutex | 95475 |
| 1491 | wait/synch/rwlock/sql/MDL_lock::rwlock | 50250 |
| 1492 | wait/synch/mutex/sql/MDL_map::mutex | 50250 |
| 1493 | wait/synch/mutex/sql/THD::LOCK_thd_data | 120600 |
+----------+---------------------------------------------------------+------------+

It's a good Engine to Diagnose Problems.
read more