MYSQL5.5.25aレビュー (performance_schema)

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 5.5.25a がリリースされました

改めて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;

file_summary_by_instance

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

Post Navigation