利用MySQL系统数据库做性能负载诊断的方法

1. set session transaction isolation level read uncommitted ; 
2. select  
3.   database_name, 
4.   sum(compressed_size)/1024/1024 as allocated_memory, 
5.   sum(data_size)/1024/1024 as data_memory, 
6.   sum(is_hashed)*16/1024 as is_hashed_memory, 
7.   sum(is_old)*16/1024 as is_old_memory 
8. from  
9. ( 
10.  select  
11.   case when instr(table_name,'.')>0 then replace(substring(table_name,1,instr(table_name,'.')-1),'`','') 
12.   else  'system_database' end as database_name, 
13.   case when instr(table_name,'.')>0 then replace(substring(table_name,instr(table_name,'.')+1),'`','') 
14.   else 'system_obj' end as table_name, 
15.   if(compressed_size = 0, 16384, compressed_size) as compressed_size, 
16.   data_size, 
17.   if(is_hashed = 'yes',1,0) is_hashed, 
18.   if(is_old = 'yes',1,0) is_old 
19.  from information_schema.innodb_buffer_page 
20.  where table_name is not null 
21. ) t 
22. group by database_name 
23. order by allocated_memory desc 
24. limit 10; 

1. set session transaction isolation level read uncommitted ; 
2. select  
3.  database_name, 
4.  ifnull(cast(sum(total_read) as signed),0) as total_read, 
5.  ifnull(cast(sum(total_written) as signed),0) as total_written, 
6.  ifnull(cast(sum(total) as signed),0) as total_read_written 
7. from 
8. ( 
9.  select  
10.   substring(replace(file, '@@datadir/', ''),1,instr(replace(file, '@@datadir/', ''),'/')-1) as database_name, 
11.   count_read, 
12.   case  
13.    when instr(total_read,'kib')>0 then replace(total_read,'kib','')/1024 
14.    when instr(total_read,'mib')>0 then replace(total_read,'mib','')/1024 
15.    when instr(total_read,'gib')>0 then replace(total_read,'gib','')*1024 
16.   end as total_read, 
17.   case  
18.    when instr(total_written,'kib')>0 then replace(total_written,'kib','')/1024 
19.    when instr(total_written,'mib')>0 then replace(total_written,'mib','') 
20.    when instr(total_written,'gib')>0 then replace(total_written,'gib','')*1024 
21.   end as total_written, 
22.   case  
23.    when instr(total,'kib')>0 then replace(total,'kib','')/1024 
24.    when instr(total,'mib')>0 then replace(total,'mib','') 
25.    when instr(total,'gib')>0 then replace(total,'gib','')*1024 
26.   end as total 
27.  from sys.io_global_by_file_by_bytes  
28.  where file like '%@@datadir%' and instr(replace(file, '@@datadir/', ''),'/')>0  
29. )t 
30. group by database_name 
31. order by total_read_written desc; 

1. set session transaction isolation level read uncommitted ; 
2.  
3. select  
4.  schema_name, 
5.  digest_text, 
6.  count_star, 
7.  avg_timer_wait/1000000000000 as avg_timer_wait, 
8.  max_timer_wait/1000000000000 as max_timer_wait, 
9.  sum_lock_time/count_star/1000000000000 as avg_lock_time , 
10.  sum_rows_affected/count_star as avg_rows_affected, 
11.  sum_rows_sent/count_star as avg_rows_sent , 
12.  sum_rows_examined/count_star as avg_rows_examined, 
13.  sum_created_tmp_disk_tables/count_star as avg_create_tmp_disk_tables, 
14.  sum_created_tmp_tables/count_star as avg_create_tmp_tables, 
15.  sum_select_full_join/count_star as avg_select_full_join, 
16.  sum_select_full_range_join/count_star as avg_select_full_range_join, 
17.  sum_select_range/count_star as avg_select_range, 
18.  sum_select_range_check/count_star as avg_select_range, 
19.  first_seen, 
20.  last_seen 
21. from performance_schema.events_statements_summary_by_digest 
22. where last_seen>date_add(now(), interval -1 hour) 
23. order by  
24. max_timer_wait 
25. -- avg_timer_wait 
26. -- sum_rows_affected/count_star  
27. -- sum_lock_time/count_star 
28. -- avg_lock_time 
29. -- avg_rows_sent 
30. desc 
31. limit 10; 

1. create definer=`root`@`%` procedure `create_test_data`( 
2.  in `loopcnt` int 
3. ) 
4. language sql 
5. not deterministic 
6. contains sql 
7. sql security definer 
8. comment '' 
9. begin 
10.  -- start transaction;  
11.   while loopcnt>0 do 
12.    insert into test_mrr(rand_id,create_date) values (rand()*100000000,now(6)); 
13.    set loopcnt=loopcnt-1; 
14.   end while; 
15.  -- commit; 
16. end 

1. set session transaction isolation level read uncommitted ; 
2.  
3. select  
4.  schema_name, 
5.  digest_text, 
6.  count_star, 
7.  first_seen, 
8.  last_seen 
9. from performance_schema.events_statements_summary_by_digest 
10. where sum_errors>0 or sum_warnings>0  
11. order by last_seen desc; 

1. set session transaction isolation level read uncommitted ; 
2.  
3. select substring_index(name, '/', 1) as wait_type,count(1)  
4. from performance_schema.setup_instruments 
5. group by 1  
6. order by 2 desc; 
7.  
8.  
9. select 
10. event_name, 
11. count_star, 
12. sum_timer_wait 
13. from performance_schema.events_waits_summary_global_by_event_name 
14. where event_name != 'idle' 
15. order by sum_timer_wait desc 
16. limit 100;