MYSQL5.5.25にはオプティマイザーのバグがあったようで、
5.5.25をインストールした人は5.5.25aにアップグレードしてくださいとの事です。
Bug #65745
MySQL 5.5.25a is a replacement of MySQL 5.5.25, in that version a
regression bug was detected in the optimizer (Bug #65745).
That bug is specific to MySQL 5.5.25, other versions are not affected.
MySQL Community Server 5.5.25a has been released
改めてMYSQLのシステムテーブル(performance_schema)をレビューしてみます。
データベースサイズ
select table_schema, sum(data_length+index_length) /1024 /1024 as MB from information_schema.tables group by table_schema order by sum(data_length+index_length) desc;
テーブルサイズ
select table_name, engine, table_rows as tbl_rows, avg_row_length as rlen, floor((data_length+index_length)/1024/1024) as allMB, floor((data_length)/1024/1024) as dMB, floor((index_length)/1024/1024) as iMB from information_schema.tables where table_schema=database() order by (data_length+index_length) desc;
PERFORMANCE_SCHEMA
mysql> show tables;
+———————————————-+
| Tables_in_performance_schema |
+———————————————-+
| cond_instances |
| events_waits_current |
| events_waits_history |
| events_waits_history_long |
| events_waits_summary_by_instance |
| events_waits_summary_by_thread_by_event_name |
| events_waits_summary_global_by_event_name |
| file_instances |
| file_summary_by_event_name |
| file_summary_by_instance |
| mutex_instances |
| performance_timers |
| rwlock_instances |
| setup_consumers |
| setup_instruments |
| setup_timers |
| threads |
+———————————————-+
17 rows in set (0.01 sec)mysql>
PERFORMANCE_SCHEMAを有効にしてパフォーマンスを確認
mysql> SHOW VARIABLES LIKE ‘perf%’;
+—————————————————+———+
| Variable_name | Value |
+—————————————————+———+
| performance_schema | OFF |
| 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 | 1000000 |
| performance_schema_max_rwlock_classes | 30 |
| performance_schema_max_rwlock_instances | 1000000 |
| performance_schema_max_table_handles | 100000 |
| performance_schema_max_table_instances | 50000 |
| performance_schema_max_thread_classes | 50 |
| performance_schema_max_thread_instances | 1000 |
+—————————————————+———+
16 rows in set (0.00 sec)mysql> set performance_schema = ON;
ERROR 1238 (HY000): Variable ‘performance_schema’ is a read only variable
mysql>動的に設定変更出来ないようなので、/etc/my.cnfに以下のラインを追加して再起動。
————————————————————–
[mysqld]
performance_schema
performance_schema_events_waits_history_size=20
performance_schema_events_waits_history_long_size=1500
————————————————————–[root@CentOS64VM mysql]# /etc/init.d/mysql restart
Shutting down MySQL.. SUCCESS!
Starting MySQL…….. SUCCESS!
[root@CentOS64VM mysql]#mysql> SHOW VARIABLES LIKE ‘perf%’;
+—————————————————+———+
| Variable_name | Value |
+—————————————————+———+
| performance_schema | ON |
| performance_schema_events_waits_history_long_size | 1500 |
| performance_schema_events_waits_history_size | 20 |
| 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 | 1000000 |
| performance_schema_max_rwlock_classes | 30 |
| performance_schema_max_rwlock_instances | 1000000 |
| performance_schema_max_table_handles | 100000 |
| performance_schema_max_table_instances | 50000 |
| performance_schema_max_thread_classes | 50 |
| performance_schema_max_thread_instances | 1000 |
+—————————————————+———+
16 rows in set (0.01 sec)mysql>
テーブルが幾つかあるので、I/O関連のテーブルを2つだけ確認してみました。
file_summary_by_instance
File events summarized per file instance
SELECT SUBSTRING_INDEX(FILE_NAME, '/', -2) AS DATA_FILE, COUNT_READ, IFNULL(ROUND(SUM_NUMBER_OF_BYTES_READ/1024/1024/1024, 2), 0.00) AS READ_GB, IFNULL(ROUND((SUM_NUMBER_OF_BYTES_READ/COUNT_READ)/1024, 2), 0.00) AS AVG_READ_KB, COUNT_WRITE, IFNULL(ROUND(SUM_NUMBER_OF_BYTES_WRITE/1024/1024/1024, 2), 0.00) AS WRITE_GB, IFNULL(ROUND((SUM_NUMBER_OF_BYTES_WRITE/COUNT_WRITE)/1024, 2), 0.00) AS AVG_WRITE_KB, IFNULL(ROUND((SUM_NUMBER_OF_BYTES_READ + SUM_NUMBER_OF_BYTES_WRITE)/1024/1024/1024, 2), 0.00) AS TOTAL_GB, IFNULL(100-((SUM_NUMBER_OF_BYTES_READ/(SUM_NUMBER_OF_BYTES_READ+SUM_NUMBER_OF_BYTES_WRITE))*100), 0.00) AS WRITE_PCT FROM file_summary_by_instance ORDER BY TOTAL_GB DESC LIMIT 10;
events_waits_summary_global_by_event_name
Wait events summarized per event name
SELECT EVENT_NAME, SUM_TIMER_WAIT/1000000000 WAIT_MS, COUNT_STAR FROM performance_schema.events_waits_summary_global_by_event_name ORDER BY SUM_TIMER_WAIT DESC, COUNT_STAR DESC LIMIT 30;
参考
MySQL 5.5新機能徹底解説
Tracking IO with PERFORMANCE_SCHEMA
Monitoring MySQL IO Latency with performance_schema
Tracking mutex locks in a process list, MySQL 5.5′s PERFORMANCE_SCHEMA
21.2.2. Performance Schema Startup Configuration
21.8. Performance Schema System Variables